At a recent event, one of the prospects at the attendees came over to our booth and asked me to demonstrate a use case that was kind of interesting. I got a similar request from another prospect recently and thought that it might be a good idea to blog and show how it can be done using ETL Validator.
Problem: There is a source table and a target table. The attendee wanted to find the difference in a number field between two tables. In addition, he was also interested in specifying an acceptable variance and define a rule on that. If the difference is within the limits, then, he wanted the test case to be marked as success. If the difference exceeds the variance, then, he wanted the test case to be marked as failure. In just few minutes, we were able to demonstrate this use case using 3.4 version of ETL Validator.
Step 1: To simulate the source, we created a simple table and inserted few records.
- Created table table_a (Segments Varchar2(20), Counts number);
- Data Insertion
- insert into table_a (books, quantity) values (’Tier 1′, 10);
- insert into table_a (books, quantity) values (’Tier 2′, 15);
- insert into table_a (books, quantity) values (’Tier 3′, 12);
Step 2: To simulate the target:
- Created a table table_b (Segments Varchar2(20), Counts number);
- Inserted few sample records
- insert into table_b (books, quantity) values (’Tier 1′, 10);
- insert into table_b (books, quantity) values (’Tier 2′, 25);
- insert into table_b (books, quantity) values (’Tier 3′, 30);
Ok, now the sample data set was ready, it was time to showcase the Component Test Case in ETL Validator:
Step 3: I launched ETL Validator
- Opened up ETL Validator and started with the Component Test Case since, one of my favorites.
Step 4: I dragged and Dropped to create the DB components in the test case wizard.
- Had to create DB component to reflect the Source. It was a wrapper on a simple query (select * from table_a)
- Noted the name of the “Result Table Name” in properties for that component. {TCOMP_40531_40508}
- Had to create DB component to reflect the Target. It was a wrapper on a simple query (select * from table_b)
- Noted the name of the “Result Table Name” in properties. {TCOMP_40531_40509}
Step 5: Had to Drag and Drop another database component to capture the differences.
- select a.books, (a.quantity-b.quantity) as Quantity from T_COMP_40531_41058 a, T_COMP_40531_41059 b where a.books=b.books
- Now, we were ready to apply the rules on top of this component.
Step 6: Drag and drop a “Data Rules Component” to define rules on the variance. This is something unique to ETLV.
- Double click on the entity and define a “Data Rule” on the Quantity column. The rule was something like “Quantity is greater than” 10.
Step 7: Then, I just got into the run mode and tapped on “Run” at the bottom of the screen.
The results were displayed and clearly showed the records that did not match. With very minimal steps, we could demonstrate how to achieve a relatively complex scenario within a single test case in ETL Validator; pretty cool.
