Data Warehouse & ETL testing
Never ever test manually again! Using BiG EVAL you automate the test processes for your data warehouse system and the ETL or ELT processes.
BiG EVAL takes away the manual effort from testing your ETL or ELT processes and your data warehouse. This is done by comparing and checking test data between the source systems, the staging area, the data warehouse and all components in between.
Exactly the way in which you would do it manually – but automated.
During development, individual pieces of code are tested for their correct functioning without regard to their dependencies. Thus can check, e.g. single data transformation tasks or even MDX formulas.
Do the individual components of the system work together properly? This can only be verified by providing them in a test environment and reviewing them across the board.
Data plausibility check
This process checks if the data is accurate and credible. By involving the business user you increase the value of this test method.
This type of testing has two objectives with respect to the user. On the one hand, it ensures that the data provided to the user meet the expectations. On the other hand, this ensures that the tools provided to the user meet the expectations.
Testing a function over and over again increases the certainty that no errors will creep in during development that affect an already tested part of the system. Adhering to short release cycles in agile projects is not possible without regression testing.
In addition, regression testing in the productive operation of the system can be very useful to regularly monitor the quality of the data, thereby providing an early warning system for any problems that occur.
This testing process is an important aspect of user acceptance and therefore can not simply be ignored. Does loading of data lead to a slowdown of the systems? Does the parallel queriy of data lead to a slowdown of the systems?
Choose out of a predefined set of testmethods and -algorithms or create your own by using a database query language, C# or even R.
Specific tests for your data
Compare number of records
Compare the number of data records in the source systems with those in the data warehouse for a first overview.
Which records are missing or too much respectively duplicated you only learn when you review business keys.
Validate source data
Do not allow erroneous data to be loaded into your data warehouse by checking that the data provided is error-free, even during staging.
Check scopes of validity
Would you load a customer record into your data warehouse that has a date of birth around 1850? Validate information that is subject to restrictions early.
Be sure to check, if your implementation of historicized data in Slowly Changing Dimensions (SCD 2) is correct.
Are there any gaps? Are there any overlaps or date rotators?
Classifiable data should be checked for their distribution. Were around 40% of your sales made on product group A and only 10% on product group B?
Check aggregates and KPI's
You can mathematically check business logic in the data warehouse or in analysis systems such as OLAP cubes and, if necessary, compare them with values from your source systems.
If you involve business users to your tests, you'll benefit of their knowledge. Check, for example, whether the November and December sales differ in a credible manner.
Define reference queries or record real queries from your users to constantly monitor their performance.
End-to-End Data Validation
The technology-neutral concept of BiG EVAL allows validation of key performance indicators starting in your source systems up to your analysis-model and reporting layer. Let’s see a video to see how that’s done.