Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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"
}
}
}
}
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?
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]
That's not good enough - your query must include the RangeStart and RangeEnd filters.
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')
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"
}
}
}
}
User | Count |
---|---|
107 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |