Never thought that someone can stick with me for 5 years (my wife), just kidding.Life is about all those little surprises isn’t it :), but what about the EPM ones.
I agree with Tim German – “They’re opportunities. Infuriating, head-pounding, driving-to-drink opportunities.”
I was working on an ASO cube where I had to create a 27 month rolling view.
I got the MDX formula and was looking at the results and to my surprise none of the values are correct.
During troubleshooting I thought of checking whether I can jump years and updated my formula to the one above. Guess what it is not working!!!!!, so there lies our problem.
Note: You can use ([Base],[Years].CurrentMember.Lead(1)), I just wanted to see if LEVEL will work or not.
Next step was to see whether I can move in time, and lead on time dimension was working fine. I started thinking “Does Lead only work on Time dimension?”
Let’s look at what TechRef says about Lead – nothing about just on Time dimension, so it must work with any other dimension!!!
If you look carefully there is a note about “Multiple Hierarchies Enabled”
When multiple hierarchies are enabled, this function returns NULL when the source member is in one hierarchy and the result member belongs to a different hierarchy.
This is my Year dimension.
My result and source are in the same hierarchy!!!, so that is not the issue.
I tried to look at query tracking and found this.
By the way MaxL is executed from MDX Editor :), it tells us that there were no “Stored Queries” executed – Lead didn’t work.
Just for kicks, I thought of making Years as Dynamic and guess what it started working!!!!
Changed Years to Stored (by removing the formula member) it works there too.
So Lead (maybe the rest of the MDX like Lag, NextMember, PrevMember) doesn’t work with multiple hierarchies. Now could that be true, well yes and no 🙂
I started looking at all possible explanations and found one hidden in the DBAG – Look under Hierarchies in Aggregate Storage Applications, Databases, and Outlines
The first hierarchy in a multiple hierarchies enabled dimension must be a stored hierarchy.
I updated my Years dimension as below.
To summarize this surprise 🙂
Lead/NextMember works with “Multiple Hierarchies Enabled” if and only if the Parent is stored/dynamic hierarchy.