Friday, February 15, 2008

SDLC of ETL Projects: ETL Process Definitions and Deliverables

1.0 Define Requirements => In this process you should understand the business needs by gathering information from the user. You should understand the data needed and if it is available. Resources should be identified for information or help with the process.
Deliverables
A logical description of how you will extract, transform, and load the data.
Sign-off of the customer(s).
Standards
Document ETL business requirements specification using either the ETL Business Requirements Specification Template, your own team-specific business requirements template or system, or Oracle Designer.
Templates
ETL Business Requirements Specification Template
2.0 Create Physical Design => In this process you should define your inputs and outputs by documenting record layouts. You should also identify and define your location of source and target, file/table sizing information, volume information, and how the data will be transformed.
Deliverables
Input and output record layouts
Location of source and target
File/table sizing information
File/table volume information
Documentation on how the data will be transformed, if at all
Standards
Complete ETL Business Requirements Specification using one of the methods documented in the previous steps.
Start ETL Mapping Specification
Templates
ETL Business Requirements Specification Template
ETL Mapping Specification Template
3.0 Design Test Plan => Understand what the data combinations are and define what results are expected. Remember to include error checks. Decide how many test cases need to be built. Look at technical risk and include security. Test business requirements.
Deliverables
ETL Test Plan
ETL Performance Test Plan
Standards
Document ETL test plan and performance plan using either the standard templates listed below or your own team-specific template(s).
Templates
ETL Test Plan Template
ETL Performance Test Plan Template
4.0 Create ETL Process => Start creating the actual Informatica ETL process. The developer is actually doing some testing in this process.
Deliverables
Mapping Specification
Mapping
Workflow
Session
Standards
Start the ETL Object Migration Form
Start Database Object Migration Form (if applicable)
Complete ETL Mapping Specification
Complete cleanup process for log and bad files => Refer to Standard_ETL_File_Cleanup.doc
Follow Informatica Naming Standards
Templates
ETL Object Migration Form
ETL Mapping Specification Template
Database Object Migration Form (if applicable)
5.0 Test Process => The developer does the following types of tests: unit, volume, and performance.
Deliverables
ETL Test Plan
ETL Performance Test Plan
Standards
Complete ETL Test Plan
Complete ETL Performance Test Plan
Templates
ETL Test Plan Template
ETL Performance Test Plan
6.0 Walkthrough ETL Process => Within the walkthrough the following factors should be addressed: Identify common modules (reusable objects), efficiency of the ETL code, the business logic, accuracy, and standardization.
Deliverables
ETL process that has been reviewed
Standards
Conduct ETL Process Walkthrough
Templates
ETL Mapping Walkthrough Checklist Template
7.0 Coordinate Move to QA => The developer works with the ETL Administrator to organize ETL Process move to QA.
Deliverables
ETL process moved to QA
Standards
Complete ETL Object Migration Form
Complete Unix Job Setup Request Form
Complete Database Object Migration Form (if applicable)
Templates
ETL Object Migration Form
Unix Job Setup Request Form
Database Object Migration Form
8.0 Test Process => At this point, the developer once again tests the process after it has been moved to QA.
Deliverables
Tested ETL process
Standards
Developer validates ETL Test Plan and ETL Performance Test Plan
Templates
ETL Test Plan Template
ETL Performance Test Plan Template
9.0 User Validates Data => The user validates the data and makes sure it satisfies the business requirements.
Deliverables
Validated ETL process
Standards
Validate Business Requirement Specifications with the data
Templates
ETL Business Requirement Specifications Template
10.0 Coordinate Move to Production - The developer works with the ETL Administrator to organize ETL Process move to Production.
Deliverables
Accurate and efficient ETL process moved to production
Standards
Complete ETL Object Migration Form
Complete Unix Job Setup Request Form
Complete Database Object Migration Form (if applicable)
Templates
ETL Object Migration Form
Unix Job Setup Request Form
Database Object Migration Form (if applicable)
11.0 Maintain ETL Process => There are a couple situations to consider when maintaining an ETL process. There is maintenance when an ETL process breaks and there is maintenance when and ETL process needs updated.
Deliverables
Accurate and efficient ETL process in production
Standards
Updated Business Requirements Specification (if needed)
Updated Mapping Specification (if needed)
Revised mapping in appropriate folder
Updated ETL Object Migration Form
Developer checks final results in production
All monitoring (finding problems) of the ETL process is the responsibility of the project team
Templates
Business Requirements Specification Template
Mapping Specification Template
ETL Object Migration Form
Unix Job Setup Request Form
Database Object Migration Form (if applicable)

No comments: