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.
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
Interesting post and very useful one.
Thanks Chris!
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?
You probably can’t solve it, which is why I said at the end of the post to be careful!
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
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.
You’re absolutely right. Thanks!
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];
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])
)
Thanks for the correction, very helpful.
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!
Mondrain MDX for records between date range where start and end date being separate columns
I want to search date between two columns of same dimension. Columns name is start date and end date. I tried every thing, following query give me this error : Mondrian Error:Internal error: Cannot deduce type of call to function ‘:’
MDX Query :
WITH SET [~ROWS] AS Hierarchize({{[Location_Cluster.default].[All Location_Cluster.defaults]}, {[Location_Cluster.default].[Location_Cluster].Members}}) member [Measures].[QTY Percent] as ‘[Measures].[Total_Quantity]/([Measures].[Total_Quantity], [Location_Cluster.default].[All Location_Cluster.defaults])’, format_string=’0.00%’ member [Measures].[Revenue Percent] as ‘[Measures].[Total_Revenue]/([Measures].[Total_Revenue],[Location_Cluster.default].[All Location_Cluster.defaults])’, format_string=’0.00%’ member [Measures].[Margin Percent] as ‘[Measures].[Total_Margin]/([Measures].[Total_Margin], [Location_Cluster.default].[All Location_Cluster.defaults])’, format_string=’0.00%’ SELECT NON EMPTY {[Measures].[Sku_Count],[Measures].[Total_Quantity],[Measures].[QTY Percent],[Measures].[Total_Revenue],[Measures].[Revenue Percent],[Measures].[Total_Margin],[Measures].[Margin Percent]} ON COLUMNS, NON EMPTY [~ROWS] ON ROWS FROM [APCS_SALES_CUBE] Where ({[Date].[Start_Date].&[2017-01-01]: NULL} ,{NULL : [Date].[End_Date].&[2017-03-01]},[Cluster.Cluster_Id].[2],[Taxonomy.default].[Taxonomy_ID].[3],[Company.Company_Name].[Compnay_Name].[1])
I followed following links to resolve this issue. But don’t know how to apply specify function in Mondrian MDX.
https://ask.sqlservercentral.com/questions/95478/mdx-for-records-between-date-range-where-start-and.html#answer-142811
https://www.purplefrogsystems.com/blog/2013/04/mdx-between-start-date-and-end-date/#comment-1981
Please provide me some help to resolve this issue in Mondrian MDX,
Sorry, I don’t know much about Mondrian at all…