question

landryan avatar image
landryan asked

MDX - Show the composition of a Difference Column

I have spent a considerable time adapting a number of existing SQL-based reports to MDX-based reports in SSRS. However, there is one problem I am having which I can't seem to figure out: I have a time dimension of monthly periods and can build a tuple to represent the value of the "previous" month. I can then build a calculated member to show what the "difference" is (e.g. June = 160 people, May was 100 people therefore Difference is +60 people). However, the old SQL-based report actually shows the following in a table: June 160 people, People In +90, People Out -30, Difference is +60 I need to build calculated measures (members?) to work out the number of distinct people "in" and the number of distinct people "out". I have a Person dimension and a Distinct Count Measure on the People Key on the relevant Fact Table in my Cube. Hope this makes sense - I have a number of books on MDX and none of them contain any examples of this kind of calculation... is it even possible?
ssasmdx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
You are probably looking for the **`LAG`** function and properly build tuple. The `LAG` functions returns member that is specifid number of possition prior the current member. Then if we use `LAG(1)` on `CURRENTMEMBER` of the Date dimension, it will retrun previous member and we can build a tuple of the previous member and appropriate measure. Something like this should work: WITH MEMBER [Measures].[PreviousMonthCount] AS ([Date].[Year-Month-Date].CURRENTMEMBER.LAG(1), [Measures].[DistinctCountMeasure]) MEMBER [Measures].[Difference] AS [Measures].[DistinctCountMeasure] - [Measures].[PreviousMonthCount] SELECT { [Measures].[DistinctCountMeasure], [Measures].[PreviousMonthCount], [Measures].[Difference] } ON COLUMNS, NON EMPTY { [Date].[Year-Month-Date].[Month].MEMBERS } ON ROWS FROM [YourCube] This should return something like this: DistinctCountMeasure PreviousMonthCount Difference January 2007 62 020 (null) 62 020 February 2007 62 122 62 020 102 March 2007 109 189 62 122 47 067 April 2007 66 449 109 189 -42 740 **====** **EDIT** **====** So I've took a look on your problem and created a small cube with two dimensions (Date and Customer) and here is a query which shows composition of the DistinctCount difference. This will provide you exactly results wanted. However didn't tested it on large cubes as don't have currently any available with distinct count, so don't know if the performance will be perfect (It's first draft). WITH MEMBER [Measures].[PrevMonth] AS ([Date].[Year-Month-Date].CURRENTMEMBER.LAG(1), [Measures].[Distinct Count]) MEMBER [Measures].[Diff] AS [Measures].[Distinct Count] - [Measures].[PrevMonth] MEMBER [Measures].[Looses] AS SUM([Customer].[Customer].[Customer].MEMBERS, IIF([Measures].[Diff] < 0, 1, 0)) MEMBER [Measures].[Gains] AS SUM([Customer].[Customer].[Customer].MEMBERS, IIF([Measures].[Diff] > 0, 1, 0)) --MEMBER [Measures].[B] AS ([Date].[Year-Month-Date].CURRENMEMBER.LAG(1), [Measures].[Distinct Count]) SELECT { [Measures].[Distinct Count], [Measures].[PrevMonth], [Measures].[Diff], [Measures].[Looses], [Measures].[Gains] } ON COLUMNS, { EXISTS([Date].[Year-Month-Date].[Month].MEMBERS,[Customer].[Customer].[Customer].MEMBERS, "Sales") } ON ROWS FROM [Sales] This produces result like this one: Distinct Count PrevMonth Diff Looses Gains 2012 January 2 (null) 2 0 2 2012 February 3 2 1 1 2 2012 March 3 3 0 1 1
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks, a very clear example of a classic problem but I've already got this far... I'm looking for the composition of the difference - e.g. how do I identify that the value of my difference column is the result of losing 30 distinct records but gaining 90 new (distinct) ones?
0 Likes 0 ·
Just posted an update which solves the issue of the difference composition. :-)
0 Likes 0 ·
landryan avatar image
landryan answered
Many, Many thanks for this... results produced okay, but because [Customer].[Customer].[Customer].MEMBERS is not a unique dimension I ended up getting non-distinct results: 1. Here's your code adapted to my Data Warehouse: WITH MEMBER [Measures].[PrevMonth] AS ([Dim Snapshot Date].[Financial Year Quarter Month Day].CURRENTMEMBER.LAG(1), [Measures].[RESYSID Distinct Count]) MEMBER [Measures].[Diff] AS [Measures].[RESYSID Distinct Count] - [Measures].[PrevMonth] MEMBER [Measures].[Looses] AS SUM([Dim Constituent].[Constituent ID].[Constituent ID], IIF([Measures].[Diff] < 0, 1, 0)) MEMBER [Measures].[Gains] AS SUM([Dim Constituent].[Constituent ID].[Constituent ID], IIF([Measures].[Diff] > 0, 1, 0)) --MEMBER [Measures].[B] AS ([Date].[Year-Month-Date].CURRENMEMBER.LAG(1), [Measures].[Distinct Count]) SELECT { [Measures].[RESYSID Distinct Count], [Measures].[PrevMonth], [Measures].[Diff], [Measures].[Looses], [Measures].[Gains] } ON COLUMNS, { EXISTS([Dim Snapshot Date].[Financial Year Quarter Month Day].[FY Month],[Dim Constituent].[Constituent ID].[Constituent ID].MEMBERS, "Dim Constituent") } ON ROWS FROM [Data Warehouse] 2. Here are the results: RESYSID Distinct Count PrevMonth Diff Looses Gains September 47490 (null) 47490 0 54809 October 47632 47490 142 95 277 November 47372 47632 -260 860 555 December 46753 47372 -619 1043 348 January 46954 46753 201 9448 9680 February 46740 46954 -214 600 402 March 46652 46740 -88 482 381 April 46401 46652 -251 621 372 As you can see, because [Dim Constituent].[Constituent ID].[Constituent ID].MEMBERS is not truly unique, I have a problem. This may be because it is a Type 2 SCD, or possibly because there are some strange combinations of application IDs in the early Constituent dimension. I think it may be because of the former. So my solution was I had to create an outrigger dimension to my Constituent Dimension keyed on the "RESYSID" Application Key, that produced a unique list of RESYSIDs, and then refer to that in the SUM instead. I don't know if this is established practice or not but it appears to be a pretty neat trick: Code then became: WITH MEMBER [Measures].[PrevMonth] AS ([Dim Snapshot Date].[Financial Year Quarter Month Day].CURRENTMEMBER.LAG(1), [Measures].[RESYSID Distinct Count]) MEMBER [Measures].[Diff] AS [Measures].[RESYSID Distinct Count] - [Measures].[PrevMonth] MEMBER [Measures].[Looses] AS SUM([Dim RESYSID].[RESYSID].[RESYSID], IIF([Measures].[Diff] < 0, 1, 0)) MEMBER [Measures].[Gains] AS SUM([Dim RESYSID].[RESYSID].[RESYSID], IIF([Measures].[Diff] > 0, 1, 0)) --MEMBER [Measures].[B] AS ([Date].[Year-Month-Date].CURRENMEMBER.LAG(1), [Measures].[Distinct Count]) SELECT { [Measures].[RESYSID Distinct Count], [Measures].[PrevMonth], [Measures].[Diff], [Measures].[Looses], [Measures].[Gains] } ON COLUMNS, { EXISTS([Dim Snapshot Date].[Financial Year Quarter Month Day].[FY Month],[Dim RESYSID].[RESYSID].[RESYSID].MEMBERS, "Dim Constituent") } ON ROWS FROM [Data Warehouse] Results then became: RESYSID Distinct Count PrevMonth Diff Looses Gains September 47490 (null) 47490 0 47490 October 47632 47490 142 11 153 November 47372 47632 -260 414 154 December 46753 47372 -619 789 170 January 46954 46753 201 19 220 February 46740 46954 -214 384 170 March 46652 46740 -88 318 230 April 46401 46652 -251 411 160 Anyway, many thanks - got there in the end. Turns out to be more accurate than the existing SQL-based solution!
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You have posted no information about your schema, so I have only provided an example how you can achieve requested results. It was up to you to adapt it to your solution. :-)
0 Likes 0 ·
Sorry I think I should have added this as a comment underneath your answer...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.