Hello there,
I'm creating this post in response to a problem I'm currently encountering on a project.
The objective of my project is to retrieve analytical data from Azure DevOps in order to have a report pooling the analysis of several projects.
I've tried to retrieve the data through an OData query via the OData Feed connector, but I'm encountering several problems:
I can't query the projects one by one, because I'd have to create 20 tables for 20 http requests, and this could change over time, with some projects being removed or added.
I can't query the organization by filtering on the 20 projects either, because a query requires clauses such as “ProjectName eq ‘Project1’ or ProjectName eq ‘Project2’” etc. and doesn't support the IN clause that would be used in SQL Same problem as for 1
My solution was a function that would query the various projects with the name of the organization and the name of the project concerned as parameters. The problem is that it causes a refresh error: “This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed.” in Power BI Service because the source is dynamic, Power BI Service can't handle these variations automatically (I've just discovered this).
I'm trying to implement the solution here: https://www.linkedin.com/pulse/retrieve-data-from-multiple-azure-devops-microsoft-van-der-vorst but it looks like I'll have to hard-code the names of the organizations concerned, and I'm not sure what the result will be for the moment.
My questions are as follows:
- Is a Power BI Gateway the answer to this problem? I haven't had a chance to see how it works.
- Is there another way of solving this problem?
Knowing that for the required criteria:
-for the moment, the projects are in a single organization, but there may be several over time
-for the moment, we're recovering around twenty projects, but this may increase or decrease over time
-only certain AreaPaths are retrieved for each project
-we apply filters by retrieving Azure DevOps WorkItems that have certain types, certain status and preferably over a year's worth of data
