Forum Discussion

iain_robertson's avatar
iain_robertson
Regular Visitor
2 years ago
Solved

Testing Database Changes Against Suite of Reports

Hi All, 

 

I've inherited a mature SQL Server data environment that has been completely uncontrolled for a long time. It had been run as a single production server environment, i.e. no dev or test servers. It contains significant amounts of redundant code and objects that I'd like to clear out. But. We also have a large PBI estate, spread across 20+ workspaces and with many reports run on an ad-hoc and infrequent basis. 

 

I have set up a test server that mirrors my production environment. As I remove what I believe to be redundant objects, I need to verify that this won't break any of the existing PBI reports by executing them against the test server. 

 

Given the volume of reports involved, I don't want to do this manually. So how can I do this programatically? 

 

I've done some investigation of the PBI API, but this hasn't helped. I was hoping that I could simply extract the object names / query definitions from the reports and then execute these against the test server. But this doesn't appear to be possible. 

 

I have implemented an extended events session on the production server to trap queries generated by PBI. But this won't capture everything - only the reports that are executed in the period that the session is running for. 

 

This must be a solvable problem. Thoughts and advice appreciated. 

  • Use the Scanner API to harvest all queries for the Power BI data sources. That will tell you which SQL server tables, views etc are contributing.

2 Replies