Integrate Azure Devops with Microsoft Fabric, to fetch board items and queries
Subhad365
7
User Group Leader
Like
Share
Report
Azure DevOps Server, formerly known as Team Foundation Server (TFS) and Visual Studio Team System (VSTS), is a Microsoft product that provides version control (either with Team Foundation Version Control (TFVC) or Git), reporting, requirements management, project management (for both agile software development and waterfall teams), automated builds, testing and release management capabilities. It covers the entire application lifecycle and enables DevOps capabilities. Azure DevOps can be used as a back-end to numerous integrated development environments (IDEs) but is tailored for Microsoft Visual Studio and Eclipse on all platforms.
Azure Devops is now an integral part of our life. Where not we are using it? Pieplines, code repos, issue trackers, sprint managements, LCS ALMs, Swimlanes, Backlogs, Burn-up/Burn-down charts: all in all Azure Devops is no less than a big budget James Bond movie.
On the otherhand, Microsoft Fabric is an end-to-end analytics and data platform designed for enterprises that require a unified solution. It encompasses data movement, processing, ingestion, transformation, real-time event routing, and report building. Microsoft Fabric is a data analytics platform that helps organizations manage, access, and act on data. It's designed to address all aspects of an organization's analytics needs, including all ingredients of data movement like: data processing, ingestion, and transformation, as well as real-time analytics to allow users to explore, analyze, and act on large volumes of streaming data in near-real time. Essentially the Fabric could be a well-designed mechanism to separate each and every department of you your organization, by creating separate Workspaces. In a word, Workspaces are places to collaborate with colleagues to create collections of items such as lakehouses, warehouses, and reports, and to create task flows.
This blog is about a cool hack that can help you bring in all yout board items from Azure Devops into Microsoft Fabric. This could open up humoungous opportunities to
1. Create extensinve Power BI dasboards.
2. Reports, charts based on parameters like Assigned users, bugs' counts, efforts, tee-shirt sizing or any parameter of your choice
3. Understand and predict risks based on work items' history, priorities, turn around times, etc. using Fabric's pedictive modelling.
4. Not to mention performance governance of individual team members
Let us go step by step to implement the smae:
Step 1: understanding Azure Devops APIs
I have the following Azure DevOps project created as under the Organization SubhashishChakraborty:
Which when executed gives the following result:
Please note the Query Id as highlighed above.
Step 2: Define Personal Access Token (PAT):
Define the necessary details and set the scope as Full access:
Step 3: Acess the Query API
To acess this query as an API, we need to call the following API, as a POST Operation:https://dev.azure.com/subhashishchakraborty/_apis/wit/wiql?api-version=7.1
With the following query that returns the Title and Description of wWork items, of type Issue:
{
"query" : "select [System.Id], [System.Description], [System.AssignedTo] from WorkItems where [System.WorkItemType] = 'Issue'"
}
To return the actual query result, we can call the query API like this:
https://analytics.dev.azure.com/subhashishchakraborty/subha-demo-project/_odata/v4.0-preview/Workitems?$filter=WorkItemType eq 'Issue' &$select=WorkItemId,WorkItemType,Title
Where it return the Title, WorkItemId and WorkItemType:
Please note the type of autentication has been selected as Basic Auth and password = the PAT which you created above.
Step 4. Creating a workspace in Microsoft Fabric:
Create a Free access to Microsoft Fabric, by visiting: https://app.fabric.microsoft.com/ , using the ID that will be cerated from the above. This will create neecessary capacities for Microsoft Fabric, with enough provisions to let you do enough of R&Ds for 60 days ✌✌✌
Once you login, your landing page should look like:
Look at the URL from the address bar. Note that, I have purposely kept the experience= data-engineering. You can change this to PowerBI, if you wish to explore Power BI capabilities of MS Fabric.
Once done, you can create a new Workspace from as follows:
Click on Advanced >> License mode >> Select Trial:
Uh, ok -- once this is done, in your newly created workspace >> Click on New >>
Click on More Options: select Lakehouse from the below choices:
Give a proper name here:
Once you login, your landing page should look like:
Look at the URL from the address bar. Note that, I have purposely kept the experience= data-engineering. You can change this to PowerBI, if you wish to explore Power BI capabilities of MS Fabric.
Once done, you can create a new Workspace from as follows:
Give a suitable name:
Uh, ok -- once this is done, in your newly created workspace >> Click on New >>
Click on More Options: select Lakehouse from the below choices:
Give a proper name here:
Press Create to continue.
Step 5: Ingesting the data into Lakehouse:
Once the Lakehouse is ready you can fetch the data in it by click the above:
In the pop up that ensues, select OData from the above search bar:
In the next step, give the URL as desribed in the above step, to fetch WorkItems:
Give a proper connection name, Authentication kind = Basic and User name = the Admin login and password = PAT created above.
Click Next to continue.
In the next step select what you want to bring up and store in your Lakehouse. Remmember to select all that is rerlevant. For demo purpose I am selecting the WorkItems table only:
Also do remember to specify the tables/files (destination) where you want this data to reside:
And this is the final step, which actually runs the query and inserts into the target table;
This will trigger the data Pipeline to run automatically, fetching in records to the target system.
Click on the schedule run to make your data be refreshed after desired hours.
In your Lakehouse, you can see the Table been created and populated:
as is described in my previous blog:
With that you can create reports and dashboards by including necessary tables in your Lakehouse and use PowerBI capabilities to leverage the furtherance.
With that, let me take your leave. Would soon be back with another cool hack with Microsoft Fabric and data enginerring. Till then, take care, much love and Namaste, as always💓💓💓
*This post is locked for comments