Extending Your OpsMgr Power BI Dashboards to Power BI Sites

Introduction

Few days ago, my friend and CDM MVP Cameron Fuller published a great article on how to build Power BI Dashboards for OpsMgr. You can check out Cameron’s post from here: http://blogs.catapultsystems.com/cfuller/archive/2015/12/01/using-power-bi-for-disk-space-dashboards-and-reports-in-operations-manager/

The solution Cameron produced was based on Power BI desktop and OpsMgr Data Warehouse DB, which both are located in your on-premises network. After Cameron has shown us what he has produced, I spent some time, and managed to extend the reports and dashboards that Cameron has created using Power BI Desktop to Power BI sites, which is a cloud-based PaaS solution offered as a part of the Office 365.

In this post, I will go through the process of setting up this solution so you can move Cameron’s Power BI dashboard to the cloud.

Pre-Requisites

In order to create a Power BI dataset in your cloud based Power BI sites which is based on the on-prem OpsMgr Data Warehouse DB, we will need to install a component called Power BI Enterprise Gateway (Currently in preview) on a server in your on-prem data center. As shown in the diagram below, once the dataset is created for the OpsMgr Data Warehouse DB, the Power BI Site will query the OpsMgr DW DB through the Power BI Enterprise Gateway.

The solution we are going to implement requires the following pre-requisites:

• A on-prem server for hosting the Power BI Enterprise Gateway
• Power BI Desktop installed on an on-prem computer (i.e. your own PC)
• A Power BI Pro account
• A service account that has access to the OpsMgr data warehouse DB

Note: Power BI Enterprise Gateway is a feature only available for Power BI Pro accounts. Please refer to this page for differences between Power BI Free and Pro accounts.

Configuration

The install process for the Power BI Enterprise Gateway is very straightforward. It is documented here: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise/

Once it is installed and connected to your Power BI account, you will be able to manage the gateway after you’ve logged on to the Power BI Site (https://app.powerbi.com/):

And you can use “Add Data Source” to establish connection to a data base via the gateway:

I have created a data source for my OpsMgr DW DB:

After the data source is created for the OpsMgr DW DB, I then need to create a dataset based on the OpsMgr DW DB. We must create this dataset in Power BI Desktop.

As Cameron already demonstrated in his post, in Power BI Desktop, we create a new document, and select SQL Server Database:

We then specify the SQL server name and database name for the OpsMgr DW DB:

Note: Please make sure the server name and database name entered here is IDENTICAL as what you have entered for the OpsMgr DW DB Data Source you have created under the Power BI Enterprise Gateway.

Then, we will select all the tables and views that we are interested in. I won’t repeat what Cameron has already demonstrated, Please refer to his post for more details on what tables and views to select.

Please make sure you have select ALL the tables and views that you need. Once we have uploaded this configuration to Power BI Site, we won’t be able to modify this dataset in Power BI Site.

Once we have selected all required tables and views, click “Load”:

Please make sure you choose “DirectQuery” when prompted:

Power BI will then create the connections to each table and view you have selected. Depending on the number of tables and views you have selected, this process may take a while:

You can now see all the tables and views you have selected in Power BI Desktop:

Now, we can save this empty Power BI report:

After it is saved, we can go back to the Power BI Site, and import the newly created report by clicking “Get Data”:

Then choose “Files:

Select Local File, and choose the empty report we’ve just created in Power BI Desktop

Then, you should be able to the new dataset appeared under “Datasets”:

And we can create a new report in Power BI Site, same way as what Cameron has demonstrated in his post:

In my lab, I have created the dataset and imported the Power BI report file to Power BI Site few days ago. From the above screenshot, you can see the performance data collected today (as highlighted in red). This means the Power BI Site is directly quering my On-Prem OpsMgr DW DB in real time via the Power BI Enterprise gateway. And when I checked my usage, the OpsMgr DW DB dataset has only consumed 1MB of data:

Summary

In this post, I have demonstrated how to extend the PowerBI report and dashboard you have build in PowerBI Desktop located in your On-Prem environments to the cloud based version – Power BI Site, via the newly released Power BI Enterprise Gateway (still in preview at the time of writing).

Although we must have a Power BI Pro account in order to leverage the Power BI gateways, since are using direct query method when connecting to the OpsMgr DW database, this solution should not consume too much data from your monthly allowrance for the PRO user.

The performance for the report is really fast. My OpsMgr management group and the Power BI Enterprise Gateway server is located in my home lab, which is connected to the Internet only via an ADSL 2+ connection.

This is certainly a cheaper alternative OpsMgr dashboarding solution (compared with other commercial products). Additionally, since it is hosted on the cloud, it is much easier to access the reports and dashboard no matter where you are. Power BI also provides mobile apps for all 3 platforms (Windows, iOS and Android), which you can use to access the data using your preferred mobile devices. You can find more information about the mobile apps here: https://powerbi.microsoft.com/en-us/mobile

You also have the ability to share the dashboards you have created with other people within your organisation.