Thomas.I Microsoft BI & Analytics

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

Empty values in MDX

Posted by thomasivarssonmalmo on March 8, 2009

Empty values in an SSAS cube can sometimes be valuable information but according to my experience, but in most scenarios, you would like to somethng about them. The scenarios I will discuss assumes that you do not have entered zeros for non existing measures in the source fact table.
 
Let us start with the first query:
 
Select Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]) On 0,
CrossJoin(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Customer]),
Descendants([Product].[Product Categories],[Product].[Product Categories].[Product])) On 1
From [Adventure Works]
Where([Customer].[State-Province].&[FL]&[US],[Measures].[Internet Sales Amount])
 
It will show customers in Florida(USA) and what they have bought during the calendar years.
 
 
The first obvious tool in the MDX toolbox is to use the NON EMPTY key word to remove empty rows and columns from the result.
 
 
So far nothing new. Most client tools use the NON EMPTY option to remove empty rows and columns.
 
Update: I forgot the NonEmpty function. Run this MDX and it will give you the same result as in the previous picture, but probably much faster.
 
Select  NON EMPTY Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]) On 0,
NONEMPTY(CrossJoin(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Customer]),
Descendants([Product].[Product Categories],[Product].[Product Categories].[Product])),
[Measures].[Internet Sales Amount]) On 1
From [Adventure Works]
Where([Customer].[State-Province].&[FL]&[US],[Measures].[Internet Sales Amount]);
 
Lets have a look at some other MDX keywords and functions that can be useful. What can the EXISTS function do? Run the MDX below and have a look at the result.
 
Select  NON EMPTY Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]) On 0,
EXISTS(CrossJoin(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Customer]),
Descendants([Product].[Product Categories],[Product].[Product Categories].[Product])),
[Measures].[Internet Sales Amount]) On 1
From [Adventure Works]
Where([Customer].[State-Province].&[FL]&[US],[Measures].[Internet Sales Amount]);
 
 
 
The empty rows appears again but that is because EXISTS will use the members from the first set, Customers in Florida, that have bought products but only show the members from the Customers(in Florida) dimension. The products these customers have bought will not be shown.
 
Using the EXISTING key word will return the same result as EXIST.
 
Select  NON EMPTY Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]) On 0,
EXISTING CrossJoin(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Customer]),
Descendants([Product].[Product Categories],[Product].[Product Categories].[Product])) On 1
From [Adventure Works]
Where([Customer].[State-Province].&[FL]&[US],[Measures].[Internet Sales Amount]);
 
Another option is to use the FILTER function. It will return the same result as the NON EMPTY key word.
 
Select NON EMPTY Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]) On 0,
Filter(CrossJoin(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Customer]),
Descendants([Product].[Product Categories],[Product].[Product Categories].[Product])),
[Measures].[Internet Sales Amount] > 0) On 1
From [Adventure Works]
Where([Customer].[State-Province].&[FL]&[US],[Measures].[Internet Sales Amount]);
 
Finally you can see what  the COALESCEEMPTY function can do with null values. This function do not remove nulls or zeros but change their values.
 
WITH Member Measures.InternetSalesX as
CoalesceEmpty([Measures].[Internet Sales Amount],"x")
Select Descendants([Date].[Calendar],[Date].[Calendar].[Calendar Year]) On 0,
CrossJoin(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Customer]),
Descendants([Product].[Product Categories],[Product].[Product Categories].[Product])) On 1
From [Adventure Works]
Where([Customer].[State-Province].&[FL]&[US],Measures.InternetSalesX)
 
 
These examples have been written without paying any attention to performance issues. They are simple examples of how to change or remove empty values from a MDX query.
 
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: