SSAS: named queries

Question

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

Answer 1

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?