Loading DataMart Current and History

Hello Group,

I am trying to load the historic data into data mart, currently the data mart is being loaded in prod. We want to load the history data into a pre prod environment and merge them into prod environment.

We have done something before for 2 years worth of history by defining surrogate key range and then merging with prod. We are trying this approach for different requirement which has history of 20 years, trying to check if there is a better approach to do it than using surrogate key range. I am thinking if I build the pre prod environment negative surrogate keys and update the surrogate keys by adding negative values + max(surrogate key) from prod enviornment before merging to prod.

I would like to know if anybody have performed this kind of load and the approach taken, please let me know if you need additional details.

Thanks
Lourde

As long as the SKs you create in PreProd are not, and never will be, values created in Prod then you can do what you want - I would just do whatever's easiest

It is a right approach to prepare historic data other than Prod and do a bulk data load in Prod. MS SQL Server and DB2 has "identity" data type which allows to set a starting point value of surrogate key. Oracle SEQUENCE also allows to set a starting point value of surrogate key. Now you have the option to define a starting point value of surrogate key in a table, but not in your ETL.

Date ranges from date dim or a specific value on a different dim is used to find specific dim row and then join it with the fact table. So creating a positive or negative surrogate key value does not matter. If you would like to identify which historical data was loaded, then audit columns like created date or created by in a table can be used. You can set created date value as '1/1/1800' or created by value as 'Historical data load" to identify historical data.

Thank you for the response. Appreciated !

Thanks
Lourde