SSAS：命名查询 [英] SSAS: named queries

问题描述

我必须首先承认我是Analysis Services的新手，但现在必须使用新维度扩展现有的复杂多维数据集。因此，即使不说我什至没有开始计划也很难说出我的问题在哪里。好的，我会尝试说出我想要实现的目标。

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是一个带有命名计算返回。其表达式为：

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 是外键，而返回的旧规则是当它的maxActionCode是上述之一时，Claim（主度量值组）是 Return 。
新规则是，当索偿的maxActioncode为上述内容之一但没有先前的索偿带有fimaxActionCode IN（8，10，11，13，13，19，23）时，索偿为 Return ，24、26、27、28、29、30、33、34、36、37）。先前的声明是具有相同SSN_Number和Repair_Completion_date<的TOP（1）声明。这个主张。
现在我的问题（希望任何人都明白我的意思）：
如何创建这个新维度？我的第一个想法是创建一个新的命名查询'PreviousClaim'：

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;

然后我想创建一个新的命名计算在Claim-Table-Datasource视图中，该视图将检查是否具有先前的不定期声明。这是走的路还是我走在完全错误的轨道上？

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??

编辑：另一个问题：我将如何查询此命名查询？我可以定义参数变量fe：

another question: how would i query this named query? Can i define parameter variables f.e.:

AND (Claim.iddata=@ClaimID)

致谢，
Tim

Regards, Tim

推荐答案

如何将这些数据加载到实际的Claim维度（或其名称可能是什么）中？我问的原因是，通过在加载过程中添加此规则而不是编写MDX计算成员，可以更好地解决此任务。

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.

由于您必须回过头来确定该单位是否有使用fimaxActionCode的先前声明，而该声明可能会与MDX合并，因此您可以轻松地在加载期间将它们分开并相应地拆分（即，如果它有一个先前的索赔，或者没有一个先前的索赔，并且属于以下列出的代码之一：8、10、11、13、19、23、24， 26、27、28、29、30、33、34、36、37）。从本质上讲，您将创建某种缓慢变化的维度，因为我可能认为这些声明也具有某种时间关系，该声明何时发生（如果发生），等等，等等。

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.

有道理吗？

这篇关于SSAS：命名查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！