Customer Order Line Model

[lmetodiev]

Let me show the following case:

There is a customer order line fact:

Customer Sold To Key (FK)
Product Key (FK)
Order Date Key (FK)
Requested Ship Date key (FK)
More Foreign Keys
Order Number(DD)
Order Line Number (DD)
Quantity
Price
Gross Amount
Discount Amount
Net Amount

Event 1. A new customer order was placed.
Event 2. Two days after that the price was changed.
Event 3. Five days after that the quantity was changed.
Event 4. Eight days after that the requested date was changed.

By Event 1 one new fact was created.
How could be processed Events 2., 3. and 4.?

Lachezar

[dwbi_rb]

Hi,
Not sure whether I got your query correctly, hence correct me in case I am wrong in assuming that you trying to find out when the fact table records would reflect the latest changes -
The fact table data would change to reflect the latest updates based on the ETL refresh. An event that triggerred a change in your fact table can be captured through the next refresh of your warehouse.

[lmetodiev]

Hi,

The question is what to do - to update the fact or to create an other fact?
For me a fact, once created must not be updated. All changes has to be created as addiotional facts. It means - behind eachevent to have a new fact. But I am not sure whether I am right.

Please advice.

[dwbi_rb]

Hi,
If you have a multi-dimensional structure consisting of conformed dimensions and facts, then there is no reason why you should create another fact table for the same business process.

We do not update fact tables, but the fact tables should represent the business facts. So, if there is some change that has invalidated an existing record, it should get reflected in your fact table automatically the next time your warehouse is refreshed.

It is changes to dimensions that we think of handling through Type1 / Type2 etc. The fact should always represent performance measurements generated by the organization's business activities.

There is no need to build another fact table for capturing identical business process measurements.

[VHF]

I don't see a problem with updating an existing fact record if the underlying data in the source system has changed. Unless you have a requirement to track the evolution of the order in your DW, just update the existing fact record for each change so the DW reflects the most current version of the truth. In a ledger-based accounting system you would never want to update an existing record, but that rule doesn't apply in the DW!

You could handle changes to aggregatable (summable) measures by writing an additional fact record for each subsequent event--the new fact record would contain the delta amount of the change (quantity, gross amount, etc.). However, this gives "the truth" only when the original plus any subsequent fact records for each line item are aggregated (summed) together. There is a danger of someone getting erroneous results if they write a query that doesn't include all the related records.

Changes to non-aggregatable measures such as price and requested date are even more problematic. It is possible to implement a 'Type 2 Fact Table' that tracks changes by adding BEGIN_EFFECTIVE_DATE and END_EFFECTIVE_DATE fields to the fact table, but once you go down that road you are locked into including those fields in every you query your write against that fact table for the rest of your life.

Keep your DW straightforward and easy to use... just update/overwrite/replace the original fact records when there is a change to the order!

[lmetodiev]

I very like the 'Type 2 Fact Table' idea, although I've never heart about sucht fact type. In this case I will have the current status of the fact and the whole history of changes.
In order to avoid the complex queries I can have for each commulative measure two measures - delta amount ( the amount change comparing with the previos state) and current amount (accumulated amount of all deltas).
The fact will have the following attributes:
Customer Order Line Fact
Customer Sold To Key (FK)
Product Key (FK)
Order Date Key (FK)
Requested Ship Date key (FK)
More Foreign Keys
Order Number(DD)
Order Line Number (DD)
Delta Quantity
Quantity
Price
Delta Gross Amount
Gross Amount
Delta Discount Amount
Discount Amount
Delta Net Amount
Net Amount
Valid From
Valid To

Additionaly I can have a roll based fact - Last State Customer Order Line Fact filtered only the actual facts (Valid To = 9999/09/09) which can be used for the queries based on the current status.

Any comment about this approach?

[VHF]

That design will work.

Note that because you could presumably have more than one change to an order line item in a given day, your Valid From and Valid To dates need to be a database date/time field which includes a time component. Using a far future high date for the Valid To field in the current record is correct. When a change event occurs, you will need to update the Valid To field of the current record to be the same as the Valid From date of the new fact record that you insert.

With this design, you have the ability to query the state of things at any given point at time. Kimball refers to this pattern as "Instatneous Balances" (and in fact it is used most commonly with a fact table containing account balances.) For example, the following WHERE clause would return rows that were current as of April 1st at 2:00pm:

WHERE #2009-04-01 14:00:00# >= Valid_To
AND #2009-04-01 14:00:00# < Valid_From

Make sure to get the >= and < right!

In addition to providing a view that is filtered to include only current facts, you could provide a view that also omits the delta fields. Chances are this simplified view would meet the majority of queries against your fact table. When slicing and dicing sales orders by customer, product, etc., you typically won't need either the non-current records or the delta fields.

[Kateryna]

Interesting...

Taking into account the complexity of the ETL process I'd design such fact table only if there is a real business need in the tracking of all historical changes. I can imaging quering an audit (log) table in an OLTP system to get all daily changes assuming the ETL runs only once a day.

I'd also create 2 Fact tables. One for the current data and the other for historical records. If the majority of the reports is on the current data then the huge amount of historical data will not effect performance. Or do some kind of clustering

[tropically]

In our DW we have a fact that tracks historical data by using the effective start and end dates (we call them the relavance dates ) since the business requirement was that they wanted to the see fact in the original state when it was created. Since we're in healthcare, they also want to see the progression of the fact.
So like VHF says, we have embedded the relevance dates into every query, since you always want the most relevant facts at that point in time.
Downside, is space, especially if your fact data is changing rapidly.

Like others have said before me, if you don't need to trace the changes, then don't.

[Sponsored content]