Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Loading DataMart Current and History

3 posters

Go down

Loading DataMart Current and History  Empty Loading DataMart Current and History

Post  lourthad2 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

Back to top Go down

Loading DataMart Current and History  Empty Re: Loading DataMart Current and History

Post  nick_white 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 : 364
Join date : 2014-01-06
Location : London

Back to top Go down

Loading DataMart Current and History  Empty Re: Loading DataMart Current and History

Post  zoom 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

Back to top Go down

Loading DataMart Current and History  Empty Re: Loading DataMart Current and History

Post  lourthad2 Wed Nov 04, 2015 8:22 pm

Thank you for the response. Appreciated !

Thanks
Lourde

lourthad2

Posts : 2
Join date : 2015-10-26

Back to top Go down

Loading DataMart Current and History  Empty Re: Loading DataMart Current and History

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum