Tutorial Guide: Embedding Power BI Reports in Teams & SharePoint with On-Premises Data

Complete Guide: Embedding Power BI Reports in Teams & SharePoint with On-Premises Data

Complete Guide: Embedding Power BI Reports in Teams & SharePoint with On-Premises Data

Embedding Power BI reports into Microsoft Teams and SharePoint is a common and powerful enterprise scenario, enabling seamless collaboration and information sharing. When your data resides on-premises (e.g., in SQL Server, Oracle, or local files), you'll need the On-Premises Data Gateway to ensure your reports stay updated in the Power BI Service. This guide will walk you through the process step-by-step, helping you understand and implement this properly.

🔸 Overview

You can embed Power BI reports directly into:

  • Microsoft Teams: For fostering collaboration, discussions, and quick access to insights within team channels.
  • SharePoint Online: For displaying interactive reports on intranet portals, project sites, or any SharePoint page, making data accessible where users work.

When your data is on-premises (e.g., SQL Server, Oracle, local Excel files, etc.), you need the On-Premises Data Gateway to securely connect Power BI Service to your local data sources and keep the reports updated.

🔸 Prerequisites

Before you start, ensure you have the following:

  • ✅ Power BI Pro License: Required for users who are embedding reports and for sharing content.
  • ✅ Access to:
  • ✅ Installed On-Premises Data Gateway: The gateway should be installed on a machine that has access to your on-premises data sources.
  • ✅ Published Report to Power BI Workspace: You should have a Power BI report already published to a workspace in Power BI Service that uses your on-premises data.

🔸 Step-by-Step Guide

🧩 Step 1: Setup On-Premises Data Gateway

This step ensures Power BI Service can securely connect to your local data. If you've already done this, you can skip to Step 2.

  • Download and install: Get the latest gateway installer from https://powerbi.microsoft.com/gateway/.
  • Installation location: Install the gateway on a reliable, always-on server or computer that has constant access to your on-premises data sources.
  • Sign in: During installation, sign in with your Power BI (organizational) account.
  • Choose mode: Select Standard mode (recommended) as it supports Power BI, Power Apps, Azure Logic Apps, and Azure Analysis Services.
  • Register gateway: Choose to register a new gateway and provide a unique name for it. You'll also set a recovery key – keep this safe!
  • Configure data source in Power BI Service:
    • Once installed, go to Power BI Service (app.powerbi.com).
    • Click the Settings icon (gear) in the top right corner, then select Manage Gateways.
    • Select your registered gateway.
    • Click Add data source.
    • Configure the details for your on-premises data source (e.g., SQL Server name, database name).
    • Choose the appropriate authentication method (e.g., Windows, Basic) and enter the credentials.
    • Test connection to ensure everything is set up correctly.

📊 Step 2: Create & Publish Power BI Report

This step covers creating your report and ensuring its dataset is linked to the Gateway for refreshes.

  • Open Power BI Desktop.
  • Connect to your on-premises data source: Use "Get Data" to connect to your SQL Server, Oracle database, or other local data source.
  • Design your report: Create your visuals, pages, and any necessary measures or calculated columns.
  • Save and Publish: Save your report (.pbix file) and then click Publish from the Home tab to publish it to your desired workspace in Power BI Service.
  • In the Power BI Service (configure dataset refresh):
    • Go to your Workspace.
    • Navigate to the "Datasets + dataflows" tab.
    • Find your published dataset and click the three dots (... More options) next to it, then select Settings.
    • Under Gateway connection, expand the section and ensure your configured gateway is enabled and selected.
    • Under Data source credentials, verify or re-enter your SQL Server (or other data source) credentials if prompted.
    • Go to Scheduled refresh, turn it ON, and set your desired refresh frequency (e.g., daily, hourly). This ensures your report data stays fresh.

💬 Step 3: Embed in Microsoft Teams

There are two primary ways to embed a Power BI report into Microsoft Teams:

🟢 Option A: Add Report as a Tab (Recommended for persistent access)

  • Go to a Team in Microsoft Teams.
  • Select the desired Channel where you want to embed the report.
  • Click the ➕ (Add a Tab) button at the top of the channel.
  • Search for and choose Power BI from the app list.
  • In the Power BI tab configuration, select the appropriate workspace and then choose your published report.
  • Click Save.

🔐 Important: Users who view this tab in Teams must have a Power BI Pro license (or the report must be in a Premium workspace) and possess the necessary permissions to access the report and its underlying dataset in Power BI Service.

🟢 Option B: Paste Link in a Chat (Quick sharing)

  • In Power BI Service, open the report you want to share.
  • Click Share > Copy link (or "Copy a link to this report").
  • Go to a Teams chat or channel.
  • Paste the copied link directly into the message box. Teams will automatically generate a rich preview of the report.

🌐 Step 4: Embed in SharePoint Online

Embed your Power BI report directly into a SharePoint page.

  • Go to Power BI Service (app.powerbi.com).
  • Open the report you wish to embed.
  • Click File > Embed report > SharePoint Online.
  • Power BI will generate an Embed URL. Copy this URL.
  • In your SharePoint Online site:
    • Navigate to the page where you want to embed the report.
    • Click Edit on the page.
    • Click the ➕ icon to add a new web part.
    • Search for and select the Power BI web part.
    • In the web part properties pane, paste the copied Embed URL into the "Power BI report link" field.
    • You can adjust display options like page name, display pane, and filter pane visibility.
    • Click Publish or Republish the SharePoint page.

🔐 Same access rules apply: Users viewing the report on SharePoint need a Power BI Pro license (or Premium workspace access) and explicit permissions to the report and dataset in Power BI Service.

🔒 Permissions & Access

Understanding permissions is critical for successful embedding:

  • All users viewing the embedded report must have a Power BI Pro license, or the report must reside in a Power BI Premium capacity workspace accessible to them.
  • Dataset access must be given explicitly: Even if a user can see the report, they also need "Build" permissions on the underlying dataset to interact with it.
  • Use RLS (Row-Level Security) if needed for user-based data filtering. If you've defined RLS roles in your Power BI Desktop report, ensure they are properly configured and assigned to users in Power BI Service. RLS will automatically apply when users view the embedded report.

✅ Testing Gateway with On-Prem Data

After embedding, perform these checks:

  • Go to the report online (directly in Power BI Service or via Teams/SharePoint).
  • Make sure the visuals load correctly and you don't see any "can't display" or data connection errors.
  • In Power BI Service, go to your dataset settings and check the Scheduled Refresh section for the "last refresh" timestamp to confirm it's updating.
  • If errors appear, check the gateway logs on the machine where the gateway is installed for more detailed troubleshooting information.

📌 Notes

  • Teams and SharePoint embedding methods are designed for the "User Owns Data" model. This means the user viewing the report must have their own Power BI license and permissions to the report. These methods do not use embed tokens or the Power BI Embedded SDK.
  • The On-Premises Data Gateway must be online and configured correctly with data source mappings and credentials for refreshes to succeed.
  • RLS works seamlessly with embedded reports if you've defined roles in the dataset and assigned users to those roles in Power BI Service.

💡 Bonus Tip

If you want to embed Power BI reports into a custom web application where your application manages user authentication and access (known as the "App Owns Data" scenario), you would use the Power BI Embedded service along with the Power BI REST API and JavaScript SDK. Teams and SharePoint embedding, as covered here, are simpler and leverage the "User Owns Data" model for quick integration.

Raushan Ranjan

Microsoft Certified Trainer

.NET | Azure | Power Platform | WPF | Qt/QML Developer

Power BI Developer | Data Analyst

📞 +91 82858 62455
🌐 raushanranjan.azurewebsites.net
🔗 linkedin.com/in/raushanranjan

Comments

Post a Comment

Popular posts from this blog

Module 1 - Lesson 1: Getting Started with Power BI

Image
Unlocking Insights: Your Journey into Data Analysis with Power BI Lesson 1: Getting Started with Power BI Learn with Raushan Data Analysis with Power BI Hey data enthusiasts! Ever wondered how raw numbers transform into compelling stories and drive impactful decisions? Welcome to the world of data analysis, and your trusty companion on this journey is none other than Power BI! In this first lesson, we'll lay the groundwork, understanding why data analysis is crucial, the different flavors of analysis, the vital role of a Data Analyst, and how Power BI fits into their workflow. Why Data Analysis? Unearthing the 'Why' Behind the Numbers Imagine a vast ocean of data generated every second – from sales figures and website clicks to sensor readings and customer feedback. Without analysis, this data is like a treasure chest with...
Read more

Power BI Advanced learning

RR Education: Power BI Advanced Power BI Advanced Learning Learn with Raushan Welcome to the new series of blogs on Power BI Advanced! In this series, we'll delve into more powerful tools and techniques that will take your data analysis and reporting skills to the next level. Explore the Modules below to learn more about each area. Module 1: Begin with Power BI This module covers the fundamentals of using Power BI. You'll learn why data analysis is key, how Data Analysts use Power BI, and the essentials of connecting to diverse data sources via different methods (Import, DirectQuery, Live Connection). We'll also address common data import errors. Lesson 1       Lesson 2      ...
Read more

Module 1 - Lesson 2: Getting Data from Multiple Sources

Image
Module 1 - Lesson 2: Mastering Data Acquisition in Power BI Lesson 2: Getting Data from Multiple Sources Learn with Raushan Getting Data in Power BI Welcome back, aspiring data analysts! In our previous lesson, we explored the fundamentals of data analysis and the role of Power BI. Now, let's get our hands dirty with the crucial first step: bringing data into Power BI from various sources. Think of Power BI as your central hub, ready to ingest data from all corners of your business or domain. Understanding Data Sources: Where Does Our Information Live? Data can reside in a multitude of places. Understanding these sources is the first step towards connecting with them. Common data sources include: Databases: Structured collections of data organized for easy access and management (e.g., SQL Server, Oracle, MySQL)...
Read more

Module 1 - Lesson 3: Resolve Data Import Errors in Power BI

Image
Module 1 - Lesson 3: Navigating and Resolving Data Import Errors in Power BI Lesson 3: Resolve Data Import Errors Learn with Raushan Navigating and Resolving Data Import Errors in Power BI Welcome back! You've learned about connecting to various data sources in Power BI. However, the journey of data integration isn't always smooth. Sometimes, you might encounter errors during the data import process. Fear not! This lesson will equip you with the knowledge and techniques to identify and resolve common data import errors in Power BI. Web Authentication and Loading Tables: The Web Connection Hurdle Connecting to data from the web can sometimes present authentication challenges. Websites often require logins or specific authentication methods to access their data. Web Authentication Technical Definition: Web authentication is the process of verifying the i...
Read more

Module 2 - Lesson 1: Introduction to Power Query Editor

Image
Module 2 - Lesson 1: Introduction to Power Query Editor Lesson 1: Introduction to Power Query Editor Power Query Editor in Power BI Welcome back, data wranglers! We've successfully connected to our data sources and navigated potential import errors in module 1. Now, it's time to delve into the heart of data preparation in Power BI: the Power Query Editor. Think of this as your data laboratory, where you can clean, shape, and transform raw data into a polished format ready for insightful analysis and stunning visualizations. What is the Power Query Editor? Your Data Transformation Hub The Power Query Editor is a powerful tool built into Power BI Desktop that allows you to perform Extract, Transform, Load (ETL) processes on your data. It provides a user-friendly interface with a wide array of functions to manipulate data without needing to ...
Read more