Sql Server Analysis Services Storage Options

Real time – ROLAP

You are in the closest position to real information. (Every action affects this storage) But your reports will be slower than the other options. All your information is in OLTP database. We can say you are using analysis services for declaration.

If your database is very very big, MOLAP is undesirable, so you need to choose ROLAP.

Summary

  • Slowest Performance
  • Zero Latency
  • No duplicate data (less disk usage)

Real Time HOLAP

Your information is stored in relational architecture. But the aggregations are stored in multidimensional format. Whenever a change occurs server recalculates the aggregations. On calculation time analysis services switches to ROLAP Mode!

This option is best if you want the latest data and your OLTP source changes rarely.

  • Best processing time (processing time is not the time that you preview a report!)
  • Zero Latency
  • No duplicate data (less disk usage)

MOLAP

Provides and optimized multidimensional storage, both data and aggregations stored in MOLAP architecture. MOLAP gets all data from the OLTP source but compresses and does not store null data. MOLAP has fastest query result time. You need more disk space in these options. But MOLAP has the best performance.

In Analysis Services we have 5 MOLAP options; Low Latency MOLAP, Medium Latency MOLAP, Automatic MOLAP, Scheduled MOLAP and MOLAP

Low Latency MOLAP

This option has a cache. But if a change occurs in a current data server switches to ROLAP. And at least silence 10 seconds is required before updating cache. Processing occurs after 30 minutes from the first notification. Low latency MOLAP is used for frequently updated data sources when performance is more important than providing latest data.

Medium Latency MOLAP

This option works like Low latency MOLAP. The difference is Medium Latency MOLAP processes cube after 4 hours from the first notification.

Automatic MOLAP

Automatic MOLAP never switches to ROLAP. Always keeps the cache. But a 10 second silence time occurs when new cache comes. Processing occurs after 2 hours of change notification. Reports do not return the most recent data while the new cache is being built and processed.

Scheduled MOLAP

Scheduled MOLAP option does not listen server for changes. It processes automatically every 24 hours.

MOLAP

This option provides the best performance. Server does not check for any changes. You need to manually process your cube. If your reports does not need the latest data but best performance you must chose this option. If you have enough conditions (a big disk, a good processor which can calculate before you die :) ) it is very good to calculate all aggregations.
Read this article for more information. (http://msdn.microsoft.com/en-us/library/ms175646.aspx)

Be Sociable, Share!

    Category: Business Inteligence - Comments Off on Sql Server Analysis Services Storage Options

    Comments are closed.

    Back to top