MDX using EXISTING, AGGREGATE, CROSSJOIN and WHERE
- by James Rogers
It is a well-published approach to using the EXISTING function to decode AGGREGATE members and nested sub-query filters. Mosha wrote a good blog on it here and a more recent one here. The use of EXISTING in these scenarios is very useful and sometimes the only option when dealing with multi-select filters. However, there are some limitations I have run across when using the EXISTING function against an AGGREGATE member:
The AGGREGATE member must be assigned to the Dimension.Hierarchy being detected by the EXISTING function in the calculated measure.
The AGGREGATE member cannot contain a crossjoin from any other dimension or hierarchy or EXISTING will not be able to detect the members in the AGGREGATE member.
Take the following query (from Adventure Works DW 2008):
With
member [Week Count] as 'count(existing([Date].[Fiscal Weeks].[Fiscal Week].members))'
member [Date].[Fiscal Weeks].[CM] as 'AGGREGATE({[Date].[Fiscal Weeks].[Fiscal Week].&[47]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[48]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[49]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[50]&[2004]})'
select
{[Week Count]} on columns
from
[Adventure Works]
where
[Date].[Fiscal Weeks].[CM]
Here we are attempting to count the existing fiscal weeks in slicer. This is useful to get a per-week average for another member. Many applications generate queries in this manner (such as Oracle OBIEE). This query returns the correct result of (4) weeks.
Now let's put a twist in it. What if the querying application submits the query in the following manner:
With
member [Week Count] as 'count(existing([Date].[Fiscal Weeks].[Fiscal Week].members))'
member [Customer].[Customer Geography].[CM] as 'AGGREGATE({[Date].[Fiscal Weeks].[Fiscal Week].&[47]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[48]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[49]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[50]&[2004]})'
select
{[Week Count]} on columns
from
[Adventure Works]
where
[Customer].[Customer Geography].[CM]
Here we are attempting to count the existing fiscal weeks in slicer. However, the AGGREGATE member is built on a different dimension (in name) than the one EXISTING is trying to detect. In this case the query returns (174) which is the total number of [Date].[Fiscal Weeks].[Fiscal Week].members defined in the dimension.
Now another twist, the AGGREGATE member will be named appropriately and contain the hierarchy we are trying to detect with EXISTING but it will be cross-joined with another hierarchy:
With
member [Week Count] as 'count(existing([Date].[Fiscal Weeks].[Fiscal Week].members))'
member [Date].[Fiscal Weeks].[CM] as 'AGGREGATE({[Date].[Fiscal Weeks].[Fiscal Week].&[47]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[48]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[49]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[50]&[2004]}*
{[Customer].[Customer Geography].[Country].&[Australia],[Customer].[Customer Geography].[Country].&[United States]})'
select
{[Week Count]} on columns
from
[Adventure Works]
where
[Date].[Fiscal Weeks].[CM]
Once again, we are attempting to count the existing fiscal weeks in slicer. Again, in this case the query returns (174) which is the total number of [Date].[Fiscal Weeks].[Fiscal Week].members defined in the dimension. However, in 2008 R2 this query returns the correct result of 4 and additionally , the following will return the count of existing countries as well (2):
With
member [Week Count] as 'count(existing([Date].[Fiscal Weeks].[Fiscal Week].members))'
member [Country Count] as 'count(existing([Customer].[Customer Geography].[Country].members))'
member [Date].[Fiscal Weeks].[CM] as 'AGGREGATE({[Date].[Fiscal Weeks].[Fiscal Week].&[47]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[48]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[49]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[50]&[2004]}*
{[Customer].[Customer Geography].[Country].&[Australia],[Customer].[Customer Geography].[Country].&[United States]})'
select
{[Week Count]} on columns
from
[Adventure Works]
where
[Date].[Fiscal Weeks].[CM]
2008 R2 seems to work as long as the AGGREGATE member is on at least one of the hierarchies attempting to be detected (i.e. [Date].[Fiscal Weeks] or [Customer].[Customer Geography]). If not, it seems that the engine cannot find a "point of entry" into the aggregate member and ignores it for calculated members.
One way around this would be to put the sets from the AGGREGATE member explicitly in the WHERE clause (slicer). I realize this is only supported in SSAS 2005 and 2008. However, after talking with Chris Webb (his blog is here and I highly recommend following his efforts and musings) it is a far more efficient way to filter/slice a query:
With
member [Week Count] as 'count(existing([Date].[Fiscal Weeks].[Fiscal Week].members))'
select
{[Week Count]} on columns
from
[Adventure Works]
where
({[Date].[Fiscal Weeks].[Fiscal Week].&[47]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[48]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[49]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[50]&[2004]}
,{[Customer].[Customer Geography].[Country].&[Australia],[Customer].[Customer Geography].[Country].&[United States]})
This query returns the correct result of (4) weeks. Additionally, we can count the cross-join members of the two hierarchies in the slicer:
With
member [Week Count] as 'count(existing([Date].[Fiscal Weeks].[Fiscal Week].members)*existing([Customer].[Customer Geography].[Country].members))'
select
{[Week Count]} on columns
from
[Adventure Works]
where
({[Date].[Fiscal Weeks].[Fiscal Week].&[47]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[48]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[49]&[2004],[Date].[Fiscal Weeks].[Fiscal Week].&[50]&[2004]}
,{[Customer].[Customer Geography].[Country].&[Australia],[Customer].[Customer Geography].[Country].&[United States]})
We get the correct number of (8) here.
