Are PowerBI and the Intune Data Warehouse ready for enterprise reporting?

question-marks

In our quest for an enterprise mobility reporting tool that can tell whether devices are compliant, secure and functional, we have investigated the key considerations for such a tool and looked at Microsoft Azure Portal interfacing Microsoft EMS, aka Intune.  Our conclusion for Azure Portal was that it was great for operational purposes (for which it is intended) and that it even creates a lot of ad-hoc reports, but it presents challenges as an enterprise reporting tool. For that purpose, Microsoft introduced the Intune Data Warehouse. This tool is still under development, or in “preview,” but is already a great tool that “lets you create custom reports from your Intune data using a reporting service that supports OData feeds.”

While the focus of the Microsoft Intune Service Dashboard and the underpinning Graph API is to manage devices, the purpose of the Intune Data Warehouse, combined with Power BI, is to provide reports. The data warehouse consists of many linked tables, providing a rich dataset both in width (number of fields/attributes it can report on) and in depth (historical data). The abundance of data fuels powerful custom reporting.

The Intune Data Warehouse itself, however, is not a reporting engine. It contains the data that make up the report. The data must be copied over to work with it locally, since the data warehouse actually contains a copy of the Intune data. It is therefore “read-only,” and you cannot manipulate the data in the warehouse itself. The data warehouse copy of the data is not continuous, but rather a once-a-day snapshot. It contains yesterday’s data — and detailed data for every day of the 90 days before.

Data schema from the Intune Data Warehouse

Data schema from the Intune Data Warehouse

Getting information from the data warehouse – possible but tricky

The easiest way to access the Intune Data Warehouse is by downloading the Power BI file from Azure Portal and running it with the (free) Power BI Desktop.  Each person who needs reports must use Power BI Desktop (with a report template) to create their own report and customize it. This means that each person must connect to the data warehouse and download their own dataset, which has a few disadvantages:

  • There is not one consistent data set for the report – rather each has a copy of a consistent dataset to create a report from.
  • There is a hefty quantity of data to be downloaded – for 10,000 devices the dataset is several gigabytes.
  • The download only happens when the user clicks the refresh button, which is not ideal – although it might be a blessing since the action triggers such a large download!
  • The data warehouse/Power BI Desktop combination is really envisioned to be used by the recipients of the report. For enterprise reporting, the recipients (management) expect a report that has been cleaned up a bit. They also expect the service provider to see data and know about any problems that appear in the reports – we don’t want to give recipients direct access to the raw data because it can contain double entries, unfinished enrollments and other operational “dirt” that could be confusing.

After downloading and connecting to the Intune Data Warehouse, we immediately see the pre-packaged view of the device management data. The Power BI Report template consists of a set of tabs (pages) and each tab has visualizations. The Power BI presentation figure below shows visualizations for “Manufacturers,” “Device ownership,” “Azure Active Directory (AAD) compliance” and “Devices.” Once a visualization contains the data required for the report you want to share with management, it is easy to export it to Excel.

Power BI presentation of Intune data

Power BI presentation of Intune data

As in the previous blog, let’s look at the key requirements for enterprise reporting specified in blog 1 and see how Intune Data Warehouse and PowerBI stack up:

  • Use only existing data — don’t collect anything new. As obvious as this requirement sounds, there are still gaps for which Intune and the Intune Data Warehouse don’t provide data, and we use Azure Operations Management Suite (OMS) to fill the gap. Reporting on security compliance is one of those hot items that Microsoft promises to close soon. The gap is getting smaller. Intune Data Warehouse is nearly there.
  • Use all available data collected about the service. Intune – and therefore Intune Data Warehouse — doesn’t provide us with an answer to the question, “Which user has a license assigned but isn’t using it?” In fact, Intune has no insight into data that sits in the customer’s back office. That’s not really an Intune problem though. If the data is important, it could be made available to a cloud-based reporting engine or combined with a cloud-based reporting engine using Power BI. I would give the Intune Data Warehouse a passing grade here . . . just about.
  • Data encrypted in situ and in transit and GDPR compliant. I have combined two requirements here. Microsoft Azure is compliant and provides us with means to create a compliant report. Again, definitely a passing grade.
  • Access to historic data. The Intune Data Warehouse contains 90 days’ worth of data, which is suitable for most purposes. But if I want to baseline my service for the last year, or do comparisons now versus last year, Intune Data Warehouse can’t help me. Still, I would give the Intune Data Warehouse a pass here . . . but be aware!
  • Supports automation (scheduled reporting). The combination of Data Warehouse with Power BI desktop doesn’t allow report scheduling. It removes the need for report scheduling a little, because my data source (the data warehouse) has a historic copy of the data available, at a set time of the day. So I have plenty of time to create a report on what the service looked like on the first day of the month, or the first Monday of the first full week of the month, or whatever. I still have to start this activity though, and after collecting / creating the report, distribute it to the recipients. Verdict: Intune Data Warehouse is halfway there.
  • Scales to enterprise level (25k devices and above). I have already talked about the sheer size of the download for enterprises above. This will need to be addressed if Microsoft wants to propose this tool as the main solution for enterprises.

Although the dataset in the Intune Data Warehouse is great, it is still limited to the underlying sources of Intune and AAD, with a bit of Office 365. It still needs to be combined with other sources, like procurement for device age and warranty information, or ServiceNow for incident and repair data. These additional data sources provide critical insight into the service health of the mobility service.

Powerful but not quite ready for enterprise reporting

The Intune Data Warehouse/Power BI combination is a very powerful tool for creating certain kinds of management reports, although it still presents challenges for meeting the requirements of general enterprise reporting. A future blog will discuss an enterprise-ready solution built on Intune Data Warehouse. Meanwhile, Microsoft is adding new capabilities (soon) so we are all anxiously awaiting an even more powerful resource!


Ben Santing headshotBen Santing is a DXC Technologist for Workplace and Mobility. He focuses on transforming cutting-edge technology into services for enterprise customers that provide business critical functions. His interest in the bigger picture is matched by technical skills that include Windows NT MCSE, ITIL V3 expert, IT Strategy and Architecture certificate, Azure EMS MCP, and are demonstrated in knowledge briefs and white papers. He held a variety of architecture and engineering positions within DXC Workplace and Mobility before becoming a lead architect for DXC Device as a Service and DXC Business Insight for Mobility. Ben lives in the Republic of Ireland, but originates from the Netherlands.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: