PowerBI, DAX and the Azure EA billing API
More fun this morning with grappling DAX to pull out the data I need.
We write multiple tags to Azure resources – from the resource group down to anything that takes them. Makes showback, accountability, support so much cleaner.
The tags are returned, in a non deterministic order, via the billing API – and look like this (data cleaned up), a list of tag/values. JSON-like ish kinda.
“application“: “Shared SQL“,”project“: “Shared Database Engineering“,”costcenter“: “1234“,”itowner“: “Buckley, Martin“,”supportgroup“: “Database Engineering“,”businessowner“: “Buckley, Martin“
I was looking at some DAX to pull this apart; PowerBI has a native “extract text between delimiters” function. It’s not going to build a tree of arbitary values; but I can now cleanly extract the tags I need to build this datamodel.
After I get this working, it might be time to pull all of the discrete DAX steps together to make it more efficient against a vast dataset.
Fun times on a Saturday.
Follow up to this. Extracting multiple pieces of insight from the tags – that drive KPIs for service improvement:
– resource groups with no costcenter tag (this is the cardinal sin – no costcenter and showback isn’t there, getting to health on this is easy)
– resource groups where itowner or businessowner don’t meet business rules (ICs can’t be owners)
– resource groups where the supportgroup is not valid (it doesn’t match a ServiceNow Assigment group)
Most of all – I can find everything tagged with “placeholder” or “set by policy” and sweep that broom through the tagging.
All in all – a good few hours of work.