Skip Headers

Oracle9i OLAP User's Guide
Release 2 (9.2)

Part Number A95295-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

CWM2_OLAP_AW_ACCESS , 5 of 9


Specifying the Source and Target Objects

A delimited text string specifies multidimensional source objects in the analytic workspace and maps them to target columns in a relational view. You can supply this delimited text string either in files (as described in "CreateAWAccessStructures_FR Procedure") or directly in the command line (as described in "CreateAWAccessStructures Procedure").

Each source and target object is defined by a keyword followed by one or more values. Two colons (::) delimit the keywords and values. In the following example, MEASURE is a keyword, and sales and costs are the names of measures in the analytic workspace.

MEASURE::sales::costs

When you provide mapping information in a text file, each keyword begins a new line:

MEASURE::sales::costs
MEASURE COLUMNS::sales::costs

When you provide mapping information directly in the command line, a semicolon delimits the individual object specifications:

MEASURE::sales::costs;MEASURE COLUMNS::sales::costs

Each call to one of these procedures generates a single view. For example, to create one fact view and three dimension tables, you must execute the procedure four times. If you are supplying input files for the mapping information, then you must create four files, one for each view that you want to generate.


Note:

If you are creating views that will be accessed directly using SQL, then you can structure the views in whatever way is appropriate for your application.

If you will use the views to create OLAP Catalog metadata, then you must create a star schema with measure views and dimension views as described in this chapter.


Defining Dimension Views

For a star schema, you must define a dimension view for every hierarchy of every dimension of the fact view. A flat dimension, that is, one with no hierarchies, requires a single dimension view.

Since each call to one of these procedures generates a single view, you must create a separate mapping file for each one. For example, if the geography dimension has two hierarchies, then you need to create two mapping files.

Table 15-1 describes the keywords that identify the source data in an analytic workspace that will be used to create a dimension view. Table 15-2 describes the keywords that specify the target columns in the generated database dimension view. Enter these keywords in the same input file. Some of these keywords are required and others are optional. DIMENSION must be the first keyword.

Table 15-1 Keywords for Defining the Source Data for a Dimension View
Keyword Description

DIMENSION

A workspace DIMENSION, which dimensions the measures in the fact view, as described in "Dimensions". This keyword must appear first. Required.

HIERARCHY

A workspace RELATION that identifies the parent value for each dimension value in the hierarchy, as described in "Hierarchies". Required for hierarchical dimensions.

IN HIERARCHY

A workspace VARIABLE with a BOOLEAN data type that identifies whether or not each value of DIMENSION is included in the hierarchy, as described in "In Hierarchy Variables". Required only when some dimension members are omitted from the hierarchy.

HIERARCHY DIMENSION

The workspace DIMENSION that contains the names of the hierarchies, as described in "Hierarchy Dimensions". Required only if more than one hierarchy is defined for DIMENSION.

HIERARCHY DIMENSION VALUE

The dimension member in the HIERARCHY DIMENSION object that identifies the hierarchy. Required only if HIERARCHY DIMENSION is specified.

GID

A workspace VARIABLE with an INTEGER data type that identifies the hierarchy level of each dimension value. Use the GROUPINGID command to generate this variable, as described in "Grouping IDs". Improves performance of the OLAP API.

PARENT GID

Identifies the grouping IDs of the parent values. You can define a formula that generates the parent values from the GID variable, as described in "Grouping IDs".

ATTRIBUTES

One or more workspace VARIABLE objects that contain descriptive information about the dimension members, as described in "Attributes". Optional.

COLUMN LEVEL DIMENSION

A workspace DIMENSION with an INTEGER data type, which contains a value for each level in the hierarchy, as described in "Level Dimensions". It dimensions the COLUMN LEVEL RELATION object. Required for hierarchical dimensions.

COLUMN LEVEL RELATION

A workspace RELATION with a value for each level in the hierarchy. Use the HIERHEIGHT command in the OLAP DML to generate the values of this relation, as described in "Hierarchy Height". Required for hierarchical dimensions.


Important:

When listing the keywords for the target columns, you must list DIMENSION COLUMN, PARENT COLUMN, and GID COLUMN in that order. All column names must comply with Oracle requirements.


Table 15-2 Keywords for Defining the Target Columns for a Dimension View
Keyword Description

DIMENSION COLUMN

A valid name for the column that will represent dimension values from DIMENSION. Required.

PARENT COLUMN

A valid name for the column that will represent the parent value for each dimension value. Required for hierarchical dimensions.

GID COLUMN

A valid name for the column that will represent the grouping IDs from GID. Required for hierarchical dimensions.

PARENT GID COLUMN

A valid name for the column that will represent the grouping IDs from PARENT GID. Optional.

DIMENSION DATATYPES

The data types of the previously specified columns, as follows:

First value: DIMENSION COLUMN

Second value: PARENT COLUMN

Third value: GID COLUMN

Fourth value: PARENT GID

Required for each defined column.

For information about compatible workspace and database data types, search for the SQL FETCH command in the Oracle9i OLAP DML Reference help.

LEVEL COLUMNS

Valid names for the columns that represent level values. You must identify a column for each value in COLUMN LEVEL DIMENSION. For example, if the level dimension has four values, then you must define four columns. Required for hierarchical dimensions.

LEVEL DATATYPES

The data types of the columns listed in LEVEL COLUMNS. The data types must correspond in number and order to the columns listed in LEVEL COLUMNS, that is, the first column will be defined with the first data type, the second column will be defined with the second data type, and so forth. Required when LEVEL COLUMNS is specified.

ATTRIBUTE COLUMNS

Valid names for the columns that represent attribute values. The columns must correspond in number and order to the variables listed in ATTRIBUTES, that is, the first column will represent the first variable, the second column will represent the second variable, and so forth. Optional.

ATTRIBUTE DATATYPES

The data type of the columns listed in ATTRIBUTE COLUMNS. The data types must correspond in number and order to the columns listed in ATTRIBUTE COLUMNS, that is, the first column will be defined with the first data type, the second column will be defined with the second data type, and so forth. Required when ATTRIBUTE COLUMNS is specified.

Defining Fact Views

You can create a single group of views for several measures if they are dimensioned identically, as described in "Measures".

For the OLAP API, you need to create one view for each combination of dimension hierarchies. The views must contain columns for the measures themselves and the dimension values that qualify this data. You can copy statements from the input files for dimension views into the input files for fact views.

Create input files (or text strings) that includes the following keywords:

Table 15-3 lists the keywords that map workspace measures to columns in a fact view.

Table 15-3 Additional Keywords for Defining a Fact View
Keyword Description

MEASURE

One or more workspace VARIABLE, RELATION, or FORMULA objects that are dimensioned identically, as described in "Measures". The MEASURE keyword must appear before the other keywords listed in this table.

MEASURE COLUMNS

The names for the columns in the fact view where the data from MEASURE will be represented. You can specify any valid column name. The columns correspond in number and order to the workspace objects listed in MEASURE, that is, the first measure will be mapped to the first column, the second measure to the second column, and so forth.

MEASURE DATATYPES

The data types of the columns in the fact view. The data types must correspond in number and order to the columns listed in MEASURE COLUMNS, that is, the first column will be defined with the first data type, the second column will be defined with the second data type, and so forth.

For a comparison between workspace data types and database data types, search for the SQL FETCH command in the Oracle9i OLAP DML Reference help.


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 2001, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback