We have PBI Premium and I am attemping to have one of our reports use Import from the SQL Azure data warehouse and incrementally load 2 of the main tables. I've followed the documentaiton, but I am having some issues. Is there an easy way to troubleshoot or test what is actually refreshing and/or what the errors are?
Which says do not use the same column for the "Detect Data Changes" as the column you use against the RangeStart and RangeEnd parameters. We do have an auditing/process date/time column on all DW tables which indicates when it was loaded. My thought is to simply use this for the Detect Data Changes column, but then I don't know what to use for the partition column for RangeStart/RangeEnd parameters. We really only need to see the last DW load time in the process date/time column.
For incremental refresh, datasets are filtered by using Power Query date/time parameters with the reserved, case-sensitive namesRangeStartandRangeEnd. These parameters are used to filter the data imported into Power BI Desktop, and also to dynamically partition the data into ranges once published to the Power BI service. The parameter values are substituted by the service to filter for each partition. Once published, the parameter values are overridden automatically by the Power BI service. There's no need to set them in dataset settings in the service. Once published, the parameter values are overridden automatically by the Power BI service.
For your case, you need to set the RangeStart and RangeEnd parameters for process date/time column, and then use auditing date/time column for "Detect Data Changes".
Thank you for the reply. The issue is that my main fact table only has the process date/time column - this is the audting column which is updated in SQL each time the data is loaded. I do not have any other date/time columns in the fact table. I believe I should use this as the Detect Data Changes column, since it will always have a single date of the last load, but I do not have any other date time column to use for RangeStart and RangeEnd. How do I handle this situation? The documentation says not to use the process date/time column for both.