July 14, 2013

Exist v.s Existing and Auto Exist in MDX – Part 2–Usual confusion

Please read the Part1 of the same series to get the continuity

Usual confusions

  • Exists and “Auto exists” are nearly same only difference is Exists function don’t display the second set .

Actually no. One set the context other doesn’t

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

Above query will produce no results because we are slicing by two different members(Bikes & Clothing) at the same time. But the the query allows you to use the same dimension hierarchy in both rows Axis and slicer axis.


But the below query will produce errors, because it sets the context


SELECT [Measures].[Reseller Sales Amount] ON 0      
, [Product].[Subcategory].[Subcategory].Members *
[Product].[Category].[Clothing]

on 1
FROM [Adventure Works]
where [Product].[Category].&[1]

image_thumb[22]



  • Existing keyword is just an extension of Exists, we can achieve the similar results with exist and currentmember function



Yes, But use of existing will improve the maintainability of code


Both the below codes will produce the same results


with member countofsubcategory as 
count( existing [Product].[Subcategory].[Subcategory].Members)
SELECT {[Measures].[Reseller Sales Amount], countofsubcategory} ON 0
, [Product].[Category].[Category].Members
on 1
from [Adventure Works]

with member countofsubcategory as
count( exists( [Product].[Subcategory].[Subcategory].Members
,[Product].[Category].currentmember ))
SELECT {[Measures].[Reseller Sales Amount], countofsubcategory} ON 0
, [Product].[Category].[Category].Members
on 1
from [Adventure Works]
image



  • DO we have not exists function ?



No. But this can be simulated with except function


The below query produces 8 rows of data


SELECT [Measures].[Reseller Sales Amount] ON 0      
, Exists( [Product].[Subcategory].[Subcategory].Members ,
[Product].[Category].[Clothing]
)

on 1
FROM [Adventure Works]
image 


With the except function in the below query it produces the opposite set with 30 rows 


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

image_thumb[24]

image_thumb[26]



  • Generate function forces it’s own context against existing keyword



Check the below query. It is clear that existing keyword uses the generate function’s context and that is why we got 3 subcategory against all category in the result set

with member countofsubcategory as 
Generate ([Product].[Category].[Bikes]
, count( existing [Product].[Subcategory].[Subcategory].Members)
)
SELECT {[Measures].[Reseller Sales Amount], countofsubcategory} ON 0
, [Product].[Category].[Category].Members
on 1
from [Adventure Works]

image_thumb[28]

 

This is an another proof that existing keyword is simply an extension of Exists function

Happy Reading Smile

No comments:

Post a Comment