Analyzing Azure EA Consumption Using Power BI – Part 1


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.

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

AzConInghDsselection

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]),
InsertMonthName = Table.AddColumn(InsertDayInt, "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)

in
InsertWeekEnding
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

dateKeytable

Once we have the DateKey table defined we need to setup relationship between usage and DateKey so we can apply time intelligence.Our relationship will be between Date column in DateKey table to Date column in Usage 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

NewDatatable

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

SummaryPage1


Azure Resource Inventory

Inventory1

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

Cost by Service

costbyService

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.

sampleDrilldown


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

DrilltroNav

Sample Details Dashboard

DetailsbyService

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.

EnrollmentId 

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

enrollmentidusage

Specify your Account Key and Connect

enrollment3 

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…

Comments (0)

Skip to main content