Solution to “Very strange results from MDX Query”

The solution to “Very strange results from MDX Query

It turns out that there was both a psychological component and a technical one. The sleuth who help me discover these issues was none other than my friend, former and occasionally current colleague, Scot Reagin.

  • The technical issue: I was using FORMAT_STRING to format the numbers I was using as my test case (primarily for presentation convenience). It turns out that this formatting had enough of an impact to present rounding errors.
  • The psychological issue: It turns out that the rounding errors affected the numbers that I was using to test my results but not the results themselves. In other words, I assumed that MonthProjection should match (MTD / PCComplete) after I exported these figures to a spreadsheet and performed equivalent calculations. However, since I introduced rounding errors in my test figures, the numbers I was using to test for a correct result were in fact incorrect. The MonthProjection figure in my original post was in actual fact, correct. I was trying to get a correct answer to match an incorrect test case, based on the assumption that the test case was perfect.

I assumed my testing code was perfect. << sigh >>

I still think that this method of forecasting the month is very slick. In fact today at the client I complimented the analyst that came up with it.

The good news – today I presented a prototype of this forecasting technique against production sales data. There are actually more refinements developed, and more to come, so the end result is pretty cool.

I am really fortunate to be able to work with smart and innovative people.

This entry was posted in Analysis Services, Business Intelligence, MDX, OLAP. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *