Replacing Linkmember with a Many-to-Many Relationship

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.

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]

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:

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.

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])

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])

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.