In the June 2018 release of Power BI Desktop there were a number of improvements made to the way the Power Query engine handles OData data sources. You can read about them here:
https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-june-2018-feature-summary/#oData
However, while testing them out, I noticed one important point that the announcement didn’t make: an existing Power Query query will only benefit from these changes if you make a small change to its M code, adding the Implementation=”2.0” option to the OData.Feed() function.
Take the following M query, running on the UK Houses of Parliament OData API:
[sourcecode language=’text’ padlinenumbers=’true’]
let
Source = OData.Feed(“https://api.parliament.uk/odata”),
GovernmentOrganisation_table =
Source{[
Name=”GovernmentOrganisation”,
Signature=”table”
]}[Data],
#”Expanded GroupHasPosition” =
Table.ExpandTableColumn(
GovernmentOrganisation_table,
“GroupHasPosition”,
{“PositionName”},
{“PositionName”}),
#”Filtered Rows” =
Table.SelectRows(
#”Expanded GroupHasPosition”,
each [PositionName] = “Chancellor of the Exchequer”)
in
#”Filtered Rows”
[/sourcecode]
It queries a table called GovernmentOrganisation, expands a column called GroupHasPosition and then filters on one of the expanded columns, PositionName, to only return the rows where PositionName contains the text “Chancellor of the Exchequer”.
Using Fiddler in the way I describe here, I can see that when this query runs the engine first tries to fold the filter on “Chancellor of the Exchequer” and then when this request returns an error, it defaults to a very slow approach that involves making multiple requests to the API:
However, if you change the code above so that the OData.Feed() function uses the Implementation=”2.0” option like so:
[sourcecode language=’text’ padlinenumbers=’true’ highlight=’2,3,4,5′]
let
Source = OData.Feed(
“https://api.parliament.uk/odata”,
null,
[Implementation=”2.0″]),
GovernmentOrganisation_table =
Source{[
Name=”GovernmentOrganisation”,
Signature=”table”
]}[Data],
#”Expanded GroupHasPosition” =
Table.ExpandTableColumn(
GovernmentOrganisation_table,
“GroupHasPosition”,
{“PositionName”},
{“PositionName”}),
#”Filtered Rows” =
Table.SelectRows(
#”Expanded GroupHasPosition”,
each [PositionName] = “Chancellor of the Exchequer”)
in
#”Filtered Rows”
[/sourcecode]
…then Fiddler shows a completely different request made:
In this case it does not try to fold the filter, but it at least does the expansion in a single request. The performance of the resulting query is a lot better.
It looks like all new code generated by the Power Query Editor uses the Implementation=”2.0” option for OData.Feed() so it will get the benefits described in the post on the Power BI blog. Existing M code won’t have this option set though so you will need to update it appropriately. As always you should test thoroughly after making these changes to make sure you do indeed make your query run faster and get the same behaviour as you had before.
Before these changes I did not notice any performance improvements in the ODataFeed, which corresponds with your blog.
Now that i have applied these changes I see an extreme performance improvements, from waiting 20 minutes for a query to load to just a couple of seconds! It’s not as fast as a proper back-end, but most definitely a great improvement! Thank you for the helpful post!
Hey Chris, what do you think the odds are that Microsoft would open source some (all?) of their connectors?
I’ve developed several custom M connectors but there’s so little best-practice guidance that I feel my work may be way off the mark. I mean, the samples on Git can only take you so far…
I don’t know. I suppose there’s a chance that some of the code will be open sourced, and I agree the more samples we can get the better.
Does this also apply when using the Dynamics 365 connector?
I don’t know, sorry
Anyone else notice that their Functions disappear from the lookup selection list in PowerBi when connecting with [Implementation=”2.0″]??? Removing this option from the source through Advanced Settings allowed them to re-appear?
Source = OData.Feed(“https://XXXX.com”, null, [Implementation=”2.0″])
Changed to…
Source = OData.Feed(“https://XXXX.com”, null)
One of the changes in the 2.0 implementation is that we no longer infer top-level functions from the metadata document; they must now be present in the service document to be listed in the navigation table. This is to avoid the current ambiguity which exists for generating the corresponding URL.
The website for OData proposals had this question come up, but the resolution is unclear. The closest to that is this quote from Mike Pizzo: “the service document should fully represent the information that is needed to navigate the results without applying any convention beyond the conventions defined for a service document in atompub”.
Thanks Curt for the reply and sorry for the delay.
As I understand it from MS functions can’t appear in the service document? which basically restricts the end user from choosing these in their UI.
See thread;
http://community.powerbi.com/t5/Issues/After-upgrade-Odata-functions-are-missing-in-connector/idi-p/453753
I’m a C# developer using the ODataConventionModelBuilder, Does anyone have any suggestions for a workaround? Thanks.
The new OData parser (Implementation=”2.0″) unfortunately ignores the filter restrictions given in $Metadata
See details: https://community.powerbi.com/t5/Issues/New-OData-parser-Implementation-quot-2-0-quot-ignores-the-filter/idi-p/459916
Update:
Occurs for fields with “space” in the field name.
New OData parser (Implementation=”2.0″) ignores the filter/expand restrictions.
The new OData feed implementation in PowerBI (Excel in production, after last update) doesn’t filter/expand the results using $expand system query.
Example:
Problem of implementation=”2.0″
Query:
——-
let
Source = OData.Feed(“https://api.parliament.uk/odata”, null, [Implementation=”2.0″]),
GovernmentOrganisation_table = Source{[Name=”GovernmentOrganisation”,Signature=”table”]}[Data],
#”Expanded GroupHasPosition” = Table.ExpandTableColumn(GovernmentOrganisation_table, “GroupHasPosition”, {“LocalId”, “PositionName”, “PositionHasIncumbency”, “PositionHasGroup”}, {“GroupHasPosition.LocalId”, “GroupHasPosition.PositionName”, “GroupHasPosition.PositionHasIncumbency”, “GroupHasPosition.PositionHasGroup”})
in
#”Expanded GroupHasPosition”
Background Query send to server:
—————-
/odata/GovernmentOrganisation(‘NDZSYjeE’)/GroupHasPosition?$top=1000
After removed the [Implementation=”2.0″] section in OData.Feed
Query:
——-
let
Source = OData.Feed(“https://api.parliament.uk/odata”,null),
GovernmentOrganisation_table = Source{[Name=”GovernmentOrganisation”,Signature=”table”]}[Data],
#”Expanded GroupHasPosition” = Table.ExpandTableColumn(GovernmentOrganisation_table, “GroupHasPosition”, {“LocalId”, “PositionName”, “PositionHasIncumbency”, “PositionHasGroup”}, {“GroupHasPosition.LocalId”, “GroupHasPosition.PositionName”, “GroupHasPosition.PositionHasIncumbency”, “GroupHasPosition.PositionHasGroup”})
in
#”Expanded GroupHasPosition”
Background Query send to server:
—————-
GET /odata/GovernmentOrganisation?$expand=GroupHasPosition(%24select%3DLocalId%2CPositionName%2CPositionHasIncumbency%2CPositionHasGroup)
Can you please install the latest version of Power BI or excel update and check the above?
On PowerBI Report Server you might get an error when using odata with Implementation=”2.0″ on scheduled refreshes:
SessionID: 1b20ebb2-589c-4861-8fb7-63b065774c36
[0] -1055784932: File or Folder: Could not load file or assembly ‘Microsoft.OData.Core.NetFX35.V7, Version=7.4.0.11102, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies. The system cannot find the file specified.. The exception was raised by the IDbCommand interface.
[1] -1055129594: The current operation was cancelled because another operation in the transaction failed.
[2] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.
However, without using Implementation=”2.0″ this works fine…
I am filtering on ODATA as you suggested above but I use &Cross-company=true to return all the data. It is extremely slow because it brings in all the data before I can filter on just the last 3 days. If I add a dynamic portion to the source then I can’t schedule a refresh in the service. What ODATA filter can I use to grab only the last 3 days for all companies?