The lesser-known NullProcessingOption

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.

image

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.

image 

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.

image

I love how computers always do exactly what we ask them to.

G

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: