question

Bartoloz avatar image
Bartoloz asked

MDX issue after SSAS migration 2000 to 2012

After SSAS migration from version 2000 to 2012 (of course going through intermediate step 2008) one of my company reports designed in Arcplan 7 is not able to read data from properties of dimension attribute. It executes following MDX query with an error: SELECT [Class].[Class].MEMBERS Dimension PROPERTIES [Class].[Name] ON COLUMNS FROM [Cube] CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORMAT_STRING, FORE_COLOR On new SSAS 2012 installation I am getting following error message: >Executing the query ... Query (1, 59) The [Class].[Name] dimension attribute was not found. Execution complete Dimension design is quite simple with two attributes: Dimension: Class Attributes: Class (key), Name Where Class has rigid relation to Name: Class -> Name Is it a way to redesign dimension and make query execution successful? Any help greatly appreciated!
ssasmdxmigration2012
1 comment
10 |1200

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

This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
happycat59 avatar image
happycat59 answered
There has been a number of architectural changes made to SSAS over the years. One of them has been with member properties - I think that is how [Class].[Name] was setup in your cube. I think that the MDX statement probably should look something like SELECT ([Class].[Class].MEMBERS ,[Class].[Name]) ON COLUMNS FROM [Cube] CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORMAT_STRING, FORE_COLOR
10 |1200

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

Bartoloz avatar image
Bartoloz answered
With this approach I am getting other error: > Query (1, 8) The Class hierarchy is used more than once in the Crossjoin function. Maybe there is another way with dimensions desing, some hacks, tricks? It's really simple dimension, maybe I should change something in the design?
10 |1200

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

happycat59 avatar image
happycat59 answered
Can you post your updated MDX statement. I am wondering why you are getting an error with the crossjoin function - I am not expecting any crossjoin to be used based on your original and my suggested query
1 comment
10 |1200

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

I used exactly the same query which you recommended me: SELECT ([Class].[Class].MEMBERS ,[Class].[Name]) ON COLUMNS FROM [Cube] CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FORMAT_STRING, FORE_COLOR The problem which I actually have is not how to rewrite the query but how to make the original query start working. I have migrated reporting application and it's rather impossible I would change it (the query as well) so I am trying to find the way to make the dimension readable by that query, unfortunately without success.
0 Likes 0 ·
happycat59 avatar image
happycat59 answered
I am not sure that you will be able to achieve what you want because of the architectural changes. From memory, in 2000, you added columns as properties on a dimension where you wanted them to be available in your cube but not to be used as aggregations. In SSAS 2012, you add the column as an attribute in the dimension and set a property of that dimension attribute so that it is not aggregated. From an MDX perspective, in SSAS 2012, you select both column in the same way.
1 comment
10 |1200

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

Thank you happycat59 for spending some time on my problem.
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.