Adding dimension with Referenced relationship

After having completed the Cube Wizard the new cube structure will present itself in a cube designer window in Visual Studio. The cube designer contains several tabs, one of them being the Dimension Usage tab.

Contrary to what we might think, it seems that the cube is using only two of the three dimensions. The Customer dimension is currently not on the list.

The reason is that the Cube Wizard was not able to correctly identify the relationship between the fact table (the Sales Invoice Line table) and the dimension table (the Customer table). As you may recall, these two tables are directly linked to each other, but are linked through an intermediate table: the Sales Invoice Header table.

To fix this, we will have to manually add the Costumer dimension.

Pick the Customer dimension from the list.

But since the Customer dimension table has no direct relationship to the Sales Invoice Line measure group table, we will need to help the system to establish this relationship. An empty box in the intersection between a dimension and a measure group means that a relationship has not been properly established and that the dimension will be useless together with the measures of that measure group.

Click the grey button to the right of the Customer dimension in the Dimension Usage view to start establishing a relationship.

The relationship type we are looking for in this case is the so-called Referenced relationship type. A referenced relationship type has to be used when the dimension table and the measure group table is not directly related to each other.

When looking at the table relationships e.g. in the Data Source View we can see that the Sales Invoice Header is the intermediate dimension table between the Customer table and the Sales Invoice Line table.

When defining a referenced relationship we must point out an intermediate dimension (not an intermediate dimension table!). With the current setup we are lucky to have one intermediate dimension based on the Sales Invoice Header table: The Customer Posting Group dimension. If we did not have this dimension we would have had to create a dimension based on the Sales Invoice Header table – just in order to be able to make the referenced relationship.

Furthermore, after having selected the intermediate dimension, we must point out the primary key attribute (reference dimension attribute) of the referenced dimension (the Customer dimension) and the foreign key attribute (intermediate dimension attribute) of the intermediate dimension (the Customer Posting Group dimension).

Notice that both of these attributes must be defined in their respective dimensions beforehand. If they are included for the sole purpose of making this relationship they will often be hidden as well.

The Materialize option is recommended to be kept enabled as it will then potentially create aggregations on the intermediate dimension attribute – potentially improving overall performance.

Notice the special symbol that indicates a referenced relationship type via the Customer Posting Group dimension.