SSAS Cubes – Speeding up Queries and Performance

So, you have a SSAS 2005 (SQL Server Analysis Services) Cube, and its not performing how you want it to. What do you do?

The first thing to look at is your attribute relationships (http://technet.microsoft.com/en-us/library/ms174557.aspx). Make sure you don’t have redundant relationships (AS should warn you as well) By default when you create a cube, and go through the wizard, only the default attribute relationships are set up. What this means is that all attributes are related to the key attribute by default. If you have related attributes, you want to set up those relationships!! This will help in a couple of ways. First, when creating hierarchies, it won’t warn you that you don’t have relationships set up. Second, your aggregations will actually have a better design, Analysis Services will be able to take into account the relationships and your aggregations should hopefully rock!

What’s next? Look at your reports and queries off the cube. Can you redesign them? Are you running some crazy query? Here is a good tip for using sp_create_plan_guide to influence query plans.

My suggestion is to run SQL Profiler and see how your cube is being accessed. Are the aggregations being used? Are things being read from cache? Use the BIDS Helper to take a look at your aggregations and see the row counts and make sure everything is working as you have planned.

Ok, what else? Cache Warming.(http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!994.entry) After running Profiler, or even setting up something automated to capture the queries being ran into another table, you can use this info to “warm your cache”. Basically when you process your cube, run the most frequently ran queries automatically so then the next time users run them, it will pull from cache.

Balancing your cache, aggregations and storage design modes is something you seriously want to dig into. Should you be purely MOLAP? Or MOLAP with Proactive Caching? Or do you want to go purely ROLAP or all the different designs in between the two? You want to minimize data latency.

Some of these things often get overlooked when first designing a cube, which then could make it tough to do once you have things deployed to end users. Also, SSAS 2005 isn’t the best at letting you tweak some of this stuff, I watched a presentation on SSAS 2008 by Donald Farmer and things look A LOT better in SSAS 2008, which I cannot wait for!

But that is a post for another day 🙂