Select Page

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.


Maintenance night – SLES 12 SP5 updates on Azure.

zypper patch-check

zypper migration

Nice and easy.

Side note – if products somehow get unregistered:

zypper pd

SUSEConnect -p sle-module-legacy/12/x86_64

To the cloud!

The never ending saga of running infrastructure has gone full circle. All of my self-hosted blogs and websites have moved from a Hyper-V server in my garage and up to Microsoft Windows Azure.

I spent a decade (2001-2011) self-hosting. Running both email and website on platforms as diverse as NetWare through RedHat, SUSE and finally Windows Server. In early 2011 I decided to get out of the hosting game and move the blogs to GoDaddy and the mail to Exchange Online (then BPOS, now Office 365).

GoDaddy really frustrated me. Poor performance, poor logging, strange “go slows” and unexpected disconnects of SSH. Understandably “I got what I paid for” – but frustrating none the less.

It has taken eighteen months – but slowly the various blogs and websites have been culled and archived, moved back to self-host and now up to Azure.

Windows Azure has really improved over the past two years – I started by evaluating the Platform as a Service capabilities back in November 2011. Functional – but missing some capabilities that I needed to run the photo blogs (all written for the LAMP stack).

More recently as the Infrastructure as a Service capabilities have moved from preview to production I tested this blog running on Azure. Performance and uptime have been great. The blog is running a SLES 11 SP3 platform on Azure – and it’s as functional as running on bare metal or any hypervisor.

I finally took the time to move the family blogs and business website to the platform over the last week or so. The migration of WordPress blogs and Gallery photo sites is pretty simple now.

.. the more things stay the same

Back to self hosting.


The blog has moved from home, to hosting at GoDaddy and up to Azure.

All had advantages – all had downsides. It’s the private cloud/public cloud conversation in a nutshell.

Ultimately GoDaddy performance let it down – especially for the database – was unacceptable. Their support was also pretty poor. As always “you get what you pay for” – but the bottlenecks for even simple, near static, WordPress sites were unacceptable.

Azure has a lot going for it – I am still keeping my eye on future features that are currently in beta. Performance was incredible; the process of getting apps updated was a little too cumbersome for me.

Self hosting really requires me to get dirty with the infrastructure and tuning – but the fact that I can lets me drive the performance. I’m also responsible for everything below the app – hardware, storage, network, connectivity, OS, security etc etc.