SSAS Cube Deployment
What I miss with SSAS cube deployment (regardless of MSSQL Server version) is all sorts of statistics where one could track and easily see how fast cube or partition is being processed.
Besides talking to several MVP's (three in general) and reading piles (well, four of five) of books regarding this issue, i still couldn't see where the problem was hiding.
In general four basic components are related when it comes to SSAS Cube process and deployment:
- Building Indexes - this time depends on SSAS service/server
- Building Aggregations - also this time depends on SSAS service/server
- Building partitions - also this time depends on SSAS service/server.
Both are available within deployment process with guided GUI. Neat and nice. But the third one was almost mysterious when i realized it had a huge impact on cube process and deployment.
- Reading the query on the source table / database - this time depends on Sequel server.
Which is so obvious, that i never thought about it. But it makes sense, that processing the cube is just querying the data and preparing aggregated data for faster browsing. So how to diminish the process time with the last one.
Fairly simple - optimize your fact table and all relating tables (star/snowflake) for all the dimensions and hierarchies to read and retrieve the data as fast as possible.
Of course, one can also lose couple of dimensions and hierarchies and cube process will also be dimished. As well as delete couple of million rows :)
Facts: I have couple of OLAP cubes (each holding cca 100mio records) and it took me on daily basis up to 1,5 hour per cube for processing and deploying. But keeping in minds the last rule, I did some query and table optimization and process time decreased dramatically. Now i need less then 30 minutes per cube to process and deploy. And it saved me in general speaking up to one hour in the morning, so fresh data can be delivered one hour earlier.