MDX Ordering By Dimension

3
votes

Over in the SQL side, my data is looking like this: 

Select f.id, f.TimeKey,t.CalendarYearMonth 
from FactSubmission f
inner join DimTime t on t.TimeKey = f.TimeKey
order by f.Id asc

enter image description here

Sorting from MDX we have descending

SELECT
NON EMPTY ORDER(
 [DimTime.CalendarYearMonth].[CalendarYearMonth].Members,
 [DimTime.CalendarYearMonth].CurrentMember.Properties("MEMBER_KEY"), 
 DESC
 )  ON COLUMNS
FROM [PSE_FactSubmission]

enter image description here

And Ascending

enter image description here

The January dates aren't at the top of either sort, which suggests I'm sorting by the FactSubmission.ID key instead of DimTime.CalendarYearMonth

Is this how things are supposed to work? I'd like to pull back Jan,Feb,March.

DimTime.CalendarYearMonthNum is a column with data in the form 201501,201502,201503 etc. Here's an attempt at using this column to to sort the CalendarYearMonth data.

enter image description here

Debugging Query to Select Keys enter image description here

NonEmpty Query enter image description here

2
sortingmdxpentaho
It seems to be ordering strings, which would result in such output depending on ASC / DESC order. Perhaps you could change the values to number of months eg: 2016-01 ... – Kamil Gosciminski
@ConsiderMe I kind of see what you're saying. If I switch the first and second ORDER parameters to be strict number values the sorting works as expected. Would there be a way to select the string version, and sort by the corresponding numeric values? – Andrew Walters

2 Answers

0
votes

Try ordering using a different property:

SELECT
NON EMPTY ORDER(
 [DimTime.CalendarYearMonth].[CalendarYearMonth].Members,
 [DimTime.CalendarYearMonth].CurrentMember.Properties("MEMBER_Value"), 
 DESC
 )  ON COLUMNS
FROM [PSE_FactSubmission];

or maybe this:

SELECT
NON EMPTY ORDER(
 [DimTime.CalendarYearMonth].[CalendarYearMonth].Members,
 [DimTime.CalendarYearMonth].CurrentMember.MEMBERValue, 
 DESC
 )  ON COLUMNS
FROM [PSE_FactSubmission];

In the above you should be good using DESC - sometimes you need to break the underlying hierarchical ordering by adding a B i.e. BDESC

From here I cannot see MEMBER_VALUE: http://mondrian.pentaho.com/documentation/mdx.php

...but there is a function .VALUE so maybe try the following:

SELECT
NON EMPTY ORDER(
 [DimTime.CalendarYearMonth].[CalendarYearMonth].Members,
 [DimTime.CalendarYearMonth].CurrentMember.Value, 
 DESC
 )  ON COLUMNS
FROM [PSE_FactSubmission];

Strange that the key doesn't work. What values do you get if you run something like this?

WITH MEMBER [KEYcheck] AS
    [DimTime.CalendarYearMonth].CurrentMember.Properties("MEMBER_KEY")
    //[DimTime.CalendarYearMonth].CurrentMember.MEMBER_KEY
    //[DimTime.CalendarYearMonth].[CalendarYearMonth].CurrentMember.MEMBER_KEY
    //[DimTime].CurrentMember.MEMBER_KEY
SELECT
  [KEYcheck] ON 0,
  [DimTime.CalendarYearMonth].[CalendarYearMonth].Members ON 1
FROM [PSE_FactSubmission];
0
votes

You are doing an alphabetical sort. February->January->March.

For doing a sort based on the month number, there needs to be a field which maps January-1, February-2, March-3.

If you have such a column in cube, use that to to sort. If not create a calculated member like below -

WITH MEMBER Measures.CalendarMonth AS
CASE [DimTime.CalendarYearMonth].CurrentMember
 WHEN  [DimTime.CalendarYearMonth].&[January] THEN 1
 WHEN  [DimTime.CalendarYearMonth].&[February] THEN 2
 WHEN  [DimTime.CalendarYearMonth].&[March] THEN 3
END

SELECT
NON EMPTY ORDER(
 [DimTime.CalendarYearMonth].[CalendarYearMonth].Members,
 Measures.CalendarMonth, 
 DESC
 )  ON COLUMNS
FROM [PSE_FactSubmission]

EDIT for Andrew

with member Measures.[MonthNum] as
NonEmpty
    ( 
     [DimTime.CalendarYearMonthNum].[CalendarMonthNum].members,
     ([DimTime.CalendarYearMonth].[CalendarYearMonth].currentmember, Measures.foo)
    ).item(0).membervalue

select
non empty
order
  (
   [DimTime.CalendarYearMonth].[CalendarYearMonth].members,
   Measures.[MonthNum],
   desc
  ) on rows
from [PSE_FactSubmission]

EDIT - with EXISTS

with member Measures.[MonthNum] as
EXISTS
      (
       [DimTime.CalendarYearMonthNum].[CalendarMonthNum].members,
       [DimTime.CalendarYearMonth].[CalendarYearMonth].currentmember,
       "SomeMeasureGroup"
      ).item(0).membervalue