About preparing hierarchy data
Updated: 2009-04-30
This topic includes information about what it means to create a hierarchy and the different ways that you can create hierarchies for loading data to the application database.
To learn how to prepare hierarchy data for loading to the staging database, see one of the following topics:
Hierarchy Creation
A hierarchy is a structured organization of a subset of dimension members. The structured organization of a hierarchy is a parent-child relationship between dimension members. Hierarchies are used to help describe data in a fact table and to define the data rollup that is required by parent members. A dimension can contain one or more hierarchies.
Planning Server supports the following methods for creating a hierarchy for a dimension. You can create hierarchies in the following ways:
In the staging database, hierarchies can be populated from other source systems during data preparation in Step 3: Populating the staging database (a manual process).
In the staging database, hierarchies can be created during data preparation by using the bsp_DI_CreateHierarchyFromDimension stored procedure. To learn more, see Populate a property-based hierarchy.
Note
Before you create or populate a hierarchy in the staging database, you must create an empty member set in Planning Business Modeler and then synchronize the staging database. See Step 2: Synchronizing the staging database.
The following assumptions apply to population of hierarchies:
The dimension is loaded with valid data and, for a hierarchy that is based on dimension properties, custom properties are created.
Synchronization is performed to copy the hierarchy structure to the staging database.
Hierarchy Example
The following information is an example of a dimension, a related hierarchy, and a visual representation of the hierarchy. The number of rows and columns is reduced for illustration.
D_BusinessProcess Dimension
The following table shows a simplified example of the D_BusinessProcess dimension.
| MemberID | Label | Description |
|---|---|---|
2 |
CONSOLIDATED |
Consolidated value |
3 |
ELIMINATION |
Elimination |
7 |
AUTOADJ |
Automatic adjustment |
8 |
INPUT |
Input |
9 |
MANADJ |
Manual adjustment |
10 |
ALLOC |
Allocations |
11 |
PREALLOC |
Before allocations |
12 |
POASALLOC |
Post allocated value |
H_BusinessProcess_Standard Hierarchy Table
The H_BusinessProcess_Standard hierarchy is created as a system hierarchy for the D_BusinessProcess dimension. In the following table, the H_BusinessProcess_Standard hierarchy is represented by mapping each MemberID to a ParentMemberID in a parent-child relationship.
| RowID | MemberID | ParentMemberID |
|---|---|---|
1 |
12 |
2 |
2 |
10 |
12 |
3 |
11 |
12 |
4 |
8 |
11 |
5 |
9 |
11 |
6 |
7 |
11 |
10 |
2 |
0 |
12 |
3 |
2 |
Hierarchies That Are Based on Dimension Properties
A dimension can be populated or defined in Planning Business Modeler. A custom property, such as a parent or category name, is mapped to the label of a dimension. If the property value is correctly mapped, a dimension hierarchy can be created by using the Label and custom property in the dimension. The following table shows a simplified example of an account dimension hierarchy that uses the Label and AccountHierarchyProperty custom property for a parent-child relationship.
| MemberID | Label | AccountHierarchyProperty | Name |
|---|---|---|---|
15094 |
1 |
NULL |
Retained profit for the period |
15095 |
2 |
1 |
Profit for financial period |
15092 |
3 |
1 |
Dividend expense to group companies |
15093 |
4 |
1 |
Dividend to minority shareholders |
As a result, the visual representation of the account dimension hierarchy lets users view accounts as they roll up to their respective parent account, as follows.
Retained profit for the period
Profit for financial period
Dividend expense to group companies
Dividend to minority shareholders
To learn how to create a hierarchy that is based on labels or dimension properties, see Populate a property-based hierarchy.
Download this book
This topic is included in the following downloadable book for easier reading and printing:
See the full list of available books at Downloadable content for PerformancePoint Planning Server.