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.

Finding More Information

Chris Webb’s blog – One Cube vs Multiple Cubes