Problem: I need the ability to create a separate dimension hierarchy structure for different reports.
Solution: Alternate Hierarchy functionality allows you to have more than one hierarchy to consolidate dimension members in multiple ways for reporting needs. For example, cost centers might be aggregated one way for management reporting and another way for the legal entity reporting. The default dimension hierarchy structure in Model Manger can be setup with the legal entity structure. For management reporting, an Alternate Hierarchy can be used.
Tip #1 The alternate hierarchy can be used in any dimension, except the Account dimension.
Tip #2 The alternate hierarchy functionality is used only for reporting purposes. You cannot perform data entry in, or run a process for, an alternate hierarchy structure.
Tip #3 You can have multiple alternate hierarchies within the dimension.
Steps to set-up Alternate Hierarchy: for Organization Dimension
-
Open the Organization dimension.
-
In the lip, click on Edit custom member properties.
- We need to add a new property. Click Add . Choose Add New.
- Enter the following:
-
Name: Legal Entity
-
Description (optional)
-
Data Type: Text
-
Default Value: North
-
Table Column Name: Legal
-
Size: 100
-
Click OK . You should now see Location appearing in the Member Properties for each of your members.
-
Type in a location for each leaf member. Choose from North, East, and West.
-
Save your changes.
-
The next step is to create an alternate hierarchy that groups my members by region. From the lip, select Edit Alternate Hierarchy.
-
Click Add.
-
Enter the following:
-
Hierarchy Name: Region
-
Description (optional)
-
Level Name: Legal Entity
-
Member Property: Legal Entity
- Switch to the Preview tab. Here you can see the new rollups you defined.
- Save and Publish. You will now see the option to select the Alternate Hierarchy when creating data view.
Let us know how this works!