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
ConfigMgr Make Model Power BI Report
ConfigMgr Client Boot Time Power BI Report
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.
- Now select the Edit Queries button the ribbon
- 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
- 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
- Change the data types to parameters and select appropriate ones and select Next
- Type in the name of the view you want to query and select OK
- When asked about setting up the storage mode select Direct Query, we will convert to import later
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
- Rename the query to the view you are adding, v_Site
- Right click on the blank query and select Advance Editor
- 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
- Change the data types to parameters and select appropriate ones
- Select Advance Options and paste in your SQL syntax and select OK
- Rename the query to the view you are adding, v_Site
- You may receive an error saying Power BI cannot connect, select Retry
- Now you will be asked if you approve running a native database query, select 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.
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)
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)
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)
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.
