Tuesday, October 9, 2018

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:

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
           Substring(
             m.CostCenter,
             CharIndex('&', m.CostCenter) + 1,
             Len(m.CostCenter))
      WHEN CharIndex('-', m.CostCenter) > 0 THEN
        Substring(
          m.CostCenter,
          CharIndex('-', m.CostCenter) + 1,
          Len(m.CostCenter)) ELSE NULL END,
    CASE WHEN CharIndex('&', ea.CostCenter) > 0 THEN
           Substring(
             ea.CostCenter,
             CharIndex('&', ea.CostCenter) + 1,
             Len(ea.CostCenter))
      WHEN CharIndex('-', ea.CostCenter) > 0 THEN
        Substring(
          ea.CostCenter,
          CharIndex('-', ea.CostCenter) + 1,
          Len(ea.CostCenter)) ELSE '' END) AS [Activity Code],
  'N/A' AS [Customer Account],
  'q' + Convert(CHAR(1), DatePart(QUARTER, @startDate)) AS [Month to be invoiced],
  'Report from ' + Convert(VARCHAR(20), @startDate) + ' to '
  + Convert(VARCHAR(20), @endDate) AS Report_StartDate_EndDate
  FROM [dbo].[EaBillingData] ea
    LEFT OUTER JOIN dbo.Billing_MappingList m
      ON m.DepartmentName = ea.DepartmentName
  WHERE ea.[Date] BETWEEN @startDate AND @endDate
    AND m.DepartmentName IS NOT NULL
  GROUP BY 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),
  IsNull(
    CASE WHEN CharIndex('&', m.CostCenter) > 0 THEN
           Substring(
             m.CostCenter,
             CharIndex('&', m.CostCenter) + 1,
             Len(m.CostCenter))
      WHEN CharIndex('-', m.CostCenter) > 0 THEN
        Substring(
          m.CostCenter,
          CharIndex('-', m.CostCenter) + 1,
          Len(m.CostCenter)) ELSE NULL END,
    CASE WHEN CharIndex('&', ea.CostCenter) > 0 THEN
           Substring(
             ea.CostCenter,
             CharIndex('&', ea.CostCenter) + 1,
             Len(ea.CostCenter))
      WHEN CharIndex('-', ea.CostCenter) > 0 THEN
        Substring(
          ea.CostCenter,
          CharIndex('-', ea.CostCenter) + 1,
          Len(ea.CostCenter)) ELSE '' END), m.BusinessUnit,
  m.AppOrProject, m.ResponsibleContactEmail, ea.AccountOwnerId
UNION ALL
-- Subscr and RG
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
           Substring(
             m.CostCenter,
             CharIndex('&', m.CostCenter) + 1,
             Len(m.CostCenter))
      WHEN CharIndex('-', m.CostCenter) > 0 THEN
        Substring(
          m.CostCenter,
          CharIndex('-', m.CostCenter) + 1,
          Len(m.CostCenter)) ELSE NULL END,
    CASE WHEN CharIndex('&', ea.CostCenter) > 0 THEN
           Substring(
             ea.CostCenter,
             CharIndex('&', ea.CostCenter) + 1,
             Len(ea.CostCenter))
      WHEN CharIndex('-', ea.CostCenter) > 0 THEN
        Substring(
          ea.CostCenter,
          CharIndex('-', ea.CostCenter) + 1,
          Len(ea.CostCenter)) ELSE '' END) AS [Activity Code],
  'N/A' AS [Customer Account],
  'q' + Convert(CHAR(1), DatePart(QUARTER, @startDate)) AS [Month to be invoiced],
  'Report from ' + Convert(VARCHAR(20), @startDate) + ' to '
  + Convert(VARCHAR(20), @endDate) AS Report_StartDate_EndDate
  FROM [dbo].[EaBillingData] ea
    LEFT OUTER JOIN dbo.Billing_MappingList m
      ON ea.SubscriptionName = m.Subscription
     AND ea.ResourceGroup = m.ResourceGroup
  WHERE ea.[Date] BETWEEN @startDate AND @endDate
    AND m.DepartmentName IS NULL
    AND m.Subscription IS NOT NULL
    AND m.ResourceGroup IS NOT NULL
  GROUP BY 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),
  IsNull(
    CASE WHEN CharIndex('&', m.CostCenter) > 0 THEN
           Substring(
             m.CostCenter,
             CharIndex('&', m.CostCenter) + 1,
             Len(m.CostCenter))
      WHEN CharIndex('-', m.CostCenter) > 0 THEN
        Substring(
          m.CostCenter,
          CharIndex('-', m.CostCenter) + 1,
          Len(m.CostCenter)) ELSE NULL END,
    CASE WHEN CharIndex('&', ea.CostCenter) > 0 THEN
           Substring(
             ea.CostCenter,
             CharIndex('&', ea.CostCenter) + 1,
             Len(ea.CostCenter))
      WHEN CharIndex('-', ea.CostCenter) > 0 THEN
        Substring(
          ea.CostCenter,
          CharIndex('-', ea.CostCenter) + 1,
          Len(ea.CostCenter)) ELSE '' END), m.BusinessUnit,
  m.AppOrProject, m.ResponsibleContactEmail, ea.AccountOwnerId
UNION ALL
-- all shared costs that are unallocated AND all classsic resources
SELECT '' AS [Business Unit],
  'shared costs (ExpressRoute, VPN etc) that are unallocated AND all classsic resources' AS [Application/Project],
  '' 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],
  NULL AS [Network/Cost Center], NULL AS [Activity Code],
  'N/A' AS [Customer Account],
  'q' + Convert(CHAR(1), DatePart(QUARTER, @startDate)) AS [Month to be invoiced],
  'Report from ' + Convert(VARCHAR(20), @startDate) + ' to '
  + Convert(VARCHAR(20), @endDate) AS Report_StartDate_EndDate
  --,ea.SubscriptionName,ea.ResourceGroup
  FROM [dbo].[EaBillingData] ea
    LEFT OUTER JOIN dbo.Billing_MappingList m2
      ON ea.SubscriptionName = m2.Subscription
     AND ea.ResourceGroup = m2.ResourceGroup
  WHERE(ea.[Date] BETWEEN @startDate AND @endDate)
   AND (ea.DepartmentName NOT IN
          (SELECT DISTINCT DepartmentName
             FROM dbo.Billing_MappingList
             WHERE DepartmentName IS NOT NULL))
   AND
     (m2.Subscription IS NULL AND m2.ResourceGroup IS NULL)
   AND
     (ea.SubscriptionName IS NOT NULL AND ea.ResourceGroup = '')
  GROUP BY ea.AccountOwnerId
UNION ALL
-- everything else
SELECT '' AS [Business Unit], 'For review' AS [Application/Project],
  '' 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],
  NULL AS [Network/Cost Center], NULL AS [Activity Code],
  'N/A' AS [Customer Account],
  'q' + Convert(CHAR(1), DatePart(QUARTER, @startDate)) AS [Month to be invoiced],
  'Report from ' + Convert(VARCHAR(20), @startDate) + ' to '
  + Convert(VARCHAR(20), @endDate) AS Report_StartDate_EndDate
  FROM [dbo].[EaBillingData] ea
    LEFT OUTER JOIN dbo.Billing_MappingList m2
      ON ea.SubscriptionName = m2.Subscription
     AND ea.ResourceGroup = m2.ResourceGroup
  WHERE(ea.[Date] BETWEEN @startDate AND @endDate)
   AND (ea.DepartmentName NOT IN
          (SELECT DISTINCT DepartmentName
             FROM dbo.Billing_MappingList
             WHERE DepartmentName IS NOT NULL))
   AND
     (m2.Subscription IS NULL AND m2.ResourceGroup IS NULL)
   AND
     (ea.SubscriptionName IS NOT NULL AND ea.ResourceGroup <> '')
  GROUP BY ea.AccountOwnerId;

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:

CREATE PROCEDURE [dbo].[GetRecordsForReviewReport]
  (
@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;
SELECT DISTINCT ea.DepartmentName, ea.SubscriptionName,
  ea.ResourceGroup,
  Convert(DECIMAL(18, 2), Sum(ea.ExtendedCost)) AS TotalCost,
  Min(Convert(DATE, ea.Date)) AS FirstTimeSeen,
  'Report from ' + Convert(VARCHAR(20), @startDate) + ' to '
  + Convert(VARCHAR(20), @endDate) AS Report_StartDate_EndDate
  FROM [dbo].[EaBillingData] ea
    LEFT OUTER JOIN dbo.Billing_MappingList m2
      ON ea.SubscriptionName = m2.Subscription
     AND ea.ResourceGroup = m2.ResourceGroup
  WHERE(ea.[Date] BETWEEN @startDate AND @endDate)
   AND (ea.DepartmentName NOT IN
          (SELECT DISTINCT DepartmentName
             FROM dbo.Billing_MappingList
             WHERE DepartmentName IS NOT NULL))
   AND
     (m2.Subscription IS NULL AND m2.ResourceGroup IS NULL)
   AND
     (ea.SubscriptionName IS NOT NULL AND ea.ResourceGroup <> '')
  GROUP BY ea.DepartmentName, ea.SubscriptionName, ea.ResourceGroup;
GO
CREATE PROCEDURE [dbo].[GetUnmappedSharedCostsAndClassicResourcesReport]
  (
@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;
-- all shared costs that are unallocated AND all classsic resources
SELECT ea.DepartmentName, ea.SubscriptionName, ea.ResourceGroup,
  ea.Product, ea.Service, ea.ServiceType,
  Convert(DECIMAL(18, 2), Sum(ea.ExtendedCost)) AS TotalCost,
  Min(Convert(DATE, ea.Date)) AS FirstTimeSeen,
  'Report from ' + Convert(VARCHAR(20), @startDate) + ' to '
  + Convert(VARCHAR(20), @endDate) AS Report_StartDate_EndDate
  FROM [dbo].[EaBillingData] ea
    LEFT OUTER JOIN dbo.Billing_MappingList m2
      ON ea.SubscriptionName = m2.Subscription
     AND ea.ResourceGroup = m2.ResourceGroup
  WHERE(ea.[Date] BETWEEN @startDate AND @endDate)
   AND (ea.DepartmentName NOT IN
          (SELECT DISTINCT DepartmentName
             FROM dbo.Billing_MappingList
             WHERE DepartmentName IS NOT NULL))
   AND
     (m2.Subscription IS NULL AND m2.ResourceGroup IS NULL)
   AND
     (ea.SubscriptionName IS NOT NULL AND ea.ResourceGroup = '')
  GROUP BY ea.DepartmentName, ea.SubscriptionName, ea.ResourceGroup,
  ea.Product, ea.Service, ea.ServiceType;

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

CREATE LOGIN [Manager] WITH PASSWORD = 'AbcAbc123b@!';
GO
CREATE LOGIN [ABC] WITH PASSWORD = 'Abc123Abck@!';
GO

2. Create users

CREATE USER Manager FROM LOGIN Manager;
GO
GRANT CONNECT TO [Manager]
GO
CREATE USER ABC FROM LOGIN ABC;
GO
GRANT CONNECT TO [ABC]
GO

3. Create a security function

CREATE SCHEMA [Security]
    AUTHORIZATION [dbo];
CREATE FUNCTION [Security].[fn_securitypredicate](@login AS sysname)  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result   
WHERE @login = USER_NAME() OR USER_NAME() = 'Manager';
IF EXISTS
(
    SELECT 1
    FROM sys.security_policies sp
    WHERE OBJECT_ID('LoginFilter') = sp.object_id
)
    DROP SECURITY POLICY [dbo].[LoginFilter];

4. Create the views

CREATE VIEW [dbo].[vFlattenedData]
WITH SCHEMABINDING
AS
SELECT ea.AccountOwnerId, ea.AccountName, ea.ServiceAdministratorId,
  ea.Id AS AzureSubscriptionID, ea.SubscriptionId,
  ea.SubscriptionGuid, ea.SubscriptionName, ea.Service,
  ea.ServiceType, ea.ServiceResource, ea.ServiceInfo, ea.Component,
  ea.ServiceInfo1, ea.ServiceInfo2, ea.ResourceKey, ea.Date,
  ea.Product, ea.ResourceGUID, ea.ResourceGroup, ea.ServiceRegion,
  ea.ResourceQtyConsumed, ea.ServiceSubRegion, ea.AdditionalInfo,
  ea.Tags, ea.DepartmentName, ea.CostCenter, ea.ExtendedCost,
  ea.ResourceRate, m.RowLevelSecurityLogin
  FROM dbo.EaBillingData ea
    LEFT OUTER JOIN dbo.Billing_MappingList m
      ON ea.SubscriptionName = m.Subscription
     AND m.ResourceGroup = ea.ResourceGroup;
GO
CREATE VIEW [dbo].[vGetBillingReport]
WITH SCHEMABINDING
AS
-- 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
  DateAdd(DAY, 1, EOMonth(GetDate(), -1)) 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
           Substring(
             m.CostCenter,
             CharIndex('&', m.CostCenter) + 1,
             Len(m.CostCenter))
      WHEN CharIndex('-', m.CostCenter) > 0 THEN
        Substring(
          m.CostCenter,
          CharIndex('-', m.CostCenter) + 1,
          Len(m.CostCenter)) ELSE NULL END,
    CASE WHEN CharIndex('&', ea.CostCenter) > 0 THEN
           Substring(
             ea.CostCenter,
             CharIndex('&', ea.CostCenter) + 1,
             Len(ea.CostCenter))
      WHEN CharIndex('-', ea.CostCenter) > 0 THEN
        Substring(
          ea.CostCenter,
          CharIndex('-', ea.CostCenter) + 1,
          Len(ea.CostCenter)) ELSE '' END) AS [Activity Code],
  'N/A' AS [Customer Account],
  'q'
  + Convert(
      CHAR(1),
      DatePart(QUARTER, DateAdd(DAY, 1, EOMonth(GetDate(), -1)))) AS [Month to be invoiced],
  'Report from '
  + Convert(VARCHAR(20), DateAdd(DAY, 1, EOMonth(GetDate(), -1)))
  + ' to '
  + Convert(
      VARCHAR(20), EOMonth(DateAdd(DAY, 1, EOMonth(GetDate(), -1)))) AS Report_StartDate_EndDate,
  m.RowLevelSecurityLogin
  FROM [dbo].[EaBillingData] ea
    LEFT OUTER JOIN dbo.Billing_MappingList m
      ON m.DepartmentName = ea.DepartmentName
  WHERE ea.[Date] BETWEEN DateAdd(DAY, 1, EOMonth(GetDate(), -1)) AND EOMonth(
                                                                        DateAdd(
                                                                          DAY,
                                                                          1,
                                                                          EOMonth(
                                                                            GetDate(),
                                                                            -1)))
    AND m.DepartmentName IS NOT NULL
  GROUP BY 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),
  IsNull(
    CASE WHEN CharIndex('&', m.CostCenter) > 0 THEN
           Substring(
             m.CostCenter,
             CharIndex('&', m.CostCenter) + 1,
             Len(m.CostCenter))
      WHEN CharIndex('-', m.CostCenter) > 0 THEN
        Substring(
          m.CostCenter,
          CharIndex('-', m.CostCenter) + 1,
          Len(m.CostCenter)) ELSE NULL END,
    CASE WHEN CharIndex('&', ea.CostCenter) > 0 THEN
           Substring(
             ea.CostCenter,
             CharIndex('&', ea.CostCenter) + 1,
             Len(ea.CostCenter))
      WHEN CharIndex('-', ea.CostCenter) > 0 THEN
        Substring(
          ea.CostCenter,
          CharIndex('-', ea.CostCenter) + 1,
          Len(ea.CostCenter)) ELSE '' END), m.BusinessUnit,
  m.AppOrProject, m.ResponsibleContactEmail, ea.AccountOwnerId,
  m.RowLevelSecurityLogin
UNION ALL
-- Subscr and RG
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
  DateAdd(DAY, 1, EOMonth(GetDate(), -1)) 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
           Substring(
             m.CostCenter,
             CharIndex('&', m.CostCenter) + 1,
             Len(m.CostCenter))
      WHEN CharIndex('-', m.CostCenter) > 0 THEN
        Substring(
          m.CostCenter,
          CharIndex('-', m.CostCenter) + 1,
          Len(m.CostCenter)) ELSE NULL END,
    CASE WHEN CharIndex('&', ea.CostCenter) > 0 THEN
           Substring(
             ea.CostCenter,
             CharIndex('&', ea.CostCenter) + 1,
             Len(ea.CostCenter))
      WHEN CharIndex('-', ea.CostCenter) > 0 THEN
        Substring(
          ea.CostCenter,
          CharIndex('-', ea.CostCenter) + 1,
          Len(ea.CostCenter)) ELSE '' END) AS [Activity Code],
  'N/A' AS [Customer Account],
  'q'
  + Convert(
      CHAR(1),
      DatePart(QUARTER, DateAdd(DAY, 1, EOMonth(GetDate(), -1)))) AS [Month to be invoiced],
  'Report from '
  + Convert(VARCHAR(20), DateAdd(DAY, 1, EOMonth(GetDate(), -1)))
  + ' to '
  + Convert(
      VARCHAR(20), EOMonth(DateAdd(DAY, 1, EOMonth(GetDate(), -1)))) AS Report_StartDate_EndDate,
  m.RowLevelSecurityLogin
  FROM [dbo].[EaBillingData] ea
    LEFT OUTER JOIN dbo.Billing_MappingList m
      ON ea.SubscriptionName = m.Subscription
     AND ea.ResourceGroup = m.ResourceGroup
  WHERE ea.[Date] BETWEEN DateAdd(DAY, 1, EOMonth(GetDate(), -1)) AND EOMonth(
                                                                        DateAdd(
                                                                          DAY,
                                                                          1,
                                                                          EOMonth(
                                                                            GetDate(),
                                                                            -1)))
    AND m.DepartmentName IS NULL
    AND m.Subscription IS NOT NULL
    AND m.ResourceGroup IS NOT NULL
  GROUP BY 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),
  IsNull(
    CASE WHEN CharIndex('&', m.CostCenter) > 0 THEN
           Substring(
             m.CostCenter,
             CharIndex('&', m.CostCenter) + 1,
             Len(m.CostCenter))
      WHEN CharIndex('-', m.CostCenter) > 0 THEN
        Substring(
          m.CostCenter,
          CharIndex('-', m.CostCenter) + 1,
          Len(m.CostCenter)) ELSE NULL END,
    CASE WHEN CharIndex('&', ea.CostCenter) > 0 THEN
           Substring(
             ea.CostCenter,
             CharIndex('&', ea.CostCenter) + 1,
             Len(ea.CostCenter))
      WHEN CharIndex('-', ea.CostCenter) > 0 THEN
        Substring(
          ea.CostCenter,
          CharIndex('-', ea.CostCenter) + 1,
          Len(ea.CostCenter)) ELSE '' END), m.BusinessUnit,
  m.AppOrProject, m.ResponsibleContactEmail, ea.AccountOwnerId,
  m.RowLevelSecurityLogin
UNION ALL
-- all shared costs that are unallocated AND all classsic resources
SELECT '' AS [Business Unit],
  'shared costs (ExpressRoute, VPN etc) that are unallocated AND all classsic resources' AS [Application/Project],
  '' 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
  DateAdd(DAY, 1, EOMonth(GetDate(), -1)) AS [Effective Date(MM/YYYY)],
  ea.AccountOwnerId AS [Network/Cost Center Owner],
  NULL AS [Network/Cost Center], NULL AS [Activity Code],
  'N/A' AS [Customer Account],
  'q'
  + Convert(
      CHAR(1),
      DatePart(QUARTER, DateAdd(DAY, 1, EOMonth(GetDate(), -1)))) AS [Month to be invoiced],
  'Report from '
  + Convert(VARCHAR(20), DateAdd(DAY, 1, EOMonth(GetDate(), -1)))
  + ' to '
  + Convert(
      VARCHAR(20), EOMonth(DateAdd(DAY, 1, EOMonth(GetDate(), -1)))) AS Report_StartDate_EndDate,
  m2.RowLevelSecurityLogin
  FROM [dbo].[EaBillingData] ea
    LEFT OUTER JOIN dbo.Billing_MappingList m2
      ON ea.SubscriptionName = m2.Subscription
     AND ea.ResourceGroup = m2.ResourceGroup
  WHERE(ea.[Date] BETWEEN DateAdd(DAY, 1, EOMonth(GetDate(), -1)) AND EOMonth(
                                                                        DateAdd(
                                                                          DAY,
                                                                          1,
                                                                          EOMonth(
                                                                            GetDate(),
                                                                            -1))))
   AND (ea.DepartmentName NOT IN
          (SELECT DISTINCT DepartmentName
             FROM dbo.Billing_MappingList
             WHERE DepartmentName IS NOT NULL))
   AND
     (m2.Subscription IS NULL AND m2.ResourceGroup IS NULL)
   AND
     (ea.SubscriptionName IS NOT NULL AND ea.ResourceGroup = '')
  GROUP BY ea.AccountOwnerId, m2.RowLevelSecurityLogin
UNION ALL
-- everything else
SELECT '' AS [Business Unit], 'For review' AS [Application/Project],
  '' 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
  DateAdd(DAY, 1, EOMonth(GetDate(), -1)) AS [Effective Date(MM/YYYY)],
  ea.AccountOwnerId AS [Network/Cost Center Owner],
  NULL AS [Network/Cost Center], NULL AS [Activity Code],
  'N/A' AS [Customer Account],
  'q'
  + Convert(
      CHAR(1),
      DatePart(QUARTER, DateAdd(DAY, 1, EOMonth(GetDate(), -1)))) AS [Month to be invoiced],
  'Report from '
  + Convert(VARCHAR(20), DateAdd(DAY, 1, EOMonth(GetDate(), -1)))
  + ' to '
  + Convert(
      VARCHAR(20), EOMonth(DateAdd(DAY, 1, EOMonth(GetDate(), -1)))) AS Report_StartDate_EndDate,
  m2.RowLevelSecurityLogin
  FROM [dbo].[EaBillingData] ea
    LEFT OUTER JOIN dbo.Billing_MappingList m2
      ON ea.SubscriptionName = m2.Subscription
     AND ea.ResourceGroup = m2.ResourceGroup
  WHERE(ea.[Date] BETWEEN DateAdd(DAY, 1, EOMonth(GetDate(), -1)) AND EOMonth(
                                                                        DateAdd(
                                                                          DAY,
                                                                          1,
                                                                          EOMonth(
                                                                            GetDate(),
                                                                            -1))))
   AND (ea.DepartmentName NOT IN
          (SELECT DISTINCT DepartmentName
             FROM dbo.Billing_MappingList
             WHERE DepartmentName IS NOT NULL))
   AND
     (m2.Subscription IS NULL AND m2.ResourceGroup IS NULL)
   AND
     (ea.SubscriptionName IS NOT NULL AND ea.ResourceGroup <> '')
  GROUP BY ea.AccountOwnerId, m2.RowLevelSecurityLogin;

5. Create the Security Policy

This will deliver the datasets, based on the login function

CREATE SECURITY POLICY [dbo].[LoginFilter] 
ADD FILTER PREDICATE [Security].[fn_securitypredicate]([RowLevelSecurityLogin]) ON [dbo].[vGetBillingReport],
ADD FILTER PREDICATE [Security].[fn_securitypredicate]([RowLevelSecurityLogin]) ON [dbo].[vFlattenedData]
WITH (STATE = ON, SCHEMABINDING = ON)
GO

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

GRANT SELECT ON OBJECT::[dbo].[vFlattenedData] TO [Manager] AS [dbo];
GRANT SELECT ON OBJECT::[dbo].[vGetBillingReport] TO [Manager] AS [dbo];
GRANT SELECT ON OBJECT::[dbo].[vFlattenedData] TO [ABC] AS [dbo];
GRANT SELECT ON OBJECT::[dbo].[vGetBillingReport] TO [ABC] AS [dbo];

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:

CREATE PROCEDURE [dbo].[AuditResourceCreation]
(
    @startDate DATE = NULL,
    @endDate DATE = NULL
)
AS
IF @startDate IS NULL
    SET @startDate = DATEADD(DAY, 1, EOMONTH(GETDATE(), -1));
IF @endDate IS NULL
    SET @endDate = EOMONTH(@startDate);
PRINT @startDate;
PRINT @endDate;
WITH cte
AS (SELECT ROW_NUMBER() OVER (PARTITION BY SubscriptionName, ResourceGroup ORDER BY Date) AS RowNo,
           SubscriptionName,
           ResourceGroup,
           Date AS DateFirstSeen
    FROM dbo.EaBillingData)
SELECT SubscriptionName,
       ResourceGroup,
       cte.DateFirstSeen
FROM cte
WHERE RowNo = 1
      AND cte.DateFirstSeen
      BETWEEN @startDate AND @endDate;

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.

The post EA Financial reporting and granular access to data through the Enterprise appeared first on Simple Talk.



from Simple Talk https://ift.tt/2Cxvf1a
via
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)