SSAS : Selecting facts with reference dimension

Introduction
In this blogpost i'll explain a problem i have at a customer. This problem is about relations between stars, facts and dimensions. To be more specific: selecting values of a fact with a (non conformed) dimension that is not directly linked with the fact but to another fact. In this blogpost i'll try to explain some backgrounds, the problem, a solution and finally some conclusions about what i have encountered during this investigation.

This is the second blogpost about this issue in a serie of 3. In this blogpost i'll describe a solution that is a better solution than i've described in a former post.

The problem
In this section i'll decribe the problem  in more detail. There is a wish from my customer about selecting values of a fact with a dimension that belongs to another fact. This is not a conformed dimension and i think this is the cause of the problem. As i'm reading the book "The Microsoft data warehouse toolkit, second edition" about conformed dimensions it says : "In order to combine information from two facts it is requirement to have a common dimension with the same keys. This is called drilled accross".

Suppose i've the following situation (SQL Server tables):

The Type dimension is not a conformed dimension because it is only shared by one fact and not more facts. A key of FactA is also present in FactB and therefore a dashed line is drawn. This will be the base for drawing connectin the Type dimension with FactB.

The diagram above typically descibes a situation as a 1:n relation between facts, for instance order (FactA) and orderlines (FactB). For this purpose a degenerated dimension table is created, which is a 1:1 dimension of FactA (order) and a 1:n with FactB (orderlines) A date dimension is also included.

Now, the problem is about selecting FactB records with the Type dimension.This can't be done by standard operating of the SSAS engine, unfortunately.

In SQL query:

The solution
On the MSDN forum i was pointed to usage of the reference dimension. The referenced dimension is one of the six different types of relationships as i can see on the MSDN page regarding Referenced dimension. The first lines about a referenced dimension: "A reference dimension relationship represents the relationship between dimension tables and a fact table in a snowflake schema design. When dimension tables are connected in a snowflake schema, you can define a single dimension using columns from multiple tables, or you can define separate dimensions based on the separate dimension tables and then define a link between them using the reference dimension relationship setting."



So i decided to implement a reference dimension in a different way than i have descibed in my former post. Now i've removed the materialized DD dimension table in SQL Server and use a direct relation from FactA to FactB.

The cube structure is shown in the screenshot below. As you can see now there is a direct relaltion between FactA and FactB. Field FK_FactA_DDkey is present in both fact tables FactA and FactB and is set as a foreign key in FactB.

Below a screenshot from the dimension usage tab of the cube. Here you can see the usage of the reference dimension relationship. The DimType is connected with the FactB because of the reference dimension FactA DD.

Below the "Define relationship" window that pops up when a you click on the box of the intersection of measure group and the Type dimension on the cubestructure tab.

In order to get the reference dimension relationship working, I had to add the FK Type Key to Fact A DD dimensio too.

And the results are:

Correct again!

Conclusion
In contrast with the first solution i've described in the blogpost titled: "SSAS: Selecting facts with different dimensionality" this is a neat solution without a extra physical SQL table, the FactDD dimension. Because of this implemented solution, dimensions from FactA can benefit of the reference relationship between FactA and FactB and therefore a powerful solution it is.

Advantages :

• No materialized table used and therefore:
• Less maintenance.
• Less storage.
• Less joining.
• Easy to build.

Disadvantages

• Perhaps that the cube structure is a bit different than the physical tables and less debug opportunities(?).

Greetz,
Hennie