July 3, 2015 at 1:54 am
All,
I have a large(ish) SSAS Cube (~20 measure groups & ~15 dimensions).
An overnight job processes up the most recent partition for each measure group, and takes about 30 minutes. (full re-process is done at the weekend).
Recently I've made some enhancements to the cube - creation of three new measure groups, and one dimension - and since then the overnight processing time has shot up to over 2 hours (i.e. about 400%).
I'm struggling to explain why such a small increase in the number of measure groups has had such a negative effect on the cube processing.
After some Googling, most advice in this area suggests performance issues with the server etc. - however in my case nothing has changed with the box, the only change has been to the cube structure itself.
Does anyone have any suggestions for what might cause performance deterioration in a cube simply by adding some new measures?
July 7, 2015 at 7:51 am
Interestingly, sdome analysis of the logs suggests that the extra time is being eaten up during the "Process Indexes" phase.
Each measure group is partitioned by week, so index processing was pretty quick before (~15mins). Now takes over an hour.
Struggling to explain why a few relatively light changes to the cube have cause this.
Anyone seen this problem before?
July 7, 2015 at 8:25 am
Just taking a stab at this. Do the three new measure groups share a lot of dimensions in common with the measure groups that were already in the cube?
July 7, 2015 at 9:36 am
Yes, they do. They represent slightly different measures, but use data from the same fact/dim tables in the DWH.
Do you think they could be conflicting with each other?
July 7, 2015 at 11:34 am
That's a possibility. Normally a measure group will have it's own fact table because that fact represents a certain level of granularity based on the table. And when I say normally, I've never seen it differently, but wouldn't say there couldn't be exceptions. Is the data being aggregated somehow?
July 9, 2015 at 9:58 am
Hi Ron,
Thanks for the advice. I have now resolved the problem.
First - agree it is highly odd that we have two measure groups looking at the same fact table. The problem is poor design in the DWH (outside my control). We have a fact table that contains both end-of-day totals (non-additive) and daily changes (additive) - highly confusing to the user, let alone me. It is a terrible design, so I've created two views of the fact table - one for each - and the cube has a MG built on each view.
As it turns out, this was a red herring anyway...
Solution... when I created the new measure group, I added a measure based on a fact table column that had never been used in the cube before. I realise now that this column wasn't included in the index of the table, so SSAS was having to perform a full table scan when processing, whereas it had previously used the index.
So after rebuilding the index on the DWH table to include that column, the time taken to process the cube returned to normal. Phew.
July 9, 2015 at 12:30 pm
I added a measure based on a fact table column that had never been used in the cube before. ... [T]his column wasn't included in the index of the table, so SSAS was having to perform a full table scan when processing, whereas it had previously used the index.
So after rebuilding the index on the DWH table to include that column ...
So one of the two was an aggregation. That would be one example I could understand. Not really following the situation you're describing. For example, unsure what you mean by "the index of the table." Would you be able to provide more details?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply