December 10, 2011 at 10:05 am
Let me preface this by saying I am production support DBA. SSAS is not my area of expertise, at all. So forgive me if I butcher some terminology and feel free to correct it.
Some recent code that was pushed into prod resulted in our processing times going from 2 - 3 hours to 20 hours. We do a full processing of the entire SSAS database very night. From the relational side I could see the problem. The new code was inefficient resulting in memory and I/O pressure. We are working to resolve these issues by partitioning the cube and creating appropriate indexes.
However, each time we processed in test and the first time we processed in prod, the entire thing took 4 hours, max. We have never been able to get back to those times. I think I came across the reason why. Each processing attempt in test was preceded with deploying an update version of the SSAS database. The same would be true for the first run in prod.
It appears to me, from watching and tracing the processing process, that if the database is "new" the Dimensions are processed first and then the Measure Groups (cube) are processed. All subsequent processing, for a database that already exists, results in Dimensions and Measure Groups being processed concurrently.
So this would make sense as to why subsequent processing takes longer and beats on the relational side much harder.
Is this how things work? Can I control this behavior for all subsequent processing?
Thanks!
December 19, 2011 at 12:01 pm
mnDBA (12/10/2011)
Let me preface this by saying I am production support DBA. SSAS is not my area of expertise, at all. So forgive me if I butcher some terminology and feel free to correct it.Some recent code that was pushed into prod resulted in our processing times going from 2 - 3 hours to 20 hours. We do a full processing of the entire SSAS database very night. From the relational side I could see the problem. The new code was inefficient resulting in memory and I/O pressure. We are working to resolve these issues by partitioning the cube and creating appropriate indexes.
However, each time we processed in test and the first time we processed in prod, the entire thing took 4 hours, max. We have never been able to get back to those times.
Did the processing schedule change between now and back when you were seeing 4hr processing times? For example, is the cube now being processed during the day and before it was being processed after hours?
What about the amount of data being processed... is it the same amount of data between now and back when you were getting 4hr processing times? It is common in BI projects to deliver a solution to the business users involving a cube with 2 yrs of historical data only to have them change their minds and request 5 yrs.
Did any of the memory settings change on the AS server?
Is AS located on the same box as the DB engine?
mnDBA (12/10/2011)
I think I came across the reason why. Each processing attempt in test was preceded with deploying an update version of the SSAS database. The same would be true for the first run in prod.
It is common to see this when making changes to the AS databases. When working in BIDs and making changes to the SSAS database, the cube may need to be deployed before it can be subsequently processed.
mnDBA (12/10/2011)
It appears to me, from watching and tracing the processing process, that if the database is "new" the Dimensions are processed first and then the Measure Groups (cube) are processed. All subsequent processing, for a database that already exists, results in Dimensions and Measure Groups being processed concurrently.
There are many ways to configure processing. Do you know what option is being used each night? Probaly "Process Full".
AS is pretty good at building a dependency tree so that it knows what needs to be processed in what order...dimensions always come before measure groups (think about how you load data into a normalized OLTP database with foreign key constraints). If an AS database changes requiring a full re-deploy, then everything has to be processed. However, if no changes are made and regular processing over-night processing is taking place, AS can better detect what needs to be processed and what doesn't and should finish in less time.
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply