SSDT Query Builder: MDX Query returning NULL instead of AllMembers

  • Article

 

*SSDT – SQL Server Data Tools

*SSMS – SQL Server Management Studio

In SSDT Query Designer getting Null for All members:

One of my customers recently reported this issue so thought of sharing with world……

Demo given below will provide an insight……………

Simple query, where we are doing cross join of allmembers from Product and Date dimension

select non empty ([Product].[Category].allmembers*[Date].[Calendar].[Calendar Year].allmembers ) on 1,

([Measures].[Internet Sales Amount]) on 0

from [Adventure Works]

Output in SSMS: All Products visible from SSMS

clip_image001

In SQL 2012 SSDT we have an option of writing MDX queries directly in SSAS Project.

Pretty simple if you haven’t used it…here you will find Query Writer option in same lines you will find in SSRS Project

For opening Query windows in SSDT, open your SSAS database in SSDT and right click on a cube  -> Browse this will open your Browse Window ( you can get the same windows in SSMS as OWC is deprecated)  and click on design mode button which is highlighted below.

image

Once opened execute the same query and you will find All Products which were showing earlier will turned to NULL – now question is why?

Output in SSDT, if you do comparison with SSMS Query Windows and this, you will find “All Product” are showing as NULL. image

Explanation goes with Format of Query when its executed from SSDT Design Mode or SSRS  Vs. in Query Windows of SSMS

Pulled this query from Profiler Trace and the difference you will find in format, in case of SSMS its Native and in-case of SSDT / SSRS its Tabular.

Now what’s tabular format, its basically flattening multi-dimensional result to tabular using Flattening as explained in this article - Flattening Algorithm

Important thing to keep in mind is “If a hierarchy has an ALL member and that member appears on an axis, this is represented in the flattened rowset by a row that has a NULL in every column that corresponds to a level below it”

SSMS:

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">

<Format>Native</Format>

</PropertyList>

SSDT / SSRS

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">

    <Format>Tabular</Format>

</PropertyList>

“Another behavior of the flattening algorithm is that the "[(ALL)]" level is not included in the dataset. This has a couple of implications. The first is that if you want to include data from the "All" member of a dimension, you'll need to create a calculated member to represent this member as shown below:

CREATE MEMBER CURRENTCUBE.[Product].[Category].[All Products].[All Product]
AS aggregate([Product].[Product].members),
VISIBLE = 1  ;  

After creating this I had re-executed the query and here is the output with Calc Members

image