Getting Started with ConfigMgr and Power BI

Welcome to the first series of blogs I plan on doing to help you adopt Power BI into your Configuration Manager (ConfigMgr) environment.  This post will outline getting started with ConfigMgr and Power BI.  Specifically creating your initial template.  We will start by creating Power BI parameters to store your ConfigMgr database server and name.  Finally creating two simple template files, a Direct Query and Import.  

This post is part of a series of posts in my “Getting Started with ConfigMgr and Power BI”

(this post)
Core ConfigMgr Queries for Power BI

Power BI Template

To Direct Query or to Import

Microsoft describes in detailed the differences between both data set types.  Default to import if you are unsure on which one to do.  My rule of thumb, if one your data set supports Direct Query and you want up to the minute information, then use it.  If the data set can be refreshed through out the day, use import. 

https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery

Import – the selected tables and columns are imported into Power BI Desktop. As you create or interact with a visualization, Power BI Desktop uses the imported data. You must refresh the data, which imports the full data set again, to see any changes that occurred to the underlying data since the initial import or the most recent refresh.

DirectQuery – no data is imported or copied into Power BI Desktop. For relational sources, the selected tables and columns appear in the Fields list. For multi-dimensional sources like SAP Business Warehouse, the dimensions and measures of the selected cube appear in the Fields list. As you create or interact with a visualization, Power BI Desktop queries the underlying data source, which means you’re always viewing current data.

Direct Query Benefits

There are benefits of using Direct Query for your Power BI datasets. 

  • Lets you build visualizations over very large datasets, where it would otherwise be unfeasible to first import all of the data with pre-aggregation
  • Underlying data changes can require a refresh of data, and for some reports, the need to display current data can require large data transfers, making re-importing data unfeasible. By contrast, Direct Query reports always use current data
  • The 1-GB dataset limitation does not apply to Direct Query

Some limitations do exist, and the most import one for ConfigMgr, Intune, and Desktop Analytics is that Direct Query results can only use a single database.  As you/we add other data sources we will have to rely on importing data into Power BI. 

Creating the Template

First things first, download and install Power BI Desktop: https://www.microsoft.com/en-us/download/details.aspx?id=58494

  • First thing thing I like to do is change the background color and add my company or customer’s logo.

Power BI Background Color

Power BI Background Logo

  • Now select the Edit Queries button the ribbon

Edit Queries

  • Right-click in the left blade under Queries [0] and select new group
  • Name the group Parameters
  • Right-clock on Parameters and select New Parameter
  • Create two parameters named ConfigMgrSQLServer and ConfigMgrSQLDatabase
    • Enter your ConfigMgr server information

ConfigMgr Power BI Parameters

Power BI Parameters Group

  • Right-click in the left blade under Queries and select new group
  • Name the group ConfigMgr

Query Options

There is a few options for creating queries for SQL server.  If your environment has a lot of collections, any third party add-ins, you may want to setup a blank query.   If you have a complex query you can use SQL statement options. 

Using the Navigator

The first option is to utilize the built in query navigator.  If you ConfigMgr environment is minimal, then you will have no issue running this. 

  • Right-click on the group ConfigMgr and select New Query > SQL Server

Query SQL Server

  • Change the data types to parameters and select appropriate ones and select Next

SQL Server Database Selection

  • Type in the name of the view you want to query and select OK

SQL Navigator

  • When asked about setting up the storage mode select Direct Query, we will convert to import later

Storage Mode

The Blank Query Option

If your ConfigMgr site does not load nicely in the built in navigator.  As a result you can shortcut this step by utilizing the blank query option. 

  • Right-click on the group ConfigMgr and select New Query > Blank Query

Query Blank

  • Rename the query to the view you are adding, v_Site
  • Right click on the blank query and select Advance Editor

Advance Editor - SQL

  • If asked again, set to the storage mode to Direct Query

You can also use the navigator option present before this, and copy and paste.  Then update using the advanced editor

Using a SQL Statement

Furthermore, if you have developed a SQL statement that does joins and data manipulation, you can utilize it.  Power BI  desktop does give you annoying warning, in which you can ignore. 

  • Right-click on the group ConfigMgr and select New Query > SQL Server

Query SQL Server

  • Change the data types to parameters and select appropriate ones

SQL Server Database Selection

  • Select Advance Options and paste in your SQL syntax and select OK
  • Rename the query to the view you are adding, v_Site

NOTE: If you have a complex query or big environment, I believe the default time is at 90 seconds.  Adjust accordingly. 

SQL Statement

  • You may receive an error saying Power BI cannot connect, select Retry

SQL Statement Retry

  • Now you will be asked if you approve running a native database query, select Run

SQL Statement Run

Saving as a Template

Finally, once you have created your necessary query in your preferred method, select the Close & Apply in the query editor window.

Close & Apply

Direct Query Template

  • Open up the File menu and select Save As
  • Browse to your desired location and name the file ConfigMgr-DirectQuery-Template
  • Change the file type to Power BI template files (*.pbit)

File Template Direct Query

Converting to Import Storage Mode

Similarly, once you grow your Power BI datasets to tie together other data sources, you will have to utilize the import storage mode.  These data sets can include Desktop Analytics and Intune.  Above all, you can change this later as you grow your Power BI reports. 

  • Click on the bottom right corner where it states Storage Mode: DirectQuery (click to change)

Power BI Storage Mode Conversion

Power BI Storage Mode Conversion Zoom

Import Template

  • Open up the File menu and select Save As
  • Browse to your desired location and name the file ConfigMgr-Import-Template
  • Change the file type to Power BI template files (*.pbit)

File Template Import

Conclusion 

I hope this article helps you start your ConfigMgr Power BI journey.  I have linked the sample templates for you to download. 

Check back as we dive deeper into Power BI and ConfigMgr. 

PowerBI Getting Started Template

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.