Multiple Cube vs Single Cube Approach

Shifting my focus to SSAS, so here is the post on whether to go for Multiple cube or Single cube.Well, if you ask me what's my opinion then I will say wait for the post to complete. 

First of all, let me put all the relevant information and then we will be in a good position to have a call on this.

Let me start briefing about the general terms that I will be using in this post like Database, Cube, Measures, Dimensions etc.

Database is the collection of data, in SSAS it can be collection of cube data.

Cube also termed as OLAP cube, is a data structure that helps you analyze & visualize your data in multiple dimensional way. It is set of related measures (or facts) and dimensions.

Measures are the numeric facts (or data).

Dimensions are the attributes, for which Measures are categorized.

So, what exactly is Single/Multiple cubes?
If your database contain only one cube, then you have a single cube wherein all the users refer to the same cube.
And, if your database contains more than one cube, each cube for specific users then its a part of multiple cube.




Single Cube Approach - Pros & Cons

• Users can easily fetch data from multiple measure groups even if they not related. Performance won't be degraded in cross quering measure groups.
• Security Management is tough here, for example, we want certain users to have the access to certain measure groups and not all. So, restrictions won't be easy.
• Less user-friendly, as every measure is present in a single location so navigating through long list of measures is a tough ask.
• Complexity is bound to increase with everything loaded into one.
• Change Management, any change made, how small it may be will result in whole cube structure change, that may effect users who are not associated with that change.

Multiple Cube Approach - Pros & Cons

• Performance can be a worry if user performs cross-querying based on multiple cubes.

Just to elaborate more on that, MDX provides a function to cross-querying 2 cubes using LookUpCube() function.

• Security Management is relatively easy, as you have cube for each specific usage (only certain users will be there). So, we can apply security restrictions on each cube.
• User-friendly, limited number of measures groups and that too related to each other.
• Less complex structure.
• Change Management is relatively simpler, changes will be applied to specific cube so others users won't be affected by that.

These were some of the differences that can help you plan your approach according to your requirement.

Now, my call on this will be:

Initially, go for the One cube approach,add unrelated dimensions and measures to a certain extent.

But, once you get the feeling that beyond this point, adding more things can create confusion and complexity, immediately move to Multiple cube approach