Easily Integrate SCCM with Power BI Report Server
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.
Table of Contents
Integrate SCCM with Power BI Report Server
First of all you must install Configuration Manager 2002 update. Here is a SCCM 2002 upgrade guide for you. To know about the new features in ConfigMgr 2002, read this post.
In a site running SCCM 2002, you can either integrate SCCM with 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 you integrate SCCM with Power BI Report Server, 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 integrate SCCM with Power BI Report Server is successful.
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
This is the final step after we integrate SCCM with Power BI Report Server. 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.
After the PBIRS install and it’s time to change the database, I get the Report Server Mode “Unknown” instead of “Native” after selecting the previous “ReportServer” database.
Do you have any suggestions?
Always appreciate your posts. We have an existing Power BI Reporting Server in our enterprise. Do you know if there is a way to use our existing PBIRS for the SCCM reporting point?
How much the Power BI Server license costs?
If you go through this process, do you loose any of the custom reports or old reports SCCM installed? Or are these reinstalled when the reporting services role is reinstalled on the site server?
I don’t think you should loose any custom reports. However you must always have a backup of your custom reports and store it in another location.
Thanks for the post. I would like to ask, I must install PowerBI Report Server on primary site server or database server? Or I can use other remote server with PowerBI Report Server installed on.
If I should use remote server, is there any difference if I will install Reporting Service point Role under primary server or database server?
Thanks for answer.
Good question, I’d love to know the answer … if PBIRS goes on a separate server.
I just tried it on ConfigMgr version 2002 with all Hotfixes (we’re not yet up to 2006)
Primary – SQL DB – PBIRS all separate servers
Did NOT work – the only way I got this to work in version 2002 was to move the SQL DB and PBIRS back to the Primary
We will be trying again later when we upgrade again
Good job! I have one question. How do you manage to have Power create report option under Monitoring > Reporting?
When I try to install DPM (Data Protection Manage) on Another server then the server that have sql server and report server installed, I get a error that i have to install bireportserver? It look like it can’t connect to the sql reportserver/BIReportServer. Can antyone help me?
do we have any examples of reports to see this in action