Incremental Cube Refresh with three cases: Insert, Update and delete

Question

We have use case where we want to leverage incremental cube refresh. However, Incremental Cube Refresh on based three cases: Insert, Update and delete. In source data, data can be updated/deleted from historical records and new data will be appended. In this scenarios, what we expect whenever we do cube refresh Deleted source records should be removed from Cube (if it previously processed), Updated source records should be updated in Cube (on mentioned attributes) and New records should be appended to cube. On cube we partitions created based on time period.

Currently we are doing full refresh every time cube is processed. But this impacts processing time. How can we reduce it?

Answer 1

Basically, you need a way to detect whether each partition needs to be refreshed. Note that there's no notion of updating or deleting data in tabular models. Whole partitions are simply refreshed.

Patrick walks you through a solution with Power BI Incremental Refresh here: Handling Deletes in Incremental Refresh in Power BI

For SSAS/AAS you would use a similar approach; you must have some way to track which partitions have updates, and refresh just those partitions.