Example 3 - Hierarchies and Aggregate Weights

From OLAP

(Redirected from Example 3)
Jump to: navigation, search

Continued from:



>==Creating a Dimension Hierarchy==

Once the Members have been defined for a Dimension, the Hierarchy of these Members must also be defined. The Hierarchy determines the aggregation of Dimension members.

You will now create the Hierarchy by selecting Members and moving them to the Hierarchy Definition dialog box on the right side of the Dimension Hierarchy dialog box.

Define the Hierarchy for the Months Dimension as follows:

1. Select Total Year from the Members list box on the left.
2. Drag it so that it is placed just below Months in the Hierarchy Definition box on the right, so that it appears as below:


PowerOLAP Hierarchies


3. Select 1st Quarter, press and hold "Ctrl", select 2nd Quarter, 3rd Quarter, and 4th Quarter.
4. Drag the selected Members just below Total Year in the Hierarchy Definition dialog box. A sigma sign (for sum) appears beside Total Year indicating that now this Member has become an Aggregate Member.


PowerOLAP Hierarchies


5. Select January through March (by holding the "Shift key")
6. Drag the selected Members just below 1st Quarter in the Hierarchy Definition dialog box. Now the sigma sign appears beside 1st Quarter.
7. Select April, May and June in the left window. Release the "Ctrl key". Now Select 2nd Quarter in the Hierarchy Definition dialog box on the right like so:


PowerOLAP Hierarchies


8. Press the Add Selected Members as Child button PowerOLAP Add Selected Members as Child button. The sigma sign appears, next to 2nd Quarter. 
NOTE: These are the two methods, drag and via buttons, that you can use to create and edit Dimension Hierarchies.
9. Drag over the Detail Member July under 3rd Quarter.
10. Highlight August and September from the list on the left and highlight July on the right.
11. Click on the Add Selected As Sibling button, PowerOLAP Add Selected As Sibling button, to add them to the 3rd Quarter Hierarchy. Now August and September are shown under July, and the three Members comprise 3rd Quarter. 
NOTE: There are other buttons available for Hierarchy edits: "Add all Members as Child", PowerOLAP Add all Members as Child button, and "Add All As Sibling" button, PowerOLAP Add All As Sibling button
12. Complete the Hierarchy for the 4th Quarter so that when you are done, the Hierarchy looks as follows:


PowerOLAP Hierarchies


13. Close the Dimension Hierarchy dialog by clicking the PowerOLAP Hierarchies button. You are returned to the Dimension dialog box.


Contents

Hierarchies for "Accounts" and "Regions"

1. Add the following Members into the appropriate Dimensions. You will define their type, whether Detail or Aggregate, according to the Hierarchies you see in the following two figures:


PowerOLAP Hierarchies


2. When complete, the Dimension Hierarchies for Accounts and Regions should look as follows:


"Accounts" Hierarchy dialog box
"Regions" Hierarchy dialog box

A hierarchical relationships — as you completed in the three Dimensions, Months, Accounts and Regions — defines a parent-child relationship between Members. Just as we have member siblings that exist on the same level in a Hierarchy under an Aggregate member, we also speak in terms of Child members, which are defined as all Members that make up parent aggregations. It is important to note that a Child member is not necessarily a Detail member. Child members may themselves be parents of other Members within a Hierarchy. For example, in the Regions dimension, North America is an Aggregate member; but it is also a child of Total Regions.

The Dimension Hierarchy Toolbar

The buttons on the toolbar in the Dimension Hierarchy dialog box, and the functions they perform, are as follows:


PowerOLAP Hierarchy Toolbar


  1. Create New Member: Add a new Member (or press Ctrl-Enter to successively add new Members).
  2. Format: Select formatting option for the corresponding cells.
  3. Member Aliases: Define and edit Aliases and Alias Groups for the selected Member.
  4. Alias Group: Add or delete Alias Groups, and assign names for each Member.
  5. Properties: Assign Members a property for annotation purposes. A Member’s properties can be displayed on an Excel worksheet when needed.
  6. Mark Member as Persistent: Tag a Member as ‘persistent’ to prevent its data from being overwritten when updates or Cube re-builds occur.
  7. Add Selected as Sibling: Insert the selection in the Member list on the left into the Hierarchy Definition on the right, below the selected Member.
  8. Add All as Sibling: Insert all Members in the Member list after the selected Member in the Hierarchy, making them sibling Members.
  9. Add Selected Members as Child: Insert selected Member(s) in the Member list as children of selected Member in the Hierarchy.
  10. Add All Members as Child: Insert all Members in the Member list as children of selected Member in the Hierarchy.
  11. Specify Member Weights: Edit the weight of a child Member (active only when a child Member is selected in the right hand Hierarchy Definition box).
  12. Help: Accesses the help screen for the Dimension Hierarchy.
  13. OK: Exits the dialog, with changes made and saved for the Dimension.

The functions listed above are described more fully in the PowerOLAP User Manual, though we will now discuss Aggregate weights in order to complete our QS Database Dimension set-up.

Aggregate Weights

Aggregate weights are used when a Member must be assigned a multiplied value in an aggregation in order for the data to be properly represented. For example, in the Accounts hierarchy, Cost of Sales should be handled as a negative number in the Gross Profit aggregation because it will represent an amount subtracted from Gross Profit (i.e., Gross Profit = Net Sales - Cost of Sales). To designate Cost of Sales as a negative number in the Gross Profit aggregation, 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 the Hierarchy.

To “weight” Cost of Sales, complete the following process:

1. Return to the Accounts Hierarchy dialog box (double-click on Accounts in the Dimensions dialog), and double-click Cost of Sales in the Hierarchy Definition box on the right. 
Note that, to the right of Cost of Sales, a box appears where “1” is highlighted and where you can enter a weight for the Member.
2. Enter -1 in the box so that it appears as follows:


PowerOLAP Aggregate Weights


3. Click the "OK" button, PowerOLAP Ok button, to return to the Dimension dialog box.
4. Click "OK" again to be returned to the Main Application Window 
In PowerOLAP, 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 exercise, all Aggregates you defined are standard with the exception of Gross Profit, in the Accounts dimension.

Therefore, you do not need to define Aggregate weights for the remaining Aggregate members.
Now try Example 4 - OLAP Cubes and Slices
Retrieved from "http://www.olap.com/w/index.php/Example_3_-_Hierarchies_and_Aggregate_Weights"
Personal tools