Analyzing Azure EA Consumption Using Power BI – Part 1 Setting Up Data

Analyzing the usage and tuning resources is a key responsibility in Cloud Management. We need to understand where we spent , what are the trends and where we can tune our spending. When it comes to analyzing Azure usage Microsoft offers different tool set with different capabilities;

• Cloudyn
• New Azure Cost Management
• Azure Consumption API Connector for Power BI

In this blog post I would get into details of using Consumption API to bring usage metrics into Power BI and we will be using Power BI capabilities for analyzing the data. Power BI gives you the most flexibility when it comes to reporting and customization. In this  series I will get into Azure Inventory and Usage Analysis, utilizing tags, MoM /YoY analysis, distributing the cost between departments / sub companies , custom charge back options, combining consumption data with app usage and resource performance etc.

For initial setup and Power BI template check Part 1

For extracting tags and bring them into Usage Analysis check Part 2

For  Advanced Calculations (MoM/Cumulative Usage) check  Part3

 

Disclaimer :I’m not a Power BI expert. This work is mainly collection of exercises we have gone through with our customers. There are multiple ways of achieving the results in Power BI and these are the ones we have seem to make this work……

Using Azure Consumption Insights in Power BI Desktop

Since connecting data using new connector is well described in Azure Documentation, I will not get into details on how to connect. Please follow steps from

https://docs.microsoft.com/en-us/power-bi/desktop-connect-azure-consumption-insights  to connect your usage data.

When connecting to consumption insights I prefer to bring ;

1. Marketplace

2. PriceSheets

3. Summaries data with the connector

To get usage details I start with Get Data / Blank Query and use Advanced  Editor. This approach lets me to specify the number of months for usage data. Here in this sample I will bring last 3 months usage for the enrollment 100.

let

enrollmentNumber = “100”,

optionalParameters = [ numberOfMonth = 3, dataType=”DetailCharges” ],

data = MicrosoftAzureConsumptionInsights.Contents(enrollmentNumber, optionalParameters)

in

data

Finally I will rename this table to “Usage”

Power BI will ask for Enrollment Key and you are all set to download usage details into Power BI. Please note that depending on number of months to fetch , downloading all data might take a while.

Do not forget to save your work otherwise you might end up downloading the data again in case of a problem!

Shaping Data Cost by Subs/Resource Type/Date

Date Table

When we bring EA usage data into Power BI we first need to do a bit of modeling to be able to get most out of it. One of the first things is to bring a Date table. This table will help us with time intelligence in our calculations and help with filtering data from multiple  sources like log analytics / custom data.

Here I prefer to utilize a dynamic date function from https://gist.github.com/philbritton/9677152

By using this function we will create a dynamic date table covering the the dates in our Summaries Table using BillingMonth column.

Here is the M Query to generate dynamic date table;

let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate – StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{“Column1”, type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{“Column1”, “Date”}}),
InsertYear = Table.AddColumn(RenamedColumns, “Year”, each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, “QuarterOfYear”, each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, “MonthOfYear”, each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, “DayOfMonth”, each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, “DateInt”, each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertBillMonthInt = Table.AddColumn(InsertDayInt, “BillingMonth”, each [Year] * 100 + [MonthOfYear] ),
InsertMonthName = Table.AddColumn(InsertBillMonthInt, “MonthName”, each Date.ToText([Date], “MMMM”, Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, “MonthInCalendar”, each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & ” ” & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, “QuarterInCalendar”, each “Q” & Number.ToText([QuarterOfYear]) & ” ” & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, “DayInWeek”, each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, “DayOfWeekName”, each Date.ToText([Date], “dddd”, Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, “WeekEnding”, each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, “WeekNumber”, each Date.WeekOfYear([Date]))
in
InsertWeekNumber

in
CreateDateTable

To create the Date table ;

• Step 1 – Date Function :

  First start with Get Data/Blank query , go to advanced query and  paste  the the data function you copied above. When done rename it to “Dates Query”.

• Step 2 – DateKey Table :

  For the datekey table we will use the date function (Dates Query) we just defined to generate a dynamic date table. Again we start with Get Data/Blank Query/ Advanced Query Editor. Definition of the DateKey Table will be

let

Source = #”Dates Query”(Date.FromText(List.Min(Usage[Date])),DateTime.Date(DateTime.LocalNow()))

in

Source

Once we have the DateKey table defined we need to setup relationship between usage and DateKey so we can apply time intelligence.Our first relationship will be between Date column in DateKey table to Date column in Usage table, second one will be between BillingMonth column in DateKey to BillingMonthId in Summaries table.

 

Calculated Columns

We will extend our Usage table with 2 new columns. Right click to Usage table and select New Column;

Resource Type we will extract resource type from Instance ID.

Resource Type = PATHITEM(SUBSTITUTE(Usage[Instance ID], “/”, “|”), 8)

Resource Name we will extract resource name from Instance ID

Resource Name = PATHITEMREVERSE(SUBSTITUTE(Usage[Instance ID], “/”, “|”), 1)

Measure Definitions

We will define some measures to be used in our calculations. For ease of access we will sore them under measures table _MyMeasures.

To create new table empty table by select Enter Data and rename table to _MyMeasures

Now lets add a few measures by Right click on _myMeasures select new measure and paste the following formulas one by one ;

Total Resource Count

TotalResources = DISTINCTCOUNT(Usage[InstanceId])

Here we refer to 2 new column we created in previous section

Virtual Machine Count

VMCount = CALCULATE(

DISTINCTCOUNT(Usage[Resource Name]),

FILTER(Usage,Usage[Resource Type]=”virtualMachines”))

SQL Instance Count

SQLInstanceCount = CALCULATE(

DISTINCTCOUNT(Usage[Resource Name]),

FILTER(Usage,Usage[Resource Type]=”servers”&&Usage[Consumed Service]=”Microsoft.Sql”))

Dashboards Summary / Inventory / Cost by Service

Now we can use the data we have to visualize the consumption for our EA enrollment.

My summary dashboard

Azure Resource Inventory

Kudos to my colleague  Marcel Keller for providing samples for   Inventory View and extracting Resource Name …

Cost by Service

You can find the template link  at the end of the blog post.

Enable drillthrough for Consumption data

We can utilize Power BI drill down feature to navigate to usage  details / meter details to analyze the data further in detail.

First create a new page and select Consumed Service as Drillthrough filter

and select one of the services to enable drill down.

Now we can go back to CostbyService dashboard select any Service and Right click / Drillthrough / Details by Service

Sample Details Dashboard

Using the same we can create detail pages focusing different elements of Azure Usage .

Download Template

Please note that after you open the template you need to cancel data refresh, go to Edit Queries  and change data source for Market

Place/Pricesheets/Summaries  select Advanced Editor  and change the highlighted enrollment number (100) to your own enrollment id.

Also for Usage data  , go to Advanced Editor and change highlighted enrollment number to your own.

Specify your Account Key and Connect

Template should populate with your usage data after data refresh is completed.

Final Comments for Dashboards

• Add more slicers to filter the data by Date, Subscription, Location or by Service
• Add more drillthrough dashboards to control user experience
• Bring in usage / performance from Log Analytics to correlate with consumption

In Part 2 I will be  focusing on utilizing Azure Tags for usage analysis…