MS SQL Server OLAP - Urgent: Using same measure twice in a MDX but based on 2 different Dimensions

Author

Urgent: Using same measure twice in a MDX but based on 2 different Dimensions

Sumit Pilankar

2005-11-23, 7:24 am

Hi,

I am currently working on a project which involves cubes and MDX and
displaying reports using SQL Reporting Services. The project is using Yukon
(SQL 2005) Server RC1 build and Business Intelligence Studio 2005.

I am stuck up with a problem, which i guess should be satisified by MDX but
dont know the actual way to do it. I have tried many of ways, please let me
know if its possible and is yes, please tell me how.

In my fact table, I have userid, signupdate and deletedate columns. The last
2 contain DateID mapped to Date Dim Table, which contains corresponding
DateIDs and actual Dates.

In the cube, i have a measure 'User Fact' which is count of rows in Fact
table. Also, i have 2 Date Dimensions as 'Signup Date Dim' and 'Delete Date
Dim'.

Now, if i choose 'User Fact' on columns and 'Signup Date Dim' on rows, i get
the # of signups on the Date. Similarly, i get # of deletions from 'User
Fact' on columns and 'Delete Date Dim'.

Signup Date User Fact (Signups)
1/1/05 3
2/1/05 4

Deletion Date User Fact (Deletions)
1/1/05 2
2/1/05 1

But, i need a output, where i have

Date Signups Deletions
1/1/05 3 2
2/1/05 4 1

I m not sure how to combine the dimensions and display a common report.

I am using Yukon server and SQL 2005 Reporting Services for report display,
so if there is any way i can combine the 2 datasets in reporting services,
so that i can create 2 different datasets for signups and deletions and
combine it into single dataset using reporting services.

Please help me out with this.
Thanks.
Sumit Pilankar

Deepak Puri

2005-11-23, 8:24 pm

Assume that there are equivalent [Date] hierarchies for both the [Signup
Date] and [Deletion Date] dimensions. Either hierarchy could be used for
the report, or a 3rd [Report Date] dimension could be added just for
reporting. If the [Signup Date].[Date] hierarchy is used for the report,
calculated measures for Signups and Deletions can be created within the
MDX query:
[color=darkred]
With
Member [Measures].[Signups] as
'([Measures].[User Fact],
[Deletion Date].[Date].DefaultMember)'
Member [Measures].[Deletions] as
'([Measures].[User Fact],
[Signup Date].[Date].DefaultMember,
LinkMember([Signup Date].[Date].CurrentMember,
[Deletion Date].[Date]))'

Select {[Measures].[Signups],
[Measures].[Deletions]} on columns,
[Signup Date].[Date].[Day].Members on rows
from & #91;UserCube][color=
darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Sumit Pilankar

2005-11-24, 3:23 am

Thanks Deepak.

The query worked perfectly as expected. Thank you very much.:)

Sumit Pilankar

"Deepak Puri" < deepak_puri@progress
ive.com> wrote in message
news:OPahmlI8FHA.3416@TK2MSFTNGP15.phx.gbl...
> Assume that there are equivalent [Date] hierarchies for both the [Signup
> Date] and [Deletion Date] dimensions. Either hierarchy could be used for
> the report, or a 3rd [Report Date] dimension could be added just for
> reporting. If the [Signup Date].[Date] hierarchy is used for the report,
> calculated measures for Signups and Deletions can be created within the
> MDX query:
>
> With
> Member [Measures].[Signups] as
> '([Measures].[User Fact],
> [Deletion Date].[Date].DefaultMember)'
> Member [Measures].[Deletions] as
> '([Measures].[User Fact],
> [Signup Date].[Date].DefaultMember,
> LinkMember([Signup Date].[Date].CurrentMember,
> [Deletion Date].[Date]))'
>
> Select {[Measures].[Signups],
> [Measures].[Deletions]} on columns,
> [Signup Date].[Date].[Day].Members on rows
> from [UserCube]
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.droptable.com ***

Sumit Pilankar

2005-11-24, 9:23 am

Hi Deepak,

Thanks for your help. I didnt knew about linkmember function, but thanks to
you now i know.

But i found a wierd thing about the linkmember function, when i use it at
the first level of hierarchy i.e. Year, it works perfectly, but at the level
of Quarter, the results returned are wrong. For verification, i tried this
and the results were strange.

with
member [Measures].[Delete] as 'LinkMember([Signup Date Dim].[Date
Hierarchy].CURRENTMEMBER,[Delete Date Dim].[Date Hierarchy]).UNIQUENAME'
member [Measures].[Signup] as '[Signup Date Dim].[Date
Hierarchy].CurrentMember.UNIQUENAME'

select
{
[Measures].[Signup],
[Measures].[Delete]
} on columns,
( [Signup Date Dim].[Date Hierarchy].[Year].&[1988]:[Signup Date
Dim].[Date Hierarchy].[Year].&[1990] ) on rows
from [User Analytics]

Result:
Signup
Delete
1988 [Signup Date Dim].[Date Hierarchy].[Year].&[1988]
[Delete Date Dim].[Date Hierarchy].[Year].&[1988]
1989 [Signup Date Dim].[Date Hierarchy].[Year].&[1989]
[Delete Date Dim].[Date Hierarchy].[Year].&[1989]
1990 [Signup Date Dim].[Date Hierarchy].[Year].&[1990]
[Delete Date Dim].[Date Hierarchy].[Year].&[1990]

But when i fire the same query for quarter, the results returned are not as
expected.

with
member [Measures].[Delete] as 'LinkMember([Signup Date Dim].[Date
Hierarchy].CURRENTMEMBER,[Delete Date Dim].[Date Hierarchy]).UNIQUENAME'
member [Measures].[Signup] as '[Signup Date Dim].[Date
Hierarchy].CurrentMember.UNIQUENAME'

select
{
[Measures].[Signup],
[Measures].[Delete]
} on columns,
( [Signup Date Dim].[Date Hierarchy].[Year].&[1988].&[1]:[Signup Date
Dim].[Date Hierarchy].[Year].&[1990].&[4] ) on rows
from [User Analytics]

Result:

Signup
Delete
1 [Signup Date Dim].[Date Hierarchy].[Year].&[1988].&[1]
[Delete Date Dim].[Date Hierarchy].[Year].&[1900].&[1]
2 [Signup Date Dim].[Date Hierarchy].[Year].&[1988].&[2]
[Delete Date Dim].[Date Hierarchy].[Year].&[1900].&[2]
3 [Signup Date Dim].[Date Hierarchy].[Year].&[1988].&[3]
[Delete Date Dim].[Date Hierarchy].[Year].&[1900].&[3]
4 [Signup Date Dim].[Date Hierarchy].[Year].&[1988].&[4]
[Delete Date Dim].[Date Hierarchy].[Year].&[1900].&[4]
1 [Signup Date Dim].[Date Hierarchy].[Year].&[1989].&[1]
[Delete Date Dim].[Date Hierarchy].[Year].&[1900].&[1]
2 [Signup Date Dim].[Date Hierarchy].[Year].&[1989].&[2]
[Delete Date Dim].[Date Hierarchy].[Year].&[1900].&[2]
3 [Signup Date Dim].[Date Hierarchy].[Year].&[1989].&[3]
[Delete Date Dim].[Date Hierarchy].[Year].&[1900].&[3]
4 [Signup Date Dim].[Date Hierarchy].[Year].&[1989].&[4]
[Delete Date Dim].[Date Hierarchy].[Year].&[1900].&[4]
1 [Signup Date Dim].[Date Hierarchy].[Year].&[1990].&[1]
[Delete Date Dim].[Date Hierarchy].[Year].&[1900].&[1]
2 [Signup Date Dim].[Date Hierarchy].[Year].&[1990].&[2]
[Delete Date Dim].[Date Hierarchy].[Year].&[1900].&[2]
3 [Signup Date Dim].[Date Hierarchy].[Year].&[1990].&[3]
[Delete Date Dim].[Date Hierarchy].[Year].&[1900].&[3]
4 [Signup Date Dim].[Date Hierarchy].[Year].&[1990].&[4]
[Delete Date Dim].[Date Hierarchy].[Year].&[1900].&[4]

Can you please help me out?

Thanks in advance.
Sumit Pilankar

"Deepak Puri" < deepak_puri@progress
ive.com> wrote in message
news:OPahmlI8FHA.3416@TK2MSFTNGP15.phx.gbl...
> Assume that there are equivalent [Date] hierarchies for both the [Signup
> Date] and [Deletion Date] dimensions. Either hierarchy could be used for
> the report, or a 3rd [Report Date] dimension could be added just for
> reporting. If the [Signup Date].[Date] hierarchy is used for the report,
> calculated measures for Signups and Deletions can be created within the
> MDX query:
>
> With
> Member [Measures].[Signups] as
> '([Measures].[User Fact],
> [Deletion Date].[Date].DefaultMember)'
> Member [Measures].[Deletions] as
> '([Measures].[User Fact],
> [Signup Date].[Date].DefaultMember,
> LinkMember([Signup Date].[Date].CurrentMember,
> [Deletion Date].[Date]))'
>
> Select {[Measures].[Signups],
> [Measures].[Deletions]} on columns,
> [Signup Date].[Date].[Day].Members on rows
> from [UserCube]
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.droptable.com ***

Sponsored Links