--Deepak Kumar
Contents
Appendix
Various terms used in this article and its description. Considering that, this document should be useful across various ETL tools available.
| Terms used | Description |
| DWH | Data Warehousing |
| ETL | Extraction Transformation Loading |
| OLAP | On line Analytical Processing. |
| SDLC | Software development life cycle |
| JUnit Load Ru nner Test Director Rational Suite | Testing Tools used in Market. |
| Component/St age/Transformation | Each components to represent a functionality e.g. Sort, Aggregator etc., used in ETL tools like Ab Initio, Datastage, Informatica in order. |
| Job/Graph/Ma pping | Collection of components to form a flow in GUI. They are used in ETL tools like Datastage or SAS, Ab Initio, Informatica in order. |
| Flow /JobSequence/Mapplet | Collection of Jobs that are called in specific order in ETL tools like SAS, Datastage, Informatica in order. |
| ErWin | Tool used for Modeling. |
| SIT | System Integrated Testing. |
| UAT | Unit Acceptance Testing. |
| SLA | Service Level Agreement. |
Overview
Business Intelligence is a process rather than a product. BI includes ETL, OLAPs, Scorecard/Dashboard, Datamining/predictive analysis etc. This paper confines to the ETL, which creates the foundation for all types of analysis including reporting/ ad-hoc queries etc.
This article includes ETL Testing life cycle, processes followed and some tips for people who involve in testing as well as those in support. Some design standards and best practices are also discussed which help to avoid some haze with respect to testing. All of the tried and true testing disciplines still apply to the ETL Testing. Unit, System/integration, User Acceptance and Production/Volume stress testing phases apply for the ETL modules.
Test Tools and Test Data
The focus of the testing is more on data than operational process. In ETL Testing use of the testing tools may be limited for logging test cases (Test Director) and to generate test data. Tools like Rational Suite can be used to generate data up to 999,999,999,99
rows. But as known, most of the ETL tools themselves have the features to generate test data for the given metadata.
Since major transformations are data dependent, using the subset of the actual or masked live production or production like data will give out more insight and help to uncover many defects. Conventional testing pattern to generate simultaneous multiple users (Load Runner) which are used in web application testing, will not be of much help in ETL Testing. Interestingly Erwin is leveraged in many sites to get good visualization of the Logical data models and helps in getting a common language between modelers and test team.
Test Team
It is a good practice to have a dedicated team if it is a big enterprise wide DWH. It should be ensured that people working in this team have good business knowledge and atleast rudimentary knowledge on the ETL tool being used. The Tester should be able to understand the language of the Developers in usage of technologies as well as that of the Business domain experts with respect to Business flow and requirements.
The Team should be made to involve in full SDLC for testing. If a change at the stage of requirement gathering cost $1, the same will cost $1000 when it is in production. Hence testing at early stages should be encouraged. Testing team should have access to the repository where all the necessary documents are stored, like starting from architecture document to Test cases/results to Agreement sign offs etc.,
Test Plan and Test cases
Best practices recommend that overall test plan should be ready well before the testing commences. A well-defined matrix that deals with the deadlines should be created and circulated among leads of all concerned groups, so that any slippage of the schedule can be attributed to the respective group. Scope of ETL Testing should be defined clearly in the document and should be agreed upon by all stakeholders. The same should be shared with the testing teams. Ideally all test cases need to be prepared upfront, but realistic test cases will be prepared only during the time of each subject area launch.
Unit Testing
Generally the Developers themselves do the Unit testing. A better approach is to shuffle the piece of codes among developers so that one does not test the code one develops. Tool like JUnit can be used at early stage to avoid bottlenecks at later stages. Following list includes the best practices to be followed during development /testing as well as important aspects (technical or otherwise) that can be tracked and ensured during testing. These would also make further testing easy.
- Follow good naming standards. It must be documented prior to development. A good practice is to have Naming standards for each link available in job. And the same is tested during Unit Testing.
- As a part of mandatory check, there shouldn’t be any warnings appearing in the log. Unless they are unavoidable or there is very high labour intensive work around for it or these are standard non-consequential warnings.. Aborts and errors are obviously unacceptable.
- Need to ensure that the necessary jobs / stages are parameterized.
- As a part of best practice, necessary comments and annotations need to be given where ever required.
- General ETL functionalities like Null handling, Row leakages need to be checked.
- Surrogate key integrity needs to be verified. Sequence number generation for consecutive runs etc.,
The reason behind taking care of the warnings is that if the jobs are flooded with warnings serious errors might get unnoticed by operation guys while in production.
System Integration Testing
The subject area that clears the Unit testing will undergo System Integration Testing. It is in general a White box testing (Structural test- which involves testing at Object level and done by the test team). A separate environment will be maintained for Integration testing. Generally executed through the Master Batch job or through shell scripts, which trigger the jobs in an order. Few important points to be ensured during SIT:
- Need to make sure the jobs are Environment independent. The objects, which are promoted from DEV to TEST environment, might still hold some hard-coded stuff (Database details, File paths etc.,), which might cause error or unexpected results.
- Need to ensure the end-to-end overall flow, As the individual components are being triggered by the main Master Job control.
- Transformation rules should be tested to ensure they cover all the Business requirements. Maintaining a vertical matrix will be helpful.
- Need to ensure that the Rejects have proper business reasons and error codes.
- Loading order: Lookup tables if necessary, Dimensions tables followed by Fact tables etc.
- If any external data is being sourced, the frequency and the process in which it is automated should be tested. Timezone related issues need to be addressed, if the data are fed from different parts of the world.
- Check point Restartability of the Batch job should be tested, in case of restart, rollbacks.
- If any audit tables are maintained, start time and end time populating point should be verified. If Batch ids for each run are being logged, there can be different test cases for restart and scheduled reruns.
User Acceptance Testing
End-to-end system tested code will be provided to users for UAT. The real business users cannot be expected to test the object code. This will be a Black box testing. They will be more happy if they could see the input that they provide in the source been reflected in DWH. Most of the rejected records based on business logics will be scrutinized here. Data transformed and loaded with good quality (cleansing and profiling) gets appreciation from users. Wrong requirement gathering will become transparent at this stage. Chances of getting many CRs are also possible.
The defects like misleading values in lookup data, format and unit of the data in dimensions, reject that doesn’t bind to the business logics etc are typically raised during UAT.
Stress Testing
Once the defects that were reaised are fixed and retested to obtain the expected results the UAT is signed off and code is moved to production.
Most commonly the Volume/Stress test will be performed in production or in pre-production environment. Here the batch window will be measured and it should meet the SLA. Many projects fail at this point. Rework on performance improvement might take place. Regression test need to be performed for the same.
Multiple runs/ Parallel runs with different batch need to be made as prescribed in the Job flow diagram provided by the Development team. More modularized the jobs are, more the option of parallel execution. Statistics on memory usage, CPU usage, Storage (Database, file system) etc., should be documented. This can be used to reconcile with the initial capacity planning as well.
In most of the cases, Security testing (if conducted) will be done as a part of Integration testing or during Post implementation verification testing directly in production environment. At some confidential sites like Banking, Government etc., it will be expected to show the database authentications in an encrypted format only, even in ETL logs.
Defects
Defects raised can be logged in the testing tool. Many tools have the feature to send immediate mail notification to respective person mentioned in the mail group. Severity for each defect will vary from organization to organization. It can be of 3 levels to 5 levels. In general the severity is from Critical to low. And the turn around time for each severity will be agreed initially and will be available in the SLAs. As a general notion, there is no critical defect in Data warehousing system, as it is not a business showstopper as an operational system is. But considering the methodology and timeliness for the outcome the conventional hierarchy of
testing is maintained. Following will be a typical defect report sheet
This defect report sheet needs to be distributed across different levels, from development team to the sponsors of the project, in order to make the process transparent. Show stoppers need to be informed /escalated to the respective associates.
Change Control
Once the defects are raised, the developers will be releasing the code fixes. Since the whole system works well with in the closed loop functionality, the complete testing cycle needs to be followed. But based on the impact and the intensity of the release, Regression test has to be conducted for each release.
A change control should be maintained with version numbers and with change description mentioned clearly in the order of date. Developed object should be imported into test environment only through version control. Many ETL tools have their own version control mechanism. A third party tool can also be incorporated (eg Rational Clear Case etc). Even for any development regarding to CR or defect fixing, it is a recommended practice to take the code from Version control. It should be a single point of reference with all versions maintained. The over all flow looks like the following diagram
Testing Flow
The flow shows the overall picture from requirement gathering to moving the code to production. The point to note is that the defects raised during the period of UAT will be directly assigned to SIT and should be answered by the SIT team. It will be assigned back to Development or Design team only if that need to be addressed by them.
Conclusion
Generally the Testing/QA team it the one to be blamed for any pit falls in production. On the contrary they don’t get any appreciation for error free code in production. Testing plays quite a unique role in software life cycle. Testing team should closely associate with development team to know the technical aspect and at the same time should associate with business people to get the business related logics explained. During testing, defect will be raised against the same person whom test team has associated with. If it’s a bug, it should reach the right person through right channel to get it rectified at the given timeframe. But the same should be handled in diplomatic manner.
It is easy for a person to test and handle the subject if equipped with good business and technical knowledge of that domain. The aim for the Test team should be to propagate the defect free object to production. And not just to increase the number of defects. The other teams should be on the same page with respect to this.
