CODEWITHSUNDEEP
sql-server-2005ssasolap
user2386811
user2386811

Reputation: 23

SQL Server 2005 Analysis Services data update

I'm new to Analysis Services

My first cube has been deployed and it seems to work. Dimension tables are ok and fact tables are ok.

My question is very simple : If I add a new record in the related datasource table, Browsing the cube, I don't see the new record until process again the cube. In my mind I think if new records are addedd, then cube must reflect the changes.

How to solve this issue? Do I need to reprocess the cube every time a new record is added? This is impossible of course.

Upvotes: 2

Views: 441

Answers (1)

kzhen
kzhen

Reputation: 3118

You understand that essentially your cube represents a bunch of aggregated measures? That means that when the cube is processed it looks at all the data that is in your fact tables and processes the Measures (according to the dimensions).

The result of this is that you're able to access the data in the cube quickly and efficiently. The downside is as you have mentioned is that when new data is added to the fact table the cube isn't updated.

Typically there will be a daily batch job that will update the cube with the latest fact data, depending on the amount of data you have and the "real-time" requirements this could be done more than once p/day. A lot of people do this out of hours.

If you look closely in BIDS you will notice on the Partitions tab that for each partition it has a Storage Mode which you can define.

I would recommend you read this this article http://sqlblog.com/blogs/jorg_klein/archive/2008/03/27/ssas-molap-rolap-and-holap-storage-types.aspx

Basically, there are a few different modes you can use:

MOLAP (Multi dimensional Online Analytical Processing) MOLAP is the most used storage type. Its designed to offer maximum query performance to the users. Data AND aggregations are stored in optimized format in the cube. The data inside the cube will refresh only when the cube is processed, so latency is high.

ROLAP (Relational Online Analytical Processing) ROLAP does not have the high latency disadvantage of MOLAP. With ROLAP, the data and aggregations are stored in relational format. This means that there will be zero latency between the relational source database and the cube. Disadvantage of this mode is the performance, this type gives the poorest query performance because no objects benefit from multi dimensional storage.

HOLAP (Hybrid Online Analytical Processing) HOLAP is a storage type between MOLAP and ROLAP. Data will be stored in relational format(ROLAP), so there will also be zero latency with this storage type. Aggregations, on the other hand, are stored in multi dimensional format(MOLAP) in the cube to give better query performance. SSAS will listen to notifications from the source relational database, when changes are made, SSAS will get a notification and will process the aggregations again. With this mode it’s possible to offer zero latency to the users but with medium query performance compared to MOLAP and ROLAP.

To get the real-time reporting without having to reprocess your cube you will need to try out ROLAP, but beware, the performance will suffer (depending on the size of your cube and server!).

Upvotes: 1

Related Questions