Ideas for improve loading times

[TurinTurambar]

Problem
The slowest loading tables seem to be "WorkItems Bl0cked" and "WorkItems Daily Bl0cked Count". I believe this is related to the sheer amount of data that has to be acted on when transformations are done directly to the WorkItemRevisions endpoint. Specifically this is happening due to the "Removed Columns" action right after the OData feed. This action is just selecting the only 6 columns needed in both tables because the WorkItemRevisions endpoint returns a lot of more columns (aka more data). This is not needed since the endpoint accepts a column selection parameter.

Solution
In both tables instead of querying the endpoint with a filter, do it with a select and a filter and then remove the subsequent Removed Columns (select columns) action.
Instead of:

(...)/WorkItemRevisions?$filter=(...)

Do:

(...)/WorkItemRevisions?$select=WorkItemId,Title,AreaSK,Blocked,CycleTimeDays,ChangedDate,ChangedDateSK,TagNames,CompletedDateSK,CompletedDate&$filter=(...)

This removes the need for a subsequent select action in the BI side.

I've tested this approach and it cuts loading time for those tables in about 75%

[nbrown02]

Thank you! I'm away at the minute but will test this on a new version once I'm back 👍🏼👍🏼👍🏼

[nbrown02]

Hi there - I just started playing around with this and also remembered why I designed it the way I did :)

'Blocked' is not available in any projects with an 'Agile' process template - so it actually throws an error using the query you suggest:

So I don't think this suggestion will work...

[nbrown02]

Closing as suggestion would mean solution would be incompatible with users on the 'Agile' process template

[TurinTurambar]

That's interesting. I'm using a custom process derived from agile. I've added no "Blocked" property to it and yet, it works for me.

This is my full query

let
    Source = OData.Feed(#"Http/Https" & "://" & #"Analytics URL/Azure DevOps Server" & "/" & Organization & "/_odata/v3.0-preview/WorkItemRevisions?$select=WorkItemId,Title,AreaSK,Blocked,CycleTimeDays,ChangedDate,ChangedDateSK,TagNames,CompletedDateSK,CompletedDate&$filter=StateCategory%20ne%20%27Proposed%27%20and%20WorkItemType%20ne%20%27Issue%27%20and%20WorkItemType%20ne%20%27Task%27%20and%20WorkItemType%20ne%20%27Test%20Case%27%20and%20WorkItemType%20ne%20%27Test%20Plan%27%20and%20WorkItemType%20ne%20%27Shared%20Parameter%27%20and%20WorkItemType%20ne%20%27Shared%20Steps%27%20and%20WorkItemType%20ne%20%27Test%20Suite%27%20and%20WorkItemType%20ne%20%27Impediment%27%20and%20WorkItemType%20ne%20%27Epic%27%20and%20WorkItemType%20ne%20%27Feature%27%20" & "and ChangedDate ge 2020-06-01Z", null, [Implementation="2.0"]),
    #"Kept Duplicates" = let columnNames = {"WorkItemId"}, addCount = Table.Group(Source, columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(Source, columnNames, removeCount, columnNames, JoinKind.Inner),
    #"Sort Items" = Table.Sort(#"Kept Duplicates",{{"WorkItemId", Order.Ascending}, {"ChangedDate", Order.Ascending}})
in
    #"Sort Items"

Here's a screenshot of it refreshing

Here's the refresh stamp

No issues at all and I have my data

This is the exact same query running from Postman, note the results are coming through without error

I also have no project in my organization using CMMI

Any ideas on what else might be different?

[nbrown02]

Let me have a look and get back to you (reopening)

[nbrown02]

Ah ok - looks like I've got it working now 👍

[nbrown02]

Fixed in the latest version - thank you again :)