When working with SQL Server, it is important to understand the different storage modes available for storing data and aggregations. Each storage mode has its own advantages and disadvantages, and choosing the right one can greatly impact the performance and flexibility of your SQL Server implementation.
MOLAP (Multi-dimensional OLAP)
The most common storage mode in SQL Server is MOLAP. In this mode, data and aggregations are stored in a multidimensional format, providing optimal query performance. However, one downside of MOLAP is that it creates a completely offline data set. Any updates made to the underlying relational database will not be reflected in the cube unless it is re-processed. For most data warehouse solutions, a daily or weekly re-processing of the cubes is sufficient, making MOLAP a recommended storage mode in these environments.
Scheduled MOLAP
Scheduled MOLAP is similar to MOLAP, but with the added benefit of automatic updates every 24 hours. This storage mode utilizes proactive caching, ensuring that the cube/partition cache is refreshed automatically without the need for manual intervention. This is particularly useful when you want to have an up-to-date cube without the need for frequent re-processing.
Automatic MOLAP
In Automatic MOLAP mode, Analysis Services listens to notifications from the underlying relational database. When an update occurs, an event is raised and sent to Analysis Services, triggering the automatic refresh of the cube’s cache. Users can still access the cube while the cache is being refreshed, but they will be using the old cache until the process is complete. This storage mode provides users with an almost up-to-date cube, with a target latency of 2 hours from update to process.
Medium Latency MOLAP
Medium Latency MOLAP is a storage mode that switches to Real Time ROLAP when an update is performed in the underlying database. The switch is determined by the latency variable, with the default cache update occurring after 4 hours from the time the relational database was updated. This storage mode is ideal when the underlying database goes through occasional updates, and users require an up-to-date cube with reasonable performance.
Low Latency MOLAP
Similar to Medium Latency MOLAP, Low Latency MOLAP allows for a latency of up to 30 minutes. This means that the cube switches to ROLAP more frequently, resulting in poorer query performance. However, users get to see a more updated version of the data.
Real Time HOLAP
In Real Time HOLAP mode, data is maintained in its relational format, while aggregations are stored in a multidimensional format. This allows for real-time data with the added benefit of MOLAP aggregations to improve query performance. However, performance is not as good as when data is stored in a purely multidimensional format.
Real Time ROLAP
The last storage mode available is Real Time ROLAP, where both data and aggregations are stored in a relational format. This provides zero latency for users, allowing them to access the cube and retrieve real-time data. However, query performance is the poorest in this mode as no MOLAP objects are available.
It is important to note that the various storage modes can be applied to cubes, partitions, and dimensions. Additionally, proactive caching allows for manual tuning of settings such as latency, listening intervals, and notification types. Processing options can also be fine-tuned to determine whether a partition should be fully processed or incrementally processed.
Conclusion
SQL Server offers a range of storage modes, including MOLAP, HOLAP, and ROLAP, each with its own benefits and considerations. The addition of proactive caching in SQL Server provides users with real-time data while still enjoying the query performance benefits of MOLAP. Understanding these storage modes and their implications can help you make informed decisions when designing and implementing your SQL Server solution.