Need a suggestion from Datawarehouse and ETL experts. I have a project which has bunch of fact and dimension table, and we wire the facts using surrogate keys to dimension (religiously following kimball). We have had some incidents of worng wiring.
So I want to write few scripts that will do the data validation by doing the cross lookup and checking if each surrogate keys refer to the right record from appliaction database. I just wanted to ask you guys if you had to deal with any data validation in reports? My solution is not very efficient and very tedious to implement, wanted to check if any easier suggestion is there.
PS: I had this question on stackoverflow, but unfortunately got no response. So trying my luck here.
Best Answer
Usually surrogate keys are implemented based on
dimension tablesand externaldata sourcesin order to have coherent and valid data records; theETLprocess, which in charge of extracting data from different sources, transforming and finally delivering data into the data warehouse, must ensure this condition (i.e., surrogate keys refer to the right record).Personally, I did perform a data validation once by simply using a comparison between the OLAP(i.e., Data Warehouse) result and OLTP(i.e., DB sources) result for the same simple analysis query in order to see if there are any incoherent information.