Design a site like this with
Get started

Azure Built in Tagging Policy. Deny the Creation of a Resource group

Deny the creation of a resource group without applicationName Tag

Every resource group should have an application name because Resource Groups should be Application Related. However you cant get the applicationName from the Above Subscription.

As a Example, the Resource Group is for HR Performance.

applicationName Tag Configuration

  • Deny the resource group if its missing the applicationName tag

Steps to apply this policy in Azure

Search for policy and Assign

Go to Assignments. This shows you all the policies or Initiatives that are in place. The Scope for this shows that the all subscriptions are being shown in this list.

Click Assign Policy

Set Scope of Policy

First thing you need to do is set the Scope. We have Management groups in place and as a test, a Personal Subscription is being used

The Resource Group isn’t set because this is just being set at Subscription level

Exclude Resource

You can also exclude resource, and in this case resources from the above Policy. However for this policy we don’t need to do this.

Set the Policy Definition.

There are two types of definitions. Custom and built in

If you look for ‘Tag’ you get a list of the Built in Definitions for tags.

We know that we want to ‘Require a tag on resource groups’ so this built in policy can be selected

Choose the Built in policy and Select

The Assignment Name has been slightly changed to include the tag this will be set for

We are in the Basics tab at the moment


Go to the Parameters Tab.


Click the Remediation Tab

In order to understand the Remediation tab there are a few specific concepts to Understand

Managed Identity

Managed identities is a more secure authentication method for Azure cloud services that allows only authorized managedidentity-enabled virtual machines to access your Azure subscription.

This means that any service that ‘does something’ to another service can do so automatically using a Managed Identity

DeployIfNotExists and Modify effects

Every single Policy in Azure Policy has a single effect. Effects behave differently if they are for a new, updated or existing resource. The Effects supported are:

  • Append
  • Audit
  • AuditIfNotExists
  • Deny
  • DeployIfNotExists
  • Disabled
  • EnforceOPAConstraint (preview)
  • EnforceRegoPolicy (preview)
  • Modify

The question is. What is the effect of Require a tag on resource groups


Go to Definitions in Policy and Search for the Built in Policy.

The effect of your chosen built in Policy the effect is to deny .

You can also see the JSON Definition of the chosen Policy

Because the Policy is deny we don’t need to set up a Managed Identity

Click Review + Create

And Click Create


Going back to the policy Definition

You can see that this Policy has been assigned to a Subscription

Checking your Policy has worked

To test the policy create a new resource Group within the selected Subscription without adding any tags

Resource ‘Test-RG’ was disallowed by policy. Policy identifiers: ‘[{“policyAssignment”:{“name”:”Require tag applicationName on resource groups”,”id”:”/subscriptions/7e7aeec9-7cb0-4963-a029-128c29530b5f/providers/Microsoft.Authorization/policyAssignments/febb7d3199864e4ab84411f5″},”policyDefinition”:{“name”:”Require a tag on resource groups”,”id”:”/providers/Microsoft.Authorization/policyDefinitions/96670d01-0a4d-4649-9c89-2d3abc0a5025″}}]’.

Because the Name of the required tag was added into the Policy name, the warning lets you know which tag you need to set.

You can Implement this on any resource group that needs a tag setting when it cant inherit from anything above (Like Subscriptions)


Azure Use Policies for Tagging Introduction

When you get started with Azure, you want to ensure that your resource groups and resources are tagged. this is great for Management of your Azure Subscriptions/

You can categorise all your resources with tags. You can also view Billing by Tags.

When you add a tag into the Portal (Or programatically) you can add in any tag name as you go. Once created they come up in a list and you can choose from them again.

You can use Azure Policies to try and Enforce some Tagging Logic

Azure Policies

Policies allow you to use the built in or custom definitions. the Policies can then be assigned to a Management group, Resource Group or Subscription

Policies are executed when new resources are created. old resources may not be compliant

You can use built in tagging Policies that are already defined within definitions in JSON (Built in)

Or you can add a new definition by adding your own JSON or copying and amending a built in Policy (Custom)

We are going to have a look at some specific examples of how to use Policies for Tagging.


Azure Tagging with Powershell Example

Azure uses Tagging for easier management. Tagging is done with Key value pairs. You can create the manually in the Azure Portal but you can also do it programatically via Powershell (For Example)

For this example Log onto the Azure Portal, Open Cloud Shell (This is the new Azure Portal, Not classic)

And set it to run with powershell

Just to get warmed up, lets list all our Resource groups


You can right click in powershell and paste the code above. then click Enter

This gives you a list of all your resource Groups

You even get a list of tags for each resource group with the above command

However, the resource groups that are available to me currently are the ones on my personal visual studio subscription. the Current Subscription

Lets see what subscriptions we have available


You get the Subscription Name, ID and TenantID

Lets find out the current Subscription Context


You can also use the following command to get subscription details


Now we want to change the current Subscription context. this example is going to look at a resource group and a resource in our Proof Of Concept Subscription

Set-AzContext -SubscriptionId "xxxx-xxxx-xxxx-xxxx"

The ID can be generated by using Get-AzureRMSubscription

And running Get-AzureRmContext again, provides you with confirmation that you have changed subscriptions

Now lets have a look at all the resource groups within this subscription with Get-AzResourceGroup again

These are the two resource groups we are interested in. POC has no tags and training Resources have a couple of tags at resource group Level.

Get-AzureRmResource | ft

This scrips allows you to get a list of all the resources and Resource groups within the current subscription

What I want to do now is check all the tags on the resource Group

(Get-AzResource -ResourceGroupName POC_Resource_Group).Tags

This function allows us to see every tag at resource level against the Resource group. So this script doesnt quite give us what we want. We know that there are no tags at resource group level. How do we establish this with Powershell?

$resourceGroup = Get-AzResourceGroup -Name POC_Resource_Group  
Get-AzTag -ResourceId $resourceGroup.ResourceId

This script allows us so see the tags on the Resource Group.

It would now be useful to get a list of Resources in the resource Group

Get-AzureRmResource -ResourceGroupName POC_Resource_Group | ft

So now we can see every resource in the Resource group

Next step is to have a look at tags per resource

(Get-AzureRmResource -Name "AdventureWorksDW").Tags

We know what our Resources are called now (See Above) so we can get specific tags of a resource group

Scripts to Add tags to a Resource Group

It would be really good to have the following tag at resource group level

  • businessProcess, Proof of Concept
  • environmentType, Dev
  • client, na
  • billingCategory, Internal Analytics

Lets have a go with powershell

$tags = @{"businessProcesst"="Proof of Concept"; "environmentType"="Dev"; "client"="na"; "billingCategory"="Internal Analytics"}
$resourceGroup = Get-AzResourceGroup -Name POC_Resource_GroupNew-AzTag -ResourceId 
$resourceGroup.ResourceId -tag $tags

The resource group now has Tags

Scripts to add tags to a resource

Completely update the List of Tags

We also want to add a tag to a resource.

If you were to use the following script

$tags = @{"costCentre"="000000"} $resource = Get-AzResource -Name AdventureWorksDW -ResourceGroup POC_Resource_Group  
New-AzTag -ResourceId $ -Tag $tags 

And then run (Get-AzureRmResource -Name “AdventureWorksDW”).Tags to look at the tags, this script deletes all the tags and inserts just the one, this isn’t the logic we want to use. We want to Add tags to a Resource that already has tags

Add Tags to a resource that already has tags

$resource = Get-AzResource -Name AdventureWorksDW -ResourceGroup POC_Resource_Group 
$tags = @{"costCentre"="000000"} 
Update-AzTag -ResourceId $ -Tag $tags -Operation Merge

Instead of New, Update has been used. and we gave the script a resource name and a resource group

(Get-AzureRmResource -Name “AdventureWorksDW”).Tags shows that the new tag has been added to the previous tags

Amend a Tag

After running (Get-AzureRmResource -Name “AdventureWorksDW”).Tags, adventureworkks is a value that has been inputted incorrectly and needs amending.

$resource = Get-AzResource -Name AdventureWorksDW -ResourceGroup POC_Resource_Group 
$tags = @{"applicationName"="adventureworks"} 
Update-AzTag -ResourceId $ -Tag $tags -Operation Merge

Great. The incorrect value has been updated

Get Tags

a couple more really useful Scripts in regards to tags


This script allows you to see all your tags and how many times they are used within the subscription

From the above script we can see that businessProcesst is a tag that has been added which needs amending to businessProcess (Its been incorrectly typed in)

Get more information about the Tag (Tag Values and Count of Use)

Get-AzureRmTag -Name "environmentType"

This script is great for looking at the values in a specific tag. And in this case we want to know more about businessProcesst

Get-AzureRmTag -Name "businessProcesst"

And to really tie it down we want to know what resource or resource Group it belongs too?

You can run the following Script to get the Specific resource Group for the tag

(Get-AzResourcegroup -Tag @{ “businessProcesst"="Proof of Concept"}).ResourceGroupName

Its in the Proof of Concept Resource group so all that is needed is to delete and recreate

And this Script checks if its against any resources

(Get-AzResource -Tag @{ “businessProcesst"="Proof of Concept"}).Name

In this case No

Remove-AzureRmTag -Name "businessProcesst"

Although we have checked, the error message stills says that the Tag is being referenced by resources.

It may be easier to update the Tag in Azure rather than using Code. there doesnt appear to be a way of changing the tag name in poweshell (Or at least I haven’t found it yet)

You can go to tags. Click on the Tag. Then click on … at the end of the tag Row and choose Edit tags.

Then Save

This just gives a flavor of how to use powershell to manage tags. This information can be saved into Scripts so we can reproduce the creation of tags if required.

Tags applied to resource Groups don’t get added to the Resources below. Powershell gives you move control over making sure the Resources and resources Groups have the correct tags applied

Introduction to Azure Tagging

Tagging is a feature that has been introduced into the Azure Resource Manager model (ARM). They can be used to Logically group and track resources. The old (Classic) version of Azure was Azure Service Manager.

Azure tagging can be done using Azure Portal, Azure Powershell, CLI (Command Line User Interface) or ARM (Azure Resource Manager) JSON templates

Tags can then be used to select resources or Resource Groups and are useful when you need to organize resources for billing or management

You apply tags to your Azure resources giving metadata to logically organize them into a taxonomy.

Each tag consists of a name and a value pair. For example, you can apply the name “Environment” and the value “Production” to all the resources in production.

Key Points

  • You can only apply tags to resources that support Resource Manager operations
    • VMs, Virtual Networks and Storage created through the classic deployment model must be re-deployed through Resource Manager to support tagging
    • A good way around this is to tag the resource group they belong to instead.
  • All ARM resources support tagging
  • Each resource or resource group can have a maximum of 15 tags.
  • Tags are key/value pairs, name is limited to 512 characters, value is limited to 256 characters
  • Tags are free-form text so consistent correct spelling is very important
  • Tags defined on Resource Groups exist only on the group object and do not flow down to the resources under them
    • Through the relationship you can easily find resource by filtering by tagged resource group Its recommended keeping the tags to the resource group unless they are resource specific.
  • Each tag is automatically added to the subscription-wide taxonomy
    • Application or resource specific tags will “pollute” the tag list for the entire subscription.

Issues with Tags

Using the recommended Tag procedure of tagging at resource group level causes issues because the Tags dont get inherited at Resource level.

The hope was that any tags that you apply at one level of the hierarchy will be inherited by the lower levels within the hierarchy and this doesnt happen.

You need to be careful that your tags stay Logical and don’t differ from higher levels. It may well be preferable to do this via Powershell Scrips that manually to ensure correct logic is maintained between resources and Resource Groups.

Resource Groups

  • The underlying technology that powers resource groups is the Azure Resource Manager (ARM).
  • ARM was built by Microsoft in response to the shortcomings of the old Azure Service Manager (ASM)
  • ARM requires that resources be placed in resource groups, which allows for logical grouping of related resources.
  • Although creating a resource group requires specifying a region for it to be stored in, the resources in that resource group could span multiple regions.
  • In the ARM architecture, resource groups not only become units of deployment, but also units of management of related resources.
  • It allows role-based access control (RBAC) at the resource group level, making it much easier to manage user access to the resources in the group.
  • When users log into the Azure Portal, they will only see resource groups they have access to and not others within the subscription. u Administrators will still be able to assign access control for users to individual resources within the resource group based on their roles. This is great to see costs associated with Each Resource Group

Successful Azure Resource Groups

If an application requires different resources that need to be updated together, such as having a SQL database, a web app, a mobile app, etc. then it makes sense to group these resource in the same resource group.

Use different resource groups for dev/test, staging, or production, as the resources in these groups have different lifecycles.

All the resources in the group should share the same environment (Dev, Test etc) because you deploy, update and delete together

If you have for example the marketing analytics database in one Resource Group and a demo database in another resource group, Each resource group needs its own server

You cant Rename a resource Group

A good naming convention to use is rg-projectorapp-subprojectorapp-dev or projectorapp-subprojectorapp-dev-rg

Examples of Resource groups

Adding Tags to a VM resource within a Resource Group

Tagging Examples

Now you have logical resource Groups set up we can set up tags which are good for larger organisations. 

Business Tags:

  • Cost centre
  • Responsible Person or Party
  • Application Name
  • Environment
    • Development
    • Testing
    • Staging
    • Production

Security Tags:

  • Data Profile

Automation Tags

  • Power Off
  • Maintenance Window
costCenter12345This is your internal billing codeBusiness or email addressBusiness
applicationNamemyappname of the Project Business
environment<production, Staging, QA>Identifies the environmentBusiness
dataProfile<Public, Confidential, Restricted, Internal>Data Sensitivity. Public: This information is public information, and can be openly shared on your website
Internal: Internal information is company-wide and should be protected with limited controls.
Confidential: Confidential information is team-wide and its use should be contained within the business.
Restricted: Restricted information is highly sensitive and its use should be limited on a need-to-know basis.
powerOffyes, noCan this resource be shut down at 7pmAutomation
Example of a Subscription with Tags on the Resource groups.

Looking at this example, the Tagging has been added to the resource Groups. However if you look at a resource, you wont see the tags.

We need to look at a way of ensuring that the Tags applied to the resource group are also applied for each resource.

Adding tags to a resource in Azure Portal

Policies for Tags

  • Azure Policy is a service in Azure that you use to create, assign and, manage policies.
  • These policies enforce different rules and effects over your resources, so those resources stay compliant with your corporate standards and service level agreements
  • Microsoft provides sample JSON scripts to create the tagging policies
  • JSON scripts can be deployed in the Azure Portal, Azure Powershell, Azure CLI or REST API.
  • When you create resource groups and Resources you should always apply tags and the default value for that tag.
  • There are Billing tags policy initiatives you can apply
  • You can also enforce tags and value via Policies on resource groups or Resources


As an administrator, you may need to lock a subscription, resource group, or resource to prevent other users in your organization from accidentally deleting or modifying critical resources.

You can set the lock level to CanNotDelete or ReadOnly. In the portal, the locks are called Delete and Read-only respectively. it may be useful to add a tag for this

LockLevel <CanNotDelete, ReadOnly, NA>


We will look at tagging in more detail in other posts. What Policies you can apply. tagging via Powershell, CLI and ARM JSON Templates. How to manage and enforce good Tagging Logic.

We will also look at ways to use your tags


Azure keeping your costs under control with Spending Limits and Budgets

We can now start adding Subscriptions for all our different Business domains and environments. Azure allows you to quickly get started adding Resource groups and Resources to your subscriptions. Using Management groups above subscriptions allows for even more control. You can manage access, policy, and compliance across multiple subscriptions.

This is all great but what about controlling costs? You will very likely have team members that cant wait to try resources to see what they can do but what happens when you came from after a weekend and you test Cosmos DB has racked up a huge amount of money?

We want to be able to control things more so lets look at some options.

Azure Spending Limit

The following Subscriptions have the ability to apply Azure Spending Limits

  • 0044P Free trial
  • 0059P Visual Studio Professional subscribers
  • 0060P Visual Studio Test Professional subscribers
  • 0062P MSDN Platforms subscribers
  • 0063P Visual Studio Enterprise subscribers
  • 0064P Visual Studio Enterprise (BizSpark) subscribers
  • 0029P Visual Studio Enterprise (MPN) subscribers
  • 0025P Action Pack
  • 0111p Azure in Open Licensing
  • 0170p Azure for Students
  • 0144P Microsoft Azure for Students Starter

When you get a free account, or are for example a Visual Studio Enterprise Subscriber you will get personal use credits.

for example. Going to a personal Subscription and looking at the overview

This Visual Studio Enterprise Subscription allows the user £115 Credits. You cant increase set credits.

If you reach your spending Limit, all services are disabled until the next billing period.

If you need to uncap you need to remove the spending limit by clicking on the above message.

Remove the Spending Limit for the current billing period will allow you to continue and the credits will then reset.

Remove the Spending Limit indefinitely will allow you to go over your Spending limit every month by adding your credit card details (Pay as you go)

However the recommendation is to only remove if required to keep more control

This is obviously a great choice for personal Azure Subscriptions, used for testing and trying things out.

Monitor Costs When using Azure Services

You cant cap the following subscriptions

  • 0136P Microsoft Azure EA Sponsorship
  • 0003P Pay-as-you-go
  • 0023P Pay-as-you-go Dev/Test
  • 0148P Enterprise Dev/Test
  • 0036P Microsoft Azure Sponsored Offer
  • 0243P Azure Pass
  • 0145P Azure in CSP

Track Costs with Budgets

Budgets allow you to set thresholds for your spending. You can then get alerts on these thresholds.

If for example, you have a budget for £500 and a threshold for £400 you could also start adding in automation to start shutting down resources, like VMs at the threshold.

Setting the Budget at the Subscription Level

Within the Subscription Overview

Click Next

Setting Alerts

Application Insights Smart Detection has been used as the action Group if the costs go up to 90% of the budget that has been set

‘Smart Detection automatically warns you of potential performance problems and failure anomalies in your web application’

Then Create

Targeting a Resource with a Budget

Again going to a Subscription and Clicking Cost Analysis

The resource that has a habit of becoming expensive is the database in this subscription

We want to make sure it doesn’t get out of control

Create a new budget for this subscription and then add a filter (the filter will only work once the graph is displayed

Resource Type is selected

then Microsoft SQL Servers. You could also put tags to good use in Budgets. You could for example check for tags of resources created by specific people.

You can now have Budgets that specifically check on certain services. Great if you have Services that have a tendency to create lots of costs.

So now we can rest a little easier knowing that Azure is going to let us know what is happening. It would be great if we could apply automation to shut something down if it was accumulating costs over the threshold. We can look at this next time

Setting up a Board in Azure DevOps (AGILE)

Its time to start tracking Projects with Azure DevOps boards.

With Boards, teams can manage software Projects. They can track user stories, backlog items, tasks, features etc. You can choose the environment you want to work with like AGILE or SCRUM.

For this example, there is only one developer (me) and I’m am tracking my progress on a project where I have been the single developer

AGILE is the process that going to be used

Agile is an iterative approach to project management and software development that helps teams deliver value to their customers faster and with fewer headaches. Instead of betting everything on a “big bang” launch, an agile team delivers work in small, but consumable, increments.

First Open Azure DevOPs

Sign into your DevOps account.

And create a new Project under your Enterprise

Work Items

Now we have a new project we can start working with Boards but first we need to understand what our AGILE work items are and how they interact with each other.


I have specific Epics I want to achieve

  • Reporting from the companies main system
  • Social Media reporting
  • Reporting for the Surveys
  • Reporting for all the telephone enquirers
  • Reporting for Complaints
  • Main Reporting Area for all the data Auditing
  • Reporting for Report usage

So, just looking at this. I want 7 Epics to work with (To start with)


A feature is some complete behavior to implement a new business process. So for example. for the Social Media Epic we want

  • Overall View of Business Performance as provided by the surveys
  • Monthly level reporting on customer satisfaction with drill through

User Stories

User stories are within a feature. These are the smallest change that will result in behavior change. If you don’t observe a change then it cant be demonstrated

For example, as the Customer Satisfaction Manager I want to see the Survey results by month and have the ability to see how we are doing by over the year and at the same point in the year because we need to know if we are doing well as a company to see our trends in satisfaction

As the Company Head of Service I want a full review of our performance using our scoring system against customer satisfaction and how our competitors are doing for benchmarking


These are within a User Story and are the smallest independently deployable change.

  • Get file of Survey data (Pilot project)
  • Move Survey data into the Azure Data Warehouse (Staging area) Incremental loading using Data Factory
  • Establish dimensions and facts
  • Create Dim 1
  • Create Dim 2…….
  • Create Power BI Data Flows
  • Create Top level report by Month of Customer Satisfaction containing last 12 months
  • Create KPIs for Satisfaction against this time last year
  • Drill through to detailed report
  • Drill through to lowest level


a Bug is an error in the code

  • Incremental Refresh is causing Duplicates
  • NULL data Items in Survey Data set


An issue is more related to a process, when the System fails to meet user expectation

  • Created a report based on poorly Served Customers but this needs changing to the new business logic.

Test Case

Test cases can validate individual parts of your code. We will look at this item in another blog post


Lets start with boards. These boards are Kanban Boards

A Kanban board is one of the tools that can be used to implement Kanban to manage work at a personal or organizational level.

When the board is first opened up, Epics don’t seem to be available

With Boards Selected Go to Configure Team Settings

Make sure that Epics are ticked in Under Backlogs

And now with Epics selected click on New Items are start adding in the required Epics

Next we need to start adding some features. It would seem that you cant add the features and then connect them to the epics. You have to create the Features from the epics

go back to the epic, click on … and Add Feature

And you can then see the feature within the Epic

Now we have a Feature we can add the user Story. Go to Features and click on the Add User Story

Same again. Move to User Stories and add Tasks

You can also go into the items and add lots more detail

This link to the Microsoft Documentation gives you lots of information regarding, effort, story points, business value, Priority etc.

Its always good to create the epic and work your way down into the Tasks

Retrospective Items

For this example, items are being added for a sprint that was closed some time ago because the project is being retrospectively moved into Azure Boards


I am starting them all from the beginning of this particular Project and for this I can add a Start Date


The Start Date also Applies to Stories but these will be set when the stories were originally created

However when you close a task and move the whole story into completed, you cant set a completed date

If you click on History and look at the state graph, you cant change the New and Resolved Times. These are set at the time of the action which makes it difficult to add past information into the Board


  • Backlogs help you to Quickly define work (User Stories, backlog items, requirements)
  • You can reorder the backlog so you work on the highest priority first
  • Add details and estimates
  • Assign items to team members and sprints by either bulk update or drag and drop
  • Map items within a hierarcy
  • Review the portfolio of work
  • Forecase work to estimate deliveries
  • Display rollup progress, counts and totals to show completion of work

Basically your backlog displays work items as lists and boards display them as cards

The Remaining Active User Stories have been dragged to Iteration 1

Work Items

All the work items you create can be viewed in here as well as created

Hopefully this gives you a little head start into the world of Azure DevOps boards

Power BI 2020 Updates Incremental Processing for Power BI Pro from (Source) Azure SQL Database (Bug)

This is really the most exciting update for probably all of us working with Power BI. Currently we already have a backlog or reports in Pro that have required Incremental loading for some time so its great to be able to finally get the chance to try it

Our Project, to view Tweets with sentiments etc in a Power BI Report involves the following Services:

Logic Apps

A Logic app that is triggered when a new tweet is posted regarding a specific company

Content Moderator – Logic Apps uses the content moderator to check for profanity

Cognitive Services – Logic apps uses the Cognitive Service to add a sentiment score and to find key phrases

There is also a second Logic App that uses the same logic for Tweets posted by the company.

Azure Storage Account – Tabular Data Storage

  • The Logic Apps loads the tweet information into a Tweets Table
  • The Keyphrases into a Keyphrases table that connects to the tweets table
  • The Media items into a Media table that connects to the tweets table

Data Factory

Data Factory is used to load the information from the Tabular Data Storage into a SQL Server Data base staging area incrementally

The logic is fairly straight forward in that data items are inserted. Nothing is updated or deleted

There is a Pipeline for each single table

The SQL For the Lookup for the data set Mentions

SELECT MAX(WatermarkValue) AS WatermarkValue From [staging].[watermarktable]
WHERE WatermarkValue IS NOT NULL
AND TableName = 'staging.mentions'

the Watermark is a table in SQL that is updated with the Max Date at the end of the process

The query for the Source

CreatedAt gt '@{activity('LookupWatermarkOld').output.firstRow.WaterMarkValue}'

Basically brings through records greater that the value in the lookup table

I have a pipeline over each separate pipeline to run them all sequentially

Next comes a pipeline to run all the stored Procedures to move data from staging to dims and facts in SQL

At the end of these stored procedures we move the date on to the max date in the watermark table (And at the beginning in case there is an error in the SQL pipeline)

Doing this means that Data Factory only loads new records and doesn’t have to reload the staging area ever time

The Top level pipeline runs all the incremental Copy pipelines and then the Stored Procedures

Lets have a look at our watermark table before and then after a load

And a look at our last import of tweets in the audit table.

There are more audit tables to help find any issues. This is after the run on the 13th March (Test 2)

Data Factory Trigger

Because its not in full use at the moment the data set is loaded once a week on a Sunday at 12:50 and until this is being retested its being set to off

Azure SQL Database

In here we have the Watermark Table. All the audit tables, the staging tables and dimensions and facsts

the Fact and Dimensions are currently created via Stored procedures but the hope is to try and change to data flows.

Power BI

the Data is imported into Power BI Pro (Full Process) so the model is dropped and recreated.

Azure Data Studio

Is there any way we can have a look at what is going on when we load. Yes, by using Azure Data Studio

Once installed, connect to the SQL Database that is your data source

So long as you have the profiler extention installed you can Launch Profiler

If you don’t have it, you can download the extension

Once Launched Start a Profiler Session

Now we need sometime to Profile. go into Power BI service, Datasets.

click on refresh now and then go to data Studio to see whats happening

From Logon to Logout during the run, it took 20 minutes because the entire model is refreshed. obviously it would be really good if we could get the time down using incremental refresh

Before you set up Incremental processing, ensure that the services preceding the Power BI Load have been well tested and signed off.

Incremental Processing in Power BI Pro

In Power BI desktop. Incremental refresh is now out of preview so no need to go to Options and Settings to turn it on anymore.

Define your Incremental refresh policy

If the system are acting as they should and there are no bugs or issues

  • New rows are added into the data set
  • No historical data is updated or deleted
  • Incremental loading can be added to every table apart from media tables because there are that many records. they can be left as full upload

Set up incremental refresh in Desktop. Create Parameters

It isn’t practical to hold all your data when you are working in desktop if you are working with a Large model.

Go to Power Query Editor

Select Manage Parameters

The two parameters that need setting up for incremental loading are RangeStart, RangeEnd

These are pre defined parameter names only available in Premium for Incremental processing

Range Start and Range End are set in the background when you run power BI. They partition the data

You need to be aware of Query folding here. This is when, you write lots of steps in M Query to transform the data and where possible they are applied at source. So RangeStart and RangeEnd will be pushed to the source system. Its not recommended to run incremental processing on data sources that cant query fold (flat files, web feeds) You do get a warning message if you cant fold the query

The suggested values are simply ones you add that get amended later during processing.

this start date was chosen because at present, the data only started to get collected in 2019 so there is only around a year of data

Filter the data in the model

Still in Power Query Editor, all the tables that require incremental load need to have the rangeStart and RangeEnd paramters adding to the filtered date column

Incremental refresh isn’t designed to support cases where the filtered date column is updated in the source system.

With this in mind, imagine you have a sales table with an Orderdate and an UpdateDate. the OrderDate is static. The UpdateDate will be updated if there are any changes to the record.

Order date would need to be chosen as its static, so lets go through the tweet tables and set the filters. Click on the Column header icon to get to the filters

In power BI you don’t need so much data to do the testing, so this is great to keep the data smaller in desktop. At the moment, its using the default settings we provided.

  • dim.mentionsKeyphrases Twittertimestamp
  • dim. mentionsTweet CreatedAtDateTime
  • dim.BusinessKeyphrases TwitterTimeStamp2
  • dim.BusinessTweets CreatedAt
  • dim.BusinessReplies CreatedAt
  • fact.Mentions Date (For this, date was created from the date time Key in Power BI Query Editor)

Close and Apply

Define your Incremental Refresh Policy in Power BI Pro

go to your first table and choose incremental refresh

Storing everything  for 5 years. its set to months so the partitions are smaller

If this is running every single day then you would only need to refresh rows in the last 1 day. However as a just in case 1 month has been used, in case for any reason the job is suspended or doesnt run.

Detect Data Changes has been used. The months data will only be refreshed if the ImportDate for this record is changed (Or there are new records)

No records are deleted so we don’t need to worry about this

Publish the new Power BI Report and Data Flow

You might be thinking at this point, but I dont want the filters that I have set for Desktop to be applied in Service. I want to see all my data in Service

Dont worry, in Service RangeStart and RangeEnd don’t keep the dates specified for the filters in Desktop.

they are set via your incremental refresh policy. So they are set as the partitions for our 60 months (Instead of setting it to 5 years, meaning there is one RangeStart and OneRangeEnd, you get RangeStart for Month one, RangeEnd for Month 1, RangeStart for Month2, RangeEnd for Month2 etc, breaking your 5 years down into much smaller partitions to work with,

You need to set up the Incremental Refresh policy for every table that has been filtered with RangeStart and RangeEnd

Test the Process

I have a visual for Number of Tweets

Today so far there are 11 Tweets

I also have an audit report

  1. The Logic App has been processing tweets realtime into Table Storage
  2. Run Data Factory (2 new records)
  3. Reprocess Power BI Pro data Error Resource name and Location need to match
  4. If there hadn’t been an error we would move to Azure Data Studio Check. Note that it now takes a second to run
  5. Check the Visual

Error Resource name and Location need to match

The data set now has a warning sign. after speaking to Microsoft this is a Known issue and should be fixed in April. it is believed to be something to do with detect Data Changes So basically…… to be continued

DevOps Organisation Settings

Lets have a look in a little more detail at the Settings for the DevOps Organisation Level and at what the Organisation is.

When setting up DevOps, it created at Organisation level of [My Name] , and another Organisation was then created manually for the company

Organisations can be treated as accounts and each organisations has its own URL. You will also hear organisations being called Collections

You must always start with one organisation but you can have multiple.

Go to Organisation Settings



This gives you the overview of the Devops organisation and there are a few options in here to be aware of

Privacy URL

Your Privacy URL can be added here to link to your organisations document describing how we handle internal and external guest data. If you have a public website or app you are required to have a dedicated Privacy policy URL.

Q Do you have a privacy document already in place?

Organisation Owner

the owner is set against the person who created the organisation account but this can be changed.


New Projects can be set up here. See


A good area to find out the Access levels for each user

  • Stakeholder: Partial access and can be assigned to unlimited users for free
  • Basic: Provides access to everything but test plans. Up to 5 users free, then £4.48 per month
  • Basic + Test Plans: Includes test plans. £38.76 per month
  • Visual Studio subscription: For users with a Visual Studio Subscription and features are enabled based on Visual Studio Enterprise, Professional Test Professional or MSDN Platform

Group Rules

DevOps includes group based licensing for Azure Active Directory (Azure AD) Groups and Azure DevOps groups.

Azure Active Directory Groups are created in Azure Active Directory

DevOps Groups and Collection Level Groups can be found within the Permissions section so we can look at this in more detail later.

Add a group Rule to assign an access level or extention to the group and resources in Azure DevOps are assigned to all members of the group.

When users leave a group the licenses are freed and returned to your pool.

Imagine this scenario

It will be easier to add the following Groups

  • Project A Contributor Group and add Debbie and Jo
  • Project A Reader Group and add Tess
  • Project A Administrator Group and add Sarah
  • Project B Reader Group and add Debbie and Tess
  • Project C Contributor and add Jo
  • Project C Administrator and add Debbie

To manage licenses and group rules, you must be a Project Collection Administrator (PCA) for the organization. Check this within Security > Permissions

Click Add a group rule

Both users within this new group have a visual Studio account so Group Level is set to to Visual Studio. However with Group Rules you assign the Access level for the Users at Group Rule Level

At this point I also added a new Group.

You can click on the … Button after Last Evaluated to get to either Manage Group Rules or Manage Members

These are the two rules for this group

What happens when your users have a mix of access levels within the one User Group?

The Users get their Access Levels from the Rule so their Access level would be reset


Before Looking at Billing you can use the Azure Calculator to get more of a feel of what you need

Note that you are paying for 5 basic plans, the first 5 are free but the 10 developers who need the Basic + Test Plans License are the ones adding to the monthly cost

Why would you up Additional Parallel CI/CD jobs on either Microsoft Hosted of Self hosted Pipelines?

See Parallel jobs for more information

Azure DevOps Billing is through Azure and because Billing has not yet been set up we only have access up to the Free Tier limits

If you click on Set up Billing You need to choose an Azure Subscription to add it too

You cant add it to your Personal credits because of the spending Limit caps. Once set up you can then manage the paid access for your users, bearing in mind the free usage tier


Allows you to see all the audit-able events and you can export the log and filter on specific events

Global Notifications

You will recieve notifications for lots of Actions in DevOps like when a build completes or a Deployment approval is pending. You can configure the organisation notifications from here


Usage allows you to see whats been going on in DevOps

You can also filter the information and choose your columns that you are interested in

You can Select

  • TFS (Any application within the Organisation Service account, e.g. TFS
  • Release Management (Any application within Release Management Service)
  • Analytics (Any application within the Analytics Service)


  • Normal
  • Delayed
  • Blocked

And time period, for example, the last 7 days.


You can browse the marketplace for additional DevOps Services. Like Analytics above to gain insight into the health and status of your DevOps Projects

Just Searching for Git brings up lots of free Git Services that you can use

Azure Active Directory

When DevOps was accessed, an email was used that was connected to a Tenant in Azure because we use Office 365

Office 365 uses Azure Active Directory (Azure AD) to manage user identities behind the scenes. Your Office 365 subscription includes a free subscription to Azure AD so that you can integrate Office 365 with Azure AD if you want to sync passwords or set up single sign-on with”

Because of this, Azure DevOps connected up the Azure Active Directory



If we never want to allow Public Projects we can simply set the above policy to Off.

We can also dis-allow external guest access through policies


Project Collection Administrator Has permissions to administer build resources and permissions for the collection

A collection is a container for a number of projects in Azure DevOps and corresponds to the Organisation.

Lets set up another Project Collection Administrator

We want everyone in the POC Admin Group to also be a Project Collection Administrator

The DevOps Groups that have been created and Collection Level (Enterprise Level) Groups can all be seen here

You can also look at the users and access all the Settings per user from the User tab.



Gives you more information on what will be available on the board for each type, e.g. Agile, Scrum etc.


Agent Pools

DevOps Pipeline Pools are scoped to the entire organisation so they can be shared across Projects. You don’t need to manage Agents individually. You can organise them into Pools

An agent is installable software that runs one job at a time.

With Microsoft hosted agents, every time you run a pipeline you get a fresh virtual machine which is discarded after use


This is an example status badge in Boards

You can set variables at queue time unless the above option is enabled. Only variables Settable at queue time can be set if its limited

By default, the collection (Enterprise)-scoped identity is used, unless the Limit job authorization scope to current project is set in Project Settings > Settings.

Deployment Pools

An agent pool (Above) defines the sharing boundary for all agents in that pool. Whereas deployment Pools are about deployment target machines that have agents installed on them.

Deployment pools are convenient when you want to deploy a project to different machines

Deployment groups represent the physical environments; for example, “Dev”, “Test”, “UAT”, and “Production”.

The deployment group is a layer over the deployment pool which makes the targets available to the release definitions within a project

Parallel Jobs

As we have already established. The free tier allows you to have 1 parallel job but What is a parallel job?

A job is a set of one or more build tasks that run sequentially on the same target.

You could have for example a project that contains a web app and a Web API. They dont need to be run sequentially, they can be run in parallel to save time

Each App type can equate to 1 job within a build definition. Each build definition as its own build pool/agent

If you have two licensed build pipelines you could run them concurrently which will decrease overall build time.

You could also split up tests across jobs to be executed across parallel builds which will reduce the overall time

You need to look at your Projects and what you are building and see if Parallel jobs are relevant to you, or if you are happy for the agents to run your builds sequentially

Oauth Configurations

Register and Authorise your app



Check the storage sizes of your artifacts. Remember you get 2 GB free for your artifacts.

Hopefully this gives you a little more information of each sections of your organisational settings. You need to understand at a basic level

  • Who your teams will be and what they will require (Set Access Levels, Groups and Rules up)
  • What you will be creating and can they be built in parallel?
  • what environments will you be releasing too

Each section can then be looked at in much more detail

Create a DevOps Project

At this point we know that we are using DevOps Service rather than the on premesis DevOps Server so go to

To get to your initial DevOps page

Which gives you three routes in. Do you…

  • Start Free?
  • Start Free with GitHub?
  • Or Sign in to Azure DevOps if you already have an account?

At this point, we don’t have an account

Making Sense of DevOps Pricing

Start Free or Start Free with Github

Start free with GitHub

Use this option if you already have a GitHub account

In this example Start for free option is used. Because [I] have an azure account, DevOps is already logged in and it knows what my tenant is.

At this point there is no suggestion of a 30 day trial or any other information. so for the time being lets get started by adding a project (This is because I have a Visual Studio account. This may be different for uses without a Visual Studio Subscription)

Also Note the level

This is the organisation level and this is my own personal devOps account. What happens if you want to set up a New Organisation to connect related projects and scale out to enterprise Level?

Select New organisation

And New Project

the New Organisation level has been set up with the Companies name

I can now start working with DevOps at an organisational level. We can have 5 basic users for free so for the time being, this is what we will stick to.

We know we want a Private DevOps area

Version Control Git has been selected because this seems to be the one that other team members are the most comfortable with

Work Item Process Agile, Basic, CMMI, Scrum

The default is Agile but lets have a quick look at each of these processes


The simplest model.


Agile includes scrum, Works great if you want to track user stories and bugs on a kanban board


Supports the Scum methodology. Really good for tracking backlog items and bugs on a kanban board.


if your team follows more formal methods use CMMI (capability Maturity Model Integration.

For this Project, the Agile approach is selected

Invite a user into the Project.

Under 5 users and DevOps is free. We wont be doing anything with test plans at the moment so lets add one other user into this area

DevOps Access Levels

  • Stakeholder can be assigned to users with no license or subscriptions and need a limited set of features
  • Basic Provides access to most features. up to 5 users is free
  • Basic + Test Plans The user has access to test plans but this costs around £34 a month extra and you dont get any free accounts
  • Visual Studio Subscription – Assign to users with a Visual Studio Subscription

Check Levels of Access

Go back up to the organisational level and select Organisation settings in the bottom left hand corner of the screen

Next go to users and you can check the settings. Note that one account is under a Visual Studio Enterprise Subscription so this is assigned to users who already have a Visual studio Subscription.

We have 1 basic account which will be free at the moment

Checking Spending

Obviously the one thing you want to do is check that you aren’t spending money unnecessarily.

Still in Organisation Settings

Currently there is no billing applicable but this section will need a more detailed how to later on

Checking what has been done at organisation level

Here you can see that Tess has been added by myself to a Group and her access was set to basic.

There should be a post coming a long soon that will look into Organisational Setting in more detail

We have created a project and set up new users with Basic level accounts.

Getting back to your Project

Close Devops down and then re open

This time you can sign in and go straight to your new project

Next time we will start using some of the services on offer like Boards, Repos, pipelines, test Plans and Artifacts