Dynamic Set for MDX Calculations

PROBLEM

I have been running into a problem trying to get the MIN, MAX and MEDIAN MDX calculations to work against a dynamic set as opposed to setting a static set in these Calculated Members in my cube. However, there was a wrinkle to my problem, the dynamic set would go across attributes of the same dimension and this is where I was running into problems.

For simplicity, I have the following tables in my warehouse: LOAN, GEOGRAPHY, LOAN_FACT, TIME

• In the LOAN, I have an ID and a LOADING SYS field. The LOADING SYS field describes what source system the Loan came from
• In the Geography table, I have a Geography ID and Geography Name (very simplistic for this post)
• In the LOAN_FACT, I have the Loan Id, Geography Id, Time Id and EOM BAL.

When I create my cube, the LOAN FACT table is the FACT table and the other tables are the Dimensions of the cube.

So far, so simple.

Now, I want to create calculated members within the cube that gives me the MIN, MAX, and MEDIAN EOM BAL across whatever is selected by the user with the understanding one principal, the LOADING SYS is a very important breakdown of the data.

SOLUTION TAKE 1

I started with the following calculation:

CREATE MEMBER CURRENTCUBE.[MEASURES].[EOM BAL MIN] AS

MIN([LOAN].[LOAN].children, [MEASURES].[EOM BAL])

Then a select statement like the following would be expected to give me my result:

SELECT [MEASURES].[EOM BAL MIN] on COLUMNS,

NON EMPTY(

[GEOGRAPHY].[GEOGRAPHY NAME].CHILDREN *

[LOAN].[LOADING SYS].CHILDREN

) on ROWS

FROM [LOAN CUBE]

However, this did not work as it went through and game me the lowest EOM BAL for every loan within a Geography regardless of the LOADING SYS and then repeated that value for each LOADING SYS even if the LOADING SYS did not exist for a GEOGRAPHY. A Really Bad Answer!

SOLUTION TAKE 2

Taking another stab at the calculation, I updated it to read:

CREATE MEMBER CURRENTCUBE.[MEASURES].[EOM BAL MIN] AS

MIN(DESCENDANTS([LOAN].[LOAN].CURRENTMEMBER,,LEAVES), [MEASURES].[EOM BAL])

This answer came close as it would give me the lowest loan value within a GEOGRAPHY and only present those LOADING SYS values that actually existed. However, it would still only give me the MIN EOM BAL within the GEOGRAPHY regardless of the LOADING SYS, so it still was incorrect.

CORRECT SOLUTION

After a little research, I found that I could setup my CURRENT LOADING SYS as a CALCUALTED MEMBER against the MEASURES Parent Hierarchy. Once I had that, I could setup my LOAN set to use this member. The following two CALCULATED MEMBERS were then created and viola, my answer that I wanted:

CREATE MEMBER CURRENTCUBE.[MEASURES].[CURRENT LOADING SYS] AS

[LOAN].[LOADING SYS].CURRENTMEMBER.NAME

CREATE MEMBER CURRENTCUBE.[MEASURES].[EOM BAL MIN] AS
MIN(
(DESCENDANTS([LOAN].[LOAN].CURRENTMEMBER,,LEAVES), IIF([MEASURES].[CURRENT LOADING SYS] = 'ALL', [MEASURES].[CURRENT LOADING SYS], STRTOMEMBER([MEASURES].[CURRENT LOADING SYS]))),
[MEASURES].[EOM BAL])

Now, if I run the following again:

SELECT [MEASURES].[EOM BAL MIN] on COLUMNS,

NON EMPTY(

[GEOGRAPHY].[GEOGRAPHY NAME].CHILDREN *

[LOAN].[LOADING SYS].CHILDREN

) on ROWS

FROM [LOAN CUBE]

What I return is the MIN EOM BAL within a GEOGRAPHY and LOADING SYS. If I take GEOGRAPHY out of the SELECT, then the query will return the MIN EOM BAL within a LOADING SYS. If I add another dimension to the query, maybe TIME.YEAR for instance, I get the proper results as well.

A couple of things to notice:

• I had to check for the ALL member as the STRTOMEMBER function would not return a result so my Grand Total would error out without this check.
• I still used the DESCENDANTS function to get to the lowest loan grain within the context of the query

CONCLUSION

One can take advantage of the CURRENTMEMBER of a dimension's query context to obtain a different attribute's value within that DIMENSION. This value can then be used in a later calculation for basically creating a DYNAMIC SET according to the query context and essentially grouping by that value for your calculation.