Release 2 (9.2)
Part Number A95295-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i OLAP User's Guide Release 2 (9.2) Part Number A95295-01 |
|
CWM2_OLAP_AW_ACCESS , 4 of 9
The CWM2_OLAP_AW_ACCESS package can expose various types of analytic workspace objects in relational views. You will need to gather information about these objects and decide how you are going to map them to the columns of a relational view. These are the steps you might take:
Following are descriptions of these objects.
Measures are VARIABLE, FORMULA, or RELATION objects with a numeric data type. For the best performance and data retrieval, the measures represented in a single fact view should have the exact same dimensions listed in the exact same order.
For example, these two variables can be represented very effectively in the same fact view:
DEFINE SALES VARIABLE SHORT <GEOGRAPHY PRODUCT CHANNEL TIME> DEFINE COSTS VARIABLE SHORT <GEOGRAPHY PRODUCT CHANNEL TIME>
You can combine them with a variable that uses a composite, as long as the dimensions are listed in the same order:
DEFINE UNITS VARIABLE SHORT GEOGRAPHY PRODUCT SPARSE <CHANNEL TIME>
However, you should not combine the sales and costs variables with variables dimensioned in any of the following ways:
<GEOGRAPHY TIME> Fewer dimensions results in repetitive data in the view.
<TIME GEOGRAPHY PRODUCT CHANNEL> Different order of dimensions means that the data is stored differently and runtime performance will suffer as a result.
<TIME SPARSE <GEOGRAPHY PRODUCT CHANNEL>> Different order of dimensions means that the data is stored differently and runtime performance will suffer as a result.
If the measure is sparse, then it is probably dimensioned by a composite or a conjoint dimension. If it is dimensioned by a composite, then you will need to identify the base dimensions. If it is dimensioned by a conjoint, then you will need to identify the conjoint dimension.
A flat dimension (that is, one without a hierarchy or one in which all members are at the same level of a hierarchy) requires no supporting objects. However, a hierarchical dimension requires other objects to identify the relationships among members, as described in "Hierarchies".
Hierarchical dimensions are supported by several workspace objects: hierarchy dimensions, parent-child relations, level dimensions, and "in hierarchy" variables.
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.
DEFINE GEOGRAPHY.HIERARCHIES DIMENSION TEXT LD Hierarchy dimension for GEOGRAPHY REPORT geography.hierarchies GEOGRAPHY.HIERARCHIES ---------------------- STANDARD CONSOLIDATED
A parent- or self-relation is typically used to identify the parent of each dimension member. In the following example, the geography dimension has a parent relation named geography.parentrel.
Because geography has two hierarchies, STANDARD and CONSOLIDATED, a hierarchy dimension named geography.hierarchies also dimensions the parent relation. The names of the hierarchies are the members of geography.hierarchies.
DEFINE GEOGRAPHY.PARENTREL RELATION GEOGRAPHY <GEOGRAPHY GEOGRAPHY.HIERARCHIES> LD Parent-child relation for GEOGRAPHY REPORT DOWN geography geography.parentrel ---GEOGRAPHY.PARENTREL--- --GEOGRAPHY.HIERARCHIES-- GEOGRAPHY STANDARD CONSOLIDATED -------------- ------------ ------------ WORLD NA NA AMERICAS WORLD NA CANADA AMERICAS AMERICAS TORONTO CANADA NA MONTREAL CANADA NA OTTAWA CANADA NA
From this example, you can see that in the STANDARD hierarchy, CANADA is the parent of TORONTO, MONTREAL, and OTTAWA, AMERICAS is the parent of CANADA, and WORLD is the parent of AMERICAS.
The levels of a dimension hierarchy are defined by the members of a dimension. This dimension often has a TEXT data type so that the members have descriptive names. For example, the geography.leveldim dimension might list geography levels such as CITY, STATE, COUNTRY, REGION, and so forth.
However, if you are providing support for the OLAP API, you will need to have an INTEGER dimension for the levels.
The following is the workspace definition of geography.leveldim, which is an dimension with an INTEGER data type required by the HIERHEIGHT command.
DEFINE GEOGRAPHY.LVLDIM DIMENSION INTEGER
The content of all INTEGER dimensions is sequential numbers. In this case, the length of the dimension is important, since it indicates the number of levels.
REPORT geography.lvldim GEOGRAPHY.LVLDIM ---------------- 1 2 3 4
You can specify the length of the INTEGER dimension based on the content of a text dimension that contains the names of the levels. For example, this command sets the length of the geography.lvldim INTEGER dimension based on the number of values currently in status for the geography.leveldim TEXT dimension:
LIMIT geography TO ALL MAINTAIN geography.lvldim MERGE STATLEN(geography.leveldim)
If a hierarchical dimension contains members that are excluded from a hierarchy, then a boolean variable is needed to identify whether a dimension member is in the hierarchy (YES) or not in the hierarchy (NO or NA). Otherwise, an NA value in the parent 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. If all the members of a dimension are included in the hierarchy, then this boolean dimension is not needed because there is no ambiguity.
The following example shows a boolean variable named time.inhierarchy defined for the time dimension, which has two hierarchies, STANDARD and YTD.
DESCRIBE time.inhierarchy DEFINE TIME.INHIERARCHY VARIABLE BOOLEAN <TIME TIME.HIERARCHIES> REPORT DOWN time time.inhierarchy time.parentrel -------------------------TIME.HIERARCHIES-------------------------- ------------STANDARD------------- ---------------YTD--------------- TIME TIME.INHIERARCHY TIME.PARENTREL TIME.INHIERARCHY TIME.PARENTREL ------------ ---------------- ---------------- ---------------- ---------------- LAST.YTD no NA yes NA CURRENT.YTD no NA yes NA JAN01 yes Q1.01 yes LAST.YTD FEB01 yes Q1.01 yes LAST.YTD . . . . . . . . .Q1.02 yes 2002 no NA Q2.02 yes 2002 no NA 2001 yes NA no NA 2002 yes NA no NA
The OLAP DML contains two commands for generating objects that provide information for the OLAP API:
GROUPINGID. Populates a variable that identifies the hierarchy level of each dimension value.HIERHEIGHT. Populates a relation that contains a value for each level in the hierarchy.Use these commands to generate this information for each hierarchy of each dimension. If you do not generate these objects for use in the views, then the OLAP API performs a runtime calculation using SQL functions. This slows performance.
The following is the definition of a variable that stores the results of the GROUPINGID command. It is an INTEGER variable dimensioned by the data dimension (geography) and the hierarchy dimension (geography.hierarchies).
DEFINE GEOGRAPHY.GID VARIABLE INTEGER <GEOGRAPHY GEOGRAPHY.HIERARCHIES> LD Output from GROUPINGID command
A command like the following populates geography.gid:
groupingid geography.parentrel INTO geography.gid
The contents of geography.gid specify the hierarchy level for each dimension value. These values correspond in order (but not value) to the level numbers. In this case, geography.gid identifies the four levels as 0, 1, 3, and 7.
----------GEOGRAPHY.GID---------- ------GEOGRAPHY.HIERARCHIES------ GEOGRAPHY STANDARD CONSOLIDATED ------------ ---------------- ---------------- WORLD 7 0 AMERICAS 3 3 CANADA 1 1 TORONTO 0 0 MONTREAL 0 0 OTTAWA 0 0 VANCOUVER 0 0 EDMONTON 0 0 CALGARY 0 0
Parent grouping IDs identify the GID value of the parent for each member. You can define a formula that calculates these values based on the values of the GID variable described in "Grouping IDs" above.
For dimension members at the topmost level of a hierarchy, the parent GID must be calculated separately because their parent member is NA. That equation is 2**levels-1, where levels is the number of levels in the hierarchy.
Create a formula like the following one, which calculates parent GIDs for the geography dimension.
DEFINE GEOGPARENT.GID FORMULA INTEGER <GEOGRAPHY GEOGRAPHY.HIERARCHIES> EQ IF geography.parentrel EQ NA THEN 2**STATLEN(geography.leveldim)-1 ELSE geography.gid(geography geography.parentrel) LIMIT geography.hierarchies TO 1 REPORT DOWN geography geography.gid geogparent.gid GEOGRAPHY.HIERARCHIES ------STANDARD------- GEOGRAPHY. GEOGPARENT GEOGRAPHY GID .GID -------------- ---------- ---------- WORLD 7 15 AMERICAS 3 7 CANADA 1 3 TORONTO 0 1 MONTREAL 0 1 OTTAWA 0 1 VANCOUVER 0 1 EDMONTON 0 1 CALGARY 0 1
The following is the definition of a relation that stores the results of the HIERHEIGHT command. The relation must have the dimension as a data type (geography) and be dimensioned by the same dimensions as the parent relation (geography and geography.hierarchies) and the integer dimension for levels (geography.lvldim).
DEFINE GEOGRAPHY.HIERHEIGHT RELATION GEOGRAPHY <GEOGRAPHY GEOGRAPHY.HIERARCHIES GEOGRAPHY.LVLDIM> LD Output from HIERHEIGHT command
The following command generates the content of geography.hierheight:
HIERHEIGHT geography.parentrel INTO geography.hierheight
The geography.hierheight relation identifies the hierarchy level of each dimension value and its ancestors.
REPORT DOWN geography ACROSS geography.lvldim: geography.hierheight -----------GEOGRAPHY.HIERHEIGHT------------ -------------GEOGRAPHY.LVLDIM------------- GEOGRAPHY 1 2 3 4 ------------ ---------- ---------- ---------- ---------- WORLD NA NA NA WORLD AMERICAS NA NA AMERICAS WORLD CANADA NA CANADA AMERICAS WORLD TORONTO TORONTO CANADA AMERICAS WORLD MONTREAL MONTREAL CANADA AMERICAS WORLD OTTAWA OTTAWA CANADA AMERICAS WORLD
| See Also:
Oracle9i OLAP DML Reference help for syntax and examples of the |
Attributes are text variables 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. The following example shows two variables that contain short and long descriptive names for geography members.
REPORT DOWN geography geography.shortlabel geography.longlabel GEOGRAPHY GEOGRAPHY.SHORTLABEL GEOGRAPHY.LONGLABEL -------------- ------------------------- ------------------------- WORLD World Regions of the World AMERICAS Americas Areas in the Americas CANADA Canada Canada TORONTO Toronto Toronto MONTREAL Montreal Montreal OTTAWA Ottawa Ottawa VANCOUVER Vancouver Vancouver EDMONTON Edmonton Edmonton CALGARY Calgary Calgary USA USA United States of America
|
 Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|