Example of a business process with more than 1 fact table

[Teto]

Hello,

The first step in dimensional modeling per Kimball is to select the business process. Most or all of the business processes I have seen in the book (Order, Shipment, Inventory ...) all have a single fact table.
Kimball also mentions that each process could spawn one or more fact tables*.

I am looking for an example of a business process with more than 1 fact table. If there are multiple fact tables at different grain levels, how would they be linked? Would one be the parent fact table and the other the child fact table?

Example
I have a sales order fact table and the shipping fact table.
The sales order fact table: Has details on the sales order header and sales order line.
The sales order shipping fact table: Each sales order line could be delivered as part of multiple shipments.

Since the shipping fact table requires all the keys from the sales order fact table. Should sales order fact table be made the parent fact table and the shipping fact table be made the child fact table?

I would appreciate any comments.


*: The Data Warehouse Toolkit - 2nd Edition: Pg 68

Thank you

[BoxesAndLines]

Take your sales example. You can easily imagine 2 fact tables for any sales process. The first fact table is the standard transaction fact table. The second fact could be an accumulating snaphshot fact table to allow easy reporting on time gaps between common sales events (e.g. order taken, order fulfilled, order shipped).

If you have a snapshot table for customer counts, you can easily add a customer install fact and customer disconnect fact to simplify reporting.

[Teto]

Thank you for the response.

So - It is one transactional fact table and the other is an accumulating snapshot table would represents the entire life of an entity.

By asking the above question, I was trying to get to find out the best practice for linking 2 dimensional models?

Say, If I have 2 fact tables. One is a sales fact table and the other is a sales shipping fact table. The relationship between sales and the sales shipping is 1 to n. One sale could be delivered in one or more shipping transactions. In such a scenario, how would you put together the dimensional model(s)?

1) One fact table for sales and one fact table for shipping - parent and child fact tables (Image #1)?
2) One fact table for sales and one fact table for shipping. The sales fact table is a subset of the shipping fact table (Image #2)?
3) One fact table for sales and one fact table for shipping (Image #3)
4) Other option

Please see attached diagrams

From the reading I did I understand that dimensional models can be linked through confirmed dimensions. In options #2 and #3 (Image #2 and #3), the item and date dimensions would be the confirmed dimensions. If I go with option #3, I would know the item being shipped, how about the customer and the employee for the item being shipped?



Image 1Image 2Image 3

[ngalemmo]

The best way to look at it is not as a single business process... the sales lifecycle is made up of many processes and events, and the dimensional model should reflect that.

There are three key processes: sales orders (and maintenance of such), fulfillment (shipping) and invoicing. Each one of these is its own subject area associated through conforming dimensions. The basic ETL processes should treat each separately as each has their own peculiarities (such as substitutions during fulfillment).

Since the business usually wants to be able to perform analysis on the full order lifecycle, you should then consider building an aggregate fact table that combines information from all three subject areas into a single fact table. But you do this only after you have established the base subject areas.

In a data warehouse I designed for a large CPG manufacturer, the base load processes spun off deltas, that were then aggregated and appended to the lifecycle facts.

[Teto]

Thank you ngalemmo for the reply .

When you have multiple business processes - How would you put a report together that has information in multiple processes?

Say you have 3 key processes (Sales Order, Shipping and Invoicing) each with its own subject area and own fact table. If you want a report that has information in the Sales Order fact table and Shipping fact table.
How would you put this together?

1) Pulling information from the 2 facts tables and joining them through confirmed dimensions?
or
2) Create an aggregate fact table for both the subject areas

If you go with option 2 - won't you end up with multiple aggregate fact tables?

Would appreciate your comments on the 2 options and any other approach to handling this issue.

Thank you

[ngalemmo]

You can do either 1 or 2.

If you go with an aggregate, as I have done in the past, it results in a single fact table, which is the whole point of doing an aggregate in the first place.
The aggregate is basically a union of the various facts along conforming dimensions.

Depending on the use of the aggregate, you may need to control how and when data is loaded. In my case, the aggregate was used to report sold goods, the business definition of which is after it has been invoiced. So, in my case, invoicing triggered pulling all related order lines and shipment lines from their respective facts and aggregating them into the target fact table.

[Teto]

Thank you ngalemmo, BoxesAndLines for your comments.

[Sponsored content]