Monthly Archives: July 2018

Working with Azure tags in Power BI

Using tags in Azure is a great way to logically organise resources by categories.  Combining tags with the Power BI Microsoft Azure Consumption Insights connector should offer a powerful way to analyse and gain insights into your Azure expenditure.

AzurePowerBI1.png

Unfortunately, the connector imports the tags as a single field containing the tagging JSON, which makes it difficult to work with the tags in a meaningful way.  For example, if you have tags called System, Geography and Environment it would be great to be able to work with these in Power BI as an individual fields.  Fortunately, this is possible, but not with Power BI in the browser.

Download Power BI Desktop  and get data from the Microsoft Azure Consumption Insights (Beta).

AzurePowerBI2

Enter your EA enrollment number, which you can get from https://ea.azure.com/ .  You’ll also need an API access key, which is available from https://ea.azure.com/ under reports -> download usage -> API access key.

Select UsageDetails, then right-click and select edit.

AzurePowerBI3

If you’ve only recently added tags order the data by date descending.  This is because the Power BI editor loads 1000 rows initially and if you’ve only recently started using tags they’ll only be listed against recent records.

Find the Tags column, right-click choose Transform, followed by JSON.

AzurePowerBI4

Power BI will now parse the JSON and enable you to split out each tag into a separate column.  Click on the arrows and the tag fields will be loaded.

AzurePowerBI5

AzurePowerBI6

Click ok to add the new tag fields to the data set, followed by close & apply.

AzurePowerBI7

You can now work with each tag individually.

AzurePowerBI8

Advertisements

Mirrored SQL Databases Stuck In Recovery Mode

Following a server restart, two of three SQL Server databases in a SQL Server Database Mirroring configuration were stuck in recovery mode on the primary server.

Running sys.dm_tran_locks showed a session with a resource_database_id for each of the databases stuck in recovery.

It appeared that SQL Server Database Mirroring was preventing the recovery process completing on the two databases following the server restart.

The resolution was to stop and start the database mirroring endpoints on the primary and secondary SQL Servers.  To return a list of endpoints run select * from sys.endpoints.

To stop and restart the endpoints run

ALTER ENDPOINT %ENDPOINT NAME% STATE=STOPPED

ALTER ENDPOINT %ENDPOINT NAME% STATE=STARTED