March 6, 2013 at 5:53 pm
Hi Guys,
We have a 3rd party developing our data warehouse, and we do the SSRS devevelopment in house via BIDS and report builder. The DW devlopers want to push a new version of the data warehouse to us in a few weeks and they have changed the naming conventions on roughly 50 columns in 30+ tables. As opposed to taking a reactive approach and waiting for the new wharehouse to go live and see which reports break, I'm wondering if it would be possible for me to execute a script against the report server to identify which reports have the columns that would be impacted by the change? In my previous role I've written scripts to convert the .xml in SSIS packackages into sting data and load it into table that I can query against , but SSRS is somewhat new to me and I'm still waiting for my permissions to be setup so I can execute queries.
Also, If I can query to identify the changes, is it possible to then write a SQL script to alter the .xml in the RDL files so we can avoid having to open each report and make the changes one at a time?
Lastly, are there any good tools out there that would allow us to do an impact analysis in an automated fashion as opposed to using SQL queries whenever schema changes occour?
Thanks in advance!
March 12, 2013 at 5:49 pm
Bump
March 13, 2013 at 8:22 am
Code-1029433 (3/6/2013)
Hi Guys,We have a 3rd party developing our data warehouse, and we do the SSRS devevelopment in house via BIDS and report builder. The DW devlopers want to push a new version of the data warehouse to us in a few weeks and they have changed the naming conventions on roughly 50 columns in 30+ tables. As opposed to taking a reactive approach and waiting for the new wharehouse to go live and see which reports break, I'm wondering if it would be possible for me to execute a script against the report server to identify which reports have the columns that would be impacted by the change? In my previous role I've written scripts to convert the .xml in SSIS packackages into sting data and load it into table that I can query against , but SSRS is somewhat new to me and I'm still waiting for my permissions to be setup so I can execute queries.
Also, If I can query to identify the changes, is it possible to then write a SQL script to alter the .xml in the RDL files so we can avoid having to open each report and make the changes one at a time?
Lastly, are there any good tools out there that would allow us to do an impact analysis in an automated fashion as opposed to using SQL queries whenever schema changes occour?
Thanks in advance!
First of all, I would pitch a screaming fit about the vendor changing column names in a data warehouse. The vendor should know what a major disruption this could cause (i.e., breaking EVERY query and report that refers to those columns). The vendor should do so only when there is a significant justification.
Second, RDL files are just like any other XML files. I'm not sure that you can work directly against the SSRS database as you may be thinking, though. You could pull the .rdl files of the deployed reports into a SQL Server table with SSIS as you have before and use XQuery (check Books Online for learning resources) to search and modify it. You probably should use the XML datatype for the column that holds the RDL, though, rather than a varchar or nvarchar. Once you have made the corrections, you could create new .rdl files and deploy them in place of the old ones.
As an alternative to having to change all your reports whenever the table definitions change, what about implementing an abstraction layer of views and coding the reports against these? When table definitions change, you need only update the view definitions accordingly rather than having to find and change all the references to the tables in the reports and queries. The reports and queries will never know the difference. In fact, this may be a reasonable request to make of your vendor - if it is going to be tinkering with the table definitions, provide and maintain the views that don't change.
Jason Wolfkill
March 13, 2013 at 3:53 pm
Thanks for the feedback wolfkill!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply