slowly changing fact table (millions a night)

[jon]

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.

[ngalemmo]

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.

[jon]

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.