test case

[r.julia]

Hi everyone,
Im just wondering what test cases can be created for DW jobs. I mean for example, I have an ODBC or Oracle source file, and loading to a target ODBC or Oracle database. there could be hash file lookups and other contraints. this looks so obvious to test for errors or some defects. What kind of test cases can be written for this.

To make it clearer, if there are 500records in teh source file and the target has 300files.In the transformer is some hashfile lookup. Whats there to be tested?

Im sorry this might be a stupid question, but Ive never tested DW jobs before.

Ex:
hashfile
||
SourceOracle(200)=>transformer=>TargetOracle(150)


Thanks
Julia

[pmadhavi]

If the jobs are incremental in nature, u have to check whether new records are getting populated with current timestamp
and the number of records coming from the lookups.
If existing rows have to be updated, pls check the created date for the row and updated date once the increment is done.

And also if there are any standards for the naming conventions of the jobs, we need to have different test cases for them too.

[kduke]

I think all ETL should be ignored in QA. You should QA the results without knowing anything about or how the ETL works. The source to target documents should be used to design QA scripts. How many rows are in your source tables? How many are in the target? If they match then compare more specific things like are there ways to join the source to the target through something like a db link? If so then:

select count(*) from source, target where sourceId = targetId

Then add

and SourceCol = TargetCol

This is for dimension tables. For fact tables you can

sum(SourceCol) as SourceMetric, sum(TargetCol) as TargetMetric

If things agree in aggregate then check the same for one specific customer or all customers in Texas.

If you cannot join source to target then calculate these separately then compare. You should do this anyway. The join will tell you what made it into the warehouse matches or does not match.