Post reply

Processing attributes - Controlling sort order

  • September 5, 2007 at 4:42 pm

    #118576

    Hopefully someone has figured out a work around on this issue:

    I need to be able to control the sort order for attributes, when the dimension is being processed. The reason for this, is that MSAS assigns internal IDs to attributes as they are returned from queries, which it uses as part of it's access schema.

    When you build / design / process partitions, MSAS keeps track of the min/max values for each attribute in each partition. It can use this information to make intellegent choices about which partitions to query.

    If you can sort the attributes (order by on the SQL statement) with some regard to the partition design, you can optimize the query paths.

    However, I can't find any way to add order bys to the dimensional queries. I haven't been able to add them to view definitions or named queries, since SQL Server won't allow nested ORDER BY statements.

    Note: in my case, I simply want to order by the primary key columns (ID columns). But there shoudn't be that restriction. You can't rely on a "natural sort order" being returned from the database, due to the additional join logic MSAS forces in (via the DSV definitions).

    Any suggestions out there?

  • September 9, 2007 at 6:26 pm

    #732383

    Hi Dave,

    I'm probably looking at your problem too simply, but there's an MS tutorial on defining dimension attribute sort order here (open in IE): ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqltut9/html/67dacf68-9ab7-4524-8698-844d0f6e6c6d.htm

    Whether or not this achieves your goal of optimising the query path I'm not sure, but maybe it'll help.

    Sam

  • September 9, 2007 at 6:35 pm

    #732386

    Thanks for your post Sam, but no, that's not what this post is all about. (I'm very aware of sorting diemnsion members.)

    What thsi involves is sort the SQL statement sent by AS to the relational engine. For anyone who might happen on this post, what I'm trying to do is optimize the min/max values in each partition.

    For anyone out there trying to optimize their partitions with medium to large dimensions (i.e. > 200k items) I'd be interested in comparing notes. The cube I'm processing is approx 1.5 billion rows, so getting the query access path right is important.

    Thanks again Sam for the interest.

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply