Updating records in a fact table

[kclark]

Hi Experts,

I have ran across some clients who have business processes which require an update to records that already in the Data Warehouse. Essentially an update to an existing record OR a record which errored out during the ETL process which is not inserted and the Summary tables in the DW need to be re summarized with the addition of the corrected record.

Any thoughts?

Thanks guys!

[KimballFan]

 A lot of this depends on your architecture and refresh process.  Hopefully the summary table isn't summarized at a high level as it could require a complete rebuild.  If that is the case, you might want to consider a meterialized view with fast refresh.  In Oracle, this only works if the view isn't real complex.  I'm not sure about other database platforms
Would it be possible to correct the data warehouse record with a script and rerun the job that refreshes the summary table? That would be the safest way. It also depends on what is being corrected. If it was just the fact record, that should work

[ngalemmo]

There are other ways of dealing with summary tables. There is no rule that says a summary table must have one row for each unique combination of dimensions. So, if you have the net change to the summary, there is no reason you cannot simply append those rows to the table. Queries will still work, totals will still total, and you save a lot of work.

[Booma]

I too have a question about updating records in a fact table. I wonder if it is an accumulating fact table or not. I don't really see any other options, but I read that accumulating facts are pretty rare.

Here is the situation:
Customers buy a product, which they get an invoice for. If they don't pay within 8 days, they get a reminder. When they don't pay within 14 days, they get another mail. After 29 days, they get a letter sent to them. Etc, there are a couple of more steps. However if someone pays the same day as the get the invoice, that ofcourse wont receive reminders.

My design looks like this
fact_invoice_line
product (FK to dimension)
customer (FK to dimension)
sent_date (when was the invoice sent?)
sent_time (FK to time dimension)
invoice_id (degenerate key)
invoice_line_id (degenerate key)
first_reminder_date (FK to date dimension)
second_reminder_date (FK to date dimension)
etc
cancellation_date (The order is cancelled, FK to date dimension)
accountblock_date (After 50 days the customers account is blocked. FK to date dimension)
paid_date (when did the customer pay? FK to date dimension)
paid_indicator (customer paid / hasn't paid yet)
total_excl_btw
amount_excl_btw

Just wanted to check if this is a good design, since alot of times those reminders will be 'unknown'. Also, not all invoices will have the same milestones. Many will skip the reminders.

Also I have another table
In our webshop we sell subscriptions. Most of the time a subscription lasts 1 year. If the customer terminates their subscription, we would like the date of then he/she did that, and the date of when the subscription ends.

fact_product_registrations
customer (FK to dimension)
product (FK to dimension)
product_registration_date (Date when the customer order this product. FK to date dimension)
product_termination_date (Date when customer terminates this product. FK to date dimension. Is 'Unknown' if customer doesn't terminate the product)
product_ends_date(Date when the products ends. FK to date dimension)
product_is_terminated_indicator (is terminated / is active)

Is this also a accumulating snapshot? I didn't make 2 seperate fact tables (1 for orders, 1 for terminations) because we want to know the average length of subscriptions, for example.

[ngalemmo]

Invoices and reminders are two different business processes. Use a different fact table to track when reminders are sent.

[Sponsored content]