SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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


  • 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


  • 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


  • 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


  • 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

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


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

Loading comments...