Introduction to SQL Server 2000 Analysis Services: Working with the Cube Editor - Page 11

First, let's provide some hierarchical information regarding our products. For the purposes of our example, let's say that, based upon the requests of information consumers, we want to provide product class and category information -- information that we realize does not appear in the products table. We will insert another table, product_class, as part of meeting this requirement.

58.  Click Insert on the top menu.

59.  Select Tables on the dropdown menu.

The Select Table dialog appears.

60.  Select the product_class table, noting that the column list appears to the right in the Details section of the dialog, as shown below.

Illustration 48: Account No Level of the Customer Dimension

61.  Click Add to add the product_class table to the schema pane.

62.  Click Close to dismiss the dialog.

We note that the product_class table, joined to the product table via the product_class_id column, appears in the schema pane.

63.  Drag the following fields from the product_class table over to drop onto the folder for the SKU level we defined earlier, in the order shown:

• product_category
• product_subcategory

Dragging the above onto the folder precisely in the order shown will result in their alignment as shown below:

Illustration 49: Product Dimension with New Member Additions

64.  Right-click on each of the above newly added dimension members, and rename each, respectively, to the following:

• Category
• Subcategory

65.  Select the Basic tab in the Properties pane for the SKU level.

66.  Click the Member Name Column field.

67.  Type the following into the field:

The above expression again uses the VBA CStr function to convert the SKU field to a string that we can concatenate with the product name, much in the way we used it in the Customer dimension above. While the layout would depend upon the requirements of the information consumers, the point of the example is to show that such expressions can be accommodated within the Member Name Column field of the Properties pane. We would be able to precede the SKU identifier, sort by the product name instead of the SKU, and so forth relatively easily. (Combining different fields, as we have in our example, might make reporting and browsing easier from different information consumer perspectives).

Page 12: The Cube Editor (Continued)

See All Articles by Columnist William E. Pearson, III