You design a Business Intelligence (BI) solution by using SQL Server 2008. You plan to deploy a new database to the SQL Server 2008 Analysis Services (SSAS) instance. The database contains a cube. The cube contains three Type 1 slowly changing dimensions. The database is updated throughout the day by adding 5,000 rows of data every hour. You need to ensure that the cube always contains up-to-date data. You also need to ensure that the users can access the cube during cube processing.
What should you do?
A.
Use the relational online analytical processing (ROLAP) cube storage model.
B.
Use the hybrid online analytical processing (HOLAP) cube storage model. Use the snapshot isolation level in the relational database that the cube is built on.
C.
Use the automatic multidimensional online analytical processing (MOLAP) cube storage model.
D.
Use the hybrid online analytical processing (HOLAP) cube storage model. Use SQL Server 2008 Integration Services (SSIS) pipeline tasks to schedule periodic cube updates.
Explanation:
Tip: "up-to-date data … access the cube during cube processing" = "ROLAP"ROLAP
Relational OLAP (ROLAP) does not make a copy of the facts on SSAS. It reads this information from the star schema source. Any aggregations that are designed are written back to tables
on the same star schema source system. Query performance is significantly slower than that of partitions using MOLAP or HOLAP; however, particular business scenarios can be well served by using ROLAP partitions:
Huge amounts of source data, such as cubes that are many TBs in size
Need for near real-time dataor example, latency in seconds
Need for near 100 percent cube availabilityor example, downtime because of processing limited to minutes or seconds
MOLAP (default) Source data (fact table rows) is copied from the star schema to the SSAS instance as MOLAP data. Source metadata (which includes cube and dimension structure and dimension data) and aggregations are copied (for dimension data) or generated (for all other metadata and aggregations). The results are stored in MOLAP format on SSAS, and proactive caching is not used.
MOLAP (nondefault) Source data is copied. Metadata and aggregations are stored in MOLAP format on SSAS. Proactive caching is enabled. This includes scheduled, automatic, and medium- and low-latency MOLAP.
HOLAP Source data is not copied, metadata and aggregations are stored in MOLAP format on SSAS, and proactive caching is enabled
(Smart Business Intelligence Solutions with Microsoft SQL Server 2008, Copyright 2009 by Kevin Goff and Lynn Langit)http://msdn.microsoft.com/en-us/library/ms174915.aspx
Partition Storage Modes and Processing
MOLAP
The MOLAP storage mode causes the aggregations of the partition and a copy of its source data to be stored in a multidimensional structure in Analysis Services when the partition is processed. This MOLAP structure is highly optimized to maximize query performance. The storage location can be on the computer where the partition is defined or on another computer running Analysis Services. Because a copy of the source data resides in the multidimensional structure, queries can be resolved without accessing the partition’s source data. Query response times can be decreased substantially by using aggregations. The data in the partition’s MOLAP structure is only as current as the most recent processing of the partition.
As the source data changes, objects in MOLAP storage must be processed periodically to incorporate those changes and make them available to users. Processing updates the data in the MOLAP structure, either fully or incrementally. The time between one processing and the next creates a latency period during which data in OLAP objects may not match the source data. You can incrementally or fully update objects in MOLAP storage without taking the partition or cube offline.
ROLAP
The ROLAP storage mode causes the aggregations of the partition to be stored in indexed views in the relational database that was specified in the partition’s data source. Unlike the MOLAP storage mode, ROLAP does not cause a copy of the source data to be stored in the Analysis Services data folders. Instead, when results cannot be derived from the query cache, the indexed views in the data source is accessed to answer queries. Query response is generally slower with ROLAP storage than with the MOLAP or HOLAP storage modes. Processing time is also typically slower with ROLAP. However, ROLAP enables users to view data in real time and can save storage space when you are working with large datasets that are infrequently queried, such as purely historical data.
HOLAP
The HOLAP storage mode combines attributes of both MOLAP and ROLAP. Like MOLAP, HOLAP causes the aggregations of the partition to be stored in a multidimensional structure in an SQL Server Analysis Services instance. HOLAP does not cause a copy of the source data to be stored. For queries that access only summary data in the aggregations of a partition, HOLAP is the equivalent of MOLAP. Queries that access source datafor example, if you want to drill down to an atomic cube cell for which there is no aggregation datamust retrieve data from the relational database and will not be as fast as they would be if the source data were stored in the MOLAP structure. With HOLAP storage mode, users will typically experience substantial differences in query times depending upon whether the query can be resolved from cache or aggregations versus from the source data itself.
Partitions stored as HOLAP are smaller than the equivalent MOLAP partitions because they do not contain source data and respond faster than ROLAP partitions for queries involving summary data. HOLAP storage mode is generally suited for partitions in cubes that require rapid query response for summaries based on a large amount of source data. However, where users generate queries that must touch leaf level data, such as for calculating median values, MOLAP is generally a better choice.