Delta refresh of SSAS Cube

0
votes

I have a scenario where an SSAS cube's data needs to be refreshed. We want to avoid using a full refresh that takes an hour, and do a 'delta' refresh. The delta refresh should 1) Update fact records that have changed 2) Insert fact records that are new 3) Delete fact records that no longer exist

Consider a fact table with three dimensions: Company, Security, FiscalYear and two measures: Qty, Amount

Scenario: In the fact table, a record with Company A, Security A, FiscalYear A has the measure Qty changed from 2 to 20. Previously the cube correctly showed the Qty to be 2. After the update,

If we do a Full refresh, it correctly shows 20. But in order to get this, we had to suffer a full hour of cube processing.

We tried adding a timestamp column to the fact table, split the cube into Current and Old partitions, full refreshed the Current Partition and Merged into Old partition as seems to be the popular suggestion. When we browse the cube, it shows 22, which is incorrect

We tried an Incremental refresh of the cube, same issue. It shows 22, also incorrect.

So what I am trying to ascertain here, is whether there is no way to process a cube so it only takes the changes (and by that I mean Updates, Inserts AND deletes, not just Inserts!) and applies them to the data inside an SSAS cube?

Any help would be greatly appreciated!

Thanks!

1
ssas
Yes, if a row is processed into the old partition as Qty=2 then processed into the newer partition as Qty=20 it will show as Qty=22. You are responsible for ensuring a row doesn't appear in multiple partitions. – GregGalloway
Can you describe what subset of your fact table receives inserts, updates or deletes? Maybe only rows from the most recent 60 days change? Do rows older than 60 days change very infrequently? If a row older than say 60 days got updated is it a strict requirement for your delta load to reload it? – GregGalloway

1 Answers

0
votes

No, there is no way to do this. The only control you have over processing is the granularity of what you process. For instance, if you know that data over a certain age will never change, you can put data over that age in a partition, and not include it in your processing.