Creating fact tables

Now that we have created all the dimensions, it is time to create a fact table that contains numeric values and the primary keys of the dimensions as foreign keys. The Sales Base and Customer Base data show that the Sales Base table holds many numeric values. Therefore, a fact table can be derived from the Sales Base table, which we name Sales, by following these steps:

1. Reference the Sales Base table and then rename the new table Sales.

We want to get ProductKey from the Product table. To do so, we can merge the Sales table with the Product table.

2. Click Merge Queries.
3. Select the ProductCategory, ProductSubcategory, and Product columns, respectively.
4. Select the Product table from the drop-down list.
5. Again, select the ProductCategory, ProductSubcategory, and Product columns.
6. Select Left outer (all from first, matching from second) from the Join Kind drop-down list.
7. Click OK:

Figure 6.37:...