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

Monday Nov 11th 2002 by William Pearson
Share:

The latest article in the advanced SQL series Introduction to MS SQL Server 2000 Analysis Services integrates many of the components constructed in previous articles, with an objective of undertaking a complete cube build that pulls together the full set of concepts explored earlier. The article demonstrates the assembly of a cube more sophisticated than those generated in earlier lessons and also introduces further capabilities as the new cube is constructed 'from scratch.'


About the Series ...

This is the fifth article of the series, Introduction to MSSQL 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 an initial cube with the assistance of the Dimension Wizard. We progressed through subsequent articles, creating similar dimensions to those we built with the Wizard, focusing largely in our second article on using the Dimension Editor to illustrate options for building a more customized cube. We continued this examination of dimensions in Article Three, where we recreated the calendar time dimension, this time focusing on the process through which the Dimension Wizard converts existing time/date fields to a time dimension, along with its hierarchy of levels and members. Article Three also exposed ways to customize the predefined, time-related properties that the wizard establishes in building the time dimension, suggesting options for customization of these properties to enhance the cube, from the dual perspectives of user-friendliness and the reporting needs of the organization. We created an example of an alternate time dimension for fiscal time reporting, and then we discussed some of the considerations surrounding the simultaneous housing of both hierarchies in the same OLAP cube structure.

In Article Four, we examined another special type of dimension, the Parent-Child dimension, and explored the attributes that make it different from a regular dimension. We discussed the considerations that surround Parent-Child dimensions, such as the recursive nature of their data sources, and various actions that must be handled differently in their creation and maintenance. We discussed unbalanced hierarchies, and scenarios where we need a parent-child dimension. We then created a parent-child dimension using the Dimension Wizard, within which we worked with levels and properties. Finally, we enabled values at the parent level of our newly created parent-child dimension.

In this lesson, Working with the Cube Editor, we will review, summarize and integrate many of the components that we have constructed, and the concepts that we have explored individually, in the last four lessons. Our objective will be to undertake a complete cube build, pulling together all that we have learned, to demonstrate the assembly of a cube more sophisticated than the cube we generated in our first lesson with the Cube Wizard. As a part of this objective, we will also introduce further capabilities as we construct our new cube "from scratch."

In this article, we will:


  • Discuss the use of the Cube Editor, as opposed to the Cube Wizard, as a means of cube construction;
  • Create a basic "starter" cube, using the fact table alone, to serve as a foundation for a more elaborate cube;
  • Process the cube to review the steps involved;
  • Expand dimensions of the cube to include the associated dimension tables;
  • Define the Member Name Column dimension property to meet illustrative business requirements of information consumers;
  • Review sample uses and purposes of Member Properties;
  • Add a derived dimension to meet illustrative information consumer needs;
  • Use the Dimension Browser as a design and review tool;
  • Revisit calculated members and add a calculated measure to our cube;
  • Work with various properties of measures and dimensions to control the behavior and characteristics of our cube.


Page 2: Working with the Cube Editor


See All Articles by Columnist William E. Pearson, III



Working with the Cube Editor

Experience with MSSQL Server 2000 Analysis Services brings with it a comfort level that makes it easy to see that the Cube Editor is quite often the best route to rapid cube design and creation. The Cube Wizard, while user-friendly and helpful with its "guided procedure" for cube construction, nevertheless provides only the basics with regard to the many properties and other attributes that often need to be addressed in developing a cube that we can effectively deploy and use in the real world. The Wizard might, as we have mentioned in past articles, provide an excellent means of generating a quick basic cube, but beyond that we're likely to call upon the Cube Editor as the tool of choice to refine and build further. After building cubes over time, we might actually find it more efficient to begin with the Cube Editor and bypass the Wizard entirely, much as we do with other applications' wizards, once we "learn the ropes" through constant practice.

In this lesson, we will use the Cube Editor from the very start, initially to build a basic cube that we will use as the foundation for several new concepts within this lesson. Our objective, as we have stated, will be to consolidate principles and procedures from earlier lessons into a single, integrated cube build. Along the way, we will take advantage of opportunities that arise to introduce new concepts, which we will be able to appreciate more fully with the preceding four lessons behind us.


Building the Basic Cube with The Cube Editor

We will launch the Cube Editor at this stage and begin a new cube. As mentioned earlier, we will initially focus upon the creation of the core cube, basing it upon the fact table of the FoodMart2000 sample database. As we have stated before, the FoodMart 2000.mdb is installed as a sample when we perform the Typical installation of MSSQL Server 2000/Analysis Services. We will begin again from square one, assuming no setup whatsoever and only the presence of the installed applications we cited in our initial article, Creating Our First Cube.

Preparation for Creating the New Cube

To begin the steps of the lesson, we will create a new database/data source in Analysis Manager for the FoodMart 2000 database. While many of us may already have the database set up from the previous lesson(s) in our series, we will perform setup again quickly, in keeping with our objective of making each lesson stand on its own, independent of the other lessons of the series. We will name the database the same as in previous lessons; if we already have a database with that name, and we perhaps want to recreate it for review purposes, we'll need to either delete the existing database or simply call the new database something else.

As we learned in Lesson One, we first create an OLAP database to organize cubes, roles, data sources, shared dimensions, and other objects. We will call our OLAP database MyCube2 (be careful here -- as we have stated before, 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. In addition to setting up our database, we will link a data source to our database before we start to construct our cube.

  1. Start Analysis Manager.
  2. Expand the Analysis Servers folder by clicking the "+" sign to its immediate left.
  3. Right-click on the Analysis Server name, to see the context menu shown below:



Illustration 1: Right-Click on the Analysis Server


  1. Click New Database.

The Database dialog box appears.

  1. 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 "Tutorials Database," here -- the description is optional, of course.

The dialog appears as below.



Illustration 2: The Database Dialog Box


  1. Click OK.

MyCube2 appears just below the existing FoodMart 2000 Database.

  1. Expand MyCube2's database/cube icon by clicking in the plus (+) sign to the left of the icon.

Note that MyCube2 appears in the left-side tree, complete with predefined, empty folders for object storage later, as shown in Illustration 3 below.



Illustration 3: The New Database with Directory Structure


Page 3: Working with the Cube Editor (Continued)


See All Articles by Columnist William E. Pearson, III



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

  1. Right click the folder within MyCube2 called Data Sources, and on the context menu, select New Data Source.

The Data Link Properties dialog box appears, with its Provider, Connection, Advanced and All tabs, as shown in Illustration 4 below. Beginning with the Provider tab (where the dialog opens by default):

  1. Select the Microsoft Jet 4.0 OLE DB Provider (the native MS Access OLE DB provider).

Note: For more on this, review the on-line documentation for OLE DB Providers, and data sources in general.



Illustration 4: The Data Link Properties Dialog -- Provider Tab


  1. Click Next.

The focus moves to the Connection tab.

  1. Select the FoodMart 2000 database in Box 1.

FoodMart 2000.mdb is located, as we have noted, by default, in the [Install Directory]:\Program Files\Microsoft Analysis Services\Samples director; Click on the ellipses (...) button, to navigate to the .mdb as shown in Illustration 5 below.



Illustration 5: Selecting the FoodMart 2000 Database


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



Illustration 6: The Data Link Properties Dialog -- Connection Tab


  1. Insert User Name and Password information, as appropriate (the illustration displays default settings).
  2. Click 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 7).



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


  1. Click OK, and the Microsoft Data Link verification dialog box closes.

We will leave all the other Data Link Properties at their default setpoints for now.

  1. Click OK on the Data Link Properties dialog.

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 shown below.



Illustration 8: Initial View of Our Newly Created Data Source


Page 4: Working with the Cube Editor (Continued)


See All Articles by Columnist William E. Pearson, III



As we mentioned in Lesson One, as well as at other points, 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 available, so we will have to be creative here; a right-mouse click on our new data source allows a Copy action, which will serve as a workaround for renaming the object in question.

  1. Right-click the new data source.
  2. Click Copy.
  3. Highlight the Data Sources folder.
  4. 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 MyFoodMart2000, using the dialog box that appears (as shown below in Illustration 9).



Illustration 9: Changing the Name of the Newly Copied Data Source as a Means of Renaming


  1. Type MyFoodMart2000 into the Name box of the Duplicate Name dialog.

Once we click OK, the Duplicate Name dialog acts to add the newly named data source under the data sources folder.

  1. Click OK to close the Duplicate Name dialog.

All that remains is to delete the original data source, from which we cloned MyFoodMart2.

  1. Right-click the original data source, and select Delete on the popup menu
  2. Click the Yes button, to confirm the deletion.

Our tree should now resemble that shown in Illustration 10.



Illustration 10: MyFoodMart2000 Appears in the Tree


In Lesson One, we used the Cube Wizard, together with the subsidiary specialized wizards (including the Dimension Wizard), as called by the Cube Wizard, to rapidly create a simple cube to explore the various aspects and steps of the process from a relatively high level. In this lesson, the focus is the creation of a cube "from scratch" with the Cube Editor. As we have already stated, we will create a core cube from the fact table upon which we will base the rest of the cube creation, together with the addition of several new components.

Our preparation for the lesson (and for the creation of any cube) is complete. We now have an OLAP database in place, linked to a valid data source (the FoodMart 2000 database). Next, we will initialize the Cube Editor, and begin creating our cube.


Page 5: Creating the Cube


See All Articles by Columnist William E. Pearson, III



Creating the Cube

The Cube Editor will first guide us through the selection of our fact table, which houses the measures upon which we seek to report. We will create a basic cube shell by taking the following actions:

1.      Right-click the new Cubes folder under the MyCube2 database we created above.

2.      Select New Cube from the initial shortcut menu.

3.      Click Editor, as shown in the illustration below.



Illustration 11: Initializing the Cube Editor


The Cube Editor appears, beginning with the Choose a Fact Table dialog, providing us with an opportunity to select a fact table for our cube, as depicted in Illustration 12 below.



Illustration 12: The Choose a Fact Table Dialog


4.      Select the Sales_Fact_1997 table.

The list of columns in the Sales_Fact_1997 table appears in the Details pane on the right half of the dialog.

5.      Click OK.

The Fact Table Row Count message box appears, asking if we want to count fact table rows, as shown below:



Illustration 13: The Fact Table Row Count Message Box


6.      Click Yes.

The Cube Editor window appears, showing the cube tree (top) and properties pane (bottom) on the left of the window, and the fact table schema (the Schema tab view) on the right, by default. The window should appear as depicted in Illustration 14 below.

We are required to select a fact table to proceed.



Illustration 14: The Cube Editor, with the Sales_Fact_1997 Table Schema View


7.      Add the following measures to the Measures folder, by dragging each from the Sales_Fact_1997 table and dropping it onto the folder, in the following order:

  • store_cost
  • store_sales
  • unit_sales

The selected measures fields appear in the Measures folder (sans underscores, which are converted to spaces; initial caps are another automatic conversion) as shown below:



Illustration 15: The Measures Folder with Newly Added Measures


Page 6: Creating the Cube (Continued)


See All Articles by Columnist William E. Pearson, III



A cube must contain at least one measure from the designated fact table; in addition, it must also contain a minimum of one dimension that is associated with a key column in the table. We will add dimensions next.

8.      Add the following dimensions to the Dimensions folder, by dragging each from the Sales_Fact_1997 table onto the folder, in the following order:

  • time_id
  • customer_id
  • product_id


Illustration 16: The Dimensions Folder with Newly Added Dimensions


The dimensions that we have added are private dimensions, meaning that they belong to this cube only. Now let's make some adjustments to the measure and dimension properties, simply to illustrate them, as well as to refresh our memories, in some cases, of several member properties attributes.

9.      Click the Time Id dimension to select it in the cube tree.

10.  Click Properties beneath the tree pane to display the properties of the Time Id dimension.

11.  Click the Basic tab in the Properties pane.

12.  Rename the dimension to Calendar.Time.

The Basic tab of the Properties pane for the Calendar.Time dimension appears as shown below:



Illustration 17: Calendar.Time Dimension, Properties Pane, Basic Tab


13.  Click the Time Id level underneath the Calendar.Time dimension to select it in the cube tree.

14.  Ensure the Basic tab in the Properties pane is selected.

15.  Rename the level to Calendar Time (note that the delimiter "." is not allowed here).

The Basic tab of the Properties pane for the single Time level appears as shown below:



Illustration 18: Time Level, Properties Pane, Basic Tab


16.  Click the Calendar.Time dimension again to select it in the cube tree.

17.  Click the Advanced tab in the Properties pane.

18.  Modify the All Caption to read All Calendar Time.

The Advanced tab of the Properties pane for the Calendar.Time dimension appears as shown below:



Illustration 19: Calendar.Time Dimension, Properties Pane, Advanced Tab


19.  Complete steps 34 through 43 above, for each of the other two dimensions, Customer Id and Product Id, to rename the dimensions (and their associated single underlying levels) to Customer and Product, respectively, on the Basic tabs of the associated Properties panes.

20.  Modify the All Caption fields on the Advanced tab in the Properties panes for Customer and Product to read All Customers and All Products, respectively.

The cube tree should appear as shown below:



Illustration 20: Cube Tree, Reflecting Modifications of Customer and Product Dimension (and Associated Levels) Names


Keep in mind that the dimensions we have defined are private dimensions. Because private dimensions (and the associated dimension levels) are defined for the individual cube, they are created/modified, as we have seen above, using the cube editor. We in essence must be "inside" the cube with the editor to readily see the private dimensions exposed in the cube tree.

We will process the cube at this stage.


Page 7: Processing the Cube


See All Articles by Columnist William E. Pearson, III



Processing the Cube

Let's process our new cube, and then take a look at the results via the Preview pane on the Data tab.

1.      Select Tools from the main menu.

2.      Click Process Cube, as shown below.



Illustration 21: Select Tools -> Process Cube


NOTE: We might have alternatively chosen the Process Cube button depicted in Illustration 22. We might also have right-clicked the cube, and selected Process Cube from the context menu.



Illustration 22: Process Cube Button


The Save the Cube dialog appears as shown below.



Illustration 23: The Save the Cube Dialog


The Cube: New Name dialog appears.

3.      Name the cube ANSYS05, as shown in Illustration 24.



Illustration 24: The Cube: New Name Dialog


4.      Click OK.

A message box (shown below) appears, warning that the cube has no aggregates designed, and asking if we want to design aggregations prior to processing the cube.



Illustration 25: Warning Message Box: No Aggregates Designed


5.      Click No.

The Process a Cube dialog appears, as shown in Illustration 26. The Full Process option is selected by default, as this is the first time the cube has been proposed for processing.



Illustration 26: The Process a Cube Dialog


6.      Click OK.

Cube processing begins, as evidenced in the status display of the Process dialog shown below:



Illustration 27: The Process Dialog, Displaying Status


The Process dialog displays status events within the cube processing evolution and then displays a "Processing Completed Successfully" statement in green at the bottom of the dialog when processing is finished.

7.      Click Close.

8.      Click the Data tab.

We see the actual data presented in the Preview pane, as shown below.



Illustration 28: Partial View, Actual Data in the Preview Pane (Compressed)


Note that our dimensions, Calendar.Time, Customer and Product, are represented by numbers (the ID fields in the fact table) within the various views we can access. This is expected, as we have used only the ID keys to create the dimensions so as to limit the initial cube build to a central fact table.

We must process the cube before browsing actual data anytime we build a new cube and design its storage options and aggregations, or anytime we change a cube's structure (measures, dimensions, and so on) and save the changes to the cube. We must also process a cube anytime we change the structure of a shared dimension (using the Dimension Wizard) used in the cube. If data in the data source (i.e. data warehouse) supporting the cube has been added or changed, processing is appropriate to furnish updated, accurate results when browsing the cube.


Page 8: Expanding the Basic Cube with the The Cube Editor


See All Articles by Columnist William E. Pearson, III



Expanding the Basic Cube with The Cube Editor

We will now expand our cube to include data from the respective dimension tables, among other enhancements. First, we will add the dimension tables we require to the existing core cube, and derive more useful dimension data thereby, using the following steps.

1.      Click the Schema tab to return to the table view.

Only the sales_fact_1997 table appears, at this stage of our development effort, as shown below:



Illustration 29: Schema Pane, Initial Cube Design (Compressed)


2.      Click Insert from the Main Menu.

3.      Click Tables from the menu that appears, as shown.



Illustration 30: Insert -> Tables from the Main Menu


The Select Table dialog appears.

4.      Select the time_by_day table by clicking and highlighting it.

The Detail section of the dialog becomes populated with the columns of the time_by_day table. The Select Table dialog now appears as shown in Illustration 31.



Illustration 31: The Select Table Dialog, time_by_day Table Selected


5.      Click the Add button.

The time_by_day table appears on the schema tab, while the Select Table dialog remains open.

9.      Add the following additional tables after the manner of the time_by_day table insertion above:

  • customer
  • product

10.  Click Close on the Select Table dialog.

The dialog disappears, leaving the schema view as depicted below (after arrangement):



Illustration 32: The Arranged Schema View with Added Dimension Tables


Note that joins between the fact table and the dimension tables are already in place, using the id keys in each. This will likely not be the case in a real world design effort, and the appropriate joins (perhaps not as straightforward as those found in our model) would need to be created.

As part of making our cube more user friendly for information consumers, we wish to substitute the dimension ID keys that it currently presents with more intuitive dimension fields. Let's begin with the time_by_day table.

In a case where we may not be familiar with the nature of the data or the characteristics of the fields in a given table, such as our time table, we can browse the data to get a look at its makeup.

11.  Click the upper portion (where the name appears) of the time_by_day table to select the table.

12.  Right-click and select Browse Data from the context menu, as shown in Illustration 33.



Illustration 33: Select Browse Data to View a Sample of a Table's Data


The Browse Data Viewer appears, as shown in Illustration 34 below.



Illustration 34: Partial View of the Browse Data Viewer


13.  Close the viewer after reviewing the data columns and the formats of their contents.

14.   In the Cube Tree, expand the existing Calendar.Time dimension by clicking the "+" sign to its immediate left.

Beginning with the Calendar.Time dimension and proceeding to the Customer and Product dimensions, we will eliminate the original dimension key as the member and substitute a more user friendly field from the related dimension table.

15.  Select the Calendar Time dimension level member (the single level under the Calendar.Time dimension), as shown below:



Illustration 35: Calendar.Time Dimension as Currently Constructed


16.  Right-click the Calendar Time dimension level member.

17.  Select Delete from the context menu.

18.  Click Yes at the Confirm Level Delete dialog to delete the member.

19.  Perform steps 15 through 18 for the Customer and Product dimension level members (the only level members within each), respectively.


Page 9: The Cube Editor (Continued)


See All Articles by Columnist William E. Pearson, III



We should now have each dimension in place, with no levels under each. We will add dimension levels from the dimension tables in the next steps.

20.  Drag the following fields from the time_by_day table over to drop onto the Calendar.Time dimension folder, in the order shown:

  • the_year
  • quarter
  • the_month
  • the_date

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



Illustration 36: Calendar.Time Dimension with New Member Additions


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

  • Calendar Year
  • Calendar Quarter
  • Calendar Month
  • Date

22.  Drag the account_num field from the customer table over to drop onto the Customer dimension folder.

23.  Right-click on the newly added account_num member and rename it as Account No.

24.  Drag the sku field from the product table over to drop onto the Product dimension folder.

25.  Right-click on the newly added sku member and rename it as SKU.

The modified dimension section of the cube tree appears as shown below:



Illustration 37: The Newly Modified Dimensions in the ANSYS05 Cube Tree


26.  Click the Calendar.Time dimension to highlight/select it.

27.  Click the Advanced tab of the Properties pane.

28.  Change the Type field for the Calendar.Time dimension to Time, as shown below.



Illustration 38: The Calendar.Time Dimension Advanced Properties


29.  Click the Calendar Year level to highlight/select it.

30.  Click the Advanced tab of the Properties pane.

31.  Change the Level Type field for the Calendar Year level to Years, as shown in the following illustration.



Illustration 39: The Calendar.Time Dimension, Calendar Year Level Advanced Properties


32.  Click the Calendar Quarter level to highlight/select it.

33.  Click the Advanced tab of the Properties pane.

34.  Change the Level Type field for the Calendar Quarter level to Quarters, as shown in the following illustration.



Illustration 40: The Calendar.Time Dimension, Calendar Quarter Level Advanced Properties


35.  Click the Calendar Month level to highlight/select it.

36.  Click the Advanced tab of the Properties pane.

37.  Change the Level Type field for the Calendar Month level to Months, as shown in the following illustration.



Illustration 41: The Calendar.Time Dimension, Calendar Month Level Advanced Properties


38.  Click the Date level to highlight/select it.

39.  Click the Advanced tab of the Properties pane.

40.  Change the Level Type field for the Date level to Days, as shown in the following illustration.



Illustration 42: The Calendar.Time Dimension Date Level Advanced Properties


Page 10: The Cube Editor (Continued)


See All Articles by Columnist William E. Pearson, III



I typically style the lowest level in the Date hierarchy as Date; calendar, fiscal, and perhaps other date hierarchies would typically share Date as a common lowest level.

Now let's make a few enhancements to the Customer and Product dimensions. First, let's provide an identifier for our customers that makes them easily isolable. We might learn from interviews with information consumers, for example, that a common desire is to be able to seek information on customers by name, last name first, with account number also readily viewable in the ID. This will require a concatenation of the name information, together with the Account No information (our current dimension level). We will take the following steps to give the information consumers what they want.

41.  Click to highlight the Customer dimension in the cube tree.

42.  Select the Advanced tab in the Properties pane.

43.  Click the Member Names Unique property field.

44.  Ensure that False is selected as the property setpoint from the dropdown selector.

45.  Ensure that the Allow Duplicate Names property is set to True.

The Advanced tab of the Properties pane for the Customer dimension appears as shown in Illustration 43:



Illustration 43: Properties Pane, Advanced Tab, Customer Dimension


46.  Click to highlight the Account No level of the Customer dimension in the cube tree.

47.  Select the Advanced tab in the Properties pane.

48.  Click the Member Names Unique property field.

49.  Ensure that False is selected as the property setpoint from the dropdown selector.

The Advanced tab of the Properties pane for the Account No level appears as shown in Illustration 44:



Illustration 44: Properties Pane, Advanced Tab, Account No Level


50.  Select the Basic tab in the Properties pane for the Account No level.

51.  Click the Member Name Column property.

52.  Type the following into the field:

          "customer"."lname"+', '+"customer"."fname"+' -- '+CStr("customer"."account_num")

The above expression uses the VBA CStr function to convert the account_num field to a string that we are able to concatenate with the last and first names. While many other variations are possible for presentation, 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 can easily check the effects of our work by using the Browse feature at the Customer dimension.

53.  Right-click the Customer dimension in the cube tree.

54.  Select Browse from the context menu, as shown below.



Illustration 45: Select Browse from the Context Menu


The Dimension Browser window appears.

55.  Expand the All Customers level to reveal the results of our Member Name Column expression, as partially displayed in Illustration 46.



Illustration 46: Partial View of Expanded All Customer Level


56.   Click the Close button on the Dimension Browser when finished.

57.   Drag the date_accnt_opened column from the customer table onto the Member Properties folder underneath the Account No level.

The Account No level of the Customer dimension appears as shown in Illustration 47:



Illustration 47: Account No Level of the Customer Dimension


The addition of the date to the level member properties will allow us to track the longevity of customer accounts for reporting and browsing purposes.


Page 11: The Cube Editor (Continued)


See All Articles by Columnist William E. Pearson, III



Having made the illustrative enhancements to the Customer dimension, let's turn now to the Product dimension.

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:

          CStr("product"."SKU")+ ' - ' + "product"."product_name"

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



Let's check the effectiveness of our work by using the Browse feature at the Product dimension.

68.  Right-click the Product dimension in the cube tree.

69.   Select Browse from the context menu, as we did with the Customer dimension earlier.

The Dimension Browser window appears.

70.  Expand All Products, along with a sample of the two levels underneath (I chose All Products -> Baking Goods -> Cooking Oil for illustrative purposes below), to reveal the results of our enhanced Member Name Column expression, as partially displayed in Illustration 50.



Illustration 50: Partial View of the Expanded All Product Level


71.   Click the Close button on the Dimension Browser when finished.

72.   Drag the recyclable_package column from the product table onto the Member Properties folder underneath the SKU level.

The SKU level of the Product dimension appears as shown in Illustration 51:



Illustration 51: SKU Level of the Product Dimension


The addition of the information, which concerns the recyclable nature of the product packaging, to the level member properties will allow us to access the information for reporting and browsing purposes. Classing this data as a Member Property makes it available, albeit not as easily used as a member in its own right. Again, the requirements of the business environment will usually dictate the proper treatment of data fields.

73.  Right-click the Product dimension in the cube tree.

74.  Select Browse from the context menu, once again.

The Dimension Browser window appears.

75.  Expand the All Product, along with a sample of the two levels underneath (again, I chose All Product -> Baking Goods -> Cooking Oil), to see the Member Property displayed to the right of the Member Name Column (in the Member Properties box), as illustrated below.



Illustration 52: Example Member Property Displayed from the Dimension Browser


76.  Click the Close button on the Dimension Browser when finished.


Page 13: Using the Cube Editor to Work with Measures


See All Articles by Columnist William E. Pearson, III



Using the Cube Editor to Work with Measures

Having set up our dimensions, we will now conclude our cube design by focusing on the measures we have designated. While measures can come directly from the fact table (a minimum of one measure is required in an Analysis Services cube), as have the measures we have defined up to this point in our design exercises, we have additional options for measure addition, including the use of expressions. Let's examine several characteristics of measures as we complete our cube development in the following steps.

Let's say, as an illustrative requirement, that information consumers have asked us to make available a measure for the gross profit generated by the various products of the organization. We ascertain that what they want is the difference in Store Cost and Store Sales, two measures that we already include in our model. Further, the consumers want to generate a measure that presents gross profit as a percentage of Store Sales.

We will create a derived measure (a measure that does not occur naturally in the database) and a calculated measure (a calculated member in the Measures dimension) to add the requested information to our cube. To do so, we will take the following steps within the Cube Editor.

1.      Right-click the Measures folder for the ANSYS05 cube.

2.      Click New Measure from the context menu, as shown below.



Illustration 53: Click New Measure to Begin Creation of a New Measure


The Insert Measure dialog appears.

3.      Select Store Cost, as shown in Illustration 54.



Illustration 54: Select the Fact Table Column upon which to Base the New Measure


4.      Click OK.

We see a new measure, Store Cost 1, appear in the cube tree.

5.      With Store Cost 1 selected, click the Basic tab in the Properties pane beneath.

6.      Rename Store Cost 1 by modifying the Name field to read Gross Profit.

7.      Type the following into the Source Column field.

          "sales_fact_1997"."store_sales" - "sales_fact_1997"."store_cost"

8.      Click the Advanced tab in the Properties pane for the Gross Profit measure.

9.      Click the Display Format property.

10.  Click the dropdown selector in the Display Format property field and select Currency ( if not already selected), as shown below.



Illustration 55: Display Format Property Setpoint - Currency


11.  Right-click the Calculated Members folder for the ANSYS05 cube.

12.  Click New Calculated Member from the context menu, as shown below.



Illustration 56: Click New Calculated Member to Begin Creation of a Calculated Measure


The Calculated Member Builder appears.

13.  Type the following expression into the Value Expression box:

          [Measures].[Gross Profit]/[Measures].[Store Sales]

The components of the expression can also be selected by double clicking the objects in the Data tree below the Value Expression box.

14.  Type GP Percent Sales into the Member Name box.

15.  Ensure that the Parent Dimension is indicated as Measures.

The Calculated Member Builder appears as shown in Illustration 57.



Illustration 57: The Completed Calculated Member Builder


16.  Click OK.

We see a new calculated member, GP Percent Sales, appear in the Calculated Members folder in the cube tree, as shown below:



Illustration 58: The New Calculated Member Appears


17.  With GP Percent Sales selected, click the Advanced tab in the Properties pane beneath.

18.  Click the Format String property.

19.  Click the dropdown selector in the Format String property field and select Percent, as shown below.



Illustration 59: Format String Property Setpoint - Percent


We now have two derived measures in addition to the original measures based directly on fact table fields. At any time we can click the Data tab to see the layout of our work via the Preview pane, but we will need to keep in mind that, until we process the cube, only sample values appear (again, note the warning at the bottom of the Preview pane). This provides adequate testing for formats but obviously not for the results of most expressions, etc.

Another consideration before processing the cube might be the order in which the measures appear in the cube tree: the first measure listed in the tree is the default measure for the cube.


Page 14: Process the Cube from the Cube Editor


See All Articles by Columnist William E. Pearson, III



Process the Cube from the Cube Editor

We will now process the cube, having modeled our work so far with sample data, as noted in the warnings that appeared at the bottom of the Preview pane, and as we discussed above.

1.      Right-click ANYSYS05 in the cube tree of the Cube Editor.

2.      Click Process Cube on the context menu, as shown in Illustration 60.



Illustration 60: Select Process Cube...


We receive a warning to save the cube, as shown below, unless we have saved it more recently than the last modification(s).



Illustration 61: Select Yes to Save the Cube


3.      Click Yes to perform the save.

We next receive a dialog regarding aggregation matters, asking if we wish to start the Storage Design Wizard, as shown below.

 



Illustration 62: "Just Say No" to Storage Design Wizardry


4.      Click No.

The Process a Cube: Select the Processing Method dialog appears, as depicted in Illustration 63.



Illustration 63: The Process a Cube: Select the Processing Method Dialog


5.      Click the Full Process radio button to select it, if necessary.

The Process status dialog appears, displaying status until completion, with a final display similar to that partially shown in Illustration 64.



Illustration 64: Partial View of the Process Status Dialog upon Successful Completion


6.      Click Close to close the Process status dialog

The Process status dialog disappears.

7.      Click the Data tab to refresh the Preview pane.

8.      Expand All Calendar.Time, then Calendar Year 1997 to obtain a view similar to that shown below.



Illustration 65: The Preview Pane after Processing ANSYS05


9.      Explore with drilldown and browse as desired, then close Analysis Services.

For more information on browsing the cube, see Article 1 of this series, Creating Our First Cube.

We have added the requested measures to our cube, using a derived measure and a calculated measure, so as to gain exposure to each. Our cube stands ready for browsing and reporting with the dimensions and measures in place as required.


Page 15: Next in Our Series...


See All Articles by Columnist William E. Pearson, III



Next in Our Series ...

In this article, Working with the Cube Editor, we reviewed and integrated many of the components that we have constructed and the concepts that we have explored individually in the last four lessons. We undertook a complete cube build, demonstrating the assembly of a cube more sophisticated than the cube we generated in our first lesson with the Cube Wizard. We also introduced new capabilities and concepts as we constructed our new cube "from scratch."

In this article, we also discussed the use of the Cube Editor, as opposed to the Cube Wizard, as a means of cube construction, and then we created a basic "starter" cube directly from the fact table, to serve as a foundation for a more elaborate cube. We expanded the dimensions of the cube after adding the associated dimension tables. We worked with the dimensions, defining the appearance of the Member Name Column to meet illustrative business requirements of information consumers; in addition, we reviewed sample uses and purposes of member properties.

We made use of the Dimension Browser for design and observation tasks at various points. We added a derived dimension, and we revisited calculated members, adding a calculated measure to our cube. Throughout the lesson, we worked with various properties of measures and dimensions to control the behavior and characteristics of our cube.

In our next lesson, Exploring Virtual Cubes, we will introduce the concept of virtual cubes. We will discuss the options that virtual cubes provide in allowing us to combine measures from more than one existing cube, as well as in the removal of dimensions and/or measures from our cubes for various reasons. We will practice the use of the Virtual Cube Wizard to create a virtual cube that is a subset of an existing cube, as a means of "customizing" the information presented by a cube to a given information consumer or consumer group. We will expose a process by which we can combine measures from multiple cubes, as well as a means for creating calculated members in our virtual cube. Finally, we will practice the import of calculated members from a sample source cube to our virtual cube.


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