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.
Published Wednesday, January 6, 2010 6:42 PM by blakmk
Filed under: , ,

Comments

No Comments