MDX Trick

So what was the problem I was trying to solve.

I need to compute something called – Change Over Previous Year, so for example if my Quarterly data looks like

2004-Q1 2004-Q2 2004-Q3 2004-Q4 2005-Q1 2005-Q2 2005-Q3 2005-Q4 2006-Q1 2006-Q2
123 456 789 987 654 321 123 456 789 987

I wanted to get the Change of each quarters data for previous year. The output should look like

2004-Q1 2004-Q2 2004-Q3 2004-Q4 2005-Q1 2005-Q2 2005-Q3 2005-Q4 2006-Q1 2006-Q2
654-123 321-456 123-789 456-987 789-654 987-321
531 -135 -666 -531 135 666

The 2nd row above shows the actual values that are desirable.

The simplest MDX Query to do the above would be something like this

With Member [Measures].[Value Change] as

‘(([Time].CurrentMember, [Measures].[Value]) – (ParallelPeriod ([Time.Time].[Year], 1, [Time].CurrentMember), [Measures].[Value]))’

SELECT {[Time.Time].[Quarter].Members} ON COLUMNS,

{[Product.Product_Package].[product_name].[Patient Visits]}  ON ROWS

FROM [CubeName]

WHERE ([Measures].[Value Change])

It was returning me the values as below – (the 1st 4 columns of which are undesirable to me)

2004-Q1 2004-Q2 2004-Q3 2004-Q4 2005-Q1 2005-Q2 2005-Q3 2005-Q4 2006-Q1 2006-Q2
123-NA 456-NA 789-NA 987-NA 654-123 321-456 123-789 456-987 789-654 987-321
123 456 789 987 531 -135 -666 -531 135 666

So I changed the MDX query to be this now

With Member [Measures].[Value Change] as

‘(IIF( IsEmpty( (ParallelPeriod ([Time.Time].[Year], 1, [Time].CurrentMember), [Measures].[Value]) ),  (([Time].CurrentMember, [Measures].[Value]) – (ParallelPeriod ([Time.Time].[Year], 1, [Time].CurrentMember), [Measures].[Value]))/ ( (ParallelPeriod ([Time.Time].[Year], 1, [Time].CurrentMember), [Measures].[Value])), ([Time].CurrentMember, [Measures].[Value]) – (ParallelPeriod ([Time.Time].[Year], 1, [Time].CurrentMember), [Measures].[Value])))’

SELECT {[Time.Time].[Quarter].Members} ON COLUMNS,

{[Product.Product_Package].[product_name].[Patient Visits]}  ON ROWS

FROM [CubeName]

WHERE ([Measures].[Value Change])

What I did – was essentially use 2 – functions provided by MDX.

IfEmpty( ) and IIF ( )

I first use the method – IsEmpty( ) on the [Measures].[Value] for the Previous Year’s Time Period – which is – ParallelPeriod ([Time.Time].[Year], 1, [Time].CurrentMember)

and if that is EMPTY – then I do a calculation that is sure to return me Infinity or NAN ( not a Number ) – otherwise I return the original calculated value that I want to.

Once, I get a values of NAN / Infinity from the OLAP Engine – my Downstream Code and Visualizing tool picks that up as a NA and does not show any Number

So initially if my Visualizing tool was showing data as below ( with a different data set from the one shown above in the table )

Now with the modified MDX Query the visualization looks more like this

Now It correctly shows the data as

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s