June 19, 2017 at 3:44 am
Hi All, I have a cube that seems to take rather longer than its server-mates to process, by quite a margin.
It is a little bigger and more complex than the others but it takes 5 hours to process while the others take 10-30 minutes.
I ran a trace on its activity and most of this "extra" time seems to be spent in processing aggregations rather than fetching data.
The advice I've seen so far seems to indicate fiddling with Memory settings on the SSAS instance in order to get it to do more parallel processing but I'm not very experienced in this area and I don't want to break anything!
Can anybody advise me of some "things to try" in order to speed this along a bit?
Many thanks
Paul J
June 19, 2017 at 6:32 am
pjames 76441 - Monday, June 19, 2017 3:44 AMHi All, I have a cube that seems to take rather longer than its server-mates to process, by quite a margin.
It is a little bigger and more complex than the others but it takes 5 hours to process while the others take 10-30 minutes.
I ran a trace on its activity and most of this "extra" time seems to be spent in processing aggregations rather than fetching data.
The advice I've seen so far seems to indicate fiddling with Memory settings on the SSAS instance in order to get it to do more parallel processing but I'm not very experienced in this area and I don't want to break anything!Can anybody advise me of some "things to try" in order to speed this along a bit?
Many thanks
Paul J
A few things to go through before you start changing instance-level memory properties.....
1) Get BIDSHelper (https://bidshelper.codeplex.com/wikipage?title=Aggregation%20Manager&referringTitle=Documentation%5B/url%5D)
2) Use the Aggregation Manager to determine redundant and duplicate indexes. Run these tools a couple of times.
3) Determine if the indexes that are left are being hit. I have attached a profiler trace template for you to do this. Run the trace for an appropriate period of time and then see which ones are being used. If they are not being used, remove them. Be brutal.
These 3 steps should reduce your aggregations and allow for faster processing.
Another option is to split up your processing strategy so that you first do a ProcessData on your partitions and follow this with a ProcessIndex. After the ProcessData the cube will be available for users and the aggregations will process in the background. The caveat here is that queries may be slower whist the aggregations are processing.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply