次の方法で共有

Integrating OMS and Power BI

  • [アーティクル]

Summary: Learn how to integrate Power BI with Microsoft Operations Management Suite (OMS) by activating the integration and creating a dataset to work with the data.

Hi all, Daniel Örneling here. Usually I spend my days working as a specialist consultant involved in several projects around Systems Center Operations Manager and Operations Management Suite, both directly connected to OMS and connected through SCOM. I´ve had the opportunity to write a guest blog for the OMS team, and what can be a better way to share your knowledge? That´s why I will today explain to you how to export data from OMS to Microsoft Power BI and create a really good looking report.

The Power BI integration with OMS recently stepped into a public preview, and I think it took me about 30 seconds from reading the words, OMS, Power BI, and public preview. before I had logged into my OMS workspace and activated the preview feature. So, what’s to do after the preview feature had been activated? Well, keep reading and you´ll find out.

Activate the Power BI integration

The first thing you need to do after you´ve signed in to your OMS workspace is to head to the Settings of your workspace. Just click the button seen below.

Image of Settings in OMS workspace.

Now, the following menu will come in handy three times during this blog. The first thing to do is to head to Preview Features.

Image of menu options with Preview Features highlighted.

Switch the PowerBI Integration to Enabled as seen below, and you´re almost good to go.

OMS-3-31-16-3

Now, go to Accounts from the menu.

Image of Accounts selected in the menu.

Select Connect to Power BI Account, sign in by using your Office 365 account, and you´re all set for the Power BI integration.

Image of Connect to Power BI Account sign-in page.

Create a dataset

Okay, so the integration has been set up, and we´re about to export our data to Power BI. Go to Log Analytics search by selecting Log Search from the main page.

The dataset I will create for this demo will contain event information such as Event IDs, event level names, event logs, and so forth. Just type Type=Event and press Enter to search. After you´ve verified that the search actually generates information, select PowerBI as seen in the following picture.

Image of Power BI selected in search.

My dataset will simply be called Events, I will use the current search query (Type=Event), and I want the data to be shipped every hour. This rate can be adjusted down to every 15 minutes as the fastest schedule. Bear in mind that shipping data every 15 minutes might cause syncing errors due to the (potentially) heavy load of data.

Image of dialog box that has options to schedule delivery of search results to Power BI.

It can take up to 15 minutes before the dataset is created in Power BI, and your information has been transported. In the meantime, we can check our datasets out inside OMS. Head back to the Settings pane. This time, select Power BI.

Image of Power BI selected in the Settings menu.

There it is, our dataset that we´ve just created along with a couple of other datasets I´ve created. The only difference among the datasets is that the queries differ. I´ve used Type=ConfigurationChange and Type=Perf instead and created datasets for those just as I showed earlier so it´s really easy to create the datasets. You will also see these datasets in Power BI later in this post.

Image of list of scheduled datasets.

Work with the data in Power BI

Go to PowerBI.com, and sign in with your Office 365 account. I used my corporate account because that´s where I have my Office 365 benefits. After you´re logged in, select the menu  button (Menu button.) to bring out this view, and you will now be able to see your datasets. Here you can also see my other two datasets that I had created earlier.

On the PowerBi.com website, the Events dataset and two datasets that were created earlier.

Now what I want to do is to create a diagram containing information on the number of events per computer in my environment. Select the Pie Chart model, and then check Computer and EventID as seen in the following screenshot.

Image of visualization options.

Now there are two things left to do before this starts to present some interesting data. Drag EventID to Values as I´ve done in this screenshot, and then make a filter for the EventLevelName parameter. In this case, I want to show only errors and warnings in my report. You could also filter the computer names if you like. This way you wouldn´t have to show all computers in your environment.

Image of filtered data that shows errors and warnings.

Now that the information is there in our report, we will see the following diagram. Looks fancy, but don´t we want some information about which color represents which server?

Image of pie chart that shows warnings and errors.

Select the format icon (the pencil) to the right, turn on Legend,  choose Right center (or whatever you want), and give it an appropriate name. You will now see the server list in the report as well.

Image of pie chart that shows warnings and errors and legend.

Now you can hover over a field with your mouse, and you will see the server name (Nano server in OMS, pretty cool, ain't it?), the event level name, and the number of events on that server (the high amount of errors is expected in this case).

Image that shows data that you see when you hover the mouse point over part of the pie chart.

Now save your report, and you will be able to do all sorts of Power BI stuff with it, exporting it to Excel, publishing it to websites, etc.

Image that shows saved reports in the workspace.

Select the report name, and there it is, our very own Power BI report completely based on information that we´ve gathered from OMS with the help of a connector between the two services.

Image of the customized pie chart that shows data from OMS in a Power BI report.

Summary

What I´ve shown here in this blog post is how you, in a very fast (and easy) way, can set up recurring shipment of your OMS data to Power BI where we can create really fancy reports in just a minute. With OMS as the gatherer and Power BI as the reporting tool, our life is made much easier when it comes to presenting the data to the rest of the business. So if you have been waiting for a great way to create reports based on your OMS data, this is your answer.

That´s all for now, folks. I hope you´ve liked what you´ve read and that you will find this blog post useful to get started with the Power BI integration. Leave a comment below if there are questions.

Thank you, Daniel, for an awesome blog post. It is both informative and useful. Join me tomorrow when we will have another article from Alexander Frankel as he talks about exporting data to Power BI.

I invite you to follow me on Twitter and the Microsoft OMS Facebook site. If you want to learn more about Windows PowerShell, visit the Hey, Scripting Guy Blog. If you have any questions, send email to me at scripter@microsoft.com. I wish you a wonderful day, and I’ll see you tomorrow.

Ed Wilson
Microsoft Operations Management Team

Comments

  • Anonymous
    May 21, 2016
    I'm using the PowerBI integration with OMS. I setup a dataset Type=Events, but when the data is synced to PowerBI I only have two columns available 'Count' and 'TimeRead' . .it doesn't seem what info I sync to PowerBI I get those same two columns.
    • Anonymous
      June 06, 2016
      @Zak, Did you find the issue? I have the same type of issue. Dataset is created in Power BI but it's empty.
      • Anonymous
        June 06, 2016
        My issue is solved. It was because i used Microsoft Account for Azure and Corporate account for Power BI.
        • Anonymous
          April 06, 2017
          I'm using corporate account for both OMS and PBI, and still having this issue.
  • Anonymous
    June 29, 2016
    I created a Dataset in OMS , but it does not get Synced with Power BI. Sync Status shows up as "Not Synced". 4 other Data sets that were created earlier have synced successfully.
  • Anonymous
    August 22, 2016
    I imported the OMS details into powerBI but ther powerBI has stopped collecting the data after fey days and im not able to refresh the dataset too. has anyone came across with the same problem? Is there any solution for this?
  • Anonymous
    December 20, 2016
    How do we edit the query that was initially created to push data to powerbi
    • Anonymous
      October 03, 2017
      That's huge. I can't even find a way to change the reporting frequency.
  • Anonymous
    December 21, 2016
    The comment has been removed
  • Anonymous
    April 29, 2017
    Is there any way i can get my Power BI data to OMS?
  • Anonymous
    May 17, 2017
    I must say it was hard to find your website in search results.You write awesome articles but you should rank your page higher in search engines. If you don't know 2017 seo techniues search on youtube: how to rank a website Marcel's way