Why Include the Iteration, Area Path, and Other Fields to the ODATA Query
The Azure DevOps Analytics Views offer direct access to fields like iteration, area path, project name, and user name within the dataset for reporting purposes. However, incorporating these values directly into the dataset using an ODATA query requires additional effort. The ODATA tables feature navigation properties that enable users to include related entities such as Area Paths and Iterations in the result set. Without utilizing these navigation properties, the result set for the Area Path will only return the AreaSK field, which merely contains a key value rather than the actual Area Path. By leveraging these navigation properties, you can add these values directly to the report from the ODATA query and not have to have a separate table for the Areas, Iterations, Users, and Team Projects.
Why Do I need this for my Report?
The ODATA entities for Work items include only the navigation properties: AreaSK, IterationSK, ProjectSK, and UserSK. For optimal report data modeling, a star schema is recommended, as these navigation properties serve as the foundation for referencing other tables in the schema. However, if your report is small and you prefer to maintain a single table data model, or reporting in Excel, the following ODATA queries will provide the necessary values for your report.
Implementation:
The Work Item ODATA entity provides the navigation properties for the Area and Iteration paths and the Team Projects. This is the result set from the standard ODATA query referencing the AreaSK, IterationSK, and ProjectSK. Not especially useful in a single table data model report.
As mentioned before, navigation properties represent relationships between different entities. For example, a work item might have a navigation property to its related project or iteration and you can return the columns from the entities in the ODATA query using the $Expand statement. This Microsoft help file explains in more detail how to use the statement to return the different entities.
The following ODATA queries should be run in VS Code before adding to Power BI. Follow the steps in this blog post on using VS Code and testing the ODATA queries.
Area Paths:
Using the $expand statement allows you to return the Area Name, Area Path, Number, Area Level, etc. This makes it easy to return the relevant values from the ADO analytics for your report.
This query returns the top 10 User Stories that are created after 1/1/2023 with the Area Path fields.
https://analytics.dev.azure.com/your ADO Org/Your Team Project/_odata/v4.0-preview/workitems?
$select=WorkItemID,Title,WorkItemType,AreaSK,
&$expand=Area($select=AreaSK, AreaName, AreaPath,Number,Depth, AreaLevel1)
&$filter=(CreatedDate ge 2023-01-01Z) and (WorkItemType eq 'User Story')
&$top=10
When the ODATA query is added to the Power Query editor in Power BI, you will need to expand the columns. The $Expand is considered another table of data.
The Area information is now integrated into the ODATA query, offering a more detailed view of the Area Path. For example, the AreaName field provides access to the last node in the Area Path, simplifying visuals and slicers by using a single name instead of the entire path. Additionally, you can access various levels within the Area Path and utilize the Depth field to parse the path for specific values. This enhancement brings greater clarity and precision to your data analysis.
NOTE: To view the fields available for Area Paths or other ODATA entities, run the following command in VS Code to view the metadata for your ADO organization:
https://analytics.dev.azure.com/Your ADO Org/Your Team Project/_odata/v4.0-preview/$metadata
Iteration Paths:
The Iteration Paths entity provides more details about the iteration. Now you have access to the last node in the iteration path and can determine the iterations that have ended using the IsEnded field.
The following query returns the top 10 User Stories created after 1/1/2023 with the Iteration Path information provided in the result set.
https://analytics.dev.azure.com/your ADO org/your Team Project/_odata/v4.0-preview/workitems?
$select=WorkItemID,Title,WorkItemType,IterationSK,
&$expand=Iteration($select=IterationName,IterationPath,StartDate,EndDate,IsEnded,Depth)
&$filter=(CreatedDate ge 2023-01-01Z) and (WorkItemType eq 'User Story')
&$top=10
Team Projects:
The Team Project name in the result set is very handy in the result set to make it easier to use in a slicer to filter data in the reports. This is used in single team project and multi-team project reports. It is really helpful to have the Team Project name in the result set to make it easier to add slicers on the report or filter the queries by the Team Project name.
https://analytics.dev.azure.com/your ADO org/your Team Project/_odata/v4.0-preview/workitems?
$select=WorkItemID,Title,WorkItemType,ProjectSK,
&$expand=Project($select=ProjectName)
&$filter=(CreatedDate ge 2023-01-01Z) and (WorkItemType eq 'Epic')
&$top=10
Users:
For the User’s ODATA entity, it is helpful to use the a filter in the Expand clause to not return null for the user’s name. The recommendation from Microsoft is to append the following to the end of your ODATA queries for better performance:
[Implementation=”2.0″,OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]
The downside to this is you may experience an error when expanding on the User’s name field. The $filter=UserName ne null helps. If it does not eliminate the error, then remove the OmitValues = OOdataOmitValues.Nulls from the command and use the following in the ODATA query in the Power Query editor:
[Implementation=”2.0″,ODataVersion = 4]
https://analytics.dev.azure.com/your ADO org/your Team Project/_odata/v4.0-preview/workitems?
$select=WorkItemID,Title,WorkItemType,
&$expand=AssignedTo($select=UserName;$filter=UserName ne null),CreatedBy($select=UserName;$filter=UserName ne null),ClosedBy($select=UserName;$filter=UserName ne null),ResolvedBy($select=UserName;$filter=UserName ne null)
&$filter=(CreatedDate ge 2023-01-01Z) and (WorkItemType eq 'Epic')Multiple $Expands in a single ODATA Query:
You can use multiple expand statements in a single ODATA query by separating the table names and using the $select statement to list the fields from each table.
https://analytics.dev.azure.com/your ADO org/your Team Project/_odata/v4.0-preview/workitems?
$select=WorkItemID,Title,WorkItemType,
&$expand=Area($select=AreaSK, AreaName, AreaPath),Iteration($select=IterationSK,IterationName),AssignedTo($select=UserName;$filter=UserName ne null),CreatedBy($select=UserName;$filter=UserName ne null),ClosedBy($select=UserName;$filter=UserName ne null),ResolvedBy($select=UserName;$filter=UserName ne null),Project($select=ProjectName)
&$filter=(CreatedDate ge 2023-01-01Z) and (WorkItemType eq 'Epic')
&$top=10Hope this helps with your reporting experience with ADO data!