Building a super-efficient incremental refresh process in SSAS

Question

Suppose I have 2 years time entry data (~25M rows) by employee and day and project etc. At any point of time any rows within those 2 years can change (new rows added, existing rows modified or potentially some rows deleted too). The number of rows changed could just be <1000 rows across the entire period in any 30 min interval but for the sake of making this a complex problem, let's assume the 1000 rows can be across 12 random months within the 2 year period. Is there a way to setup an incremental refresh policy in SSAS such that these 1000 changes are processed in the model and correctly reflected (without any duplication) without actually processing all 12 months of data (~12M rows) every 30 mins?

Answer 1

What we have done with this in the past is:

• Partition the table in SSAS / AAS - perhaps partition by week
• Update the rows in your source that SSAS / AAS loads from.
• Keep track of which weeks the changes exist in
• Process only the table partitions that are affected.

Most of the time it will be a small number of partitions involved, and you'll only average around 115000 rows per partition, which should load quickly.

Answer 2

(Sorry, answer got duplicated when I thought it had disappeared)

Answer 3

At any point of time any rows within those 2 years can change

You can perform an "incremental processing" to bring new fact data into the cube, but you can not perform an update processing on fact data, only on dimensions. So if you fact data changes, you have to perform a full processing.