Exists v.s Existing and Auto Exist in MDX

Exists v.s Existing and Auto Exist in MDX

 

Let us Compare

Though Existsexisting 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 dimensions each then it will produce 16 members

It forces the natural hierarchy in  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 use When we want to display the measures based on two different dimension When we want to display the measures based on the members in different level from same hierarchy Achieve the same Auto exists results with out displaying second Dimension Forces 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] 

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] 

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] 


 

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]
 
 

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.

Let us find the count of subcategory first

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

 
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]

 
As you can see from the output though it has given the cross product, still it has the same 38 (37 rows of data + 1 header) rows in the output. normal behaviour should have produced 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]
 
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]

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]

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

Usual confusions

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

No, Actually one sets the context and 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]

The 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

This is True, 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

Exist–With Measures

Most of the examples around the Existing keyword talks about the effect of Exists on Dimension . But what will happen if i use it against measure

Will that make any difference?

Existing keyword set the current context on measure calculation which is really invaluable

Let us check the below query

with member SalesAmtAustraliaCanada as
Aggregate({[Geography].[Geography].[Country].&[Australia],
[Geography].[Geography].[Country].&[Canada]
}
,[Measures].[Reseller Sales Amount]
)
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada} on 0,
[Date].[Calendar].[Calendar Year].members on 1
from [Adventure Works]

image

As expected, the Calculated measure has summed up the sales related to Australia and Canada, which is way lower than total
amount against a year.

Let us execute the below query and get the sales against different countries

with member SalesAmtAustraliaCanada as
Aggregate({[Geography].[Geography].[Country].&[Australia],
[Geography].[Geography].[Country].&[Canada]
}
,[Measures].[Reseller Sales Amount]
)
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada} on 0,
[Geography].[Geography].[Country].members on 1
from [Adventure Works]
image

As you can see, it displays the summed value against each country and they are same. Though theoretically this is correct, we would ideally want to get the data only against Australia and Canada for this new measure.

So what is happening here?

Calculated members doesn’t get affected by the current query context . If we need the current context then we need existing keyword

with member SalesAmtAustraliaCanada as
Aggregate(existing {[Geography].[Geography].[Country].&[Australia],
[Geography].[Geography].[Country].&[Canada]
}
,[Measures].[Reseller Sales Amount]
)
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada} on 0,
[Geography].[Geography].[Country].members on 1
from [Adventure Works]

image


What will happen if we  change the query back to Date dimension?

with member SalesAmtAustraliaCanada as
Aggregate(existing {[Geography].[Geography].[Country].&[Australia],
[Geography].[Geography].[Country].&[Canada]
}
,[Measures].[Reseller Sales Amount]
)
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada} on 0,
[Date].[Calendar].[Calendar Year].members on 1
from [Adventure Works]

image

It produces the same result as above because in the above query the current context for Geography dimension is “All Geographies”

let us confirm this with below query

with member SalesAmtAustraliaCanada as
Aggregate(existing {[Geography].[Geography].[Country].&[Australia],
[Geography].[Geography].[Country].&[Canada]
}
,[Measures].[Reseller Sales Amount]
)
member Geographycurrenthier as
[Geography].[Geography].membervalue
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada,Geographycurrenthier} on 0,
[Date].[Calendar].[Calendar Year].members on 1
from [Adventure Works]

image

This is very useful technique for End user reporting.


 See Also





Sort by: Published Date | Most Recent | Most Useful
Comments
Page 1 of 1 (2 items)