Using DA with Existing Dimensions from another Data Warehouse

If you are a proponent of the Ralph Kimball school of Data Warehouse design, and the Data Warehouse Bus Architecture, you will subscribe to the concept of conformed dimensions. Put simply, the concept of conformed dimensions states that Data Marts within an Organisation should share common dimensions so that cross Data Mart/Enterprise/Business process analysis can be delivered. To support this concept Dimodelo Data Warehouse Studio contains a number of advanced features. To conform with an external dimension in another Data Warehouse/Mart, do the following.

• Define the external Dimension as an internal Dimension in the DA controlled Data Warehouse. This is so you can associate fact tables with it. When defining the Dimension, it is only necessary to define the Business Keys of the Dimension in the design.
• You will need to set the dimension to use a Smart Key (see the Defining a Dimension with Smart Keys section), and map the surrogate key of the external Dimension to the smart key of the internal dimension.
• If the external dimension uses a different standard for Surrogate key names and data types then on the advanced tab you will need to specify a custom surrogate key, and custom surrogate key data type.
• Set up a Connection Manager and Source system for the external Data Warehouse/Mart.
• Define a staging table for the external Dimension. Again only the business keys and surrogate key of the external dimension are required. Source the data from the external Data Warehouse/Mart.
• Define the ETL for the internal Dimension mapping only the business keys and Surrogate key as the smart key.

With this setup, a copy of the external Dimension is brought into the Data Warehouse. The ETL must be scheduled so the external Dimension is updated in the external Data Warehouse/Mart prior to the ETL that runs to stage the external Dimension. The Dimension in the Data Warehouse will have the same business keys and surrogate keys as the external Dimension, so when the Fact ETL is processed, Facts are associated with members in the Dimension with the same surrogate keys as in the External Dimension. When defining an SSAS Cube or tabular model add the Fact from the Data Warehouse and the Dimension from the External Data Warehouse/Data Mart to the model. Associate them via the dimension surrogate key, which should now match.