Azure EA Financial Reporting and Granular Access to Data Through the Enterprise

With the billing data from your Azure Enterprise Agreement collected into one database, it is still necessary to break down the costs and provide access to the correct departments and individuals. This is important for chargeback and further analysis. In this article, Feodor Georgiev demonstrates how to provide granular access to the billing data.

The series so far:

  1. Understanding your Azure EA Billing Data and Building a Centralized Data Storage Solution
  2. Azure EA Financial Reporting and Granular Access to Data Through the Enterprise

This series of articles describes how to customize the way that Azure does its billing, to meet the requirements of the accounts and budgeting of a typical business. In the previous article, we saw how to set up a centralized storage (Azure SQL Database) to use as a repository for the EA billing data. We created a Web App in Azure and a Web Job under it, so as to import new data as it comes to the EA portal. This is an easy way to get access to the EA billing data and gives us the opportunity to query and mine it.

In this article, we will focus on giving granular access. By this, I mean that each user role, whether per department or by a Subscription / Resource group combination, can access only their own data, whereas a Billing Manager can access it all. We will also explore the reporting possibilities and the composing of the Financial reporting for the Enterprise.

Many organizations benefit from purchasing an EA agreement for their Azure accounts in order to get benefits such as better pricing, easier procurement, internal cross-charges tagging.

To get the best use of this EA agreement, you will need to

  • control the resources added to the cloud
  • get a holistic view of all of the Azure resources being used
  • be able to check that the enterprise is getting good value for money
  • ensure that the resources are being used effectively

In the previous article we built a solution to poll and store data from the EA API, and now we are going to rely on that data to actually achieve these goals. This won’t be easy, because a typical organization will not have a single source of knowledge about the requirements, and there will be several Azure subscriptions for various departments, which are likely to run their own completely independent projects in Azure.

In order to account for costs optimization and resource utilization in such cases, the organization admins would have assigned access rights to different resource groups, based on projects. Each project would have a cost center number in the Finance department, and the costs for running the project will usually have to be billed to the specific department or team at the end of the monthly billing cycle.

Because of this project-ownership, it is important to give control of the resources and the cost management to each project / team, so they can bear informed responsibility for the way that they use these resources.

There are several challenges in doing this, and there are several ways to solve these challenges. As mentioned in the previous article, there are several ways to gather information about billing and spending patterns such as third party tools or Azure tools. These Azure tools include Cloudyn, the Billing Reader RBAC per Resource Group or PowerBI reports directly from EA portal

These options are great in general, but they are very weak contenders when it comes to the security concerns of the third party tools and the flexibility of the Azure built-in tools. The advantage with having a customized solution to store and mine the EA data is that we can use the Row Level Security feature of Azure SQL to give granular access only to certain users and only to their own resources: Also, we can automate the daily notifications of overspending patterns, and the creation of new resources.

Here are the topics covered in the article:

  • Automate the chargeback reporting sent to Finance by using a mapping table for each Department or Subscription / ResourceGroup
  • Give access to the appropriate EA billing data only to authorized users, Each user should see its own data and nothing more.
  • A Manager should be able to see reports on all data
  • A report with common costs should be produced. Express Route costs, for example, can be accumulated from different entities across the organization.
  • A list of newly created resources should be visible daily
  • Alarms should be sent as soon as possible for resources which incur costs over a certain rate

The cost of the home-brew solution should be minimal and the reporting should, preferably, be free in order to cut costs on the billing solution itself. For this purpose, we will optimize the solution resources and use Excel templates for users to explore their own data.

Automate the chargeback reporting sent to Finance by using a mapping table per Department or Subscription / ResourceGroup

Here is the solution roadmap we discussed earlier:

For charge distribution we need to create a mapping table that contains the data about which entities belong to what cost centers.

For the purpose of giving granular access to the organizational entities to view their own expenditure, we will build a Row Level Access functionality in the Azure SQL Database, so a specific login is mapped to a specific subset of the EA data, I.e. only their own. Only a Manager login will have access to the entire dataset.

To do this, we will create a table like this:

There are a few things to notice:

  • The CostCenter column is used by the Finance department to indicate where to send the bill
  • The BusinessUnit, AppOrProject and ResponsibleContactEmail columns are filled in during the introduction of new enterprise entities in Azure
  • The DepartmentName, Subscription and ResourceGroup are used for joining the data to the EA raw data
  • The RowLevelSecurityLogin column is used later on to map which SQL login has access to what subset of data

After creating the mapping table, and after inserting some data into it, we will be using the following stored procedure to get the monthly chargeback report:

There are four sections in the above procedure:

  • first we get all costs that are per department,
  • then we get all costs that are per Subscription and ResourceGreoup,
  • then we get the costs that are not in the mapping table yet and the shared resources
  • and finally, we get everything else which is not part of the subsets above

A report with common costs should be produced (for example Express Route costs can be accumulated from different entities across the organization)

There are several challenges remaining: there are certain resources in Azure which incur costs, but they are shared. For example, if the organization is using Express Route, the cost for it is a bulk number in the EA data, but this cost can be generated by several organizational entities. This challenge has to be tackled internally in each enterprise, but from the point of view of the Finance department, all numbers should add up in the end. In other words, the Financial reporting has to point to a common shared cost (whether the cost is divided equally between entities or not). Here is the query for the report:

Notice that the procedures are written in a way that if no parameters are supplied, they always return the data for the most recent month that has finished. Otherwise, if a start date parameter is supplied, then the procedures return data for time between the start date and the last date of the month the start date is in.

Give access to the EA billing data only to authorized users

Now that we have the Mapping table ready and the Financial summary reports ready, we can move on to giving access to the specific Departments / Project users to their own data. Each user should see its own data and nothing more, and a Manager should be able to see reports on all data

In Azure SQL there is a functionality called Row Level Security and we will use it for creating a row level access functionality.

1. Create logins

2. Create users

3. Create a security function

4. Create the views

5. Create the Security Policy

This will deliver the datasets, based on the login function

Note that the Mapping table needs to have the appropriate login name for a specific row. The Manager login can see all data.

6. Give SELECT access to the users

A list of newly created resources should be visible daily, and alarms should be sent as soon as possible for resources which incur costs over a certain rate

It is essential for the cost optimization purposes to be able to audit the newly created resources in the Azure environment. This is fairly easy to do with a Common Table Expression query like this:

Getting the reporting from Excel

So far, we set up the access permissions and the queries for the reports. From this point, we could use PowerBI, or any other reporting tool, but since the goal is cost savings, we will pull the data into Excel and mine it there.

Excel has a great functionality when it comes to connecting to external data sources. Just go to the Data tab, click on Get Data, from Azure SQL Database.

In the next window enter the SQL Server login details and the query:

Then enter the login credentials

Preview the data and load it into a Excel sheet. From there on, the options are unlimited: graphs, pivot tables what-if scenarios and so on.

In this case we demonstrated the Manager’s view of the billing report. But if we repeat the above procedure for getting data into Excel and we use the ABC login together with the following query “SELECT * FROM [dbo].[vGetBillingReport] ” then the data only for the user ABC will be loaded in the Excel sheet (provided that the user ABC is mapped properly in the Mapping table, i.e. at least one record in the mapping table must have ABC in the RowLevelSecurityLogin column ).

Another great thing about using this approach is that once the Excel documents are created they can be sent to other users as templates. There is no need to re-create the Excel sheets. The data in them will be visible to the user who receives them (so make sure you clean up the data from the Excel sheets before sending them!), however they will be asked to enter their login credentials and as soon as they do that, the most recent data will be loaded for them based on their access.

Conclusion

So far in the series of articles on EA billing data management, we saw how to set up a centralized storage (Azure SQL Database) and use it as a repository for the EA billing data. We set up a Web App in Azure and a Web Job under it, so it can be scheduled and import new data as it comes to the EA portal.

This is an easy way to get access to the EA billing data, and it gives us the opportunity to query and mine it.

In this article we focused on the ways of giving granular access – each entity of users can access only their own data, and a Manager can access it all. We also explored the reporting possibilities and composing the Financial reporting for the Enterprise.