Preliminary Steps

Most applications require the data to be presented in a specific format. You must know the requirements of your application in order to construct a call to OLAP_TABLE that returns a result set that complies with those requirements.

In addition, you need to gather information about the data containers in the analytic workspace and decide how you are going to map them to the columns of a relational view. These are the steps you might take:

1. Identify the measures that you want to make available to applications.
2. Identify the dimensions (including composite dimensions) of the measures.
3. For hierarchical dimensions, identify the objects that support the hierarchy.
4. Identify the dimension attributes, which are data containers that provide additional information about the dimensions.
5. If you plan to create OLAP catalog metadata, generate the additional data containers that are needed by the Java-based OLAP API.

Following are descriptions of these data containers.

Measures

Measures are VARIABLE, FORMULA, or RELATION containers with a numeric data type. If you are creating views for a star schema, you will experience the best performance and data retrieval if the measures represented in a single fact view have the exact same dimensions listed in the exact same order.

For example, the following variables compose the same cube and are dimensioned identically.

DEFINE ANALYTIC_CUBE_F.COSTS VARIABLE DECIMAL <ANALYTIC_CUBE_COMPOSITE <CHANNEL GEOGRAPHY 
PRODUCT TIME>>

DEFINE ANALYTIC_CUBE_F.SALES VARIABLE DECIMAL <ANALYTIC_CUBE_COMPOSITE <CHANNEL GEOGRAPHY 
PRODUCT TIME>>

You can combine these variables with formulas derived from them. Although formulas do not use composites, they are defined with the same dimensions in the same order as their source variables. For example, the following command creates a formula named ANALYTIC_CUBE_PROFIT, which is calculated by subtracting ANALYTIC_CUBE_F.COSTS from ANALYTIC_CUBE_F.SALES.

->DEFINE analytic_cube_profit FORMULA analytic_cube_f.sales - analytic_cube_f.costs

The resulting formula is dimensioned the same as the source variables, but without the composite.

->DESCRIBE analytic_cube_profit

DEFINE ANALYTIC_CUBE_PROFIT FORMULA DECIMAL <CHANNEL GEOGRAPHY PRODUCT TIME>
EQ analytic_cube_f.sales - analytic_cube_f.costs

You can also specify formulas within the MEASURE clause of the OLAP_TABLE function.

Dimensions

If a measure is sparse, then it is probably dimensioned by a composite or a conjoint dimension. The definition of a measure identifies its dimensions.

Hierarchies

Dimensions that contain members at all levels of a hierarchy are supported by several workspace objects that define the hierarchy: hierarchy dimensions, hierarchy relations, level dimensions, level relations, and "in hierarchy" variables.

A flat dimension (that is, one without a hierarchy, or one in which all members are at the same level of a hierarchy) does not require these supporting objects.

Hierarchy Dimensions

When a dimension has more than one hierarchy, then a hierarchy dimension is used to identify them. The members of the hierarchy dimension are the names of the hierarchies.

The following example shows the hierarchy dimension for the GEOGRAPHY dimension.

->DESCRIBE geography_hierlist

DEFINE GEOGRAPHY_HIERLIST DIMENSION TEXT

->REPORT W 25 geography_hierlist

GEOGRAPHY_HIERLIST
------------------
STANDARD
CONSOLIDATED

Hierarchy Relations

A self-relation identifies the parent of each dimension member. This type of relation is often called a parent relation. In the following example, the GEOGRAPHY dimension has a parent relation named GEOGRAPHY_PARENTREL.

GEOGRAPHY_HIERLIST also dimensions the parent relation. GEOGRAPHY has two hierarchies, STANDARD and CONSOLIDATED, which are the dimension members of GEOGRAPHY_HIERLIST.

->DESCRIBE geography_member_parentrel

DEFINE GEOGRAPHY_MEMBER_PARENTREL RELATION GEOGRAPHY <GEOGRAPHY 
GEOGRAPHY_HIERLIST>

->LIMIT geography TO 'L4.KUALALUMPUR'
->LIMIT geography ADD ANCESTORS USING geography_member_parentrel
->REPORT W 16 DOWN geography W 20 geography_member_parentrel


                 -------GEOGRAPHY_MEMBER_PARENTREL--------
                 -----------GEOGRAPHY_HIERLIST------------
GEOGRAPHY             STANDARD           CONSOLIDATED
---------------- -------------------- --------------------
L4.KUALALUMPUR   L3.MALAYSIA          L6.MALAYSIA
L3.MALAYSIA      L2.ASIA              NA
L6.MALAYSIA      NA                   L5.ASIA
L2.ASIA          L1.WORLD             NA
L5.ASIA          NA                   NA
L1.WORLD         NA                   NA

From this example, you can see that levels L1, L2, and L3 are in the STANDARD hierarchy, and levels L5 and L6 are in the CONSOLIDATED hierarchy. Malaysia and Asia are each represented by two dimension members, one for each hierarchy.

Level Dimensions

The levels of a dimension hierarchy are defined by the members of a level dimension. This dimension has a TEXT data type so that the members can have descriptive names. For example, GEOGRAPHY_LEVELLIST is the level dimension for GEOGRAPHY.

->DESCRIBE geography_levellist
DEFINE GEOGRAPHY_LEVELLIST DIMENSION TEXT

Six levels are defined for the two GEOGRAPHY hierarchies.

->REPORT geography_levellist

GEOGRAPHY_LEVELLIST
-------------------
L4
L3
L2
L1
L6
L5

In-Hierarchy Variables

If a hierarchical dimension contains members that are excluded from a hierarchy, then a boolean variable is used to identify whether a dimension member is in the hierarchy (YES) or not in the hierarchy (NO or NA). If all the members of a dimension are included in the hierarchy (which is typically the case when there is only one hierarchy), then this boolean dimension is not required because there is no ambiguity. However, if a dimension member is part of one hierarchy but excluded from another (which is typically the case when there are multiple hierarchies) an NA value in the hierarchy relation is ambiguous. It can mean either that the member is at the top level of the hierarchy and therefore has no parent, or that it is excluded from the hierarchy.

The following example shows an in-hierarchy variable named GEOGRAPHY_INHIERARCHY defined for the GEOGRAPHY dimension, which has two hierarchies, STANDARD and CONSOLIDATED.

->DESCRIBE geography_member_inhier
DEFINE GEOGRAPHY_MEMBER_INHIER VARIABLE BOOLEAN <GEOGRAPHY GEOGRAPHY_HIERLIST>

->REPORT DOWN geography W 12 geography_member_inhier

               -GEOGRAPHY_MEMBER_INHIER-
               --- GEOGRAPHY_HIERLIST---
GEOGRAPHY        STANDARD   CONSOLIDATED
-------------- ------------ ------------
L4.KUALALUMPUR          yes          yes
L3.MALAYSIA             yes           NA
L6.MALAYSIA              NA          yes
L2.ASIA                 yes           NA
L5.ASIA                  NA          yes
L1.WORLD                yes           NA

Grouping IDs

Grouping IDs identify the depth of a dimension member in the hierarchy. You can create a GID variable manually by using the GROUPINGID command in the OLAP DML. Grouping IDs are used by the OLAP API to improve performance.

->DESCRIBE geography_member_gid

DEFINE GEOGRAPHY_MEMBER_GID VARIABLE INTEGER <GEOGRAPHY GEOGRAPHY_HIERLIST>

->REPORT DOWN geography W 12 geography_member_gid


               --GEOGRAPHY_MEMBER_GID---
               ---GEOGRAPHY_HIERLIST----
GEOGRAPHY        STANDARD   CONSOLIDATED
-------------- ------------ ------------
L4.KUALALUMPUR            0            0
L3.MALAYSIA               1           NA
L6.MALAYSIA              NA            1
L2.ASIA                   3           NA
L5.ASIA                  NA            3
L1.WORLD                  7           NA

Parent Grouping IDs

Parent grouping IDs provide the GID value of the parent of each dimension member. OLAP_TABLE calculates the parent grouping IDs from the member grouping IDs. Thus, you do not need to define the parent GIDs in an object in the analytic workspace. However, you do need to specify the PARENTGID clause so that OLAP_TABLE will generate them.

This information is used by the OLAP API to improve performance. If you specify a parent relation, then you also need to specify a parent GID.

Family Relations

A family relation is used when generating a view in rollup form, that is, a view in which a multiple-column key identifies the full parentage of each dimension value. Each column in the key contains values at one level of the dimension hierarchy. A family relation formats the information in this way in the analytic workspace.

You can create a family relation manually by defining a relation and populating it using the HIERHEIGHT command in the OLAP DML.

The following is the definition of the family relation for GEOGRAPHY.

->DESCRIBE geography_member_familyrel

DEFINE GEOGRAPHY_MEMBER_FAMILYREL RELATION GEOGRAPHY <GEOGRAPHY 
GEOGRAPHY_LEVELLIST GEOGRAPHY_HIERLIST>

->LIMIT geography_levellist TO FIRST 4
->REPORT W 12 DOWN geography W 16 geography_member_familyrel


GEOGRAPHY.HIERLIST: STANDARD
             ---------------------GEOGRAPHY_MEMBER_FAMILYREL--------------------
             -------------------------GEOGRAPHY_LEVELLIST-----------------------
GEOGR
APHY                L4               L3               L2               L1
------------ ---------------- ---------------- ---------------- ----------------
L4.ADELAIDE  L4.ADELAIDE      L3.CENTRAL.AUST  L2.AUSTRALIA     L1.WORLD
L4.AMSTERDAM L4.AMSTERDAM     L3.NETHERLANDS   L2.EUROPE        L1.WORLD
L4.ATHENS    L4.ATHENS        L3.GREECE        L2.EUROPE        L1.WORLD
L4.BANGKOK   L4.BANGKOK       L3.THAILAND      L2.ASIA          L1.WORLD
                                 .
                                 .
                                 .

Attributes

Attributes are typically text variables or relations that provide descriptive information about dimension members, and are useful for displaying the data. Dimension members are usually very cryptic, and are more useful for uniquely identifying the data internally than for labeling the data for users in a table or graph. For this reason, dimensions often have one or more variables that provide descriptions of the dimension members.

Attributes can also provide other types of information and be other data types, like the end date and time span attributes for a time dimension. The following example shows attributes for the TIME dimension.

->LIMIT time_hierlist TO 'STANDARD'
->REPORT DOWN time time_short.description time_end_date time_time_span


LANGUAGELIST: AMERICAN_AMERICA
               ----------TIME_HIERLIST---------
               ------------STANDARD------------
               TIME_SHORT  
               .DESCRIPTI  TIME_END_ TIME_TIME_
TIME           ON          DATE      SPAN
-------------- ---------- ---------- ----------
L1.1996        1996       31DEC96        366.00
L1.1997        1997       31MAY97        151.00
L2.Q1.96       Q1.96      31MAR96         91.00
L2.Q1.97       Q1.97      31MAR97         90.00
L2.Q2.96       Q2.96      30JUN96         91.00
          .
          .
          .