Reporting Services MDX Generation
Just discovered something interesting the other day which I’d
like to share. I was getting very odd and different results between two almost
identical SSRS reports which were using a cube as a data source. The crutch of
the report was a calculated measure that was stored in the cube and the only
thing that was different between the reports was an additional column selected.
One of the reports was correct and the other was blatantly
wrong, so just to test the theory I added the additional column which resulted in the
correct report. But then strangely I deleted the column and the report still
remained correct. A deeper dig into the generated MDX by both the original and
the dodgy and I could see why. (with of course the helpful formatting feature
of Mosha’s MDX studio
http://www.mosha.com/msolap/mdxstudio.htm)
Reporting services MDX generation essentially works by using
recursive select statements for filtering\column addition. Adding the additional
column had modified the MDX in a way that didn’t get undone when the column was
deleted. What had resulted was the correct filtering of data that I was
interested in. Which is good, because I solved the problem. However what I
learned from the whole experience is never to just trust a fire and forget
query generation tool, but to try and understand how the query is being
generated and what question it is trying to answer. MDX can be fickle enough at
the best of times but its just worth eyeballing the resulting query just to see
if there are any nasties in there.