Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Replacing Linkmember with a Many-to-Many Relationship

with 11 comments

Linkmember is one of those MDX functions that I feel like I should never have to use – I always wonder whether I’ve modelled my data correctly when I do use it – even though it does come in very handy from time to time. There are two main problems with it:

  • Using it in calculations can cause performance problems, although its impact is much less with Analysis Services 2008 compared to what it was with 2005. My post on optimising the currency conversion calculations generated by the Add Business Intelligence wizard is a good example of this.
  • It’s not very flexible – it links members on one hierarchy to another hierarchy, whereas what you often want to be able to do (especially if you’re using role-playing dimensions) is to map the currentmember on any hierarchy of a dimension to the currentmember on the equivalent hierarchy on the other dimension.

Take the following example. Here’s a very simple cube using the Adventure Works database, with one measure group based on the Internet Sales fact table, one measure called Sales Amount and with three dimensions: Order Date, Due Date and Customer.

image

If you want to be able to compare the sales on a given Order Date with the sales on the same day on the Due Date dimension, you might write a query something like this:

WITH
MEMBER MEASURES.DUEDATESALES
AS
([Measures].[Sales Amount], [Order Date].[Date].[All Periods],
LINKMEMBER([Order Date].[Date].CURRENTMEMBER, [Due Date].[Date]))
SELECT
{MEASURES.DUEDATESALES,[Measures].[Sales Amount]} ON 0,
[Order Date].[Date].[Date].MEMBERS ON 1
FROM [LinkmemberTest]

image

It’s a bit contrived, I’ll admit, but you get the idea. Performance here isn’t much of a problem, but flexibility is: if you put Years on rows instead of Dates, you get meaningless results. So:

MEMBER MEASURES.DUEDATESALES
AS
([Measures].[Sales Amount], [Order Date].[Date].[All Periods],
LINKMEMBER([Order Date].[Date].CURRENTMEMBER, [Due Date].[Date]))
SELECT
{MEASURES.DUEDATESALES,[Measures].[Sales Amount]} ON 0,
[Order Date].[Calendar Year].[Calendar Year].MEMBERS ON 1
FROM [LinkmemberTest]

…gives us the sales for the Year by Order Date in both columns, because in the calculation the Linkmember function returns the All Member on the [Order Date].[Date] hierarchy:

image

Fortunately, we can get around this by replacing the use of Linkmember with a many-to-many relationship. For this particular example, we need to do the following:

1) Create a new utility dimension that will allow us to switch between showing Order Date values and Ship Date values. We can do that using a view or named query something like this:

SELECT        0 AS DateTypeKey, 'Order Date' AS DateTypeDesc
UNION ALL
SELECT        1 AS DateTypeKey, 'Due Date' AS DateTypeDesc

And from this building a dimension (which I’ve called Date Type) with a single hierarchy, where the IsAggregatable property is set to False.

2) Build a new bridge table to use as the intermediate measure group in our many-to-many relationship that gives us all the distinct combinations of Order Date and Due Date, and Due Date and Order Date, that exist in our fact table. I used the following SQL to do this:

SELECT DISTINCT 0 AS DateTypeKey, OrderDateKey AS DateKey, OrderDateKey, DueDateKey
FROM            dbo.FactInternetSales
UNION ALL
SELECT DISTINCT 1 AS DateTypeKey, DueDateKey AS DateKey, OrderDateKey, DueDateKey
FROM            dbo.FactInternetSales AS FactInternetSales_1

Luckily, in the Internet Sales Fact table there’s a 1:1 relationship between Order Dates and Due Dates, but that’s because the data’s fake.

3) Add a new measure group to the cube based on this fact table (I’ve called it Date Type Switch), add a new role-playing instance of the Date dimension (which I’ve just called Date) to the cube as well as the Date Type dimension created above, and give these dimensions as well as the Order Date and Due Date dimensions regular relationships to this new measure group. You can then create many-to-many relationships between the Date Type and Date dimensions and the Internet Sales measure group.

image

You can then use the Date dimension in your queries, and use the Date Type dimension to choose between showing Order Date values or Ship Date values. For example:

SELECT
[Date Type].[Date Type].[Date Type].MEMBERS
ON 0,
[Date].[Date].[Date].MEMBERS
ON 1
FROM [M2MTest]
WHERE([Measures].[Sales Amount])

image

And this time, we can use any hierarchy on the Date dimension and get the correct results:

SELECT
[Date Type].[Date Type].[Date Type].MEMBERS
ON 0,
[Date].[Calendar Year].[Calendar Year].MEMBERS
ON 1
FROM [M2MTest]
WHERE([Measures].[Sales Amount])

image

How does this work, then? You can think of the many-to-many relationship here working like a switch. in most cases when there are two dimensions between the intermediate measure group and the main measure group in a many-to-many relationship, the relationship is resolved through both dimensions. In this case, however, the relationship is only ever resolved through one dimension or the other and the Date Type dimension controls which of the dimensions is used. When the Due Date member on Date Type is selected, for example, whatever is selected on the Date dimension is related to the equivalent members on the Due Date dimension while on the Order Date dimension it’s as if you’re not selecting anything (or, strictly speaking, it’s as if you’re selecting all the members on each hierarchy that have data associated with the Due Dates you’ve selected).

In this particular case, because there’s a relatively small number of rows in the intermediate measure group, performance is marginally better than the Linkmember approach. However this is not always going to be the case – with a larger intermediate measure group you’ll find performance may be significantly slower than using Linkmember. Therefore, you’ll need to test thoroughly if you do decide to use this approach and your main reason for using it probably should be for the flexibility it provides.

Written by Chris Webb

May 24, 2011 at 11:31 pm

Posted in Analysis Services, MDX

11 Responses

Subscribe to comments with RSS.

  1. Wowww, you posted this at 11:30 pm, and we, in a project, we faced the same issue at 5:00 pm, we were thinking about how resolve this….; Thank you

    ilyas

    May 25, 2011 at 10:32 am

  2. Interesting post and very useful one.

    Thanks Chris!

    Ayyappan

    May 26, 2011 at 12:31 am

  3. A Goog trick, Chris!
    but I think there will be a performance problem
    when “Date Type Switch” measure group processing (select distinct….) on large fact tables. How do you solve this problem?

    Alexander

    May 26, 2011 at 12:14 pm

    • You probably can’t solve it, which is why I said at the end of the post to be careful!

      Chris Webb

      May 26, 2011 at 1:27 pm

  4. Interesting your post but don’t you think Richard Lee post (link below) resolves, more or less, your problem?

    http://richardlees.blogspot.com/2011/06/linkmember.html

    Jorge

    July 13, 2011 at 11:53 pm

    • No, he doesn’t – in this post he (perfectly accurately) describes how the LinkMember function works. The point I’m making here is that you can only use LinkMember when you know in advance which hierarchies are going to be used in your queries; my approach works whatever query you run. As I say, this new approach isn’t appropriate in all cases and if you can use LinkMember it may perform better.

      Chris Webb

      July 14, 2011 at 12:06 am

      • You’re absolutely right. Thanks!

        Jorge

        July 14, 2011 at 11:31 am

  5. I read your blog with great interest and learn a lot.

    Sorry this question is regarding Linkmember, not your M2M alternative.

    I’ve recreated your LinkmemberTest cube in AS2005 and these 3 MDX queries but I can’t reconcile the differences.

    I am assuming that this first query is showing 2005 sales by order week, alongside sales by due week.

    I’ve then split it into two separate queries to show sales by order week, and then sales by due week.

    Strangely, the numbers in the Due Date query don’t match the Linkmember query.

    Week 27 and 28 of every year and week 29 of 2005 returns a different result. I assume that this is because the link is failing because the keys don’t match, but that doesn’t explain the week 29 in 2005.

    I am seeing something similar in a production cube I am trying to report from so I wonder what the issue is, and how I can rectify it.

    //Query of Sales by Order Date –> linked to Due date
    //and Sales by due date

    WITH
    MEMBER [Measures].[DueDateSales] AS
    (
    [Measures].[Sales Amount], [Order Date].[Week of Year].[All Periods]
    ,LINKMEMBER([Order Date].[Week of Year].CURRENTMEMBER, [Due Date].[Week of Year])
    )

    SELECT
    {Measures.DueDateSales,[Measures].[Sales Amount]} ON columns,
    {[Order Date].[Fiscal Year].[Fiscal Year]*
    [Order Date].[Week of Year].[Week of Year] } ON rows
    FROM [LinkmemberTest];

    //Query of 2005 sales by due date — look at weeks 27 and 28

    SELECT
    {[Measures].[Sales Amount]} ON columns,
    {[Due Date].[Fiscal Year].[Fiscal Year]*
    [Due Date].[Week of Year].[Week of Year] } ON rows
    FROM [LinkmemberTest];

    //Query of 2005 sales by Order Date — These numbers all agree as expected
    SELECT
    {[Measures].[Sales Amount]} ON columns,
    {[Order Date].[Fiscal Year].[Fiscal Year]*
    [Order Date].[Week of Year].[Week of Year] } ON rows
    FROM [LinkmemberTest];

    Davos

    October 11, 2011 at 5:41 am

    • I think I can see what the problem is here. In your calculation, you’re only using LinkMember on the Week of Year hierarchy. However in your queries you’re using the Fiscal Year and Week of Year hierarchies. So in your first query the calculation is actually returning the value for the Sales Amount for the Due Date Week of Year that is the same as the Order Date Week of Year, but for the Order Date Fiscal Year; in your second query you are returning the value for Due Date Week of Year and Due Date Fiscal Year. Changing your calc as follows should fix this:

      WITH
      MEMBER [Measures].[DueDateSales] AS
      (
      [Measures].[Sales Amount], [Order Date].[Week of Year].[All Periods]
      ,LINKMEMBER([Order Date].[Week of Year].CURRENTMEMBER, [Due Date].[Week of Year])
      , [Order Date].[Fiscal Year].[All Periods]
      ,LINKMEMBER([Order Date].[Fiscal Year].CURRENTMEMBER, [Due Date].[Fiscal Year])
      )

      Chris Webb

      October 11, 2011 at 5:47 pm

  6. Thanks for the correction, very helpful.

    Davos

    October 12, 2011 at 2:35 am

  7. Hi Chris, thank you for the post. I am working on a banking project and reports typically have several date intersections. This is a very nice alternative to LinkMember and might end up being useful for self-service. Also, thanks for the heads-up on possible performance issues. Most of my roll-ups happen at the month & year level and – so far – performance is snappy. Thanks again!

    James

    January 30, 2013 at 7:49 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,868 other followers

%d bloggers like this: