A recursive hierarchy contains members that are based on the contents of the two columns of a parent-child relationship.
Note: | If a source database contains recursive tables, you must create a self-join between the parent and child columns to access related information and to consolidate the data properly. This can be done in the minischema in Essbase Studio. See Adding or Editing Joins in a Minischema for more information. |
In the TBC sample database, the Measures table contains two columns, “PARENT” and “CHILD”, which have the parent-child relationship necessary to build a recursive hierarchy. The relationship between the rows of the PARENT and CHILD columns are illustrated below:
PARENT CHILD Measures Profit Profit Margin Margin Sales Margin COGS Profit Total Expenses Total Expenses Marketing Total Expenses Payroll Total Expenses Misc Measures Inventory Inventory Opening Inventory Inventory Additions Inventory Ending Inventory Measures Ratios Ratios Margin % Ratios Profit % Ratios Profit per Ounce
Using the PARENT and CHILD columns above, an Essbase outline would be created as shown:
Measures
Profit
Margin
Sales
COGS
Total Expenses
Marketing
Payroll
MISC
Inventory
Opening Inventory
Additions
Ending Inventory
Ratios
Margin %
Profit %
Profit per Ounce
Remember, to build a recursive hierarchy properly, you must create a self join between the parent and child columns in the recursive table. Using the TBC sample, create a self join between the PARENT and CHILD columns of the Measures table.
Figure 11, Recursive (parent-child) hierarchy is an example of the recursive hierarchy created in Essbase Studio using the PARENT and CHILD columns in the Measures table of the TBC sample database:
