Automating the Power BI Report Testing of Data

Question

Hi All,To implement Power BI data testing of report and automate the process what is the best method?&nbsp;I know there are lot of 3rd party tools but if we want to build it from scratch, what are the key things to consider or anyone has already did that?

lbendlin · Answer

What kind of testing are you looking for? Unit testing, UAT, or usability test?

anonymous · Answer

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.

lbendlin · Answer

That's too vague. Do you want to test if the data made it all the way through to the dataset on the service? You would connect to the XMLA endpoint for that. Or do you want to test if the number renders correctly on the report visual? You would use browser test tools for that. Etc.

brendon · Answer

I'm working for a client that does something similar so I'll give you our experience.&nbsp;&nbsp;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.&nbsp;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.&nbsp;&nbsp;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.

Forum Discussion

Automating the Power BI Report Testing of Data

4 Replies

Related content

Unit testing in report

Automated Purview audit log reports?

Power BI automation testing

Report testing Automation (using Playwright) - unable to click button

Automated refreshes