ODBC Driver Wildcard

beerygaz · November 1, 2019, 3:00pm

I'm trying to access elasticsearch data in PwoerBi using the ODBC driver. It seems I can't use a wild-card to consolidate my indexes.

For example I have filebeats-yyy.mm.dd indexes and I want to visualise data across filebeats-* - instead I have to select each index individually and it creates discrete data sets in PowerBi.

beerygaz · November 7, 2019, 10:45am

Poor form to bump your own post, but this is becoming a major stumbling block for or architecture. If we can't query across indexes using ODBC it's a major issue.

bogdan.pintea · November 8, 2019, 4:41am

Hi @beerygaz,
What's the error you get? The driver simply pipes the SQL statement through as is in vast majority of the cases, so if it's properly formatted, it should just work.
Nevertheless I will look further into it next week, just in case, but alternatively, wouldn't defining a pattern alias on those indices work for you?
Regards,
Bogdan.

Christian_Dahlqvist · November 8, 2019, 5:56am

I have not used the ODBC driver, but recall seeing posts around the JDBC driver requiring quotation marks around the index pattern to work. Might this apply to the ODBC driver as well? Exactly what does your query look like?

beerygaz · November 12, 2019, 5:42am

That was the missing piece. A pattern alias was what I was missing.

However, when I create an pattern alias I get the following error:

Before I go ahead and adjust this setting, are there any negative impacts it may have? I'm guessing it was set thus for a reason?

beerygaz · November 12, 2019, 5:43am

I have also just read that I can't use the ODBC drivers for Power Bi Web/Online reports, only in the desktop version. So back to the drawing board to try and get a JSON Web service to work.

bogdan.pintea · November 12, 2019, 9:07am

The online documentation reads: Doc-value fields are costly since they might incur a per-field per-document seek, so I believe the setting could have a performance impact.
If you wouldn't need all the 300+ fields, you could also perform a custom query with Power BI to only fetch those fields relevant for your report.

bogdan.pintea · November 12, 2019, 9:13am

I have also just read that I can't use the ODBC drivers for Power Bi Web/Online reports, only in the desktop version.

The Power BI Gateway might be of help in this case.
We might look into a dedicated connector at some point, but currently these - using the Desktop version or potentially the Gateway - are the only options available.

system · December 10, 2019, 9:14am

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.