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:
[sourcecode language=”text” padlinenumbers=”true”]
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’)
[/sourcecode]
[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:
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:
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.
Discover more from Chris Webb's BI Blog
Subscribe to get the latest posts to your email.
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.
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
I am not sure this reply turns up at the right place in the thread, but anyway, thanks Chris for your answers!
I found the ROLAP model + columnstore indexing is very fruitful. You won’t spend time on processing (which is very annoying in BigData era). Also, if you can use smart triggers for updating the DWH in real time, you will get the really real time OLAP. Why do we still process OLAP cubes these days?
I have used ROLAP more frequently in the past few years, and while I agree it works well in a lot of cases there are still too many scenarios (usually with complex calculations) where performance is much worse than MOLAP.
You should turn your calculations into pre-calculations. That’s the key.
I know what you mean, but in many cases it’s not possible to pre-calculate what’s being done in MDX.
Hi again Chris
Do you know if Process Default (with index and aggregation being rebuilt), supposed to “lock” a cube from being queried during its duration? This is at least what I am currently experiencing with a SSAS MD database. My processing sequence is:
– Process Update dimensions
– Process Data partitions
– Process Default Database
While the Process Default Database is running, cubes seem to completely stop responding. For example, in SSMS Clicking Browse on a cube gets stuck in the “Opening file” dialog. This is of course not acceptable when processing during business hours. (We currently do not have a dedicated processing server unfortunately).
Any ideas?
Thanks,
Martin
It won’t lock the database until the end of processing – have you read this post? https://blog.crossjoin.co.uk/2017/03/18/ssas-2016-locking-improvements/ The other possible cause is the fact that after a Process Data there are no indexes, so any queries will be very, very slow to run and if multiple users try to query the cube it could end up overloading the server. Are you doing the Process Update/Data/Default as a single transaction or as three separate processing operations? You should always do processing like this as a single transaction.
Thanks a lot Chris for taking the time to reply, and for the tips you provided.
We are on SSAS 2016 already so the locking-improvements should be available to us. The processing is done via three different SSIS-tasks, executed in sequential order. Do you mean that we should try and merge them all into a single XMLA-command, containing a mix of dimension processing and facts processing, to get them under one single transaction? If so, don’t we lose control of in which order things get processed (for example, we intentionally Process Update Dimensions first and after that the partitions). Or maybe you mean something else entirely? 🙂
Yes, you should do them all in a single XMLA command – and I believe SSIS allows you to do this without writing any XMLA. This article has a good overview of the options: https://www.mssqltips.com/sqlservertip/2994/configuring-the-analysis-services-processing-task-in-sql-server-2012-integration-services/
Thanks Chris, will check it out!
Martin