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.
