The use cases of iCEDQ is 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.
Types of Rules
Users can create different types of rules in iCEDQ to automate the testing of your Data-Centric projects. 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
Recon rule allows a user to compare the full volume of data between the source and target. It compares data row by row and column by column, which makes it easier for the user to identify the exact row and column having the data issue. Below are some of the use cases of Recon Rule.
- Identify missing records from source and/ or target
- Validate the data transformation ETL or business rule
- Compare data in a file with a database
- Compare database schema across environments
Validation rule enables the user to verify a single source to be accurate, it might be a single database or file at source or target. Multiples columns or rows can be tested at once using this rule. This type of rule can be used for pushdown validations too. Below are a few of the use cases of the Validation Rule.
- Format and Null value check
- Type II dimension testing
- Duplicate Data check
- Feed file validation
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 that 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.
iCEDQ has a web-based GUI allowing global teams to work together regardless of location. All of the information is stored in a centralized database repository making it easier to access any information at any time.
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.
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.
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.
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.