slowly changing fact table (millions a night)

View previous topic View next topic Go down

slowly changing fact table (millions a night)

Post  jon on Tue Sep 07, 2010 1:14 pm

i have an interesting datawarehouse process.

fact data are fully loaded every night.

following are the sample table and data.

On Monday
myFact1
date MemberID PurchaseBarCode
Mon 123 qwe
Mon 123 sdg


On Tuesday
myFact1
date MemberID PurchaseBarCode
Tue 123 qwe


On Wednesday
myFact1
date MemberID PurchaseBarCode

the PurchaseBarCode sdg was return on Tuesday morning, so, there is only one row left.
the PurchaseBarCode qwe was return on Wednesday morning, so, there is nothing left for that customer fetch from the source.

how to build a fact table on situation liked this? What are the DW columns should be added to capture this type of info.
Is having the fact table to retain the history is liked asking for a lot of trouble? currently, the table has about 2 million rows nightly.

Thanks.

jon

Posts : 11
Join date : 2010-05-10

View user profile

Back to top Go down

Re: slowly changing fact table (millions a night)

Post  ngalemmo on Tue Sep 07, 2010 1:38 pm

I've no idea what you are trying to represent, but if this is some sort of sales cycle, why not add a quantity column and just insert transactions (no updates). Use 1 for sales and -1 for returns. Queries can easily figure out what's going on and you don't need to worry about one-of updates and inserts on millions of rows. Just bulk insert the facts.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: slowly changing fact table (millions a night)

Post  jon on Tue Sep 07, 2010 6:17 pm

thanks for the suggestion on the column.

the source doesn't tell us if an item has been return, it was enter in error orginally, or etc.

so, i am trying to see if there is any way to track the historical aspect of the person's purchase activity.

my guess is the records will double in size in half year. so, i am hoping to find a way to capture the history.




jon

Posts : 11
Join date : 2010-05-10

View user profile

Back to top Go down

Re: slowly changing fact table (millions a night)

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