REM REM Copyright (c) 2001, 2005, Oracle. All rights reserved. REM REM NAME REM cwm2mtbl.sql REM REM DESCRIPTION REM CwM2Lite Metadata Table Definitions REM Script first drops all tables then creates them REM REM NOTES REM REM MODIFIED (MM/DD/YY) REM awesley 06/17/05 - change display name and plural name length REM dbardwel 09/23/03 - REM dbardwel 09/16/03 - REM dbardwel 09/08/03 - Add ODBO Metadata support for 10g REM Aziz.Mboya 07/29/03 - Fix to bug 2998892: PROBLEMS WITH MAKCMCAT.SQL ORGANIZATION, amboya_txn108325 REM dbardwel 06/23/03 - Moved trigger and constraints on cwm2$awviewcols to REM separate files cwm2pkfk.sql and cwm2trg.sql REM mstasiew 06/16/03 - REM dbardwel 05/01/03 - Adding aggregation columns to cwm2$factkeydimhiermap REM cdalessi 04/28/03 - Separate triggers out to cwm2trg.sql; alter and REM sequence out to cwm2inst.sql REM cdalessi 02/07/03 - change to create or replace trigger REM dbardwel 01/27/03 - Add Lev's XML Tables REM mstasiew 12/16/02 - REM mstasiew 12/13/02 REM awesley 12/03/02 - REM awesley 10/28/02 - remove CwM2$OLAPValidateTable REM awesley 09/25/02 add CwM2$OLAPValidateTable REM dbardwel 09/28/01 created for 9i release 2. REM dbardwel 10/15/01 minor updates REM dbardwel 11/02/01 added transaction timestamp information REM dbardwel 11/02/01 added alter for cwm$cube table for REM MVSUMMARYCODE REM dbardwel 11/12/01 added DefaultCalcHier to cwm2$cubedimensionuse REM dbardwel 01/24/02 added not null constraints to logical key columns in REM logical entity tables. REM REM REM Create all CwM2 Model Tables for 9i release 2 REM REM 1. CwM2$Dimension create table olapsys.CwM2$Dimension ( IRID number not null, Owner varchar2(30) not null, Name varchar2(30) not null, ShortDescription varchar2(240), Description varchar2(2000), DisplayName varchar2(90), PluralName varchar2(90), DimensionType varchar2(10), Invalid varchar2(2), AW_DBObjectID number, DefaultHier_IRID number, CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 2. CwM2$Level create table olapsys.CwM2$Level ( IRID number not null, Dimension_IRID number, Name varchar2(30) not null, ShortDescription varchar2(240), Description varchar2(2000), DisplayName varchar2(90), PluralName varchar2(90), Hidden varchar2(2), CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 3. CwM2$Hierarchy create table olapsys.CwM2$Hierarchy ( IRID number not null, Dimension_IRID number, Name varchar2(30) not null, ShortDescription varchar2(240), Description varchar2(2000), DisplayName varchar2(90), SolvedCode varchar2(2), Hidden varchar2(2), Origin varchar2(30), CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 4. CwM2$HierCustomSort create table olapsys.CwM2$HierCustomSort ( IRID number not null, Dimension_IRID number, Hier_IRID number, TableName_ID number, ColumnName_ID number, Position number, SortPos number, SortOrder varchar2(4), NullOrder varchar2(5), MetadataVersion varchar2(3), CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 5. CwM2$HierLevelRel create table olapsys.CwM2$HierLevelRel ( IRID number not null, Dimension_IRID number, Hierarchy_IRID number, ParentLevel_IRID number, ChildLevel_IRID number, LevelDepth number, CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 6. CwM2$DimensionAttribute create table olapsys.CwM2$DimensionAttribute ( IRID number not null, Dimension_IRID number, Name varchar2(30) not null, ShortDescription varchar2(240), Description varchar2(2000), DisplayName varchar2(90), CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 7. CwM2$LevelAttribute create table olapsys.CwM2$LevelAttribute ( IRID number not null, Dimension_IRID number, Level_IRID number, DimAttr_IRID number, Name varchar2(30) not null, ShortDescription varchar2(240), Description varchar2(2000), DisplayName varchar2(90), CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 8. CwM2$LevelAttributeMap create table olapsys.CwM2$LevelAttributeMap ( IRID number not null, HierLvlRel_IRID number, LevelAttr_IRID number, Table_ID number, Column_ID number, CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 9. CwM2$Cube create table olapsys.CwM2$Cube ( IRID number not null, Owner varchar2(30) not null, Name varchar2(30) not null, ShortDescription varchar2(240), Description varchar2(2000), DisplayName varchar2(90), AW_DBObjectID number, Invalid varchar2(2), MVSummaryCode varchar2(2), CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 10. CwM2$Measure create table olapsys.CwM2$Measure ( IRID number not null, Cube_IRID number, Name varchar2(30) not null, ShortDescription varchar2(240), Description varchar2(2000), DisplayName varchar2(90), CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 11. CwM2$CubeDimensionUse create table olapsys.CwM2$CubeDimensionUse ( IRID number not null, Cube_IRID number, Dimension_IRID number, Position number, DefaultCalcHier_IRID number, CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 12. CwM2$DimHierLvlMap create table olapsys.CwM2$DimHierLvlMap ( IRID number not null, DimHierLvl_IRID number, Object_ID number, Column_ID number, ParentColumn_ID number, Position number, Style varchar2(10), CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 13. CwM2$AW_DimensionMap create table olapsys.CwM2$AW_DimensionMap ( IRID number not null, Hier_IRID number, Object_ID number, ObjectType varchar2(30), DetailObj_ID number, CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 14. CwM2$FactDimHierMap create table olapsys.CwM2$FactDimHierMap ( IRID number not null, Cube_IRID number, FactTableName_ID number, FullyStoredIndicator varchar2(2), CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 15. CwM2$FactDimHierTplsDtl create table olapsys.CwM2$FactDimHierTplsDtl ( IRID number not null, Cube_IRID number, FactDimHier_IRID number, Hier_IRID number, Position number, CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 16. CwM2$MeasureTableMap create table olapsys.CwM2$MeasureTableMap ( IRID number not null, Measure_IRID number, FactDimHier_IRID number, FactTableName_ID number, FactColumn_ID number, CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 17. CwM2$FactKeyDimHierMap create table olapsys.CwM2$FactKeyDimHierMap ( IRID number not null, FactDimHier_IRID number, Dimension_IRID number, DimensionKeyMapType varchar2(30), GIDColumnName_ID number, AggOperator_IRID number, AggOrder number, AggWeightTbl_ID number, AggWeightCol_ID number, CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 18. CwM2$FactKeyDimHierLvlMap create table olapsys.CwM2$FactKeyDimHierLvlMap ( IRID number not null, FactKeyDimHier_IRID number, Level_IRID number, Hierarchy_IRID number, ColumnName_ID number, Position number, CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 19. CwM2$AW_MeasureMap create table olapsys.CwM2$AW_MeasureMap ( IRID number not null, FactDimHier_IRID number, Object_ID number, ObjectType varchar2(30), DetailObj_ID number, CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 20. CwM2$StoredDimLvlTpls create table olapsys.CwM2$StoredDimLvlTpls ( IRID number, FactDimHier_IRID number, StoredLvlTplID number, CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 21. CwM2$StoredDimLvlTplsDtl create table olapsys.CwM2$StoredDimLvlTplsDtl ( IRID number, StoredLvlTplID number, Level_IRID number, CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 22. CwM2$AWViews REM NEEDS WORK. REM This implementation will not allow the same view to REM have different RowToCell Columns if the view is mapped to REM different logical entities. Don't know if this is a problem. REM I assume not. create table olapsys.CwM2$AWViews ( IRID number not null, TABLE_ID number not null, ROWTOCELLCOL_ID number, VIEW_OWNER varchar2(30) not null, VIEW_NAME varchar2(30) not null, AW_OWNER varchar2(30), AW_NAME varchar2(30), CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM 23. CwM2$AWViewCols create table olapsys.CwM2$AWViewCols ( IRID number not null, AWVIEWS_IRID number not null, COLUMN_ID number, AWOBJECT varchar2(2000), CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); Rem CwM2$OLAPManagerTable create global temporary table CwM2$OLAPManagerTable (Row_ID number ,Log_File_Handle_id number ,Log_File_Handle_datatype number ,Log_File_Directory varchar2(100) ,Log_File_Name varchar2(30) ,Log_File_Append varchar(2) ,Log_Echo varchar(2) ) on commit preserve rows / REM new for 10g for ODBO Metadata support create table olapsys.cwm2$classificationvaluepair ( IRID number not null, ClassEntry_IRID number not null, ParameterName varchar2(80), ParameterValue varchar2(4000), ParameterValue2 varchar2(4000), ParameterValue3 varchar2(4000), ParameterValue4 varchar2(4000), Position number, CreatedDate date, UpdatedDate date, LastChangeUser varchar2(30) ); REM new for 10i XML processing metadata tables and 1 sequence create table olapsys.XML_LOAD_RECORDS ( xml_loadid number not null, xml_recordid number not null, xml_recordtext varchar2(2000), primary key (xml_loadid, xml_recordid)) / create table olapsys.XML_LOAD_LOG ( xml_loadid number not null, xml_recordid number not null, xml_date date not null, xml_aw varchar2(80), xml_message varchar2(2000), primary key (xml_loadid, xml_recordid)) / -- Table to keep log of next available Custom Measure ADT column to use. CREATE TABLE OLAPSYS.CWM2$_AW_NEXT_PERM_CUST_MEAS ( AW_ACCESS_VIEW_NAME VARCHAR2(61), NUM_CUST_MEASURE VARCHAR2(30), TEXT_CUST_MEASURE VARCHAR2(30)) / -- Table to contain a catalog of mappings for PERMANENT Custom Measures by View. CREATE TABLE OLAPSYS.CWM2$_AW_PERM_CUST_MEAS_MAP ( AW_ACCESS_VIEW_NAME VARCHAR2(61) NOT NULL, CUST_ADT_COLUMN VARCHAR2(30) NOT NULL, WORKSPACE_NAME VARCHAR2(61), AW_MEASURE_NAME VARCHAR2(64), SESSIONID VARCHAR2(10), USERNAME VARCHAR2(30)) / --Table to keep log of next available Custom Measure ADT column to use --(Session-specific). CREATE GLOBAL TEMPORARY TABLE OLAPSYS.CWM2$_AW_NEXT_TEMP_CUST_MEAS ( AW_ACCESS_VIEW_NAME VARCHAR2(61), NUM_CUST_MEASURE VARCHAR2(30), TEXT_CUST_MEASURE VARCHAR2(30)) ON COMMIT PRESERVE ROWS / -- Table to contain a catalog of mappings for TEMPORARY Custom Measures by -- View (Session-specific). CREATE GLOBAL TEMPORARY TABLE OLAPSYS.CWM2$_AW_TEMP_CUST_MEAS_MAP ( AW_ACCESS_VIEW_NAME VARCHAR2(61) NOT NULL, CUST_ADT_COLUMN VARCHAR2(30) NOT NULL, WORKSPACE_NAME VARCHAR2(61), AW_MEASURE_NAME VARCHAR2(64), SESSIONID VARCHAR2(10), USERNAME VARCHAR2(30)) ON COMMIT PRESERVE ROWS / --Table for storing temporary session-specific runtime information. CREATE GLOBAL TEMPORARY TABLE OLAPSYS.CWM2$_TEMP_VALUES ( INT_VAR NUMBER(30), NUM_VAR NUMBER(36,6), TEXT_VAR VARCHAR2(255)) ON COMMIT PRESERVE ROWS / show errors;