Example: Creating a Solved, Level-Based Dimension Table

Assuming a parent-child dimension table with the PARENT and CHILD columns shown in "Parent-Child Dimensions", you could use a command like the following to represent these columns in a solved, level-based dimension table.

execute cwm2_olap_pc_transform.create_script
     ('/dat1/scripts/myscripts' ,
     'jsmith' ,
     'input_tbl' ,
     'PARENT' ,
     'CHILD' ,
     'output_tbl' ,
     'jsmith_data');

This statement creates a script in the directory /dat1/scripts/myscripts. The script will convert the parent-child table input_tbl to the solved, level-based table output_tbl. Both tables are in the jsmith_data tablespace of the jsmith schema.

You can run the resulting script with the following command.

@create_output_tbl

You can view the resulting table with the following command.

select * from output_tbl_view

The resulting table would look like this.

GID SHORT_DESC    LONG_DESC     CHILD1 CHILD2 CHILD3    CHILD4 CHILD5
--- -----------   ------------  ------ -----  --------  ------ -------
0   Boston        Boston        World  USA    Northeast  MA    Boston
0   Burlington    Burlington    World  USA    Northeast  MA    Burlington
0   New York City New York City World  USA    Northeast  NY    New York City
0   Atlanta       Atlanta       World  USA    Southeast  GA    Atlanta
1   MA            MA            World  USA    Northeast  MA
1   NY            MA            World  USA    Northeast  NY
1   GA            GA            World  USA    Southeast  GA
3   Northeast     Northeast     World  USA    Northeast
3   Southeast     Southeast     World  USA    Southeast
7   USA           USA           World  USA
7   Canada        Canada        World  Canada
15  World         World         World

Grouping ID Column

The script automatically creates a GID column, as required by the OLAP API. The GID identifies the hierarchy level associated with each row by assigning a zero to each non-null value and a one to each null value in the level columns. The resulting binary number is the value of the GID. For example, a GID of 3 is assigned to the row with the level values World, USA, Northeast, since the three highest levels are assigned zeros and the two lowest levels are assigned ones.

CHILD1 CHILD2 CHILD3    CHILD4 CHILD5
------ -----  --------  ------ -------
World  USA    Northeast
0      0      0          1      1

Embedded Total Key Column

The script automatically generates columns for long description and short description. If you have columns in the input table that contain this information, you can specify them as parameters to the CREATE_SCRIPT procedure.

If you do not specify a column for the short description, the script creates the column and populates it with the lowest-level child value represented in each row. If you do not specify a column for the long description, the script simply replicates the short description.

The ET key column required by the OLAP API is the short description column that is created by default.

Summary of CWM2_OLAP_PC_TRANSFORM Subprograms

Table 20-1 CWM2_OLAP_PC_TRANSFORM

Subprogram	Description
CREATE_SCRIPT Procedure	Generates a script that converts a parent-child table to an embedded-total table.

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,
          slb_table          IN   VARCHAR2,
          slb_tablespace     IN   VARCHAR2,
          pc_root            IN   VARCHAR2   DEFAULT  NULL,
          number_of_levels   IN   NUMBER     DEFAULT  NULL,
          level_names        IN   VARCHAR2   DEFAULT  NULL,
          short_description  IN   VARCHAR2   DEFAULT  NULL,
          long_description   IN   VARCHAR2   DEFAULT  NULL,
          attribute_names    IN   VARCHAR2   DEFAULT  NULL);

Parameters

Table 20-2 CREATE_SCRIPT Procedure Parameters

Parameter	Description
`directory`	The directory that will contain the generated script. This may be either a directory object or a directory path specified in the `UTL_FILE_DIR` initialization parameter.
`schema`	Schema containing the parent-child table. This schema will also contain the solved, level-based table.
`pc_table`	Name of the parent-child table.
`pc_parent`	Name of the column in `pc_table` that contains the parent values.
`pc_child`	Name of the column in `pc_table` that contains the child values.
`slb_table`	Name of the solved, level-based table that will be created.
`slb_tablespace`	Name of the tablespace where the solved, level-based table will be created.
`pc_root`	One of the following: `null-` Root of the parent-child hierarchy is identified by `null` in the parent column. (default) `condition` - Root of the parent-child hierarchy is a condition, for example: `'long_des = "All Countries"'`
`number_of_levels`	One of the following: `null` - The number of levels in the solved, level-based table will be all the levels of the hierarchy in the parent-child table. (default) `number` - The number of levels to be created in the solved, level-based table.
`level_names`	One of the following: `null` - The column names in the solved, level-based table will be the source child column name concatenated with the level number. (default) `list` - A comma-delimited list of column names for the solved, level-based table.
`short_description`	One of the following: `null` - There is no short description in the parent-child table. The highest level non-null child value in each row of the solved, level-based table will be used as the short description. This constitutes the ET key column (default) `column name` - Name of the column in the parent-child table that contains the short description. This column will be copied from the parent-child table to the solved, level-based table.
`long_description`	One of the following: `null` - There is no long description in the parent-child table. The short description will be used. (default) `column name` - Name of the column in the parent-child table that contains the long description. This column will be copied from the parent-child table to the solved, level-based table.
`attribute_names`	One of the following: `null` - There are no attributes in the parent-child table. (default) `list` - A comma-delimited list of attribute columns in the parent-child table. These columns will be copied from the parent-child table to the solved, level-based table

Usage Notes

If a table with the same name as the solved, level-based table already exists, the script will delete it.
You can reduce the time required to generate the script by specifying the number of levels in the number_of_levels parameter. If you do not specify a value for this parameter, the CREATE_SCRIPT procedure calculates all the levels from the parent-child table.
To define additional characteristics of the solved, level-based table, you can modify the generated script file before executing it.