Create an Intune report from the OData feed with Power BI
This article explains how to create a treemap visualization of your Intune data using Power BI Desktop that users an interactive filter. For example, your CFO might like to know how the overall distribution of devices compares between company-owned devices and personal devices. The treemap provides insight into the total number of device types. You can review the number of iOS/iPadOS, Android, and Windows devices that are either company owned or personally owned.
Overview of creating the chart
To create this chart, you will:
- Install Power BI Desktop if you don't already have it.
- Connect to the Intune Data Warehouse data model and retrieve current data for the model.
- Create or manage the data model relationships.
- Create the chart with data from the devices table.
- Create an interactive filter.
- View the finished chart.
A note about tables and entities
You work with tables in Power BI. A table contains data fields. Each data field has a data type. The field can only contain data of the data type. Data types are numbers, text, dates, and so on. The tables in Power BI fill with recent historical data from your tenant when you load the model. Although the specific data changes with time, the table structure won't change unless the underlying data model is updated.
You may be confused by the use of the term entity and table. The data model is accessible through an OData (Open Data Protocol) feed. In the universe of the OData, the containers that are called tables in Power BI are called entities. These terms both refer to the same thing that holds your data. For more information about OData, see the OData Overview.
Install Power BI Desktop
Install the latest version of Power BI Desktop. You can download Power BI Desktop from: PowerBI.microsoft.com
Connect to the OData feed for the Intune Data Warehouse for your tenant
You need permission to Reports in Intune. For more information, see Authorization.
Sign in to the Microsoft Endpoint Manager admin center.
Select Reports > Intune Data warehouse > Data warehouse.
Copy the custom feed URL. For example:
Open Power BI Desktop.
From the menubar, select File > Get Data > Odata feed.
Paste the custom feed URL, that you copied from the earlier step, into the URL box in the OData feed window.
Select Organization account, and then sign in with your Intune credentials.
Select Connect. The Navigator will open and show you the list of tables in the Intune Data Warehouse.
Select the devices and the ownerTypes tables. Select Load. Power BI loads data to the model.
Create a relationship
You can import multiple tables to analyze not just the data in a single table but related data across tables. Power BI has a feature called autodetect that attempts to find and create relationships for you. The tables in the Data Warehouse have been built to work with the autodetect feature in Power BI. However, even if Power BI doesn't automatically find the relationships, you can still manage the relationships.
- Select Manage Relationships.
- Select Autodetect... if Power BI has not already detected the relationships.
The relationship is displayed in a From column to a To column. In this example, the data field ownerTypeKey in the devices table links to the data field ownerTypeKey in the ownerTypes table. You use the relationship to look up the plain name of the device type code in the devices table.
Create a treemap visualization
A treemap chart shows hierarchical data as boxes within boxes. Each branch of the hierarchy is a box contains smaller boxes showing subbranches. You can use Power BI desktop to create a treemap of your Intune tenant data that shows relative amounts of device manufacturer types.
- In the Visualizations pane, find and select Treemap. The Treemap chart will be added to the report canvas.
- In the Fields pane, find the
- Expand the
devicestable and select the
- Drag the
manufacturerdata field to the report canvas and drop it on the Treemap chart.
- Drag the
deviceKeydata field from the
devicestable to the Visualizations pane and drop it on under the Values section in the box labeled Add data fields here.
You now have a visual that shows the distribution of manufacturers of devices within your organization.
Add a filter
You can add a filter to your treemap so that you can answer additional questions using your app.
To add a filter, select the report canvas, and then select the Slicer icon () under Visualizations. The empty Slicer visualization will appear on the canvas.
In the Fields pane, find the
ownerTypestable and select the
onwerTypeNamedata field from the
ownerTypestable to the Filters pane and drop it on under the Filters on this page section in the box labeled Add data fields here.
OwnerTypestable, there's a data field called
OwnerTypeKeythat contains a data as to whether a device is company-owned or personal. Since you would like to show friendly names in this filter, look for the
ownerTypestable and drag the ownerTypeName to the Slicer. This example shows how the data model supports relationships between tables.
You now have an interactive filter that can be used to toggle between company owned and personally owned devices. Use this filter to see how the distribution changes.
- Select Company within the Slicer to see that the company owned device distribution.
- Select Personal within the Slicer to see the personally owned devices.