Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation - Page 6
June 21, 2004
The Step 1 of 3 dialog is a great example of how the wizard makes design straightforward and rapid - assuming planning (based upon a solid understanding of the business requirements of the information consumers) has taken place before we embark upon cube design. We simply select from a list the data source fields that we wish to present, and how we wish to summarize each of those fields, in an efficient and easy-to-use screen.
In this step, it is important that we decide which of our source data fields it makes sense to use as data fields. Data fields contain values (that is, they are measures) that we want to summarize, such as store costs for which information consumers have a need for totals. The wizard requires that we select at least one field to be a data field.
When the dialog initially appears, the wizard has several boxes checked already. These are selected by the well-meaning (but not necessarily correct) wizard, based upon its conclusion that these fields appear to contain measure-like data. It "proposes" them, as a result, for selection in this step. It is critical to verify whether the wizard's proposals are correct, and to make any changes to fit our business requirements. The fields that we leave unchecked in this step will comprise the set of available dimension fields in Step 2, from which we will select and organize those we need to design our dimension hierarchy structures
18. Fill out the Step 1 0f 3 dialog, ensuring that only the settings in Table 1 below exist (clearing any unwanted checkboxes).
Table 1: Initial Measures List with Suggested New Names
In the above settings, we made minor modifications to the field names, as we might to fit terminology that exists in current reports, and so forth.
The OLAP Cube Wizard - Step 1 of 3 dialog now partially appears, with all relevant selections displayed, as shown in Illustration 12.
19. Click Next.
The OLAP Cube Wizard - Step 2 of 3 dialog appears.
In this step, we organize the descriptive data into dimensions, each of which can be used as a field in any report we generate from our cube. The organization of the fields in levels of detail that we design at this stage should allow information consumers to select the level of detail to view, starting with high-level summaries, drilling to details and zooming back to summaries as appropriate to meet their reporting needs.
The wizard requires that we designate at least one dimension for a cube. We can designate fields that provide isolated facts, and do not belong in any particular hierarchy, such as the Store Type in our example, as dimensions with a single level. Rather obviously, our cube will be more useful for reports if we design some of the fields, as levels, to "roll up" to higher levels and dimensions.
To create a level within a dimension, drag each field from the Source Fields list onto an existing dimension or level in the Dimensions box, as shown in the following steps. To rename a selection, simply right-click and select Rename from the shortcut menu that appears. (The "click label and wait" routine also enables the direct typing of changes.)
20. Move the selections shown below in Table 2 from the Source fields ("Table Name" in Table 2) list on the left to the appropriate position in the Dimensions list on the right. (To correctly place the dimensions / levels under the dimensions, use the "template" guide that automatically adjusts itself to remain at the bottom of the existing Dimension list, for each new dimension created.)
21. Rename each "Table Name" selection, with the suggested "New Name" below it, as shown in Table 2. (See Illustration 13, below Table 2, to clarify any confusion as to placement).
Table 2: Initial Dimensions List with Suggested New Names
The OLAP Cube Wizard - Step 2 of 3 dialog now resembles that partially illustrated in Illustration 13.
22. Click Next.
The OLAP Cube Wizard - Step 3 of 3 dialog appears.
23. Select the radio button with the caption Save a cube file containing all data for the cube by clicking it, if necessary.
24. Select a convenient location in which to save the cube file. (I left mine at default.)
25. Click Save, as required.
The OLAP Cube Wizard - Step 3 of 3 dialog now appears similar to Illustration 14 below.