SharePoint 2019 - Power BI Reporting

In this article, we will explore how we can integrate Power BI reporting with SharePoint Server 2019 on-premises using the gateway.

Overview

 
SharePoint 2019 supports Power BI reporting, which is one of the rich cloud features made available in on-premises. The introduction of Power BI to SharePoint 2019 on-premises is one of the major enhancements in BI.
 
In this article, we will explore how we can integrate Power BI reporting with SharePoint Server 2019 on-premises using the gateway.
 

Power BI Overview

 
Power BI helps to turn unrelated sources of data into interactive insights. Power BI allows us to connect to various data sources and create interactive reports. Power BI consist of a Windows desktop application called Power BI desktop and an online SAAS service called Power BI service.
 

On-premises Data Gateway

 
On-premises gateways allow us to connect to on-premises data sources without the need to move the data. It provides flexibility to query large datasets.
  • Login to Power BI site. The sign-in account could be an Office 365 account (either a work account or a school account).
  • Click Download > Data Gateway.
SharePoint 2019 - Power BI Reporting
  • Click “Download Gateway”.
SharePoint 2019 - Power BI Reporting
  • PowerBIGatewayInstaller.exe will be downloaded to your system. Double click the file to start the installation wizard.
  • Click Next.
SharePoint 2019 - Power BI Reporting 
  • Choose the type of gateway you need. Click Next.
SharePoint 2019 - Power BI Reporting 
  • Click Next.
SharePoint 2019 - Power BI Reporting 
  • Choose the installation directory. Accept the terms of use. Click Install.
SharePoint 2019 - Power BI Reporting 
  • Installation will take some time.
SharePoint 2019 - Power BI Reporting 
  • Provide the email address to use with the gateway. Click “Sign in”. The sign-in account could be an Office 365 account (either a work account or a school account).
SharePoint 2019 - Power BI Reporting 
  • Register a new gateway or migrate an existing gateway. Click Next.
SharePoint 2019 - Power BI Reporting 
  • Specify the gateway name and recovery key. Optionally you may change the region. Click "Configure".
SharePoint 2019 - Power BI Reporting 
  • Once installed successfully, the gateway should be online and ready to use.
SharePoint 2019 - Power BI Reporting 
 

Manage Gateway

 
Once the gateway is configured, we can make use of it to connect to on-premises data sources. The personal mode of gateway works only with Power BI.
  • In the Power BI site, click Settings > Manage gateways.
SharePoint 2019 - Power BI Reporting
  • All available gateways are listed here. Click “Add data sources to use the gateway”.
SharePoint 2019 - Power BI Reporting 
  • Specify the data source name.
  • Select Data Source Type as SharePoint.
  • Specify the SharePoint site URL to get the data from. Specify the authentication method.
  • Click Add.
SharePoint 2019 - Power BI Reporting 

Create Data Source

  • Download and install Power BI Desktop from here (https://powerbi.microsoft.com/en-us/desktop/).
  • From the Home menu, click Get Data > More…
SharePoint 2019 - Power BI Reporting 
  • In the Get Data screen, type SharePoint. 
  • Select “SharePoint list” option.
  • Click Connect.
 
SharePoint 2019 - Power BI Reporting 
  • Enter the SharePoint 2019 site url. Click OK.
SharePoint 2019 - Power BI Reporting 
  • Select the list, click Load. 
SharePoint 2019 - Power BI Reporting 
  • Create a report and publish it.
SharePoint 2019 - Power BI Reporting 
  • Select the already created workspace to publish the report.
SharePoint 2019 - Power BI Reporting 
  • Under “My Workspaces”, click Datasets.
  • Select the dataset we created (e.g. Inventory).
SharePoint 2019 - Power BI Reporting 
 

Install Personal Gateway

 
We will have to install the personal gateway to proceed further. The On-premises data gateway (personal mode) allows individuals to install a gateway on their own computer, and gain access to on-premises data.
  • Click “Install now”.
  • It will download On-premises data gateway (personal mode).exe file.
  • Double click the file to install.
  • Click Next
SharePoint 2019 - Power BI Reporting
  • Select the “Install to” location. Click “I accept the terms of use”.
  • Click Install.
SharePoint 2019 - Power BI Reporting 
  • Specify the email address. Click “Sign in”. 
SharePoint 2019 - Power BI Reporting 
  • Once installed, the on-premises gateway is ready to use in personal mode.
SharePoint 2019 - Power BI Reporting 
  • We now have on-premises data gateway as well personal mode gateway.
SharePoint 2019 - Power BI Reporting 

Power BI Reports

  • Under “My Workspace”, click Reports.
  • Click the report name we created now.
SharePoint 2019 - Power BI Reporting
  • The report can be seen in Power BI. 
SharePoint 2019 - Power BI Reporting 
 

Publish the Power BI Report

 
The Power BI Report does not have an option as “Embed in SharePoint” as it does have in SharePoint Online. However we can use “Publish to web” option.
  • Click File > Publish to web.
 SharePoint 2019 - Power BI Reporting
  • The generated embed code can be used on SharePoint 2019 On-Premises site. 
SharePoint 2019 - Power BI Reporting 
  • Open SharePoint 2019 on-premises site. Add Embed web part.
SharePoint 2019 - Power BI Reporting 
  • Copy the embed code to web part property.
SharePoint 2019 - Power BI Reporting 
 

Scheduled Refresh of Datasets

 
We can define the schedule for refreshing the datasets from SharePoint 2019.
  1. Under My workspace, click Data sets.
  2. Select the dataset (e.g. Inventory).
  3. Under Scheduled refresh, define the refresh schedule.
SharePoint 2019 - Power BI Reporting

Summary

 
The Power BI reporting cloud feature is available in SharePoint 2019. On-premises data gateway helps to connect to on-premises data sources. The Power BI Report for on-premises does not have an option “Embed in SharePoint” as it does have in SharePoint Online; however, Power BI reports can be published to web and can be embedded to SharePoint 2019.