New

Power BI Connector: Cannot filter on external id when using push-down filtering to load Sequences into CDF

Forum|Forum|1 year ago
September 9, 2024
4 replies
52 views

Anders Brakestad
Seasoned

We use push-down filtering to get our relevant data from CDF into Power BI. We filter on the external ID property, since this is constant across our environments in CDF. Here is an example of that this looks like:

Source = Cognite.Contents(cdf_env & "/Timeseries/?$filter=ExternalId eq 'an_external_id'", null)

This works great for Time Series, but doing the same thing for Sequemces leads to an error:

DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed: 
OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request. (The provided filter was invalid.)
OData Version: 4, Error: The remote server returned an error: (400) Bad Request. (The provided filter was invalid.)
OData Version: 3, Error: The remote server returned an error: (400) Bad Request. (The provided filter was invalid.)
Details:
    DataSourceKind=Cognite
    DataSourcePath={"project":"akerbp-dev\/Sequences?$filter=ExternalId eq 'val_alarms_top_10_weekly'"}

Push-down filtering on the external id seems to be supported based on the information here; Am I doing something wrong? Is there maybe a bug somewhere?

Filtering on the Name attribute does work, and it keeps us going for the moment, but I don’t like that the Name is not a unique identifier of the object.

Thanks for any help and advice! :)

Everton Colling
Seasoned Practitioner
Forum|Forum|1 year ago
September 9, 2024

Hi Anders,

Thank you for bringing this issue to our attention. You're correct that there seems to be a discrepancy between the documented functionality and the actual behavior.

Push-down filtering on externalIDs for sequences is indeed not currently implemented in our OData service. This limitation exists due to historically low usage and lack of customer requests. We'll update our public documentation to make sure this limitation is highlighted.

While we don't currently support filtering on exact external ID for sequences, here are some alternatives you can use:

Retrieve by internal ID: Sequences(1546153672296696)
Use 'startswith' for external ID: Sequences?$filter=startswith(ExternalId, 'an_external_id')
Filter by ID: Sequences?$filter=Id eq 1546153672296696

If server side filtering by external ID on sequences is an important feature for your use case, we can transform this into a Product Idea and monitor community interest to potentially include it in upcoming updates.

Like

Anders Brakestad
Author
Seasoned
Forum|Forum|1 year ago
September 10, 2024

Thanks for the fast feedback!

We prefer to filter on the external id since it remains the same across all environments in CDF. We can indeed use the internal ids, but this will need more logic/config setup. Additionally, when we redeploy our solution we delete and recreate the resources in CDF, and so the internal id will be different each time, while the external id is defined explicitly in our project config.

We will consider if the `startswith` filter on external id is better than our current `name` filter for our case.

I would appreciate if we can add this as a Product Idea. Should I do this?