Home Dashboard Directory Help
Search

Correct internal dataid order of dimension members by Pete Griffiths


Status: 

Active


9
0
Sign in
to vote
Type: Suggestion
ID: 695504
Opened: 10/18/2011 6:18:56 AM
Access Restriction: Public
1
Workaround(s)
view

Description

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.
Details
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!

Regards
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.
Sign in to post a workaround.
Posted by Pete Griffiths on 10/18/2011 at 6:20 AM
You can create indexes on your source table to cover the fields in the select distinct query. This will give a correctly ordered set of results back.