MS SQL Server OLAP - Problem with LinkMember Function

Author

Problem with LinkMember Function

Sumit Pilankar

2005-11-25, 3:23 am

Hi,

As suggested by Deepak, i am now using LinkMember() for linking members of 2 different Dimensions. Thanks Deepak for that.

But, while trying some queries with linkmember(), i found a weird result.
[color=darkred]
Query:
with
member [measures].[linkmember] as 'LinkMember([Signup Date Dim].[Date Hierarchy].&[2000],[Delete Date Dim].[Date Hierarchy]).UNIQUENAME'
select
[Measures].[linkmember] on columns
from [Cube]

Result:
linkmember
[Delete Date Dim].[Date Hierarchy].[Year].&& #91;2000][color=dark
red]

This is perfect, but when i run the same query at a lower level of the heirarchy ie. at Quarter Level, the result returned is wrong.
[color=darkred]
Query:

with
member [measures].[linkmember] as 'LinkMember([Signup Date Dim].[Date Hierarchy].&[2000].&[3],[Delete Date Dim].[Date Hierarchy]).UNIQUENAME'
select
[Measures].[linkmember] on columns
from [User Analytics]

Result:
linkmember
[Delete Date Dim].[Date Hierarchy].[Year].&[1900].&& #91;3][color=darkred
]

My expected result was '[Delete Date Dim].[Date Hierarchy].[Year].&[2000].&[3]', though the quarter value ie. 3 returned it right, it always returns 1900 ie. First year from the heirarchy and not 2000 as expected.

Can anyone please tell me where am I making a mistake in the query?
Thanks
Sumit Pilankar

Deepak Puri

2005-11-25, 1:23 pm

Hi Sumit,

The issue is most likely related to how the key for the Quarter
attribute is defined in your case, since LinkMember() uses the member
key to find equivalent members. Assuming that you are using AS 2005,
this Adventure Works query returns the desired result:
[color=darkred]
with
member [measures].[linkmember] as
'LinkMember([Ship Date].[Calendar].[Calendar Quarter].&[2003]&[3],
[Delivery Date].[Calendar]).UNIQUENAME'
select
[Measures].[linkmember] on columns
from [Adventure Works][color=darkred
]

If you examine the Properties of the "Calendar Quarter" attribute of the
Date dimension, its ColumnKey is a collection of 2 columns: CalendarYear
and CalendarQuarter, making it unique across all quarters.

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Sumit Pilankar

2005-11-29, 3:23 am

Thanks Deepak, for the help.

We found that in our Time Dimension the hierarchy members were not linked to
each other properly, so we added attributes to each member and then
linkmember started working as expected.

Thanks again
Sumit Pilankar

"Deepak Puri" < deepak_puri@progress
ive.com> wrote in message
news:e3abWVf8FHA.500@TK2MSFTNGP15.phx.gbl...
> Hi Sumit,
>
> The issue is most likely related to how the key for the Quarter
> attribute is defined in your case, since LinkMember() uses the member
> key to find equivalent members. Assuming that you are using AS 2005,
> this Adventure Works query returns the desired result:
>
> with
> member [measures].[linkmember] as
> 'LinkMember([Ship Date].[Calendar].[Calendar Quarter].&[2003]&[3],
> [Delivery Date].[Calendar]).UNIQUENAME'
> select
> [Measures].[linkmember] on columns
> from [Adventure Works]
>
> If you examine the Properties of the "Calendar Quarter" attribute of the
> Date dimension, its ColumnKey is a collection of 2 columns: CalendarYear
> and CalendarQuarter, making it unique across all quarters.
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.droptable.com ***

Sponsored Links