Stop exporting your Cost Management data to Azure SQL Database
Why should I stop exporting my Cost Management export to Azure SQL Database? It took me ages to setup that horrible ADF pipeline!
I know, it’s sad, but I give 3 valid reasons:
— The Cost Management export doesn’t contains relational data. That’s why you shouldn’t export it to an RDBMS in the first place. You should maybe export it to a NoSQL database or, even better (and cheaper), to an Azure Table Storage.
— If you want to query the Cost Management the right way to do it is to use the Azure Cost Management connector in Power BI Desktop
— One day you will change your Microsoft Azure Consumption Commitment (MACC) , your Billing Account ID will change and your ADF pipeline or your Azure Cost Management connector in Power BI Desktop will not work neither.
So what do you propose as an alternative?
Just export your Cost Management to Azure Blob Storage and query it through Power BI:
Naaa… it cannot be that easy
Yes. And it’s also faster than your ADF pipeline or your Azure Cost Management connector.
Plus if you want to add more months or remove months or swapping the Actual Cost with Amortized Costs you just have to add/remove the .CSV
And you can have daily fresh data if you use a bit of Logic App.
OK, show me how you do it
First of all you need to create these Exports:
I like to keep both monthly Actual Costs and Amortized Costs. So I can use whatever I want.
Keep the One Time Export in case.
And create the Month to date Export if you want to have daily data refresh.
And it’s a good practice to separate the Actual Costs from the Amortized Costs in different containers:
So far so good. Then what?
You are done.
Click on the 3 dots of the Container and go to Properties, here you will find the Blob Storage URL.
Copy that URL and paste it into the Power BI prompt:
Then Power BI will load all your CSVs.
And you can query them, as long as they have the same columns. Power BI will merge them and treat them like a unique big file.
It’s a kind of magic.
I’m speechless
I know, I was as well.
In the end a NoSQL database is a search engine that digs into big JSON, CSV, TXT, etc… Here we are doing the same. But it costs a fraction of a CosmoDB. And there is no ADF pipeline to maintain.
I want to cry
Before your eyes get wet go to Transform Data and you will see the full list of the CSVs. Now click on the two down arrows of the column Content:
And see how Power Query merges all the CSVs in seconds:
Why no one told me that before?
Yeah, I don’t know why.
Just re-post this and share the love.
Anything else I need to be aware of?
Ah, right. Troubleshooting.
— The CSV I had were exported by Microsoft. They might have a bug somewhere and I found out that the column resourceID was sometimes named resourceID but sometimes ResourceID. This was creating empty lines and strange calculations in Power BI. Rename all columns with the same header and you are good to go.
— If you want to have a scheduled monthly refresh don’t forget to use the Key as Authentication method:
If you use Anonymous, OAuth2 or Shared Access Signature (SAS) the refresh will fail.
And for the daily refresh?
I will write a new post about that.