Overview:  A quick tip re: getting the underlying data model correct when a bridge table is involved, to avoid errors in SQL Server Analysis Services.

Level:  Assumes familiarity with bridge tables, as well as SSAS Dimension Usage relationships.

SSAS Error Received

When you have a complex set of data being modeled for addition into the data warehouse, sometimes the star schema joins are not as simple as we’d like them to be.  My first choice is always to join facts to dimensions at the surrogate key level (i.e., the lowest level of granularity).  However, recently we had a challenging set of data where we needed to model the data a little differently.  What we had done in the relational tables (i.e., the underlying data warehouse) was rejected by SSAS.

      

The SSAS deployment error:

Errors in the metadata manager.  The ‘Dimension 2’ many-to-many dimension in the ‘Fact 1’ measure group requires that the granularity of the ‘Dimension 1’ dimension is lower than that of the ‘Many-To-Many Bridge’ measure group.

The Underlying Data Model

To avoid the error shown above, Join A needs to be at a lower level of granularity than Join B:

If Join A is at the surrogate key level (i.e., the level of granularity the Dimension 1 records are stored at), you shouldn’t run into this issue. 

Why wouldn’t Join A be at the surrogate key level, you ask?  SSAS does let you create a relationship using a non-key granularity attribute (i.e., using a join that’s higher than the lowest level of detail).  A common example of this is joining on the non-key attribute of Quarter when your Date dimension is stored at the day level, because the facts aren’t available at the day level. 

SSAS Dimension Usage

The star schema shown above translates to the following relationships in the SSAS Dimension Usage: