Thomas.I Microsoft BI & Analytics

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

Division by null and zero in MDX

Posted by thomasivarssonmalmo on May 22, 2008

This is a task that I do frequently, check for null or zero in the denominator in calculations, to avoid the famous  1.#INF messages in the cells. Mosha has written a good blog post here about the subject. What I have done is to make an example from that blog post and the comments there to quickly understand when this problem occur and what to do about it. I do not add any new insights, only an MDX-Select for understanding the problem quickly.
 
Two of the calculated members, NullValuePerUnit and ZeroValuePerUnit, are only used for provoking the division by zero or null problem. The calculated member CheckDivByZero is the way Mosha recommends as a general solution that will work with bort null and zero in the denominator. You will also be able to see why the IsEmpty function will not work with zero in the denominator. The last two calculated members will show that dividing zero or null by a value(Internet Sales Amount) will not cause any problem and only return null.
 
WITH
MEMBER [Measures].[NullValuePerUnit] AS Null
MEMBER [Measures].[ZeroValuePerUnit] AS 0
MEMBER [Measures].[DivideByZero] As
[Measures].[Internet Sales Amount]/[Measures].[ZeroValuePerUnit] ,Format_String = ‘#.#0’
 
MEMBER [Measures].[DivideByNull] As
[Measures].[Internet Sales Amount]/[Measures].[NullValuePerUnit] ,Format_String = ‘#.#0’
 
MEMBER [Measures].[CheckDivByZero] AS IIF([Measures].[ZeroValuePerUnit] = 0, Null,
[Measures].[Internet Sales Amount]/[Measures].[ZeroValuePerUnit]),Format_String = ‘#.#0’
 
MEMBER [Measures].[CheckDivByNULL] AS IIF(IsEmpty([Measures].[NullValuePerUnit]), Null,
[Measures].[Internet Sales Amount]/[Measures].[ZeroValuePerUnit]),Format_String = ‘#.#0’
 
MEMBER [Measures].[CheckDivByNULLByZero] AS IIF(IsEmpty([Measures].[ZeroValuePerUnit]), Null,
[Measures].[Internet Sales Amount]/[Measures].[ZeroValuePerUnit]),Format_String = ‘#.#0’
 
MEMBER [Measures].[DivideZeroByNonZero] AS
[Measures].[ZeroValuePerUnit]/[Measures].[Internet Sales Amount], Format_String = ‘#.#0’
MEMBER [Measures].[DivideNullByNonZero] AS
[Measures].[NullValuePerUnit]/[Measures].[Internet Sales Amount], Format_String = ‘#.#0’
 
Select {[Measures].[Internet Sales Amount],
[Measures].[NullValuePerUnit],[Measures].[ZeroValuePerUnit], [Measures].[DivideByZero],
[Measures].[DivideByNull] ,[Measures].[CheckDivByZero],[Measures].[CheckDivByNULL],
[Measures].[CheckDivByNULLByZero],[Measures].[DivideZeroByNonZero],[Measures].[DivideNullByNonZero]} On Columns,
 [Customer].[Country].Children On Rows
From [Adventure Works]
Where([Date].[Calendar].[Calendar Year].&[2004]);
 
 
Run the MDX-Select in MDX-studio och Management Studio(SQL Server 2005)
Advertisements

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: