0

Our cube is probably bigger than it needs to be, with about 20 measure groups, and between 20 and 100 partitions in some of them - but that's the way it is.

What we did notice is, that SSAS adds an ORDER BY on some queries it generates when processing the partitions, and it does not add it to others.

We cannot find out what influences this behaviour. There is no obvious setting on the partitions, or the measure groups. We can also not trace it back to the view it queries to process partitions.

Any idea what makes SSAS decide when to load "ordered" and when to load "whatever" is delivered?

For reference: SQL Server is Version 2012 SP 4 Edition is Enterprise. Analysis Services in multidimensional model, all partitions are MOLAP. The views we load are based on "regular" tables, no columnstore indexes. Some are indexed views, but we see ordered and not ordered selects against those indexed views.

Thanks for any idea!

Ralf
  • 538
  • 1
  • 6
  • 17

1 Answers1

0

I though this would never happen, but I think I posted a question, and can finally myself deliver the answer. Well... here it is:

In SSAS, it is a good idea to create measures that are aggregated with DISTINCT COUNT in their own measure group. More information on this can be found here, and on several other pages.

Well, this is about all there is to say as answer: It is these measure groups and their partitions, that get an "ORDER BY" attached to the sql loading them. The sort will use the field that is DISTINCT COUNTed as criteria.

The consequence of this might be to have an index on this column, and to take in all other table fields that SSAS needs to hold references to dimensions as definded in "Dimensions Usage" tab as included columns, if the clustered index is not usable to support the sort.

Ralf
  • 538
  • 1
  • 6
  • 17