Home SCCM How to Build PowerBI Live Dashboard Integration with SCCM | ConfigMgr

How to Build PowerBI Live Dashboard Integration with SCCM | ConfigMgr

0
PowerBI Live Dashboard Integration with SCCM

A quick post about “PowerBI Live Dashboard Integration with SCCM” how it connects with SCCM SQL Database integration and How to prepare live dashboard and also detailed about PowerBI Data Source options.

Video Recording of my HTMD Live eventHow to Integrate PowerBI with SCCM Video

NOTE! – You need an enterprise license to Publish and Share live Dashboards.

What is PowerBI

PowerBI is a business analytics solution that lets you visualize your data and share insights across your organization or embed them in your app or website.

PowerBI Can connect to hundreds of data sources and bring your data to life with live dashboards and reports.

Altaro Office 365 Backup
Advertisement Altaro Office 365 Backup

PowerBI Data Sources

With Power BI Desktop, you can connect to data from many different sources. For a full list of available data sources, see Power BI data sources.

You connect to data by using the Home ribbon. To show the Most Common data types menu, select the Get Data button label or the down arrow.

Most Common data types menu, Get Data in Power BI Desktop - PowerBI Live Dashboard Integration with SCCM
PowerBI Live Dashboard Integration with SCCM
  • To go to the Get Data dialog box, show the Most Common data types menu and select More.
  • You can also bring up the Get Data dialog box (and bypass the Most Common menu) by selecting the Get Data icon directly.
Get Data button, Power BI desktop - PowerBI Live Dashboard Integration with SCCM
Get Data button, Power BI desktop – PowerBI Live Dashboard Integration with SCCM

NOTE! – The Power BI team is continually expanding the data sources available to Power BI Desktop and the Power BI service. As such, you’ll often see early versions of work-in-progress data sources marked as Beta or Preview. Any data source marked as Beta or Preview has limited support and functionality, and it shouldn’t be used in production environments.

Additionally, any data source marked as Beta or Preview for Power BI Desktop may not be available for use in the Power BI service or other Microsoft services until the data source becomes generally available (GA).

NOTE! – There are many data connectors for PowerBI Desktop that require internet Explorer 10 (or newer) for authentication

Data Sources

The Get Data Dialog box organizes data types in the following categories.

  • All
  • File
  • Database
  • Power Platform
  • Azure
  • Online Services
  • Other

The All category includes all data connection types from all categories.

File data sources

The File category provides the following data connections:

  • Excel
  • Text/CSV
  • XML
  • JSON
  • Folder
  • PDF
  • SharePoint folder

The following image shows the Get Data window for File.

File data sources, Get Data dialog box, Power BI Desktop -  PowerBI Live Dashboard Integration with SCCM
File data sources, Get Data dialog box, Power BI Desktop – PowerBI Live Dashboard Integration with SCCM

Database data sources

The Database category provides the following data connections:

  • SQL Server database
  • Access database
  • SQL Server Analysis Services database
  • Oracle database
  • IBM DB2 database
  • IBM Informix database (Beta)
  • IBM Netezza
  • MySQL database
  • PostgreSQL database
  • Sybase database
  • Teradata database
  • SAP HANA database
  • SAP Business Warehouse Application Server
  • SAP Business Warehouse Message Server
  • Amazon Redshift
  • Impala
  • Google BigQuery
  • Vertica
  • Snowflake
  • Essbase
  • AtScale cubes
  • BI Connector
  • Data Virtuality LDW (Beta)
  • Denodo
  • Dremio
  • Exasol
  • Indexima (Beta)
  • InterSystems IRIS (Beta)
  • Jethro (Beta)
  • Kyligence
  • MarkLogic

Note – Some database connectors require that you enable them by selecting File > Options and settings > Options then selecting Preview Features and enabling the connector.

  • If you don’t see some of the connectors mentioned above and want to use them, check your Preview Features settings.
  • Also note that any data source marked as Beta or Preview has limited support and functionality, and should not be used in production environments.
  • The following image shows the Get Data window for Database.
Database data sources, Get Data dialog box, Power BI Desktop
Database data sources, Get Data dialog box, Power BI Desktop – PowerBI Live Dashboard Integration with SCCM

Power Platform data sources

The Power Platform category provides the following data connections:

  • Power BI datasets
  • Power BI dataflows
  • Common Data Service
  • Power Platform dataflows

The following image shows the Get Data window for Power Platform.

Power Platform data sources, Get Data dialog box, Power BI Desktop - PowerBI Live Dashboard Integration with SCCM
PowerBI Live Dashboard Integration with SCCM

Azure data sources

The Azure category provides the following data connections:

  • Azure SQL Database
  • Azure SQL Data Warehouse
  • Azure Analysis Services database
  • Azure Database for PostgreSQL
  • Azure Blob Storage
  • Azure Table Storage
  • Azure Cosmos DB
  • Azure Data Lake Storage Gen2
  • Azure Data Lake Storage Gen1
  • Azure HDInsight (HDFS)
  • Azure HDInsight Spark
  • HDInsight Interactive Query
  • Azure Data Explorer (Kusto)
  • Azure Cost Management
  • Azure Time Series Insights (Beta)

The following image shows the Get Data window for Azure.

Azure data sources, Get Data dialog box, Power BI Desktop - PowerBI Live Dashboard Integration with SCCM
PowerBI Live Dashboard Integration with SCCM

Online Services data sources

The Online Services category provides the following data connections:

  • SharePoint Online List
  • Microsoft Exchange Online
  • Dynamics 365 (online)
  • Dynamics NAV
  • Dynamics 365 Business Central
  • Dynamics 365 Business Central (on-premises)
  • Microsoft Azure Consumption Insights (Beta)
  • Azure DevOps (Boards only)
  • Azure DevOps Server (Boards only)
  • Salesforce Objects
  • Salesforce Reports
  • Google Analytics
  • Adobe Analytics
  • appFigures (Beta)
  • Data.World – Get Dataset (Beta)
  • GitHub (Beta)
  • LinkedIn Sales Navigator (Beta)
  • Marketo (Beta)
  • Mixpanel (Beta)
  • Planview Enterprise One – PRM (Beta)
  • Planview Projectplace (Beta)
  • QuickBooks Online (Beta)
  • Smartsheet
  • SparkPost (Beta)
  • SweetIQ (Beta)
  • Planview Enterprise One – CTM (Beta)
  • Twilio (Beta)
  • tyGraph (Beta)
  • Webtrends (Beta)
  • Zendesk (Beta)
  • Asana (Beta)
  • Dynamics 365 Customer Insights (Beta)
  • Emigo Data Source
  • Entersoft Business Suite (Beta)
  • FactSet Analytics (Beta)
  • Industrial App Store
  • Intune Data Warehouse (Beta)
  • Microsoft Graph Security (Beta)
  • Product Insights (Beta)
  • Quick Base
  • TeamDesk (Beta)
  • Workplace Analytics (Beta)

The following image shows the Get Data window for Online Services.

Online Services data sources, Get Data dialog box, Power BI Desktop - PowerBI Live Dashboard Integration with SCCM
PowerBI Live Dashboard Integration with SCCM

Other data sources

The Other category provides the following data connections:

  • Web
  • SharePoint list
  • OData Feed
  • Active Directory
  • Microsoft Exchange
  • Hadoop File (HDFS)
  • Spark
  • Hive LLAP (Beta)
  • R script
  • Python script
  • ODBC
  • OLE DB
  • BI360 – Budgeting & Financial Reporting (Beta)
  • FHIR
  • Information Grid (Beta)
  • Jamf Pro (Beta)
  • MicroStrategy for Power BI
  • Paxata
  • QubolePresto (Beta)
  • Roamler (Beta)
  • Siteimprove (Beta)
  • SurveyMonkey (Beta)
  • Tenforce (Smart)List (Beta)
  • TIBCO(R) Data Virtualization (Beta)
  • Vena (Beta)
  • Workforce Dimensions (Beta)
  • Zucchetti HR Infinity (Beta)
  • Blank Query

The following image shows the Get Data window for Other.

Other data sources, Get Data dialog box, Power BI Desktop - PowerBI Live Dashboard Integration with SCCM
Other data sources, Get Data dialog box, Power BI Desktop – PowerBI Live Dashboard Integration with SCCM

Connecting to a data source

To connect to a data source, select the data source from the Get Data window and select Connect. In the following image, Web is selected from the Other data connection category.

Connect to web, Get Data dialog box, Power BI Desktop

A connection window is displayed, specific to the type of data connection. If credentials are required, you’ll be prompted to provide them. The following image shows a URL being entered to connect to a Web data source.

Input URL, From Web dialog box, Power BI Desktop

Enter the URL or resource connection information, and then select OK. Power BI Desktop makes the connection to the data source, and it presents the available data sources in the Navigator.

Navigator dialog box, Power BI Desktop

To load the data, select the Load button at the bottom of the Navigator pane. To transform or edit the query in Power Query Editor before loading the data, select the Transform Data button.

That’s all there is to connecting to data sources in Power BI Desktop! Try connecting to data from our growing list of data sources, and check back often – we continue to add to this list all the time.

Using PBIDS files to get data

PBIDS files are Power BI Desktop files that have a specific structure, and they have a .PBIDS extension to identify it is a Power BI data source file.

You can create a PBIDS file to streamline the Get Data experience for report creators in your organization. To make it easier for a new report author to use PBIDS files, we recommend that an administrator create these files for commonly used connections.

When an author opens a PBIDS file, Power BI Desktop opens and prompts the user for credentials to authenticate and connect to the data source that’s specified in the file. The Navigation dialog box appears, and the user must select the tables from that data source to load into the model. Users may also need to select the database(s) if none was specified in the PBIDS file.

From that point forward, the user can begin building visualizations or select Recent Sources to load a new set of tables into the model.

Currently, PBIDS files only support a single data source in one file. Specifying more than one data source results in an error.

To create the PBIDS file, an administrator must specify the required inputs for a single connection. They can also specify the connection mode as either DirectQuery or Import. If mode is missing/null in the file, the user who opens the file in Power BI Desktop is prompted to select DirectQuery or Import.

PBIDS file examples

This section provides some examples from commonly used data sources. The PBIDS file type only supports data connections that are also supported in Power BI Desktop, with two exceptions: Live Connect and Blank Query.

The PBIDS file doesn’t include authentication information and table and schema information.

The following code snippets show several common examples for PBIDS files, but they aren’t complete or comprehensive. For other data sources, you can refer to the Data Source Reference (DSR) format for protocol and address information.

These examples are for convenience only, aren’t meant to be comprehensive, and don’t include all supported connectors in DSR format. An administrator or organization can create their own data sources using these examples as guides, from which they can create and support their own data source files.

PowerBI License information

Power BI service features by license type

In the Power BI service, users have defined capabilities based on the type of per-user license they have and whether the content they are acting on is in a workspace assigned to a Power BI Premium capacity.

  • Per-user – Power BI Pro and Power BI (free) licenses. A Power BI Pro license enables a user to collaborate with other Power BI Pro users by consuming content from, and sharing content with other users with a Power BI Pro license. Only users with a Power BI Pro license can publish content to app workspaces, share dashboards, and subscribe to dashboards and reports. A free license enables a user to consume content in a workspace assigned to a Power BI Premium capacity and access to some of the features of the Power BI service for their own personal content in their My Workspace. For more information, see Sign up for Power BI as an individual and Purchase and assign Power BI Pro user licenses.
  • Power BI Premium capacity – Power BI Premium licensing. Power BI Premium provides dedicated capacity to deliver more consistent performance and support larger data volumes in Power BI. Power BI Premium also enables widespread distribution of content by Pro users without requiring users who view the content to have Power BI Pro licenses. For more information, see What is Power BI Premium?

Prepare SQL custom view in SCCM DB

Connect SCCM SQL DB and Create view

Below query will helps to create location view by using staring letters of computer names, you can modify query by using IP Address or AD Site Name or any unique settings as per your org requirements.

USE [CM_PRI] GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE VIEW [dbo].[Assets_Location] AS 
Select DISTINCT
vrs.ResourceID as 'ResourceID', vrs.name0 as 'Machine Name', vrs.Resource_Domain_OR_Workgr0 as 'Domain', CASE when vrs.Name0 LIKE 'IND%' then 'Arcade' when vrs.Name0 LIKE 'CHI%' then 'China'
when vrs.Name0 LIKE 'USA%' then 'NEW-York' when vrs.Name0 LIKE 'LON%' then 'London' when vrs.Name0 LIKE 'UAE%' then 'Dubai' when vrs.Name0 LIKE 'JAP%' then 'Japan' when vrs.Name0 LIKE 'ITA%' then 'Italy' when vrs.Name0 LIKE 'AUS%' then 'Austria' when vrs.Name0 LIKE 'BRI%' then 'Brizal' when vrs.Name0 LIKE 'CAN%' then 'Cannada' else 'Other' end as 'Location'
from v_R_System as VRS
GO

Get Data to PowerBI from SCCM SQL DB by using Direct Query

Install PowerBI Desktop

Open PowerBI Desktop using the Home ribbon. select the Get Data button label or the down arrow and select SQL Server

PowerBI Live Dashboard Integration with SCCM
PowerBI Live Dashboard Integration with SCCM

Connect your SQL Server and provide SCCM SQL DB Instance if you have multiple Databases in your SQL Server.

Select DirectQuery and past your select query inn SQL Statement page to get data from SCCM DB to PowerBI Dekstop application

PowerBI Live Dashboard Integration with SCCM ConfigMgr
PowerBI Live Dashboard Integration with SCCM

PowerBI Desktop application will load data from SCCM SQL Server and shows in below table to import data into PowerBI Desktop

PowerBI Live Dashboard Integration with SCCM ConfigMgr
PowerBI Live Dashboard Integration with SCCM ConfigMgr

Get Data to PowerBI from SCCM SQL DB by using Import Query

Open PowerBI Desktop using the Home ribbon. select the Get Data button label or the down arrow and select SQL Server

PowerBI Live Dashboard Integration with SCCM ConfigMgr
PowerBI Live Dashboard Integration with SCCM ConfigMgr

Connect your SQL Server and provide SCCM SQL DB Instance if you have multiple Databases in your SQL Server.

Select Import to get data from SCCM DB SQL view’s to PowerBI

PowerBI Live Dashboard Integration with SCCM ConfigMgr
PowerBI Live Dashboard Integration with SCCM ConfigMgr

Select the required view and click Load to import data to PowerBI Desktop

PowerBI Live Dashboard Integration with SCCM ConfigMgr
PowerBI Live Dashboard Integration with SCCM ConfigMgr

Get Data to PowerBI from Excel file

Open PowerBI Desktop using the Home ribbon. select the Get Data button label or the down arrow and select Excel

PowerBI Live Dashboard Integration with SCCM ConfigMgr
PowerBI Live Dashboard Integration with SCCM ConfigMgr

Select the Excel file and Select Sheet which you want to import data into PowerBI

PowerBI Live Dashboard Integration with SCCM ConfigMgr
PowerBI Live Dashboard Integration with SCCM ConfigMgr

Select Load to import data into PowerBI from excel file

PowerBI Live Dashboard Integration with SCCM ConfigMgr
PowerBI Live Dashboard Integration with SCCM ConfigMgr

Create Custom Columns in PowerBI

Creating custom columns in PowerBI for count of machines Compliant% and count of machines Non-Compliant%

Right click on Patch & Compliance Query and select “New Column” and use below queries to create count of Patch Compliant and Non-Compliant Count of machines which will help to prepare Graphical design tiles in PowerBI.

PowerBI Live Dashboard Integration with SCCM ConfigMgr
PowerBI Live Dashboard Integration with SCCM ConfigMgr
Count of Non-Compliant% = CALCULATE(COUNT('Patch Compliance'[Compliant%]), 'Patch Compliance'[Compliant%] < "100.00%")
Count of Compliant% = CALCULATE(COUNT('Patch Compliance'[Compliant%]), 'Patch Compliance'[Compliant%] = "100.00%")

Apply filter to show patch compliance by location wise on each tile.

PowerBI Live Dashboard Integration with SCCM ConfigMgr
PowerBI Live Dashboard Integration with SCCM ConfigMgr

Add Last Refresh Date & Time Query for PowerBI

Open PowerBI Desktop using the Home ribbon. select the Get Data button label or the down arrow and select More – goto Others and Select “Blank Query

PowerBI Live Dashboard Integration with SCCM ConfigMgr
PowerBI Live Dashboard Integration with SCCM ConfigMgr
PowerBI Live Dashboard Integration with SCCM ConfigMgr
PowerBI Live Dashboard Integration with SCCM ConfigMgr

Select Advanced Editor to Add Date and Time sync query in PowerBI

PowerBI Live Dashboard Integration with SCCM ConfigMgr
PowerBI Live Dashboard Integration with SCCM ConfigMgr

Given Query in below for Last Data & Refresh in PowerBI

let
TodaysDate = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),-8)),
#"Converted to Table" = #table(1, {{TodaysDate}}),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Last Refresh Date Time"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Last Refresh Date Time", type datetime}})
in
#"Changed Type"
PowerBI Live Dashboard Integration with SCCM ConfigMgr
PowerBI Live Dashboard Integration with SCCM ConfigMgr

Add Tile for Last Refreshed Date and Time and Select the suitable visualization

How to Build PowerBI Live Dashboard Integration with SCCM | ConfigMgr 1

Publish PowerBI Dashboard

When you publish a Power BI Desktop file to the Power BI service, you publish the data in the model to your Power BI workspace. The same is true for any reports you created in Report view. You’ll see a new dataset with the same name, and any reports in your Workspace navigator.

Publishing from Power BI Desktop has the same effect as using Get Data in Power BI to connect to and upload a Power BI Desktop file.

NOTE! – Any changes you make to the report in Power BI won’t be saved back to the original Power BI Desktop file. This includes when you add, delete, or change visualizations in reports.

To publish a Power BI Desktop dataset and reports

In Power BI Desktop, choose File > Publish > Publish to Power BI or select Publish on the ribbon.

Publish button

Sign in to Power BI.

Select the destination.Select publish destination

When publishing is complete, you receive a link to your report. Select the link to open the report in your Power BI site.

Publish success dialog

Republish or replace a dataset published from Power BI Desktop

The dataset, and any reports you created in Power BI Desktop, upload to your Power BI site when you publish a Power BI Desktop file. When you republish your Power BI Desktop file, the dataset in your Power BI site is replaced with the updated dataset from the Power BI Desktop file.

What is PowerBI Gateway

Gateway creates the connection between Power BI cloud-based data analysis technology and the data source located on-premises. Gateway is an application that can be installed on any servers in the local domain. Gateway is responsible for creating the connection and passing data through.

Configure Auto Refresh schedules

The Scheduled refresh section is where you define the frequency and time slots to refresh a dataset. As mentioned earlier, you can configure up to eight daily time slots if your dataset is on shared capacity, or 48 time slots on Power BI Premium. The following screenshot shows a refresh schedule on a twelve-hour interval.

Configure scheduled refresh

Having configured a refresh schedule, the dataset settings page informs you about the next refresh time, as in the screenshot above. If you want to refresh the data sooner, such as to test your gateway and data source configuration, perform an on-demand refresh by using the Refresh Now option in the dataset menu in the nav pane. On-demand refreshes don’t affect the next scheduled refresh time.

Note also that the configured refresh time might not be the exact time when Power BI starts the next scheduled process. Power BI starts scheduled refreshes on a best effort basis.

The target is to initiate the refresh within 15 minutes of the scheduled time slot, but a delay of up to one hour can occur if the service can’t allocate the required resources sooner.

NOTE! – Power BI deactivates your refresh schedule after four consecutive failures or when the service detects an unrecoverable error that requires a configuration update, such as invalid or expired credentials. It is not possible to change the consecutive failures threshold.

Getting Refresh Failure Notifications

By default, Power BI sends refresh failure notifications through email to the dataset owner so that the owner can act in a timely manner should refresh issues occur.

Power BI also sends you a notification when the service disables your schedule due to consecutive failures. Microsoft recommends that you leave the checkbox Send refresh failure notification emails to me enabled.

It is also a good idea to specify additional recipients by using the Email these users when the refresh fails textbox. The specified recipients receive refresh failure notifications in addition to the dataset owner. This might be a colleague taking care of your datasets while you are on vacation.

It could also be the email alias of your support team taking care of refresh issues for your department or organization. Sending refresh failure notifications to others in addition to the dataset owner is helpful to ensure issues get noticed and addressed in a timely manner.

Note that Power BI not only sends notifications on refresh failures but also when the service pauses a scheduled refresh due to inactivity. After two months, when no user has visited any dashboard or report built on the dataset, Power BI considers the dataset inactive.

In this situation, Power BI sends an email message to the dataset owner indicating that the service paused the refresh schedule for the dataset. See the following screenshot for an example of such notification.

Email for paused refresh

To resume scheduled refresh, visit a report or dashboard built using this dataset or manually refresh the dataset using the Refresh Now option.

Checking Refresh Status and History

In addition to failure notifications, it is a good idea to check your datasets periodically for refresh errors. A quick way is to view the list of datasets in a workspace. Datasets with errors show a small warning icon.

Select the warning icon to obtain additional information, as in the following screenshot. For more information about troubleshooting specific refresh errors, see Troubleshooting refresh scenarios.

Refresh status warning
PowerBI Live Dashboard Integration with SCCM ConfigMgr

The warning icon helps to indicate current dataset issues, but it is also a good idea to check the refresh history occasionally. As the name implies, the refresh history enables you to review the success or failure status of past synchronization cycles.

For example, a gateway administrator might have updated an expired set of database credentials. As you can see in the following screenshot, the refresh history shows when an affected refresh started working again.

Refresh history messages

NOTE! – You can find a link to display the refresh history in the dataset settings. You can also retrieve the refresh history programmatically by using the Power BI REST API. By using a custom solution, you can monitor the refresh history of multiple datasets in a centralized way..

Resources

NO COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here

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