Populating order fact table incrementally,

View previous topic View next topic Go down

Populating order fact table incrementally,

Post  dk2014 on Wed Dec 03, 2014 1:00 pm

I have several dimensions (DimProduct, DimCustomer, DimCustomerAddress, DimOrderDate, DimOrderStatus) and a fact table that tracks orders at the order line/product level.
The fact table also has OrderNumber as a degenerate dimension and this along with the FKs from the dimensions makes up the primary key in the fact table. The measures included in the fact table are ProductOriginalPrice, ProductPriceAfterDiscount, ProductQuantity, ProductDiscountPercentage, COGS, Profit, Margin, and ShippingAmount (this comes from the source system on the order level but is divided by quantity amount to bring it down to item/product level).
I have SCD set up on most of my dimensions and that works fine. I also populated the fact table initially and it has almost 50 million records.
My question has to do with loading the fact table incrementally. How do I do this? Btw, this is a transactional fact table.
I don't want to pull all 50 mill records every time I update DW so do I just check if the DD/FKs combination (PK) exists and if not insert the record into the fact table?
What happens if, even though this should not happen, one of the measures changes in the source system - say ProductQuantity? I can't insert a new row into the fact table as it'll have the same PK and as such it won't let me do it. Do I check if any of the measures for any of the records in the fact table has changed and if so I update it? Or do I just ignore it because at the time of the load this is what the source system had? What is the typical way of doing this? Again, this is suppose to be a transactional fact table.
Thanks!

dk2014

Posts : 15
Join date : 2014-11-10

View user profile

Back to top Go down

Re: Reply with quote Populating order fact table incrementally,

Post  hkandpal on Wed Dec 03, 2014 2:06 pm

Hi,

are your business users interested in capturing the changes/updates in the fact table, if not interested then you can update the fact table.

thanks

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Populating order fact table incrementally,

Post  ngalemmo on Wed Dec 03, 2014 2:22 pm

If you want to keep change history, one way to do it is insert the change amount and get rid of the PK declaration. This would be a transactional fact table (as opposed to a table that stores information about transactions) in that rows are insert only and each row represents a net change.

Alternately, you can build an accumulating snapshot fact which, in a sense, is a lot like a type 2 dimension. You store versions of rows that are restatements of a previous state. Rows would be date bounded. If you declare a PK you would include the effective date in that key.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Populating order fact table incrementally,

Post  dk2014 on Thu Dec 04, 2014 9:39 am

Thank you hkandpal and ngalemmo!

dk2014

Posts : 15
Join date : 2014-11-10

View user profile

Back to top Go down

Re: Populating order fact table incrementally,

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum