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 dont 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:

/****** Object:  Table [dbo].[AzureAccounts]    Script Date: 4/21/2015 10:56:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AzureAccounts](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[AccountOwnerId] [nvarchar](256) NOT NULL,
	[AccountName] [nvarchar](256) NOT NULL,
	[ServiceAdministratorId] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_AzureAccounts] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

GO
/****** Object:  Table [dbo].[AzureBillingLines]    Script Date: 4/21/2015 10:56:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AzureBillingLines](
	[LineID] [int] IDENTITY(1,1) NOT NULL,
	[LineKey] [nvarchar](512) NOT NULL,
	[AccountOwnerId] [nvarchar](256) NOT NULL,
	[AccountName] [nvarchar](256) NOT NULL,
	[ServiceAdministratorId] [nvarchar](256) NOT NULL,
	[SubscriptionId] [nvarchar](256) NOT NULL,
	[SubscriptionGuid] [nvarchar](256) NOT NULL,
	[SubscriptionName] [nvarchar](256) NOT NULL,
	[LineItemDate] [datetime] NOT NULL,
	[LineItemMonth] [int] NOT NULL,
	[LineItemDay] [int] NOT NULL,
	[LineItemYear] [int] NOT NULL,
	[Product] [nvarchar](256) NULL,
	[ResourceGuid] [nvarchar](256) NULL,
	[Service] [nvarchar](256) NULL,
	[ServiceType] [nvarchar](256) NULL,
	[ServiceRegion] [nvarchar](256) NULL,
	[ServiceResource] [nvarchar](256) NULL,
	[ResourceQtyConsumed] [nvarchar](256) NULL,
	[ServiceSubRegion] [nvarchar](256) NULL,
	[ServiceInfo] [nvarchar](256) NULL,
	[Component] [nvarchar](256) NULL,
	[ServiceInfo1] [nvarchar](256) NULL,
	[ServiceInfo2] [nvarchar](256) NULL,
	[AdditionalInfo] [nvarchar](256) NULL,
	[Tags] [nvarchar](256) NULL,
	[DepartmentName] [nvarchar](256) NULL,
	[CostCentre] [nvarchar](256) NULL,
	[InsertedDate] [datetime] NOT NULL,
	[AzureResourceId] [int] NULL,
	[ExtendedCost] [nvarchar](256) NULL,
	[ResourceRate] [nvarchar](256) NULL,
	[ExtendedCostAmount] [float] NULL,
	[ResourceRateAmount] [float] NULL,
 CONSTRAINT [PK_AzureBillingLines] PRIMARY KEY CLUSTERED 
(
	[LineID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

GO
/****** Object:  Table [dbo].[AzureBillingMonths]    Script Date: 4/21/2015 10:56:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AzureBillingMonths](
	[AzureMonthID] [int] IDENTITY(1,1) NOT NULL,
	[MonthAndYear] [nvarchar](50) NOT NULL,
	[CompletedDate] [datetime] NOT NULL,
	[IsComplete] [bit] NOT NULL,
 CONSTRAINT [PK_AzureBillingMonths] PRIMARY KEY CLUSTERED 
(
	[AzureMonthID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

GO
/****** Object:  Table [dbo].[AzureResources]    Script Date: 4/21/2015 10:56:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AzureResources](
	[ResourceID] [int] IDENTITY(1,1) NOT NULL,
	[Service] [nvarchar](256) NULL,
	[ServiceType] [nvarchar](256) NULL,
	[ServiceResource] [nvarchar](256) NULL,
	[ServiceInfo] [nvarchar](256) NULL,
	[Component] [nvarchar](256) NULL,
	[ServiceInfo1] [nvarchar](256) NULL,
	[ServiceInfo2] [nvarchar](256) NULL,
	[ResourceKey] [nvarchar](2048) NULL,
	[InsertedDate] [datetime] NOT NULL,
	[AzureSubscriptionID] [int] NULL,
 CONSTRAINT [PK_AzureResources] PRIMARY KEY CLUSTERED 
(
	[ResourceID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

GO
/****** Object:  Table [dbo].[AzureSubscriptions]    Script Date: 4/21/2015 10:56:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AzureSubscriptions](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SubscriptionID] [nvarchar](256) NOT NULL,
	[SubscriptionGuid] [nvarchar](256) NOT NULL,
	[SubscriptionName] [nvarchar](256) NOT NULL,
	[AzureAccountID] [int] NOT NULL,
 CONSTRAINT [PK_AzureSubscriptions] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

GO
/****** Object:  Table [dbo].[JobRuns]    Script Date: 4/21/2015 10:56:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[JobRuns](
	[JobID] [int] IDENTITY(1,1) NOT NULL,
	[RunDate] [datetime] NOT NULL,
 CONSTRAINT [PK_JobRuns] PRIMARY KEY CLUSTERED 
(
	[JobID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

GO
/****** Object:  View [dbo].[FlattenedData]    Script Date: 4/21/2015 10:56:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[FlattenedData]
AS
SELECT        dbo.AzureAccounts.ID AS AzureAccountID, dbo.AzureAccounts.AccountOwnerId, dbo.AzureAccounts.AccountName, dbo.AzureAccounts.ServiceAdministratorId, 
                         dbo.AzureSubscriptions.ID AS AzureSubscriptionID, dbo.AzureSubscriptions.SubscriptionID, dbo.AzureSubscriptions.SubscriptionGuid, 
                         dbo.AzureSubscriptions.SubscriptionName, dbo.AzureResources.ResourceID AS AzureResourceID, dbo.AzureResources.Service, 
                         dbo.AzureResources.ServiceType, dbo.AzureResources.ServiceResource, dbo.AzureResources.ServiceInfo, dbo.AzureResources.Component, 
                         dbo.AzureResources.ServiceInfo1, dbo.AzureResources.ServiceInfo2, dbo.AzureResources.ResourceKey, dbo.AzureResources.InsertedDate, 
                         dbo.AzureBillingLines.LineID AS BillingLineID, dbo.AzureBillingLines.LineKey, dbo.AzureBillingLines.LineItemDate, dbo.AzureBillingLines.LineItemMonth, 
                         dbo.AzureBillingLines.LineItemDay, dbo.AzureBillingLines.LineItemYear, dbo.AzureBillingLines.Product, dbo.AzureBillingLines.ResourceGuid, 
                         dbo.AzureBillingLines.ServiceRegion, dbo.AzureBillingLines.ResourceQtyConsumed, dbo.AzureBillingLines.ServiceSubRegion, 
                         dbo.AzureBillingLines.AdditionalInfo, dbo.AzureBillingLines.Tags, dbo.AzureBillingLines.DepartmentName, dbo.AzureBillingLines.CostCentre, 
                         dbo.AzureBillingLines.ExtendedCost, dbo.AzureBillingLines.ResourceRate, dbo.AzureBillingLines.ExtendedCostAmount, 
                         dbo.AzureBillingLines.ResourceRateAmount
FROM            dbo.AzureSubscriptions INNER JOIN
                         dbo.AzureAccounts ON dbo.AzureSubscriptions.AzureAccountID = dbo.AzureAccounts.ID INNER JOIN
                         dbo.AzureResources ON dbo.AzureSubscriptions.ID = dbo.AzureResources.AzureSubscriptionID INNER JOIN
                         dbo.AzureBillingLines ON dbo.AzureResources.ResourceID = dbo.AzureBillingLines.AzureResourceId

GO
/****** Object:  View [dbo].[ResourcesByLifetimeCost]    Script Date: 4/21/2015 10:56:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ResourcesByLifetimeCost]
AS
SELECT        TOP (100) PERCENT SubscriptionName, SubscriptionGuid, AzureResourceID, Service, ServiceType, ServiceResource, ServiceInfo, Component, 
                         SUM(ExtendedCostAmount) AS TotalCost, SUM(ResourceRateAmount) AS ResourceRate
FROM            dbo.FlattenedData
GROUP BY AzureResourceID, Service, ServiceType, ServiceResource, ServiceInfo, Component, ServiceInfo1, ServiceInfo2, SubscriptionName, SubscriptionGuid
ORDER BY TotalCost DESC

GO
/****** Object:  View [dbo].[SubscriptionLifetimeCosts]    Script Date: 4/21/2015 10:56:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[SubscriptionLifetimeCosts]
AS
SELECT        TOP (100) PERCENT SubscriptionName, SubscriptionGuid, SUM(ExtendedCostAmount) AS TotalCost
FROM            dbo.FlattenedData
GROUP BY SubscriptionName, SubscriptionGuid
ORDER BY TotalCost DESC

GO
/****** Object:  View [dbo].[SubscriptionCostsByMonth]    Script Date: 4/21/2015 10:56:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[SubscriptionCostsByMonth]
AS
SELECT        TOP (100) PERCENT SubscriptionName, SubscriptionGuid, SUM(ExtendedCostAmount) AS TotalCost, CONVERT(nvarchar(6), LineItemDate, 112) 
                         AS BillingMonth
FROM            dbo.FlattenedData
GROUP BY SubscriptionName, SubscriptionGuid, CONVERT(nvarchar(6), LineItemDate, 112)
ORDER BY TotalCost DESC

GO
/****** Object:  View [dbo].[ResourceCostsByMonth]    Script Date: 4/21/2015 10:56:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ResourceCostsByMonth]
AS
SELECT        TOP (100) PERCENT SubscriptionName, SubscriptionGuid, AzureResourceID, Service, ServiceType, ServiceResource, ServiceInfo, Component, 
                         SUM(ExtendedCostAmount) AS TotalCost, SUM(ResourceRateAmount) AS ResourceRate, CONVERT(nvarchar(6), LineItemDate, 112) AS BillingMonth
FROM            dbo.FlattenedData
GROUP BY AzureResourceID, Service, ServiceType, ServiceResource, ServiceInfo, Component, SubscriptionName, SubscriptionGuid, CONVERT(nvarchar(6), 
                         LineItemDate, 112)
ORDER BY TotalCost DESC

GO
/****** Object:  View [dbo].[ResourceOverview]    Script Date: 4/21/2015 10:56:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ResourceOverview]
AS
SELECT        TOP (100) PERCENT SubscriptionName, SubscriptionGuid, AzureResourceID, Service, ServiceType, ServiceResource, ServiceInfo, Component, 
                         CONVERT(nvarchar(6), LineItemDate, 112) AS BillingMonth, Product, ResourceGuid, ServiceRegion, AdditionalInfo, Tags, DepartmentName, CostCentre, 
                         ServiceInfo1, ServiceInfo2, ResourceKey, AzureAccountID, AccountOwnerId, AccountName, AzureSubscriptionID, SubscriptionID, ServiceAdministratorId
FROM            dbo.FlattenedData
GROUP BY AzureResourceID, Service, ServiceType, ServiceResource, ServiceInfo, Component, SubscriptionName, SubscriptionGuid, CONVERT(nvarchar(6), 
                         LineItemDate, 112), Product, ResourceGuid, ServiceRegion, AdditionalInfo, Tags, DepartmentName, CostCentre, ServiceInfo1, ServiceInfo2, ResourceKey, 
                         AzureAccountID, AccountOwnerId, AccountName, AzureSubscriptionID, SubscriptionID, ServiceAdministratorId
ORDER BY Service, ServiceType, ServiceResource, ServiceInfo, Component

GO
/****** Object:  View [dbo].[RecentlyAddedResources]    Script Date: 4/21/2015 10:56:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[RecentlyAddedResources]
AS
SELECT        dbo.AzureAccounts.AccountOwnerId, dbo.AzureAccounts.AccountName, dbo.AzureSubscriptions.ID, dbo.AzureSubscriptions.SubscriptionID, 
                         dbo.AzureSubscriptions.SubscriptionGuid, dbo.AzureSubscriptions.SubscriptionName, dbo.AzureResources.ResourceID, dbo.AzureResources.Service, 
                         dbo.AzureResources.ServiceType, dbo.AzureResources.ServiceResource, dbo.AzureResources.ServiceInfo, dbo.AzureResources.Component, 
                         dbo.AzureResources.ServiceInfo1, dbo.AzureResources.ServiceInfo2, dbo.AzureResources.ResourceKey, dbo.AzureResources.InsertedDate
FROM            dbo.AzureSubscriptions INNER JOIN
                         dbo.AzureAccounts ON dbo.AzureSubscriptions.AzureAccountID = dbo.AzureAccounts.ID CROSS JOIN
                         dbo.AzureResources
WHERE        (dbo.AzureResources.InsertedDate > DATEADD(dd, - 7, dbo.AzureResources.InsertedDate))

GO
ALTER TABLE [dbo].[AzureBillingLines]  WITH CHECK ADD  CONSTRAINT [FK_AzureBillingLines_AzureResources] FOREIGN KEY([AzureResourceId])
REFERENCES [dbo].[AzureResources] ([ResourceID])
GO
ALTER TABLE [dbo].[AzureBillingLines] CHECK CONSTRAINT [FK_AzureBillingLines_AzureResources]
GO
ALTER TABLE [dbo].[AzureResources]  WITH CHECK ADD  CONSTRAINT [FK_AzureResources_AzureSubscriptions] FOREIGN KEY([AzureSubscriptionID])
REFERENCES [dbo].[AzureSubscriptions] ([ID])
GO
ALTER TABLE [dbo].[AzureResources] CHECK CONSTRAINT [FK_AzureResources_AzureSubscriptions]
GO
ALTER TABLE [dbo].[AzureSubscriptions]  WITH CHECK ADD  CONSTRAINT [FK_AzureSubscriptions_AzureAccounts] FOREIGN KEY([AzureAccountID])
REFERENCES [dbo].[AzureAccounts] ([ID])
GO
ALTER TABLE [dbo].[AzureSubscriptions] CHECK CONSTRAINT [FK_AzureSubscriptions_AzureAccounts]
GO
/****** Object:  StoredProcedure [dbo].[DeleteData]    Script Date: 4/21/2015 10:56:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[DeleteData] 
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Delete From AzureBillingMonths
	Delete From AzureBillingLines
	Delete From AzureResources
	Delete From AzureSubscriptions
	Delete From AzureAccounts
END

GO

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 runable (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 ones that it has already processed.  It will also at the end of the month mark a month as fully loaded so that it doesnt 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-ran 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 hadnt got around to learning how to use Power BI properly.  With the new Power BI Service it seemed simpler.

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.  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:

Dashboard

 

 

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.

Azure Lifetime Spend

 

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 werent using and also so we could ensure that we could get resources at the right scale so we didnt 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.

Spend By Day

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 hadnt realized was costing what it was.  You can see how this was brought back into a better place.

Spend By Day By Subscription

 

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.

Monthly Spend by Resource Type

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-y 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

  • Magnus Tjerneld

    Great blog! Now there’s an easier (but less leet) way, in case you or any visitor missed it:
    https://support.powerbi.com/knowledgebase/articles/755976-microsoft-azure-enterprise-content-pack-for-power

  • Sathyan Narasingh

    other than EA number and API Key do we need to configure anything in Azure AAD

  • Sathyan Narasingh

    Great Blog. I am using your sample. only issue we see is it is missing some data in DB.
    For example for a month we are expecting 1000 lines of usage data the table stores only 800 lines. Not sure why it is losing some data to save.

  • Simon Estrada

    Hello! First, thanks a lot for your blog…I’m learning a lot! I’d like to ask kyou about the next error. I made the step 1 and in the step two I pasted the Enrolment key, the APIKey and the Connection String but when I run the app I got the next:
    In line 33 from Program.cs if (highLevelUsageData.AvailableMonths != null) a window appear and it says:

    An unhandled exception of type ‘System.NullReferenceException’ occurred in AppFx.AzureEA.Billing.DataProcessor.exe

    Please advice. Regards,

  • Steve Borgwardt

    This is a very helpful blog post. Just wondering if you have tested this solution recently against the EA site? When running this as console app with the config file setup with enrollment # and API Key and SQL conn, I am getting null reference exception on the GetResponse method called from GetHighLevelData(). Any ideas? Thanks in advance.

    • Steve Borgwardt

      For anyone with the same issue, i found the api-version header changed in the preview API. Update the APISecurity.cs class in the AddHeaders method and replace this line: request.Headers.Add(“api-version”, “2015-06-01-preview”);

      • MikeStephensonUK

        thanks for spotting and fixing this steve much appreciated

  • Vinski

    Thanks for posting this, excellent information!

    I had a bunch of errors which required some fixing. First for some reason in the detailed month data, there was a short line with only mac style line break r and no data, so parsing data failed. Second problem was with format differences, date conversion (dd/mm/yyyy vs. mm/dd/yyyy) and double conversion (0.01234 vs 0,01234) needed some fiddling.

    Also for some reason the data is only returned from the last 6 months, but EA still reports that data starts from 2015-05, so asking the detailed data from those months failed.

  • havi

    This is a very helpful blog post. I’ve a error with collecting the data (the response: “/,/”), I got error on those lines:
    var b = new BillingDetailLineItem();
    b.AccountOwnerId = items[0].FormatBillingLineItem();

    this is the error: An unhandled exception of type ‘System.IndexOutOfRangeException’ occurred in AppFx.AzureEA.Billing.DataProcessor.exe
    Additional information: Index was outside the bounds of the array.

    Please advice. Regards,

  • Nalith

    Thanks for posting this, it is exactly what I am looking for however it seems to be giving run-time errors at the moment, do you possibly have a never working version? I have changed the API version as below however it still seems not to be getting data returned properly. This is the error I am getting “An unhandled exception of type ‘System.IndexOutOfRangeException’ occurred in AppFx.AzureEA.Billing.DataProcessor.exe”

    • michael stephenson

      hi

      I dont really use this now since the power bi content pack is available. Its probably that the billing api has changed since this sample was created and the data structure is different.

      From what i remember, Im not sure that there is anything the tool did that cant be done with the content pack now

  • Nalith

    Thanks for posting this, it is exactly what I am looking for however it seems to be giving run-time errors at the moment, do you possibly have a never working version? I have changed the API version as below however it still seems not to be getting data returned properly. This is the error I am getting “An unhandled exception of type ‘System.IndexOutOfRangeException’ occurred in AppFx.AzureEA.Billing.DataProcessor.exe”