June 17, 2013 at 8:27 pm
Help! Can you point me too some resources that will help me to work out how to identify the source and transformations on data used by a report. The report is based on datamart data which is copied and possible transformed from production databases by ssis and sp's.
The reason I need this is because I have been given the task of finding the root cause of why 2 reports are different and I am not very familiar with SSIS. One of the reports is from a production system and the other is from a datamart which is populated by SSIS. The report is populated from a number of tables some of which are derived and others a direct copy of the production data.
tia
Martin
June 18, 2013 at 9:51 am
martin.walter (6/17/2013)
Help! Can you point me too some resources that will help me to work out how to identify the source and transformations on data used by a report. The report is based on datamart data which is copied and possible transformed from production databases by ssis and sp's.The reason I need this is because I have been given the task of finding the root cause of why 2 reports are different and I am not very familiar with SSIS. One of the reports is from a production system and the other is from a datamart which is populated by SSIS. The report is populated from a number of tables some of which are derived and others a direct copy of the production data.
tia
Martin
Presumably you can open the SSIS package and from there identify the source, transforms, and destination.
Follow from the start of the package to see what is being done. Take note of the connection managers to be very certain of the source data.
It would also be helpful for you to know how the reports vary so you have a focal point vs having to figure out the entire process when all they really want to know is "why is this field null so often?" or something.
This is probably a good starting place for SSIS, seeing how someone else did something.
Please post back with specific questions. Otherwise, just follow the control flow and inspect the different components to see what's going on.
June 18, 2013 at 7:14 pm
The package is too complicated to specify well enough so that you could answer a question on how to solve my problem. That's why I was asking here. But thanks.
The package does an ETL from a production database to a datamart. There are 100 or so tables involved and there are 200 or so "boxes" in the package. Most tables have a pair of "boxes" a "delete" followed by a "load" box. Many of the loads are straight copies from the prod database but some call stored procedures that transform the data or load data from other tables in the datamart. Some of the tables in the datamart have no ETL processes associated for them - so I'm guessing that they contain config data.
I spent several fun hours yesterday going through the "boxes" manually. I've found that one of the tables that the report uses is loaded by a stored procedure that extracts data from other datamart tables. One of these tables is a config table that contains type data that is missing a value that the user is complaining isn't being picked up by the report. Bingo! I hope. I added the missing value to the table. The type name was easy, but it also contains 4 "ordering" columns which I had to guess.
I ran the ETL for the table and then ran the report and got different values. Unfortunately I have got no idea of whether the new values are correct or not. We've got no documentation on the report of the ETL process. I've sent the developer back to the business to check it out.
This was far from an ideal solution but the best I could do without reengineering the whole datamart. - which should be done, but I've got other stuff to do. Hopefully I've found that only table that needed to be fixed.
Martin
June 19, 2013 at 1:47 pm
oh, goodness, that's a biggie. We actually scrapped our web reports for this very reason once it became an enormous task to validate new BI deliverables against old reports that had been modified by many hands over the years. It just wasn't worth it.
I think you're going to want to snoop around to see who/how/why the values got changed in the first place. Person vs process, one Ooops or a recurring change to keep you up nights.
Best of luck!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply