Subselecting with MDX
- by Vince
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