Evented partial preaggregation


I don’t know where this topic exactly belongs but the use case is as follows:
We have a big fact table and a dimension. The fact table changes once per day but the dimension table is modified by the application constantly (user input). The queries run of course on both with aggregate and filtering functions. The user’s expectations are that changes in the dimension table are reflected after short time (<5s) in the query results.
So invalidating or a forced partial pre-aggregation should be kicked off in an evented manner (we know what to preaggregate and when). Is there any recipe how to do that? With cube store? Any numbers/experiences how big the delay is? costs?

Thx for any input

Hi @pschrammel :wave:

You can achieve that with a custom refreshKey for your pre-aggregation that should change once the data in the dimension table changes. Also, you’ll need to tune the refreshKeyRenewalThreshold so the refreshKey is checked frequently enough.

Also, please feel free to the Real-Time Dashboard guide for more info on using custom refresh keys.

I definitely suggest using Cube Store because it contains various performance optimizations compared to regular databases.

Please also consider using time partitioning to split the creation of pre-aggregation into multiple tasks and increase the responsiveness.

thx, will have a close look at the refreshKey magic. I’ll report back here how we solved our challenges so others have an easier start (though the blog and docs are awesome)