Null processing of measures in SSAS2005

A few days ago a question regarding null processing of fact records was posted on the SSAS forum. It was also about nulls in dimension tables but let us focus on what to do with empty measures in a fact record. Should you put zero in them or allow null? I answered that post with the assumption that you should never feed a cube with zeros because it will make the NON EMPTY keyword useless in MDX Selects. Still I never checked the options in SSAS 2005 so this post is the outcome of the need to check this more carefully.
 
I recommend you to think twice about your fact table design because if you have null or zeros for measures you might have designed it wrong. If not you can continue with this technical solution that SSAS 2005 offers.
 
We will need to build a simple cube to understand how this works. Run the following TSQL script in management studio in order to create a time dimension and a simple fact table.
———————————————
Create Table TimeDim1
( FullDate datetime,
  MonthNo int,
  MonthNameT Char(3),
  YearNo Int)
Go
 
Insert Into TimeDim1 (FullDate) Values (’2008-01-01′)
Insert Into TimeDim1 (FullDate) Values (’2008-02-01′)
Insert Into TimeDim1 (FullDate) Values (’2008-03-01′)
Insert Into TimeDim1 (FullDate) Values (’2008-04-01′)
Insert Into TimeDim1 (FullDate) Values (’2008-05-01′)
Insert Into TimeDim1 (FullDate) Values (’2008-06-01′)
Go
 
Update TimeDim1
 Set MonthNo = Month(FullDate),
 MonthNameT = Left(DateName(mm,FullDate),3),
 YearNo = Year(FullDate)
Go
 
Create Table FactsWithNull
( FullDate datetime,
 SalesValue int,
 SalesQty int)
Go
 
Insert Into FactsWithNull (FullDate, SalesValue, SalesQty) Values(’2008-01-01′, 100, 1)
Insert Into FactsWithNull (FullDate, SalesValue, SalesQty) Values(’2008-02-01′, Null, Null)
Insert Into FactsWithNull (FullDate, SalesValue, SalesQty) Values(’2008-03-01′, 100, 1)
Insert Into FactsWithNull (FullDate, SalesValue, SalesQty) Values(’2008-04-01′, 100, 1)
Insert Into FactsWithNull (FullDate, SalesValue, SalesQty) Values(’2008-05-01′, 100, 1)
Insert Into FactsWithNull (FullDate, SalesValue, SalesQty) Values(’2008-06-01′, 0, 0)
—————————————–
 
When you run select * on the fact table you will have february 2008 with two null measures and june 2008 with two zero measures.
 
Copy of The fact table
 
Next, you build the simple cube with one time dimension like this.
 
The cube Project
 
 Now it is time to run the test on the two measures in the cube. The nullprocessing property of a measure is placed under the source property in the cube structure pane in BIDS. The settings available are automatic, preserve, error and zeroOrblank. I will test all except for error.
 
NullprocessingProperty
 
 The first case is NullProcessing = Automatic. This is also the deafult. You will see a result like this, below, in ProClarity or the cube browser in BIDS. The Null measures will be converted to zeros. If you add NON EMPTY on columns, february 2008 will still be seen. This is also the outcome of NullProcessing = ZeroOrBlank.
 
ProClarity Null processing automatic
 
The next case is Nullprocessing = Preserve. In this case, Nulls will be kept and presented as empty cells and when you apply NON EMPTY on columns the empty month, february 2008, will be removed.
 
ProClarity Null Preserve
 
Conclusion: I still do not see any reason for entering zero in empty measures because you have setting in SSAS 2005 that can convert null to zero. If you would like to use the NON EMPTY keyword, to remove empty rows or columns, you should keep the nulls with Preserve.
 
Edit: This geeky blog reached 20 000 hits this week. Next time i mention this will be when i reach 50 000 hits.
 
About these ads

2 Comments

  1. Vidas

    Link to this post was added to our website in the [SSAS Articles]/[Design] section: NULL Processing of measures in SSAS2005.

  2. Dan

    why is Preserve not the default? is this just a compatibility thing with 2000? seems like this should be changed and default should be switched from Automatic to Preserve.

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: