Storage Modes in SSAS 2005
By Yaniv Mor,
Storage Modes in analysis Services 2005
Overview In SSAS 2005, a cube, a partition within a cube
or a dimension can store data and aggregations in numerous ways, each with its
own pros and cons. While Microsoft has retained the storage modes available in
AS 2000 (MOLAP, HOLAP and ROLAP), it has added several new storage modes, which
make an SSAS implementation a more robust and flexible solution in a production
environment. A new concept: proactive caching has been introduced, and as the
name implies, using this new feature enables the administrator to better control
the frequency and the way cube updates are being performed.
What exactly do we store? SSAS 2005, being an OLAP
engine, stores cumulative data of the measures defined in the cube. For example,
if we have a cube with a customer dimension and a time dimension, we might have
a measure like: sales amount which will indicate the dollar figure of the
sales per customer in a certain date and time. This data is stored as part of
the cube. On top of the measures data, each cube can have its own
aggregations. Aggregations are pre-calculated summaries of data for various
combinations of dimension slicing. SSAS 2005 needs to store both the measures
data and the aggregation data on disk. This enables the OLAP engine to retrieve
answers to users queries faster. In the remainder of this article, Ill try to
explain what types of storage modes SSAS 2005 offers to store this data.
1. MOLAP (Multi dimensional OLAP)This is the most common
storage mode. It implies that the cubes data and aggregations will be stored in
a multidimensional format. This is the most efficient way to store data and will
provide the user with optimal query performance. The downside with using this
storage mode is that it creates a completely offline data set. Once the cube
retrieved the data from the underlying relational database and processed it,
there is no further connection to the relational database. Any subsequent
updates in the database will not be reflected in the cube, unless it is
re-processed. Now, for most data warehouse solutions, a daily or even a weekly
re-process of the cubes is more then enough. For environments like these, it is
highly recommended to use the MOLAP storage mode.
2. Scheduled MOLAPThis is somewhat similar to the MOLAP
storage mode, in that it stores its data in a multidimensional format. However,
the content of the cube/partition cache will be automatically updated every 24
hours. In a way, using this storage mode already utilize the proactive caching
feature. It is important to note that theres no need to create a SQL Agent job
or an IS package to process the cube. The cubes cache will be refreshed
automatically every 24 hours.
3. Automatic MOLAPAgain, data is stored in a
multidimensional format. There is a basic change, though, when compared with the
previous 2 storage modes. Using an automatic MOLAP storage mode means that
Analysis Services needs to listen to notifications from the underlying
relational database. Once an update has occurred in the database, an event is
raised and sent to Analysis services. This type of messaging system can be
achieved by using Notification Services, for example. When the Analysis Services
Server has received the notification, it will automatically refresh the cubes
cache, to reflect the new changes in the database. Users will still be able to
access the cube while the cache is being refreshed, but they will be using the
old cache. Once the cache is refreshed, users will be redirected to the new
data. This type of storage provides the users with an almost up-to-date cube
(the target latency is 2 hours from update to process). This obviously depends
on the cube size and the magnitude of changes in the underlying database.
4. Medium Latency MOLAPThings are starting to get
interesting as we approach the latency variance (which we touched briefly in
the previous section). Initially data is stored in a multidimensional format.
The Analysis Services server is listening to notifications from the underlying
relational database. When an update is performed in the database, the cube
switches to a Real Time ROLAP storage mode. We will touch on this storage mode
shortly, but I will briefly note that this storage mode means: no
multidimensional storage at all and the cubes data is being retrieved directly
from the underlying relational database. The timing of the switch to ROLAP is
determined by the latency variable. By default, cache will be updated after 4
hours from the time the relational database was updated. This switch should not
last for long though, as in parallel, the cubes MOLAP cache gets processed to
reflect the latest changes. Once the cache is processed, the cube switches back
to the MOLAP storage mode. You would want to use this type of storage when you
realize that the underlying database goes through updates occasionally, and
users do want to have an up-to-date cube. However, they still require
performance to be reasonable. Having the cube reflecting data which is accurate
up to the last 4 hours is reasonable for the users and provides the correct
trade-off between performance and real-time data. If the users agree to these
terms, then this is the preferred storage mode to use.
5. Low Latency MOLAPThis mode is similar in its behavior
to the Medium Latency MOLAP mode, the only change is (yes, you may have guessed
it by now) the latency. Instead of allowing a latency of 4 hours, this mode will
allow only up to 30 minutes of latency. This implies a more frequent switch to
the ROLAP mode and even poorer query performance, as the cube is being processed
more often. The users do get to see a more updated version of the data though.
6. Real Time HOLAPHOLAP stands for hybrid OLAP. So far we
have discussed having the data and aggregations stored in a multidimensional
format. In the hybrid storage mode, data is maintained in its relational format
(i.e. data resides in the relational database), while aggregations are stored in
a multidimensional format. What this means is that the data is always
real-time data. The Analysis Services server still listens to notifications,
and when changes in the relational database occur, the aggregations (which are
still stored in a multidimensional format) are refreshed. During this process,
the cube is switched to the infamous ROLAP mode, until aggregation processing is
complete. It is easy to see that users are enjoying real-time data with the
added benefit of MOLAP aggregations to improve query performance. Still,
performance is not as good as it used to be when compared to data stored in a
multidimensional format, as well as the aggregations.
7. Real Time ROLAPThis is the last storage mode available
and we are actually quite familiar with it already. Data and aggregations are
stored in a relational format. This means zero latency for users, who will
always be able to access the cube and retrieve real-time data. Query performance
is the poorest here though as no MOLAP objects are available at all.
Just a bit more before finishingYou can apply the
various storage modes on cubes, partitions and dimensions. Some of the features
will not be available on some of the objects. When using proactive caching,
there are several settings which you can manually tune, like the latency, the
listening intervals and the notification types. You can also fine-tune the
processing options and instruct the SSAS engine whether to fully process a
partition or to incrementally process it.
ConclusionThe MOLAP, HOLAP and ROLAP storage modes were
available in the Analysis Services 2000 version. The important addition to the
2005 version is the proactive caching feature, which enables the user to have a
real-time version of the data while still enjoying the query performance
benefits of the MOLAP storage mode.
Total article views: 7037
Views in the last 30 days: 9
Automatic MOLAP doesn't show newly inserted record in database.
Publication Database moving to different Storage
Storage costs are constantly rising, especially for databases as we gather more and more data. Howev...
Microsoft has released a whitepaper on SQL Server 2005 Physical Database Storage Design
Do you use or need a database process framework? Read on to see if this is something that might help...