Azure FinOps :: Connect Power BI to Azure Cost Management

Azure Cost Management notoriously slooow…

Instead what you want to do is to query the Azure Portal in real time.

Microsoft is providing Azure Cost Management connector in Power BI Desktop and in the official guide there are 3 ways to connect to it:

— using your billing profile id (it doesn’t work)

— using the Enrollment Number (only if you have an Enterprise Agreement and it will soon be retired)

— using the Manually Input scope (which is the only one that really works)

Connect to a Microsoft Customer Agreement account… if you can
In order to use the 3rd and only option you need to grab your:

— Billing profile ID

— Billing account ID

Now that you have them set the Scope Identifilre as such:

/providers/Microsoft.Billing/billingAccounts/{billingAccountId}/billingProfiles/{billingProfileId}

And set the Number of months to 3. If you set more than that the Power Query will mostly fail.

Don’t set Start Date or End Date, it will just make things worst.

Now you can wait a good 10 minutes, especially if you have a lot of resources. The table you are looking for is called “Usage details” and it contains the most juicy data you are going to use.

Publish, share and refresh

Now that you have pulled the data you are ready to create your dashboards and share it within your organization.

Publishing in Power BI is a smart move for 2 reasons:

1) If you fill up your credentials and schedule an automatic refresh you will have daily data that you can query at light bolt speed. Not like the Azure Portal.

Now that we have these dashboard we are not only using it for FinOps but to run any investigation about our resources on the cloud.

2) Now that the dataset is published on the Data Hub you colleagues don’t need to pull the data again but they can query that same dataset directly from their Power BI and pull the data.

But I want to pull more than 3 months…

Bet you do, you freaky old FinOps, you.

Go into Usage details > Edit query > Advanced Editor, and change this number after the URL form 3 to whatever the number of months that you want to pull.

Now if you save and publish again the Power BI online template will refresh the number of month you have set.

Keep in mind that I would not pull more than 12 months as Data row requests exceeding one million rows isn’t supported by Power BI.

But I want to pull more than 12 months…

In this case there is no other alternative than exporting data through CSV directly from the Azure Portal.

And then setting up an ETL process that ingest the data to whatever database you want.