Analyzing your Azure EA Billing Data with Power BI

UPDATE Oct 2015

There is now a content pack available for Power BI which lets you get some analysis of the Azure EA Billing data.

http://blogs.msdn.com/b/powerbi/archive/2015/10/21/visualize-your-azure-enterprise-data-with-power-bi.aspx

A whole bunch of people seem to still be using the below approach too so you might still want to check out the below.

Over the years most of the customers I have worked with have obtained an enterprise agreement (EA) for Microsoft Azure. There are many benefits to the EA such as:

1. The prices are cheaper
2. It can streamline the procurement process for new resources in the cloud
3. With subscriptions and the ability to indicate departments and cost codes I can apply cross charging internally to Azure usage

That said although there are benefits there are some challenges which cloud introduces and can be magnified if you have an EA. Some of these include:

1. How do I control resources added to the cloud? I don’t want a candy shop for developers
2. How do I get a holistic view of all of my Azure resources?
3. How do I know I am getting good value for money?
4. How do I know my resources are being used effectively?

There are probably a bunch of other questions that you might want to ask and if you have access to the billing data you can find out some interesting stuff. The problem, however, is that the billing data is available via an API or a CSV download so if we want to mine this data we need to get it into a friendly place. I know that some vendors have been exploring the option to provide a SaaS application which would allow you to give them your EA Billing API Key and they would give you some analysis but I think the customers I have typically worked with are typically quite security conscious and giving this key to a 3rd party and also allowing a 3rd party to see a lot of information about our Azure usage is something which they would typically have some concerns with so we decided to explore options to do something ourselves.

This is what I did.

(Please note some of the figures/amounts are fudged slightly as its a blog article rather than using real data)

Step 1 – Create somewhere to store the data

I decided that the best thing to do here was to create a SQL Azure DB which I would load data from the billing API into. I chose to use SQL Azure as it was an easy thing to setup and the basic SQL Azure Database gives me plenty of space but has a very small cost yet it would allow me to easily do some analysis.

The script I used to setup the database is below:

[snippet id=”491″ title=”EA Billing Database – TSQL” height=”10″ line_numbers=”false”]

Once the database is created in the normal way and you have ran the above script to create the tables and views you will have somewhere to save the data.

Step 2 – Data Loading Web Job

The next step was to create a console application which would query the EA Billing API and load the data into the database. I have created a console application which can be downloaded from the below link which will do this. In the console application you will need to go to the app.config file and supply a value for the following:

• Database connection string where the data will be saved
• EA Enrolment number which can be obtained from the EA Portal
• EA API Key which can be obtained from the EA Portal

Once you have these configuration values the console app will be able to run and copy the data over to the database.

Please note in the code I have broken down the various steps involved into seperate classes in the Processes folder within the code base and they are loaded into an autofac container from where they are accessed. If you are not familiar with this approach its pretty straightforward but I did this so when I want to tidy the tool up and make it a bit more production worthy we can do some decent mocked unit testing here.

Once you have applied the config settings and build the project it will be runnable (as long as you have configured the firewall for your SQL DB). What I want to do though is run this as a scheduled task in Azure and the best way to do this is with a WebJob. I created a zip file containing the output from visual studio (the .exe, .dll’s and config files) and using an Azure Websites webjob I uploaded the zip file to create my new webjob. I set the schedule to run the job once each night. The console app will check the records to see if it has already processed a billing item and discard the ones that it has already processed. It will also at the end of the month mark a month as fully loaded so that it doesn’t have to keep processing all records each day, it should only be processing the current month and any months not marked as complete. This means you should not get any duplicate data so the job can be re-run repeatedly without issue.

Once I have my web job setup and loading the data I now just need to figure out how to show it to people.

Download console app here: http://cscblogsamples.blob.core.windows.net/publicblogsamples/AppFx.AzureEA.Billing.zip

Step 3 – Power BI

Now we have the data ready to display, recently I had seen a session by Tord Glad Nordahl at the London BizTalk Summit where he talked about the changes to Power BI and previously I had liked the idea but was a little put off with all of the Office 365 requirements and hadn’t got around to learning how to use Power BI properly. With the new Power BI Service it seemed simpler, especially when it came to some information on Excel PowerPivots as well as integration with the Azure service. It’s good to see the improvements that a few extra months brings.

I signed up at https://app.powerbi.com and within minutes I had my reporting dashboard ready to go. Note you need to use an organisational login.

Looking at Power BI I needed to use the Power BI designer to work with data from an Azure SQL DB so I downloaded the designer and added a connection (remembering the SQL DB firewall). Within minutes I had the data available to start playing with things and seeing what I could do. If you’re an advanced user like my friend is, you can often add an Excel plugin or two if you are importing Excel spreadsheets for data visualization. This will reduce compatibility issues when sharing data with stakeholders. But when I was ready I uploaded the power BI designer file into the portal and I could choose which graphs I wanted to display in the dashboard. Within 30 minutes of never having used Power BI I had 5 colleagues around my desk looking at the cool insights we could get on our Azure estate.

Lets take a look at a few screen shots of what we did:

The Dashboard

The initial view I created was for the team to be able to see an overview of some interesting stuff about our Azure estate. Straightaway you can see some things which look interesting. See below:

Total Lifetime Spend

This gives us a cool view of the highest level figure of how much we have spent on Azure since we started the enterprise agreement.

Spend by Day

Before analysing the billing data we knew that we were inefficiently using some resources and had spent some time tidying things up so we were not paying for stuff we weren’t using and also so we could ensure that we could get resources at the right scale so we didn’t configure stuff that was bigger than we needed.

The chart below shows how over time we managed to improve this and bring in our daily spend overall.

We also found that one of our subscriptions was where most of the spend was going and that allowed us to focus on that one area and see some improvements in that subscription to bring it more inline with the others with tighter control on costs. You can see this in the below chart where the green line shows a subscription which was one of our first ones contained a bunch of dev and test stuff which we hadn’t realized was costing what it was. You can see how this was brought back into a better place.

Spend by Month on Resource Type

In the next graph you can see that we can analyse how much we are spending on each type of service on Azure.

This is pretty cool and you can see how we brought in some of the costs of our Virtual Machines by using Azure Automation to turn off dev/test machines when they were not being used (the big blue coloured section). We also could see that we were spending more money on cache than we thought so we will be looking into that next.

Other Reports

There were a few other reports I was able to produce which were pretty cool but they contain sensitive information which I can not share here. They included:

• What are my most expensive Azure resources
• Show the change in cost per day for my resources
• What resources have been added to our Azure subscriptions in the last 7 days

Lessons Learnt

I am new to Power BI from the developer perspective and one of the things I learned was that using the Power BI designer means you are not currently supported for auto-refresh of the dataset in your report. In hindsight, it would have been better to have an excel spreadsheet which uses power query to access the SQL Azure DB and store the excel spreadsheet in One Drive which would allow you to configure Power BI to refresh the data and perhaps if we did this each morning before everyone got to the office then that would work well.

Conclusion

In conclusion this experience was a really positive way to create a dashboard for our EA billing data which took hardly any time to put together. Id guess the console application and database took a couple of hours one evening and the Power BI bit, well in 30 mins I had loads of interesting graphs ready to look at. In the future we suspect that one of the important roles in your IT organisation will be around understanding the cloud billing and usage by your organisation and managing that you are getting good value for money.

We wanted to share this approach with the community in its current draft form to get feedback on what kind of things it should include and perhaps create a github project somewhere down the line, or alternatively hope that Microsoft will just offer EA billing as a data source for Power BI to make it even easier.

Please feel free to try this and share your thoughts