July 7, 2013

Exist v.s Existing and Auto Exist in MDX – Part 1

Though Exist, existing and Auto exist are conceptually very simple, they are the root cause for many confusion in the complex MDX queries. So let us do a simple comparisons

 

Normal Behaviour

Auto Exists

Exists

Existing
Theory

When the two sets are cross joined it will produce all possible combination. E.g. if we join two sets with 4 dimension each then it will produce 16 members

It forces the natural hierarchy in the below Scenarios


When two sets containing members of the same dimension are cross-joined, the resulting set is limited to those combinations of members actually observed in that dimension.
When the WHERE
clause contains a member of a dimension, sets along the axes containing members from that same dimension are limited as well.

It forces the natural hierarchy with out returning the forcing/second set

Auto exist is not applicable to calculated members and if we want to force this behaviour then we need existing keyword

When to useWhen we want to display the measures based on two different dimensionWhen we want to display the measures based on the members in different level from same hierarchyAchieve the same Auto exists results with out displaying second DimensionForces the local context in the calculated members

Normal Behaviour

Run the below query

SELECT [Measures].[Reseller Sales Amount] ON 0
, [Product].[Category].[Category].Members on 1
from [Adventure Works]

image

Based on the above query we have 4 Categories in the Product Categories Hierarchy

SELECT [Measures].[Reseller Sales Amount] ON 0
, [Geography].[Geography].[Country].members
on 1
from [Adventure Works]
image

Based on the above query we have 6 countries under geography hierarchy

Let us Cross Join it

SELECT [Measures].[Reseller Sales Amount] ON 0
,[Product].[Category].[Category].Members
* {[Geography].[Geography].[Country].Members
} on 1
from [Adventure Works]

image

image

So along with the header row it has returned 25 rows which is direct cross product of 4 Products and 6 countries (24 data row + 1 Header row)

What other ways to achieve the cross join

You can use the cross join function as shown below

SELECT [Measures].[Reseller Sales Amount] ON 0
,crossjoin ([Product].[Category].[Category].Members
, [Geography].[Geography].[Country].Members
) on 1
from [Adventure Works]

Even if you remove the Crossjoin keyword in the above query,  it will still produce the same result .

 
SELECT [Measures].[Reseller Sales Amount] ON 0
      ,([Product].[Category].[Category].Members
, [Geography].[Geography].[Country].Members
         ) on 1
from [Adventure Works]
image
 The reason is MDX will try to resolve the other dimensions of the cube for every combination (remember the partial tuple law)and will get you the cross join.

Auto Exists

In the above example we have used the cross join between two different hierarchies (Product.category and geography.country). let us join between category and sub category from same product hierarchy.

Step 1: Let us find the count of subcategory first

SELECT [Measures].[Reseller Sales Amount] ON 0
, [Product].[SubCategory].[SubCategory].Members on 1
from [Adventure Works]
image
 Along with header row we have 38 subcategories, let us cross join subcategory and category.

 

SELECT [Measures].[Reseller Sales Amount] ON 0
, [Product].[Category].[Category].Members *
[Product].[Subcategory].[Subcategory].Members on 1
from [Adventure Works]
image
image

As you can see from the output though it has given the cross product, it still only has the same 38 (37 rows of data + 1 header) rows in the output. as per the normal behaviour it should be 4*38 = 152

The reason is, based on the Product category hierarchy, only valid category and Subcategory combinations are produced. 

 

What is Existing 

Let us create a calculated member in the above query
with member countofsubcategory as 
count( [Product].[Subcategory].[Subcategory].Members)
SELECT {[Measures].[Reseller Sales Amount], countofsubcategory} ON 0
, [Product].[Category].[Category].Members *
[Product].[Subcategory].[Subcategory].Members
on 1
from [Adventure Works]
image 
Though we are displaying each subcategory in the list, still we got the total count . This is because Auto exist doesn’t control the calculated members  
 
So by inserting Existing keyword in the above query,it will produce the expected result which 1 per subcategory
with member countofsubcategory as 
count( existing [Product].[Subcategory].[Subcategory].Members)
SELECT {[Measures].[Reseller Sales Amount], countofsubcategory} ON 0
, [Product].[Category].[Category].Members *
[Product].[Subcategory].[Subcategory].Members
on 1
from [Adventure Works]
image

 

What is Exists function ?

Exists uses the “Auto exist” functionality but avoid displaying the “second set” in the results

SELECT [Measures].[Reseller Sales Amount] ON 0      
, Exists( [Product].[Subcategory].[Subcategory].Members ,
[Product].[Category].[Clothing]
)
on 1
FROM [Adventure Works]

image

It only displays the subcategories which are related to Clothing category without displaying category in the result.

No comments:

Post a Comment