Introduction to SQL Server 2000 Analysis Services: Working with Dimensions

Tuesday Sep 10th 2002 by William Pearson
Share:

The second article of William E. Pearson's advanced SQL series Introduction to MS SQL Server 2000 Analysis Services continues working with dimensions and presents options for building more customized cubes.


About the Series

This is the second article of the series, Introduction to MS SQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series will be an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services ("Analysis Services"), with each installment progressively adding features designed to meet specific real-world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.


Introduction

In the first article of the series, we used the Cube Wizard to build our first cube with the assistance of the Dimension Wizard. While the Dimension Wizard is a helpful tool for rapid cube design, we often come across scenarios where we have to take more direct control of the dimension design process. We accomplish this through the use of the Dimension Editor, which exposes numerous properties of a dimension that are not accessible to us through the use of the Dimension Wizard.

Using Analysis Services, we can create our basic dimensions through the use of the wizard, or we can use the manual Dimension and Cube Editors to build our dimensions -- and indeed, our entire cube structure, from scratch. (I often do a rapid design of an overall cube "skeleton"- a "draft setup," as it were, using the Wizards - which I then return to "fine tune" with the respective Editors. Using the Dimension Editor allows us to generate much more sophisticated cubes overall and to produce OLAP data sources that help us analyze more precisely the attributes of our business that we deem to be important.

In this article we will create dimensions similar to those with which we worked before, using the Dimension Editor to illustrate options for building a more customized cube. As we will see in the steps we undertake together, once we have created our dimensions, we can set and modify various properties for optimization and other purposes.

We will begin our exploration of dimensions by creating a database similar to the one we created in Lesson One, Creating Our First Cube. Database creation is ordinarily the first step in creating a cube. We will then work with two of the three main windows of Analysis Manager, the Main Console and the Dimension Editor (the Cube Editor, being the third of the main windows, does not play a significant role in this lesson), to recreate the dimensions we created via the Wizards in our first lesson. Our example will continue with the next two lessons, Handling Time Dimensions and Parent-Child Dimensions, leading to the fifth lesson, Working with the Cube Editor. In Lesson Five we will pull together the components constructed in Lessons Two through Four, to assemble a cube similar to, but more sophisticated than, the cube we generated in the first lesson.

In this article, we will:

  • Create a new OLAP database;
  • Become familiar with the Dimension Editor by creating a single-table dimension;
  • Learn to use the Dimension Editor to manipulate dimensions, and the hierarchical levels that exist within dimensions;
  • Create Multi-table Dimensions;
  • Manipulate dimension and member properties, and discuss design and development characteristics for each.


Page 2: Setting up the Database and Data Source



Preparing to Work with Dimensions - Creating a Database

To begin the steps of the lesson, we will create a new database/data source for the FoodMart 2000 sample provided with the Typical MSSQL 2000 Analysis Services installation. As part of the preparation for the coming lessons, we will replicate the process found in our first lesson.

As we learned in Lesson One, before we can design a new cube, we need to set up a database -- more specifically, in Analysis Manager, we need to set up an OLAP database. The OLAP database we create will organize cubes, roles, data sources, shared dimensions, and other objects that we will cover in later segments. We will call our OLAP database MyCube2 (be careful here - you cannot rename a database in the Analysis Services console once it is created), setting it up initially by right-clicking on the Server we see at the left of the console. Keep in mind that, among other objects, the database can contain multiple cubes, each with a single data source. In addition to setting up our database, we will link a data source to our database before we start to construct our cube.

1. We start Analysis Manager, then right-click on the Analysis Server name (shown as MOTHER in Illustration 1 below).



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

 

 

 

 

 

 

 

Table 4: Dimension Properties - Advanced Tab


We will revisit a number of the dimension setpoints throughout the series. They, too, are powerful attributes to use in the "global" setting of many member behaviors, as well as dimension and level characteristics. The dimension and dimension level setpoints above also interact to enforce properties and attributes within the membership of cube objects in general, and so must be considered simultaneously in any design/development decisions.


Page 9: Summary- and Detail- Level Properties



Summary- and Detail- Level Properties

Now let's look at summary- and detail-level member properties. Member properties can exist at any level of the hierarchy, as we have seen from the above tables and at other points in the lesson. There are myriad ways to use this circumstance to add valuable information to our dimensional levels. Moreover, many tables we might use in dimension building contain columns that, while housing valuable information, might not warrant placement as a discreet level in the dimensional hierarchy. Let's gain some exposure to potential uses for summary- and detail-level member properties to accommodate these scenarios.

First, let's consider a situation in our current multi-table dimension build where a summary-level member property might be used to add value to our design. The product_class table contains a column called product_family. Let's assume, in the case of the current example, that management wants to be able to easily display the product_family information but does not want to introduce the information as a new level in the somewhat simple but effective hierarchy that we have already constructed for the Product dimension (any given department can only belong to one family, after all, they reason). They do not intend to routinely report summaries based upon the product_family attribute with the cube that will be driven from the model we are building, but only want the information on an "as needed" basis. We will take the following steps to assign the product_family column to a new product_department level, creating a summary-level member property in the process.

16. Add a product_department level to the hierarchy, immediately above the Category level, by dragging the product_department column from the product_ class table (you may need to click the Schema tab to access the table) onto the Category level in the Dimension tree.
17. Click / highlight the new product_department level, then click the Basic tab in the Properties pane below the Dimension tree.
18. Rename the product_department level to Department by making the appropriate change to the Name value in the Properties pane.
19. Press Enter.

The Dimension tree should now appear as illustrated below.



Illustration 30: The Dimension Tree with the new Department Level


20. With the Department level highlighted, select Insert --> Member Property at the top menu.

The Insert Property dialog appears, as shown in Illustration 31 below.



Illustration 31: The Insert Member Property Dialog


21. Double-click product_family in the Member Property dialog.

The Insert Property dialog closes, and we return to the Dimension Editor, where we can see the new Product Family addition to the Member Properties folder, under the Department level in the Dimension tree, as shown below.



Illustration 32: The Added Member Property Appears in the Dimension Tree


22. Select File --> Save from the top menu to save the modifications to the Product dimension.

We have added a summary-level member property to accommodate the needs of the Information Consumers to be able to associate each Product Family attribute with its respective Product Department in their reporting functions.


Page 10: Conclusions



To conclude the lesson, let's consider a scenario where we might employ a detail-level member property to add value to our dimension structure. Through a process of capturing business requirements from management for consideration in our dimension design and development, we become aware of a column in the product table, low_fat, which we immediately identify as an attribute that hardly requires a level unto itself in the Product dimension. We also eliminate the attribute from consideration as a summary-level member property, because none of the currently existing levels could accommodate this wholly product-centric attribute; a casual browse of the Category column in the data reveals that even the Category level is too broad (any given Category member contains multiple types of products, and none would be limited to "low fat" members only) for the "low fat" attribute to apply in meaningful way.

This is a perfect case for a detail-level member property to enter the picture. Because the "low fat" information relates to products, but not to the levels under which the products are grouped, we will include this additional information in the Product dimension as a detail-level member property that resides at its lowest dimensional level, Product Name.

23. Expand the Product Name level to reveal its Member Properties folder.
24. Drag the low_fat column from the product table (Schema pane) onto the now exposed Product Name Member Properties folder.

Low Fat appears as a property under the Product Name Member Properties folder, as shown in Illustration 33 below.



Illustration 33: The New Detail-Level Member Property


25. Click the Data tab and expand the hierarchy as follows to see the results depicted below:

All Product --> Baking Goods --> Jams and Jellies --> Jelly --> Landslide Grape Jelly



Illustration 34: Confirmation of the Detail - Level Property in the Dimension Members Hierarchy - Member Properties List


The "-1" depicted as the Value in the Member Properties List is simply a form of the -1 / 0 representation of "yes / no" with which most of us are familiar.


Page 11: Next in Our Series



Next in Our Series

In this, the second article of the Introduction to MSSQL Server 2000 Analysis Services series, we created similar dimensions to those with which we worked in our first lesson, Creating Our First Cube. We did this with a focus toward using the Dimension Editor to illustrate some of the myriad options that this tool offers for a more flexible, custom design. To prepare for this approach toward understanding dimension design and construction in Analysis Services, we created a new OLAP database and discussed the differences between approaching dimension design with the Cube Editor, versus undertaking the process with the Cube and Dimension Wizards that we used previously.

We first undertook the creation of a single-table dimension, the simpler of two main types of dimensions, before moving to the construction of the slightly more complicated multi-table dimension. Once we performed the various general tasks involved in recreating our dimensions from Lesson One with the Dimension Editor, we turned an eye toward setting and modify various member and dimension properties for model optimization and other purposes, as an extension of our use of the Dimension Editor to manipulate dimensions, and the hierarchical levels that exist within dimensions. We exposed several considerations that often arise in dimension design and construction, and explored numerous options that we can leverage in the Dimension Editor to preempt issues, and to provide effective reporting and analysis environments for organizational Information Consumers.

Our example will continue through the next two lessons, Handling Time Dimensions and Parent-Child Dimensions, leading to the fifth lesson, Working with the Cube Editor. In Lesson Five we will pull together the components that we have constructed in Lessons Two through Four, to assemble a cube similar to, but more sophisticated than, the cube we generated in the first lesson.


See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved