PRO: Test Power BI Incremental Refresh in TV 2.0

[jesusitd]

https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh

Testing for Calls, with a Rogue dataset

[jesusitd]

General: https://radacad.com/all-you-need-to-know-about-the-incremental-refresh-in-power-bi-load-changes-only

To Detect Data Changes (a full Partition will be Updated): https://guyinacube.com/2019/07/24/power-bi-incremental-refresh-understanding-detect-data-changes/

[jesusitd]

INSERT MODE.

Initial discoveries and thoughts:

• Incremental Refresh must be set up with Power Query parameters RangeStart and RangeEnd
• The query for the incremental table should ideally be Power Query -folded
• OTHERWISE, tests are being made to pass the parameters using Replace on top on the Custom SQL statement (pay attention to the Start >= and the End <)
• MUST PLAY with the incremental refresh configuration, for example: 2 yrs Store and 3 Days partitions for Leads - Calls and see the performance in each scenario
• CONFIRMED that multiple refreshes a day (like we do) are NOT DUPLICATING the data of the current partition (tested with 5-days partitions) :)

When a dataset in incremental refresh is uploaded, IT LOOKS LIKE:

• Data from Pushed dataset takes over online dataset
• After uploading refresh Is made historical, to replace pushed data with fresh data. For Leads - Calls it took 40 seconds.
• Next refresh will be incremental and took 5 seconds for Leads - Calls.

CC @CesarITD

[jesusitd]

Upcoming Tests:

• Detect Data Changes with Native Query Folding
• Detect Data Changes with Custom SQL parameter injection

[jesusitd]

Further findings:

• DEFINE whether the Partitions will be based on UTC/EST. RECOMMENDED UTC.

[jesusitd]

GOT ERROR WHILE TRYING TO ENABLE DETECT DATA CHANGES IN A SECONDARY UPSERT TABLE: Something went wrong There was an error when processing the data in the dataset. Please try again later or contact support. If you contact support, please provide these details. Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Resource name and Location need to match. Resource name: LeadsNative. Location: Polling_36686F87_B3FA_4760_A613_9C6D47A37170."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}}],"exceptionCulprit":1}}} Cluster URI: WABI-US-EAST2-redirect.analysis.windows.net Activity ID: f06724a2-1e41-4d84-ab33-65e7951b1a07 Request ID: 78d17e45-bd28-e305-d70a-1d5b26b58928 Time: 2020-03-23 16:54:35Z

Contacted Microsoft Support

[jesusitd]

This seems to be a long-standing issue as the whole thing has been around for too short.

Tests finished. Result: UNSUITABLE until further development from Microsoft.