Overview

The provider allows you to access Azure DevOps data at multiple levels, from organization-wide information down to specific project and repository details.

Once connected, the provider organizes data into two catalog types. The static CData catalog operates at the organization level and contains one schema called Information, which provides data across your entire Azure DevOps organization. The provider also creates dynamic Project catalogs for each specific project in your organization. Each Project catalog contains two static schemas and additional dynamic schemas. The Analytics schema provides data from the Analytics service, while the Project schema contains general information about that specific Azure DevOps project. Additionally, each Project catalog automatically generates Repository schemas for every repository within that project, allowing you to access repository-specific information. An overview of the types of catalogs and schemas that can be expected after a connection has been established is given here:

• CData
  • Information
• Project_1
  • Analytics
  • Project
  • Repository_c36a682e-db74-4bc1-b0c3-8929402ce826
  • Repository_61b94efc-fe12-4179-825c-5ab3d0fee244
• Project_2
  • Analytics
  • Project
  • Repository_3dfd899f-c4c2-419a-a33d-23c59582922a

CData catalog

This contains information that is related to the entire Azure DevOps organization rather than being tied to a specific project. It can be accessed by setting the catalog to CData.

Information schema

As the only schema in the CData catalog, this also contains information that is related to the entire Azure DevOps organization. It can be accessed by setting the schema to Information.

This schema is useful if you are trying to query information on projects. For example, if you are trying to compile a list of project names that you can use to build the Project catalog names, query the Projects table.

SELECT Name FROM [CData].[Information].Projects

This schema is also useful if you need to retrieve data that is shared across all projects, like Agent Pools.

SELECT * FROM [CData].[Information].AgentPools

Project catalogs

Most data in Azure DevOps is related to a project. In the provider, each project is modeled in its own catalog, so the amount and names of the Project catalogs depend on how many projects are in your Azure DevOps organization.

The names of these catalogs must be in either the format 'Project_projectId' or 'Project_projectName'. For example, to query data in a project named 'dev' with the Id '2ee729d6-804d-4ece-84af-d5befa94abe8', you can set the catalog to either Project_dev or Project_2ee729d6-804d-4ece-84af-d5befa94abe8.

Project schema

The Project schema contains information on the project specified in the catalog name. It can be accessed by setting the schema to Project.

Information such as Test Suites, Teams, Builds, and other items related to a project can be found here. For example, the following query retrieves all test suites for the project named 'dev'.

SELECT * FROM [Project_dev].[Project].TestSuites

It can also be used to retrieve a list of repository Ids, which can be used to construct the Repository schema names. For example, the following query retrieves all repository Ids in the project named 'dev'.

SELECT Id FROM [Project_dev].[Project].Repositories

It also contains information found in a project's repositories, sliced across all the repositories in the project. This allows you to query data across all repositories, but it is less performant than the Repository schemas.

SELECT * FROM [Project_dev].[Project].PullRequests

Repository schemas

Each repository is modeled in its own schema, which will be different depending on how many repositories are in a specified project.

The names of these schemas must be in the format 'Repository_repositoryId'. For example, to query data in a repository with the Id '3ee729d6-804d-4ece-84af-d5befa94abe8', you can set the schema to Repository_3ee729d6-804d-4ece-84af-d5befa94abe8.

The Repository schema is useful when you are trying to query data in a single repository, such as pull requests, commits, and Git branches. While these tables are also available in the Project schema, it is more performant to use one of the Repository schemas as this does not require the data to be sliced across all repositories. For example, if you are trying to query pull requests in repository 'd36a682e-db74-4bc1-b0c3-8929402ce829', it would be more performant to use the repository schema, as shown below.

SELECT * FROM [Project_dev].[Repository_d36a682e-db74-4bc1-b0c3-8929402ce829].PullRequests

Analytics schema

This is unique in that while all other schemas connect to REST endpoints, the Analytics schema connects to the OData Analytics service. It can be accessed by setting the schema to Analytics.

It is useful for querying the analytics data for a project, such as analytics for teams or work items, as shown below.

SELECT count(TeamId) as noOfTeams FROM [Project_dev].[Analytics].Teams

SELECT sum(CompletedWork) as SumOfCompletedWork, sum(RemainingWork) as SumOfRemainingWork FROM [Project_dev].[Analytics].WorkItems