Once you’ve created a fact table, the final step in the dimensional modelling process is to add the dimensions which provide context to the fact.
When first created, a fact table carries the BUSINESS keys from its stage tables. By adding dimensions to the fact table, our objective is to replace the BUSINESS keys with SURROGATE keys from the dimension table.
Here is a screen shot of a newly created fact table. You’ll notice that it carries the business keys for customer, track and date.
We’re going to add the TRACK dimension in this example. In doing so, we will replace the track business key with the surrogate key for the dimension.
Select the Dimensions option from the fact context menu:
When you do this for a new fact table, you’ll see an empty list of dimensions. Press the Add link to add a new dimension to the fact table.
You’ll be prompted to select the dimension you want to join to the fact table. In this case, we select the Track dimension:
After pressing the Add button, we need to select the FACT column that joins to the DIMENSION business key. The field label is the name of the BK column in the dimension, the drop-down contains the list of columns from the fact from which a match should be chosen:
When you press the Change button, the new surrogate key will be automatically added to the fact table, using the same column name as the dimension key. This will help BI tools to automatically link tables. The left-side shows the dimension, and the right-side shows the business-key joins for the selected dimension.
Returning to the Fact list, we can see that the dimension key has been added to the fact, highlighted in red. But note the orange highlight, showing that we are still carrying the business key in the fact table. This column should now be deleted, it is no longer required in the fact.
We can use the Change link at the head of the column list to modify the columns. After deleting the old business key, and moving the track_key to the head of the column list (a common convention), our screen should look like this:
Having added one dimension to the fact, we would normally repeat the cycle for all other dimensions.
At the end of this process, our fact tables are ready for processing!