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 previousarticle, 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:

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

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:

CREATE TABLE [dbo].[Billing_MappingList] ([BusinessUnit] [NVARCHAR](20) NULL, [AppOrProject] [NVARCHAR](255) NULL, [ResponsibleContactEmail] [NVARCHAR](255) NULL, [DepartmentName] [NVARCHAR](255) NULL, [Subscription] [NVARCHAR](255) NULL, [ResourceGroup] [NVARCHAR](255) NULL, [CostCenter] [NVARCHAR](255) NULL, [RowLevelSecurityLogin] [VARCHAR](20) NULL);

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:

CREATE PROCEDURE [dbo].[GetBillingReport] ( @startDate DATE = NULL, @endDate DATE = NULL) AS IF @startDate IS NULL -- this gets the first day of the mosst recent finished month SET @startDate = DateAdd(DAY, 1, EOMonth(GetDate(), -2)); -- this gets the last day of the most recent finished month IF @endDate IS NULL SET @endDate = EOMonth(@startDate); PRINT @startDate; PRINT @endDate; -- dptmnt SELECT m.BusinessUnit AS [Business Unit], m.AppOrProject AS [Application/Project], m.ResponsibleContactEmail AS [Responsible DM/SM/PM], 'Microsoft' AS [Software Vendor], -- always 'Azure' AS [Product], -- always Convert(DECIMAL(18, 2), Sum(ea.ExtendedCost)) AS [Cost], 'EUR' AS [Currency], -- always '' AS [Cost in SEK], -- always @startDate AS [Effective Date(MM/YYYY)], ea.AccountOwnerId AS [Network/Cost Center Owner], IsNull( CASE WHEN CharIndex('&', m.CostCenter) > 0 THEN Substring(m.CostCenter, 0, CharIndex('&', m.CostCenter)) WHEN CharIndex('-', m.CostCenter) > 0 THEN Substring(m.CostCenter, 0, CharIndex('-', m.CostCenter)) ELSE m.CostCenter END, CASE WHEN CharIndex('&', ea.CostCenter) > 0 THEN Substring(ea.CostCenter, 0, CharIndex('&', ea.CostCenter)) WHEN CharIndex('-', ea.CostCenter) > 0 THEN Substring(ea.CostCenter, 0, CharIndex('-', ea.CostCenter)) ELSE ea.CostCenter END) AS [Network/Cost Center], IsNull( CASE WHEN CharIndex('&', m.CostCenter) > 0 THEN Subst

本文系统(windows)相关术语:三级网络技术 计算机三级网络技术 网络技术基础 计算机网络技术

本文标题:Azure EA Financial Reporting and Granular Access to Data Through the Enterprise

技术大类 技术大类 | 系统(windows) | 评论(0) | 阅读(75)