This post is the first in a series of three about the Azure Billing API and the analysis of the data it provides using Excel. This post is about the analysis part and the next will be about developing a program to download the data. Then, the third post will be about my ideas on how to make this reporting work in a large enterprise where different departments need access to their own consumption and not the entire company’s.
Since July 2014 there is a Billing API in the Azure EA Portal that enables you to create a key and download the consumption in tabular CSV format. It is the same data that you previously could download from the portal, but the using the API you can automate this and download a complete year in just minutes. With a few lines of code you have a complete integration ready and you have the information ready to be analysed.
Analysing the data in Excel
Once you have downloaded the data, you face the task of analysing several hundreds of thousends of rows of data with Azure internal names like Account, Subscription, Virtual Machines, yada-yada. It is all a mixed bowl of soup of Azure nomenclature. You need some simple way to visualize the data so you can get a clear view of your Azure consumption. There is a simple way to do that with Excel 2013 and PowerQuery.
Download PowerQuery
PowerQuery, which includes PowerView and PowerPivot, is a separate add-in that you have to download and install. It requires Excel 2013. You know that you have it if you see Power Query in the Excel menu.
Starting from scratch
Getting the data into Excel
Get the data into Excel is the first step and that is very simple. Do the following
- Start Excel 2013, create a Blank Worksheet
- Select POWERPIVOT in the menubar and then Manage. A new window called PowerPivot for Excel opens
- Press the “From Other Sources” and select SQL Azure or SQL Server depending on your database type
- Enter the connection information. If you are using SQL Azure, make sure you use Test Connection so that your corporate firewall doesn’t block that type of network traffic.
- Choose the “Select from a list of tables and views…” and select the table you store the billing data in (BillingUsageDetail in the demo case)
- The import process starts and ends with stating how many rows where imported
- Close the window and see a windows where Excel fills all table data.
Before continuing, this is the time to set the data type of some columns if you like them to be presented in certain ways. I usually do the following adjustments:
- ExtendedCost = Currency
- ResourceRate = Currency
Just select the column as usual in Excel and then set the data type.
Creating the first PivotTable
To create the first PivotTable, click PivotTable in the menubar and then PivotTable again.
Select “New Worksheet” when asked. By doing this you have a pretty empty Excel sheet looking like the below screenshot. The trick now is to drag the fields into the right areas of Filters, Columns, Rows and Values. Start with this to get a basic report:
- AccountName, SubscriptionName, Service, Component to the ROWS area
- ExtendedCost to VALUES area
- Year, Month to the COLUMNS area
Easiest way is to select-and-drag each column namn since clicking/checking it will lead to Excel sending it to the area it thinks it is best. If you get the order wrong you can sort by dragging columns around.
If you did that and add coloring via PowerTable Styles, you have a report looking like this. Since it is a PivotTable, you can drill down in the areas you dragged to ROWS, that is Account, SubscriptionName, Service and Component. Especially the latter two are of interest if you want to drill down to why a subscription is costing as much as it is.
- Service = Storage, SQL, Virtual Machines, WebSites, ie the different resource types you have deployed in Azure
- Component = the individual resource names (mostly) within each service
The basic report also gives you a breakdown on consumption over each year and month per ROWS area.
The ExtendedCost column in the billing data are values in the currency you have in the Azure Enterprise Agreement, which in normal cases means the current currency in the country. Regrettably, there is no Currency column in the billing data.
Creating the second PivotTable
The second PivotTable we will create will illustrate the trend of the consumption for each Subscription. To create that, go back to the PowerPivot for Excel window and
- Press PivotTable in the menubar but this time select PivotChart
- Click on the Chart in the sheet and select Change Chart Type and select a Line Chart
- Drag SubscriptionName field to LEGEND area
- Drag Year, Month fields to AXIS area
- Drag ExtendedCost to VALUES area
With a little styling, the PivotChart gives the Azure consumption in a diagram like the one below
With this, you can analyse your company’s Azure consumption and not be afraid of costs running wild! The third blog post will help you understand the fields DepartmentName, CostCenter and Tags that you can use to get values into the report that makes sense to your business.
Opening the Excel document again
When opening the Excel document again, the data you see in the report is not live data but data saved within the Excel document. To refresh the figures, press PowerPivot in the menubar and then Manage and you will find Refresh in the menubar. Pressing refresh will ask you for the database password and when you hit OK the data will be imported again.