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:

  1. Power BI – Using the ‘Azure Enterprise’ content pack for Power B provides a glimpse into the last 12 months azure consumption.
  2. 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.

vishwalpic1

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).

vishwalpic2

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:

  1. Power BI: http://app.powerbi.com
  2. Power BI editions and pricing: https://powerbi.microsoft.com/en-us/pricing/
  3. Visualizing Azure Enterprise data in Power BI:
    http://blogs.msdn.com/b/powerbi/archive/2015/10/21/visualize-your-azure-enterprise-data-with-power-bi.aspx
  4. Azure Enterprise Content Pack for Power BI:
    https://powerbi.microsoft.com/en-us/documentation/powerbi-content-pack-azure-enterprise
  5. Sharing personal dashboards in Power BI:
    https://powerbi.microsoft.com/en-us/documentation/powerbi-service-share-unshare-dashboard
  6. Creating organizational content packs in Power BI:
    https://powerbi.microsoft.com/en-us/documentation/powerbi-service-organizational-content-pack-tutorial-create-and-publish/
  7. Finding and opening organizational content packs in Power BI:
    https://powerbi.microsoft.com/en-us/documentation/powerbi-service-organizational-content-pack-find-and-open
  8. Azure Billing and Usage API Overview:
    https://azure.microsoft.com/en-us/documentation/articles/billing-usage-rate-card-overview
  9. Azure Billing and Usage REST API reference:
    https://msdn.microsoft.com/en-us/library/azure/mt218998.aspx
  10. Exporting billing data to CSV:
    http://blogs.technet.com/b/keithmayer/archive/2015/06/30/export-azure-subscription-usage-with-new-billing-api-and-powershell.aspx

About Vishal Gamji

Vishal is a Data Architect at AIS with over eight years of experience in designing and implementing large scale data warehouses, and over four years of experience leading diverse and remote Agile teams. Vishal's work in the consulting domain prior to joining AIS spans various areas; the majority of which are the healthcare sector of managed care, medical devices & COTS products. Vishal has extensive expertise in the Microsoft Business Intelligence domain working with clients like CDC, Amerigroup, Medtronic Inc. & TeleTracking. Vishal received his Bachelors degree in Computer Engineering from Vishweswariah University, India and his Masters degree in Computer and Information Sciences from University of South Alabama. He is currently enrolled in the part-time MBA program at the University of Maryland majoring in Marketing & Strategy and is planning on graduating in Spring 2014. Vishal is also a Microsoft Certified IT Professional in SQL Server technology.