Embed PowerBI Reports in Jupyter Notebook using PowerBIClient

Microsoft PowerBI is a powerful business analytics service that enables users to visualize data and share insights. It provides an intuitive interface for creating interactive dashboards and reports from various data sources. Jupyter Notebook has emerged as a popular tool for data scientists to perform exploratory data analysis (EDA), build machine learning models, and share reproducible research.

Wouldn‘t it be great if we could combine the strengths of PowerBI‘s rich visualizations with the flexibility and interactivity of Jupyter Notebooks? That‘s where the powerbiclient library comes into the picture. It allows embedding PowerBI reports directly inside Jupyter Notebook cells, enabling seamless integration between reporting and analytics workflows.

In this article, we will dive deep into the capabilities of powerbiclient and demonstrate how to embed PowerBI reports in Jupyter Notebooks through practical examples. Whether you are a data analyst, business intelligence professional or a machine learning engineer, this guide will help you to create compelling data narratives by leveraging the best of both worlds.

Setting up the Environment

Before we embark on our journey of embedding reports, let‘s ensure we have all the necessary tools in our arsenal. First and foremost, you need to have Python and Jupyter Notebook installed on your machine. You can install them individually or use a package manager like Anaconda which provides a comprehensive data science platform.

Next, we need to install the powerbiclient library which is available through PyPI (Python Package Index). Open a terminal or command prompt and run the following command:

pip install powerbiclient

This will download and install the latest version of powerbiclient along with its dependencies.

To embed PowerBI reports, you need to have a PowerBI Pro license which allows sharing and collaboration features. If you don‘t have a Pro license, you can sign up for a free trial from the PowerBI website. Once you have the license, you need to generate an access token that will be used to authenticate and authorize requests to the PowerBI service.

There are different types of access tokens depending on the embedding scenario – app owns data, user owns data, or service principal. For the examples in this article, we will use the app owns data scenario where the application (Jupyter Notebook) authenticates to PowerBI service using a master account and the end users are not required to have PowerBI licenses.

To generate an access token, follow these steps:

1. Go to the PowerBI App Registration Tool (https://dev.powerbi.com/apps)
2. Sign in with your PowerBI Pro account
3. Give your application a name and select the required permissions
4. Note down the Client ID and Client Secret
5. Use the Client ID and Secret to obtain an access token by calling the Azure AD OAuth endpoint

Embedding PowerBI Report using Report URL

The simplest way to embed a PowerBI report in Jupyter Notebook is by using its embed URL. An embed URL is a direct link to the report that also contains an access token. To get the embed URL, open the report in PowerBI service, click on File menu and select "Embed" option. Copy the embed URL from the dialog box.

Now we have the embed URL, let‘s use powerbiclient to render the report in a Jupyter Notebook cell. Create a new Python notebook and import the required classes:

from powerbiclient import Report, models

Next, let‘s create an instance of the Report class by passing the embed URL and access token:

embed_url = "https://app.powerbi.com/reportEmbed?reportId=abc123&groupId=xyz456" 
access_token = "eyJ0eXAiOiJKV1QiLCJhbG...ow0KfQ._3lBioDnkLGVeUhN" 
report = Report(embed_url=embed_url, access_token=access_token, token_type=models.TokenType.EMBED)

In the above code, replace the embed_url and access_token with the values obtained from your PowerBI report.

Finally, we can display the report by evaluating the report object in a notebook cell:

report

This will render the PowerBI report inside the Jupyter Notebook interface, allowing you to interact with it just like in the PowerBI service. You can apply filters, change pages, drill down into visuals, and more.

Embedding PowerBI Report using Report ID and Group ID

Another way to embed PowerBI reports is by using the report ID and group ID (also known as workspace ID). This method provides more flexibility as you can generate embed tokens with specific permissions and expiration times.

To find the report ID and group ID, open the report in PowerBI service and look at the URL in the browser address bar. The URL will be in this format:

https://app.powerbi.com/groups/abc123/reports/xyz456/ReportSection

Here, "abc123" is the group ID and "xyz456" is the report ID.

To embed the report using IDs, we need to generate an embed token by making a HTTP request to the PowerBI REST API. We can use the access token obtained in the previous section to authenticate the request.

import requests
group_id = "abc123"
report_id = "xyz456"
url = f"https://api.powerbi.com/v1.0/myorg/groups/{group_id}/reports/{report_id}/GenerateToken"
headers = {
"Authorization": f"Bearer {access_token}",
"Content-Type": "application/json"
}
payload = {
"accessLevel": "View",
"allowSaveAs": "false"
}
response = requests.post(url, headers=headers, json=payload)
if response.status_code == 200:
embed_token = response.json()["token"]
embed_url = response.json()["embedUrl"]
print(f"Embed URL: {embed_url}")
print(f"Embed Token: {embed_token}")
else:
print(f"Error generating embed token: {response.text}")

In the above code, we make a POST request to the GenerateToken endpoint with the group ID and report ID. We also specify the access level (View or Edit) and whether to allow users to save a copy of the report. The response will contain the embed URL and token.

We can now use the embed URL and token to create a Report instance and display it in the notebook:

report = Report(embed_url=embed_url, access_token=embed_token, token_type=models.TokenType.EMBED)
report

Interacting with Embedded Reports from Python Code

One of the key benefits of embedding PowerBI reports in Jupyter Notebook is the ability to interact with them programmatically using Python code. The powerbiclient library provides a rich set of APIs to manipulate and customize the embedded reports.

For example, we can apply filters at report level to slice the data based on certain conditions. Let‘s say we want to filter a sales report by a specific date range:

report.filters.add(models.AdvancedFilter(table="Date", column="Date", logical_operator="And", conditions=[
    models.AdvancedFilterCondition(value="2022-01-01", operator="GreaterThanOrEqual"),
    models.AdvancedFilterCondition(value="2022-12-31", operator="LessThanOrEqual")
]))
report.refresh()

Here, we create an AdvancedFilter object that specifies the table, column, and filter conditions. We then add this filter to the report using the filters property and call the refresh method to apply the changes.

Similarly, we can change the active page of the report using the active_page property:

report.active_page = "Sales by Region"

This will navigate to the specified page in the embedded report.

We can also capture user interactions with the report visuals and handle them in Python code. For instance, when a user clicks on a bar in a chart, we can retrieve the underlying data and perform further analysis or trigger an action.

from IPython.display import display, HTML
def on_click(event_data):
print(f"Clicked visual: {event_data.visual.title}")
print(f"Clicked data point: {event_data.dataPoints[0]}")
report.events.loaded.add_listener(lambda: display(HTML("""

        $(document).ready(function() {
            const iframe = document.querySelector(‘#pbi-iframe‘);
            iframe.addEventListener(‘click‘, function(event) {
                if (event.detail.visual) {
                    const message = {
                        type: ‘visualClicked‘,
                        data: event.detail
                    };
                    iframe.contentWindow.postMessage(message, ‘*‘);
                }
            });
        });
    
""")))
window.comms.on_message(on_click)
report

In this example, we attach a JavaScript event listener to the embedded iframe that listens for click events on visuals. When a visual is clicked, it sends a postMessage with the event data. We capture this message in Python using the on_message event of Jupyter Comms and extract the relevant information like visual title and data points.

Advanced Embedding Scenarios

PowerBI embedding supports various advanced scenarios that cater to different business requirements. Let‘s explore a few of them:

1. Row-level security (RLS): RLS allows applying data filters at the row level based on user roles or permissions. This ensures that users see only the data they are authorized to access. To enable RLS with powerbiclient, you need to generate an embed token with an effective identity that maps to the user roles defined in the PowerBI dataset.

2. Multiple reports: You can embed multiple PowerBI reports in the same Jupyter Notebook by creating separate instances of the Report class with different embed URLs and access tokens. This is useful when you want to compare data from different sources or create a dashboard with multiple visualizations.

3. Dashboards and tiles: Apart from reports, you can also embed PowerBI dashboards and individual tiles (visuals) in Jupyter Notebooks. The process is similar to embedding reports, but you need to use the appropriate embed URLs and IDs for dashboards and tiles.

4. Integration with Python libraries: Embedding PowerBI reports in Jupyter Notebook opens up possibilities for integrating with popular Python libraries for data manipulation, machine learning, and more. For example, you can use pandas to preprocess the data before feeding it to PowerBI, or use scikit-learn to build predictive models and visualize the results in PowerBI.

Best Practices and Considerations

When embedding PowerBI reports in Jupyter Notebook using powerbiclient, there are certain best practices and considerations to keep in mind:

1. Secure access tokens: Embed tokens are like keys to your PowerBI reports, so it‘s crucial to handle them securely. Avoid hardcoding tokens in notebook cells or version control systems. Instead, use environment variables or secret managers to store and retrieve tokens.

2. Error handling: Embedding reports involves making API calls to the PowerBI service, which can occasionally result in errors due to network issues, authentication failures, or server-side problems. Make sure to wrap the embedding code in try-except blocks and provide meaningful error messages to users.

3. Performance optimization: Embedding large or complex reports can impact the performance and responsiveness of Jupyter Notebooks. Consider using report filters, page navigation, and visual interactions judiciously to minimize data transfer and rendering overhead. You can also enable caching and use smaller datasets for faster loading times.

4. Limitations: While embedding PowerBI reports in Jupyter Notebook offers many benefits, there are certain limitations compared to using the PowerBI service directly. For instance, you may not have access to all the features and settings available in the PowerBI interface. Also, embedding relies on network connectivity, so offline access to reports may not be possible.

Real-world Use Cases

Now that we have explored the technical aspects of embedding PowerBI reports in Jupyter Notebook, let‘s look at some real-world use cases where this integration can be beneficial:

1. Exploratory data analysis (EDA): Data scientists often use Jupyter Notebooks for EDA to understand the structure, patterns, and relationships in the data. By embedding PowerBI reports in the notebook, they can visualize the data interactively and derive insights more effectively. This combination of code, markdown, and interactive visuals enhances the EDA workflow.

2. Machine learning workflows: Jupyter Notebook is a popular tool for building and training machine learning models. With PowerBI embedding, data scientists can create interactive dashboards to monitor the model performance, visualize feature importance, and compare different algorithms. This helps in making informed decisions and communicating results to stakeholders.

3. Collaborative data storytelling: Jupyter Notebooks provide a narrative-style interface for presenting data analysis and findings. By embedding PowerBI reports, analysts can create engaging data stories that combine text, code, and interactive visualizations. These notebooks can be easily shared with colleagues or published online for wider accessibility.

4. Web applications: PowerBI embedding is not limited to Jupyter Notebooks; it can also be used in web applications built with frameworks like Flask or Django. Jupyter Widgets, which are interactive UI components, can be used to create a bridge between the notebook and the web application. This allows building data-driven applications with PowerBI visualizations without the need for separate dashboarding tools.

Conclusion

In this article, we have explored the powerful combination of PowerBI and Jupyter Notebook using the powerbiclient library. We learned how to embed PowerBI reports in notebook cells using embed URLs or report IDs, how to interact with reports programmatically using Python code, and how to handle advanced embedding scenarios like row-level security and multiple reports.

By leveraging the strengths of PowerBI‘s interactive visualizations and Jupyter Notebook‘s flexible and collaborative environment, data professionals can create compelling data narratives and make data-driven decisions more effectively. The integration of PowerBI and Jupyter Notebook opens up new possibilities for exploratory data analysis, machine learning workflows, and data storytelling.

As the powerbiclient library continues to evolve, we can expect more features and improvements to make the embedding experience even smoother. Microsoft is actively investing in the PowerBI ecosystem, and the integration with Jupyter Notebook is a testament to their commitment to empowering data professionals.

To learn more about PowerBI embedding and Jupyter Notebooks, check out the following resources:

• PowerBI Embedded Documentation: https://docs.microsoft.com/en-us/power-bi/developer/embedded/
• Jupyter Notebook Documentation: https://jupyter-notebook.readthedocs.io/
• PowerBI Developer Center: https://powerbi.microsoft.com/en-us/developers/

Happy embedding and happy data exploration!