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

How to create staging layer to handle incremental load

3 posters

Go down

How to create staging layer to handle incremental load Empty How to create staging layer to handle incremental load

Post  jim123456789jim Thu Jan 02, 2014 7:50 am

Hi,

I'm new to this forum and not sure is this a right place to post this question. Please move/delete this question if it is not relavent.

We are designing a Staging layer to handle incremental load. I want to start with a simple scenario to design the staging.

In the source database There are two tables ex, tbl_Department, tbl_Employee. Both this table is loading a single table at destination database ex, tbl_EmployeRecord.

The query which is loading tbl_EmployeRecord is, SELECT EMPID,EMPNAME,DEPTNAME FROM tbl_Department D INNER JOIN tbl_Employee E ON D.DEPARTMENTID=E.DEPARTMENTID.

Now, we need to identify incremental load in tbl_Department, tbl_Employee and store it in staging and load only the incremental load to the destination.

The columns of the tables are,

tbl_Department : DEPARTMENTID,DEPTNAME

tbl_Employee : EMPID,EMPNAME,DEPARTMENTID

tbl_EmployeRecord : EMPID,EMPNAME,DEPTNAME

Kindly suggest how to design the staging for this to handle Insert, Update and Delete.

Regards

Jim

jim123456789jim

Posts : 2
Join date : 2014-01-02

Back to top Go down

How to create staging layer to handle incremental load Empty Re: How to create staging layer to handle incremental load

Post  BoxesAndLines Fri Jan 03, 2014 11:06 am

Make it look like the target with the natural keys from the source. That will allow you to do lookups based on the source keys. If you find the row, check for updates. If no row found, then it is a new record. The ETL toolkit book covers this in detail.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

How to create staging layer to handle incremental load Empty How to create staging layer to handle incremental load

Post  jim123456789jim Mon Jan 06, 2014 1:03 am

Thanks. How to handle deletes. Say in these two tables(tbl_Department,tbl_Employee) if there is a delete in tbl_Employee then how to handle it(attribute table). If a delete happened in tbl_Department(driving table), then how to handle it?

Is it possible to have a generic senario to handle delete?

Regards
Jim

jim123456789jim

Posts : 2
Join date : 2014-01-02

Back to top Go down

How to create staging layer to handle incremental load Empty Re: How to create staging layer to handle incremental load

Post  ngalemmo Mon Jan 06, 2014 1:07 am

Yes… don't.

You can flag a dimension row in some manner if you wish to indicate it has been deleted in the source system, but you should never delete dimension rows.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

How to create staging layer to handle incremental load Empty Re: How to create staging layer to handle incremental load

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