Working with Dimensions and Members

Working with Dimensions and Members

About metadata

Metadata defines the foundation of a proCube database—in other words, it describes the characteristics of OLAP objects used for the database architecture. Metadata enables search capabilities, as well as the management and linking of database content, thereby enabling users to assemble “reusable” database objects. The minimum Metadata components required to describe a proCube model are:

  • Dimensions

  • Members

  • Dimension Hierarchies

  • Cubes

Expanded models may include Weighted Members , Alias Groups , Property Groups , and Cube Formulas .

A proCube database model is constructed using the following steps:

constructingprocubedatabasemodeldiagram.png

Figure 1.    Constructing a database model

Constructing dimensions

Constructing Dimensions  is the first step in developing a multidimensional proCube model. Dimensions are lists of related things, like Staff Members or Products. Defining the Dimensions that will meet your organization’s reporting and analysis needs is critical to the creation an optimal database. Some organizations may only need a 3- or 4-dimensional model (for example, Months, Regions, Salespeople and Accounts), while others may need a dozen.

Note that Dimensions do not contain or store data; they are made up of Hierarchies of Members. Putting the Dimension members into Hierarchies defines the axes within the database to store data. The resultant axes define the shape of a Cube. Fact Data is stored at Dimension Member intersection points on these axes within the Cube.

A simple example of a Dimension may be Months, which would list months of the year as separate Members along the axis of the Months dimension. Members of the Months dimension might be January, February, March, etc. Additional examples of Dimensions and their Members appear in the table below.

Table 1.       Dimensions and Their Members

Dimension

Accounts

Region

Period

Product

Salespeople

Member 1

Sales

USA

January

Hi-Fi

James Taylor

Member 2

Cost of Sales

UK

February

T.V.

Jill Fraser

Member 3

Gross Profit

Australia

March

Microwave

Dawn Gilbert

Member 4

Gross Margin

Germany

April

Stove

Ron Gifford

Member 5

Expenses

Japan

May

Kettle

Hugh Mather

There is no absolute limit to the number of Dimensions, Members or Cubes that you can create within a proCube database. You may, however, be limited by the available memory in your computer.

Given this background on Metadata, we will turn our attention to working with Dimensions—specifically, how to model Dimensions and their Members.

Modeling dimensions

The Dimensions command, found on the Model menu, allows you to create, modify and delete dimensions in a currently opened database.

Creating dimensions

To create a dimension:

  1. From the Model ribbon, click Dimension to open the Dimension dialog. The Dimensions field is at the top of the dialog  and dimension names are displayed below. If you have created a new database and are entering dimensions for the first time, the dialog box will be empty.

  2. In the field, enter Salesperson and click Add.

dimensionsdialogsalesperson.png

Figure 2.    Dimensions Dialog - Salespersons

The new Dimension is created, and its name is added to the list.

Renaming a dimension

You can change the name of a Dimension at any time without losing its defined Members and Hierarchies or any data referenced by its Members. However, changing Member or Dimension names requires that you edit any associated formulas. Security privileges are required to modify a dimension.

To modify the name of a dimension:

  1. From the Model ribbon, click Dimensions to open the Dimensions dialog.

  2. Select a dimension to be modified.

  3. In the Dimension field, enter the new dimension name

  4. Click Rename.

  5. Click OK to save the new name.

Deleting a dimension

Within the Dimension dialog  you can delete a dimension from the current database. If the dimension is part of the Metadata of a Cube, the application will display a warning message that the dimension is being used.

To delete a dimension from the current database:

  1. From the Model ribbon, click Dimensions to open the Dimensions dialog.

  2. Select a dimension to be deleted.

  1. Click Delete.

  2. Click OK.

Entering a dimension description

Dimensions names are often self-explanatory, but for more complex models, descriptions can be added. Dimension descriptions are useful when viewed in the proCube Web interface, where they display side-by-side with the dimension names while users create reports (the proCube Web equivalent of slices).

To enter a dimension description:

  1. From the Model ribbon, click Dimensions to open the Dimensions dialog.

  2. Select a dimension to be described.

  3. Click Description (a dialog opens) and enter the explanatory text in the field.

  4. Click OK to return to the Dimensions dialog.

Member types/hierarchy dialog

Defining Members  is the next step in database design. proCube supports two Dimension member types: detail and aggregate.

Detail members

Detail member s 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 (Japan, China, USA) in a Dimension calledRegions. 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, such as 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 Months, 1st 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 a PC has, the faster your models will recalculate and update.

Since “child” Members  can be Aggregates, a child Member shouldn’t be equated with Detail members. See the diagram below for an illustration of how a child Member can also be an Aggregate.

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

The figure below the detail members Argentina, Columbia, Canada and USA as children of their respective Aggregate members, South America and North America. These are in turn are children of their Aggregate parent member, World. The sum icon indicates Aggregate members.

regionsworldnahierarchy.png

Figure 3.    Sample Member Hierarchy

Creating a new member

To add Members  to a Dimension—whether it be the earliest Member of a Dimension or the “latest” Member (e.g., the newest hire for a Salesperson dimension), you will use the Dimension Hierarchy dialog.

To create a new member:

  1. From the Model ribbon, click Dimensions to open the Dimensions dialog.

  2. Select the Salesperson Dimension and click Edit. The Salesperson Hierarchy Dialog opens.

The left side of the dialog contains the Members list . The right side of the dialog contains the Hierarchy Definition (we will soon demonstrate how to use these boxes to create a Hierarchy). A splitter (center post) allows you to resize these two areas. This dialog is also resizable (the maximized box in the top-right corner allows the dialog to be sized to the full screen). If you make changes in the dialog , you will be prompted to save when you are either changing dimensions or attempting to close the dialog.

It is not necessary to manually designate your member types as aggregate or detail when you add Members. Hierarchy will be determined when you move the members over to the right side of the dialog.

  1. Right click within the Members list and click Create New Member. An editable “untitled” displays.

  2. Enter one of the Acme Trading Company Salespeople names as shown in Salesperson Hierarchy Dialog.

  3. Add the remaining 4 names.

salespersonhierarchy.png

Figure 4.    Salesperson Hierarchy Dialog

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

Renaming/deleting a member

When you highlight a Member in the Member list, you can rename or delete it by right-clicking within the list. This opens a menu and you can then select your intended command.

rightclickmenuhierarchydialog.png

Figure 5.    Right Click Menu - Hierarchy Dialog

Finding/sorting members

When long lists of Members  exist in the Member list box, it can be cumbersome to scroll through the list in order to locate a specific Member. proCube solves this problem by providing special Functions for honing in on Member names. Using Subsets or Filters, you can quickly select Members and more easily construct hierarchies to view within a proCube slice or a linked Excel worksheet.

You will also perform these same finding and sorting functions within the Edit Slice Dialog.

Selecting the right click menu's Sort command displays a sub menu containing sort options for Natural, Ascending, and Descending.

Member Subsets

The drop-down list at the top left in the Dimension Hierarchy dialog 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 (in the Edit Slice dialog) is displayed.

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.

Creating a dimension hierarchy

Once you have added members to a dimension, you will define that dimension's hierarchy. The hierarchy determines the aggregation of dimension members.

To create a Dimension Hierarchy:

  1. Display the Salespersons dimensions as shown in Salesperson Hierarchy Dialog.

  2. Click a Dimension name and drag it over to the other side and drop it under the Salesperson node.

  3. Create additional members and arrange them as shown in Salesperson Hierarchy - Expanded.

salespersonexpandedhierarchy.png

Figure 6.    Salesperson Hierarchy - Expanded

By dragging a category (Domestic) from the left side and releasing it under Salesperson, Domestic becomes a child of its Salesperson parent. Dragging and dropping East under Domestic makes East a member to its Domestic aggregate. In this sense, Domestic is both a member(to Salesperson) and an aggregate (to East). An aggregate is designated by a Sigma. Its member is designated by a #.

The Salesperson Dimension contains Domestic and Foreign Aggregate members. Domestic has children Aggregates: East, Midwest and Southwest, with Detail salespeople under them, while Foreign salespeople are grouped together as Detail members.

Members are created in the Member list and one member can be added to the hierarchy by highlighting and clicking the right arrow. To move multiple members, highlight them and click the double right arrow. Once a hierarchy is built, it can be collapsed or expanded by clicking the + or icon to the left of an aggregate member name. The Hierarchy Definition box supports multiple selections and drag-and-drop editing.

  1. Click OK (the green check) to save your Salesperson hierarchy.

Deleting members from a hierarchy

To delete members  from a dimension hierarchy:

  1. Select a member in the Hierarchy Definition , which is located on the right in the Hierarchy dialog.

  2. Right-click to open the Right Click Menu and select Delete.

When you delete a Member from a Hierarchy, the Member remains in the Member list.

Creating Multiple and Ragged Hierarchies

In Salesperson Hierarchy - Expanded, the Hierarchy has two Aggregate children, Foreign and Domestic, and both roll up into the Salesperson aggregate. You probably noticed that the Domestic aggregate was further broken down into regions (East, Midwest and Southwest), whereas the Foreign aggregate did not include that additional level of depth. This is an example of a ragged hierarchy (i.e., a hierarchy in which some Members are at different depths, or levels, than others).

To consider another example of a ragged Hierarchy, you could create some salespeople who do not belong to either the foreign or domestic aggregations at all, but exist as Detail members rolling up directly under theSalesperson aggregate.

You may also want to create multiple Hierarchies in a Dimension. Perhaps your salespeople, in addition to being categorized as foreign or domestic, should also be rolled up into types: those who sell to wholesalers, and those who sell to restaurants. You could call these categories Type A and Type B. Some of your domestic salespeople and foreign salespeople may be Type A, and others may be Type B. Still others may not be included in either type.

These atypical types could roll up into an aggregate called Total Customer Type. The Domestic and Foreign aggregates would then roll up into a separate Hierarchy label which we could create and title Total World. If we made these changes, our completed hierarchy would look like:

multipleraggedhierarchy.png

Figure 7.    Multiple Ragged Hierarchy

As you can see, you can use multiple and ragged hierarchical structures to model the members of a Dimension in meaningful aggregations so that they are useful in your reports and business analysis.

Aggregate weights

Aggregate weights are used when a member in an Aggregate must be assigned a multiplied value in relation to other Members in the Aggregate in order for the data to be properly represented. For example, in the Accountdimension, because it is a factor of Margin, Cost of Sales should always be handled as a negative number because it will always represent an amount subtracted from Revenue.

To designate Cost of Sales as a negative number in relation to the Margin hierarchy, we can assign it a weight of –1. This means that the data included in Cost of Sales will always be multiplied by –1 when it is rolled up in that Aggregate. The Margin hierarchy in the Account Dimension appears as follows:

marginrevenuecostsales-1.png

Figure 8.    Margin Hierarchy with Cost of Sales as a Negative Number

The following steps will show you how to define the Aggregate Weight of a child member in the Sales Account dimension.

To “weight” the Cost of Sales, proceed as follows:

  1. Open the 'Sales Account' Hierarchy dialog (double-click on Sales Account in the Dimensions dialog).

Note that currently, there is no Hierarchy under Sales Account, and we only have the three members: Quantity, Revenue and Cost of Sales.

  1. Add the new Member, Margin.

  2. Set up the Hierarchy (in the Hierarchy Definition box on the right) so that the natural relationship is established between the members as follows:

salesaccountdimension.png

Figure 9.    Sales Account Dimension

  1. Double-click or Right-click on the member Cost of Sales and select Edit Weights from the pop-up menu; or select the Specify Member Weights toolbar button.

Note that a box appears to the right of Cost of Sales, and in this box, you can enter a weight for this Member.

  1. Enter -1 as the weight of Cost of Sales in the box.

  2. Click OK to return to the Dimension dialog.

  3. Click OK to return to proCube Management Studio.

In proCube, the default Aggregate weight  is equal to ‘1’. Thus, a parent Aggregate member is simply the sum of all child Members defined in the Dimension hierarchy. In the example exercises we have completed, all aggregates you defined are standard with the exception of Margin, in the Sales Account dimension

Member aliases and alias groups

Dimension members are usually referenced by their unique name, but they may also have multiple Aliases. For example, a Member called “January” might have aliases like “Janvier” or “Jan”. Aliases are organized by Alias Groups, which allow you to specify categories of aliases for all members in a Dimension. The Aliases above may correspond to Alias Groups such as “French full name” and “Abbreviation,” respectively. The Alias commands, shown in the margin, allow you to define Alias names and Alias Groups for ranges of Members.

Aliases must be unique within a Dimension. In other words, the selected Alias field must have a unique name for each Member. In addition, an Alias name for a Member cannot be the same name as another Member in the Dimension.

To create Member aliases from the Dimension Hierarchy dialog box: 

  1. Select from the Member List box (which is on the left-hand side) the Member that you want to assign to an Alias Group (in this exercise, use the Month dimension).

  2. Click the Member Aliases button on the toolbar (shown in the following figure), or right-click the Member name in the Member box and select Alias from the pop-up dialog.  The Alias dialog box is displayed (in the example below, January is the selected member):

aliasdialogaliasgroupalias.png

Figure 10.           Alias Group and Alias

  1. Click Add Group.  A Group named “Group 1” is displayed below the Alias Group tab.

  2. Double-click the name and rename it to french full name.

  3. Double-click under the Alias tab and enter Janvier.

  4. Click OK. You are returned to the Dimension Hierarchy dialog .

  5. Click Alias Groups. This allows you to create Aliases and new Alias Groups for all the Members in the Dimension at once; to change Group or Alias names; or to Add or Delete Groups.

aliasgroupsjanvier.png

Figure 11.           Alias Groups - French Full Name

Member properties

Properties allow you to “tag” Members in a Dimension with pertinent information. Properties differ from Aliases in that a property does not uniquely identify a Member. As a result, a Property name may be repeated for two or more Members.

  • A property belongs to a Property Group for a dimension.

  • A Property Group categorizes similar properties across an entire dimension.

You can assign Members a Property for annotation purposes in the Excel environment. A Member’s properties can be displayed on a linked Excel worksheet, providing you with easy access to additional information about a Member.

Additionally, the Functions in the Formula dialog in proCube can make use of Properties (in place of individual Member names) in formula construction. Using Properties  in formulas can greatly reduce the number of formulas needed for a calculation.

To modify/view properties, use the Dimension Hierarchy dialog from the Model menu:

  1. Select the Salesperson dimension.

  2. Double-click Salesperson to open it in the Salesperson Hierarchy dialog.

  3. Click Properties to open the Property Groups dialog. All members in the dimension are listed, much like the Alias Groups dialog. You have the ability to create Property Groups and include appropriate Members in those Property Groups.

propertygroupsdialog.png

Figure 12.           Property Groups Dialog

Persistent members

This feature allows you to tag a Member that has been created in proCube to “persist” through an Xchange Rebuild of a Cube. In other words, a Member marked as Persistent will not be deleted in the Rebuild.

Xchange is required to enable usage of the persistent members feature.

To make a member persistent:

  1. Open the Dimensions dialog.

  2. Double-click the Salespersons dimension to display its members in the Salsesperson Hierarchy dialog.

  3. Highlight a dimension member and click Mark Member as Persistent. The icon in front of the Member name gets a blue tint to show that it is persistent.

Working with subsets

Subsets are a saved group of members in a Dimension. Subsets are useful because they can be referenced in a Slice Window within proCube, or they can be referenced in reports via the OLAPPivot function and be used to write formulas and other Excel functions.

Subsets are created through the Edit Slice Dialog or the Subset Manager. Subsets can be deleted, renamed, and edited through the Subset Manager, but not in the Edit Slice Dialog.

Adding a new subset

To add a new subset:

  1. From the Model tab, click Dimensions.

  2. Select the Salespersons dimension and click Subsets to open the Edit Subsets Dialog.

  3. In the text box, enter BeverageProducts and click Add. The Edit Salespersons for BeverageProducts dialog opens.

  4. Select one or more Members by dragging it across from the left hand side to the right hand side

.editsalespersonforbeverageproducts.png

Figure 13.           Edit Salesperson for BeverageProducts Dialog

  1. When finished, click OK. The Edit Subsets dialog opens with your saved subset.

editsubsetsdialog.png

Figure 14.           Edit Subsets Dialog

Deleting a subset

To delete a subset:

  1. From the Model tab, click Dimensions.

  2. Select the Salespersons dimension and click Subsets to open the Edit Subsets Dialog.

  3. Select a subset and click Delete.

Renaming a subset

To rename a subset:

  1. From the Model tab, click Dimensions.

  2. Select the Salespersons dimension and click Subsets to open the Edit Subsets Dialog.

  3. Highlight a subset and in the Subset text box enter the new name.

  4. Click Rename.

Editing a subset

To edit a subset:

  1. From the Model tab, click Dimensions.

  2. Select the Salespersons dimension and click Subsets to open the Edit Subsets Dialog.

  3. Click Edit to open the Edit Dimension Dialog.

  1. Add or remove members from the right side.

  2. Click OK.

Sorting in the edit slice dialog

Sorting helps you to find and arrange your members when modifying your slice dimensions. Most of the sorting functionality is found on the Member tab. However, a filter field search can also be performed on subsets in the Hierarchy tab.

The drop-down list at the top left in the Dimension Hierarchy dialog 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 (in the Edit Slice dialog) is displayed.

editslicedialogproductsalesacct.png

Figure 15.           Edit Slice Dialog - Sorting Options

The Filter field 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. Clearing the Filter field allows all contents to display.

For both tabs, clicking any of the columns sorts between Ascending and Descending. The Name column sorts by dimension name, the Weight column sorts by assigned weight values, the # column sorts by ordinal number, and the T column sorts by member types (A is for Aggregate).

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk