Retrieving same Measure based on 2 Different Dimen

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Analysis Services (2000)

Retrieving same Measure based on 2 Different Dimen

Author

Topic

sumit_pilankar
Starting Member

2 Posts

Posted - 2005-11-17 : 01:51:57

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
Sumit Pilankar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources