Example: Creating a Solved, Level-Based Dimension Table

Assuming a parent-child dimension table with the PARENT and CHILD columns shown in Figure 25-1, 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.