Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nicklarge
Starting Member
40 Posts |
 Posted - 2012-03-28 : 10:37:52
|
| HelloI have an SSAS project. It has 7 cubes of varying sizes. In this project there are 2 cubes with around 300 million fact rows. We import about 7 million rows a week. With our hardware it takes about 3 hours to process the data (ProcessData). I have aggregations that I built around this that use default as the setting in the Design aggregations, for all aggs. This produces about 20 aggregations at about 15%. When I run the process indexes, it can take about 6 hours to process the indexes themselves. I manually specified the default/full/none/unrestricted behaviors and ended up with over 150 at 3%. Should I even bother with aggregations if I am hitting this issue? 6 hours+ seems a long time to process the idx's on one cube, especially when I have 7 cubes to process (although not all are as large as this).Thanks, Nick. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-28 : 12:10:25
|
| it depends on how users use the cube. What you can analyse is what would be analysis users will be interested on. Based on that you can set it to do only require aggregations and prestore it. also make sure you double check and do aggregations only on required measure groups. By default it identifies lots of columns to be aggregatable so you've to analyse and include only required columns. For adhoc queries from users you can always set cube to fetch and calculate the measures on the fly so far as its not too frequent.Also one other thing you can consider is to apply partitions over cube data. By doing so, you dont have to process whole cube always. It might be one or two or recent partitions that would change always. You can check for deltas and make sure only affected partitions would get processed. You can also apply proactive caching in partitions to reduce the latency further.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
nicklarge
Starting Member
40 Posts |
Posted - 2012-03-29 : 14:13:57
|
| Thank you Visakh.On the subject of processing partitions for incremental's, suppose I process dimensions that are used by the cube, using Process Update, will it invalidate the cube partitions? If I only want to process partition C, but before I do I will need to update the dimensions (such as customer, Sales Territory dimensions) will partitions A and B be invalidated, and so need to be reprocessed regardless of whether I only need to process partition C ? Thanks, Nick. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-29 : 14:58:49
|
| Process Update is for dimensional processing to pick up delta not for calculating aggregations in measures.yep. because you process the measure group partitions you need to process all dimensions.sorry didnt understand what you meant by partitions A and B be invalidated. ideally nothing will change for restrospective data. Any change in dimension attribute will not affect partition data as reference key (surrogate key) is still the same. A change of association for a measure means its a new item and should find its way to latest partition ideally unless you regard it as type 1 and need retropective update------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nicklarge
Starting Member
40 Posts |
Posted - 2012-03-30 : 11:03:04
|
| Thanks Visakh.Let me clarify a little more what I meant by Partitions A,B and C.Supposing I have a cube called Sales. This cube has 3 partitions, one for sales 2010, one for sales 2011 and the last one for sales 2012. I then have new data for the dimensions which I load by doing a "Process Update" on the dimensions. Lets suppose that the new fact data is for Week 10 of 2012. the partitions containing data for 2010 and 2011 are unchanged - there are no modifications whatsoever. Once I do the "Process Update" on the dimensions, will the partitions for 2010, 2011 and 2012 automatically become unavailable at that time with the expectation that they should be reprocessed fully before I can browse them? If so, how should I be doing this processing in order to make sure that the dimensions and partitions are up-to-date without having to do a "Process Full" on everything?Thanks again,Nick. |
|
|
|
|
|
|
|
|
SSAS Aggregations