SSAS Multidimensional: Are Your Indexes Processed?

If you are using SSAS Multidimensional and you use Process Update to process your dimensions, here’s something for you to try: run a Process Default on your cube. Does it finish in a few seconds? Then you’re ok. If it doesn’t, and it takes minutes or even longer then read on – you might have a problem that’s causing slow query performance.

One of the most common sources of query performance problems I see with my SSAS Multidimensional customers is unprocessed aggregations and indexes. If you run a Process Update on a dimension it may result in indexes and aggregations being dropped from partitions in your cubes; for more details on this, and why it happens, see this post:

https://blog.crossjoin.co.uk/2010/05/12/what-happens-when-you-do-a-process-update-on-a-dimension/

This classic post by Darren Gosbell explains how you can check if you have unprocessed aggregations on a partition:

http://geekswithblogs.net/darrengosbell/archive/2008/12/02/ssas-are-my-aggregations-processed.aspx

However, unprocessed indexes can also be a problem for query performance too. You can tell if the indexes on a partition are built by using the discover_partition_dimension_stat DMV. Here’s an example of how to use it for a partition in the Adventure Works database:

SELECT 
DIMENSION_NAME, ATTRIBUTE_NAME, ATTRIBUTE_INDEXED, 
ATTRIBUTE_COUNT_MIN, ATTRIBUTE_COUNT_MAX 
FROM SystemRestrictSchema($system.discover_partition_dimension_stat
        ,DATABASE_NAME = 'Adventure Works DW 2008'
        ,CUBE_NAME = 'Adventure Works'
        ,MEASURE_GROUP_NAME = 'Internet Sales'
        ,PARTITION_NAME = 'Internet_Sales_2003')

 

[For some background on running SSAS DMV queries, see here]

Here’s what the above query returns, a list of dimensions and attributes that are related to the partition:

image

If the ATTRIBUTE_INDEXED column shows false then indexes are not built for the attribute on the dimension. In this example no indexes are built at all on the partition; if I do a Process Index or Process Default on this partition, here’s what the DMV returns:

image

Now you can see the ATTRIBUTE_INDEXED property is set to true for most attributes. Note that there is an (All) attribute that is never indexed, and if you have set the AttributeHierarchyEnabled property to false or the AttributeHierarchyOptimizedState property to NotOptimized on an attribute, it will not have indexes built for it either (this is typically done to improve processing performance – see here for a few more details).

In a real-world cube it is likely that only a few indexes will be dropped on partitions as a result of a Process Update on a dimension, and even then this will depend on whether any changes take place in the dimension’s data, so you will have to look down the list of attributes returned by this DMV very carefully to see if ATTRIBUTE_INDEXED returns false when it should be returning true.

The solution to this problem, as several of the posts I’ve linked to above suggest, is to always run a Process Default on your cube as the last step in your processing schedule. A Process Default will process any object that is in an unprocessed state, so it will automatically rebuild any aggregations or indexes that are dropped as a result of Process Updates on dimensions.

6 thoughts on “SSAS Multidimensional: Are Your Indexes Processed?

  1. Hi Chris, and thanks for sharing this.
    I have a question: I just used your example query to get the ATTRIBUTE_INDEXED values for a partition. I noted they were all returning False. Then I ran a Process Default on the entire cube containing that partition. But I noted that the processing did not do anything with the specific partition in question. It did however build aggregations and indexes for other partitions in the cube:
    “Building the aggregations and indexes for the ‘xyz’ partition has started.”

    What mechanism determines which partitions will get its indexes/aggregations rebuilt in this scenario?

    Thanks
    Martin

    • Hmm, so the DMV still shows that all of the attributes for that partition are not indexed, even after a Process Default on the cube? A Process Default on the cube should have rebuilt any indexes and aggregations that need rebuilding. Does the partition contain a small amount of data? If there are less than 4096 rows in a partition, by default no indexes and aggregations will be built. If that’s not the case, what happens if you run a Process Index on the partition?

      • Hi again
        I have now tested with more/better data. And now I can confirm Process Default rebuilt indexes that before the processing were reported as ATTRIBUTE_INDEXED = False.

        I have a couple of follow-up questions:
        1. The definition for Process Default from Microsoft includes this sentence: “If you change a data binding, Process Default will do a Process Full on the affected object.”
        What does this mean more precisely? During Process Default, Is there a risk for full-processing a dimension that will bring down the cube? I assumethis is not the case, but just want to understand what that sentence means.
        Link: https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/processing-options-and-settings-analysis-services

        2. Is there any risk or downside at all running Process Default on a cube with the purpose of bringing indexes and aggregations up, in comparison to explicitly running Process Index on all affected partitions?

        3. Will a Process Default ever process any data from the relational source?

        Thanks again.
        Martin

      • To answer your questions:
        1) Changing the data binding means changing which SQL query or view the partition uses as its data source. There’s no risk a Process Default will ever un-process anything, it only ever processes objects that are in an un-processed state.
        2) If you do a Process Default at the cube level you will get better parallelism compared to looping over individual partitions and running a Process Index on them, so it’s likely to be a lot faster.
        3) Process Default will result in data being retrieved from the relational source if partitions or dimensions are in a totally un-processed state.

  2. Thanks Chris,
    yes it seems the partition I tested against was very small which is then most likely the reason for no index/aggregations be built. I will continue testing on more/better data the coming days and update here with my findings.

    Martin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s