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.