New Data Mart: Create a New SSAS Cube or a Perspective in an Existing Cube?

Overview: Some things to consider when deciding if you want to integrate new data within an existing cube (and, optionally, use a perspective) or create a new separate cube.

New Data Mart

Recently we created a new data mart (subject area) within an existing data warehouse.  The new data mart had several new facts, several new dimensions, as well as relationships to existing (conformed) dimensions such as Date & Customer.  I had more new dimensions than usage of conformed dimensions.

Using the existing SSAS database project wasn’t up for discussion – we need to reuse the conformed dimensions after all.  I also used the same Data Source View (DSV).  What wasn’t so clear immediately was whether to create a new cube for the subject area, or use the existing cube.  A perspective in the existing cube could certainly help simplify things, but we actually chose to create a new cube.  Here’s why…

Reasons We Chose to Create a New SSAS Cube

This isn’t an exhaustive list, but it’s the things that were meaningful for our project.  Be sure to check out Chris Webb’s blog entry for more things to consider (like security).

• We were dealing with a separate data mart – the new subject area had no overlap between measure groups & no expected need to do cross-analysis of existing measure groups with the new measure groups.  Put another way, we expect the reporting & analysis to be independent.  (Although linked measure groups are not ideal, that is in our back pocket if the need should come up in the future.)
• Shield the existing cube from any data quality issue / cube processing errors we may experience during the early going (in our situation, the data mart was based upon a brand new Oracle module which was heavily customized & not yet in Production itself).
• Less regression testing upon deployment (we were on a very short timeline).
• The existing cube was already a bit large.
• Potential for improved query performance (we expected a high volume of data).
• Facilitates multiple developers in the SSAS BIDS environment (this is a big deal in a team environment).
• Allows for a separate Agent job to process the dimensions and the measure groups (although we do still have a dependency on the conformed dimensions).  This flexibility is a good thing in our situation, since it’s a different subject area which can be run faster on its own, and on an independent schedule.
• Simplification for end users, without the need for a perspective.

Any additional thoughts or differences of opinion?  Leave me a comment!

Finding More Information

Chris Webb’s blog – One Cube vs Multiple Cubes

Comments

[Jason Thomas]

I was going to post an old forum conversation that I was part of - http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/681e59bd-93ca-4a91-9f26-8ed96e825553 but then it is already mentioned in Chris' blog :)

[Samuel]

You've nicely laid out the thought process of a common problem although i'm in favor of single cube in most cases. Reason for my choice is cross analysis (which isn't a requirement in your case), but in may case there was no easy way for users with pivot table to compare new business and sales if they are two separate cubes.

[Dattatrey Sindol (Datta)]

Nice article Melissa !!
Not quite sure on the last point "Simplification for end users, without the need for a perspective". How does it make a difference to the end user, whether they are offered a separate cube or a separate perspective ?

[Melissa Coates]

Datta, I was thinking from the cube designer's angle with that item I listed - ie, no need to create or maintain an additional perspective. I agree the users shouldn't care as long as they are trained on how to access what.

Thanks for reading!
Melissa

[Dattatrey Sindol (Datta)]

Thanks Melissa for clarifying.