CWM2_OLAP_PC_TRANSFORM , 3 of 3

CREATE_SCRIPT Procedure

This procedure generates a script that converts a parent/child dimension table to an embedded-total dimension table.

Syntax

CREATE_SCRIPT (
          directory          IN   VARCHAR2,
          schema             IN   VARCHAR2,
          pc_table           IN   VARCHAR2,
          pc_parent          IN   VARCHAR2,
          pc_child           IN   VARCHAR2,
          t_table            IN   VARCHAR2,
          t_tablespace       IN   VARCHAR2,
          pc_root            IN   VARCHAR2   DEFAULT,
          levels             IN   VARCHAR2   DEFAULT,
          levels_list        IN   VARCHAR2   DEFAULT,
          short_description  IN   VARCHAR2   DEFAULT,
          long_description   IN   VARCHAR2   DEFAULT,
          attributes_list    IN   VARCHAR2    DEFAULT);

Parameters

Table 16-2 CREATE_SCRIPT Procedure Parameters

Parameter	Description
`directory`	Full path of a directory where the procedures will put the generated script.
`schema`	Schema containing the parent-child table. This schema will also contain the embedded-total table.
`pc_table`	Parent-child table.
`pc_parent`	Column containing the parent in the parent-child table.
`pc_child`	Column containing the child in the parent-child table.
`t_table`	Embedded total table.
`t_tablespace`	Tablespace where the embedded total table will be created.
`pc_root`	One of the following: `null -` Root of parent-child table hierarchy is identified by `null` in the parent column. (default) `condition` - Root of the hierarchy in the parent-child table is a condition, for example: `'long_des = "Northeast Region"'`
`levels`	One of the following: `null -` Compute the number of levels in the parent-child table and create the embedded-total table with that number of levels. (default) `number` - The number of levels for the embedded-total table.
`levels_list`	One of the following: `null` - Generate the embedded-total table column names based on the parent-child column and level number. (default) `list` - A comma-separated list of column names for the embedded-total table.
`short_description`	One of the following: `null` - No short description in the parent-child table. Use the highest level non-null child in row of embedded-total table as short description. (default) `column name` - Name of the column in the parent-child table that contains the short description. Copy the short description column from the parent-child table to the embedded-total table.
`long_description`	One of the following: `null` - No long description in the parent-child table. Use the short description in the embedded-total table as the long description. (default) `column name` - Name of the column in the parent-child table that contains the long description. Copy the long description column from the parent-child table to the embedded-total table
`attributes_list`	One of the following: `null` - No attributes. (default) `list` - A comma-separated list of attribute columns in the parent-child table. Copy these columns to the embedded-total table.

Usage Notes

The CREATE_SCRIPT procedure must have write access to the directory where the script will reside. The database must be able to execute scripts in this directory.
The script identifies the root of a parent-child hierarchy by a null in the pc_parent parameter. If the root is identified some other way, you may need to edit the generated script and change the way that the root is identified.
If a table with the same name as the embedded-total table already exists, the script will delete it.
Calculating the number of levels in the parent-child table is a relatively expensive operation. You can reduce the time required to generate the script by specifying the number of levels to be processed in the et_levels parameter.