Automating Your Organization’s Azure Dev-Ops Power-BI Reports.
Azure Dev-Ops provide us a way of managing, organizing, and tracking all the tasks and their subtasks for a project.
Since there is a direct integration between Azure Dev-Ops and Power BI, Power BI can be used to analyse, and create reports using Dev-Ops data.
In this blog, as the title says, we are going to have a look at how to automate your organization’s Azure Dev-Ops Power-BI report.
How you shape or model your Dev-Ops data is irrelevant to the following discussion as each business will select their own parameters/attributes, relationships, and business logic to construct the reports.
So, in essence, we are automating a process that gets data from the source without any human dependence, this data will further trickle into the system of how you have shaped, modelled, and defined your business logic.
There are two approaches to automate Azure DevOps reports in Power-BI depending on your access levels in azure environment.
- Approach one – If you have project level
- Requires some level of manual intervention but minimizes number of steps needed.
- Needs you to exist as a member in each project with “Basic” level of access.
- Approach two – If you have organization level
- Can achieve complete automation.
- Needs you have organizational level of access on the entire azure environment.
Both the levels have their points of access/link as follows:
- Project Level –
dev.azure.com
- Org Level –
analytics.dev.azure.com
Both use different ways of connecting / power-bi connectors to access and get data into PBI which will be explored further when we get into each method.
Let’s look at automation steps for both approaches.
Approach 1 – When you have project level access:
Works with dev.azure.com
PBI connectors used/explored:
- Web connector
- Azure Dev Ops (Boards Only)
For this method, report author needs to be added as a member in each of the projects present in your organization.
Now,
Step- 1
To get a list of all projects present in your organization.
You can do that through web connector in PBI using the following endpoint:
https://dev.azure.com/{organization}/_apis/projects?api-version=6.0
This will bring in a list of all projects present in your organization if you are present as a member in these projects.
It will contain several columns, we only need the one containing a list of project names, remove other columns.
Here Value.name is the project name column.
Step-2
Creating a custom column against the data obtained in above step.
For this, we need to understand how Azure DevOps (Boards Only) PBI connector works behind the scenes.
To get data of a particular project into power-bi we can use Azure DevOps (Boards Only) connector.
As you can see from the above screenshot, it takes two parameters to get data.
- Organization
- Team project (Project Name).
Once you enter these details and connect with “Organization account” access method you’ll get data for one project.
To continue our process of automation, if we have a look at the auto-generated M-Code of the above process we can see the following:
The M-Code may vary, but it doesn’t matter here.
If you have a closer look at the source link, it’s as follows:
= VSTS.AnalyticsViews(“{Org-Name}”, “Project-Name”, [])
As you already know, it takes two parameters as input, “Organization name” and “project name”.
If you remember, we had gotten the list of project names in the Step 1.
All we need to do now is to create a custom column against “value.name” (Project Name) with the “Project Name” parameter in above link pointing to the list of columns that has project name. (Refer the screenshots below)
Once applied,
You will see all the relevant data for each project:
If any new project is now added in your organization (and given that the report creator is added as a member in that project), it will appear in the table with all the relevant data captured in it.
This method needs the report author to be added as a user again and again if a new project is added in the organization.
Relevant /Referred Links:
Approach 2 – When you have organization level access:
Works with analytics.dev.azure.com
PBI connectors used/explored:
- OData feed
This method is straightforward as we just need to place the required links with endpoints in OData feed connector.
But there are some considerations and limitations that we faced and needs further explaining when it comes to consolidating all the required data in one place.
Step- 1
Getting the list of projects with the following OData link:
https://analytics.dev.azure.com/{organization-name}/_odata/v2.0/Projects
You will get several columns and some tables that can be expanded.
In our case we needed “Project SK” and ‘Project Name” column.
You can check on your own requirements and move ahead.
Few expandable tables will also be visible, but azure does not allow the expanding operation in Power Query Editor.
Step-2
Getting all the necessary data that you will use for reporting.
Most of your reporting requirements can be fulfilled by the default entities present in azure:
Reference link:
You can find the list of entities present in Azure in below link:
We, in our case, were concerned with the entity named “Work Items”.
We will now get all the data related to work items using following OData link:
https://analytics.dev.azure.com/{ Org-Name} /_odata/v2.0/WorkItems?
columns, some in plain column format, some in the form of expandable tables and others in the form of expandable records.
For example (these are all present in “Work-Items” entity):
It’s not a complete list.
Azure does not allow expanding the tables or records within an entity, in power query editor, and some of the data that we needed were present in them. (For example, AssignedTo(UserName column) and CreatedBy(UserName column) fields).
Most of the fields that we needed were present in WorkItems but, AssignedTo(UserName column) and CreatedBy(UserName column) fields were present in their respective records as its visible in the screenshot.
Step -3
Expanding records that cannot be expanded in Power Query Editor
To access records, some M Code changes are needed.
Azure also does not provide a way to expand multiple records in the same query, so we must query “WorkItems” two times, once to retrieve “AssignedTo (UserName column)” and again to retrieve “CreatedBy(UserName column)” in the following way highlighted in bold:
“https://analytics.dev.azure.com/ {Org-Name}/_odata/v2.0/WorkItems?
$select=WorkItemId, WorkItemType, Title, State, TargetDate, CompletedDate, AssignedTo &$expand=AssignedTo($select=UserName)”
AND
“https://analytics.dev.azure.com/ {Org-Name}/_odata/v2.0/WorkItems?
$select=WorkItemId, WorkItemType, Title, State, TargetDate, CompletedDate, CreatedBy &$expand=CreatedBy($select=UserName)”
Other columns need to be retrieved again as we need to merge the tables, further in our automation process.
Azure allows expanding of records within an entity in a controlled manner that needs to be hard coded in M Query by selecting a particular record and then using
$expand = RecordName.
You will need to further select particular fields within those records by using
$expand = RecordName($select=FieldName) as you can see in the above example.
Now there are three tables in total:
- Projects (containing ProjectSk, Project Name)
- WorkItems (containing ProjectSk, AssignedTo column), other relevant columns)
- WorkItems (containing ProjectSk, CreatedBy, other relevant columns)
Include/Exclude other fields as per your requirements but keep the same columns in both WorkItems query.
Step- 4
Performing merge operations to create a master table.
To create a master table, follow the below steps:
Merge the two WorkItems tables to place AssignedTo(UserName column) and CreatedBy(UserName column) columns in one table.
- Merge the output table from above step with Projects table based on ProjectSk to incorporate Project Name.
- Use this table as a master table for your further reporting.
- Preserve all the dependant queries in query editor and now your reports are automated.
Relevant /Referred Links:
All the links that we have used to get data have certain OData and API versioning which may change in future.
Versions we used to create reports are as follows:
- API-version = 6.0
- OData Version = 2.0
As a reiteration, below are the access levels on project and organization level:
Other project level permissions are:
Basic, Stakeholder, Visual Studio Enterprise level access.
Depending on the approach you take for your reports, you need either relevant project level or org level access permissions on dev-ops environment.
Thank you for reading!
Akshay Gothe
Data Analyst
Addend Analytics