CWM2_OLAP_AW_ACCESS , 6 of 10

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 views, 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. The object naming conventions used by AW_CREATE are provided in the description of the source data. 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. The

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. `AW_CREATE` name: [owner_]dimension
`HIERARCHY`	A workspace `RELATION` that identifies the parent value for each dimension value in the hierarchy, as described in "Hierarchies". Required for hierarchical dimensions. `AW_CREATE` name: [owner_]dimension_`PARENTREL`
`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. `AW_CREATE` name: [owner_]dimension_`INHIERARCHY`
`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`. `AW_CREATE` name: [owner_]dimension_`HIERDIM`
`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. `AW_CREATE` name: [owner_]dimension_`GID`
`PARENT GID`	The same GID variable used with the GID keyword. Parent grouping IDs will be generated automatically from the GID variable.
`ATTRIBUTES`	One or more workspace `VARIABLE` objects that contain descriptive information about the dimension members, as described in "Attributes". Optional. `AW_CREATE` name: [owner_]dimension_attribute
`COLUMN LEVEL DIMENSION`	A workspace `DIMENSION` whose values identify the levels of a cube dimension, and which dimensions the `COLUMN LEVEL RELATION` object. Required for hierarchical dimensions. `AW_CREATE` name: [owner_]dimension_`LVLDIM`
`COLUMN LEVEL RELATION`	A workspace `RELATION` with a value for each level in the hierarchy. The `HIERHEIGHT` command in the OLAP DML generates the values of this relation. Required for hierarchical dimensions. `AW_CREATE` name: [owner_]dimension_`FAMILYREL`

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 calculated parent grouping IDs. 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:

All of the keywords in Table 15-3. They must appear in the order shown at the beginning, before keywords for the dimensions.
The following keywords from Table 15-1, " Keywords for Defining the Source Data for a Dimension View" if they appear in the input file for the dimension view: DIMENSION, HIERARCHY, IN HIERARCHY, and GID. If you wish to create a denormalized view for use by SQL applications, you can include additional keywords.
Keywords from Table 15-2, " Keywords for Defining the Target Columns for a Dimension View" that correspond to the source data keywords. The OLAP API uses the DIMENSION and GID columns in the fact views, and uses the dimension views for all other information about the dimensions. Thus, you only need to define columns for the dimension members and the GIDs.

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

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.

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.