Click to Enlarge
Illustration 1: Right-Click on the Analysis Server

We click New Database, and the Database dialog box appears. We fill in the Database Name, MyCube2, and a description that might be of value to a user or developer down the road. Let's simply add "Lesson 2 Cube creation with MSSQL Server 2000 Analysis Services," here - the description is optional, of course. The dialog appears as below.

Illustration 2: The Database Dialog Box

2. Click OK, and note that MyCube2 appears in the left-side tree, complete with predefined, empty folders for object storage later.

We will need to connect to the data source from which we wish to draw values.

3. Expand MyCube2's database / cube icon, (clicking on the plus (+) sign to the left of the icon will do the trick), right click the folder within MyCube2 called Data Sources and select New Data Source on the popup shortcut menu.

The Data Link Properties dialog box appears, with its Provider, Connection, Advanced and All tabs, as shown in Illustration 3 below. Beginning with the Provider tab we will select the Microsoft Jet 4.0 OLE DB Provider (the native MS Access OLE DB provider). (For more on this, review the on-line documentation for OLE DB Providers and data sources in general.)

Illustration 3: The Data Link Properties Dialog - Provider Tab

4. Click Next.

The focus moves to the Connection tab.

5. We select the FoodMart 2000 database, located by default in the [Install Directory]:\Program Files\Microsoft Analysis Services\Samples directory, by clicking on the ellipses (...) button, to navigate to the .mdb as shown in Illustration 4 below.

Illustration 4: Selecting the FoodMart 2000 Database

6. Click Open, returning to the Connection tab, as we see below:

Illustration 5: The Data Link Properties Dialog - Connection Tab

7. Leaving all other Data Link Properties setpoints at their defaults for now, test the connection by clicking the Test Connection button in the bottom right-hand corner of the Connection tab.

We should get a verification dialog confirming that the source has been established in our definition (as shown in Illustration 6).

Illustration 6: Verification of the Connection to the FoodMart 2000 Database

8. Click OK, and the Microsoft Data Link verification dialog box closes.
9. Click OK on the Data Link Properties tab.

The Data Link Properties dialog closes, and we can see that the new source appears under the Data Sources folder in the tree area on the left side of the Management Console, displaying the actual file name.

As mentioned in Lesson One, we might want to make this a more intuitive - or at least shorter - name in order to keep a tidy appearance. A simple "rename" capability is not in the cards - unlike circumstances in the similar looking, but functionally different, Windows Explorer scenario. A right-mouse click on our new data source will, however, allow a Copy action.

1. Right-click the new data source.
2. Click Copy.
3. Highlight the Data Sources folder.
4. Right-click the MyCube2 database folder we created earlier.
5. Select Paste from the popup menu,

This causes Analysis Services to indicate that a duplicate has been detected, and to prompt us for a unique name to rectify the confusion. We will respond to the new name request with MyFoodMart2, using the dialog box that appears (as shown below in Illustration 7).

Illustration 7: Changing the Name of the Newly Copied Cube as a Means of Renaming

The Duplicate Name dialog thus acts as our agent of change, and, once we click OK, adds the newly named data source under the data sources folder. All that remains is to delete the original data source, from which we cloned MyFoodMart2.

6. Right-click the original data source, and select Delete on the popup menu, then click the Yes button, to organize our new data source folder.

We now have an OLAP database in place, linked to a valid data source (the FoodMart 2000 database). The next step in our exploration will be to introduce the Dimension Editor, and to design a single-table dimension.

Page 3: Building and Managing a Single-Table Dimension

Building and Managing a Single-Table Dimension

Before can create a cube, one or more dimension structures must be in place. While we have experienced dimension creation as integrated steps of the cube building cycle in Lesson One, we will be working without the Cube Wizard in coming lessons, and so will need to create our dimensions prior to building our cube. In Lesson One we discussed dimensions from a star-schema, otherwise known as single-table dimensions. We will begin this lesson by becoming familiar with the Dimension Editor in its simplest context: we will use the Dimension Editor to build a single-table dimension.

To gain exposure to working with dimensions and hierarchical levels, we will perform the following steps.

1. Right-click the Shared Dimensions folder in the MyCube2 Database tree.
2. Select New Dimension --> Editor, on the shortcut menu that appears, as pictured below.

Click to Enlarge
Illustration 8: Initializing the Dimension Editor via the Shortcut Menu

The Choose a Dimension Table dialog appears, listing tables from which we can choose to build our dimension.

3. Select Region from the Tables list, as shown in Illustration 9.

Illustration 9: The Region Table is selected in the Tables List

The Details pane of the Choose a Dimension Table dialog becomes populated with the Column names of the Region table, as shown above.

4. Click OK.

The Dimension Editor appears, as shown in Illustration 10.

Illustration 10: The Dimension Editor, with Keyed Sections

The sections of the Dimension Editor correspond to the keys shown in Illustration 10 above, as follows:

A. Dimension Tree
B. Schema Pane
C. Properties Pane
D. Schema Tab
E. Data Tab

5. Click the Name section of the Properties pane, Basic tab. Type in Region as the value.

This names the new dimension as Region, and places it in the Dimension tree, as shown below:

Illustration 11: The Region Dimension appears in the Dimension Tree

6. Drag the sales_region column from the Region table over the dimension name (the newly added "Region") in the Dimension tree, and drop to create a new dimension level as shown in Illustration 12.

Illustration 12: The Sales Region Level appears in the Dimension Tree

IMPORTANT: Note that while levels can be renamed via the Properties pane, dimension names, once saved, cannot be changed.

7. Rename the Sales Region level in the dimension tree, highlighting it first, then typing Region in as the new name. Press Enter to save.

We now have the Region table (the relational table, represented in the Schema pane, which is the actual data source), a Region dimension (within which the Region hierarchy will reside) and a Region level (which will contain the Region Sales members). To see the actual members, we can browse the dimension.

8. Click the Data tab (see Illustration 10 above). This retrieves a hierarchical representation of the Region dimension (the top level being named "All Region" by default). We can expand the All Region level of the Region dimension to see the eight regional members, as depicted below.

Illustration 13: A Browse of the Region Dimension Members

Page 4: Building a Single-Table Dimension (Continued)

We now wish to complete the addition of the other levels to our Region dimension. We will follow the common-sense hierarchy for the table members, based upon geography in our example, while acknowledging that relationships might not be intuitive in many real-world scenarios. As most of us know, understanding the relationships of the table members to each other, as well as understanding their reporting significance, becomes vital to an effective design process.

9. Switch to the Schema tab in the Dimension Editor (See Illustration 10 above for a "map," if necessary).
10. Drag the sales_district column from the Region table onto the Region dimension.

Dragging the sales_district column onto the Region dimension makes it appear beneath the Region level, as shown below. This is because dropping the sales_district column onto the Region dimension adds the column at the dimension's lowest level. Our intent is to make sales_district lower in the hierarchy than Region; hence this is a good way to start.

Click to Enlarge
Illustration 14: Adding the Sales District Level to the Dimension Tree

11. Rename the new level to District (see Step 7 above for guidance).

We next want to add sales_country as a level above Region.

12. Drag the sales_country column from the Region table onto the Region level.

Dragging the sales_country column onto the Region level makes it appear above the Region level, as shown below.

Illustration 15: Adding the Sales Country Level to the Dimension Tree

13. Rename the new Sales Country level to Country.
14. Drag the sales_state_province column from the Region table onto the District level.

Dragging the sales_state_province column onto the District level makes it appear above the District level, (it becomes the parent level of the level upon which it is dropped) as shown below.

Illustration 16: Adding the Sales State Province Level to the Dimension Tree

15. Rename the new Sales State Province level to State.
16. Drag the sales_city column from the Region table onto the Region dimension.

Dragging the sales_city column onto the Region dimension makes it appear beneath the District level (the lowest level before). It drops to the lowest level of the dimension, as explained earlier.

17. Rename the new Sales City level to City.
18. Compare the Region Dimension tree hierarchy to the partial view of the hierarchy depicted below.

Illustration 17: The Region Dimension Hierarchy

We can review our work by performing another browse of the dimension, this time seeing the members that exist at each of the levels we have just created.

19. Click the Data tab (for location, see Illustration 10 above). This retrieves a hierarchical representation of the Region dimension. We can expand the Region dimension to see the hierarchical levels that we have added in the foregoing steps, as depicted below.

Illustration 18: The New Hierarchical Levels of the Region Dimension

Simply because we can, let's change the Dimension Members List's "All Region" top level name to "World" in our Region Dimension Members list.

20. In the Dimension tree, click / highlight the Region dimension.
21. Click the Advanced tab (Properties pane below the Dimension tree).
22. Click the All Caption property value to highlight it.
23. Change the Caption to read "World".

The top level name will change to "World" when any refresh action takes place (for example, clicking the Schema tab, then clicking the Data tab).

24. Click the Save Button (shown below), or select File --> Save, to save the single-table dimension that we have created.

Illustration 19: The Save Button

Page 5: Building and Managing a Multi-Table Dimension

Building and Managing a Multi-table Dimension

As we saw in Lesson One, the Dimension Wizard provides a radio button setpoint, in its "Choose how you want to create the dimension" dialogue, where we can indicate that we need to create a multi-table dimension (a "Snowflake Schema"), as shown in the illustration below.

Click to Enlarge
Illustration 20: The Choose How You Want to Create the Dimension Dialog

While we can use the Dimension Wizard to add the tables we need to accomplish this, our objective in the next several lessons is to lessen our dependence on "wizardry" and, more importantly, to familiarize ourselves with the advanced features of the Dimension Editor. We will add the tables ourselves, with the objective of recreating the Product dimension that we created via the Wizard in our first lesson.

This will involve dealing with properties with which we were not confronted using the Dimension Wizard, but therein lay opportunities to more finely customize our dimensions, and to add a higher level of sophistication to our overall cube design. We will expose some of these properties, and discuss other relevant design considerations, as we build the multi-table dimension.

Building the Dimension

We begin creating a multi-table dimension by taking the following steps:

1. Select from the Dimension Drop-down List box in the upper right corner of the Schema pane (see Illustration 10 above for location of the Schema pane.)

The Dimension Drop-down List box always indicates the dimension in which we are currently working. Illustration 21 shows the Dimension Drop-down List box, with the option selected.

Illustration 21: The Dimension Drop-down List Box

We wish to select the product and product_class tables, as we did in Lesson One, to allow us to define the Product dimension. While it is always best to select multiple tables from the lowest to the highest, or the highest to the lowest, level in the hierarchy (to ensure that joins are properly placed), in our example (with two tables) this should not present too serious a problem.

2. Select the product table.

We see the Details pane populated with the columns of the product table, as shown in Illustration 22.

Illustration 22: The Select Table Dialog with product Table Selected

3. Click OK, and return to the Dimension Editor, with the product table now appearing on the right side.
4. Select Insert from the top menu, Tables ... option, as shown below.

Illustration 23: Selecting Insert --> Tables ... from the Top Menu

5. When the Select Table dialog appears, (shown below) select the product_class table, in accordance with our selection in Lesson One.

Illustration 24: The Select Table Dialog with product_class Table Selected

6. Click Add, and then Close.

We see the two tables appear together when the Dimension Editor reappears, as seen in Illustration 25 below.

Illustration 25: The Dimension Editor Dialog with product and product_class Tables

A join appears between the two tables at product_class_id. The join is appropriate, so we leave it undisturbed. NOTE: Had we needed to add a join, this would be done in a manner very similar to the process of creating joins in MS Access and other applications. The column to be joined from one table is simply dragged over onto its "partner" in the other table(s). To delete a join, again like MS Access, et. al., we simply click the join line to select it, then press Delete.

7. Click the Basic tab of the Properties pane.
8. Type in Product as the Name of the new Dimension.
9. Press Enter.

The Dimension tree reflects the new Product dimension, as shown in the illustration below.

Illustration 26: The New Product Dimension Appears in the Dimension Tree

Page 6: Dimension and Member Properties and Considerations

Dimension and Member Properties and Considerations

While an understanding of the workings of the Dimension Editor is key to the successful design and building of our dimensions and, ultimately, cubes, a good working knowledge of the data itself is another essential part of designing a useful structure. We will explore our data as we design dimensions to ensure sound planning and construction of hierarchical levels. In the real world, this is an important stage; it is a phase within which we often adjust properties to be consistent with our design as we determine the business requirements for the dimensions and cube, and compare these desired states (of the Information Consumers) with the existing state (of the data itself).

Analysis Services makes a quick review of the data simple. One of the niftiest features of the Dimension Editor, from a designer's/developer's perspective, is the capability it offers us to do an ad hoc browse of a sample of the data in any table that we have in our designer's crosshairs. Let's do a quick browse of the Product table, and see how useful the information returned can be in helping us consider dimension property setpoints within the context of our data.

Browsing the Data

10. Right-click on the product_class table with the mouse pointer on the caption bar (the blue bar in the picture below, which contains the table name, at the top of the table).

Illustration 27: Right-click on the Caption Bar to Initialize a Data Browse of the Table

1. Select Browse Data.
2. The Browse Data Viewer appears - with the first 1,000 rows of data in the table, as shown below.

Illustration 28: The Browse Data Viewer

3. Close the Browse Data viewer.
4. Double-click the product_name column in the product table.

Product_name is added as a new bottom level to the Dimension tree. The effect is much the same as dragging a column name onto the Product dimension, after the manner of our single-table dimension build in the earlier section.

5. Drag the product_subcategory column from the product_class table onto the product_name level in the Dimension tree.
6. Drag the product_category column from the product_class table onto the product_subcategory level.

As we might have expected from working with the Region hierarchy in the Dimension tree in the Single-table Dimensions section earlier, this creates new levels for the dragged columns, each above the levels onto which they have been dropped.

7. Rename the Product Category and Product Subcategory levels in the Dimension tree to Category and Subcategory, respectively. (For guidance on renaming levels, see the earlier section, Building and Managing a Single-Table Dimension.)

The Dimension tree should appear as illustrated below.

Illustration 29: The Newly Defined Product Hierarchy - Dimension Tree View

Page 7: Examining Property Setpoints

Examining Property Setpoints

We can examine various setpoints at this stage to see if we need to adjust them to fit the realities of the data (upon which we can easily perform ad hoc browses, courtesy of Analysis Services). Several key setpoints exist in the Properties pane. Let's examine some of these.

8. Highlight the Category level in the Dimension tree.
9. Click the Basic tab in the Properties pane.
10. Review the setpoints, together with the explanations, for a standard dimension level, in Table 1 below.

		Basic Tab Properties
		(Standard Dimension Level)
Name		Explanation		Notes / Comments
Description		The level description		Fixed Text Value
Member Key Column		The name of the column that contains member names		Editable -- Syntax must be preserved Can be used as a sort basis, in conjunction with Member Name Column or otherwise, among other purposes Contains integer keys for the member Default value -- same as Member Name Column Set as a unique column in the source table, such as a unique ID field, the Key setpoint prevents grouping of members with identical names, etc. (but we would first have to set Dimension Member Names Unique property to allow for this -- See Table 4) Values can consist of more than a single column name, including any SQL expression that is understood by the relational data source, provided that the expression returns a number or a string. (Concatenations of column values are a common example.)
Member Name Column		The name of the column that contains member keys		Editable -- Syntax must be preserved Identifies names of table and column that provide member names for the level. Can be used as a sort basis, in conjunction with Member Key Column or otherwise, among other purposes Typically appears in heading row or columns on a report -- use meaningful terms! Default value -- same as Member Key Column Values can consist of more than a single column name, including any SQL expression that is understood by the relational data source, provided that the expression returns a number or a string. (Concatenations of column values are a common example.)

Table 1: Level Member Properties (Standard Level) - Basic Tab

11. Click the Advanced tab in the Properties pane.

Review the setpoints, together with the explanations, for a standard dimension level, in Table 2 below.

		Advanced Tab Properties
		(Standard Dimension Level)
Name		Explanation		Notes / Comments
Member Count		The estimated number of level members
Member Keys Unique		Indicates whether member keys are unique throughout the entire level		Disabled for a top dimension
Member Names Unique		Indicates whether member names are unique throughout the entire level		False allows identical names, but Keys would have to be unique to use (and in any case for a top dimension) Keep in mind that a False setpoint also means groups of members with identical names are treated as a single members for purposes of numerous properties / operations (i.e., the group would appear as a single member in the hierarchical tree of the Schema pane - Data tab).
Level Type		Indicates the level type
Key Data Size		The size of the key column in bytes
Key Data Type		The key column data type
Hide Member If		Determines how placeholder members that should be hidden are identified
Visible		Determines whether the level is visible
Order By		Property base on which members will be ordered		Controls sort basis -- options are Key or Name, but other options can be handled through various means
Custom Rollup Formula		MDX expression used to override the default rollup of values in the level
Custom Members		Determines whether custom formulas can be created for individual members
Custom Member Options		Determines whether calculation options can be defined for custom members on this level and unary operators on the following level
Grouping		Determines whether members in the level are used individually or are grouped together into group members
Unary Operators		Determines whether members have unary operators that control how individual member values are aggregated into their parent member's value		Great potential for allocation and other business uses

Table 2: Level Member Properties (Standard Level) - Advanced Tab

Page 8: Examining Property Setpoints (Continued)

We will revisit a number of the level setpoints throughout the series. They are powerful attributes to use in the "global" setting of many member behaviors as well as level characteristics.

Developmental review of, and ongoing planning for, the Dimension Properties is also highly critical to meeting the business needs of the reporting/analysis population. Let's review dimensional setpoints at this stage, as well.

12. Highlight the Product Dimension in the Dimension tree.
13. Click the Basic tab in the Properties pane.
14. Review the setpoints, together with the explanations for a dimension in Table 3 below.

		Basic Tab Properties
		(Dimension)
Name		Explanation		Notes / Comments
Name		The dimension name		Fixed Text Value -- cannot be changed once the dimension is saved!
Description		The dimension description
Data Source		The name of the column that contains member keys

Table 3: Dimension Properties - Basic Tab

15. Click the Advanced tab in the Properties pane.

Review the setpoints, together with the explanations, for a dimension, in Table 4 below.

		Advanced Tab Properties
		(Dimension)
Name		Explanation		Notes / Comments
All Level		Indicates whether the dimension contains the (All) level. The (All) level is the top level of a dimension. It contains a member that is the aggregation of all members in the level below
All Caption		The caption for the (All) level		This is the place to rid ourselves of the pervasive "All" that appears by default in top levels
Type		Type of dimension
Default Member		Determines the default member of the dimension. The default member is used when a cell is being evaluated and no other member has been specified for the dimension		The member used if the dimension is not specified in a query / browsers such as the Pivot Table List browser Standard default member is the All level member, but this can be edited.
Depends on Dimension		The name of the dimension upon which this dimension depends
Changing		Indicates that the dimension is optimized to handle data changes efficiently at the cost of some querying speed
Write-enabled		Determines whether the dimension can be written to
Member Keys Unique		Indicates whether member keys are unique throughout the entire dimension
Member Names Unique		Indicates whether member names are unique throughout the entire dimension		(See Member Key Column in Table 1)
Allow Duplicate Names		Determines whether multiple children of a single member can have the same name
Source Table Filter		Filters the source table to restrict members
Storage Mode		Determines how the dimension is stored
Enable Real-Time Updates		The Analysis server responds to changes in the dimension table(s) and automatically updates the dimension data
Virtual		Determines whether the dimension is a virtual dimension based on the tables and columns of another dimension		An "alias" concept with many possible valuable uses
All Member Formula		The MDX expression used to override the default rollup of the all member