The parent-child hierarchy structure is particularly common in business so it is very important for you to master the techniques related to it. In this section, the concepts discussed in the previous section are extended. Several important properties are supported by Analysis Services 2005 for parent-child hierarchies. One of the important properties for a parent-child hierarchy is called the UnaryOperatorColumn.
Unary operators are used for custom rollup of members to their parent where the rollup operation is a unary operation. A unary operator, as the name suggests, is an operator that takes a single argument — the member — and rolls up the value of the member to its parent. As with the custom rollup column, you need to have the unary operators specified as a column in the relational table, and this column must be set as a property for the parent-child hierarchy. Unary operators can be applied also to non parent-child hierarchies, but that scenario will not be covered here. The following table shows the various unary operators supported by Analysis Services and a description of their behavior.
The value of the member is added to the aggregate value of the preceding sibling members. This is the default operator used if no unary operator column is specified.
The value of the member is subtracted from the aggregate value of the preceding sibling members.
The value of the member is multiplied by the aggregate value of the preceding sibling members.
The value of the member is divided by the aggregate value of the preceding sibling members.
The value of the member is ignored.
The value is multiplied by N and added to the aggregate values. N can be any numeric value (typically N is between 0 and 1).
An empty unary operator is equivalent to "+."
A business scenario commonly used to demonstrate the usefulness of rollups using unary operators is the case where Net Income equals Sales minus Cost of Goods Sold. As for the calculation of tax at different rates depending on tax bracket, that will come a little later because calculating those values will require custom rollups, and for now the topic of discussion is the unary operator. In this example, Sales figures will be added, hence the "+" operator is used, and for Cost of Goods Sold, which is subtracted, the "−" operator is used. Were depreciation included in the following example, given that it is a non-cash expense, you might choose to ignore it in the hierarchy by using the tilde (~) unary operator. Follow the steps below to set up unary operators for the parent-child hierarchies in the Account and Organization dimensions.
Open the Account dimension and click the Accounts hierarchy. In the Properties window you will see the properties associated with parent-child hierarchies under a section called Parent-Child, as shown in Figure 8-10. Click the drop-down list box next to UnaryOperatorColumn and select New.
In the Object Binding dialog select Operator as the column, as shown in Figure 8-11.
If you explore the data for DimAccount table, you can see the unary operators associated for each account as shown earlier in Figure 8-7. Note that there are "~" operators indicating "ignore this" for certain items. Deploy the changes to your Analysis Services instance. Because the Operator column is an attribute hierarchy in the Account dimension, it automatically becomes a member property of the key attribute. All member properties of the key attribute are automatically inherited by the parent attribute, the parent-child hierarchy. Therefore you can view the unary operators associated with each account in the Dimension Browser by enabling the member property Operator as shown in Figure 8-12.
The next step is to ensure that you have set the unary operators correctly and the rollup to parent occurs as desired. For verification, edit the Adventure Works DW cube and click the Browser tab. Drag and drop the Amount measure and the parent-child hierarchy Accounts from the Metadata pane to the data and row area within the Cube Browser, and then expand the levels Net Income→Operating Profit→Gross Margin. Before specifying the unary operator, Analysis Services aggregates the values for Net Sales and Total Cost of Sales as a sum to calculate the value for Gross Margin. Because a unary operator has been specified, you should see the value for Gross Margin is the difference of Net Sales and Total Cost of Sales, as shown in Figure 8-13.
Analysis Services 2005 introduces a new unary operator, which is referred to as N in the unary operator table. N is a numerical value that is used as a weighting factor so that the value of the member is multiplied by the value N and then aggregated to the parent. For example a company might calculate overhead costs for utilities like electricity at the level of an entire factory. Electricity consumption is not evenly distributed across the organizations within the factory, though; the manufacturing floor might be sucking up power at a rate completely disproportionate to the administrative section. This disparity is something that should be accounted for when doing internal costing analysis. One way managerial accountants address the problem is to calculate the overhead at the departmental level and at the factory level. The amount of electricity consumed can be weighted by some predetermined amount such that cost assignments are rolled up to the correct parents. For example, the manufacturing department could be assigned a ratio of 1 to 9 for factory level overhead versus department level overhead. That is, 10% of the electricity used by manufacturing facilities is assigned to factory overhead, while the other 90% is assigned as a department-specific cost.
In some organizations, even though a group is reporting to a parent (higher level group), the measure value of the sub-group might be rolled up as a fraction of the total measure value to the parent. This is because parent groups only own a part of the organization. If you know the percentage of ownership for various organizations as a measure, you can specify CustomRollupColumn with an appropriate MDX expression. However, with Analysis Services 2005's unary operator N, you can simply specify the rollup using numerical values.
The AdventureWorksDW relational database provides an example of the unary operator N for the Organization dimension, where the percentage of ownership is specified in a column in the Dim Organization table. Follow the steps below to enhance the Dim Organization dimension created by the Dimension Wizard so that the dimension is modeled to match the actual business results:
Open the Dim Organization Dimension. Specify the NameColumn property for the key attribute as OrganizationName.
Rename the Parent Organization Key parent-child hierarchy to Organizations. Click the UnaryOperatorColumn and select New. In the Object Binding dialog, select the PercentageOfOwnership column.
At this point, you have specified the weighted average for the Organizations parent-child hierarchy. You might immediately want to see the results by deploying the changes. However, if you do deploy as such, you might see weird results because of the way calculations are applied in the cube. This is because when Analysis Services retrieves data for measures across a specific hierarchy, it takes the default members of other hierarchies while doing this evaluation. For most hierarchies the All member is the default. In the Account dimension the IsAggregatable property is set to false because aggregating the data for the top-level members in the Account dimension does not make business sense. In such circumstances, Analysis Services uses the default member of the hierarchy. If a default member is not specified, Analysis Services retrieves the first member of the hierarchy. The first member of the Accounts hierarchy is Balance Sheet and the unary operator for Balance Sheet, ~ (tilde), will result in very weird or non-intuitive results. In order for you to see meaningful results, you need to make sure you choose the right default.
For the Accounts hierarchy, click the DefaultMember property. In the Set Default Member dialog, choose a member to be the default option, select Net Income, and click the OK button. The Net Income member has a unary operator of +, which will result in meaningful data being seen while browsing the Amount measure for other dimensions. Now, deploy the changes to the Analysis Services instance. This percentage of ownership will be applied for the measures being queried.
If you browse the Organization dimension along with the measure Amount you will notice appropriate rollups based on the weighted unary operator. Figure 8-14 shows the amount for various organizations, and you can see that the amount from the Canadian organization is aggregated as 0.75 to the North American organization.
When multiple calculations are specified for a cell, Analysis Services 2005 uses a specific order in which calculations are evaluated. Due to the order of calculations, cell values might not always be intuitive and you can sometimes see results other than expected. This is mostly due to the way in which calculations are being applied. We highly recommend you know your cube design well and the calculation precedence (order of calculations) and to subsequently verify the results. Several calculations can be applied to a measure: semi-additive calculation, unary operator, and custom rollup. You learn more about semi-additive measures briefly in this chapter and in detail in Chapter 9. When Analysis Services is evaluating the measure value for a member, it initially calculates the regular aggregate of the measure value. This aggregate can be Sum, Count, or any of the semi-additive functions (to be discussed later). If a unary operator is specified, the unary operator rollup is applied for the member across that specific dimension and the value of the measure is overwritten. Finally, if a custom rollup is specified for the member, the value resulting from the custom rollup MDX expression is evaluated as the final result. The evaluation of a cell value is done across each dimension and if dimensions have custom rollups and unary operators then all the unary operators are applied followed by custom rollups based on the order of the dimensions within the cube.
When you create multi-level hierarchies, various attribute hierarchies form the levels of the hierarchy. For example, in a Geography hierarchy you will have Country, State, City, and Zip Code as levels and when you browse the dimension you can see the names of the levels as the names of the attribute hierarchies. Parent-child hierarchies are unique and different from the user hierarchies. While creating regular hierarchies, you can see the various levels in the dimension editor, but for parent-child hierarchies you cannot visually see the number of levels unless you process and browse the hierarchy. The levels within a parent-child hierarchy are embedded within the relationship. Analysis Services allows you to define a name for each level of the parent-child hierarchy. Typically, parent-child hierarchies can contain multiple levels and the total number of levels corresponds to the depth of the parent-child hierarchies. By default, Analysis Services 2005 provides names for the levels — Level 01, Level 02, and so on. Level N is based on the depth of the parent-child hierarchy.
If you want custom names to be specified for each level, Analysis Services 2005 provides a property for just that. For example, if you have an org-structure parent-child hierarchy, you can name the levels CEO, Presidents, Vice Presidents, General Managers, Product Unit Managers, Managers, Leads, and Individual Contributors. If you want to specify common prefix, use the parent-child property called NamingTemplate. If you click the selection for NamingTemplate, you will launch the Level Naming Template dialog, as shown in Figure 8-15. In this dialog you can specify the name for each level in the parent-child hierarchy. If you want a constant prefix name followed by the level number, such as Employee Level 1, Employee Level 2, and so on, you just need to specify Employee Level * as shown in Figure 8-15 and Analysis Services will automatically append the level number at the end of each level. Edit the Dim Employee dimension and specify the level names as Employee Level * for the Parent Employee Key hierarchy as shown in Figure 8-15. Change the name of the parent-child hierarchy to Employees, add a new calculated column called Full Name in the DimEmployee table within the DSV, which is the sum of FirstName and LastName, and make the Full Name relational column as the NameColumn for the key attribute of employees. Deploy the changes to the Analysis Services instance.
When you browse the Employees hierarchy you will see the new level names as shown in Figure 8-16. When you click on a member, you will see the level name shown next to the Current Level.
In Figure 8-16 you can see that the member David Bradley also reports to David Bradley. This is because each parent member is also included as its child, so that the value for that parent member is an aggregate of all its children and its own value. By way of example, if you have a Sales organization of employees and each manager manages a few sales employees in a region in addition to being in charge of certain sales, the total sales by the manager is a sum of all the direct reports plus the manager's own sales. That is why you sometimes see a member reporting to him or herself while browsing a parent-child hierarchy. If you know that the non-leaf members (as with managers in an employee organization) do not have fact data associated with them and are just an aggregate of the children, Analysis Services 2005 provides a property by which you can disable a member being a child of itself. This property is called MemberWithData and setting the value to NonLeafDataHidden, as shown in Figure 8-17, allows you to disable a member being shown as reporting to itself.
You have now learned several enhancements to parent-child hierarchies. The properties for parent-child hierarchies provided by Analysis Services help you model requirements for different business scenarios. In the next section you will look at other properties of attributes and dimensions that help you enhance dimensions.