Pär Adeen on 12 Aug 2020 11:51:48
The current Dataflows Incremental Refresh implementation requires a datetime column.
Some datasources (DW for example) uses a date key instead of a datetime format and we then need to convert the date key into datetime format while keeping query folding. Currently there are no conversion available supporting the format conversion from YYYYMMDD to datetime while maintaining the query folding using SQL server as a data source.
I see two possible solution:
1) Start to support datetime conversion while maintaining query folding towards popular databases like SQL
2) Implement an advanced/custom configuration in the incremental refresh settings dialog. This feature will then not require a datetime column in the data source, and as a consequence of that, the users will be responsible to implement the filtering code them-self based on the provided RangeStart/RangeEnd parameters. For a YYYYMMDD format in the database, the user can easily extract the year, month, and day from the parameters RangeStart and RangeEnd using:
Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)
And using the advanced mode will also remove the requirements that the "Detect Data Changes" must be of datetime type, instead this can be of any format, preferably a integer representing a sequence number in the data base