SSAS Debuging MDX
I've been delving into the world of MDX recently and it
is another world. You really have to get your head thinking all sorts of
different ways to try and grasp the tuples, slices, hierachies etc.
What writing calculated measures I have often found the result not to be what
I expected.
This is often due to me navigating the wrong hierarchy, naming something
wrong or navigating to a the wrong place in a dimension. For example if you want
the last an average of the last 12 months, then you need to find the period that
is 12 months before the current one. Functions like parallel period help in
allowing you to specify just that, however I found that for a fiscal calendar of
544 (weeks). It wasn't working, the number where incorrect.
Aggregate({ParallelPeriod([Date].[Date Hierarchy].[Year],1,Date.[Date
Hierarchy].CurrentMember):Date.[Date Hierarchy].CurrentMember}
So where do you start in resolving this. The key is to start from the bottom
and work up. In this case I need to check that the period returned by
ParallelPeriod was the right one. You can't just use the ParallelPeriod as that
returns a member and not a value. The trick I started using was to create a
calculated measure that returned the name of the member, simple really.
So my calculated measure was something like,
ParallelPeriod([Date].[Date Hierarchy].[Year],1,Date.[Date
Hierarchy].CurrentMember).Name
This is also useful if you spell something wrong, because you won't get an
error if you reference a level. So if you break down your main calculation and
you still get (null) it means you've navigated into a blackhole, where nothing
exists. In my case I'd done the cut and paste error in copying between my normal
date hierarchy and the fiscal one. The Fiscal one doesn't have a [Year] level it
has a [Fiscal Year] level.
-