Connecting Power Bi to the Device Analytics

Connecting Power Bi to the Device Analytics

Written by Giuseppe

Last published at: June 28th, 2023

Power-BI Sample Report


Requirements

 

  1. Power-BI Desktop
    1. Download Microsoft Power BI Desktop from Official Microsoft Download Center
  2. Reader (minimum) access to:
    1. Azure Log Analytics Workspace
    2. ThinScale Management Server SQL Database
  3. Management Server Database with new SQL-Views
    1. “v-da-DevicesSummary”
    2. “v-da-DeviceFolderPathC”
    3. “v-da-LicenseUsage” 

 

Getting Started


Before getting started, review the “Notes & Warnings” section to become familiar with the security notification you will likely see from Power-BI and what to expect for authentication screens for Azure Log Analytics and the “ThinScale Management Server” database.
 

On loading the Power-BI template for the 1st time, you will be prompted for the necessary parameters.
(Hovering over the Info icon will provide additional information and default info)

 

 

You should be prompted for your credentials to Azure Log Analytics workspace and the SQL server. Both should have multiple methods available. Use the correct method for your environment.

 

Notes & Warnings
 

You may* get the following notifications from Power-BI for security awareness. These are displayed before the initial run to allow you to inspect the query for possible malicious queries. All queries used in these reporting templates are SELECT/READ only. You may* also get some First-Run / Setup dialogs.
(* depending upon previous Power-BI usage and settings/preferences of your computer)

These may not be in order.

 

Optional: you can cancel here.
 

 

Warning dialog when opening a report book with multiple sources of data.

 

 

SQL Queries


You may get a warning the 1st time a new SQL statement is run for each query in the report template (several).

 

Azure Log Analytics Workspace

 

 

SQL Server

 

Appendix A
Authentication Notes


On authentication screens, the method used will be determined by your organization and more specifically for the “Azure
Log Analytics Workspace” and “Management Server” SQL database for your ThinScale implementation (for v7.x on-prem
deployments).

 

Appendix B
SQL Views

The following are extended SQL Views used in Device Analytics Reporting Templates.
(Note: preliminary, should be already included in future updates/installs of the Management Server tbd)


Create a database backup or snapshot before making any changes to your database. We recommend having your database administrator review and assist with adding these views.


View: v-da-DevicesSummary (Provides a summary view of Device Details)

Device Summary.txt

 

View: v-da-DeviceFolderPathsC (Provides a flattened summary view of Device Folders and Sub-Folders)

Device Folder.txt

 

View: v-da-LicenseUsage (Provides a view of license table with product names)

 

License Usage.txt