How to create staging layer to handle incremental load
3 posters
Page 1 of 1
How to create staging layer to handle incremental load
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
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
Re: How to create staging layer to handle incremental load
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- Posts : 1212
Join date : 2009-02-03
Location : USA
How to create staging layer to handle incremental load
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
Is it possible to have a generic senario to handle delete?
Regards
Jim
jim123456789jim- Posts : 2
Join date : 2014-01-02
Re: How to create staging layer to handle incremental load
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.
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.
Similar topics
» New Layer in DWH for Reporting
» Presentation Layer
» Modeling Fact Tables that have direct relationships, but at a detail and not a dimension layer
» Datasecurity within Business Intelligence
» Do we need an additional layer on top of Datamart?
» Presentation Layer
» Modeling Fact Tables that have direct relationships, but at a detail and not a dimension layer
» Datasecurity within Business Intelligence
» Do we need an additional layer on top of Datamart?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|