Add Referenced dimension

It is possible in Analysis Services to reference a dimension through another dimension. This way you can avoid creating a snowflake dimension or having to add the attributes to the data warehouse table as lookups.

1. Add the dimension you want to reference to the cube dimensions.

   In this example we want to reference the Countries dimension to the Sales fact table through the Customers dimension.

   

   Notice the added dimension is labeled 'Shell Dimension'. This is because no relationship to the fact table has been set up yet.

   

2. Set up reference to fact table through the Customers dimension.

   

   We are referencing the Countries dimension to the fact table factSales through the Customers dimension on the dimension level Country in both dimensions.

   

   Always select Materialize. Otherwise, the referenced dimension will be materialized on query time. This will have a huge negative impact on queries if the dimensions are large.

3. The dimension will now be shown 'Referenced dimension' and no longer 'Shell dimension' because the dimension now has a relation to the fact table.