Friday, 8 May 2015

Analyse your ConfigMgr data with Microsoft Power BI

Back to main menu

We can now take information from our ConfigMgr databases and analyse the data using the might of Microsoft Power BI. This announcement was made by Brad Anderson last week and he seemed pretty excited about it.

You can read the post here Getting Started with SCCM & Power BI

What is Power BI (Business Intelligence)?

"Microsoft Power BI is a collection of online services and features that enables you to find and visualize data, share discoveries, and collaborate in intuitive new ways. There are two experiences now available for Power BI: the current experience, generally referred to as Power BI for Office 365, and a new experience for Power BI, currently offered as a Public Preview." (Microsoft quote).

Let's have a look at the "new" Power BI. Brad's blog contains instructions but it took me a little while to get this working. However it's really cool and worth the effort.

The first thing you have to do is download the "Getting Started with SCCM and Power BI Workbook".

It's just an Excel spreadsheet containing pre-built queries and views. All we should have to do is configure the data source. Save it locally and open the spreadsheet.

Configure the data source (Server & database names required). The instructions then described clicking on the "Power Query" tab.

I didn't have that so I had to download Microsoft Power Query for Excel

Make sure you get the right version. The architecture of your OS doesn't matter. What is your version of Office?

Install Power Query for Excel.

 Now the Power Query tab is available. Select "Show Pane".

See the pre-configured Workbook queries on the right hand side of the workbook. Right click "Configuration Manager" and click "Refresh".

As it's the first time I'm running this I am prompted for credentials to connect to the database.

Click to Run the query.

Oops. That didn't look too good......

....nor this. This baffled me for a while as I know that the account I used was Sysadmin on the SQL instance. However eventually I had a brainwave. I ran Excel as Administrator and then opened the workbook. Success (although I don't know why I had to do this).

I was prompted to configure a Privacy Level.See here for full details of Privacy Levels.

Queries ran successfully. Cool. If you're following along save the Excel file. Now navigate to the Power BI site

 Log in with your Office 365 credentials.

Click "Get Data".

Select Excel Workbook and "Connect".

Navigate and connect to the saved file......

.....and here we go. Data Analytics at our fingertips (without any serious work).

Click to Edit the Report.

See that you can simply manipulate Fields and Filters. You can also move views around as required. This is seriously cool and a very good starting point. There are 4 pre-configured Compliance sections - Mobile Device, Endpoint, Malware and Software Updates.

No comments:

Post a Comment