Forum Discussion

oconorj_86's avatar
oconorj_86
Frequent Visitor
4 years ago
Solved

Filtering data on import from ElasticSearch

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 🙂

Need Help

2 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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's avatar
      oconorj_86
      Frequent Visitor
      4 years ago

      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 🙂