Oracle9i OLAP User's Guide Release 2 (9.2) Part Number A95295-01 |
|
Creating OLAP Catalog Metadata, 6 of 6
OLAP Catalog Release 2 includes a number of related PL/SQL packages for creating CWM2
metadata. These packages contain stored procedures that can create metadata for a wide variety of data warehouses. For example, you can create metadata for parent/child dimension tables and for data stored in analytic workspaces. Before using these packages, make sure that you have performed any required preprocessing steps, as listed in "Choosing the Right Metadata Creation Method".
The following packages contain procedures that create metadata for dimension tables:
CWM2_OLAP_DIMENSION
contains procedures for creating OLAP dimensions and providing display names and descriptions.CWM2_OLAP_HIERARCHY
contains procedures for creating OLAP hierarchies and providing display names and descriptions.CWM2_OLAP_LEVEL
contains procedures for creating levels and providing display names and descriptions.CWM2_OLAP_LEVEL_ATTRIBUTE
contains procedures for creating level attributes and providing display names and descriptions.CWM2_OLAP_DIMENSION_ATTRIBUTE
contains procedures for creating dimension attributes and providing display names and descriptions.The following packages contain procedures that create metadata for fact tables:
CWM2_OLAP_CUBE
contains procedures for creating cubes and providing cube display names and descriptions.CWM2_OLAP_MEASURE
contains procedures for creating and deleting measures and providing display names and descriptions.The following package contains procedures that create metadata for analytic workspace objects:
The following packages contain procedures that create the mapping between logical metadata entities and the warehouse structures where the data is stored:
CWM2_OLAP_TABLE_MAP
contains procedures that map metadata entities to relational fact tables and dimension tables.CWM2_OLAP_AW_MAP
contain procedures that map metadata entities to multidimensional objects in analytic workspaces.
See Also:
Part IV, "OLAP Catalog Metadata API Reference" for comprehensive syntax and descriptions of these packages. |
In the Sales History sample schema, PRODUCTS is a dimension table with the following columns:
The following PL/SQL calls create a logical metadata dimension object, PRODUCT_DIM
, for the PRODUCTS
dimension table.
--- Create the PRODUCT Dimension --- cwm2_olap_dimension.create_dimension('SH', 'PRODUCT_DIM', 'Product', 'Products', 'Product Dimension', 'Product Dimiension Values'); --- Create Dimension Attributes --- cwm2_olap_dimension_attribute.create_dimension_attribute('SH', 'PRODUCT_DIM', 'Long Description', 'Long Descriptions', 'Long Desc', 'Long Product Descriptions', true); cwm2_olap_dimension_attribute.create_dimension_attribute('SH', 'PRODUCT_DIM', 'PROD_NAME_DIM', 'Product Name', 'Prod Name', 'Product Name'); --- Create STANDARD Hierarchy --- cwm2_olap_hierarchy.create_hierarchy('SH', 'PRODUCT_DIM', 'STANDARD', 'Standard', 'Std Product', 'Standard Product Hierarchy', 'Unsolved Level-Based'); --- Create Levels --- cwm2_olap_level.create_level('SH', 'PRODUCT_DIM', 'L4', 'Product ID', 'Product Identifiers', 'Prod Key', 'Product Key'); cwm2_olap_level.create_level('SH', 'PRODUCT_DIM', 'L3', 'Product Sub-Category', 'Product Sub-Categories', 'Prod Sub-Category', 'Sub-Categories of Products'); cwm2_olap_level.create_level('SH', 'PRODUCT_DIM', 'L2', 'Product Category', 'Product Categories', 'Prod Category', 'Categories of Products'); cwm2_olap_level.create_level('SH', 'PRODUCT_DIM', 'L1', 'Total Product', 'Total Products', 'Total Prod', 'Total Product'); --- Create Level Attributes --- cwm2_olap_level_attribute.create_level_attribute('SH', 'PRODUCT_DIM', 'Long Description', 'L4', 'Long Description', 'PRODUCT_LABEL', 'L4 Long Desc', 'Long Labels for PRODUCT Identifiers', TRUE); cwm2_olap_level_attribute.create_level_attribute('SH', 'PRODUCT_DIM', 'Long Description', 'L3', 'Long Description', 'SUBCATEGORY_LABEL', 'L3 Long Desc', 'Long Labels for PRODUCT Sub-Categories', TRUE); cwm2_olap_level_attribute.create_level_attribute('SH', 'PRODUCT_DIM', 'Long Description', 'L2', 'Long Description', 'CATEGORY_LABEL', 'L2 Long Desc', 'Long Labels for PRODUCT Categories', TRUE); cwm2_olap_level_attribute.create_level_attribute('SH', 'PRODUCT_DIM', 'PROD_NAME_DIM', 'L4', 'PROD_NAME_LEV', 'Product Name', 'Product Name'); --- Add levels to hierarchies --- cwm2_olap_level.add_level_to_hierarchy('SH', 'PRODUCT_DIM', 'STANDARD', 'L4', 'L3'); cwm2_olap_level.add_level_to_hierarchy('SH', 'PRODUCT_DIM', 'STANDARD', 'L3', 'L2'); cwm2_olap_level.add_level_to_hierarchy('SH', 'PRODUCT_DIM', 'STANDARD', 'L2', 'L1'); cwm2_olap_level.add_level_to_hierarchy('SH', 'PRODUCT_DIM', 'STANDARD', 'L1'); --- Create mappings --- cwm2_olap_table_map.Map_DimTbl_HierLevel('SH', 'PRODUCT_DIM', 'STANDARD', 'L4', 'SH', 'PRODUCTS', 'PROD_ID'); cwm2_olap_table_map.Map_DimTbl_HierLevelAttr('SH', 'PRODUCT_DIM', 'Long Description', 'STANDARD', 'L4', 'Long Description', 'SH', 'PRODUCTS', 'PROD_DESC'); cwm2_olap_table_map.Map_DimTbl_HierLevelAttr('SH', 'PRODUCT_DIM', 'PROD_NAME_DIM', 'STANDARD', 'L4', 'PROD_NAME_LEV', 'SH', 'PRODUCTS', 'PROD_NAME'); cwm2_olap_table_map.Map_DimTbl_HierLevel('SH', 'PRODUCT_DIM', 'STANDARD', 'L3', 'SH', 'PRODUCTS', 'PROD_SUBCATEGORY'); cwm2_olap_table_map.Map_DimTbl_HierLevelAttr('SH', 'PRODUCT_DIM', 'Long Description', 'STANDARD', 'L3', 'Long Description', 'SH', 'PRODUCTS', 'PROD_SUBCAT_DESC'); cwm2_olap_table_map.Map_DimTbl_HierLevel('SH', 'PRODUCT_DIM', 'STANDARD', 'L2', 'SH', 'PRODUCTS', 'PROD_CATEGORY'); cwm2_olap_table_map.Map_DimTbl_HierLevelAttr('SH', 'PRODUCT_DIM', 'Long Description', 'STANDARD', 'L2', 'Long Description', 'SH', 'PRODUCTS', 'PROD_CAT_DESC'); cwm2_olap_table_map.Map_DimTbl_HierLevel('SH', 'PRODUCT_DIM', 'STANDARD', 'L1', 'SH', 'PRODUCTS', 'PROD_TOTAL');
In the Sales History sample schema, COSTS
is a fact table with the following columns:
Column Name |
Data Type |
---|---|
|
|
|
|
|
|
|
|
The following procedures create a logical metadata cube object, ANALYTIC_CUBE
, for the COSTS
fact table. The dimensions of the cube are: PRODUCT_DIM
, shown above, and TIME_DIM
, a time dimension mapped to a table TIME
.
--- Create the ANALYTIC_CUBE Cube --- cwm2_olap_cube.create_cube('SH', 'ANALYTIC_CUBE', 'Analytics', 'Analytic Cube', 'Unit Cost and Price Analysis'); --- Add the dimensions to the cube --- cwm2_olap_cube.add_dimension_to_cube('SH', 'ANALYTIC_CUBE', 'SH', 'TIME_DIM'); cwm2_olap_cube.add_dimension_to_cube('SH', 'ANALYTIC_CUBE', 'SH', 'PRODUCT_DIM'); --- Create the measures --- cwm2_olap_measure.create_measure('SH', 'ANALYTIC_CUBE', 'UNIT_COST', 'Unit Cost','Unit Cost', 'Unit Cost'); cwm2_olap_measure.create_measure('SH', 'ANALYTIC_CUBE', 'UNIT_PRICE', 'Unit Price','Unit Price', 'Unit Price'); --- Create the mappings ---
cwm2_olap_table_map.Map_FactTbl_LevelKey ('SH', 'ANALYTIC_CUBE','SH', 'COSTS', 'LOWEST LEVEL', 'DIM:SH.PRODUCTS/HIER:STANDARD/LVL:L4/COL:PROD_ID; DIM:SH.TIME/HIER:CALENDAR/LVL:L3/COL:MONTH;'); cwm2_olap_table_map.Map_FactTbl_Measure ('SH', 'ANALYTIC_CUBE','UNIT_COST', 'SH', 'COSTS', 'UNIT_COST', 'DIM:SH.PRODUCTS/HIER:STANDARD/LVL:L4/COL:PROD_ID; DIM:SH.TIME/HIER:CALENDAR/LVL:L3/COL:MONTH;'); cwm2_olap_table_map.Map_FactTbl_Measure ('SH', 'ANALYTIC_CUBE','UNIT_PRICE', 'SH', 'COSTS', 'UNIT_PRICE', 'DIM:SH.PRODUCTS/HIER:STANDARD/LVL:L4/COL:PROD_ID; DIM:SH.TIME/HIER:CALENDAR/LVL:L3/COL:MONTH;');
|
![]() Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|