| Sumit Pilankar 2005-11-16, 9:23 am |
| 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(deletedcoun
t,0) as
Deletions, (Coalesce(signupcoun
t,0)- Coalesce(deletedcoun
t,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
|