CODEWITHSUNDEEP
ssasmdx
Tim.Messar
Tim.Messar

Reputation: 21

MDX - How to get the most recent grandchild for each parent?

I have problem regarding how I can get dimension information for the most recent grandchild while rolling up fact amounts from the childchild's parent, for each parent, (if this makes sense, I'm pretty new to SSAS and MDX so please bear with me I'll explain further).

I'm not exactly sure how to structure my tables (using dim key or group key) or attribute relationships in my dimension as well as how I can effeciently search the cube, perferably in block mode instead of cell by cell.

Here is the lay of the land:

Fact table with 500+million rows with adjustments made to the financials so summing from a date to the beginning for time is what I am currently working with.

The Dimenion table of 70+million rows has an Account (grandparent) which can have 1:M Claims (Parent) which is also type-2 having claim level informtion (grandchildren?) with record begin and end dates when dimension changes occur for each individual claim. There is a Dim Key at the grandchild level and a group key at the claim (parent) level.

So far I've attempted using a bridge table between the Fact and Dim and used the following MDX logic, which works ok for very small accounts but doesn't scale well with larger accounts. One thing to note, larger accounts can have around 60,000 claims.

With the following, the thought process was to get the tail for each claim <= the date passed in. Then use this information against the fact data slicing and summing the data for each of the claims up to the date passed in via group key (not dim key, as totalled financials are needed).

with 
set [FilterRecBeginDate] as 
Generate([CLAIM].[Claim Number].Children,
Tail(Filter(
    CrossJoin([CLAIM].[Claim Number].CurrentMember,
          {[CLAIM].[Claim Record Begin Date].Children},
          {[CLAIM].[Claim Status Code].Children}
          )
, [CLAIM].[Claim Record Begin Date].CurrentMember.Name <= '2009-08-23'
)
)
)
select 
{[Measures].[Paid Amount], 
 [Measures].[Paid Loss Amount]}on columns,
non empty [FilterRecBeginDate] on rows
from [Cube]
where ([CALENDAR DATE].[Date].&[19000101]:[CALENDAR DATE].[Date].&[2009-08-23T00:00:00]) *
{([CLAIM].[Claimant Name].&[xxxxx])}

1.) How can I structure the cube differently and/or Dim Attribute Relationships to better support this type of query, given only an Account and a Date as parameters?

2.) I have played around with MDX Generate and Desendants which seem to both loop one-by-one. Perhaps looping is the only option, but I'm hopeful that there is a better way. Can block mode be leveraged by writing the MDX differently?

Thanks in advance.

Upvotes: 0

Views: 830

Answers (1)

Benoit
Benoit

Reputation: 1993

I think you can optimize the computation of FilterRecBeginDate by reducing the size of your crossjoin. You can also compute the Filter one time:

with 
set [tmp] as 
    CrossJoin(
        Filter([CLAIM].[Claim Record Begin Date].Children, 
               [CLAIM].[Claim Record Begin Date].CurrentMember.Name <= '2009-08-23'
        ), 
        [CLAIM].[Claim Status Code].Children
    )
set [FilterRecBeginDate] as 
    Generate([CLAIM].[Claim Number].Children,
        Tail(
            CrossJoin({[CLAIM].[Claim Number].CurrentMember}, [tmp])
        )
    )

Upvotes: 0

Related Questions