Initial & Incremental Loads to Staging

Question

Initial & Incremental Loads to Staging

Welsh Corgi

SSC Guru

Points: 116520
More actions
April 2, 2012 at 11:42 am

#259354

I thought that when you load a Data Warehouse that you performed an initial load to staging and then moved the Data to the Data Warehouse.
I saw a situation where the initial load of data from the first data source was loaded into staging.
Before moving that Data the incremental load is performed using a merge on the staging table.
I thought that when you performed an initial load to staging you move the data from the staging to the Data Warehouse Tables before you perform an incremental?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

:crying:No bites.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I execute the EXEC sp_WhoIsActive and I see that there are 10 concurrent merges on the same table based on the range on the primary key.

Apparently the table loads faster and this is the best approach.

There are 137 other tables to load (incremental).

Each Merge is being perform to perform an incremental load with the data from the Initial Load still in the Staging Table using an OPENQUERY on each AS400 Table.

The approach that I would take would be to load the tables in parallel as opposed to ranges of records.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

It appears that the person of interest has no intention of ever clearing the Staging. :hehe:

They will retain the Initial Load and each incremental load.

THe staging is never cleared out.

Has anyone taken this approach?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Welsh Corgi (4/3/2012)
It appears that the person of interest has no intention of ever clearing the Staging. :hehe:
They will retain the Initial Load and each incremental load.
THe staging is never cleared out.
Has anyone taken this approach?

Nope. Cleared stage before pulling next set of data.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I took Parts of his OPEN QUERY Statements converted to Data Tranformation Task and loaded 178 tables in SSIS with the initial load and eliminated the slow OPEN QUERY Code.

He was working on SQL Statement for the Incremental.

I executed the Stored Procedure sp_whoIsActive and captured the SQL Text.

In his incremental he is doing a merge on the Staging Table comparing the Initial Load to the Incremental. I would have expected that after the Initial Load to Staging that the Data be moved to the DW. Is that what you would do?

Do you have anything in writing that might back me up? I tried finding something via goggle but no luck.

This guy is working remotely and marching to the tune of his own drummer.

Thank Lynn.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Any thought, ideas, opinions, experiences,etc would be greatly appreciated.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Nothing in writing that I can point you to on this. Staging to me is a landing point. You get your data from the source system as quickly as possible and stage it. From stage you then do any data cleanup needed to prepare the data for the DW.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Thanks and don't you clear out your staging after the initial load to Staging and the inital load to the DW is complete.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Welsh Corgi (4/3/2012)
Thanks and don't you clear out your staging after the initial load to Staging and then to the DW.

Answered that above, but yes. Stage is just a holding place. Once the data is moved on the the DW or ODS database the data in stage is no longer needed. I do make sure I have a backup of the stage database just in case that data is needed again shortly.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Thank you Lynn.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

post removed.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Olalekan Adewale Right there with Babe Points: 767 More actions · Answer 12

Good day Welsh Corgi,

I merge incremental to initial load most times, don't use to clear my staging.

I will henceforth borrow Lynn Pettis's idea.

Thanks Welsh

Thanks Lynn 😛