Correct internal dataid order of dimension members - by Pete Griffiths

Status : 


Sign in
to vote
ID 695504 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 10/18/2011 6:18:56 AM
Access Restriction Public


When processing a dimension in SSAS all of the members are assigned internal ids (dataid) which is sequentially assigned based on the order that the members are loaded.  
This order is determined by the output of a select statement that is used to generate the distinct list of members and, if applicable, any related attributes.

This does not always result in a correctly ordered list of members which causes a problem when using partitions.  SSAS assigns a minimum and maximum id for the members contained in each partition, and if the members are not ordered correctly, these ids will overlap and the query engine will have to scan more partitions than is necessary.

The order of the dataids should be the same as the order of the members when the cube is fully procesed.  As defined by the OrderBy and OrderByAttribute properties of the Dimension Attribute in BIDs.
Sign in to post a comment.
Posted by DaveBalsillie on 6/4/2013 at 5:53 AM
Come on Microsoft. What's the point in collecting feedback if you won't act upon it.

The workaround for creating indexes to trick SQL Server in returning the rows in the desired order isn't very stable. The "side effects" would be negligable. If tricking SQL Server to return the rows properly "worked", then having the query engine add ORDER BYs would work even better.

This is a relatively simple change, that would result in much better data structures and faster query repsonse times. It deserves serious consideration, even if the change / control / responsibility is firmly in the hands of the cube developer.
Posted by Microsoft on 10/18/2011 at 7:05 PM
Hi Pete,

This change would have several sideeffects that include increased processing times, more complex processAdd, differences in ByTable processing, etc.

I believe this is an optimization that the engine can do internally for common cases without changing the queries that're sent to the data source, for the rest there's the workaround you mentioned or explicitly specifying the order by clause in the query definition. As such, we'll hang on to this suggestion as a performance improvement suggestion for future releases, based on customer feedback.

Thank you for your suggestion and your input in improving the product for our user community at large!

Ashvini Sharma
Program Manager
Analysis Services

Posted by Leigh Milton on 10/18/2011 at 6:27 AM
This seems like an excellent solution to a rather annoying issue.