Starting with SCCM 2002, you can integrate Power BI Report Server with Configuration Manager reporting. This post covers the complete SCCM Power BI Report Server Integration process. This integration gives you modern visualization and better performance.
The SCCM Power BI report server integration was first introduced in SCCM technical preview version 2001. However I am glad to see this feature added in SCCM/MEMCM 2002 current branch.
Integrate SCCM with Power BI Report Server
In a site running SCCM 2002, you can either integrate Power BI Report Server with an existing Reporting Point or a site that doesn’t have a reporting point installed.
I have reporting point installed and configured in my setup. After the SCCM Power BI report server integration is complete, it adds console support for Power BI reports similar to what already exists with SQL Server Reporting Services. Furthermore you can easily manage Power BI dashboard in SCCM console.
Prerequisites for SCCM Power BI Report Server Integration
- As per Microsoft, you require Power BI Report Server license as primary prerequisite. A Power BI Report Server is an on-premises report server with a web portal in which you display and manage reports and KPIs.
- Power BI Report Server is available through two different licenses – Power BI Premium and SQL Server Enterprise Edition with Software Assurance.
- Microsoft Power BI Report Server-September 2019 – Download Link.
- Microsoft Power BI Desktop (Optimized for Power BI Report Server – September 2019) – Download Link.
Download the above softwares and don’t install them yet. We will go step by step and configure the reporting services first.
Backup SSRS Encryption Keys
In the first step we back up the reporting service (SSRS) encryption keys. A backup copy of the key enables you to reuse an existing report server database in a new installation. This step is really important hence please don’t skip.
On the machine that’s installed with Reporting Server for SCCM, launch Report Server Configuration Manager. On the Report Server Connection window, ensure the reporting service instance is correct and click Connect.
Click Encryption Keys on the left pane. On the right pane, click Backup button.
Backup encryption key by specifying file location and enter password twice. You need this password later when we restore the keys. Click OK.
Under Results, you should see a green check with Creating Encryption Key Backup message.
Remove/Uninstall Reporting Services Point Role
To uninstall reporting services point role from your site
- Open the Configuration Manager console.
- Navigate to Administration > Site Configuration > Servers and Site System Roles.
- In the Servers and Site System Roles window, locate the Server you need to remove the Reporting Services Point role from and select it.
- In the lower pane of the window (Site System Roles) scroll down until you see Reporting Services Point role.
- Right click the Reporting Services Point, click Remove Role.
When asked Are you sure that you want to remove the selected role Reporting services point, click Yes.
Open the srsrpsetup.log and monitor the role un-installation. The above procedure is also covered in this post.
Uninstall SQL Server Reporting Services
To uninstall SQL Server Reporting Services
- Go to Control Panel > Programs and Features.
- Right click Microsoft SQL Server Reporting Services and click Uninstall/Change.
On the Microsoft SQL Server 2017 Reporting services window, click Uninstall option.
Wait while the reporting services is uninstalled.
Click Close when you see Uninstall Completed message.
Install Power BI Report Server
You can download the Power BI report server here. Run the setup as administrator. On the Welcome screen, click Install Power BI Report Server.
Enter the product key and click Next.
Accept the license terms and click Next.
If you install Power BI Report Server only option enabled, click Next.
Leave the install location to default and click Install.
Configure Report Server
After installing Power BI reporting service, let’s configure report server.
On the Report Server Connection window, click Connect.
Click Database in the left pane, now click Change Database.
On the Change Database action page, select Choose an existing report server database and click Next.
Ensure the credentials to connect to database server are correct. Click Test Connection and you should see Connection Successful message. Click Next.
You must select the report server database here, click Next.
Click Next and complete the remaining steps.
Finally we see the PowerBIReport Server configuration is successfull.
Restore SSRS Encryption Keys
After you have changed the database in the above step, you must restore SSRS encryption keys back. In the Report Server Configuration Manager, click Encryption Keys and click Restore.
Specify the backup keys file location and enter the password. Click OK.
We now see the encryption keys are restored.
Install Reporting Services Point Role
Before you install Reporting Services point role, perform the below steps. This is essential step because you may see reporting services server instance blank while adding it.
In the Report Server Configuration Manager window, click Web Service URL and click Apply button. Wait until you see Task is completed successfully message.
Click Web Portal URL, click Apply button. Wait until you see Task is completed successfully message.
In the Configuration Manager console, go to Administration > Overview > Site Configuration > Servers and Site System Roles. Select the server, right click and then click Add Site System Roles.
On the System Role Selection page, select Reporting services point. Click Next.
On the Specify Reporting services settings page, ensure reporting server instance is correct. Click Verify button and click Next. Complete the remaining steps and close the wizard.
Install Power BI Desktop
You can download the Power BI Desktop from here. On a computer that has the Configuration Manager console, update the Configuration Manager console to the latest version. A detailed post on deploying PowerBI desktop using SCCM is covered here.
Close the Configuration Manager and run the Power BI Desktop setup. On the setup screen, click Next.
Accept the license terms and click Next.
Power BI Reports in SCCM Console
After you click Finish, the Microsoft Power BI Desktop launches. Close the app. Now launch Configuration Manager console. Go to Monitoring > Reporting > Power BI Reports.
When you right click Power BI Reports, you see Create Report and Report Options. Click Create Report and now the Power BI Desktop app launches. The steps to integrate SCCM with Power BI Report Server is complete.