Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Null processing of measures in SSAS2005

Posted by thomasivarssonmalmo on June 27, 2008

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)
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’)
Update TimeDim1
 Set MonthNo = Month(FullDate),
 MonthNameT = Left(DateName(mm,FullDate),3),
 YearNo = Year(FullDate)
Create Table FactsWithNull
( FullDate datetime,
 SalesValue int,
 SalesQty int)
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.
 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.

2 Responses to “Null processing of measures in SSAS2005”

  1. Vidas said

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

  2. Dan said

    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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: