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.
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.
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.