Specify Member Weight

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 Account dimension, in order to arrive at a calculation of the Parent, 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:

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

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

1.   Bring up the ‘Sales Account’ Hierarchy dialog box (double-click on Sales Account in the Dimensions dialog).

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

2.   Add the new Member, <Margin>.

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

3.   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, appearing to the right of Cost of Sales, a box appears where you can enter a weight for this Member.

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

5.   Click the OK button (green checkmark) when complete. You are returned to the Dimension dialog box.

6.   Click OK. You are returned to the main application window.

 Notes

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 exercises we have just completed, all aggregates you defined are standard with the exception of Margin, in the Sales Account dimension.