The use cases of iCEDQ, our automated ETL testing, and production data monitoring software are present in non-production and production environment. Organizations can use this for automating data warehouse testing, data migration testing, database testing, application integration testing or to purely monitor production data for issues. All of these different use cases require various types of features and functionalities. Below is the complete list of those features.
Types of Rules
Users can create four different types of rules in iCEDQ for automating ETL Testing. These four rules are known as Checksum, Recon, Validation, and Script. Each rule is used to perform a different type of test.
Checksum rule is used to compare row count, sum, average, min/ max, and other aggregated values. A simple example is making sure the customer count is the same between the customer file and the staging table.
Recon rule is our data comparison rule which can compare two data sets be it a file or a database. Examples of Recon rule are as follows
- Identify rows missing either from source and/ or target (diff)
- Validate transformation, conversion or calculation between columns
Validation rule allows users to perform source data validation, external feed file validation or any other single data set validation. Examples of validation rule are as follows
- Null value & Format check
- Type II dimension test
- FK validation
Script rule opens up groovy scripting to the users. It is designed for the advanced user and for outlying scenarios which can not be achieved by any of the above rules.
- Execute DDL/ DML statements to generate test data
- Complex rule sequencing and depending execution
Sometimes users have to perform the complex operation to test an ETL or data warehouse. These complex scenarios can be performed using our advanced scripting rule. This rule allows users to write the groovy script or consume custom java library.
Below are some of the examples for which this rule has been used by customers
- Execute a shell script on a remote Linux machine as part of the test suite
- Pick values from reference database and pass them as a parameter value to other rules
Performing regression testing in data warehousing or in any ETL related project is a big pain point. And doing that manually is almost impossible.
iCEDQ allows users to combine all the old rules (tests) and new rules (tests) into a batch (test suite), this creates a regression suite which can be triggered from anywhere. When a batch is executed it gives summary information of success and failures. Also, users can look at the status of each rule which has been executed from the batch. Below are other features of the batch.
- Configure rules to execute in a specific sequence
- Configure execution dependency between rules
- Execute sames rules across different environments (DEV,QA, UAT…)
Most of the ETL test rules a user will create have to be parameterized so that a new parameter value can be passed during execution of the rule. iCEDQ supports parameterization out of the box. Users can define multiple parameters for a rule which can be used in any part of the rule. Once the rule has been parameterized, user can pass new values when executing the rule from external tools like autosys, control-m, jenkins or any other tool.
Automatically generate a set of rules between source and target with a simple drag and drop feature. This is very useful when users are doing data migration testing, database replication testing or schema structure testing. It helps the user reduce the effort by almost 90 percent.
In manual testing, users are blocked from doing a critical task because they have to trigger the tests manually and wait for the result. Even though iCEDQ allows users to execute rules on demand it is not enough to automate end to end ETL testing.
With our inbuilt scheduler, users can schedule any job inside iCEDQ. Our users can also schedule our rules or batches using an external scheduling tool like Control-M, autosys, tidal to name a few.
When testing a data warehouse it is important to store the test results of each run/ execution as proof. iCEDQ stores the complete execution history along with its summary result in its repository. This allows users to refer the proof of execution at any point of time. It also helps managers created customer execution reports for dashboarding and reporting purposes.
Reporting & Dashboard
As all the information of the rules and its execution is stored in the repository of our data warehouse testing tool it becomes easier to do reporting. With a built-in dashboard in iCEDQ, management gains more transparency and insight into the data issues across the organization.
iCEDQ comes with a reporting module (iReport) where users can create reports using predefined templates to get the desired result. The repository is exposed to the users to create custom reports using reporting tools like Tableau, Cognos or something similar.
For a complete end to end test automation of ETL, it is important to execute rules (tests) as a part of your continuous integration build process. iCEDQ can be integrated with or trigger from any CI tool like Jenkins, Bamboo, TeamCity or any other tool.
iCEDQ allows users to trigger its rules from any of these tools and pass the result back to these tools. Using the result users can configure the build to pass or fail. We are also releasing an out of the box Jenkins plugin soon.
Using the out of the box export/ import functionality users can move or migrate rules between different instances of iCEDQ. So users can create rules in one instance test it and then export it out. Then in another instance import the exported file, it is that easy to migrate rules.
Test Tool Integration
Users can integrate iCEDQ with test management tools like HP QC/ HP ALM, TFS or any other tool which allows them to trigger a command line or web service utility. iCEDQ also provides an out of box integration with HP ALM using its iALM module.
This integration allows organizations to maintain the test management separate from test execution, which is very useful as existing enterprise reports and their formats need not be changed because of the integration. Reduces the effort of manually filling the results for the test cases as results are automatically posted.
CLI & WS APIs
If you want to integrate iCEDQ or trigger the rules present inside iCEDQ then you can easily achieve that goal by consuming our command line utility or web service utility. Users can trigger rules using these utilities and get the result back immediately. Customers have integrated iCEDQ with tools like Control-N, autosys, Tidal, Talend and other enterprise tools using our CLI or Web Service APIs.
Alerts & Notifications
Users can configure the ETL testing rules to send email alerts to the subscribers based on the success and/ or failure of the rules. These email alerts contain the details of execution as well as provides the ability to download the test results in an excel format. iCEDQ also provides the ability to customize these email templates.
In data warehouse testing every user works together to make sure the data is correct be it a developer, tester, architect, manager or business user. Therefore providing correct roles to each user becomes critical.
In iCEDQ users can be granted different types of roles on different objects making it easier for admins to manage user security. We provide four different types of roles right from manager to reader.
Today most of the organization’s force their users to connect to the database using their userID because of security reasons and they cannot use generic userID.
In iCEDQ admins can setup database connections where each user can maintain their connection profile for that database. This helps administrators set up one connection with multiple users.