SSAS：命名查询
我必须首先承认我是 Analysis Services 的新手，但现在必须使用新维度扩展现有的复杂多维数据集。因此，即使不说我什至没有计划如何开始，也很难说出我的问题出在哪里。好吧，我会尽力告诉我我想要实现什么。
给定的是一个带有命名计算“返回”的 Datasourceview。其表达式为：
CASE WHEN fimaxActionCode IN (1, 2, 3, 4, 5, 8, 9, 12, 14, 17, 18, 20, 21, 22, 23, 24, 25, 30, 31, 32, 35) THEN
'yes'
ELSE
'no'
END
fiMaxActionCode 是一个外键，而“返回”的旧规则是，当声明（主度量组）的 maxActionCode 是上述之一时，它是一个返回。 新规则是，当索赔的 maxActioncode 为上述之一但没有先前的索赔 fimaxActionCode IN (8, 10, 11, 13, 19, 23, 24, 26 、27、28、29、30、33、34、36、37）。先前的索赔是具有相同 SSN_Number 且 Repair_Completion_date < 的 TOP(1) 索赔。这个说法。 现在我的问题（希望有人明白我的意思）： 如何创建这个新维度？我的第一个想法是创建一个新的命名查询“PreviousClaim”：
SELECT TOP (1) claim.iddata as ClaimID,PreviousClaim.idData as prevClaimID, PreviousClaim.fimaxActionCode, Claim.Repair_Completion_Date as ClaimRepDate, PreviousClaim.Repair_Completion_Date as PrevClaimRepDate
FROM tabData AS PreviousClaim LEFT OUTER JOIN
tabData AS Claim ON PreviousClaim.idData <> Claim.idData
WHERE (PreviousClaim.fimaxActionCode IN (8, 10, 11, 13, 19, 23, 24, 26, 27, 28, 29, 30, 33, 34, 36, 37)) AND (PreviousClaim.fiClaimStatus IN (1, 4, 254, 255, 6))
AND (PreviousClaim.SSN_Number = Claim.SSN_Number) AND (PreviousClaim.Repair_Completion_Date < Claim.Repair_Completion_Date)
ORDER BY PreviousClaim.Repair_Completion_Date DESC;
然后我想创建一个新的 命名计算，用于检查是否有“不规则的先前声明”。这是要走的路还是我走在完全错误的轨道上？
编辑：另一个问题：我将如何查询这个命名查询？我可以定义参数变量 fe：
AND (Claim.iddata=@ClaimID)
问候， 蒂姆
I must first admit that I'm new to Analysis Services but now must extend an existing complex cube with a new dimension. So its even difficult to tell where my problem is without saying that i dont even have a plan how to start. Ok, i will try to tell what i want to achieve.
Given is a Datasourceview with a named calculation 'Returns'. Its expression is:
CASE WHEN fimaxActionCode IN (1, 2, 3, 4, 5, 8, 9, 12, 14, 17, 18, 20, 21, 22, 23, 24, 25, 30, 31, 32, 35) THEN
'yes'
ELSE
'no'
END
fiMaxActionCode is a Foreignkey and the old Rule for 'Returns' was that a Claim(main measuregroup) is a Return when its maxActionCode is one of the above.
The new Rule is that a claim is a Return when its maxActioncode is one of the above but without having a previous Claim with fimaxActionCode IN (8, 10, 11, 13, 19, 23, 24, 26, 27, 28, 29, 30, 33, 34, 36, 37). A previous claim is the TOP(1) claim with the same SSN_Number and with a Repair_Completion_date < this claim.
Now my question(hoping that anybody has understood what i mean):
How to create this new Dimension? My first idea was to create a new named query 'PreviousClaim':
SELECT TOP (1) claim.iddata as ClaimID,PreviousClaim.idData as prevClaimID, PreviousClaim.fimaxActionCode, Claim.Repair_Completion_Date as ClaimRepDate, PreviousClaim.Repair_Completion_Date as PrevClaimRepDate
FROM tabData AS PreviousClaim LEFT OUTER JOIN
tabData AS Claim ON PreviousClaim.idData <> Claim.idData
WHERE (PreviousClaim.fimaxActionCode IN (8, 10, 11, 13, 19, 23, 24, 26, 27, 28, 29, 30, 33, 34, 36, 37)) AND (PreviousClaim.fiClaimStatus IN (1, 4, 254, 255, 6))
AND (PreviousClaim.SSN_Number = Claim.SSN_Number) AND (PreviousClaim.Repair_Completion_Date < Claim.Repair_Completion_Date)
ORDER BY PreviousClaim.Repair_Completion_Date DESC;
Then i wanted to create a new Named Calculation in the Claim-Table-Datasourceview which checks if it has an "irregular-previous claim". Is this the way to go or am i on the completely wrong track??
EDIT: another question: how would i query this named query? Can i define parameter variables f.e.:
AND (Claim.iddata=@ClaimID)
Regards,
Tim
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这些数据如何加载到实际的索赔维度（或者无论它的名称是什么）？我问的原因是，通过在加载过程中添加此规则而不是编写 MDX 计算成员，可以更好地解决此任务。
由于您必须返回数据来确定该单元是否有先前的 fimaxActionCode 声明，这对于 MDX 来说可能会很麻烦，因此您可以在加载时轻松地将它们分开并相应地拆分它们（即，如果它曾有过先前的索赔或没有先前的索赔且属于所列代码之一：8、10、11、13、19、23、24、26、27、28、29、30、33、34、36 ，37）。本质上，你会创建某种缓慢变化的维度，因为我可能假设这些声明也有某种时间关系，如果发生的话，这个声明是什么时候发生的，等等。
通过按照我建议你的方式解决问题可以利用相同的维度，只添加额外的字段来表示该项目是否是索赔，如果它不属于“新”退货标准，您只需保留该记录即可。
有道理吗？
How is this data loaded into the actual Claim dimension (or whatever it's name may be)? The reason I ask is, this task may be better solved by adding this rule during the load process rather than writing an MDX calculated member.
Since you'd have to go back through the data to determine whether the unit has had a previous claim with the fimaxActionCode, which could get nasty with MDX, you could easily separate these out during load time and split them accordingly (i.e., if it has had a previous claim OR has not had a previous claim and falls into one of those listed codes: 8, 10, 11, 13, 19, 23, 24, 26, 27, 28, 29, 30, 33, 34, 36, 37). Essentially you would be creating a slowly-changing dimension of some sorts since I probably assume these claims also have some sort of time relationship, when did this claim happen if it did, etc, etc.
By tackling the problem through the way I suggested you could utilize the same dimension and just add the additional fields that would denote whether the item was a claim or not and if it did not fall into the "new" Return criteria you would simply leave the record alone.
Make sense?