Load of the datawarehouse without the use of lookup tables
This particular module has been developed for those customers where the lookup tables are very large and the join transformations shall help process the information faster than lookup. In this particular case the ETL team has replaced the lookup with join transforms.
Note: This method cannot be applied for type 2 entities
This method currently handled insert, updates and deletes.
The solution shall be developed over 3 data flows as given under:
In the supply load dataflow the ETL team has developed the methodology for inserts, updates and deletes as follows:
Method for handling inserts and updates
· Join the source and the target table using the merge transform based on the composite keys. The end client could pick up the surrogate keys based on the same.
In case of the insert, the identity key column should be checked for the identity row insert.
For updates the table data is stored in a copy of the target table. Thereafter the update sql is run for the same using the sql transform.
Handling deletes:
In the case of deletes, the ETL team looks for the composite key details in the target table and the given details should not be in the source table. In case the given condition is applicable then the row in the target table is marked as ‘D’(delete flag)
Contact details:
For resource requirements:resources@mnnbi.com
For training and consulting services: apoorv@mnnbi.com
Blogspot details: https://mndatasolutionsindia.blogspot.com/
Written by Gauri(gauri@mnnbi.com)
https://mndatasolutionsindia.blogspot.com/p/gauri.html
No comments:
Post a Comment