| Oracle® OLAP Reference 10g Release 1 (10.1) Part Number B10334-02 |
|
|
View PDF |
| Oracle® OLAP Reference 10g Release 1 (10.1) Part Number B10334-02 |
|
|
View PDF |
The CWM2_OLAP_PC_TRANSFORM package contains a procedure for generating a SQL script that creates a solved, level-based dimension table from a parent-child dimension table. .
After running the script and creating the new table, you can define OLAP metadata so that OLAP API applications can access the dimension.
|
See Also:
|
This chapter discusses the following topics:
Before running the CWM2_OLAP_PC_TRANSFORM.CREATE_SCRIPT procedure, ensure that the RDBMS is enabled to write to a file. To specify a directory, you can use either a directory object to which your user ID has been granted the appropriate access, or a path set by the UTL_FILE_DIR initialization parameter for the instance.
A parent-child dimension table must exist and be accessible to the CWM2_OLAP_PC_TRANSFORM.CREATE_SCRIPT procedure.
A parent-child dimension table is one in which the hierarchical relationships are defined by a parent column and a child column. Since the hierarchy is defined by the relationship between the values within two columns, a parent-child dimension is sometimes referred to as having a value-based hierarchy.
Sample Parent-Child Dimension Table Columns
The following example illustrates the relationships between the values in the child and parent columns. A description column, which is an attribute of the child, is also included.
CHILD PARENT DESCRIPTION ------------- ----------- ------------- World World USA World United States of America Northeast USA North East Region Southeast USA South East Region MA Northeast Massachusetts Boston MA Boston, MA Burlington MA Burlington, MA NY Northeast New York State New York City NY New York, NY GA Southeast Georgia Atlanta GA Atlanta,GA Canada World Canada
If you choose to create OLAP Catalog metadata to represent a parent-child dimension, set the solved_code for the hierarchy to 'SOLVED VALUE-BASED', as described in Chapter 12, " CWM2_OLAP_HIERARCHY ".
|
Note: You can create OLAP Catalog metadata to represent value-based hierarchies, but this type of hierarchy is not accessible to applications that use the OLAP API. |
The script generated by OLAP_PC_TRANSFORM.CREATE_SCRIPT creates a table that stores the values from the parent-child table in levels.
The resulting level-based dimension table includes the full lineage of every level value in every row. This type of dimension table is solved, because the fact table related to this dimension includes embedded totals for all level combinations.
If you want to enable parent-child dimension tables for access by the OLAP API, you must convert them to solved, level-based dimension tables. The OLAP API requires that dimensions have levels and that they include a GID (Grouping ID) column and an Embedded Total (ET) key column. GIDs and ET key columns are described in Example: Creating a Solved, Level-Based Dimension Table.
The following example illustrates how the parent-child relationships in would be represented as solved levels.
TOT_GEOG COUNTRY REGION STATE CITY DESCRIPTION -------- ------- --------- ----- ------- ---------------------- World USA Northeast MA Boston Boston, MA World USA Northeast MA Burlington Burlington, MA World USA Northeast NY New York City New York, NY World USA Southeast GA Atlanta Atlanta, GA World USA Northeast MA Massachusetts World USA Northeast NY New York State World USA Southeast GA Georgia World USA Northeast North East Region World USA Southeast South East Region World USA United States of America World Canada Canada World World
When creating OLAP Catalog metadata to represent a solved, level-based dimension hierarchy, specify a solved_code of 'SOLVED LEVEL-BASED', as described in Chapter 12, " CWM2_OLAP_HIERARCHY ".
Assuming a parent-child dimension table with the PARENT and CHILD columns shown in , 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
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
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.
Table 17-1 CWM2_OLAP_PC_TRANSFORM
| Subprogram | Description |
|---|---|
| CREATE_SCRIPT Procedure |
Generates a script that converts a parent-child table to an embedded-total table. |
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 17-2 CREATE_SCRIPT Procedure Parameters
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.
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|