Measure hierarchies are used by Essbase Studio Server to create the accounts dimension in the Essbase model. If your data contains more than one measure (data value), then define a measure hierarchy and add all measures to that hierarchy
Essbase Studio has the following guidelines for measure hierarchies.
Oracle recommends that a measure hierarchy be created from a fact table. The measure hierarchy may also include user-defined members. However, a measure hierarchy can also be created from dimension tables, and it may also include user-defined members.
An accounts dimension that is created from a measure hierarchy built from a fact table is referred to as “accounts from fact.”
In a cube schema, when you use a measure hierarchy built from a fact table, the column headers from the fact table become members in the accounts dimension in the subsequent Essbase model.
Using the TBC sample database, an example of a measure hierarchy built from the fact table, SALESFACT, would be:
Profit (user-defined member) SALES (from SALESFACT) COGS (from SALESFACT)
If a column type in a fact table is text, Essbase Studio treats it as a text measure and automatically builds a text list.
An accounts dimension that is created from a hierarchy built from dimension tables, combined with one or more loose measures, is referred to as “accounts not from fact.” (Loose measures are individual measures that are not organized into a hierarchy.)
In this case, a designated column in one of the dimension tables contains rows that represent the column headings for facts. This column is joined to a column in a separate dimension table, which contains the values for those facts. It is from these two dimension tables that the accounts dimension (not from fact) is constructed when the model is created.
Using the TBC sample database, an example of a measure hierarchy built from non-fact tables, MEASURES and SALES, would be:
PARENT (from MEASURES) CHILD (from MEASURES)
In the MEASURES table, the values in the CHILD column correspond to the values in the MEASURESID column, which is joined to the MEASURESID column in the SALES table, which provides the actual data values from the AMOUNT column of the SALES table.
When you use an “accounts not from fact” measure hierarchy in a cube schema, you drag the hierarchy to the Hierarchies field of the Cube Schema Wizard. You must also drag at least one loose measure to the Measures/Measures Hierarchy field.
Essbase Studio maps the value of the loose measures to the members in the “accounts not from fact” measure hierarchy.
If a column type in a fact table is text, Essbase Studio treats it as a text measure and automatically builds a text list.
In summary, although Oracle recommends that measure hierarchies be created from columns in the fact table, Essbase Studio does not prevent you from creating measure hierarchies from dimension tables or from user-defined members. If the column header type is text, Essbase Studio will build a text list.
Note: | Text lists larger than 1024 KB can cause deployments to abort. |