Perhaps everyone else knows this already, but this tip has saved me loads of time so I thought I’d post it anyway…
I’m always breaking cubes, or rather making changes to them that put them in an unprocessed state (usually intentionally, but quite often by accident). If cube processing takes a long time this can be quite annoying: it’s always good to have a cube to browse so you can see the effect of any changes you’ve made. You can of course use a view to limit the number of rows in your fact tables which makes processing much faster, or switch the fact tables used in the dsv, but that’s not always practical – a lot of the time I’m working with cubes where the partitioning has been done using a query to slice the fact table and you don’t want to go changing all that SQL – and you can’t simply process a single partition in the cube. Or so I thought. I found out last week if you do a Process Structure (which takes seconds) on an unprocessed cube you can browse it but no data is there; if you subsequently do a Process Full on one of the partitions then you get a cube with only that partition’s data in it.
While we’re on this topic, it’s also worth mentioning that if you redesign the aggregations on your measure group/partitions, you just need to do a Process Index to make them available which is much faster than doing a Process Full.
Hi Chris,
This is a very useful tip, however it seems to no longer work in SSAS 2012 (haven’t tried it in 2008). I made a full process of all my cube dimensions, a structure process of the cube itself, and a full process of a few select partitions. I expected the data from the processed partitions to show up, but using the SSMS cube browser, I get “No rows found” whatever I do. Even when I try to browse one of the dimensions without any measures, no rows show up.
It seems to me that there may be some hidden state inside the cube, that tells the browser that it contains no rows, even though one or more of my partitions have been processed. What do you think?
Best regards, Daniel
Disregard my previous comment. Everything works the way you described. I don’t know what I did wrong the first time.
Best regards, Daniel
I noted that in a SQL 2008 cube, even when there is one partition in each measure group processed, I am able to browse the cube. Is this expected? I always thought all partitions in all measure group had to be in processed state?
Note: I did not do a process structure as well.
No, not all partitions have to be processed for you to be able to browse the cube, but somewhere along the line the structure must have been processed too.
i have 2 fact tables which are so big that it takes a long time to process. So I partitioned them based on quarters and average partition size is 230MB. Thjis has increased the processing time from 1 hour to 28 minutes.
I would like to know is there any way in which I can process only the recent partitions and keep the cube in processed state for the business users. I created separate aggregations for the lat quarters and tried processing the partition for the last quarter, but it put the cube in partially processed state (I think that isn’t the right way to go)