Forum Discussion

adavidso's avatar
adavidso
Regular Visitor
4 years ago
Solved

Struggling To Make Incremental Refresh Work

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:   ...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    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"
      }
    }
  }
}
lbendlin's avatar
lbendlin
Super User
4 years ago

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

adavidso's avatar
adavidso
Regular Visitor
4 years ago

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's avatar
    lbendlin
    Super User
    4 years ago

    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"
      }
    }
  }
}