Subselecting with MDX

Question

Greetings stack overflow community.

I've recently started building an OLAP cube in SSAS2008 and have gotten stuck. I would be grateful if someone could at least point me towards the right direction.

Situation: Two fact tables, same cube. FactCalls holds information about calls made by subscribers, FactTopups holds topup data. Both tables have numerous common dimensions one of them being the Subscriber dimension.

FactCalls             FactTopups
SubscriberKey      SubscriberKey
CallDuration         DateKey
CallCost               Topup Value ...

What I am trying to achieve is to be able to build FactCalls reports based on distinct subscribers that have topped up their accounts within the last 7 days.

What I am basically looking for an MDX equivalent to SQL's:

select *  
  from FactCalls  
 where SubscriberKey in 
       ( select distinct SubscriberKey from FactTopups where ... );  

I've tried creating a degenerate dimension for both tables containing SubscriberKey and doing:

Exist( 
         [Calls Degenerate].[Subscriber Key].Children, 
         [Topups Degenerate].[Subscriber Key].Children 
     )

Without success.

Kind regards,

Vince

Answer 1

You know how sometimes it's the simplest and most obvious solutions that somehow elude you? Well, this is apparently one of them. They say "MDX is not SQL" and I now know what they mean. I've been working at this from an entirely SQL point of view, completely overlooking the obvious use of the filter command.

with set [OnlyThoseWithTopupsInMarch2010] as
    filter( 
            [Subscriber].[Subscriber Key].Children, 
            ( [Measures].[Topups Count], 
              [Topup Date].[Calendar].[Month Name].&[2010]&[3] ) > 0 
          )

select non empty{
        [Measures].[Count],
        [Measures].[Cost],
        [Measures].[Topup Value]

      } on columns,
      non empty{ [Test] } on rows
 from [Calls] ; 

Embarrassingly simple.

Answer 2

You would probably find something like the following would perform better. The filter approach will be forced to iterate through each subscriber, while the NonEmpty() function can take advantage of optimizations in the storage engine.

select non empty{ 
        [Measures].[Count], 
        [Measures].[Cost], 
        [Measures].[Topup Value] 

      } on columns, 
      { 
NonEmtpy( [Subscriber].[Subscriber Key].Children,     
            ( [Measures].[Topups Count],     
              [Topup Date].[Calendar].[Month Name].&[2010]&[3] ) )
   } on rows 
 from [Calls] ;