- 50,000-Foot View
- Populating the Fact and Dimension Tables
- Indexing Database Tables
- Building the Cube
- Defining the Storage Model
- Automating the Tasks
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Defining the Storage Model
Next you need to define the storage model and build aggregations—precalculated summary values that will appear on the reports. In Analysis Services 8.0 (which is part of SQL Server 2000), the tasks of defining and building aggregations are referred to as designing storage and processing the cube. There are multiple ways of storing the data with Analysis Services—Multidimensional OLAP (MOLAP), Relational OLAP (ROLAP), and Hybrid OLAP (HOLAP). MOLAP stores data on the file system in multidimensional format; this storage type is the most efficient as far as the amount of storage and data retrieval speed is concerned. The downside is that MOLAP does not seem to handle huge amount of data too well. If your data warehouse is less than 100GB, no need to worry—choose MOLAP.
ROLAP, on the other hand, stores all aggregations and data in the relational format. It creates additional tables in SQL Server for each defined aggregation. ROLAP requires a lot of storage space— much more than the database itself—so be careful. HOLAP is supposed to combine the best of the two approaches (MOLAP and ROLAP); however, in my experience, HOLAP is not nearly as efficient as MOLAP.
When you've chosen the storage option, Analysis Services lets you define the performance optimization level, as opposed to the storage space required. Of course, you should try to optimize the performance as much as you can, but 80% of performance gain will usually give you appropriate number of aggregations for the majority of your queries. You also need to watch the storage space usage, especially if you're using ROLAP or HOLAP. MOLAP seems to store aggregations in a very compact manner.
Processing the cube is completely automated: If Analysis Services finds all dimension members and appropriate keys in the fact table, it will rebuild the cube without any help from you. If the fact table contains the keys not found in the dimension tables, the cube-processing task will fail. That's why it's important to refresh your dimensions (in Microsoft Analysis Services) before rebuilding the cube.