What to do when facts change? Update the fact table with update and deletes? or use SCD type 2?

I have a Quotation and Purchase Order Management system that is largely in 3NF.

See http://forum.kimballgroup.com/t3174-instead-of-order-management-this-dimensional-modeling-is-more-for-quotation-and-purchase-order-management

I am trying to add in schema that conforms to Kimball method to assist in better reporting.

I have situations where during the back and forth between the sales rep and the customer, Quotation Line Items are changed repeatedly in terms of quantity and sometimes they are removed altogether.

What do I do with such highly frequently changing facts? As I expect my fact tables to be QuotationLineItem and PurchaseOrderLineItem.

Hi, what are your reporting requirements? Do you just want to be able to report on the current position of a Quotation or be able to track the complete history of how the Quotation changed? If you want the history then is an e.o.d. snapshot good enough or do you need to track all the changes that happen within a day?

nick_white wrote:Hi, what are your reporting requirements? Do you just want to be able to report on the current position of a Quotation or be able to track the complete history of how the Quotation changed? If you want the history then is an e.o.d. snapshot good enough or do you need to track all the changes that happen within a day?

There are several reports needed. None of them is history or tracking changes. Though it is said to be a nice to have if we can display any changes to a single Quotation.

The most difficult report is this one:

+---------------+---------------------------------+--------------------+--------------------------+-------------+
| ProductNumber | QuotationLineItemNumber | QuotationNumber | PurchaseOrderNumber | Status |
+---------------+---------------------------------+--------------------+--------------------------+-------------+
| PRD-1 | 1 | Q1 | PO1 | PO received |
| PRD-2 | 2 | Q1 | PO1 | PO received |
| PRD-3 | 1 | Q2 | NA | Awaiting PO |
+---------------+---------------------------------+--------------------+---------------------------+-------------+

These are my source tables in a 3NF database schema and they are not exhaustive.

Tables include but not limited to:

- quotation_line_items
- products
- quotations
- prices
- purchase_orders

The relationships are

- `products` is many-to-many to `quotations` via `quotation_line_items`.
- `products` is one-to-many `prices`
- `purchase_orders` is one-to-many to `quotations`

If you don't need to track history then just add/update/delete the fact records as the Quotation Lines change.
If you do need to track history then add effective start and end dates to your fact table: search Kimball for "Timespan Accumulating Snapshot" to see this described

+---------------+---------------------------------+--------------------+--------------------------+-------------+
| ProductNumber | QuotationLineItemNumber | QuotationNumber | PurchaseOrderNumber | Status |
+---------------+---------------------------------+--------------------+--------------------------+-------------+
| PRD-1 | 1 | Q1 | PO1 | PO received |
| PRD-2 | 2 | Q1 | PO1 | PO received |
| PRD-3 | 1 | Q2 | NA | Awaiting PO |
| PRD-1 | 2| Q2 | NA | Awaiting PO |
+---------------+---------------------------------+--------------------+---------------------------+-------------+


What about for the report above?

I read the Insurance chapter of the Kimball book and I realized that what I need to produce the report above may be to have 2 fact tables:

The 2 fact tables are:

Quotation Line Item (this is transaction)
Quotation Processing Workflow (this is accumulating snapshot)

The dimensions are:

Product (for Quotation Line Item)
Customer (for both tables)
Sales Rep (for both tables)
Purchase Order (for Workflow)
Quotation Header (for Workflow)

To produce the report above, it does not appear to be a drill across because the row header is actually the QuotationLineItemNumber.

My questions are:

1. Am I right to say that I need to have 2 fact tables the way I suggested?
2. if yes, then do I query the 2 fact tables separately and then run an inner join on the result sets?

Don't both fact tables have a Quotation Header Dim (possibly just a degenerate dimension if the only attribute you can't push down to the line item is the quotation number)? If so you can then just drill across using this dimension.

Alternatively, why not put the PO Dim on the Quotation Line Item fact? You'd presumably start with a 0 PO dim key andjust update the fact record with the correct Dim key when the line is assigned to a PO

nick_white wrote:Don't both fact tables have a Quotation Header Dim (possibly just a degenerate dimension if the only attribute you can't push down to the line item is the quotation number)? If so you can then just drill across using this dimension.

Alternatively, why not put the PO Dim on the Quotation Line Item fact? You'd presumably start with a 0 PO dim key andjust update the fact record with the correct Dim key when the line is assigned to a PO

According to the book, it is not ideal to have a Quotation Header Dim for Quotation Line Item. So yes, the line item will have a degenerate dimension.

So by drill across, do you mean that I should query the 2 fact tables separately and then do a outer join on the result sets as the book would suggest? or I should do a simple inner join based on the Quotation Number in the Line Item referencing the Quotation Number that would be in the accumulating workflow fact table?

Query the tables separately and then join the resultsets. This is standard best practice for dimensional models and is what most BI Tools (if you look at the SQL they generate.

Regards,

nick_white wrote:Query the tables separately and then join the resultsets. This is standard best practice for dimensional models and is what most BI Tools (if you look at the SQL they generate.

Regards,

I am using MySQL and PHP so there is no BI Tool per se. I have to write it myself.

My question is when you say "then join the resultsets", do you mean that I have to write my own code to join the resultsets or I use a MySQL query to do that?

if so, is it a inner join or a outer join?

You can also do it with a UNION ALL of the two sets, then aggregate the set on the common dimensions.
Measures that don't apply to a particular row should be null. This will allow aggregates, such as Average and Count, to calculate properly.

ngalemmo wrote:You can also do it with a UNION ALL of the two sets, then aggregate the set on the common dimensions.
Measures that don't apply to a particular row should be null.  This will allow aggregates, such as Average and Count, to calculate properly.

I disagree with UNION.

This is why. I expect the final result set like this:

+---------------+---------------------------------+--------------------+--------------------------+-------------+
| ProductNumber | QuotationLineItemNumber | QuotationNumber | PurchaseOrderNumber | Status |
+---------------+---------------------------------+--------------------+--------------------------+-------------+
| PRD-1 | 1 | Q1 | PO1 | PO received |
| PRD-2 | 2 | Q1 | PO1 | PO received |
| PRD-3 | 1 | Q2 | NA | Awaiting PO |
+---------------+---------------------------------+--------------------+---------------------------+-------------+


I expect to have only these 2 fact tables:

Quotation Line Item Fact table
Quotation Workflow Fact table

Quotation Line Item Fact table will likely have multiple rows with the same Quotation number.
Each row in Quotation Workflow Fact table should have its unique Quotation number.

How on earth can this be a UNION to produce the above result set?

Sorry, I meant to say if there are conforming dimensions to aggregate on.  In this case there are not because you are including dimensions unique to the detail fact.

If you know that the combination of time and quote number can act as a primary key to the snapshot, you can simply do a direct join and aggregate it.  An inner join should be fine if you don't need quotes with no lines or lines with no quotes.

As far a joining goes, you are correct, the standard process is to aggregate each separately, then join the sets on common conformed dimensions. Usually this is an outer join, but an inner join can be a valid choice as well. This process always works.

However, depending on the data you could take shortcuts. In this case where you can ensure a 1:M relationship between the two facts, you can do a direct join. This would not work if the relationship is M:M, which is normally the case between two fact tables.

ngalemmo wrote:Sorry, I meant to say if there are conforming dimensions to aggregate on.  In this case there are not because you are including dimensions unique to the detail fact.

If you know that the combination of time and quote number can act as a primary key to the snapshot, you can simply do a direct join and aggregate it.  An inner join should be fine if you don't need quotes with no lines or lines with no quotes.

As far a joining goes, you are correct, the standard process is to aggregate each separately, then join the sets on common conformed dimensions.  Usually this is an outer join, but an inner join can be a valid choice as well.  This process always works.

However, depending on the data you could take shortcuts.  In this case where you can ensure a 1:M relationship between the two facts, you can do a direct join.  This would not work if the relationship is M:M, which is normally the case between two fact tables.

In that case, do I do the direct join using a SQL query?

If so, then there is really no need to query the 2 datasets separately, do I?

You would not be wrong if you did a direct join, based on the specificity of the relationship, and primary keys are enforced.