Creating fact tables

Now that we've 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. Looking at the Sales Base and Customer Base data shows us that the Sales Base table holds many transactions with numeric values. Therefore, a fact table can be derived from the Sales Base table, which we will call 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 dropdown list.
5. Again, select the ProductCategory, ProductSubcategory, and Product columns.
6. Select Left outer (all from first, matching from second) from the Join Kind dropdown list.
7. Click OK:

   Figure 6.37 –...