MDX: Retrieving same Measure based on 2 Different Dimensions in same query
- From: "Sumit Pilankar" <sumit.pilankar@xxxxxxxxx>
- Date: Wed, 16 Nov 2005 19:36:01 +0530
Hi,
I am new to MDX and is stuck up on a problem which i feel is quite common.
We have a Cube named 'User'
Measures for the cube:
1.. M1
2.. M2
Dimensions for the cube:
1.. D1
2.. D2
Both D1 and D2 have been created as named queries from [Date Dim] table and
hence have the same number of entries and hierarchy.
Requirement:
We need to join A & B based on the equality of D1's member value and D2's
member value.
A) Select
{ [Measures].[M1], [Measures].[M2] } ON COLUMNS,
{ [D1].Members} ON ROWS
FROM [User]
B) Select
{ [Measures].[M1], [Measures].[M2] } ON COLUMNS,
{ [D2].Members } ON ROWS
FROM [User]
The corresponding SQL query which we are trying to simulate using MDX is
shown below:
Select Coalesce(a.si_SignupDateID, b.si_DeleteDateID) as Date,
Coalesce(signupcount,0) as Registrations, Coalesce(deletedcount,0) as
Deletions, (Coalesce(signupcount,0)-Coalesce(deletedcount,0)) as [Net
Change]
from
(
Select si_SignupDateID, count(*) as signupcount
from UserFact
group by si_SignupDateID
) as a full outer join
(Select si_DeleteDateID, count(*) as deletedcount
from UserFact
group by si_DeleteDateID
) as b
on a.si_SignupDateID = b.si_DeleteDateID
order by Date
Thanks for reading the message
Sumit Pilankar
.
- Prev by Date: Re: 2 million records
- Next by Date: Re: Recency, time since last sale
- Previous by thread: 2 million records
- Next by thread: Re: what should be in Dimension table?
- Index(es):
Relevant Pages
|
Loading
