Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

SSAS: One giant cube or many small ones?

When using SQL Server Analysis Services (SSAS) to build a cube, you may start out with one fact table, but then decide to add another fact table.  You are then faced with a decision: Do you add that fact table to your existing cube and have multiple measure groups, or do you create another cube, each with a single measure group?  An example would be if you create a cube with financial data, and then a request is made to have a cube with operational data.  Should you put the financial data and operational data together in one cube, or have two cubes, a financial cube and an operational cube?  Here is a list of the benefits and disadvantages of both approaches:

One giant cube

Benefits

  • A role based security model can be defined once for the database and has to be re-visited only when a new cube or dimension (that requires Dimension Data Access) is created
  • Requires just one SSAS project in BIDS.  The shared dimensions can be defined once and linked to associated measures groups. Code maintenance is all in one place
  • If using dashboarding tools like Performance point, SharePoint etc., common shared dimensions ensure that filters for dashboards can be built once and re-used across all cubes because they share the same structure, keys and value columns
  • If you ever need to work with data from two fact tables in the same query or calculation, or if you think you might ever need to in the future, you should go with the single cube approach.  The two options for cross-cube querying, linked measure groups and the LookUpCube MDX function, should be avoided

Disadvantages

  • Having one database for all cubes means modification to one cube or dimension would require a re-deployment of all the cubes in the database irrespective of the fact nothing has changed in other cubes
  • Depending on the nature of change, sometimes all cubes in the SSAS database may have to be reprocessed after a deployment.  Structural changes to dimensions especially the shared ones will require all the cubes in the database to be reprocessed after deployment

Many small cubes

Benefits

  • Having one cube per database gives the flexibility to customize dimension attributes and hierarchies that is relevant to a specific audience.  For example, attributes that are not relevant to a target audience can be removed
  • This method has the advantage that only affected cube databases have to be deployed
  • Because there is only one cube in the database there is no dependency on shared dimensions
  • Having multiple, smaller cubes may result in faster query performance than one large cube in some cases, especially if your fact tables have very different dimensionality
  • Maintenance can be easier and less disruptive with multiple cubes: if you need to make changes to a cube while users are querying it, you might end up invalidating users’ connections and dropping caches. With one cube the chances of this disruption affecting more users increases
  • It’s easier to scale out with multiple cubes: if you find your server is maxing out, you can simply buy another server and distribute your cubes equally between the two.  With a single cube approach you end up having to look at (admittedly not that much) more complex scale-out scenarios like network load balancing
  • Facilitates multiple developers in the SSAS BIDS environment (this is a big deal in a team environment).  With one cube, only one developer at a time can be working on the cube.  With many small cubes, separate developers can be working on each cube

Disadvantages

  • Role based security model has to be defined for every database
  • Requires a SSAS project in BIDS for each cube.  Shared dimensions have to be created once on each project.  If sufficient care is not taken during development, it could break the conformity of dimensions.  Maintaining consistent attributes and names, hierarchy and levels, key and name columns, sort order could become a development and maintenance challenge
  • In this method, you have to replicate similar filters for different cubes

More info:

SSAS: One Database, many Cubes Vs. One Database per Cube

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

One Cube vs Multiple Cubes

Comments

Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...