SQL Server Performance Forum – Threads Archive
One Strong Convincing Argument
Hello all I am trying to convince my End User who also happens to be VP of Data Analysis that when we build a cube it is better to split it up if we have 35 Dimensions instead of putting them all into one. This cube will have 5 Measures. I have recommended it to be split into three. What is one Strong Convincing Argument(or more than one) that I can use. I have already told him if is a Maintenance NIGHTMARE, Loading of the Fact Table would be a NightMare.I have shown his articles which say that it is a Big Mistake to have more than 10 dimensions. But he keeps on insisting that let us try it out before we run into problems. He says let us add one by one and see how far we can go? Ofcourse he wants to be able to play with all numbers side by side. For all that matters, the Cube will probably build up and even get processed and after which he will say — hey we got it, there is no need to split it up. I do it find it hard to Optimize the Cubes even at 10%. But when we Process it at 0% Optimization there seems to be no problem what so ever for him to access via MS Excel, so he insists that we can Process it at 0% OPtimization. I need a Strong Argument here. Please Help.
JPG
You are on the right track a cube with more than 10-12 dimensions is a poorly designed cube to start with. How large is your fact table and dimensions? Im assuming not to large if your VP is getting good response times with no aggregations set. But if your cube will need aggregations in the near future (optimize your cube) to boost response time you will most likey produce Data Explosion as a result of those 35 dimensions.
Raulie
hp
Thanks Raulie for answering this.
Our Fact table has about 24 Million Rows (Coud grow to about 36 Million by End of the Year). I guess the biggest Advantage that we have is that NONE of the Dimensions have Levels.
(Not even the Time Dimension).
JPG
How about your dimensions, how many members does your biggest table contain? Raulie
hp
My members I am assuming the number of rows in each Dimension Table Most Dimension tables contain between 5 and 10, I have about 3 tables that probably go to about 15 members. JPG
]]>