SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

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