Unable to filter rows with Athena ODBC data source

[nick_qx]

I'm using the Athena ODBC data source with the Simba Athena ODBC driver. In general this is all working well. I can create + publish reports and do scheduled updates via the On-prem data gateway. However I'm unable to filter rows by value which is maddening as I have to pull-down all the data and then filter in my reports which quickly leads to timeouts etc.

My table in the Edit Queries window looks like this

I wish to select only rows with event_id 303, so I do this.

All looks good and as expected in the newly refreshed preview.

However, when I click "Close & Apply" I get this (every time).

This can't be a limitation of Athena, as I can happily write queries along the lines of SELECT * WHERE event_id = 303 in the Athena query editor. And of course I can filter by row value happily with other data sources (eg. MySQL). I'm assuming it is some issue with the Simba ODBC driver?

Can anyone suggest a way to fix this? Or, failing that a work around that will let me achieve the same goal of filtering rows to limit the amount of data being pulled down?

Thanks!

[Anonymous]

Hi @nick_qx  & @Abdelh  One question....how could you scheduled the updates via the On-prem data gateway using AWS Athena (from app.powerbi.com)

Im trying to do the same...but the connection string to properly work  should be something like:

Driver=Simba Athena ODBC Driver;AwsRegion=us-east-2;S3OutputLocation=s3://bucket_name-xx;AuthenticationType=IAM Credentials

But seems that the connection string required is taken directly from the Simba ODBC driver...so it's something like: dsn=Sample Simba Athena ODBC DSN 

So at the end of the day it doest work...because it needs the full conexion string but the report its uploaded with the "short one" not the "full one". And also the conextion string on app.powerbi.com it's not editable

Thanks

[rafaelruivo]

How are you trying to add this connection to the gateway? I configured mine after uploading the report that contained it, that is, went into the configurations of the datasource and added it to the gateway when prompted.

For the life of me, I can't understand why we still need a gateway for two cloud services to talk to each other, that drives me crazy!

[Abdelh · Accepted solution]

I would try a work around by filtering the data at the source (get data screen). so when you connect to PBI, under advanced options connect by using "SQL statement" - enter a query with where Clause filtering by event_ID 303.

[nick_qx]

Worked great, thankyou!