Introduction to SQL Server 2000 Analysis Services: Parent-Child Dimensions

Monday Oct 7th 2002 by William Pearson
Share:

The fourth article in the advanced SQL series 'Introduction to MS SQL Server 2000 Analysis Services' examines another special type of dimension, the Parent-Child dimension, and explores the attributes that make it different from a regular dimension. The article also discusses the considerations that surround the Parent-Child dimension, such as the recursive nature of their data sources, and various actions that must be handled differently in their creation and maintenance.


About the Series...

This is the fourth 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.

A special requirement for this lesson is Microsoft Access 2000, which we will be using to briefly examine source database characteristics of the FoodMart 2000 sample database.


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.

Our last article 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 this article, we will examine another special type of dimension, the Parent-Child dimension, and explore the attributes that make it different from a regular dimension. We will discuss the considerations that surround the Parent-Child dimension, such as the recursive nature of their data sources, and various actions that must be handled differently in their creation and maintenance.

In this article, we will:

  • Discuss unbalanced hierarchies, and scenarios where we need a parent-child dimension
  • Create a parent-child dimension using the Dimension Wizard
  • Work with levels in a parent-child dimension
  • Work with parent-child dimension properties
  • Enable values at the parent level of a parent-child dimension


Page 2: Introducing Parent-Child Dimensions


See All Articles by Columnist William E. Pearson, III



Introducing Parent-Child Dimensions

As many of us have discovered from experience, Analysis Services can perform well with other-than-"white-bread" data warehouses as a source for cube building. The standard star schema, consisting of a discrete dimension table for every dimension present, is often an inadequate design for the data repositories of many of the complex organizations and sophisticated business environments that we find today. Variations upon even the snowflake theme often find their ways into the warehouse for various reasons. One of the sets of conditions behind these variations is that of unbalanced hierarchies.

As anyone who has been in the data warehouse/business intelligence field for very long knows, the classic unbalanced hierarchy is represented by the employee/supervisor scenario, where the lowest level members of the dimension under consideration are the employees. Supervisors of employees also exist within the dimension, as employees themselves, and as levels of the dimension, to which groups of lower level employees report. So is born the basis for many recursive reporting "quizzes", and for scenarios used in situations that range from client interviews of prospective consultants for reporting engagements to the well-known examples of ragged hierarchies/recursive relationships perpetually used in books or demonstrations.

In short, we are describing a dimension that differs from the standard snowflake arrangement, where the "child" member exists in an original dimension table, and the "parent", a higher-level member of the same dimension, resides in a separate table. The employee parent member and child member live in the same table, where both exist at the leaf level, and the parent member refers to a new row in the same table.

Our article will focus on the parent-child dimension, where both parent and child share the same table as we are describing. After preparing the environment, we will create a parent-child dimension using the Dimension wizard, which we will discover handles these special dimensions with ease. We will examine the Employee table contained within the FoodMart 2000 sample source database (FoodMart 2000.mdb), touching upon the elements to which we have referred, and then we will practice building a parent-child dimension using the Dimension Wizard.


A Look at the Tables...

To better understand the characteristics that underlie our the need for a parent-child dimension, let's examine the underlying basis for our exercises in this lesson, the Employees table in the FoodMart 2000 database. FoodMart 2000.mdb is installed as a sample when we perform the Typical installation of MSSQL Server 2000/Analysis Services.

1. Start Access 2000.
2. 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 1 below.


Illustration 1: Selecting the FoodMart 2000 Database


3. Highlight FoodMart2000.mdb, and click Open.

The FoodMart 2000 database opens and appears as shown below:



Illustration 2: The FoodMart 2000 Database


4. Right-click the Employee table.
5. Click Design View from the context menu, as shown in Illustration 3 below.



Illustration 3: Selecting Design View for the Employee Table


The Design View appears, as shown below.



Illustration 4: Employee Table-Design View


Every employee in the Employee table has a unique employee_id. The employee_id key provides the basis for the join to the corresponding key in the Sales fact tables that appear in our sample database when viewing it from the star schema perspective. We can see in the table that each employee also has a supervisor_id that identifies the corresponding employee (in the same table) who acts as the supervisor for the employee on which the supervisor_id appears.

The standard snowflake arrangement would be for the supervisor_id to provide the basis for a join that would be placed between this table and another table that contained the supervisor level members. The fact that supervisors and employees are all employees (all exist at the grain level in the Employee table), and that the resulting joins are between the supervisor_id of each employee and the employee_id of the supervisor's employee record, means that an unbalanced hierarchy will be present; that is, various hierarchical branches will contain different numbers of levels.

The Employee table is an excellent example of a parent-child table, where an unbalanced hierarchy exists for the dimension it houses. In many Business Intelligence/reporting engagements where such a table has been involved, I have created an alias of the table for each level of the hierarchy above the grain level, and assigned the desired fields from the aliases tables to the respective levels of the hierarchy upon which I wished to report. In the parent-child dimension we will create from the Employee table, each join will similarly be the basis of a new level within our dimension.

Let's get started by setting up our basic working environment, then we will call upon the Dimension Wizard to assist us in creating the parent-child dimension we have discussed.

6. Close the Employee table-design view.
7. Close the FoodMart 2000 database, and then close MS Access.


Page 3: Preparation for Creating the Parent-Child Dimension


See All Articles by Columnist William E. Pearson, III



Preparation for Creating the Parent-Child Dimension

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 have already set the database up from the previous lesson(s) in our series, we will perform setup again quickly for the benefit of anyone who has not. We will give the database the same name as we used in the previous lesson; 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, the OLAP database we create will organize cubes, roles, data sources, shared dimensions, and other objects that we will cover in this and later lessons, in addition to the objects that we are currently using. We will call our OLAP database MyCube2 (be careful here -- you cannot rename a database in the Analysis Services console once it has been 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. Start Analysis Manager, then right-click on the Analysis Server name, to see the context menu shown below:



Illustration 5: Right-Click on the Analysis Server


2. Click New Database.

The Database dialog box appears.

3. 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 6: The Database Dialog Box


4. Click OK.

MyCube2 appears just below the existing FoodMart 2000 Database.

5. Expand MyCube2's database/cube icon, (clicking on the plus (+) sign to the left of the icon will do the trick).

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



Illustration 7: The New Database with Directory Structure


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

6. 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 8 below. Beginning with the Provider tab (where the dialog opens by default):

7. 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 8: The Data Link Properties Dialog-Provider Tab


8. Click Next.

The focus moves to the Connection tab.

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



Illustration 9: Selecting the FoodMart 2000 Database


10. Click Open to return to the Connection tab, as we see below:



Illustration 10: The Data Link Properties Dialog-Connection Tab


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



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


13. 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.

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



Illustration 12: Initial View of Our Newly Created Data Source


Page 4: Preparation for Creating P-C Dimension Continued


See All Articles by Columnist William E. Pearson, III



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 available, so we will have to be a bit creative here; a right-mouse click on our new data source allows a Copy action, which will act as a workaround for renaming the object in question.

15. Right-click the new data source.
16. Click Copy.
17. Highlight the Data Sources folder.
18. 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 13).



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


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

The Duplicate Name dialog thus again acts as our agent of change, and, once we click OK, adds the newly named data source under the data sources folder.

20. Click OK to close the Duplicate Name dialog.

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

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

Our tree should now resemble that shown in Illustration 14.



Illustration 14: MyFoodMart2 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 and manipulation of the parent-child dimension. We will, however, need a cube structure in place to house the dimension components that we intend to build. We will now create the cube in which our dimension will reside, and then move the focus solely to the handling of dimension structures for the remainder of the lesson.

We will use the Dimension Wizard, which we will access from the Cube Wizard, in creating a cube shell structure for the reasons we have mentioned, to create our first parent-child dimension hierarchy, HumanResource.

I like to avoid the "Employee" title, because I have found at the vast majority of my clients that human resources of all types, including many varying types of non-employees (such as temporary workers, consultants, and so forth) have become common. If we do not characterize a dimension as "Employee", that leaves us the option of using the term "Employee" in an unambiguous grouping later.

Once we have created the HumanResource parent-child dimension, we will return to dimension manipulation via the Dimension Editor to work with levels in our new parent-child dimension, as well as to perform other tasks related to a dimension of this type.

We now have an OLAP database in place, linked to a valid data source (the FoodMart 2000 database). Our preparation for the lesson (and for the creation of any dimension) is complete. Next, we will initialize the Cube Wizard, and set up our basic table structure. Dimensions are obviously of little immediate use if we do not have data working within them. We will select a fact table along with dimension table sources to allow us to get a feel for how the parent-child dimension, our true concern in this lesson, interacts with the data to construct OLAP cubes, and to enable OLAP reporting.


Page 5: Creating a Cube


See All Articles by Columnist William E. Pearson, III



Creating a Cube

The Cube Wizard 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:

23. Right-click the new Cubes folder under the MyCube2 database we created above.
24. Select New Cube from the initial shortcut menu.
25. Click Wizard, as shown in the illustration below.



Illustration 15: Initializing the Cube Wizard


The Cube Wizard springs to life, and we see the Welcome dialog appear, as depicted in Illustration 16 below.



Illustration 16: The Cube Wizard Welcome Dialog


26. Click Next.

The list of tables available in the MyFoodMart2 Data Source appears.


27. Select salary from the Data Sources and Tables pane on the left.

The Details pane on the right is populated with the columns of the salary table, as shown below:



Illustration 17: The Details of the Salary Table are Displayed


28. Click Next.
29. Add the following measures, by double-clicking each, in the following order:
  • salary_paid
  • overtime_paid
  • overtime_hours

The selected measures fields move to the Details pane as shown below:



Illustration 18: The Details Pane Displays the Column Selection


30. Click Next.


Page 6: Building and Managing a Parent-Child Dimension


See All Articles by Columnist William E. Pearson, III



Building and Managing a Parent-Child Dimension

We now enter the dimension construction phase of the guided cube build, where we will begin our exploration of the parent-child dimension.


Creating the PARENT-CHILD DIMENSION with the Dimension Wizard

The Cube Wizard now prompts us to select dimensions for our cube. The dimensions are, as yet, undefined, and at this stage, we will launch the Dimension Wizard to begin designating the dimensions.

31. Click the New Dimension button.

The Dimension Wizard - Welcome dialog appears, as shown in Illustration 19 below:



Illustration 19: The Dimension Wizard Appears


32. Click Next.

The Dimension Wizard - Choose How You Want to Create the Dimension dialog appears.

33. Select Parent-Child: Two Related Columns in a Single Dimension Table by clicking the respective radio button.

We are given a description of the parent-child dimension choice in the lower section of the dialog, which is depicted in Illustration 20 below.



Illustration 20: The Dimension Wizard - Choose How You Want to Create the Dimension Dialog


34. Click Next.

The Dimension Wizard - Select the Dimension Table dialog appears.

35. Select the employee table.

The columns from the employee table appear in the right side Details area of the dialog, as shown below.



Illustration 21: The Dimension Wizard - Select the Dimension Table Dialog


36. Click Next.

We arrive at the Dimension Wizard - Select the Columns that Define the Parent-Child Hierarchy dialog.

37. Select the columns shown in Table 1 below for the respective dropdown lists within the dialog:


Parent-Child Hierarchy

Dialog Setpoints

 

 

 

 

 

Dropdown List

 

 

 

Selection

 

 

 

 

 

1. Member Key

 

 

 

employee_id

 

 

 

 

 

2. Parent Key

 

 

 

supervisor_id

 

 

 

 

 

3. Member Name

 

 

 

full_name

 

 

 

 

 

Table 1: Columns that Define the Parent-Child Hierarchy Setpoints


The completed dialog appears as shown in Illustration 22 below.



Illustration 22: The Dimension Wizard - Columns that Define the Parent-Child Hierarchy


38. Click Next.

The Dimension Wizard - Select Advanced Options dialog appears, as shown below.



Illustration 23: The Dimension Wizard - Select Advanced Options Dialog


39. Click Next to skip this dialog and to continue to its successor.


Page 7: Building a P-C Dimension Continued


See All Articles by Columnist William E. Pearson, III



The Dimension Wizard - Finish the Dimension Wizard dialog appears.

40. Name the dimension HumanResource. (See my comments above about why I like to use this versus "Employee" as a dimension name.)
41. Uncheck the Share This Dimension with Other Cubes checkbox (the default is checked).
42. Click on the "+" signs to the left of some of the dimension members displayed in the Preview window to get a feel for the structure of the dimension.

The Dimension Wizard - Finish the Dimension Wizard dialog appears, as shown below (with a portion of the Preview window displayed).



Illustration 24: The Dimension Wizard - Finish the Dimension Wizard Dialog


43. Click Finish.

The Dimension Wizard - Finish the Dimension Wizard dialog disappears. We are returned to the Cube Wizard, where we briefly left it at the Select the Dimensions for your Cube dialog. We see the newly added HumanResource member, as shown in Illustration 25 below.



Illustration 25: The Cube Wizard - Select the Dimensions for Your Cube Dialog


The hierarchy shows sibling members, some with children and some without, as one example of the unbalanced nature of the parent-child dimension. The provision in the Dimension Wizard for allowing unbalanced hierarchies offers us needed flexibility while giving us an easy means of creating parent-child dimensions.

Let's conclude the cube build we have started for the purposes of exploring the parent-child dimension, with the following steps.

44. Click Next.

The Fact Table Row Count warning dialog appears, as shown below.



Illustration 26: Fact Table Row Count Warning Dialog


45. Click Yes.

The Cube Wizard - Finish the Cube Wizard dialog appears.

46. Name the Cube MyCube2.

The Cube Wizard - Finish the Cube Wizard dialog now appears as shown in Illustration 27.



Illustration 27: The Cube Wizard - Finish the Cube


47. Click Finish.

We return to the Cube Editor. We will process the cube after making a few modifications to the HumanResource dimension in the next sections.

We will take a look at many of the properties and characteristics of the parent-child dimension in our next section, but first we will focus on level definition in the building of the parent-child dimension.


Page 8: Working with Levels in a Parent-Child Dimension


See All Articles by Columnist William E. Pearson, III



Working with Levels in a Parent-Child Dimension

As we have seen, the unbalanced, single-table nature of the underlying source for the parent-child dimension mandates special considerations in its creation. Fortunately, the Dimension Wizard facilitates ease in the design of these special dimensions in a largely prompted process. We are also granted a considerable degree of latitude in the presentation of the levels in our parent-child dimensions.

Let's examine some of the steps we can take to control presentation of the HumanResource dimension that we have created.

1. Click the "+" sign to the immediate left of the HumanResource dimension in the cube tree to expand it.
2. Select Employee ID level below the HumanResource dimension.
3. Click the Advanced tab in the Properties window.
4. Click the Level Naming Template property.
5. Click the ellipsis (...) button.
6. In the Name field, type the word President.

As shown in Illustration 28 below, when we begin typing in the Name field, to the right of the asterisk (*) symbol, we notice that a new line appears directly beneath the line in which we are typing, where the asterisk moves. The field occupied by the asterisk on the line in which we are typing becomes a 1. This is similar to the behavior of an MS Access database when we type in similar fashion to a table therein.

7. Type in the following Names for the corresponding levels in the Level Naming Template property as above:


Level Naming Template

Name Field Setpoints

 

 

 

 

 

Level No.

 

 

 

Name

 

 

 

 

 

1

 

 

 

President (already entered)

 

 

 

 

 

2

 

 

 

Sr VP

 

 

 

 

 

3

 

 

 

VP

 

 

 

 

 

4

 

 

 

Manager

 

 

 

 

 

5

 

 

 

Asst Manager

 

 

 

 

 

6

 

 

 

Supervisor

 

 

 

 

 

7

 

 

 

Operative

 

 

 

 

 

Table 2: Level Naming Template Setpoints for Name Field


After adding the items in Table 2 above, the Level Naming Template input box will resemble that shown below:



Illustration 28: The Completed Level Naming Template Input Box (Partial View)


8. Click OK.
9. Click the HumanResource dimension in the cube tree of the Cube Editor to select it.
10. Click the Advanced Tab in the Properties pane, underneath the cube tree of the Cube Editor.
11. Change the All Level property to No, as shown below:



Illustration 29: Removing the All Level from the Display



12. Click the Data tab to initialize the Preview pane on the right side of the Cube Editor window.
13. Expand the President and all lower levels by double-clicking each level heading.

The preview pane appears similar to the one partially illustrated below:



Illustration 30: The Preview Pane (Partial View) Shows Results of Our Modifications


Since the hierarchy in the organization with which we are working has only one person at the top level (Ms. Nowmer), we have no real need for the "All" level in our displays. We can remove it by simply setting the All Level property to No. Furthermore, our Preview pane headings match our new setpoints, customizing the headings to be more appropriate and less confusing within the context of our business environment.

There is one extended refinement that we would need to make in a "real world" scenario, however. Even though we have renamed the various levels in our example to more accurately reflect the names with which the information consumers might be familiar, we need to build in logic to handle the unbalanced hierarchy for our presentation to be accurate. Our source table, employee, contains a field that indicates the level at which any given employee actually exists in the food chain. Employee 51, Brian Binai, and employee 52, Concepcion Lozada, shown as VP's in our display, actually belong at lower levels (HQ Finance and Accounting). Because the recursive hierarchy we have discussed places these with others reporting to the Senior VP, our basic level handling structure would conclude that they are VP's, simply because they report to a Senior VP. When there are "skipped levels" in between some of the employees and the supervisors to whom they report, special steps need to be taken to allow us to simply display reporting hierarchy, and to enhance it with the level information built into the display.

This scenario would be easily handled within the Skipped Levels property of the Employee ID level, if we had a field in the source data to tell the number of levels to skip. The Position ID number that appears in our employee source data table (we can review the data by clicking the Schema tab, right-clicking the heading of the table, and selecting Browse Data) might be useful in deriving this value, but will not suffice on its own. Hence we discover yet another item to investigate in the source data in our real world environments, prior to assuming we have all we need to create a cube.

As we have taken the customization of the display about as far as we can within the scope of our lesson, let's look at another important concept: enabling values for dimension level members.


Page 9: Enabling Values at a Parent Level


See All Articles by Columnist William E. Pearson, III



Enabling Values at a Parent Level

Standard dimensions provide for values in the fact table "tying" or "belonging" to leaf-level members. Here, again, the parent-child dimension requires different treatment. Values must "roll up" to the hierarchical parent levels of the respective leaf-level members to meet many reporting and analysis requirements. Let's investigate the mechanics behind making this happen.

14. Select the HumanResource dimension within the cube tree again.
15. Click the Advanced tab in the Properties window beneath the tree.
16. Click the Members with Data property.
17. Select Non-Leaf Data Visible from the dropdown value selection list.

The relevant portion of the Advanced tab appears as shown below:



Illustration 31: Members with Data Property: Non-Leaf Data Visible


The property that we have modified is an option that is exclusive to the parent-child dimension. Our selection, Non-Leaf Data Visible, generates a surrogate member for each parent hierarchical level. This allows for the presentation of data in the context of the new members and the parents that they represent.

18. Press Enter.

The Preview pane appears similar to the one partially illustrated below:



Illustration 32: The Preview Pane (Partial View) Shows Results of Our Modifications


Note the appearance of the new members; by default, they are indicated by parentheses. Changing the template for how the member names are presented is straightforward.

19. Select the HumanResource dimension within the cube tree, once again.
20. Click the Advanced tab in the Properties window beneath the tree.
21. Click the Data Member Caption Template property.
22. Remove the contents of the property value field, leaving only the asterisk (*). Type "- Individual" into the field.

The relevant portion of the Advanced tab appears as shown below:



Illustration 33: The Data Member Caption Template Property with Modification



23. Double-click the Sr. VP level heading to collapse the hierarchy somewhat and to free up real estate.
24. Press Enter.

The Preview pane appears similar to the sample illustrated below:



Illustration 34: The Preview Pane with Data Member Caption Template Property Modifications


Page 10: Enabling Values Continued


See All Articles by Columnist William E. Pearson, III



We see that we have attained our objective with the small sample above. Note that Ms. Nowmer's individual information is reflected (Sheri Nowmer-Individual) as well as the total for her area of responsibility (Sheri Nowmer-Total).

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 Data Preview pane.

25. Right-click MyCube2 in the cube tree of the Cube Editor.
26. Click Process Cube on the context menu, as shown in Illustration 35.



Illustration 35: Select Process Cube...


Cube processing can also be kicked off from the Main Menu (Tools) or from the Process Cube button in the toolbar.

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



Illustration 36: Select Yes to Save the Cube


27. Click Yes to approve the save.

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



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


28. Click No.

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



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


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

The Process status dialog appears, displaying status until processing completion, with a final display similar to that in Illustration 39.



Illustration 39: The Process Status Dialog upon Successful Completion


30. Click Close to close the Process status dialog, and to refresh the Data Preview pane.

The Process status dialog disappears, leaving behind a refreshed Preview pane similar to that shown below.



Illustration 40: The Preview Pane after Processing MyCube2


We have thus built in the capability of the parent levels to display the data associated with them, and so provided a "rollup" context.


Page 11: Next in Our Series


See All Articles by Columnist William E. Pearson, III



Next in Our Series...

In this, the fourth article of the Introduction to MSSQL Server 2000 Analysis Services series, 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 several considerations that surround the Parent-Child Dimension, such as the recursive nature of the source data that populates it, as well as various actions that must be handled differently in its creation and maintenance. We discussed setting properties to achieve desired display results, and reviewed cube processing as a part of refreshing our data.

In our next lesson, Working with the Cube Editor, we will review, summarize and integrate the components that we have constructed and the concepts that we have explored individually in the last three lessons. Our objective will be to undertake a complete cube build, pulling together all that we have learned, to assemble a cube similar to but more sophisticated than the cube we generated in our first lesson with the Cube Wizard.


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