Struggling To Make Incremental Refresh Work

[adavidso]

I am trying to have two historic tables in my report refresh incremetally to no avail. This is what I've done:

My parameters

My filter:

my incremental update options:

My workspace - which is Pro

I refreshed two times. Once when I first uploaded and then again shortly after because I read you needed to refresh it twice.

No stored rows:

Thank you for your time and help in advance!

[lbendlin]

Your setup looks to be correct. Seeing multiple partition is a clear indication that Incremental Refresh is up and running.

The issue may be either with your source data (the datetime column you use to control the incremental refresh with or with any additional filters in your Power Qery that interfere with your intent.  Can you post a sanitized version of the M code?

[adavidso]

I coded everything for this in SQL - here is the native query:

select [$Table].[ReqOrderNo] as [ReqOrderNo],
[$Table].[ReqOrderItemNo] as [ReqOrderItemNo],
[$Table].[ReqType] as [ReqType],
[$Table].[RcptOrderNo] as [RcptOrderNo],
[$Table].[RcptOrderItemNo] as [RcptOrderItemNo],
[$Table].[RcptType] as [RcptType],
[$Table].[TonsPeggedToRolling] as [TonsPeggedToRolling],
[$Table].[RequestedDeliveryDate] as [RequestedDeliveryDate],
[$Table].[AvailDate] as [AvailDate],
[$Table].[ReqQuantity] as [ReqQuantity],
[$Table].[Plant] as [Plant],
[$Table].[MaterialNo] as [MaterialNo],
[$Table].[BlockResource] as [BlockResource],
[$Table].[MaterialDescription] as [MaterialDescription],
[$Table].[MRPType] as [MRPType],
[$Table].[PH5] as [PH5],
[$Table].[PlanningGroup] as [PlanningGroup],
[$Table].[PlanningStrategy] as [PlanningStrategy],
[$Table].[ProcType] as [ProcType],
[$Table].[PTPGroup] as [PTPGroup],
[$Table].[Shape] as [Shape],
[$Table].[SizeForQualCert] as [SizeForQualCert],
[$Table].[SoldTo] as [SoldTo],
[$Table].[SoldToName] as [SoldToName],
[$Table].[ShipTo] as [ShipTo],
[$Table].[ShipToName] as [ShipToName],
[$Table].[ShipToCity] as [ShipToCity],
[$Table].[ShipToState] as [ShipToState],
[$Table].[BlockNumber] as [BlockNumber],
[$Table].[RollDate] as [RollDate],
[$Table].[RollingOrdinal] as [RollingOrdinal],
[$Table].[PeggingRefreshDate] as [PeggingRefreshDate],
[$Table].[PEGGING_TYPE] as [PEGGING_TYPE],
[$Table].[Sales Organization] as [Sales Organization],
[$Table].[DivisionCode] as [DivisionCode],
[$Table].[DistributionChannel] as [DistributionChannel]
from [dbo].[SOP_FUL_PeggedToRollingHist] as [$Table]

[lbendlin]

That's not good enough - your query must include the RangeStart and RangeEnd filters.

[adavidso]

I apologize - I took this from the source query - this is the one with the filter

select [_].[ReqOrderNo] as [ReqOrderNo],
[_].[ReqOrderItemNo] as [ReqOrderItemNo],
[_].[ReqType] as [ReqType],
[_].[RcptType] as [RcptType],
[_].[PeggedTons] as [PeggedTons],
[_].[RequestedDeliveryDate] as [RequestedDeliveryDate],
[_].[Plant] as [Plant],
[_].[MaterialNo] as [MaterialNo],
[_].[BlockResource] as [BlockResource],
[_].[MaterialDescription] as [MaterialDescription],
[_].[PH5] as [PH5],
[_].[PlanningGroup] as [PlanningGroup],
[_].[PlanningStrategy] as [PlanningStrategy],
[_].[ProcType] as [ProcType],
[_].[PTPGroup] as [PTPGroup],
[_].[Shape] as [Shape],
[_].[SizeForQualCert] as [SizeForQualCert],
[_].[SoldTo] as [SoldTo],
[_].[SoldToName] as [SoldToName],
[_].[ShipTo] as [ShipTo],
[_].[ShipToName] as [ShipToName],
[_].[ShipToCity] as [ShipToCity],
[_].[ShipToState] as [ShipToState],
[_].[t0_0] as [PeggingRefreshDate],
[_].[RequestedVSToday-STOCK] as [RequestedVSToday-STOCK],
[_].[ShipmentNo] as [ShipmentNo],
[_].[SalesOrg] as [SalesOrg],
[_].[Division] as [Division],
[_].[DistributionChannel] as [DistributionChannel]
from
(
select [_].[ReqOrderNo] as [ReqOrderNo],
[_].[ReqOrderItemNo] as [ReqOrderItemNo],
[_].[ReqType] as [ReqType],
[_].[RcptType] as [RcptType],
[_].[PeggedTons] as [PeggedTons],
[_].[RequestedDeliveryDate] as [RequestedDeliveryDate],
[_].[Plant] as [Plant],
[_].[MaterialNo] as [MaterialNo],
[_].[BlockResource] as [BlockResource],
[_].[MaterialDescription] as [MaterialDescription],
[_].[PH5] as [PH5],
[_].[PlanningGroup] as [PlanningGroup],
[_].[PlanningStrategy] as [PlanningStrategy],
[_].[ProcType] as [ProcType],
[_].[PTPGroup] as [PTPGroup],
[_].[Shape] as [Shape],
[_].[SizeForQualCert] as [SizeForQualCert],
[_].[SoldTo] as [SoldTo],
[_].[SoldToName] as [SoldToName],
[_].[ShipTo] as [ShipTo],
[_].[ShipToName] as [ShipToName],
[_].[ShipToCity] as [ShipToCity],
[_].[ShipToState] as [ShipToState],
[_].[RequestedVSToday-STOCK] as [RequestedVSToday-STOCK],
[_].[ShipmentNo] as [ShipmentNo],
[_].[SalesOrg] as [SalesOrg],
[_].[Division] as [Division],
[_].[DistributionChannel] as [DistributionChannel],
convert(datetime2, [_].[PeggingRefreshDate]) as [t0_0]
from [dbo].[SOP_FUL_PeggedtoStockSnapshot] as [_]
) as [_]
where [_].[t0_0] > convert(datetime2, '2022-02-01 00:00:00') and [_].[t0_0] <= convert(datetime2, '2022-02-02 00:00:00')

[lbendlin · Accepted solution]

Looks ok - it's a short period of just a day, but could be sufficient for dev work. In Power Query can you confirm that you see data for 2022-01-01 ?

Next step would be to run a dataset refresh again and look at the SQL Server query logs, or the partition refresh timestamps.  You can also issue XMLA commands to selectively refresh individual partitions.

You can also double check the partition definitions in SSMS.  For example :

{
  "createOrReplace": {
    "object": {
      "database": "database",
      "table": "table",
      "partition": "2018"
    },
    "partition": {
      "name": "2018",
      "mode": "import",
      "source": {
        "type": "policyRange",
        "start": "2018-01-01T00:00:00",
        "end": "2019-01-01T00:00:00",
        "granularity": "year"
      }
    }
  }
}