OData filter pass-through

[jamesf]

I have found, through experimentation, that setting filters on properties annotated with Org.OData.Capabilities.V1.FilterRestrictions.NonFilterableProperties results in Power BI filtering the data locally, whereas filterable properties result in a "$filter=" query being passed to the source service. However, Power BI appears to ignore other FilterRestrictions annotations, such as RequiresFilter and RequiredProperties.

Is there any documentation or reference sample to explain the extent of annotation support for OData v4 services, particulary the Org.OData.Capabilities.*  and Org.OData.Core.* annotations?

Any help would be appreciated.

[v-qiuyu-msft]

Hi @jamesf,

I got the information from PG that

"OData filter are not supported in Power BI

I know it’s working in PBI Desktop, but it will break on the service.

The solution is to Filter from the Power Query editor that generate a call to the function Table.SelectRows:

https://msdn.microsoft.com/en-us/library/mt260810.aspx

As to RequiresFilter and RequiredProperties, we don’t currently have any plans to support those. Please file a feature request at ideas.powerbi.com so that we can track interest."

Best Regards,
Qiuyun Yu

[jamesf]

Hi

Thanks for the reply!

I am not using OData filters explicitly (in the URL). I set filters in the UI and they are translated into Table.SelectRows(). The difference is that some filters are passed through to my OData service while others are not. This appears to be dicated by the NonFilterableProperties annotation, so Power BI must respect those annotations. I have verified that this behaviour works in the same way using both Power BI desktop and the service (via an enterprise gateway to my on-premises OData endpoint).

As an example for anyone who is interested, here is a snippet of the OData metadata describing my Documents entity set, with filter restrictions:

<EntitySet Name="Documents" EntityType="ns.Document">
  <Annotation Term="Org.OData.Capabilities.V1.FilterRestrictions">
    <Record>
      <PropertyValue Property="Filterable" Bool="true"/>
      <PropertyValue Property="RequiresFilter" Bool="true"/>
      <PropertyValue Property="RequiredProperties">
        <Collection/>
      </PropertyValue>
      <PropertyValue Property="NonFilterableProperties">
        <Collection>
          <PropertyPath>Description</PropertyPath>
        </Collection>
      </PropertyValue>
    </Record>
  </Annotation>
</EntitySet>

If I filter Documents in the Power BI query editor (using the standard checkbox filter) on any property other than Description (which is annotated as non-filterable), the filter is passed through.

M:

let
    Source = OData.Feed("http://serviceurl/data/V1"),
    Documents_table = Source{[Name="Documents",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Documents_table, each ([VersionId] = 1))
in
    #"Filtered Rows"

Query recieved by my OData service:

/data/V1/Documents?$filter=VersionId eq 1

However, if I do the same for the non-filterable Description, I get the following.

M:

let
    Source = OData.Feed("http://serviceurl/data/V1"),
    Documents_table = Source{[Name="Documents",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Documents_table, each ([Description] = "Texas"))
in
    #"Filtered Rows"

Query recieved by my OData service - no filter specified:

/data/V1/Documents

You'll note that the M query is equivalent but the resulting OData query differs: in the second example, all the data will be retrieved and filtering will take place on the Power BI side. This is expected behaviour owing to the Org.OData.Capabilities.V1.FilterRestrictions annotation but is worth understanding, since the consequences of filtering on a non-filterable property can have a huge impact on the volume of data that is retrieved.

My question is whether there is any documentation or reference to indicate which annotations are supported and to what extent, given that some of the FilterRestrictions annotations appear to be respected while others are not. There are also similar annotations such as SortRestrictions, which I have not investigated. I was hoping to find the capabilities of the OData feed written down somewhere, to save me the trial and error  Does anything like that exist?

I will file a feature request for support of other annotations, as suggested.

Thanks for your time.

[jamesf · Accepted solution]

For the sake of completeness and in the absence of any formal reference material, I have done a bit more testing to confirm that both the NonFilterableProperties and NonSortableProperties annotations are respected when Power BI queries OData sources. If properties of the OData entity sets are marked with these annotations, then Power BI will bring back all the data and perform the filtering and / or sorting locally rather than passing through $filter and / or $orderby query options to the source.

It is worth noting that if some of the properties involved in a filtered are annotated as non-filterable, then the whole filter will be applied locally. You can account for this by restructuring your queries to perform filtering in multiple steps.

Here is a complete example using a simple entity call with 2 identical properties, Prop1 and Prop2. Prop1 has been annotated as both non-filterable and non-sortable. This appears in the metadata as:

<EntitySet Name="Projects" EntityType="sample.Project">
  <Annotation Term="Org.OData.Capabilities.V1.FilterRestrictions">
    <Record>
      <PropertyValue Property="Filterable" Bool="true"/>
      <PropertyValue Property="RequiresFilter" Bool="true"/>
      <PropertyValue Property="RequiredProperties">
        <Collection/>
      </PropertyValue>
      <PropertyValue Property="NonFilterableProperties">
        <Collection>
          <PropertyPath>Prop1</PropertyPath>
        </Collection>
      </PropertyValue>
    </Record>
  </Annotation>
  <Annotation Term="Org.OData.Capabilities.V1.SortRestrictions">
    <Record>
      <PropertyValue Property="Sortable" Bool="true"/>
      <PropertyValue Property="AscendingOnlyProperties">
        <Collection/>
      </PropertyValue>
      <PropertyValue Property="DescendingOnlyProperties">
        <Collection/>
      </PropertyValue>
      <PropertyValue Property="NonSortableProperties">
        <Collection>
          <PropertyPath>Prop1</PropertyPath>
        </Collection>
      </PropertyValue>
    </Record>
  </Annotation>
</EntitySet>

Querying the filterable, sortable property yields the following Power BI query and resultant OData request:

let
    Source = OData.Feed("http://myserver/Data/V1"),
    Projects_table = Source{[Name="Projects",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Projects_table, each ([Prop2] = "Proj1" or [Prop2] = "Proj2" or [Prop2] = "Proj3")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Prop2", Order.Descending}})
in
    #"Sorted Rows"

http://myserver/Data/V1/Projects?$filter=Prop2 eq 'Proj1' or Prop2 eq 'Proj2' or Prop2 eq 'Proj3'&$orderby=Prop2 desc

On the other hand, querying the non-filterable, non-sortable property yields the following Power BI query and resultant OData request:

let
    Source = OData.Feed("http://myserver/Data/V1"),
    Projects_table = Source{[Name="Projects",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Projects_table, each ([Prop1] = "Proj1" or [Prop1] = "Proj2" or [Prop1] = "Proj3")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Prop1", Order.Descending}})
in
    #"Sorted Rows"

http://myserver/Data/V1/Projects

Note that the query (M code) is the same but the resultant OData URL is quite different. In the second case, Power BI is requesting the entire Projects entity set so that it can perform the filtering and sorting on the returned data.

To the end user, the outcome of these queries would be identical but, if the entity set was very large, the time taken to load the data could be much longer in the second case.

This behaviour is expected, since Power BI respects the NonFilterableProperties and NonSortableProperties annotations.

With this knowledge, it is possible to structure your queries in such a way that you make the most of the native OData query options that are passed through to the source.

[anece]

@jamesf - thanks for digging out the information.
I'm a bit lost. How do you set up the filters in Power Query?

Annotation Term="Org.OData.Capabilities.V1.FilterRestrictions"

[jamesf]

The annotations that restrict filtering, sorting, etc. are defined by the source OData service. Power BI reads them from the OData metadata document, typically available at the root of the service URL e.g. https://services.odata.org/TripPinRESTierService/$metadata.

The filters in my example above were built in the Power BI query editor (using the standard checkbox filter) but could equally be written directly as M queries in the Advanced Editor.

Note that this investigation was done a few years ago so Power BI and it's OData connector may have moved on since then. I know that Power BI does a lot "behind the scenes" to convert from the UI query builder to the raw OData request, which is not always obvious.