Member Types/ Hierarchy Dialog Box

 

Defining Members is the next step in database design. PowerOLAP® supports two Dimension member types: Detail members and Aggregate members, as explained below.

Detail Members

Detail members store values at Member intersections. These values are stored as Fact Data within the Cube. An example of Detail members might be individual country names (JapanChinaUSA) in a Dimension called Regions. Detail members are an index of axis points, for both numeric and string values (numeric values can be integers, decimal fractions or a number in scientific notation; string values may be a combination of letters and numbers not used in mathematical functions, i.e., an address or social security number), which can be read and written.

 

Aggregate Members

Aggregate members are “parent” members when they aggregate a group of “child” members. For example, in a Dimension named Months1st Quarter is a natural Aggregate member summing January + February + March, each of which is a Detail member. Unlike Fact Data stored at Detail member intersection points, Fact Data values that occur at the intersection of one or more Aggregate members are not stored within the database file but rather are derived as roll-up calculations at runtime in RAM. (Consequently, the more RAM your PC has, the faster your models will recalculate and update.)

[Note that since “child” Members can themselves be Aggregates, a Child member should not be equated with a Detail member. See the following diagram for an illustration of how a Child member can also be an Aggregate, i.e., North America and South America.]

When defining Aggregate members and Detail members in a parent-child relationship, we are constructing a Hierarchy of Members, the next essential step in constructing a database.

The following figure illustrates the Detail members ArgentinaBrazilCanadaMexicoUSA and Venezuela as children of their respective Aggregate members South America and North America—which in turn are Children of their Aggregate parent member, World. The sum icon ( Σ )  indicates Aggregate members.

Create New Member

 

Create New Member

To add Members to a Dimension—whether the first Member of a Dimension or the “latest” Member (e.g., the newest hire for a Salesperson dimension)—you will need to access the Dimension Hierarchy dialog box.

To create a new Dimension Member:

1.   In the PowerOLAP ribbon, select Model tab then Dimension command.

2.   Select a Dimension.
We will build our new Salesperson dimension in this exercise.

3.   Select the Edit button (or double-click the Dimension name).
The Dimension Hierarchy dialog box appears.

The Dimension is indicated within single quotations marks: in the following example, the new ‘Salesperson’ Hierarchy dialog box is shown:

 Notes

A great deal more than creating Hierarchies occurs in the Hierarchy dialog box. In fact, everything having to do with Members and their attributes is determined in this dialog box. After explaining how to manipulate the various features of the dialog box for the purpose of defining Members within Dimensions, there is a SUMMARY of the toolbar icons for the Hierarchy dialog box at the end of this section.

The left side of the Dimension Hierarchy dialog box is the Members list box. The right side contains the Hierarchy Definition box (we will soon demonstrate the use of these boxes for creating a Hierarchy). A splitter (center post) allows resizing of these areas.  If you make changes in the dialog box, you will be prompted to save when changing Dimensions, or attempting to close the dialog box.

It is not necessary to manually specify Member types—i.e., whether Aggregate or Detail—when you add Members, as this information is inferred from their placement in the Hierarchy definition, which will be shown [see Create Dimension Hierarchy].

4.   Right-click within the Members list box of the dialog and select New (Alternatively, click the Create New Member button).
A new “untitled” edit box is displayed.

5.   Type in <new Member name>. Our Members will be names of Salespeople in the Avon Trading Company.

6.   Press Enter.

The following detail figure shows the Members list box containing several Salespeople.

 Notes

When a Member is first added, it is displayed in the Members list box with a Detail level icon beside it (number sign: #). If it is added as a parent of other Members within the Hierarchy Definition box, its icon changes to Aggregate (Sigma: Σ).

Rename/Delete Member

When you highlight a Member in the Member list box of the dialog box, you can Rename or Delete it by a right mouse click, which displays the options as in the following figure:

Finding/Sorting Members

When long lists of Members exist in the Member list box, it may become cumbersome to scroll through the list in order to locate a specific Member. PowerOLAP® solves this problem by providing special Functions for homing in on Member names. By selecting Members through the use of Subsets or Filters, you can more easily construct hierarchies to view within a PowerOLAP Slice or a linked Excel worksheet.

 MEMBER SUBSETS

The drop-down list box at the top left in the Dimension Hierarchy dialog box allows you to show various subsets of Members. Subsets define ranges of Members as follows:

  • All: all Members are displayed.
  • User Defined Subset: A selection of Members you have defined and saved is displayed (see the section Working with Slices concerning Subsets).

FILTERING MEMBERS

The Filter text box allows you to filter the selected list of Members. The filter grammar supports case-insensitive sub-string matching. For example, you might enter ‘J’ in the Months dimension to display Members beginning with the letter ‘J’ only. When you tab or hit return, the filter is applied to the full Member subset. Filter applications are not cumulative.

 Important

Changes made in the Hierarchy dialog box are only saved to the database when you close the Dimension Hierarchy and Dimension dialog boxes and then save the database.