Geeks With Blogs
James Rogers BI from the trenches...

 

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:
 
  1. The AGGREGATE member must be assigned to the Dimension.Hierarchy being detected by the EXISTING function in the calculated measure.
  1. 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.

 

Posted on Friday, June 11, 2010 9:43 PM Analysis Services | Back to top


Comments on this post: MDX using EXISTING, AGGREGATE, CROSSJOIN and WHERE

# re: MDX using EXISTING, AGGREGATE, CROSSJOIN and WHERE
Requesting Gravatar...
hmm... it's interesting
Left by bloger7791 on Feb 06, 2012 5:54 PM

# re: MDX using EXISTING, AGGREGATE, CROSSJOIN and WHERE
Requesting Gravatar...
Great post, thanks for the worked examples. I am trying to use a similar COUNT EXISTING on a project, but finding that EXCEL pivot tool is constructing a query that uses subqueries, and it appears that EXISTING cannot sniff out the context of the subquery.

See these Adventureworks 2012 examples (first one works, second one doesn't - note that Adventureworks 2012 only has weeks from 2005 to 2011 so might need to adjust the member references if you have a different version):

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].&[1]&[2011],
[Date].[Fiscal Weeks].[Fiscal Week].&[2]&[2011],
[Date].[Fiscal Weeks].[Fiscal Week].&[3]&[2011],
[Date].[Fiscal Weeks].[Fiscal Week].&[4]&[2011]})

GO

WITH
MEMBER [Week Count] AS
COUNT(EXISTING [Date].[Fiscal Weeks].[Fiscal Week].members )

SELECT
{[Week Count]} ON COLUMNS
FROM
( SELECT
{[Date].[Fiscal Weeks].[Fiscal Week].&[1]&[2011],
[Date].[Fiscal Weeks].[Fiscal Week].&[2]&[2011],
[Date].[Fiscal Weeks].[Fiscal Week].&[3]&[2011],
[Date].[Fiscal Weeks].[Fiscal Week].&[4]&[2011]}
ON COLUMNS
FROM [Adventure Works]
)



Left by Davos on Sep 23, 2013 11:58 AM

# re: MDX using EXISTING, AGGREGATE, CROSSJOIN and WHERE
Requesting Gravatar...
With this query:
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]

If we remove [Date].[Fiscal Weeks].[CM] from Where clause and pit it in Axis 1, it does not calculate it correctly.
Do you know why Existing function cannot realise?

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].&[3]&[2006],
[Date].[Fiscal Weeks].[Fiscal Week].&[48]&[2005],
[Date].[Fiscal Weeks].[Fiscal Week].&[6]&[2006],
[Date].[Fiscal Weeks].[Fiscal Week].&[5]&[2006]})

select

{[Week Count]} on 0,

{[Date].[Fiscal Weeks].[CM]} on 1
from
[Adventure Works]
Left by NimZy on Sep 17, 2015 4:56 PM

Your comment:
 (will show your gravatar)


Copyright © James Rogers | Powered by: GeeksWithBlogs.net