Regarding Incremental Refresh in Power BI with databricks as source

[mkravi]

Dear Community Members 

We have connected to ADLS2 using Databricks, as our data is going to be huge, so we are trying to use incremental policy functionality of power bi. But when we are going to do the same, we have found that tables that we have loaded doesn't support the query folding . I have checked in the applied steps and found that view native query is disabled. Request your guidance and support on to how to deal with such situation.

queries : 

how to deal with heavy data in  billions in power bi ( data source is Databricks)

how to implement incremental refresh while facing the above mentioned issue?

Early response will be appreciated. 

Thanks 

Ravi 

[RobertUrbanski]

I was also able to get incremental refresh working with Azure Databricks.  Only I have a custom sql query that uses RangeStart and RangeEnd.  Here is the where clause of the custom query...
where date_created >= try_to_timestamp('"& Text.From(RangeStart) &"', 'M/d/yyyy h:mm:ss a')
and date_created < try_to_timestamp('"& Text.From(RangeEnd) &"', 'M/d/yyyy h:mm:ss a')

[rbartles111213]

What is this part of your where clause? try_to_timestamp
I have a column that is a datetime named ASOFDATETIME and I am trying to update the SQL. 

Also using as a parameter so I can switch environments with a native query... I hope 🙂  

[RBot_L]

I managed to get it to work! I came across this post while looking for a solution so I hope this helps someone.

My solution was to use Databricks.Query instead of Databricks.Catalogs to load the table and write SQL statements to filter the table on the M code using Advanced Editor.

let
    Source = Databricks.Query(ServerHostName, HTTPPath, [Catalog=null, Database=null, 
    EnableAutomaticProxyDiscovery=null]),
    Table = "SELECT * FROM hive_metastore.bl.testtable",
    FilterRangeStart = " WHERE LastChanged >= timestamp '" &  DateTime.ToText(RangeStart, "yyyy- 
    MM-ddTHH:mm:ss") & "'", 
    FilterRangeEnd = " AND LastChanged < timestamp '" &  DateTime.ToText(RangeEnd, "yyyy-MM- 
    ddTHH:mm:ss") & "'", 
    Navigation = Source(Table & FilterRangeStart & FilterRangeEnd)
in
    Navigation

The parameters RangeStart and RangeEnd were not recognised as a timestamp so I had to specify they were a timestamp and convert them to text format in the exact same way as a timestamp.
 

[Anonymous]

I'm having the same issues here. It seems as if Power BI is not able to perform query folding any other than simple  renaming or casting and thus is not able to use the RangeStart and/or RangeEnd parameters in the query that is send out to the source system. In our case this makes incremental refresh completely useless, as refreshing seems only to take longer since it has several partitions and fires multiple exactly the same queries on the source system, which are subsequently handled by the Power BI engine and put into partitions.

[Abhilash_P]

HI @Tiff92 ,@mkravi ,@amitchandak
I am also facing similar issue any one resolved this..?

Regards

[Tiff92]

Hey I am having the same issue - have you resloved it and how? Thanks.

[mkravi]

Hi Amit 

Thanks for your quickest response, appreciate the same.

even when i delete the additional column then also the view native query shows disabled 

The column on which i have to apply  the incremental custom filter is already datetime 

Do you think databricks will support query folding as so far i didn't see. 

And what is your opinion in doing incremental refresh even when the query folding is disabled .

[amitchandak]

@mkravi ,My Initial thoughts

Can you check if you can do your incremental setting before adding a conditional column? After source change datatype to datetime and do incremental setting.