Filtering data on import from ElasticSearch

[oconorj_86]

Hi,

For a new project we are trying to pull data from ElasticSearch into Power Bi. The import is going very slow (I believe thanks to the VPN) and after a couple of hours we get a time-out error (roughly when we het 10M+ records)

As the table in ElasticSearch is holding roughly 18M records I was thinking of filtering out the records older than 6 months from this table as I don't see myself spending hours every week refreshing the datasource. As you can see me coming .. wondering if there is a way to connect to ElasticSearch via the ODBC and filter before we start importing like we can do with a SQL statement.

Any suggestion welcome here 🙂

[v-chenwuz-msft · Accepted solution]

Hi @oconorj_86 ,

Can you try to use advanced options when you use the ODBC connector.

Or check out this article to see if it's helpful to you.

Connect Elasticsearch to Power BI: An Easy Guide 

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[oconorj_86]

Hi,

I tried this a week or so ago and kept giving me errors as the ElasticSearch didn't accept the SQL language.
I mentioned this to the tech team and they looked into this. looks like they needed to do something their end to accept the SQL query. Fast forward to this morning and I was able to filter the data when importing 🙂

Marked this as the solution. If people get same error as me, get in contact with the ElasticSearch team in your company and ask them to enable the SQL Query DSL.

Thanks 🙂