Timestream ODBC and Power BI Connector - DirectQuery Sending a Malformed SQL Query

Question

The issue I am facing is when trying to apply a *time* filter or slice from Power BI on a Timestream table via Direct Query. When selecting time as the attribute to filter on, I am greeted with the following image:

![Image showing a Power BI error on a visualization when trying to apply a time filter]
(/media/postImages/original/IMs0gDJYLwQRqQUIE6Q7WLUg)

Looking into the logs for the Timestream ODBC driver, I found this error:

```
ERROR MSG:  diagnosable_adapter.cpp:36 timestream::odbc::diagnostic::DiagnosableAdapter::AddStatusRecord: Adding new record: AWS API Failure: Failed to execute query

...
 where "C1" < {ts '2025-04-01 12:00:00'} and "C1" >= {ts '2025-04-01 00:01:01'}
...
```

Running the exact same query in the Timestream console with the "{ts ...}" escape sequence removed returns a valid result.

Could this be a misconfiguration of the ODBC driver from my end? Or a potential problem with the custom connector or driver itself? Has anyone else seen this issue?

I am able to successfully connect the Timestream table to Power BI via the custom connector for Power BI. The preview of the data and all of the columns from the table show up in Power BI. Additionally, I can successfully retrieve summarized data via filtering (as long as I don't filter by *time*), so I know there are no issues with the actual connection between Timestream and Power BI.

Any help or advice would be greatly appreciated!
Thank you!

re:Post Agent · Answer

The issue you're experiencing seems to be related to how the Timestream ODBC driver and Power BI are handling time-based queries in DirectQuery mode. This appears to be a compatibility problem between the SQL syntax generated by Power BI and the SQL syntax expected by Timestream.

The error message you've shared indicates that Power BI is generating a SQL query with a timestamp format that Timestream doesn't recognize. Specifically, the {ts '...'} syntax is not compatible with Timestream's query language.

This is likely not a misconfiguration on your end, but rather a limitation or bug in how the Timestream ODBC driver interprets queries generated by Power BI in DirectQuery mode.

To work around this issue, you might consider the following options:

1. Use the Import mode instead of DirectQuery for your Timestream data in Power BI. While this may not be ideal for larger datasets, it could allow you to apply time-based filters without encountering this error.

2. Create calculated columns or measures in Power BI that transform the time data into a format that doesn't trigger this error when filtered.

3. If possible, pre-aggregate your data in Timestream to reduce the need for time-based filtering in Power BI.

4. Contact AWS support to report this issue, as it may be a bug in the Timestream ODBC driver that needs to be addressed.

Given that you're able to successfully connect and retrieve data for other types of queries, it's likely that your overall setup is correct. The problem appears to be specific to time-based filtering in DirectQuery mode.

If none of these workarounds are suitable for your use case, you may need to wait for an update to the Timestream ODBC driver or the Power BI connector that addresses this compatibility issue.  
**Sources**  
[ODBC - Amazon Timestream](https://docs.aws.amazon.com/timestream/latest/developerguide/ODBC.html)  
[Use the Amazon Athena Power BI connector - Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/connect-with-odbc-and-power-bi.html)  
[Troubleshooting connection with the ODBC driver - Amazon Timestream](https://docs.aws.amazon.com/timestream/latest/developerguide/ODBC-connecting-troubleshooting.html)

Timestream ODBC and Power BI Connector - DirectQuery Sending a Malformed SQL Query

Relevant content