Do you put all measure groups into single cube or split them between cubes?
Q: Do you put all measure groups into single cube or split them between cubes?
I have a SSAS 2005 database with 2 cubes where one has 35 measure groups and another 15 measure groups. My total SSAS database size can be anywere between 20 and 60GB.
From the previous SSAS design recomendations, I understood that it is recommended to have less cubes. This is quote from one of my favorit books "The Microsoft Data Warehouse Toolkit with SQL Server 2005 and the Microsoft Business Intelligence Toolset" page 322, chapter 7:
"The best practice in Analysis Services 2005 is to define a single cube for a database"... " You are still permitted to create multiple cubes in a database, but you shouldn't". Instead, create a single cube with multiple measure groups."
Now last week Microsoft released "OLAP Design Best Practices for Analysis Services 2005". To my surprise I found there recommendation to use multiple cubes as much as possible. I understood that having multiple cubes increases performance.
So, my question now is how important for me is to redesign my database structure? Are we talking here about 5% increase in performance or 50%? Any explanation on why having single cube design penalizes performance?
A: Answer from furmangg:
The best I've seen on this topic is:
I can share tests I did on one cube which had 15 measure groups and 30 cube dimensions...
Cutting the number of measure groups in half (and not changing the number of cube dimensions) had no performance impact. But cutting the number of cube dimensions in half gave me about a 15% performance boost. This, in my mind, was not significant enough for the benefits you get of putting everything in one cube.
So I agree with Thomas' statement that, "You should put measure groups in the same cube if they have related dimensions. That means dimensions that are usefull for analyzing the data in the measure group(s)."
Side note... I had a cube with a 1500 line calc script. Cutting the size of the calc script in half boosted performance by 10% even though the stuff I cut had no impact on the MDX queries I was performance testing. So that's probably another nugget when deciding on whether to put everything in one cube.
Answered by furmangg at MSDN forum:
Note: Read also this blog post.