Power BI Athena Incremental Refresh

[Anonymous]

Hi All

I have been successfully using Power BI’s incremental refresh daily with a MySQL data source. However, I can't get this configured with AWS Athena, because seemingly the latter interprets the values in the required parameters RangeStart and RangeEnd as strings. Since the data source is around 50 million rows I’d rather avoid querying this from scratch every day.

In this video from Guy in a Cube, you can clearly see that the query sent by Power BI to Azure has a convert to datetime2 function - something like this is presumably missing for Athena/Presto, which needs the type constructor TIMESTAMP in order to do datetime comparisons (https://stackoverflow.com/a/38041684/3675679), and of course incremental refresh must be based on datetime fields. I am using the datetime field adv_date for the incremental load.

Here is what the M query looks like in Power Query Editor:

= Table.SelectRows(#"Removed Columns1", each [adv_date] >= RangeStart and [adv_date] < RangeEnd) 

And here is the resultant error message in Athena:

Your query has the following errors:SYNTAX_ERROR: line 1:1: Incorrect number of parameters: expected 2 but found 0 

Whilst this is how Athena interprets the query:

    select "col1", "col2", "adv_date" 
    from "AwsDataCatalog"."test"."test_table" 
    where "adv_date" >= ? and "adv_date" < ?

I have contacted Power BI support without success.

Does anyone have a workaround for this by any chance? Happy to provide more info if needed.

[bertcasier]

I'm having the same problem. Did anyone find a solution for this?

[bertcasier]

From what I can find on the internet:

AWS athena does not support prepared statements, they advice to construct the full select before sending it to athena.

Is there a way to do that in powerQuery, in attempt to fix the incremental refresh?
We should be able to construct a query where RangeStart and RangeEnd are replaced before sending it to Athena

[rafaelruivo]

After much, much suffering, I was able to login back to this community. Not even that works well in PBI!

Well, to the subject at hand, I think I got it working. I added another step before the date filter, and it seems to have worked. I think it moves the filter for after the query load on PBI, and this is very, very annoying, but seems to be working.

Sometimes I don't understand why I still insist in punishing myself with Power BI. 

[MatheusLPS]

Same problem here. Any solution?

[Anonymous]

Any update on this? I have the same problem. Date parameters on Power BI show up as question marks in Athena giving Syntax errors

[v-frfei-msft]

Hi @Anonymous ,

Based on my test,I cannot reproduce your issue here. How about deleting other steps to check again?

[Anonymous]

Hi @v-frfei-msft , thanks for the response.

Here's the entire M query, which I have reduced slightly to the most relevant elements.

let
Source = Odbc.DataSource("dsn=athena", [HierarchicalNavigation=true]),
AwsDataCatalog_Database = Source{[Name="AwsDataCatalog",Kind="Database"]}[Data],
test_Schema = AwsDataCatalog_Database{[Name="test",Kind="Schema"]}[Data],
cpbd_test_Table = test_Schema{[Name="cpbd_test",Kind="Table"]}[Data],
#"Filtered Rows" = Table.SelectRows(#"cpbd_test_Table", each [adv_date] >= RangeStart and [adv_date] < RangeEnd)
in
#"Filtered Rows"

As you can see, all I am doing is selecting the entire table and attempting to filter on the datetime column.  I still have the same error as above.