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#)
/