So you have several cubes to build and perhaps wondering what is the best way to build and deploy your cubes. Should you build all the cubes within the same Analysis Service database or create one database per cube? The following guidelines might help you to choose the best approach to build and deploy your SSAS cubes.
| One Database, many Cubes | One Database per Cube | |
| Reusability of Shared Dimensions | Having all cubes in one database facilitates reuse of common dimensions like Client, Organisation Hierarchy, and Date/Time across all related cubes. The attributes and hierarchies of shared dimensions are consistent throughout all cubes in the database. | Having one cube per database gives the flexibility to customize dimension attributes and hierarchies that is relevant to specific audience. For e.g. attributes that are not relevant to a target audience can be removed. |
| Security | 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.
Dimension Data Access defined on shared database dimensions by default is inherited by all cubes in the database. This provides a consistent security model. If the dimension data access has to be customized for a specific cube in the database then the default access can be overridden by defining data access on cube dimension. |
Role based security model has to be defined for every database.
Dimension Data Access defined on shared database dimensions is usually sufficient unless dimension security has to be defined on a role playing dimension. |
| Development and Maintenance | Requires one SSAS project in BIDS. The Shared dimensions can be defined once and linked to associated measures groups. Code maintenance is in one place. | Requires one SSAS project in BIDS for each database. 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. |
| Deployment | 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. At this moment SSAS Deployment Wizard does not allow deployment of individual cubes within the database. | This method has the advantage that only affected cube databases have to be deployed. |
| Processing Options | 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.
SSIS packages are usually required to mimic SSAS processing schedule to process specific cubes within the database taking into account the sequence of processing dimensions, measure groups, partitions and indexes. Care should be taken not to process the shared dimensions in full process mode as it could take all associated cubes offline. |
Because there is only one cube in the database there is no dependency on shared dimensions. |
| Dashboards | 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 | In this method, have to replicate similar filters for different cubes |
As you can see, while there are benefits of having all cubes in one database there are scenarios where having one cube per database might be just as helpful.
Benny Austin 
Hi,
nice Articel. But a note relating to:
Dimension Data Access defined on shared database dimensions by default is inherited by all cubes in the database.
–> I created a fresh Sample with AdeventureWorksDW2012 and defined dynamic security on database shared dimension level.
Unfortunately I recogniced that the security definition has no affect on the cube, even when it´s not a role playing dimension.
Then i copied the same MDX Statement into the Cube Level and it worked perfectly.
–> Maybe i made a mistake, but I think their is a probelm in heritage of dynamic security.
Please be careful and check the security Settings properly, when setting them on database dimension level.
Antoine