I am new to Toad for Oracle. Can Toad be used to compare tables and schemas before and after an ETL process is run? I am trying to figure out a way to verify the data is correct. Any thoughts?
If you are talking about with a single table (or multiple), comparing the data in them before a process against the data in the same tables after a process, the answer is no, Toad doesn’t have anything like that.
If you have two identical (or similar) structured tables and you want to compare data in them, then yes, Toad has “compare table data” feature. It’s in the main menu, under Database -> Compare -> Data. This only compares one pair of tables at a time - so you’d have to set it up for each table.
If you want to compare entire schemas, look under Database -> Compare -> Schemas. This compares the structure of tables and other objects, but not the data (the closest it will come to comparing data is by comparing row count of each table).
Hope that helps.
John,
Thanks for the info. I was tasked with coming up with a test plan to verify the data before and after the ETL process is run.
As John mentioned, you could try using Compare Data and/or Schema Compare, and with that you might look at using Automation Designer to automate the tasks if you find those work for you (there’s a ton of documentation on Automation Designer on Toad World and in the Help). On a side note, there’s really not much to suggest here, since we don’t know exactly what you want to compare, and how much data you are talking about comparing, etc.
Generally speaking tho, I always found it easiest to keep a record of the changes in the actually conversion program (in your case an ETL program). What I’ve always done is make log files/tables during these types of programs, that way if you ever want to know what changed, then you have it all in one place. Granted, it requires more effort to program it to do this and make sure exceptions are handled properly, but it’s the cleanest and easiest way to know exactly what happened during a process. I would always record how many records were read, how many records were updated/inserted, reported exceptions, etc. Then if anyone ever asks what changed, you just refer to the log and your done.
Dennis, Thanks for the great input. We are currently using Hyperion and moving toward OBIEE. As we discovered we need to build a data warehouse to handle all of the requests (pulling a slew of data from OLTP was no feasible). The ETL tool we are using is Informatica. We have built some schemas, and I am looking to do validation testing.
@Zulu79
Please tell me are you really planning to compare tens or hundreds of millions of records, what is more that normal number of records for any DWH table?
Please tell me I’m really curious…or I missed something.
Brg
Damir
Damir,
I am looking to set up a test case using select tables that contain various complex calculations. My goal is to ensure Informatica (our new ETL tool) handled the calculations correctly . I want to be able to deliver test results prior to implementation of the full DWH. We are just starting the ETL process now.
first you said:
compare tables and schemas before and after an ETL process is run
and now:
et up a test case using select tables that contain various complex calculations
and on the end I do not understand what do you want.
Once again, there is no tool that can compare all rows/columns in any bigger table. Toad has that feature but with limited differences and very courious aproach how actually compare is performed. on bigger tablas this is not very efficiant and recommended at all.