DW design for data cut-off issue [message #442985] |
Thu, 11 February 2010 20:22 |
summoner
Messages: 44 Registered: March 2009
|
Member |
|
|
Dear all,
Our company is now working on building a data warehouse "DW" that involves multiple data source.
We have completed the design for the basic requirement and data can be imported into the DW per day. Some data are imported using materialized view with complete refresh option.
Right now we have a problem on data cut-off issue. Users would like to have an indicator to know the value before/after cut-off.
For example, suppose 1000 product "XYZ" were sold on March-09. The data is inserted into data source "DS". After we import the data into our DW, someone may update the # of sales to 1200. User would like to know there is a change on sales of 200.
In our data source DS, the table stores
Quote:
XYZ, 3/09, 1000
As your may see that there is no transaction/modified date in this table. For any changes, our system in DS will delete old record and create a new one
Quote:
XYZ, 3/09, 1200
If we import data from DS using complete refresh option, old data will be lost and we have no way to know the sales is 1000 before cut-off. Someone suggested to save a copy of data per day.
Quote:
XYZ, 3/09, 1000, 4/9/2009
XYZ, 3/09, 1000, 5/9/2009
XYZ, 3/09, 1000, 6/9/2009
XYZ, 3/09, 1000, 7/9/2009
XYZ, 3/09, 1000, 8/9/2009
XYZ, 3/09, 1000, 9/9/2009
...
XYZ, 3/09, 1200, 1/10/2009
The system will have a lot of duiplicate records that is no use if there is no changes. Do anyone have similar experience and willing to share? Thanks for help
|
|
|