Rem
Rem $Header: cwm2mrtb.sql 23-aug-2006.09:37:11 mstasiew Exp $
Rem
Rem cwm2mrtb.sql
Rem
Rem Copyright (c) 2002, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      cwm2mrtb.sql - script for metadata reader tables for performance
Rem                     enhancements 
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    mstasiew    08/23/06 - datatypes for cwm1 dim key cols
Rem    dbardwel    04/24/05 - Add 3 columns to refresh table bug 3996129  for performance tuning
Rem    mstasiew    08/18/04 - 3808848
Rem    dbardwel    04/19/04 - Adjust column lengths for plural name and display name
Rem    mstasiew    09/23/03 - 
Rem    mstasiew    09/09/03 - 
Rem    mstasiew    08/27/03 - 
Rem    mstasiew    06/10/03 - 
Rem    mstasiew    06/09/03 - 
Rem    mstasiew    05/15/03 - 
Rem    mstasiew    03/18/03 - 
Rem    dbardwel    02/24/03 - Add missing on commit preserve rows to tmp tables
Rem    dbardwel    01/27/03 - Adding shortdescription column where needed
Rem    mstasiew    12/16/02 - 
Rem    mstasiew    09/24/02 - mstasiew_txn104178
Rem    mstasiew    09/03/02 - 
Rem    dbardwel    07/23/02 - Created
Rem


create global temporary table olapsys.olap_session_objects (
  version_id char(4),
  objtype char(4),
  id number
)
on commit preserve rows
/

create global temporary table olapsys.olap_session_dims (
  id number,
  version_id char(4)
)
on commit preserve rows
/

create global temporary table olapsys.olap_session_cubes (
 id number,
 version_id char(4)
)
on commit preserve rows
/

create table olapsys.cwm2$mrall_awviews (
  VIEW_OWNER VARCHAR2(30),
  VIEW_NAME VARCHAR2(30),
  ROWTOCELLCOL_NAME VARCHAR2(30),
  AW_OWNER varchar2(30),
  AW_NAME varchar2(30)
);

create table olapsys.cwm2$mrall_awviewcols (
  VIEW_OWNER varchar2(30),
  VIEW_NAME  varchar2(30),
  COLUMN_NAME varchar2(30),
  AWOBJECT varchar2(2000)
);


create table olapsys.CWM2$MRALL_CATALOGS (
 CATALOG_ID number, 
 CATALOG_NAME varchar2(240),
 PARENT_CATALOG_ID number,
 DESCRIPTION varchar2(4000) 
 );


create table olapsys.CWM2$MRall_catalog_entity_uses (
 CATALOG_ID number,
 ENTITY_OWNER varchar2(240),
 ENTITY_NAME varchar2(240),
 CHILD_ENTITY_NAME varchar2(240),
 version_id char(4),
 id number
 );


create table olapsys.CWM2$MRALL_LISTDIMS (
 OWNER varchar2(30),
 DIMENSION_NAME varchar2(30),
 PLURAL_NAME varchar2(240),
 DISPLAY_NAME varchar2(240),
 SHORTDESCRIPTION varchar2(240),
 DESCRIPTION varchar2(4000),
 DESCRIPTOR_VALUE varchar2(30),
 TABLE_OWNER varchar2(30),
 TABLE_NAME varchar2(30),
 COLUMN_NAME varchar2(30),
 COLUMN_POSITION number,
 data_type varchar2(106),
 data_length number,
 data_precision number,
 level_name varchar2(30),
 version_id char(4),
 id number
 );


create table olapsys.CWM2$MRALL_DIM_HIERS (
 OWNER varchar2(30),
 DIMENSION_NAME varchar2(30),
 HIERARCHY_NAME varchar2(30),
 DISPLAY_NAME varchar2(240),
 SHORTDESCRIPTION varchar2(240),
 DESCRIPTION varchar2(4000),
 SOLVED_CODE varchar2(2),
 IS_DEFAULT varchar2(1),
 version_id char(4),
 id number
 );


create table olapsys.CWM2$MRALL_HIERDIMS (
 OWNER varchar2(30),
 DIMENSION_NAME varchar2(240),
 PLURAL_NAME varchar2(240),
 DISPLAY_NAME varchar2(240),
 SHORTDESCRIPTION varchar2(240),
 DESCRIPTION varchar2(2000),
 DEFAULT_DISPLAY_HIERARCHY varchar2(30),
 DESCRIPTOR_VALUE varchar2(30),
 version_id char(4),
 id number
 );


create table olapsys.CWM2$MRALL_DIM_HIER_LEVEL_USES (
 OWNER varchar2(30),
 DIMENSION_NAME varchar2(30),
 HIERARCHY_NAME varchar2(30),
 PARENT_LEVEL_NAME varchar2(30),
 CHILD_LEVEL_NAME varchar2(30),
 POSITION number,
 version_id char(4),
 id number
 );


create table olapsys.CWM2$MRALL_JOIN_KEY_COL_USES (
 OWNER varchar2(30),
 DIMENSION_NAME varchar2(30),
 HIERARCHY_NAME varchar2(30),
 CHILD_LEVEL_NAME varchar2(30),
 TABLE_OWNER varchar2(30),
 TABLE_NAME varchar2(30),
 COLUMN_NAME varchar2(30),
 POSITION number,
 JOIN_KEY_TYPE varchar2(30),
 version_id char(4),
 id number
 );


create table olapsys.CWM2$MRALL_DIM_ATTRIBUTES (
 OWNER varchar2(30),
 DIMENSION_NAME varchar2(30),
 ATTRIBUTE_NAME varchar2(240),
 DISPLAY_NAME varchar2(240),
 SHORTDESCRIPTION varchar2(240),
 DESCRIPTION varchar2(4000),
 DESC_ID number(38),
 version_id char(4),
 id number
 );


create table olapsys.CWM2$MRALL_ENTITY_DESC_USES (
 DESCRIPTOR_ID         NUMBER,
 ENTITY_OWNER         VARCHAR2(240),
 ENTITY_NAME          VARCHAR2(240),
 CHILD_ENTITY_NAME    VARCHAR2(240),
 SECONDARY_CHILD_ENTITY_NAME  VARCHAR2(30),
 version_id char(4),
 id number
 );


create table olapsys.CWM2$MRALL_DESCRIPTORS (
 DESCRIPTOR_ID number(38),
 DESCRIPTOR_VALUE varchar2(240),
 DESCRIPTOR_TYPE varchar2(240),
 DESCRIPTION varchar2(4000)
);


create table olapsys.CWM2$MRALL_DIM_LEVEL_ATTR_MAPS (
 OWNER varchar2(30),
 DIMENSION_NAME varchar2(30),
 HIERARCHY_NAME varchar2(30),
 ATTRIBUTE_NAME varchar2(240),
 LVL_ATTRIBUTE_NAME varchar2(240),
 LEVEL_NAME varchar2(30),
 TABLE_OWNER varchar2(30),
 TABLE_NAME varchar2(30),
 COLUMN_NAME varchar2(30),
 DTYPE varchar2(106),
 DATA_LENGTH number,
 DATA_PRECISION number,
 OLAP_API_DATA_TYPE number, 
 version_id char(4),
 id number
 );


create table olapsys.CWM2$MRALL_CUBE_MEASURES (
 OWNER varchar2(240),
 CUBE_NAME varchar2(240),
 MEASURE_NAME varchar2(240),
 DISPLAY_NAME varchar2(240),
 SHORTDESCRIPTION varchar2(240),
 DESCRIPTION varchar2(4000),
 CUBE_DESCRIPTION varchar2(4000),
 version_id char(4),
 id number
 );


create table olapsys.CWM2$MRALL_FACTTBLKEYMAPS (
 owner varchar2(30),
 cube_name varchar2(30),
 dim_hier_combo_id number,
 dimension_owner varchar2(30),
 dimension_name varchar2(30),
 hierarchy_name varchar2(30),
 level_name varchar2(30),
 fact_table_owner varchar2(30),
 fact_table_name varchar2(30),
 column_name varchar2(30),
 column_data_type varchar2(106),
 column_data_length number,
 column_data_precision number,
 gid_column_name varchar2(30),
 gid_column_data_type varchar2(106),
 gid_column_data_length number,
 gid_column_data_precision number,
 dimension_keymap_type number,
 mv_summarycode varchar2(2),
 column_position number,
 version_id char(4),
 id number,
 table_id number,
 column_id number,
 gidcolumn_id number
 );


create table olapsys.CWM2$MRFACTTBLKEYMAPS (
 owner varchar2(240),
 cube_name varchar2(240),
 dimension_owner varchar2(30),
 dimension_name varchar2(30),
 level_name varchar2(30),
 fact_table_owner varchar2(30),
 fact_table_name varchar2(30),
 column_name varchar2(30),
 column_position number,
 mv_summary_code varchar2(2),
 data_type varchar2(106),
 data_length number,
 data_precision number,
 version_id char(4),
 id number
 );


create table olapsys.CWM2$MRALL_FACTTBLFCTMAPS (
 owner varchar2(30),
 cube_name varchar2(30),
 measure_name varchar2(30),
 dim_hier_combo_id number,
 fact_table_owner varchar2(30),
 fact_table_name varchar2(30),
 column_name varchar2(30),
 data_type varchar2(106),
 data_length number,
 data_precision number,
 olap_api_data_type number,
 version_id char(4), 
 id number
 );


create table olapsys.CWM2$MRFACTTBLFCTMAPS (
 owner varchar2(240),
 cube_name varchar2(240),
 measure_name varchar2(240),
 fact_table_owner varchar2(30),
 fact_table_name varchar2(30),
 column_name varchar2(30),
 data_type varchar2(106),
 data_length number,
 data_precision number,
 olap_api_data_type number,
 version_id char(4),
 id number
); 


create table olapsys.CWM2$MRALL_HIERDIM_KEYCOL_MAP (
 owner varchar2(30),
 dimension_name varchar2(30),
 hierarchy_name varchar2(30),
 level_name varchar2(30),
 display_name varchar2(240),
 shortdescription varchar2(240),
 description varchar2(4000),
 hierarchy_position number,
 table_owner varchar2(30),
 table_name varchar2(30),
 column_name varchar2(30),
 column_position number,
 data_type varchar2(106),
 data_length number,
 data_precision number,
 version_id char(4),
 id number
 ); 


create table olapsys.CWM2$MRALL_HIER_CUSTOM_SORT (
 owner varchar2(30),
 dimension_name varchar2(30),
 hierarchy_name varchar2(30),
 table_owner varchar2(30),
 table_name varchar2(30),
 column_name varchar2(30),
 position number,
 sort_pos number,
 sort_order varchar2(4),
 null_order varchar2(5),
 data_type varchar2(106),
 data_length number,
 data_precision number,
 version_id char(4),
 id number
 );

create table olapsys.CWM2$MRALL_OLAP2_AGG_USES (
 OWNER                                      VARCHAR2(30),
 CUBE_NAME                                  VARCHAR2(30),
 DIMENSION_OWNER                            VARCHAR2(30),
 DIMENSION_NAME                             VARCHAR2(30),
 HIERARCHY_NAME                             VARCHAR2(30),
 DIM_HIER_COMBO_ID                          NUMBER,
 AGGREGATION_NAME                           VARCHAR2(240),
 AGGREGATION_ORDER                          NUMBER,
 TABLE_OWNER                                VARCHAR2(30),
 TABLE_NAME                                 VARCHAR2(30),
 COLUMN_NAME                                VARCHAR2(30),
 version_id char(4),
 id number
);

create table olapsys.CWM2$MRALL_CWM1_AGGOP (
 owner varchar2(30),
 cube_name varchar2(30),
 measure_name varchar2(30),
 dimension_owner varchar2(30),
 dimension_name varchar2(30),
 func_name varchar2(240),
 table_owner varchar2(30),
 table_name varchar2(30),
 column_name varchar2(30),
 version_id char(4),
 id number
);

create table olapsys.CWM2$MRALL_CWM1_AGGORD (
 owner varchar2(30),
 cube_name varchar2(30),
 dimension_owner varchar2(30),
 dimension_name varchar2(30),
 position number,
 version_id char(4),
 id number
);

create table olapsys.cwm2$mrall_entity_parameters (
 DESCRIPTOR_ID                                      NUMBER(38),
 DESCRIPTOR_NAME                                    VARCHAR2(240),
 ENTITY_OWNER                                       VARCHAR2(240),
 ENTITY_NAME                                        VARCHAR2(240),
 CHILD_ENTITY_NAME                                  VARCHAR2(240),
 SECONDARY_CHILD_ENTITY_NAME                        VARCHAR2(30),
 PARAMETER_NAME                                     VARCHAR2(80),
 PARAMETER_VALUE                                    VARCHAR2(4000),
 version_id char(4),
 id number);

create table olapsys.cwm2$mrall_entity_ext_parms (
 DESCRIPTOR_ID                                      NUMBER(38),
 DESCRIPTOR_NAME                                    VARCHAR2(240),
 ENTITY_OWNER                                       VARCHAR2(240),
 ENTITY_NAME                                        VARCHAR2(240),
 CHILD_ENTITY_NAME                                  VARCHAR2(30),
 SECONDARY_CHILD_ENTITY_NAME                        VARCHAR2(30),
 PARAMETER_NAME                                     VARCHAR2(80),
 PARAMETER_VALUE                                    VARCHAR2(4000),
 PARAMETER_VALUE2                                   VARCHAR2(4000),
 PARAMETER_VALUE3                                   VARCHAR2(4000),
 PARAMETER_VALUE4                                   VARCHAR2(4000),
 POSITION                                           NUMBER,
 version_id char(4),
 id number
);