OData Performance Improvements In The June 2018 Power BI Desktop Release

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:

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"

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”.

image

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:

image

However, if you change the code above so that the OData.Feed() function uses the Implementation=”2.0” option like so:

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"

…then Fiddler shows a completely different request made:

image

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.

12 responses

  1. 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!

  2. 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…

  3. 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”.

  4. 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?

  5. Pingback: Analysis Services Import Performance Improvements In The August 2018 Release Of Power BI « Chris Webb's BI Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: