Choosing a Standard Storage Setting
Microsoft SQL Server Analysis Services provides several standard storage configurations for storage modes and caching options. These provide commonly used configurations for update notification, latency, and rebuilding data. The standard configuration settings are set in the Storage Options dialog box. To choose one of the standard settings, click the Standard settings button in the Storage Settings dialog box, and then move the slider to one of the settings listed in the following table.
Storage Settings Descriptions
Standard Storage Setting |
Description |
|---|---|
Real Time ROLAP |
OLAP is in real time. Detail data and aggregations are stored in relational format. The server listens for notifications when data changes and all queries reflect the current state of the data (zero latency). This setting would typically be used for a data source with very frequent and continuous updates when the very latest data is always required by users. Depending on the types of queries generated by client applications, this method is liable to give the slowest response times. |
Real Time HOLAP |
OLAP is in real time. Detail data is stored in a relational format while aggregations are stored in a multidimensional format. The server listens for notifications when data changes and refreshes the multidimensional OLAP (MOLAP) aggregations as needed. No MOLAP cache is created. Whenever the data source is updated, the server switches to real-time relational OLAP (ROLAP) until the aggregations are refreshed. All queries reflect the current state of the data (zero latency). This setting would typically be used for a data source with frequent and continuous updates (but not so frequent as to require real-time ROLAP) and users always require the latest data. This method normally provides better overall performance than ROLAP storage. Users can get MOLAP performance from this setting if the data source stays silent long enough. |
Low Latency MOLAP |
Detail data and aggregations are stored in multidimensional format. The server listens for notifications of changes to the data and switches to real-time ROLAP while MOLAP objects are reprocessed in a cache. A silence interval of at least 10 seconds is required before updating the cache. There is an override interval of 10 minutes if the silence interval is not attained. Processing occurs automatically as data changes with a target latency of 30 minutes after the first change. This setting would typically be used for a data source with frequent updates when query performance is somewhat more important than always providing the most current data. This setting automatically processes MOLAP objects whenever required after the latency interval. Performance is slower while the MOLAP objects are being reprocessed. |
Medium Latency MOLAP |
Detail data and aggregations are stored in multidimensional format. The server listens for notifications of changes to the data and switches to real-time ROLAP while MOLAP objects are reprocessed in cache. A silence interval of at least 10 seconds is required before updating the cache. There is an override interval of 10 minutes if the silence interval is not attained. Processing occurs automatically as data changes with a target latency of four hours. This setting is typically used for a data source with frequent (or less frequent) updates when query performance is more important than always providing the most current data. This setting automatically processes MOLAP objects whenever required after the latency interval. Performance is slower while the MOLAP objects are being reprocessed. |
Automatic MOLAP |
Detail data and aggregations are stored in multidimensional format. The server listens for notifications but retains the current MOLAP cache while it builds a new one. The server never switches to real-time OLAP, and queries may be stale while the new cache is built. A silence interval of at least 10 seconds is required before creating the new MOLAP cache. There is an override interval of 10 minutes if the silence interval is not attained. Processing occurs automatically as data changes with a target latency of two hours. This setting is typically used for a data source when query performance is of key importance. This setting automatically processes MOLAP objects whenever required after the latency interval. Queries do not return the most recent data while the new cache is being built and processed. |
Scheduled MOLAP |
Detail data and aggregations are stored in a multidimensional format. The server does not receive notifications when data changes. Processing occurs automatically every 24 hours. This setting is typically used for a data source when only daily updates are required. Queries are always against data in the MOLAP cache, which is not discarded until a new cache is built and its objects are processed. |
MOLAP |
Proactive caching is not enabled. Detail data and aggregations are stored in multidimensional format. The server does not receive notifications when data changes. Processing must either be scheduled or performed manually. This setting is typically used for a data source in which periodic updates are unnecessary for the client applications but for which high performance is critical. MOLAP storage without proactive caching provides the best possible performance if your applications do not require the most recent data. It does require downtime to process updated objects, although downtime can be minimized by updating and processing cubes on a staging server and using database synchronization to copy the updated and processed MOLAP objects to the production server. For more information, see Synchronizing Analysis Services Databases. |
A good approach for a large measure group is to configure storage differently for different partitions. To do this, you can do one of the following:
Use real-time ROLAP for current data that is being continuously updated.
Use proactive caching with low latency or medium latency for partitions based on data sources that are being updated less frequently.
Use automatic MOLAP for data sources of which users require high performance but can afford some latency of the data.
Use scheduled MOLAP for data sources for which users need to be able to continuously access the data but see changes only periodically.
Use MOLAP storage without proactive caching for partitions that are changing infrequently or not at all; for partitions for which users do not need to browse the most recent data; and if the data does not have to be continuously available to users during any necessary updates and processing.
These are general guidelines, and careful analysis and testing may be necessary to develop the best possible storage scheme for your data. You may also manually configure storage settings for a partition if none of the standard configurations meet your needs.