2

We are using OLAP in our project and we are facing a scenario where we need to update the cube with real time data on daily basis.

I need some suggestions about an optimal way for updating the data in the cube.

Whenever new data arrives and made an entry in that table we need to update the current values in the cube like a trigger.

3

It's hard to give you a specific guidance on your best setup, but in any case there is no such thing as a trigger writing to an OLAP cube.

I can give you some starting points though.

You need to look into different storage options for your cubes.

By default cubes use MOLAP (Multidimensional OLAP) in which you process a cube on a scheduled basis[1] and all data and aggregations are stored in the OLAP database. This has a latency as the relational data is only visible in the cube after processing.

This is by far the easiest option from a developer perspective, but won't resolve your requirement.

[1] With MOLAP, see below for the proactive caching options

There is also a ROLAP (Relational OLAP) storage mode, which stores the aggregations in indexed views in a relational database. There is no copy of the data in the cube but the data is available immediately in the cube.

This off course has an effect on the query performance to your SSAS cube. Generally the querys will return slower and processing time will be longer.

If performance is too slow, there is a third option called HOLAP (Hybrid OLAP), which stores the data in the relational database but the aggregations in the OLAP database. When the source data is updated the aggregations will be processed again. This offers zero latency, and relatively decent performance when the queries are resolved from the aggregations, but slow performance when the users drill down or the query has to access the relational store.

Proactive caching

You should also look into Proactive caching which determines how often the aggregations are updated. This should be a balance between the load that the processing puts on your relational database and the performance of the SSAS queries and the latency in your cubes.

If you use proactive caching the cube listens to changes in the underlying data source and processes your MOLAP partitions on the schedule you choose.

Have a look at this article Introduction to SQL Server Proactive Caching for an explanation of how that works

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.