June 5, 2012 at 10:01 am
Here is a simple case study for which we are looking to design a report
We have multiple source systems of data (All are SQL Server) that are ETL'ed into another reporting data mart that is primarily used for SSRS reporting. More often than not we have issues with data being extracted or loaded causing for either duplicate records in the destination or significantly less number of records. We were looking to create a Reconcilliation report on the staging database that could be used to identify any discrepency immediately.
Any particular suggestion to this regard of how it can be approached or Any tools that already do this that could be considered?
June 6, 2012 at 11:23 am
I just make it a validation step in the load package, failing the package if numbers don't jive.
After I have a staging table popped, I do a "second dip" and count rows per client. We have automated processes going on all the time, so while we do expect change, we only accept it within a certain variance.
We also write record counts to audit tables so that when something does go wrong, we have a place to go to see which source(s) failed.
Package contains email step to tell us we're failing the package and why (in our case, I dump a grid with "failing clients" that includes row counts).
Another variation we have used is to compare every field in staging against every field in production and write changed recs to an audit table. Again, if change is within threshold, package succeeds.
For simplicity, validation on/off, record threshold to be validated (some sources have so few records that any change is a huge variance, so they have to have a certain amt of records to be validated) and the percentage variance acceptable. Very easy for anyone to change settings in my absence without having to change/redeploy the package or muck around in the job passing config variables.
June 7, 2012 at 6:33 am
Do you know what generally causes the inconsistencies? What kind of volume of data are you dealing with?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply