Friday, June 10, 2011

linkmember, my favourite MDX function

Linkmember is one of my favourite MDX functions. It tends to be useful whenever you have role playing dimensions. For example, when you have multiple (role playing date dimensions or role playing geography dimensions) such as OrderDate and DespatchDate, or CustomerStore and PurchaseStore.

You might have a report that displays the order date on rows, with sales on columns. You would also like add a column to show sales, but sliced by delivery date. So what you want to do, is put the same, sales, measure on columns, but have the "delivery date" dimension on rows.

This is exactly what the linkmember does. Simply create a calc member that uses linkmember to switch the OrderDate on rows to DeliveryDate. Here is a simple example.

with member
Measures.AmountDelivered as
(Measures.Amount,
linkmember(OrderDate.CalendarYWD,DeliveryDate.CalendarYWD),

root(OrderDate))
select
{Measures.Amount,
Measures.AmountDelivered}
on Columns,
non empty
OrderDate.CalendarYWD.Day on Rows
from MyCube


One thing that you need to remember is that the linkmember will effectively get the member with the same key in another dimension. However, it will not change the original member. So you probably want to also override the original member with a root() or .[All].

Another way of achieving the same functionality as LinkMember is to extract the key and use StrToMember() to construct the appropriate member of the other dimension. However, LinkMember is much more efficient, and is the preferred tool.

25 comments:

Unknown said...

Finally! After many hours of head scratching and Googling, I found your post and the exact answer to my question. I had got 90% of the way there using the StrToMember method, but was missing the ROOT function as per your example.

Great blog will follow from now on!!

Eric Brassell said...

When going with this solution do the totals work properly when using multiselect from an Excel client?
If not, do you know a solution?

Richard Lees said...

Yes, Eric, I think you will find that this works fine with Excel.

EWB-MAP said...

I can't seem to get syntax correct. Can you tell if I'm missing something obvious:

(
[Measures].[Work Item Count], [Date].[Week].[All]
,LINKMEMBER([Date].[Week].CURRENTMEMBER, [Work Item].[System_CreatedDate])
)

Richard Lees said...

The linkmember() function in your expression looks OK to me. You have an over-ride for the [Date].[Week] dimension, which is a common pitfall.

Your problem might be that the key for the [Date].[Week] currentmember is not the same as the key for [Work Item].[System_CreatedDate]. They sound different, at least they sound like they have different hierarchies. The safest way of using linkmember(), although not compulsory, is to use it with role playing dimensions.

Andy T said...

Linkback http://thebi-zness.blogspot.com/2012/11/mdx-query-across-multiple-role-playing.html

kiransagar said...

hi I am a layman in mdx queries.I didn't understood the use of link member function.can you explain a simple and detail one..thanks

Decheddar said...

Hi Richard,

I want to see WIP data in my cube. My fact table has a WIP_Date and a Bill_Date. If the item is not billed hence no Bill_Date, then it is in WIP. WIP is a snapshot...so at any point in time I should be able to tell what my WIP was. The below SQL query will give me this in tsql for period 201307:

select * from Fact.Time where BillDateKey is null or BillDateKey >= 20130731 and WIPDateKey <=20130731

How do I write this in MDX?

Thanks for your help.

Decheddar said...

The tsql is more like the below:
select sum(ToBill_Amt) AS WIP from Fact.Time where (BillDateKey is null or BillDateKey >= 20130131) and WIPDateKey <=20130131 and ClientKey = 9940

Richard Lees said...

Hi Decheddar,
I can't write the whole query for you, but here are the components.

This member will give you the null dates so long as you have configured the dimension to have missing members.
BillDate.BillDate.[All].UNKNOWNMEMBER

To get the actual date from the dimension member, you can use this expression
BillDate.BillDate.properties("Open Date",typed) and compare it with a literal that you put inside a cDate() function so that you are comparing dates not strings.

The reason why I encourage you to use the properties expression above is that if you use an MDX member expression with a date range, SSAS does not understand the dates (and consequently date range) if the members don't exists. Once you convert to a date expression you can use a range yourself.

Hope that helps.

Jaapp said...
This comment has been removed by the author.
Unknown said...

Hi!

you write:
"However, LinkMember is much more efficient, and is the preferred tool."

Do you have a reference for that? Has this been your experience?

Thanks!

Christos

Richard Lees said...

Hi Christos, that is just my experience. You might get different results. However, StrToMember() is quite inefficient as ssas does not know what it is until each iteration.

Please let me know if you discover a situation where StrToMember() is faster than LinkMember().

Thanks

Unknown said...

Hi Richard,

I am new to MDX have calculated member expression like
Sum(Null : link member([Asofdate].date, [Accounting date].date),measures.amount). I want to know the what happening in this logic.

Thanks,
Rao

Richard Lees said...

Hi Rao,
That calc is asking for the sum of Measures.Amount from day zero thru to [Accounting date] having the same key as [Asofdate]. Note, it is also filtered by [Asofdate].date, which is something that catches many people. If you want to only sum by [Accounting date] without also filtering by Asofdate, then you should include [Asofdate].date.[All] in the measure being sum'd. ie
Sum(Null : link member([Asofdate].date, [Accounting date].date),(measures.amount,[Asofdate].date.[All])).
Hope that helps,

Unknown said...

Hi Richard,

I don't think I understand the concept of setting the scope to Root level. Could you please explain in more detail

Unknown said...

Hi Richard,

I don't think I understand the concept of setting the scope to Root level. Could you please explain in more detail

-Jaya

Richard Lees said...

Root(dimension name) is an mdx function that removes any filters on the dimension. The reason for doing this is that we wanted to switch the Date filter from OrderDate to DeliveryDate. The LinkMember() function puts the filter on DeliveryDate, but we also wanted to remove the filter from OrderDate for that measure.
Hope that makes sense.

Unknown said...

Richard,

Thanks a ton for your response.

Here is the problem that I have.

I am trying to compute active opportunities on the context date which is defined by start date less than the context date and close date greater than the context date.

Here is the query I have
with
member [Measures].[Cumulative_count_dates]
as
aggregate({NULL:[Time Dimension].[Year-Qtr-Month-Date].currentmember.lag(1) } *
{LINKMEMBER([Time Dimension].[Year-Qtr-Month-Date].currentmember.lead(1),[Opportunity Close Dt].[Year-Qtr-Month-Date]):NULL}
,[Measures].[Opportunities Count])
select {[Measures].[Opportunities Count],[Measures].[Cumulative_count_dates]} on columns,
NON EMPTY {[Time Dimension].[Year-Qtr-Month-Date].[Month].members} on rows
from Adventcube.

I get appropriate results for Year level...but not at lower levels... Is it possible that I am running into the filtering issue like you mentioned. If so how do I eliminate it in the following query.

-Jaya

Richard Lees said...

The numbers would be different for Year, Quarter, Month and Date. This is because you are LEADing one period. And that period could be a year, quarter, month or date. I think you will find that SSAS is calculating correctly, given the range (be it year, quarter, month or date) that is the current member.

Roo said...

Hi. I managed to get this working on my Adventure works cube, and have now tried to implement it in my own cube. However when I run against my own cube, the equivalent of Measures.AmountDelivered generates #error - any ideas why this would be?

Richard Lees said...

What's the error message? If you are browsing the cube from SQLMS, you can double click on the error for more information.

sumit said...

Hi Richard
I am looking for a help on measue filter by differnt dimensions, I have forecast data which is multiple version for the 2 years like, Cut 1 for 2018/2019 and cut 2 for 2018/2019, The dimensions are same but the values differe based on different outcomes.

my needs is that we want to filter the values based on 2 versions like cut 1, 2019 Vs cut 2 2018

I have created 2 duplicated dimensions for version name, and wanted to filter the CY calculated columns based on dimension value selected from Version 1st dimennions.


Please see if this doc helps more
https://drive.google.com/open?id=16SjOjqnEqhJ7Q_Qv3Ts0bnGTXIE06By_

Paolo said...

Hi Richard,

actually I am using your Solution with .currentMember

aggregate(
LINKMEMBER(DateDIM2nd.currentmember, DateDIM),Revenue
)

However if used in Excel as a Filter and if multiple Dates are selected it will brake. I am sure that it's because of the .currentmember but how can I solve the issue?

Thank you!

Sathya said...

hi how to link 2 dimnesions .. i mean how to use 2 link member function for single measure