Conformed Dimensions

The Kimball method of Data Warehouse design describes a Data Warehouse Bus Matrix Architecture, and the concept of conformed dimensions.

Put simply, the concept of conformed dimensions states that Data Marts within an Organisation should share common dimensions to enable cross Data Mart/Enterprise/Business process analysis.

To support this concept Dimodelo Data Warehouse Studio contains a number of advanced features. These features are best described by an example. In the following image are two data warehouses, Data Warehouse A (DWA) and Data Warehouse B (DWB). DWA could be another Dimodelo Data Warehouse Studio data warehouse or a data warehouse built outside of Dimodelo Data Warehouse Studio. DWA contains a Client Dimension. DWB wants to associate its facts with the Client dimension from DWA. To achieve that, DWB needs to stage the Client Dimension from DWA and replicate it in DWB, so its available for look up when processing DWB facts. However, it’s not necessary to replicate all the attributes of the Client dimension in DWA. Only those attributes required for the fact lookup are required.

The aim is to have a relationship between the DWB facts and the DWA client dimension via the Client surrogate key in the DWA Client dimension. Therefore it is important that the DWB replica of the DWA Client dimension retains the same surrogate keys.

Step 1 – Staging

The first step is to bring the DWA Client Dimension into the staging layer of DWB.

To achieve this:

• Set up a Connection Manager and Source system for the DWA data warehouse database.
• Define a staging table for the DWA dimension. Usually it’s only necessary to stage the business keys and surrogate key of the external dimension. In this case Client_sKey and Client ID. In some cases it may be necessary to stage additional columns to facilitate the lookup between DWB facts and the DWA dimension.

• Define the extract from DWA client dimension to the staging table.

Step 2 – Dimension Transform

The second step is to define the DWB replica Dimension. It is important that the surrogate key name, data type and content matches the DWA dimension.

Create a replica Dimension. When defining the DWB Dimension attributes, only add the DWA Business Key as an attribute, and set it as the business key in DWB. Don’t add the surrogate key as an attribute.

Set the dimension to use a Smart surrogate Key. See the ‘Defining a Dimension with Smart Keys’ topic for more information about how to do that. This will allow you to map the surrogate key that you have staged from the DWA Client dimension, to the surrogate key of the DWB replica Client dimension.

Create a transform for the DWB dimension with the staged DWA dimension as its single primary staging table.

Match on business key.

Transform:

Advanced:

Map the surrogate key of the DWA dimension to the smart surrogate key (on the advanced tab) of the DWB dimension. Again – see the ‘Defining a Dimension with Smart Keys’ topic for more information about how to do that.

Step 3 – Fact Transform

The Fact transform is exactly the same as any other fact transform. The Dimension will exist in DWB with the required surrogate keys and business keys. When you are defining the staging sources of the fact, you will need to have data that you can use to look up the dimension based on business key (in this example Client_ID). That may not always be straightforward, because the Client dimension has come from a different data warehouse and therefore likely a different data domain. In this case it may be necessary to stage additional attributes of the dimension to facilitate lookups by facts.

Semantic Layer

The two data warehouses ‘join back up’ in the semantic layer. When defining an SSAS Cube or tabular model add the Fact from the DWB and the Dimension from the DWA to the model. Associate them via the fact foreign key to dimension surrogate key, which will correctly because the fact will have derived its foreign key from the DWB Client dimension, which will have the same surrogate keys as the DWA Client dimension.

ETL Schedule

There is now a processing dependency between DWA and DWB. The DWA Client Dimension must be processed prior to the staging process in DWB executing.  You can do this via workflows and jobs that are discussed further in the batch management lesson.