Blog » By James Serra on James Serra's Blog
Improving cube processing time
As your SSAS cube gets bigger, cube processing time will become a problem. This is especially true as more and more companies want cube processing during the day instead of the usual off-hours time when no one is using the cube. Partitioning the cube can help to reduce the processing time. So can using a different processing strategy than a Process Full.
The biggest benefit of partitioning is that it allows you to process multiple partitions in parallel on a server that has multiple processors. This can greatly reduce the total cube processing time. Note that partitioning requires the enterprise version of SQL Server 2008 (view version differences).
Regarding the best possible processing strategy, I suggest the following steps:
1. Process Update all the dimensions that could have had data changed. Depending on the nature of the changes in the dimension table, Process Update can affect dependent partitions. If only new members were added, then the partitions are not affected. But if members were deleted or if member relationships changed (e.g., a Customer moved from Redmond to Seattle), then some of the aggregation data and bitmap indexes on the partitions are dropped. The cube is still available for queries, albeit with lower performance (with Process Update flexible aggregations and indexes on related partitions will be dropped). This means after a Process Update you need to do a Process Index on the partitions (see step #3).
2. Process Data the partitions that have changed data (which are usually the most recent partitions). Of course, the smaller the partition, the better, so try to use daily partitions instead of monthly or use monthly partitions instead of yearly.
3. Process Index for the rest of the partitions (the partitions that have not changed). Note instead of doing step #2 and step #3 separately you could just do a Process Full (which does a Process Data and Process Index behind the scenes). However, the best practice is to do a Process Data and Process Index separately instead of a Process Full, because: it is a bit faster, it reduces the stress on the server, it makes data available to end-users sooner (while a Process Index is happening, users can still query cube), and you can just run Process Index if Process Data completes but Process Index bombs. As another option, instead of doing a Process Index, you can do a Process Default, which will evaluate the state of all the partitions: for one’s that had a Process Full it will ignore them; for one’s that did not process, it may or may not touch them (it will if they were part of a dimension and the aggregations were dropped, in which case it will rebuild those aggregations and indexes via a Process Index but it won’t reprocess the data); for one’s that had a Process Data it will build out the aggregations and indexes (Process Index).
There is one more option to consider, a Process Incremental, which can be used in place of all the above steps. But you can only do a Process Incremental if there are new records for the partition (no updates or deletes), as a Process Incremental never deletes or updates existing members, it only adds new members. A Process Incremental internally creates a temporary partition, processes it with the target fact data, and then merges it with the existing partition. Process Incremental doesn’t drop aggregations and indexes. Note a big point of confusion is that when you choose “Process Incremental” in the GUI, it really translates it into a Process Add, and it only works for partitions and dimensions (yet for some reason the Process Incremental option does not show up on the GUI for dimensions - you need to fire it using XMLA). Even though the GUI has the Process Incremental option available for Cube and Measure Group, it scripts those to work only on the partitions.
I prefer to have my cube do a Process Full occasionally to mop up any deletes or updates to measure groups, and to just generally ensure it’s exactly the same as the tables it’s built from.
Other options for increasing processing speed is to improve I/O: More/Faster spindles, short-stoke the disks, use solid-state disks.
And other ways to improve processing speed: add faster CPU, add more memory, use remote partitions, use a dedicated SSAS server, changing Processing thread pool (used for allocating worker threads for processing jobs), use Jumbo Frames, increase the network packet size, use multiple NICs.
Since a picture is worth a thousand words, here are all the process options depending on the type of object:
More info:
Can you query a SSAS cube while it is processing?
Use cube partitions to improve Analysis Services performance
Handling And Tuning Really Large Analysis Services Cubes
Video Tuning Analysis Services Processing Performance
SSAS: Process Incremental – What it is & isn’t
SSIS Design Pattern – Incremental Loads
Processing Options and Settings
Analysis Services 2005 Processing Architecture
Different Kinds of SSAS Processing in simple words…..
Video PASSMN May 2010 – SSAS Design & Incremental Processing (blog)
Implementing Analysis Services Process Add Functionality
ProcessAdd on Large Dimensions
Thanks to my father who told me regarding this
website, this website is genuinely remarkable.
Thanks to my father who told me regarding this website, this website is genuinely
remarkable.
Simply want to say your article is as amazing.
The clearness in your post is simply great and i can assume
you're an expert on this subject. Well with your permission allow me to grab your RSS feed to keep updated with forthcoming post. Thanks a million and please carry on the rewarding work.
Simply want to say your article is as amazing.
The clearness in your post is simply great and i can assume you're an expert on this subject. Well with your permission allow me to grab your RSS feed to keep updated with forthcoming post. Thanks a million and please carry on the rewarding work.
It's awesome to pay a visit this site and reading the views of all colleagues concerning this post, while I am also eager of getting experience.
Great weblog right here! Additionally your site so much up very
fast! What web host are you the usage of? Can I get your affiliate link in your host?
I wish my website loaded up as quickly as yours lol
Great weblog right here! Additionally your site so
much up very fast! What web host are you the usage of? Can I get your affiliate link in your host?
I wish my website loaded up as quickly as yours lol
Woah! I'm really loving the template/theme of this blog. It's simple, yet effective.
A lot of times it's very hard to get that "perfect balance" between superb usability and visual appeal. I must say that you've done a superb
job with this. Also, the blog loads super fast for me on Firefox.
Exceptional Blog!
Woah! I'm really loving the template/theme of this blog. It's simple, yet effective.
A lot of times it's very hard to get that "perfect balance" between superb usability and visual appeal. I must say that you've done a
superb job with this. Also, the blog loads super fast for me on Firefox.
Exceptional Blog!
Hey are using Wordpress for your site platform? I'm new to the blog world but I'm trying to
get started and set up my own. Do you need any coding knowledge to make your own blog?
Any help would be really appreciated!
Hey are using Wordpress for your site platform? I'm new to the blog world but I'm trying to get started and set up my own.
Do you need any coding knowledge to make your own blog? Any help would be really appreciated!
I used to be recommended this website by my cousin.
I am not positive whether or not this put up is written through him as no
one else know such certain approximately my difficulty.
You're incredible! Thanks!
I used to be recommended this website by my cousin.
I am not positive whether or not this put up is written through him as no one else
know such certain approximately my difficulty.
You're incredible! Thanks!
Hey there! Do you know if they make any plugins to assist with Search Engine Optimization?
I'm trying to get my blog to rank for some targeted keywords but I'm not seeing
very good success. If you know of any please share. Kudos!
Hey there! Do you know if they make any plugins to assist with Search Engine Optimization?
I'm trying to get my blog to rank for some targeted keywords but I'm not seeing very good success.
If you know of any please share. Kudos!
First off I want to say terrific blog! I had a quick question in which I'd like to ask if you don't mind.
I was curious to know how you center yourself and clear your thoughts before writing.
I've had trouble clearing my thoughts in getting my ideas out there. I truly do take pleasure in writing but it just seems like the first 10 to 15 minutes tend to be lost just trying to figure out how to begin. Any suggestions or tips? Kudos!
First off I want to say terrific blog! I had a quick
question in which I'd like to ask if you don't mind. I was
curious to know how you center yourself and clear your thoughts before writing.
I've had trouble clearing my thoughts in getting my ideas out there. I truly do take pleasure in writing but it just seems like the first 10 to 15 minutes tend to be lost just trying to figure out how to begin. Any suggestions or tips? Kudos!
Asking questions are really nice thing if you are not understanding something
fully, except this paragraph presents nice understanding even.
Asking questions are really nice thing if you are not understanding something fully, except this
paragraph presents nice understanding even.
It's nearly impossible to find knowledgeable people for this subject, however, you seem like you know what you're talking about!
Thanks
It's nearly impossible to find knowledgeable people for this subject, however, you seem like you know what you're talking about!
Thanks
My spouse and I stumbled over here by a different page and thought I should check things out.
I like what I see so now i am following you. Look forward to exploring your
web page for a second time.
My spouse and I stumbled over here by a different page and thought I should check things
out. I like what I see so now i am following you. Look forward to exploring your web page for a second time.
Attractive section of content. I just stumbled upon your site and in accession
capital to assert that I acquire actually enjoyed
account your blog posts. Anyway I will be subscribing to your feeds and
even I achievement you access consistently fast.
Attractive section of content. I just stumbled upon your
site and in accession capital to assert that
I acquire actually enjoyed account your blog posts.
Anyway I will be subscribing to your feeds and even
I achievement you access consistently fast.
Hi, Neat post. There is an issue with your site in web explorer,
would test this? IE still is the marketplace leader and a huge component to people will pass over your wonderful writing because of this problem.
Hi, Neat post. There is an issue with your site in web explorer, would test this?
IE still is the marketplace leader and a huge component to people
will pass over your wonderful writing because of this problem.
Hi my loved one! I want to say that this article is awesome,
great written and come with approximately all significant infos.
I'd like to look more posts like this .
Hi my loved one! I want to say that this article is awesome, great written and come
with approximately all significant infos. I'd like to look more posts like this .
What's up every one, here every person is sharing such experience, so it's pleasant to read this weblog, and I used to
go to see this website daily.
What's up every one, here every person is sharing such experience, so it's pleasant to read this weblog, and I used to go to see
this website daily.
Thank you for another magnificent post. Where else may anybody get that type of information in such a perfect approach of writing?
I have a presentation next week, and I'm at the look for such info.
Thank you for another magnificent post. Where else may anybody get that type
of information in such a perfect approach of writing?
I have a presentation next week, and I'm at the look for such info.
Fantastic goods from you, man. I have understand your stuff previous to and you're just extremely fantastic. I really like what you've acquired here, certainly
like what you're saying and the way in which you say it. You make it enjoyable and you still take care of to keep it smart. I can't wait to read far more from you.
This is actually a great site.
Fantastic goods from you, man. I have understand your stuff previous to
and you're just extremely fantastic. I really like what you've acquired here, certainly like what you're saying and the way in which you say it. You make it enjoyable and you still take care of to keep it smart. I can't wait to
read far more from you. This is actually a great site.
I know this web site provides quality dependent articles
or reviews and additional stuff, is there any other website which offers these data in quality?
I know this web site provides quality dependent articles or reviews and additional stuff, is
there any other website which offers these data in quality?