Finally, Automation Testing for Power BI
Boost Report Quality & Efficiency
The need
Our team is currently working on a product, which features a web application that incorporates Power BI reports. As part of our responsibilities, we create and maintain these reports, publish them to the Power BI Service, and embed them within our web application.
During the report development process, we encountered several issues:
- Reports uploaded with filters intended solely for debugging purposes, preventing users from seeing their data
- Unintentional changes to visual layouts, such as location and size
- Datasets not being refreshed for a while, leading to outdated information in reports
- Incorrect relationships between tables, resulting in no data displayed in visuals
To address these, we’ve decided to implement an automated testing solution to detect and prevent similar changes.
The challenge
My first step in automating Power BI report testing was to explore using the Power BI REST API or Selenium. However, I quickly encountered challenges. The REST API provides minimal information, such as report name and ID, and lacks access to visual elements and their properties. Additionally, writing and maintaining Selenium tests for Power BI reports is difficult and time-consuming. These factors make it challenging to create automated tests for verifying visual layouts, filters, and other components, requiring alternative methods and tools to ensure report quality.
The solution
Terminology explanation
In this section, I will use terms from Power BI.
Here is a short explanation for each:
- Report: A collection of visualizations and data insights.
- Page: A single view within a report containing various visuals.
- Visual: A graphical representation of data (e.g., chart, table, map).
- Filter: A tool for focusing on specific data within a report or visual.
technology stack
The technology stack used for the solution:
- powerbi-client: a client-side library that allows you, using JavaScript, to embed Power-BI Reports into your web application and control your embedded content.
- Jest: a widely-used JavaScript testing framework known for its easy setup and use.
- Playwright: a powerful browser automation library that enables developers to interact with web pages using programming languages.
I chose to use, for testing, the same library that we use to embed our reports ‘powerbi-client’. I chose it for testing, as it provides access to the report’s layout, filters, and data.
For the test runner, I selected Jest.
To embed the report and retrieve metadata and data from it, I required a browser engine to load the powerbi-client. For this purpose, I selected Playwright.
Let’s see the magic happen:
How it works:
First, we need to build a ReportEmbedConfiguration object for the Power-BI embed process. For this configuration, I need an access token for embedding the reports and the embed URL of the report.
For simplification, let's assume I have those values. I will add the code that fetches them at the end of this post.
This is what the configuration object looks like:
const embedConfiguration: IReportEmbedConfiguration = {
type: 'report',
tokenType: 1,
accessToken: accessToken.token,
embedUrl: reportDetails.embedUrl,
settings: {
filterPaneEnabled: false,
navContentPaneEnabled: false,
layoutType: 1,
bars: {
actionBar: {
visible: false,
},
},
customLayout: {
reportAlignment: 1,
displayOption: 1,
},
},
};Secondly, we need to open the browser and inject the powerbi-client:
const browser = await chromium.launch({ args: ['--disable-web-security'], headless: false });
const page = await browser.newPage();
await page.goto('about:blank');
await page.addScriptTag({url: 'https://cdnjs.cloudflare.com/ajax/libs/powerbi-client/2.22.1/powerbi.js'});Now, let's embed the report:
await page.evaluate((config) => {
const report = powerbi.embed(document.body, config);
// Create a promise that resolves when the 'rendered' event is triggered
return new Promise<void>((resolve) => {
report.off('rendered');
report.on('rendered', () => {
resolve();
});
});
}, embedConfiguration)This code snippet uses page.evaluate to embed a Power BI report and to create a promise that resolves when the 'rendered' event is triggered. The promise ensures that the report has finished rendering before proceeding with further actions or assertions in your test.
At this point, we should have a rendered report we can start testing.
What does the test look like?
Here is an example for testing the layout of all the visuals on the first page of the report:
it('snapshot page visuals', async () => {
const actualVisuals = await page.evaluate(async () => {
const report = powerbi.embeds[0];
const pages = await report.getPages();
const visuals = await pages[0].getVisuals();
return visuals.map((visual) => {
return {
layout: visual.layout,
name: visual.name,
title: visual.title,
type: visual.type
}
});
});
expect(actualVisuals).toMatchObject(expectedVisuals);
});This code snippet defines a test case that captures Power BI report visuals properties and compares them to expected values. It uses page.evaluate to execute JavaScript within the browser context and powerbi.embeds[0] to retrieve the visuals from the first (and only) embedded report.
Here is an example of the structure of expectedVisuals:
const expectedVisuals = [
{
"layout": {
"displayState": {"mode": 0},
"height": 300,
"width": 1775,
"x": 11,
"y": 63,
"z": 0,
},
"name": "dc0934ec0b6a354a0a01",
"title": "",
"type": "columnChart",
},
{
"layout": {
"displayState": {"mode": 0},
"height": 300,
"width": 1775,
"x": 13,
"y": 428,
"z": 14000,
},
"name": "de14c62f0c027a89015c",
"title": "Sales",
"type": "tableEx",
}
]In the same way, we can test filters of a report, a page, or a visual.
To test a specific visual, we need its auto-generated ID, which Power BI creates when adding a new visual to a page. We can prepare a mapping between these IDs and readable descriptions, like this:
const reportVisuals = [
{description: 'Sales Column Chart', name: 'dc0934ec0b6a354a0a01'},
{description: 'Sales Table', name: 'de14c62f0c027a89015c'}
]Using Jest’s it.each function, we can iterate through all the visuals in the report and test their filters with Jest’s snapshot testing:
it.each(reportVisuals)(`snapshot the filters for visual: $description`, async ({name}) => {
const actualFilters = await page.evaluate(async (visualName: string) => {
const report = powerbi.embeds[0];
const pages = await report.getPages();
const visuals = await pages[0].getVisuals();
const visual = visuals.find((visual) => visual.name===visualName);
return visual.getFilters();
}, name);
expect(actualFilters).toMatchSnapshot();
});Similarly, we can test if the visuals have data.
The visual.expotData() function returns data in CSV format. To ensure there is data (the visual is not empty), we expect the CSV to contain at least two rows: one for headers and one for values:
it.each(reportVisuals)(`there is data for visual: $description`, async ({name}) => {
const res = await page.evaluate(async (visualName: string) => {
const report = powerbi.embeds[0];
const pages = await report.getPages();
const visuals = await pages[0].getVisuals();
const visual = visuals.find((visual) => visual.name===visualName);
return visual.exportData();
}, name);
const dataLines = res.data.split('\n');
expect(dataLines.length).toBeGreaterThanOrEqual(2);
});Conclusion
Developing a test suite using Jest and Playwright for testing Power BI Reports revolutionized my approach to report testing. It provided a comprehensive, efficient, and cost-effective solution that overcame the limitations of existing methods. The best part is that everyone can use these tools without worrying about royalties, making them accessible to all.
If you’re facing similar challenges with Power BI report testing, I highly recommend giving Jest and Playwright a try. You might find that it’s the game-changer you’ve been looking for.
Appendix
Here’s the snippet used to obtain a report’s embed URL and access token:
import * as adal from 'adal-node';
import axios from 'axios'
function getPowerBIAccessToken(): Promise<string> {
return new Promise<string>((resolve, reject) => {
const context = new adal.AuthenticationContext(`https://login.microsoftonline.com/${tenantId}/v2.0`);
const clientSecret = process.env.POWERBI_SP_SECRET
context.acquireTokenWithClientCredentials(
'https://analysis.windows.net/powerbi/api',
clientId,
clientSecret,
(error, tokenResponse) => {
if (error) {
reject(error);
} else {
resolve((tokenResponse as adal.TokenResponse).accessToken);
}
});
});
}
async function getReportDetails(workspaceId, reportId) {
const accessToken = await getPowerBIAccessToken()
const url = `https://api.powerbi.com/v1.0/myorg/groups/${workspaceId}/reports/${reportId}`;
const http = axios.create({
headers: {
Authorization: `Bearer ${accessToken}`,
},
});
const resReportDetails = await http.get(url)
const reportDetails = {
reportId: resReportDetails.data.id,
reportName: resReportDetails.data.name,
embedUrl: resReportDetails.data.embedUrl,
};
// Create list of datasets
const datasetIds = [{id: resReportDetails.data.datasetId}];
const resToken = await http.post('https://api.powerbi.com/v1.0/myorg/GenerateToken',
{
datasets: datasetIds,
reports: [{id: reportId}],
targetWorkspaces: [{id: workspaceId}],
},
)
.catch((err) => {
console.log(err.toJSON())
return {data: {}}
})
return {accessToken: resToken.data, reportDetails};
}
