Loading DataMart Current and History

View previous topic View next topic Go down

Loading DataMart Current and History

Post  lourthad2 on Mon Oct 26, 2015 10:44 pm

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

lourthad2

Posts : 2
Join date : 2015-10-26

View user profile

Back to top Go down

Re: Loading DataMart Current and History

Post  nick_white on Tue Oct 27, 2015 8:34 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Loading DataMart Current and History

Post  zoom on Wed Oct 28, 2015 8:31 am

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.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Loading DataMart Current and History

Post  lourthad2 on Wed Nov 04, 2015 8:22 pm

Thank you for the response. Appreciated !

Thanks
Lourde

lourthad2

Posts : 2
Join date : 2015-10-26

View user profile

Back to top Go down

Re: Loading DataMart Current and History

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