When\u00a0I initially migrated Analysis Services\u00a0database from 2000 to 2005 version, in the new database\u00a0I\u00a0created single\u00a0cube with all measure groups\u00a0in it. At the time I believed that was a right way to do. This is quote from one of my favorite\u00a0books “The Microsoft Data Warehouse Toolkit with SQL Server 2005 and the Microsoft Business Intelligence Toolset” page 322, chapter 7:<\/p>\n
“The best practice in Analysis Services\u00a0 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.”<\/p><\/blockquote>\n
But later I found about Microsoft\u00a0recommendation “Avoid having more than 15 measure groups in cube”. I posted question on Analysis Services MSDN forum<\/a> and got reply from Greg Galloway<\/a> about his experience – about 15% performance boost after splitting and about 10% performance improvement after reducing cubes MDX script code.<\/p>\n
<\/p>\n
This post is about my experience in this area, as I finally\u00a0spent some time splitting SSAS cubes with many measure groups into multiple cubes. This specific performance test was done on the cube that had 23 measure groups originally and was split into 11\u00a0cubes with 1-3 measure groups in each. Cube size was about 6GB (sample data set) and size sum of smaller cubes did not change much after restructuring. Original big cube had about\u00a02600 lines of\u00a0MDX script. After I split cube into smaller cubes, cube MDX script size went down to 350-1100 lines per cube. There is a big chunk of code related to time intelligence functionality that I had to repeat in each cube.\u00a0\u00a0<\/p>\n
Here are\u00a0notes from my tests:<\/p>\n
\n
- After splitting cubes, I saw consistently about 20-30% improvement on most queries from the\u00a0larger measure groups.<\/li>\n
- Smaller cubes had bigger performance improvement after they were split off from the big cube. On the smaller cubes I saw performance improved by as much as 100-300%.<\/li>\n
- I used linked measure groups to access measures from different cube. That worked great and it is very easy to setup. For queries that use calculations based on linked measure groups I saw inconsistent performance behaviour. Some of the queries were 20-30% faster after the split, but few others where 10-20% slower.<\/li>\n
- There is a known bug related to linked measure groups described in this KB article: http:\/\/support.microsoft.com\/kb\/940160\/<\/a>. I had problems kind of related to this bug. But in my case\u00a0excluding cubes from the project and then adding them back in different order fixed this problem.<\/li>\n
- After splitting cubes I enjoyed the fact that related MDX scripts got smaller and it was easier to find code associated with specific measure groups.\u00a0\u00a0<\/li>\n
- One thing that I do not like about cube split was the fact that I have to repeat the same time intelligence related code in each cube.\u00a0It is going to be more difficult to maintain that code from now on.<\/li>\n<\/ul>\n
So,\u00a0it was definitely worth doing this restructuring and if you have cubes with many measure groups, consider splitting them.<\/p>\n