Creating Data Warehouse

2
votes

I am creating a data warehouse by using a star schema. I successfully build all the dimension tables, but I'm kind of stuck at the fact table. I am in a need to make a Sales table as Fact table. It has SalesKey, OrderKey, ProductKey and etc... Every order is a sale so each order will have a unique SalesKey however each sale will have more than one product.

What would be the best was to build this table?

Should I create something like that

SalesKey OrderKey ProductKey
-------- -------- ----------
s1         o1        p1
s1         o1        p2
s2         o2        p1
2
databasedata-warehousedimensional-modelingstar-schema

2 Answers

2
votes

In general when you design a starschema it is preferred that each dimension is single valued for each fact record (that is having a 1:M relation between fact and dimension).

The trick is to include an ORDER-LINE dimension so that 1 order (=1 sale) can contain many order lines. Each order-line then contains 1 product.

So basically you will be using a snowflake schema where the facttable is linked to the ORDER-LINE dimension in a 1:M relation. The ORDER-LINE dimension is then linked to the PRODUCT dimension in a M:1 relation.

With this the original problem having a M:M relation between the Salesfact and the PRODUCT dimension has been solved with the ORDER-LINE dimension as a bridge table.

2
votes

I would add that order items/lines can be tricky. There are multiple ways to handle it.

Add a column "order line item" or "transaction control id" to the fact table.

This will allow you to have SalesKey, OrderKey, ProductKey all on your fact, with an "OrderLineItem" degenerate dimension key, which is often the transaction control number or order line number from the source system.

One issue that you may encounter when using this method is when you have order-level measures that don't exist at the order-line (tax, cashier id, etc). Kimball's preferred approach is to distribute these measures down to the order line if at all possible.

Here's a good article by Kimball on degenerate dimensions: http://www.kimballgroup.com/html/designtipsPDF/DesignTips2003/KimballDT46AnotherLook.pdf