Matrix SSAS dimension/hierarchy order problem

I have a have a relatively simple SSAS cube that I'm trying to display in a matrix but the order of the data is not correct. The rows of the matrix are a time hierarchy and the query designer produces the correct MDX and when I run the MDX in the data tab, the order is correct. However in the report preview tab, the order is incorrect. Specifically, the time hierarchy looks like this Year-Month-FullDate. The report is ordering the Months like this 1,10,11,12,2,3,4,5,6,7,8,9. I ran the MDX query in Sql Studio and the order is correct, and I used Excel to consume the same cube and it produces the correct order.

The report is somehow treating the Month like character data instead of numeric, how do I fix this?

It sounds like your MDX query only retrieves the Member_Caption (which is the string label representation), but not the Member_Value property (which is the value representation with the original data type).

For example:

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS, NON EMPTY { ([Due Date].[Month Number Of Year].[Month Number Of Year].ALLMEMBERS * [Due Date].[English Month Name].[English Month Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, MEMBER_VALUE ON ROWS FROM [Adventure Works DW] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Note that the .Value field property of RDL expressions always maps to the Member_Caption property value for dimensions (the member caption is always a string!). The additional Member_Value intrinsic dimension property is not mapped directly to any of the predefined RDL extended field property names. Therefore, in order to access the MEMBER_VALUE property in the report, you have to use the so-called field property collection syntax in the matrix group expression and sort expression to get the numeric value for the dimension property and specify the property name exactly as defined in the MDX query above (the property name is case-sensitive in this case):
=Fields!Month_Number_Of_Year("MEMBER_VALUE")

Additional information about MDX intrinsic member properties can be found here: http://msdn2.microsoft.com/en-us/library/ms145528.aspx

-- Robert

|||

Robert - that worked perfectly. Thanks a bunch!

To anyone else in the same situation, to get to the matrix group expression and sort expression right-click the matrix object in the report designer and select Properties. Goto the Groups tab and then select dimension member in question and click the Edit button. This brings up another window, goto the Sort tab and enter the expression.

Materializing a reference dimension in SSAS

Hi,

I think there is an issue with the Materialize option when we have a referenced dimension in SSAS. For example if there is a dimension B which is linked to a measure group F through dimension A and materialize option is checked (which is default and in best practices to be followed) I have an issue. If there are any dimension keys of dimension A in measure group F that are not in dimension A then it doesn’t give an error saying dimension key not found when processing. The processing query ignores the rows that do not have a corresponding dimension key and the processing succeeds. Only when the materialize option is unchecked I get the error.

It seems that there is a problem since there is a regular relationship between dimension A and measure group F and if there are dim key errors then it should fail and not ignore those rows and process.

Can you please confirm if this is a bug in SSAS and if we should always avoid using this option (Materialize option checked) when use referenced dimensions?

I am using SQL 2005 SP2a.

Thanks

Arun

This is not a bug. When you click materialize, the fact table associated with the measure group is joined to the intermediate dimension using an inner join. If you have a missing reference, the effect of the inner join is that fact records are dropped.

What we always recommend to folks is that they do NOT use the Unknown Member and associated error handling features in their cubes. Instead, we recommend they resolve all references in the data warehouse. For example, if you have a fact table that represents an order that has not shipped, the order fact record has a NULL ship date reference. In the Date dimension table, you would have an entry for the NULL date and your fact record would reference that Date record's surrogate key (which we usually set to -1).

B.