SSAS : Select Facts with Reference and Many2Many relationships

Introduction
In this blogpost i'll explain how to connect two starschemas with each other and how to setup the cube in SSAS  in case you want to use the dimensions of the two facts selecting the measures in both facts. In other words, suppose i've two facts that have a 1:N relation (eg. Orders and OrderLines) and there are a couple of dimensions (eg. Customer, Department, Date, Product and Category). And, sometimes because of naming conventions (?) a referenced dimension relationship is automatically created between  a 1 fact and a N fact. This will enable selecting the measures of the N fact with the dimensions of the 1 fact. Using a Many-2-Many (M2M) relationship will enhance selecting measures in the 1 fact with the dimensions of the N fact.

The blogpost is ended with leasons learned part and conclusions.

The problem
Let's elaborate this example to a lab situation. Suppose i've the following situation:

There are two starschemas: Orders and Orderlines (OrderDetails) and they have a 1: N relationship. OrderDD is the connecting dimension that actually is a 'fact dimension' in SSAS (not materialized but could be if you want). Physically there is an Order_id attribute in the Orderlines tables present to enhance the Fact dimension relationship in the cube in SSAS.

The Order Fact has three real dimensions : Customer, Department and Date and Order Lines has a Category and a Product dimension.

Mostly, the problem is that by default relationships are not created automatically, meaning that using the dimensions doesn't have effect on the facts of both starschemas (when you browse the cube). There are additional steps needed to enable this. This will be explained in this blogpost.

There are two possible directions to influence the measures on both facts:

• Selecting measures of the N fact (eg. Orderlines) with dimensions  (eg. Customer, Department or Date) of the 1 fact (eg. Orders).
• Selecting measures of the 1 fact (eg. orders) with the dimensions (eg. Categroy or Product) of the N fact (eg.Orderlines)

In the solution described below i'll start with an initial setup where there is no relationship, only the Fact  dimension OrdersDD, then i'll add an extra relationship 'Referenced Relationship' and finally the end solution with the 'Referenced Relationship' and the 'Many2Many relationship', together.

Solution
In this solution i've identified 3 steps:

1. Possible initial situation.
2. Reference dimension relationship solution
3. Endsolution with Reference and Many2Many relationship

 1) Suppose you build a cube on the described starschema, the following structure could be the result. Could because because of namingconventions a 'reference dimension' relationships is sometimes automatically gernerated (see also step 2).

If the cube is browsed you can observe that the selection with dimension "Customer" selects the measures of the orders fact but not the orderlines measures. The OrderLines count is not controlled by the dimension "Customer".  For every dimension value the same (total value) is shown. This is not possible by default.

The other way around, selecting measures of the Orders fact by the dimensions that belongs to the  Orderline fact shows also the same problem. For every dimension value the same (total value) is shown.

2) In step 2 of the solution the reference dimension relationship is set up with the customer, Department en Date dimension. The OrdersDD functions as an intermediate dimension that directs the cube to relate the information from the Orders to the OrderLines starschema.

Below a screendump and it shows that the dimensionvalues has an imnpact on the measures on both starschemas : Orders and Orderlines.

Selecting values in the Orders starsschema with the product dimension still shows incorrect values.

3) In this final step i've added the Many2Many relationship to the cube. The Product and Category dimension now has a Many2Many relationship with Order.

The dimensions of the Orders starschema has an impact on the measures of OrderLines.

And now the dimensions of the OrderLines starschema has an impact on the measures of  the Orders starschema.

Conclusion
In this solution i've shown how to connect two starschemas (with a 1:N relation) and how to tune the cube in a way that all dimensions has an impact on the measures on both starschemas. The steps that are needed are:

1. Create a common dimension (physical or by a fact dimension relationship)  that reprensent the 1:N relation between the starschemas.
2. Create a reference dimension relationship for the dimensions that belong to the 1 Fact (Orders).
3. Create a Many2Many relationship for the dimensions that belong to the N Fact (OrderLines)

Greetz,
Hennie