Correct internal dataid order of dimension members - by Pete Griffiths

Status : 


Sign in
to vote
ID 695504 Comments
Status Active Workarounds
Type Suggestion Repros 1
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 James Stange, Jr on 8/18/2015 at 12:54 PM
Frankly issues and limitations like this are why I'm steering my team away from SSAS MD or Tabular. Microsoft just isn't interested anymore in supporting SSAS MD and extending the product to better scale for enterprise wide and support larger data sets. Even the most minor of updates as the one DaveBalsillie suggested despite having the potential greatly scale SSAS MD are met by Microsoft by only a yawn. SSAS MD has passed it's glory days.

1) I would like to have the ability to provide a "order by clause" to selected tables in the DSV.
2) Or, enhance the slice property to support Crossjoin or * operators so one can build a partition slice in the intersect of two or more attribute sets.

The work around suggest is an instable kludge and not the sort of foundation to build a enterprise wide reporting environment upon. Beside my company uses Oracle as the backend database and this kludge doesn't work on Oracle at all.
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.