Issue with Incremental refresh (Sap hana database)

[Anonymous]

Hi,

I am trying to implement incremental refresh using SAP Hana database but i am unable to do that. Actually i saw some articles on how to convert text date using (x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x) and after implementing it when i see native query i can't the incremental refresh in it. 

The code which is used in Power query is 

= Table.SelectRows(#"Added Items", each [Calendar Day] >= Date.Year(RangeStart)*10000 + Date.Month(RangeStart)*100 + Date.Day(RangeStart) and [Calendar Day] < Date.Year(RangeEnd)*10000 + Date.Month(RangeEnd)*100 + Date.Day(RangeEnd))

Before implementing the code:

After implementing the code:

As far i understand after implementing the code i must get preview data in power query but it does not give any data at all. 

The link i refered is : https://www.fourmoo.com/2020/03/11/how-to-configure-incremental-refreshing-in-power-bi-with-datekey-...

Any help on this will be highly appreciated.

Thanks,

Nisar

[v-kelly-msft]

Hi @Anonymous ,

Important

Verify queries have an equal to (=) on either RangeStart or RangeEnd, but not both. If the equal to (=) exists on both parameters, a row could satisfy the conditions for two partitions, which could lead to duplicate data in the model. For example,
#"Filtered Rows" = Table.SelectRows(dbo_Fact, each [OrderDate] >= RangeStart and [OrderDate] <= RangeEnd) could result in duplicate data.

 Tip

While the data type of the parameters must be date/time, it's possible to convert them to match the requirements of the datasource. For example, the following Power Query function converts a date/time value to resemble an integer surrogate key of the form yyyymmdd, which is common for data warehouses. The function can be called by the filter step.

(x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)

Check the reference below:

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @v-kelly-msft I already tested the same way but incremental refresh does not work. Also if you can see i have already listed a link which shows the same code which you shared but still the unable to configure the incremental refresh.

[lbendlin]

Have you tried doing it the other way round, converting your [Calendar Day] integer value into a datetime column?

[Anonymous]

Hi @lbendlin Yes i tried converting text to INT data type using CAST function and writing SQL using SQL HANA DB but still the issue is still there. And another problem is when i use SQL statement, View native query in Power Query greyed out. The only way to check whether the incremental refresh is implemented is checking the native query.

I tried incremental refresh using Azure SQL db as a data source and it worked fine even using SQL statement. But when i use SAP Hana DB View native query in Power Query greyed out also incremental refresh doesnt get implemented.

[lbendlin]

"The only way to check whether the incremental refresh is implemented is checking the native query."

That is incorrect. The only way to check is by looking at the partition refresh dates. Query folding is desired but not required.

[Anonymous]

@lbendlin  yes thats right. I agree your point. But any idea why incremental refresh is not working in my case?

[v-kelly-msft]

Hi  @Anonymous ,

What would happen if you directly fiter the date in the fact table?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft  that does not work. When i refresh report in Power BI service using this method i dont see any changes in refresh time once i do that.