Time-Dependent Hierarchy Structures in the Query

The following example explains the features and different analysis options with time-dependent hierarchy structures.

Time-Dependent Hierarchies

The time-dependent hierarchy for characteristic M has the following structure:

Hierarchy for Characteristic M

Successor Node	Predecessor Node	Valid from	Valid to
Root node		1.1.	31.12.
Node1	Root node	1.1.	31.12.
Node2	Node1	1.1.	16.2.
Leaf1	Node2	1.1.	16.2.
Leaf2	Node2	1.1.	31.1.
Node4	Node1	1.1.	31.12.
Leaf2	Node4	1.2.	31.12.
Leaf3	Node4	1.1.	31.12.
Node3	Root node	1.1.	31.12.
Node2	Node3	17.2.	31.12.
Leaf1	Node2	17.2.	31.12.
Leaf4	Node2	17.2.	31.12.
Node5	Node3	1.1.	31.1.
Leaf5	Node5	1.1.	31.1.
Node6	Root node	1.2.	31.12.
Leaf5	Node6	1.2.	31.12.

The following figure outlines the hierarchy from a modeling perspective:

InfoProvider

An InfoProvider with the characteristics Characteristic M, Day, Month and the key figure Key Figure contains the following data:

InfoProvider Data

Characteristic M	Day	Month	Key Figure
Leaf1	15.1.	Jan	10
Leaf2	15.1.	Jan	20
Leaf3	15.1.	Jan	30
Leaf4	15.1.	Jan	40
Leaf5	15.1.	Jan	50
Leaf1	15.2.	Feb	25
Leaf1	28.2.	Feb	5
Leaf2	15.2.	Feb	15
Leaf3	28.2.	Feb	5
Leaf4	28.2.	Feb	35
Leaf5	28.2.	Feb	25

Fixed Key Date

A query based on this InfoProvider contains three structural elements; key figure restricted to January Key Figure(Jan), key figure restricted to February Key Figure(Feb), key figure restricted to the whole year key figure and the characteristic CharacteristicM in the columns.

The hierarchy has a fixed key date (15.2.) and is being used as a display hierarchy.

The InfoProvider data specified above produces the following result in the query:

Query Result

	Key Figure (Jan)	Key Figure (Feb)	Key Figure
Overall result	150	110	260
* Root	110	75	185
** Node1	60	50	110
*** Node2	10	30	40
**** Leaf1	10	30	40
*** Node4	50	20	70
**** Leaf2	20	15	35
**** Leaf3	30	5	35
** Node6	50	25	75
*** Leaf5	50	25	75
* Non-assigned	40	35	75
** Leaf 4	40	35	75

The following graphic illustrates the query results:

Temporal hierarchy join

Query1: Display hierarchy

The hierarchy has a temporal hierarchy join and is used as a display hierarchy. The key date is derived from the posted data.

The InfoProvider data specified above produces the following result in the query:

Query1 Result

	Key Figure (Jan)	Key Figure (Feb)	Key Figure
Overall result	150	110	260
* Root node	110	110	220
** Node1	60	45	105
*** Node2	30	25	55
**** Leaf1	10	25	35
**** Leaf2	20		20
*** Node4	30	20	50
**** Leaf2		15	15
**** Leaf3	30	5	35
** Node3	50	40	90
*** Node2		40	40
**** Leaf1		5	5
**** Leaf4		35	35
*** Node5	50		50
*** Leaf5	50		50
** Node6		25	25
*** Leaf5		25	25
* Non-assigned	40		40
** Leaf4	40		40

The following graphic illustrates the query results:

The value of leaf1 on 15.2. is assigned to node2 which has node1 as its predecessor because leaf1 belongs to this hierarchy path on the posting day.
The value of leaf1 on 28.2. is assigned to node2 which has node3 as its predecessor because leaf1 belongs to this hierarchy path on the posting day.
The value of leaf4 on 15.1. is assigned to the non-assigned node because leaf4 did not belong to this hierarchy on the posting day.

Query2: Restriction for key figure

A query based on the data specified above, with one structural element key figure restricted to node2 key figure(node2) and month in the columns, produces the following result:

Query2 Result

	Key Figure(Node2)
Jan	30
Feb	65
Result	95

The value for January is the total of the values (leaf1, 15.1.) and (leaf2, 15.1.).
The value for February is the total of values (leaf1, 15.2.) (leaf2, 28.2.) and (leaf4, 28.2.).

Query3: Display hierarchy with filters on nodes

If, in addition, query1 is filtered using characteristicM = node2, the following result is produced:

Query2 Result

	Key Figure (Jan)	Key Figure (Feb)	Key Figure
Overall result	30	65	95
* Node2	30	25	55
** Leaf1	10	25	35
** Leaf2	20		20
* Node2		40	40
** Leaf1		5	5
** Leaf4		35	35

Note

To use a temporal hierarchy join in the definition of a query for the presentation or as a filter, proceed as follows in BEx Query Designer: You are in the Select Hierarchy dialog box (see Selecting Hierarchies). Under Select Hierarchy Date, select Of Data, Derivation Type. Use F4 to select the required key date deviation type. The key date deviation types that were set in transaction RSTHJTMAINT and are possible in this context are displayed in F4 input help.