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:

 

 

 

 

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!

 

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

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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's avatar
      adavidso
      Regular Visitor
      4 years ago

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

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