Null values in my measures - AS2005

Let me first preface my question with the fact that I am new to SQL Server applications in general. Therefore, any answer will probably require additional information as to where I need to make the changes within Analysis Services (AS). Thanks.

My cube retrieves measures from multiple fact tables. It uses a simple time dimension which contains only Year.

When I view my cube in the cube browser, one measure show nulls for certain Years. I want to know how to convert null values to zero, preferably at the cube level so it would apply to all measures. But if that is not possible then at the measure level.

I understand why the null values are appearing. It is because the primary key from the time dimension, Year, does not have a corresponding foreign key in the fact table. Since I'm pulling measures from other fact tables, a row will be created in the cube for the Year because other fact tables will have a matching foreign key for the Year.

Ex:

Year Measure 1 Measure 2 Measure 3

1994 null 10.00 -121.03

1995 null 13.30 100.00

1996 5,000.00 180.00 45.00

I've tried setting the NullsProcessing property on the measure to 'Zero or Blank'. Also tried setting the NullsProcessing to 'Zero or Blank' on the Measure Group within the relationship on the Dimension Usage tab. None of which resulted in the null value being converted to zero.

Eventually I resolved the problem on the Reporting Services side of my application but ideally I'd like to resolve it on the Analysis Services side.

Any advice is greatly appreciated!

Thanks.

Nolte

Nolte,

You could use the cube script to supply a value for null measures in your cube using the CoalesceEmpty function. Open up the cube editor in the Business Intelligence Development Studio and click on the "Calculations" tab. Then click on the "New Script Command" button which is on the tool bar just to the left of the big "X" for delete. Paste in the following to set NULL values to be 0 for your "Measure 1". Save the cube and then deploy the project if necessary. You should now see "0" instead of NULL when you browse the cube.

Scope(Measures.[Measure 1]);

This = CoalesceEmpty(Measures.[Measure 1],0);

End Scope;

HTH,

- Steve

|||Nice trick...just what I needed! Steve Rocks!