Analyzing Azure Billing and Usage Data with Power BI

As organizations move and provision resources in Microsoft Azure they find themselves with the need to analyze and monitor resource consumption for trends, review billing data to get a pulse on the organization’s return on cloud spend, and be in a better position to predict and manage operational costs.

Billing and resource usage data in Azure is made available to users in two forms:

Power BI – Using the ‘Azure Enterprise’ content pack for Power B provides a glimpse into the last 12 months azure consumption.
Azure Billing REST APIs – The Usage API and Rate Card API provided by the Azure Resource Manager allow users to retrieve resource consumption and billing information for an organization’s Azure subscription.

Problem

Accessing the billing data by any of the available means is not straightforward. There are a few things to consider with each option. This blog highlights different methods to access azure billing data with Power BI or via other alternative means when Power BI is not the best solution.

Three possible solutions are illustrated below where the first two use Power BI as the end user reporting interface and the third is a custom UI. In this blog ‘Azure Billing and Usage REST API’ will be referred to as ‘API’; and ‘Azure Billing and Usage Data’ will be referred to as ‘data’ to minimize verbosity.

Possible Solutions

Solution 1

Power BI app directly connects to the billing data using the ‘Microsoft Azure Enterprise’ content pack for a quick view into the last 12 months resource consumption.

A content pack is a connector that allows a Power BI user to connect to various services like Salesforce, Google Analytics, Microsoft Dynamics, etc. Content Packs are bundled with pre-created dashboards and datasets that automatically show data and visual insights about the business.

This method requires the user to know the enterprise’s azure account information like enrollment number, account key, and the api key. This information can be provided by the enterprise’s azure account administrator. Note that some organizations might consider this information to be sensitive and may not be willing to share it with others. In such cases solution 2 or 3 will be a better choice.

Solution 2

The second option has the Power BI app connecting to a intermediate staging location (azure table storage or azure SQL database where data is extracted from the API using an azure web job) instead of directly connecting to the API. This staging location can either be an Azure SQL database or Azure Table Storage.

While accessing the data and creating the dashboard becomes simple in this option, the task of distributing the dashboard can be an obstacle with the ‘free’ edition of Power BI. Dashboard distribution in this case needs to be done in the form of an organizational ‘content pack’ which is only available in the ‘Power BI Pro’ edition. The ‘Pro’ license has an additional license cost of $9.99 per-user-per-month (PUPM). Additional licensing info can be found here.

Note that Power BI Pro edition is *required* for creating, publishing, and viewing organizational content packs.

Solution 3

The third approach entails a custom solution (ex: an in-house developed Service Catalog or admin tool) to host the dashboard in a web page. This option does not use Power BI, but uses web page(s) to display the report. Additional report development is required in this option for creating the report(s).

Note: Solution options 2 and 3 include a storage cost for the azure SQL Database or for azure table storage.

Conclusion

The solutions above have been deliberatly discussed at a high level without going into too much detail so as to serve as a starting point for anyone interested in exploring the azure billing and usage data. Variations to solutions 2 and 3 can be to use a powershell script to import data from the API into a csv and then use SSIS to bring it into a relational schema and then create a custom report on top of that using Power BI. Regardless the approach, the idea is to get the data out from the API and into a persistent storage and then add a reporting layer on top of it. Selecting a solution should be based on how the billing data will be accessed, shared (distributed), and maintained which will lead into technical discussions on sharing the account information (S1) or use content packs (S2, S3) for organizational sharing.

Helpful links: