Hierarchies determine how data is consolidated and navigated. For example, many businesses summarize their data monthly, roll up the monthly data to get quarterly figures, and then roll up the quarterly data to get annual figures. Some businesses may also summarize data by zip code, and then by city, state, and country.
In Essbase Studio, hierarchies are the used to model dimensions. Any dimension can be used to consolidate data for reporting purposes. Levels in the hierarchy can come from different source database tables that are joined in either of these ways:
By physical joins in the relational data source.
By joins created in the minischema in Essbase Studio.
For example, a hierarchy for a MARKET dimension might look like the hierarchy shown below.
MARKET
REGION
STATEIn this hierarchy, REGION is a child of the hierarchy MARKET, and STATE is a child of REGION. The structure of the hierarchy carries over to the Essbase outline, where you can report on sales for individual states and consolidate state figures to report on regional sales.
The hierarchies used in a cube schema determine the structure of the resulting Essbase model, providing named structures that contain:
The hierarchical structure itself—a level-by-level sequence for consolidating data.
For example, sales totals by STATE roll up to sales totals by REGION. If CITY is a child of STATE in your hierarchy, sales totals by CITY could roll up to sales totals by STATE.
The data filters that are placed on selected metadata elements within the hierarchy—a way to select specific categories of information.
For example, you can filter the REGION column to only include information on sales in the USA.
The organizational sequence of the data—the sort sequence for a column.
For example, you can sort the MONTH column in descending sequence to see the most recent totals first.
Transformations—a way to control column data values or measures, which become Essbase member names.
For example, in a cube that does not support duplicate member names, if CITY is a child of STATE in your hierarchy, you can ensure unique member names by prefixing each CITY value with an appropriate value from the STATE column. You can then differentiate between CA_ALBANY and NY_ALBANY.
After a hierarchy is created, it can be dragged from the Metadata Navigator directly into the Cube Schema Wizard. Further, hierarchies can be reused in any number of cube schemas. By creating different hierarchies, you can customize a cube schema for each user group. For example, assume that you use hierarchical filters in the hierarchies that you provide to users from a specific corporate division. With such hierarchies, the users can more easily create cube schemas that contain only the data relevant to their specific division.
Essbase Studio supports standard, measure, and calendar hierarchies. See the following sections for information on creating hierarchies: