VSTS odata refresh issue

[gaccardo]

We are reading data from the VSTS odata source.  Because the queries return too much data, we create a table of

date values, then use these date values to limit the scope of the query.  Everything works perfectly well in Power BI

desktop, but once published, it will not refresh.

This query generates a table of date values and issues a single VSTS.Feed call, ignoring the date table values completely.

It refreshes with no issues.

let
    // Here are some Date functions
    now = DateTimeZone.UtcNow(),
    table = Table.FromRows({{0},{-30},{-60},{-90},{-120},{-150},{-180}},{"DaysBack"}),
    Stop = Table.AddColumn(table, "Stop", each  Date.ToText(Date.From(Date.AddDays(now,[DaysBack])),"yyyyMMdd")),
    Start = Table.AddColumn(Stop, "Start", each  Date.ToText(Date.From(Date.AddDays(now,[DaysBack]-29)),"yyyyMMdd")),

    // Here is a call to a VSTS data source
    URL = "https://OurAccount.analytics.visualstudio.com/DefaultCollection/OurProject/_odata/WorkItemSnapshot?$select=DateSK,WorkItemId,State,WorkItemType&$filter=WorkItemType eq 'Bug' and DateSK gt 20180521",
    VSTS =  VSTS.Feed(URL)
in
    VSTS

However, if we actually use the date table and run multiple queries, it won't refresh it at all after it is published.

let
    // Here are some Date functions
    now = DateTimeZone.UtcNow(),
    table = Table.FromRows({{0},{-30},{-60},{-90},{-120},{-150},{-180}},{"DaysBack"}),
    Stop = Table.AddColumn(table, "Stop", each  Date.ToText(Date.From(Date.AddDays(now,[DaysBack])),"yyyyMMdd")),
    Start = Table.AddColumn(Stop, "Start", each  Date.ToText(Date.From(Date.AddDays(now,[DaysBack]-29)),"yyyyMMdd")),

    //Combine the URL with the contents of the data function.
    BaseURL = "https://OurAccount.analytics.visualstudio.com/DefaultCollection/OurProject/_odata/WorkItemSnapshot?$select=DateSK,WorkItemId,State,WorkItemType&$filter=WorkItemType eq 'Bug' ",
    AddURLColumn = Table.AddColumn(Start, "URL", each BaseURL & " and DateSK ge " & [Start] & " and DateSK le " & [Stop]),
    AddSource = Table.AddColumn (AddURLColumn, "Source", each VSTS.Feed([URL]))
in
    AddSource

There are two errors that occur, with variations of the above query.  One error given is "You can't schedule refresh for this dataset because one or more sources currently don't support refresh." The other is: 

   Something went wrong

   Failed to retrieve the parameters.
   Please try again later or contact support. If you contact support, please provide these details.

   Activity ID 63ac0f7b-8018-3384-9f78-394f0519d8c2

   Request ID 6d963c31-cc99-409c-962e-09701e0aa20b

   Correlation ID ddf612c1-13e1-9144-16c1-2cc891704a77

   Status code 404

Any help resolving this issue would be greatly appreciated.

Best Regards,

G

[ebruseiwert]

Hi,

Is there an update on this? I am also having issues with Power BI dataset not being refreshed from VSTS Azure. I'm not getting any errors, but it's not refreshing either. I'm connecting to VSTS with OAuth2. 

Thanks!

[gaccardo-slb]

I posted the message here, on StackOverflow, and logged a bug with Microsoft.  The VSTS.Feed() function ignores the second parameter as best I can tell.  I gave up trying to work with this data and moved on to other projects.

[arsaveli]

Hi,

Some more information :

PowerBI has a limitation where it will not allow user to modify the URL due to the way security scopes work.
The following two articles go into detail of why this happens and how to work around it using query parameters and RelativePath parameter - which VSTS.AccountContents also allows you to pass.

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...
and
https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

[gaccardo]

Thank you for the assistance @arsaveli.  I posted this as a bug here, and got the response you just passed on.  However, there

are still multiple problems to solve.  First, the blog post does not show anything about VSTS.Feed.  Using the code below as a

starting point for a function should work.  However, VSTS.Feed ignores the parameters.  I've tried it with multiple projects and

I either get too many results (the initial problem), or for smaller projects, I get every field instead of just the ones selected.

let
    BaseURL = "https://server.analytics.visualstudio.com/DefaultCollection/project/_odata/WorkItemSnapshot",
    Select = "DateSK,WorkItemId,State,WorkItemType",
    Filter = "WorkItemType eq Bug and State ne Closed and State ne Removed and DateSK ge 20180517 and DateSK le 20180615",
    Source =  VSTS.Feed(BaseURL, [Query=[select=#"Select",filter=#"Filter"]])
in
    Source    

The second problem is that none of these parameters to VSTS.Feed() are documented.  There are no examples.  This has even

been requested by @ericleigh007 in this post over a year ago. The same is true for OData.Feed().

Overall, without working examples of VSTS.Feed and VSTS.AccountContents that can actually refresh, the basic problem

still remains.

[gaccardo]

One bit of information that I find interesting is that if I change VSTS.Feed([URL]) to VSTS.Contents([URL]), the dataset will refresh

with no issues.  However, the values returned by VSTS.Feed() are binary and much more difficult to transform into useful rows and

columns.

[arsaveli]

Hi,

Looks like you are trying to view work item snapshots monthly. Have you tried new Analytics views? It has a feature to return history (which is what workitemsnapshot is) at monthly intervals.

[gaccardo]

The data sources shown are from the analytics view feature.  We are accessing it via the odata feed using the VSTS.Feed() method to get complete work item histories over a 6 month period.  The reason for multiple queries is because a single query with 6 months of data returns >300K rows.  

As best I can tell, the problem seems to be an issue in the VSTS.Feed() method itself. 

• A single call to VSTS.Feed() in a query works properly in apps.powerbi.com.
• Multiple, sequential calls to VSTS.Feed() in a query work
• Multiple calls to VSTS.Feed() in an each loop cannot be refreshed in apps.powerbi.com.  They fail as shown
  in the original message.
• Using VSTS.Contents() in an each loop refreshs without issue.  However, this method is deprecated, doesn't return
  all of the data (it provides a continuation token), and also returns a binary object which is harder to process.
• Using VSTS.AccountContents()--which replaces VSTS.Contents()--fails the same way that VSTS.Feed() does.
• Using OData.Feed()--which was replaced by VSTS.Feed()--fails the same way that VSTS.Feed() does.  It also
  doesn't return all of the data, but instead returns a continuation token.

Given the number of options provided to run a query, it would be nice if one of them worked.

[arsaveli]

There are two things going on here:

The reason VSTS.Feed is breaking is because of dynamic URL generation and how that works with PowerBI - PowerBI has data source code analysis that doesn't support this kind of flow.

You are also trying to download complete WorkItemSnapshot data via this method and aggregate it in Powerbi.

VSTS specifically blocks complete WorkItemSnapshot download for performance reasons:

https://docs.microsoft.com/en-us/vsts/report/extend-analytics/odata-query-guidelines?view=vsts#odata...

You should instead use Analytics views - which will pre-aggregate your data on VSTS side.