Forum Discussion
Automating the Power BI Report Testing of Data
We are actually looking at Comparing the Data from Source to the data published on the Report.
Eg: If Sales Amount in November : 9863$, we want to validate the amount showing up is correct or not from the source. and automate this process.
I'm working for a client that does something similar so I'll give you our experience.
We have an ETL that extracts data from our source systems and feeds our reports. Our test process is divided into two main focuses: ETL validation and report testing. The ETL validation tests check that the data has been taken from one stage to another and transformed in the way described by the requirements (in our case, a spreadsheet created by the data modeler). The report testing builds on top of this by comparing the last stage in the data warehouse to what's displayed in the report.
The report testing is a framework we built and uses Selenium to interact with the Power BI service to extract data from the visuals and then compare them to the result of a SQL query to our data warehouse. The easiest way to do this is to export data from the visual using the visual header and exporting to CSV. If you can't do that, the visual header also has an option for you to view the visual as a table, giving you a standard format for you test automation to extract data. I would highly discourage trying to extract data straight from the visuals as visuals like clustered bar/column charts can be tricky to align values to categories.
I will say, however, that even though the Selenium route is definitely possible, you'll need to evaluate whether you're happy with the cost of maintenance and the distinct skillset it requires. We've now got 1500+ tests and are constantly adding to it and it's a full-time job for 4 resources. We've also found that, most of the time at least, changes to Power BI reports might be fairly low effort for a developer but would have a large testing impact, which has meant that the amount of testing work has been a constraint on how many report changes we can take in a sprint. If you're happy with that tradeoff, then building your own framework might be worth it.
I built PowerTester for scenarios like this, where you want to check that the report displays the right data by comparing to a source database but without the extra work of building a test framework and interacting with the Power BI frontend. Feel free to check it out if the selenium approach doesn't work for you.