REM
REM daw.bsq - Analytic Workspace catalog tables
REM
REM MODIFIED   (MM/DD/YY)
Rem   jcarey    03/16/09 - move ps$ and aw$ to sysaux
Rem   rsamuels  12/16/08 - OLAP API new columns & renamed columns
Rem   rsamuels  11/21/08 - Add olap_multi_options$
Rem   rsamuels  11/21/08 - Extend olap_aw_deployment_controls$.physical_name
Rem                        to VARCHAR2(64)
REM   cchiappa  04/23/08 - Add awlogseq$
REM   sfeinste  06/11/07 - Extend length of olap_descriptions$.language and
REM                        olap_impl_options$.option_value
REM   sfeinste  02/28/07 - Cleanup olap dict tables
REM   wechen    02/17/07 - rename olap_primary_dimensions$, olap_interactions$
REM                        to olap_cube_dimensions$, olap_build_processes$
REM   sfeinste  01/22/07 - Fix i_olap_syntax$
REM   ghicks    08/21/06 - add rsygen to aw$
REM   cchiappa  03/30/06 - OLAP Organized tables 
REM   smesropi  05/26/06 - add olap data dictionary tables
REM   cchiappa  01/19/06 - add aw_track$
REM   jklein    08/01/05 - creation

create table aw$
(awname varchar2("M_IDEN"),              /* name of AW */
 owner#  number not null,                /* owner of AW */
 awseq#  number not null,                /* aw sequence number */
 version number default null,            /* aw storage version */
 oids    number(10) default null,        /* object id page space */
 objs    number(10) default null,        /* object storage page space */
 dict    raw(8) default null,            /* aw dictionary object */
 rsygen  number default null)            /* resync compatibility */
 tablespace sysaux
/
create unique index aw_ind$ on aw$(awname, owner#) tablespace sysaux
/
create table ps$
( awseq# number not null,                 /* aw sequence number */
 psnumber number(10),                    /* pagespace number */
 psgen number(10),                       /* pagespace generation */
 mapoffset number,                       /* offset of map */
 maxpages number,                        /* max pages in ps */
 almap raw(8),                           /* pointer to almap */
 header raw(200),                        /* internal header */
 gelob blob,                             /* erase list */
 gelrec number default null,             /* generation erase list */
 maprec number default null)             /* map record */
 lob (gelob) store as (disable storage in row)
 tablespace sysaux
/
create unique index i_ps$ on ps$ (awseq#, psnumber, psgen) tablespace sysaux
/

create sequence psindex_seq$ /* sequence for pagespace index */
 start with 100
 increment by 1
 cache 1000  /* allow caching for rac */
 nocycle
 maxvalue 18446744073709551615
/

create sequence awseq$ /* sequence for aw index */
  start with 1000
  increment by 1
  nocache 
  nocycle
  maxvalue 4294967295
/

create sequence awlogseq$ /* sequence for log id numbers */
  start with 1
  increment by 1
  cache 10
  maxvalue 18446744073709551615
/

create table aw_obj$  /* Analytical Workspace Object table */
(awseq# number,                     /* aw sequence number */
 oid number(20),                    /* object number, up to UB8MAXVAL */
 objname varchar2(256),             /* object name, ref NAMESIZE in xsobj.c */
 gen# number(10),                   /* generation number */
 objtype number(4),                 /* object type */
 partname varchar2(256),            /* partition name */
 objdef blob,                       /* object definition */
 objvalue blob,                     /* object value */
 compcode blob)                     /* compiled code body */
lob(objdef) store as (enable storage in row)
lob(objvalue) store as (enable storage in row) 
lob(compcode) store as (enable storage in row)
tablespace sysaux
/
create unique index i_aw_obj$ on aw_obj$ (awseq#, oid, gen#) tablespace sysaux
/

create table aw_prop$ /* Analytical Workspace Property table */
(awseq# number,                          /* aw sequence number */
 oid number(20),                         /* object number, up to UB8MAXVAL */
 objname varchar2(256),                  /* object name */
 gen# number(10),                        /* generation number */ 
 propname varchar2(256),                 /* property name */
 proptype number,                        /* property type */ 
 propval blob)                           /* property value */
lob(propval) store as (enable storage in row)
tablespace sysaux
/
create index i_aw_prop$ on aw_prop$ (awseq#, oid, propname, gen#)
  tablespace sysaux
/

create table aw_track$ /* Analytic Workspace Access Tracking table */
(awseq# number,                          /* aw sequence number */
 oid    number(20),                      /* object number, up to UB8MAXVAL */
 key0   number(10),                      /* dimension key #1 */
 key1   number(10),                      /* dimension key #2 */
 key2   number(10),                      /* dimension key #3 */
 key3   number(10),                      /* dimension key #4 */
 key4   number(10),                      /* dimension key #5 */
 key5   number(10),                      /* dimension key #6 */
 key6   number(10),                      /* dimension key #7 */
 key7   number(10),                      /* dimension key #8 */
 key8   number(10),                      /* dimension key #9 */
 key9   number(10),                      /* dimension key #10 */
 key10  number(10),                      /* dimension key #11 */
 key11  number(10),                      /* dimension key #12 */
 key12  number(10),                      /* dimension key #13 */
 key13  number(10),                      /* dimension key #14 */
 key14  number(10),                      /* dimension key #15 */
 key15  number(10),                      /* dimension key #16 */
 key16  number(10),                      /* dimension key #17 */
 key17  number(10),                      /* dimension key #18 */
 key18  number(10),                      /* dimension key #19 */
 key19  number(10),                      /* dimension key #20 */
 key20  number(10),                      /* dimension key #21 */
 key21  number(10),                      /* dimension key #22 */
 key22  number(10),                      /* dimension key #23 */
 key23  number(10),                      /* dimension key #24 */
 key24  number(10),                      /* dimension key #25 */
 key25  number(10),                      /* dimension key #26 */
 key26  number(10),                      /* dimension key #27 */
 key27  number(10),                      /* dimension key #28 */
 key28  number(10),                      /* dimension key #29 */
 key29  number(10),                      /* dimension key #30 */
 acount number(16),                      /* access count */
 atime  number(16))                      /* total access time */
 tablespace sysaux
/

create unique index i_aw_track$
 on aw_track$ (awseq#, oid,
               key0,  key1,  key2,  key3,  key4,  key5,  key6,  key7,  key8,
               key9,  key10, key11, key12, key13, key14, key15, key16, key17,
               key18, key19, key20, key21, key22, key23, key24, key25, key26,
               key27, key28, key29)
 tablespace sysaux
/

create table aw_prg$  /* Analytical Workspace Program table */
( awseq# number,                     /* aw sequence number */
  oid number(20),                    /* object number, up to UB8MAXVAL */
  gen# number(10),                   /* generation number */
  stm# number,                       /* statement number */
  stmtext blob,                      /* statement text */
  compcode blob,                     /* compiled code body */
  flags number,                      /* flags */
  spare blob)                        /* reserved */
lob(stmtext) store as (enable storage in row)
lob(compcode) store as (enable storage in row)
lob (spare) store as (enable storage in row)
tablespace sysaux
/

create unique index i_aw_prg$ on aw_prg$ (awseq#, oid, gen#, stm#) 
tablespace sysaux
/

/************MAPPINGS**************/

create table olap_mappings$
(
  map_name varchar2("M_IDEN") not null,  /* map name */  
  map_id number not null,                /* map ID */    
  map_type number(2,0) not null,         /* type of map e.g. solve hier */
  mapping_owner_id number not null,      /* mapping owner ID */
  mapping_owner_type number not null,    /* mapping owner type */
  mapped_object_id number,               /* Id of mapped object */
  mapped_dim_type number,                /* type of mapped dim */
  mapped_dim_id number,                  /* Id of mapped dim */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_mappings$ on olap_mappings$ (map_id)
/

/************MODELS*************/

create table olap_models$
( 
  owning_obj_type number not null,        /* owning obj number type */
  owning_obj_id number not null,          /* owning obj number ID */
  model_role number not null,             /* role DEFAULT_STRING,...,USER */
  model_id number not null,               /* model ID */
  default_precedence number not null,     /* default precedence */
  model_name varchar2("M_IDEN") not null, /* model name */
  explicit_dim_id number,                 /* id of 1st explicit dimension */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_models$ on olap_models$ 
  (owning_obj_id, owning_obj_type, model_id)
/

create table olap_model_parents$
( 
  model_id number not null,        /* model id */
  parent_model_id number not null, /* id of parent model */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_model_parents$ on olap_model_parents$
  (model_id, parent_model_id)
/

create table olap_model_assignments$
( 
  model_id number not null,              /* model ID */
  assignment_id number not null,         /* assignment ID */
  precedence number,                     /* precedence */
  order_num number not null,             /* order num */
  calculated_member_id number, /* if null use syntax else use calc member exp */
  member_name varchar2("M_IDEN"),        /* member name */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_model_assignment$ on olap_model_assignments$ 
  (model_id, order_num)
/

create table olap_calculated_members$
(
  dim_obj# number not null,                  /* cube dimension number */
  member_name varchar2("M_IDEN") not null,   /* member name */
  member_id number not null,                 /* member ID */
  container_dim_id number not null,          /* id of container - e.g. level */
  container_dim_type number not null,        /* type of container */
  parent_member_name varchar2(100),          /* parent member */
  parent_container_id number,                /* parent level id */
  is_customaggregate number(1, 0),           /* is custom aggregate */
  storage_type number(2,0) not null,         /* DYNAMIC or PRECOMPUTE */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_calculated_members$
 on olap_calculated_members$ (dim_obj# asc, member_id asc)
/

/************GENERAL OLAP SUPPORT ***********/

create table olap_syntax$
( 
  ref_role number not null,              /* the role this syntax plays */
  owner_id number not null,              /* ID of owning object */
  owner_type number not null,            /* Type of owning object */
  order_num number not null,             /* the order within a list  */
  syntax_clob clob not null,             /* syntax text stored in clob */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_syntax$
 on olap_syntax$ (ref_role, owner_id, owner_type, order_num)
/

create table olap_descriptions$
( 
  obj# number not null,                  /* id of top level object for query */
  owning_object_type number not null,     /* owning object type */
  owning_object_id number not null,       /* owning object reference ID */
  language varchar2(80) not null,         /* description language */
  description_type varchar2("M_IDEN") not null,  /* description type */
  description_value nvarchar2(300),              /* description value */
  description_class number,                      /* description class */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_description$ 
 on olap_descriptions$ (owning_object_type, owning_object_id, 
                        language, description_type)
/


/*************BUILD PROCESSES****************/

create table olap_cube_build_processes$
(
  obj# number not null,                  /* object number */  
  audit$ varchar2("S_OPFL") not null,   /* auditing options */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_cube_build_processes$ on olap_cube_build_processes$ (obj#)
/

/***************AW VIEWS****************/

create table olap_aw_views$
(
  view_obj# number not null,             /* view obj# */  
  view_type number not null,             /* ET, STAR, REFRESH, REWRITE*/
  olap_object_type number not null,      /* owner type */
  olap_object_id number not null,        /* owner id */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_aw_views$
 on olap_aw_views$ (view_obj#)
/

create table olap_aw_view_columns$
(
  view_obj# number not null,           /* view obj# */  
  column_obj# number not null,         /* column obj# */  
  referenced_object_type number,       /* referenced object type */
  referenced_object_id number,         /* referenced object number */
  level_id number,                     /* hier level number for star views */
  column_type NUMBER not null,         /* OBJECT, KEY, PARENT ...*/
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_aw_view_columns$
 on olap_aw_view_columns$ (view_obj#, column_obj#)
/

/******MEASURE FOLDERS********/

create table olap_measure_folders$
(
  obj# number not null,                       /* Object number */
  audit$ varchar2("S_OPFL") not null,         /* auditing options */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_measure_folders$ on olap_measure_folders$ (obj#)
/

create table olap_meas_folder_contents$
( 
  measure_folder_obj# number not null,   /* measure folder object number */
  object_type number not null,           /* Type of contained object */
  object_id number not null,             /* ID of contained object */
  order_num number not null,             /* Order of measure within folder */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_meas_folder_contents$ 
 on olap_meas_folder_contents$ (measure_folder_obj#, order_num)
/

/**************DEPLOYMENTS*************/

create table olap_aw_deployment_controls$
( 
  object_role NUMBER not null,         /* role played by physical obj */
  physical_name varchar2(64),          /* name of physical aw object */
  parent_id NUMBER not null,           /* number of owner logical obj */
  parent_type NUMBER not null,         /* type code of owning object */
  awowner# number,                     /* aw owner number */
  awseq# number,                       /* aw sequence number */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),               /* used for long physical_names */
  spare4 varchar2(1000),
  spare5 varchar2(1000)
)
/

create table olap_impl_options$
(
  owning_objectid number not null,       /* owning object ID */
  object_type number not null,           /* object type */ 
  option_type number not null,           /* option type enum */
  option_value varchar2(200),            /* option value */
  option_num_value number,               /* option num value */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),                 /* used for long option_values */
  spare4 varchar2(1000),
  spare5 varchar2(1000)
)
/

create unique index i_olap_impl_options$ 
    on olap_impl_options$ (owning_objectid, object_type, option_type)
/

create table olap_multi_options$
(
 owning_objectid number not null,       /* owning object ID */
 object_type number not null,           /* object type */
 option_type number not null,           /* option type enum */
 option_order number(20) not null,      /* order of this value in the option */
 option_value varchar2(80),             /* option value */
 option_num_value number,               /* option num value */
 option_ref_obj_type number,   /* if option_num_value represents an object, 
                                  the type of object */
 spare1 number,
 spare2 number,
 spare3 varchar2(1000),
 spare4 varchar2(1000)
) 
/

create unique index i_olap_multi_options on olap_multi_options$ 
  (owning_objectid, object_type, option_type, option_order)
/

/*************DIMENSIONS**************/

create table olap_cube_dimensions$
( 
  awseq# number,                              /* aw sequence number */
  obj# number not null,                       /* object number */
  dimension_type number not null,             /* dimension type */
  audit$ varchar2("S_OPFL") not null,         /* auditing options */
  is_stale number(1,0) not null,              /* is the dimension stale? */
  default_hierarchy_id number,                /* default hierarchy */
  type# number,                               /* Data type of the dimension */
  length number,                              /* Data type length */
  charsetform number,                         /* Charsetform of data type */
  precision# number,                       /* Numeric precision of data type */
  scale number,                               /* Numeric scale of data type */
  type_property number,                       /* Data type flags */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_cube_dimensions$
 on olap_cube_dimensions$ (obj#)
/

create table olap_dim_levels$
(
  dim_obj# number not null,                    /* dimension object nmumber */  
  level_name varchar2("M_IDEN") not null,      /* level name */
  level_id number not null,                    /* level ID */
  level_order number,                          /* order of level in dimension */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_dim_levels$ on olap_dim_levels$ (level_id)
/

create table olap_attributes$
(
  dim_obj# number not null,                      /* prim. dimension number */ 
  attribute_name varchar2("M_IDEN") not null,    /* attribute name */
  attribute_id number not null,                  /* attribute number */
  target_dim# number,                            /* target dim obj number */
  target_attribute# number,                      /* target attribute */
  attribute_role_mask number,                    /* role mask of attribute */
  type# number not null,
  length number not null,
  charsetform number,
  precision# number,
  scale number,
  type_property number,                          /* Data type flags */
  attribute_order number,                /* order of attribute in dimension */
  spare1 number,
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_attributes$ 
        on olap_attributes$ (dim_obj#, attribute_id asc)
/


create table olap_attribute_visibility$
( 
  attribute_id number not null,        /* attribute number */
  owning_dim_id number not null,       /* ID of dim that sees this attr */
  owning_dim_type number not null,     /* Type of dim that sees this attr */
  order_num number,                    /* Allows vis attrs to be ordered */
  is_unique_key number(1,0),           /* 1 if unique key, 0/null otherwise */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_attribute_visibility$ on olap_attribute_visibility$ 
   (attribute_id, owning_dim_id, owning_dim_type asc)
/

create table olap_hierarchies$
(
  dim_obj# number not null,                    /* dimension object number */
  hierarchy_name varchar2("M_IDEN") not null,  /* hierarchy name */
  hierarchy_type number not null,              /* hierarchy type */
  hierarchy_id number not null,                /* hierarchy ID */  
  hierarchy_order number,                 /* order of hierarchy in dimension */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_hierarchies$ on olap_hierarchies$ 
  (dim_obj#, hierarchy_id)
/

create table olap_hier_levels$
( 
  hierarchy_id number not null,           /* ID of owning hierarchy */
  order_num number not null,              /* level order number */
  hierarchy_level_id number not null,     /* hierarchy level ID */
  dim_level_id number not null,           /* ID of dimension level */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_hier_levels$ 
 on olap_hier_levels$ (hierarchy_id, order_num)
/

/**********CUBES***************/

create table olap_cubes$
( 
  awseq# number,                         /* aw sequence */
  obj# number not null,                  /* object number */
  audit$ varchar2("S_OPFL") not null,    /* auditing options */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_cubes$ on olap_cubes$ (obj#)
/

create table olap_measures$
( 
  cube_obj# number not null,                  /* cube object number */
  measure_name varchar2("M_IDEN") not null,   /* meausre name */
  measure_id number not null,                 /* measure ID */
  measure_type number(2, 0)  not null,        /* derived vs. base */
  type# number not null,
  length number not null,
  charsetform number,
  precision# number,
  scale number,
  type_property number,                       /* Data type flags */
  is_stale number(1,0) not null,              /* is the measure stale? */
  is_hidden number(1,0),                      /* is the measure hidden? */
  measure_order number,                       /* order of measure in cube */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_measures_index$
 on olap_measures$ (cube_obj#, measure_id)
/

create table olap_dimensionality$
( 
  dimensioned_object_id number not null,    /* cube, solve_spec_region, or attribute */
  dimensioned_object_type number not null,  /* type code of dimensioned obj */
  dimensionality_id number not null,        /* dimensionality ID */
  order_num number not null,                /* order within owner */
  dimension_id number not null,             /* ID of dimension  */
  dimension_type number not null,           /* dimension type (hier or level) */
  owning_diml_id number,    /* ID of owning dim'ality for a breakout dim */
  attribute_id number,      /* ID of attribute for a breakout dim */
  breakout_flags number,    /* Numeric field for style of breakout */
  spare1 number, 
  spare2 number,
  spare3 varchar2(1000),
  spare4 varchar2(1000)
)
/

create unique index i_olap_dimensionality$ on olap_dimensionality$ 
  (dimensioned_object_id, dimensioned_object_type, order_num)
/


/* One row in olap_tab$ for every tab$ entry created as ORGANIZATION CUBE */
create table olap_tab$
(obj#   number not null,                           /* Parent table object # */
 awseq# number not null,                                 /* Underlying AW # */
 flags  number not null)                                  /* Physical flags */
                                                   /* 0x01 - On prebuilt AW */
/

create unique index i_olap_tab$
 on olap_tab$(obj#)
/

/* One row in olap_tab_object$ for every column of every entry in olap_tab$ */
create table olap_tab_col$
(obj#    number not null,                          /* Parent table object # */
 col#    number not null,                                  /* Column number */
 pcol#   number,                                    /* Parent column number */
 coltype number not null,                          /* Object type of column */
                                                                /* 1 - Fact */
                                                           /* 2 - Dimension */
                                                               /* 3 - Level */
                                                           /* 4 - Attribute */
                                                         /* 5 - Grouping id */
                                                  /* 6 - Parent grouping id */
                                                            /* 7 - Relation */
 oid     number not null,                            /* Mapped AW object id */
 qdroid  number,                              /* QDRing dimension object id */
 qdrval  varchar2(100),                                      /* QDRed value */
 hier#   number,                          /* Corresponding hierarchy number */
 flags   number not null)                                          /* Flags */
/

create index i_olap_tab_col$
 on olap_tab_col$(obj#)
/

/* One row per level of hierarchy */
create table olap_tab_hier$
(obj#    number not null,                          /* Parent table object # */
 hier#   number not null,          /* Hierarchy number (currently always 1) */
 col#    number not null,                         /* Column number of level */
 ord     number not null,                /* Ordinal of level, starting at 1 */
 flags   number not null)                                          /* Flags */
/
create index i_olap_tab_hier$
 on olap_tab_hier$(obj#)
/