readBehavior：使用Lookup查询源代码 [英] readBehavior:query to source with Lookup
问题描述
Hi Team，
我有一个管道使用一些查找来查找基于ID的维度键，因此Fact表可以使用Dimensions中的键值填充。所以Persn Lookup返回PersonKey&具有Hsptl_End的Person Id返回Person
和Datew形成为Int
我用于Source Fact_Enrolment的源Sql：
SELECT OPE.ID
       ，每[ID]群组。                                                         AS PERSON_ID
      ，INT（活动（'Lkup_D_PERSN_KEY'）。OUTPUT.value [0] .PersonKey） AS PersonKey
      ，INT（活动（'Lkup_Prs_HSPTL_ED'）。OUTPUT.value [0] .HsptlEDKey）AS HSPEDKey
      ，CAST（CONVERT（VARCHAR（8），per.DoB，112）AS INT）                   AS BirthDateKey
       ，" 1’                                                                
AS CountEnrolment
      ，CASE
                   当OPE.EndDate不为空时¥b $ b                               那么'1'
                              ELSE 0
        END AS CountHistoricalEnrolment
FROM
        [dbo]。[PERSON] AS每
        INNER JOIN
                    [dbo]。[PRGRM_ENROLMENT] AS OPE
                    ON¥b $ b                               OPE.PERSON_ID = per.ID
WHERE
        OPE.PERSON_ID IN（66111,68669,68670,68674）---仅限于这些ID用于测试
        AND per.ID = INT（活动（'Lkup_D_PERSN_KEY'）。OUTPUT.value [0] .PersonId）
        AND per.ID = INT（活动（'Lkup_Prs_HSPTL_ED'）。OUTPUT.value [0] .PERSON_ID）
ORDER BY
        per.ID
我将SQL添加为动态内容，以便它可以读取Lookup输出，然后更改要添加的代码告诉DF将其作为查询读取的属性，我在另一个管道中看到了这个，但它有SalesForce作为Source。下面是readBehavior的
代码的副本。
哪个应该产生这个结果但不是，我不知道为什么我没有正确使用Lookup值。
任何指针都会有所帮助
谢谢
Binway
Binway
Hello Binway，并感谢您的询问。
我有一个建议。 为了帮助调试并可能简化您的查询，我建议您将每个查找的输出存储到SetVariable中。 完成所有这些操作后，在另一个
SetVariable活动中进行连接/查询构建，并在CopyData活动中进行简单引用。
这样做的好处是，能够查看流程的每个步骤会发生什么。 这也应该强制所有内容都是字符串，所以你不应该有意想不到的类型。 这个建议对其他人有所帮助。
请告诉我这是否对您有所帮助。
Martin Jaffer，Azure CXP工程师。
Hi Team,
I have a pipeline that is using some lookups to find dimension keys based on Ids so the Fact table can be populated with the key values from the Dimensions. So the Persn Lookup returns the PersonKey & Person Id with the Hsptl_End returning the Person and a Datew formated as an Int
The Source Sql I am using for the Source Fact_Enrolment:
SELECT OPE.ID,per.[ID] AS PERSON_ID
, INT ( activity('Lkup_D_PERSN_KEY').OUTPUT.value [0].PersonKey ) AS PersonKey
, INT ( activity('Lkup_Prs_HSPTL_ED').OUTPUT.value [0].HsptlEDKey ) AS HSPEDKey
, CAST(CONVERT(VARCHAR(8), per.DoB, 112) AS INT) AS BirthDateKey
,'1' AS CountEnrolment
, CASE
WHEN OPE.EndDate IS NOT NULL
THEN '1'
ELSE 0
END AS CountHistoricalEnrolment
FROM
[dbo].[PERSON] AS per
INNER JOIN
[dbo].[PRGRM_ENROLMENT] AS OPE
ON
OPE.PERSON_ID = per.ID
WHERE
OPE.PERSON_ID IN ( 66111 , 68669 , 68670 , 68674 )---limited to these Ids for testing
AND per.ID = INT ( activity('Lkup_D_PERSN_KEY').OUTPUT.value [0].PersonId )
AND per.ID = INT ( activity('Lkup_Prs_HSPTL_ED').OUTPUT.value [0].PERSON_ID )
ORDER BY
per.ID
I added the SQL in as an Dynamic Content so it can read the Lookup output, then change the code to add a property to tell DF to read it as a query, I have seen this in another pipeline but it had SalesForce as the Source. Below is the copy of the code where the readBehavior goes.
Which should produce this result but isn't and I have no idea why other than I am not using the Lookup values correctly.
Any pointers will be helpful
Thanks
Binway
Binway
Hello Binway, and thank you for your inquiry.
I do have a suggestion. To help with debugging, and possibly simplify your query, I recommend that you store the output of each of your lookups into a SetVariable. Once all of those are complete, do the concatenation / query building in another SetVariable activity, and simple reference that in your CopyData activity.
The benefit of this, is being able to view what happens every step of the process. This should also force everything to be a string, so you shouldn't have unexpected types. This suggestion has helped others.
Please let me know if this helps you any.
Martin Jaffer, Azure CXP Engineer.
这篇关于readBehavior：使用Lookup查询源代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
