I’m busy preparing a nice cube environment for Tech Ed this year, and I stumbled across something I haven’t had to deal with before. Basically I’m throwing together a collection of random statistics per country over the last couple of years.
I found a fantastic site, http://earthtrends.wri.org/, which lists almost every conceivable useless and not so useless stat you can think of.
So, I have combined a number of stats together, and there are a number of NULLs as some countries have stats for a year where other countries don’t.
The typical sparse cube problem, not normally a problem.
However, based on how I had defined my underlying dataset, the NULLs were being represented as Zeroes when browsing the cube, as shown below.
This is normally not a big issue, however I want to use the LastNonempty Aggregatefunction on some of these measures. And as a result of the above, the LastNonEmpty was coming through as Zero incorrectly.
e.g. Afghanistan and Albania are NULL in the underlying SQL dataset, but become zeroes when browsing through Analysis Services. Also, the Total is coming through as Zero instead of the LastNonEmpty value as requested.
So, in comes the solution.
Each measure in a measure group has a NullProcessing option. Changing the value from Automatic to Preserve sorted out my issue.
Fixed, you’ll notice the Zero countries are no longer pulling through as Excel automatically hides NULL values.
Also, the Totals are now correctly reflecting the LastNonEmpty values.
I love how computers always do exactly what we ask them to.
G
gavin online 