Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 20, 2004

Introduction to MSSQL Server 2000 Analysis Services: Basic Storage Design - Page 4

By William Pearson

Storage Design in a Simple Cube Structure

Let's take a look at the Storage Design Wizard in a practice exercise.

We will work with a simple cube structure, to minimize distraction from the steps involved. We will return to the Storage Design Wizard within the context of managing partitions, among other considerations, in a subsequent article where more complex scenarios will arise.

1.  Right-click on the DBJ_STORDESIGN sample cube.

2.  Click Design Storage on the context menu that appears, as shown in Illustration 6.

Illustration 6: Selecting Design Storage from the Context Menu

The Storage Design Wizard Welcome dialog appears, as depicted in Illustration 7.

Illustration 7: Welcome Dialog - Storage Design Wizard

3.  Click Next.

The Select the type of data storage dialog appears. (Had aggregates already existed in the cube, an Aggregates already exist dialog would have appeared.) It is here that we select from the storage modes that we discussed earlier.

4.  Click the MOLAP radio button to select the MOLAP storage mode.

The Select the type of data storage dialog appears as shown in Illustration 8.

Illustration 8: Select the Type of Data Storage Dialog

Recall from our discussion earlier that the MOLAP option places both the detail data and the aggregations in the cube. This will be a good selection for our example, so that we can focus on the further actions of the Storage Design Wizard. We will devote future articles to the characteristics and appropriate uses of the ROLAP and HOLAP options.

5.  Click Next.

The Set Aggregation Options dialog appears. Within this dialog, we can exploit more of the powerful features of the MSAS Storage Design Wizard. Here, MSAS determines the combination of aggregations that give us the most "return," within the cube design we have submitted. The process is easy and needs to be accomplished only infrequently, providing that it is performed correctly at appropriate points in the life cycle of a given cube.

The Storage Design Wizard applies an 80 / 20 rule within the sophisticated algorithm that we mentioned earlier in helping us to attain, at least from a preliminary perspective, the optimal mix of aggregations within our cube structure. Because all aggregated measures in a cube are derivatives, higher-level aggregations of measures can be derived, upon demand, from lower level aggregations. The example that is cited most often is that of an aggregation that exists at intersects of the Time dimension and another dimension in the cube.

Let's take, for example, a Store by Month aggregation. This aggregation can "roll up" to Store by Quarter, Store by Year and other higher-level aggregations within MSAS. The benefit of this is that all the higher-level aggregations do not need to be stored in their "materialized" state, adding to the overall space requirement in what can be an exponential manner. Precalculated aggregations need not exist for every rolled up intersect. MSAS allows for the dynamic generation of these aggregations upon demand - in effect, they can be maintained as "virtual" aggregations.

On the Set aggregation options dialog, we can set parameters that affect both aggregation storage and query performance. In the Aggregation options section of the dialog, we can mandate restrictions on the total cube size by setting an upper limit for the amount of space that we feel we can afford to devote to the cube. The algorithm is then put to work to determine the "best mix" of aggregations that it can manage in the space we dictate.

We will leave the Estimated storage reaches selection at the default of 100 MB for this exercise. We will, however, make an adjustment to the next parameter, Performance gain reaches.

6.  Click the Performance gain reaches radio button to activate the percentage box to its right.

7.  Type in 20 for the percentage.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM