REM REM Copyright (c) 2001, 2004, Oracle Corporation. All rights reserved. REM REM NAME REM cwm2dbvw.sql REM REM DESCRIPTION REM The CwM2$ views which present the metadata information REM in a way that is consistent with the current CwM views. REM Each view name is always prefixed with 'dba$olap2_' REM These are the DBA version of the cwm2 views and union views REM REM NOTES REM REM MODIFIED (MM/DD/YY) REM glyon 06/24/08 - bug 7204558: eliminate references to sys.user$ REM dbardwel 06/17/04 - Fix bug in view dba_olap2_aggregation_uses which REM mistakenly was using all views instead of dba views in REM from clause. Bug related to fixing cwm2 pl/sql bugs for REM aggregation. REM mstasiew 12/04/03 - REM mstasiew 12/02/03 - 3002925 REM dbardwel 11/13/03 - Fix missing outer-join in entity_parameter view REM cdalessi 10/21/03 - change refs to cwmlite REM dbardwel 10/14/03 - Updates to union views for cwm1 cube-related REM views. Need to make sure there is a cwm$dimension REM row for all dimensions in cube-related views REM dbardwel 09/30/03 - updates for dba_olap2_entity_ext_parms for REM default member list dimension support REM dbardwel 09/10/03 - 10g descriptor views for ODBO support REM mstasiew 08/29/03 - REM dbardwel 06/18/03 - Complete fix to outer-join item where we do not want REM to display standalone RDBMS Dimension objects as valid REM cwm 1 metadata. REM dbardwel 06/16/03 - Hidden hierarchies to return no hier name. Updates to REM view all$olap2_FactTblKeyMaps per Mark REM dbardwel 06/11/03 - REM dbardwel 06/10/03 - Fix bug 2460744 REM dbardwel 06/10/03 - Hidden hierarchy's name should be null in mapping views REM dbardwel 06/01/03 - Backport Aggregation views REM mstasiew 05/15/03 - REM dbardwel 05/02/03 - Fix union level attr map bug this fix was REM not made in main line and since this file REM was backported from main, the fix was missing. REM Also fix to cwm2 join key column uses view REM mstasiew 03/19/03 - REM dbardwel 01/27/03 - Add short_description where needed REM mstasiew 12/17/02 - REM mstasiew 12/16/02 - REM dbardwel 01/04/02 - dbardwel_txn101266 REM dbardwel 01/03/02 created. REM mstasiew 01/31/02 mstasiew_txn101768 REM mstasiew 02/01/02 mstasiew_txn101798 REM dbardewl 02/06/02 fix possible classification view problem with 9i1 and 9i2 REM dbardwel 02/07/02 fixed missing outer-join from last fix REM ************************************************************* REM REM new CWM1 API Views REM REM New version of cube view adding MV Summary Code REM This version needed in union 9i2 view CREATE OR REPLACE VIEW olapsys.dba$olap1_cubes AS SELECT DISTINCT sch.physicalname owner , cub.physicalname cube_name , cwm$util.cube_invalid(cub.irid) invalid , cub.displayname display_name , cub.description short_description , cub.description description , nvl(cub.mvsummarycode, 'RU') mv_summarycode FROM dba_users u , cwm$model sch , cwm$cube cub , cwm$cubedimensionuse cdu , cwm$dimension cd , dba_users du , sys.obj$ do WHERE u.username = sch.physicalname AND sch.irid = cub.datamodel_irid AND cdu.cube_irid = cub.irid AND cdu.dimension_owner = du.username AND cdu.dimension_name = do.name AND du.user_id = do.owner# AND do.obj# = cd.irid AND do.type# = 43 AND cd.irid = cdu.abstractdimension_irid WITH READ ONLY / CREATE OR REPLACE VIEW olapsys.dba$olapmr_dimensions AS SELECT u.username owner, o.name dimension_name, dim.pluralname plural_name, dim.displayname display_name, dim.description short_description, dim.description description, display_hierarchy_name default_display_hierarchy, o.status status FROM sys.dim$ d, dba_users u, sys.obj$ o, cwm$dimension dim WHERE o.type# = 43 AND u.user_id = o.owner# AND o.obj# = d.obj# AND d.obj# = dim.irid WITH READ ONLY / CREATE OR REPLACE VIEW olapsys.dba$olapmr_dim_attributes AS SELECT u.username owner, o.name dimension_name, att.physicalname attribute_name, att.displayname display_name, att.description short_description, att.description description, ce.classification_irid desc_id FROM dba_users u, sys.obj$ o, cwm$dimensionattribute att, cwm$classificationentry ce WHERE o.type# = 43 AND u.user_id = o.owner# AND att.itemcontainer_irid = o.obj# AND att.irid = ce.element_irid(+) and ce.name (+) = 'DIMENSION ATTRIBUTE' WITH READ ONLY / REM REM Level Key Column Mappings for CWM1 Hierarchical Dimensions REM CREATE OR REPLACE VIEW olapsys.dba$olapmr_dim_levels_keymaps AS SELECT /*+ORDERED*/ u.username owner, d.name dimension_name, h.hiername hierarchy_name, dl.levelname level_name, NVL(lev.displayname,dl.levelname) display_name, NVL(lev.description,dl.levelname) short_description, NVL(lev.description,dl.levelname) description, hl.pos# pos, tu.username table_owner, t.name table_name, c.name column_name, decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(c.scale, null, decode(c.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'), 105, 'MLSLABEL', 106, 'MLSLABEL', 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 178, 'TIME(' ||c.spare1|| ')', 179, 'TIME(' ||c.spare1|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||c.spare1|| ')', 181, 'TIMESTAMP(' ||c.spare1|| ')' || ' WITH TIME ZONE', 182, 'INTERVAL YEAR(' ||c.spare2||') TO MONTH', 183, 'INTERVAL DAY(' ||c.spare2||') TO SECOND(' || c.spare1 || ')', 208, 'UROWID', 'UNDEFINED') data_type , decode(c.length, null, 0, c.length) data_length , decode(c.precision#, null, 0, c.precision#) data_precision, k.keypos# column_position FROM sys.obj$ d, dba_users u, sys.hier$ h, sys.hierlevel$ hl, sys.dimlevel$ dl, sys.dimlevelkey$ k, sys.obj$ t, dba_users tu, cwm$level lev, sys.col$ c WHERE d.type# = 43 AND /* DIMENSION */ u.user_id = d.owner# AND d.obj# = h.dimobj# AND d.obj# = hl.dimobj# AND h.hierid# = hl.hierid# AND hl.dimobj# = dl.dimobj# AND hl.levelid# = dl.levelid# AND dl.dimobj# = k.dimobj# AND dl.levelid# = k.levelid# AND k.detailobj# = t.obj# AND t.owner# = tu.user_id AND dl.dimobj# = lev.dimension_irid AND dl.levelname = lev.physicalname (+) AND k.detailobj# = c.obj# AND k.col# = c.col# / REM 9i1 view for dba$olap1_fact_level_uses CREATE OR REPLACE VIEW dba$olap1_fact_level_uses AS SELECT distinct sch.physicalname owner , cub.physicalname cube_name , cdu.dimension_owner , cdu.dimension_name , cdu.name dimension_alias , flu.level_name , fu.object_owner fact_table_owner , fu.object_name fact_table_name , flu.foreign_key_name FROM dba_users u , cwm$model sch , cwm$cube cub , cwm$cubedimensionuse cdu , cwm$factleveluse flu , cwm$facttablemap fm , cwm$factuse fu , cwm$dimension cd , dba_users du , sys.obj$ do WHERE u.username = sch.physicalname AND sch.irid = cub.datamodel_irid AND cub.irid = cdu.cube_irid AND cdu.irid = flu.cubedimensionuse_irid AND fm.mapcontainer_irid = cub.irid AND fu.operation_irid_1 = fm.irid AND cdu.dimension_owner = du.username AND cdu.dimension_name = do.name AND du.user_id = do.owner# AND do.obj# = cd.irid AND do.type# = 43 AND cd.irid = cdu.abstractdimension_irid with read only / REM REM Fact Table Key Column Mappings for CWM1 REM create or replace view olapsys.dba$olapmr_FactTblKeyMaps as select a.owner owner, a.cube_name cube_name, a.dimension_owner dimension_owner, a.dimension_name dimension_name, a.level_name level_name, a.fact_table_owner fact_table_owner, a.fact_table_name fact_table_name, fk.column_name column_name, fk.position column_position, b.mv_summarycode mv_summary_code from olapsys.dba$olap1_fact_level_uses a, olapsys.dba$olap1_cubes b, (select u.username table_owner, t.name table_name, c.name key_name, col.name column_name, ccol.pos# position from dba_users u, sys.obj$ t, sys.con$ c, sys.cdef$ cd, sys.col$ col, sys.ccol$ ccol where u.user_id = c.owner# and c.con# = cd.con# and cd.con# = ccol.con# and cd.obj# = t.obj# and ccol.intcol# = col.intcol# and col.obj# = t.obj# and cd.type# in (2,3,4) and t.type# in (2,4)) fk where a.fact_table_owner = fk.table_owner and a.fact_table_name = fk.table_name and a.foreign_key_name = fk.key_name and a.owner = b.owner and a.cube_name = b.cube_name with read only / REM REM Hierarchical Dimensions from CWM1 REM CREATE OR REPLACE VIEW olapsys.dba$olap9i1_hier_dimensions AS SELECT distinct u.username owner, o.name dimension_name, dim.pluralname plural_name, dim.displayname display_name, dim.description short_description, dim.description description, display_hierarchy_name default_display_hierarchy, (case when ce.classification_irid = 28 then 'Time' else 'Other' end) descriptor_value FROM sys.dim$ d, dba_users u, sys.obj$ o, olapsys.cwm$dimension dim, sys.hier$ h, olapsys.cwm$classificationentry ce WHERE o.type# = 43 AND u.user_id = o.owner# AND o.obj# = d.obj# AND d.obj# = dim.irid AND o.obj# = h.dimobj# AND dim.irid = ce.element_irid (+) and ce.name (+) = 'DIMENSION' WITH READ ONLY / REM ************************************************************* REM REM All the CWM2 API Views REM Note: the union VWM2 API views appear later in this file. REM REM REM AW Views from CWM2 REM create or replace view olapsys.dba$olap2_awviews as select awv.view_owner view_owner, awv.view_name view_name, col.name rowtocellcol_name, awv.aw_owner aw_owner, awv.aw_name aw_name from olapsys.cwm2$awviews awv, sys.col$ col where awv.table_id = col.obj# (+) and awv.rowtocellcol_id = col.col# (+) with read only / create or replace view olapsys.dba$olap2_awviewcols as select awv.view_owner view_owner, awv.view_name view_name, col.name column_name, awvc.awobject awobject from olapsys.cwm2$awviews awv, olapsys.cwm2$awviewcols awvc, sys.col$ col where awvc.awviews_irid = awv.irid and awv.table_id = col.obj# and awvc.column_id = col.col# with read only / REM REM Hierarchical Dimensions from CWM2 REM CREATE OR REPLACE VIEW olapsys.dba$olap9i2_hier_dimensions AS SELECT distinct dim.owner owner, dim.name dimension_name, dim.pluralname plural_name, dim.displayname display_name, dim.shortdescription short_description, dim.description description, h.name default_display_hierarchy, (case when ce.classification_irid = 28 then 'Time' else 'Other' end) descriptor_value FROM olapsys.cwm2$dimension dim, olapsys.cwm2$hierarchy h, olapsys.cwm$classificationentry ce WHERE (dim.defaulthier_irid = h.irid or dim.irid in (select irid from olapsys.cwm2$hierarchy)) and h.hidden = 'N' AND dim.irid = ce.element_irid (+) and ce.name (+) = 'DIMENSION2' WITH READ ONLY / REM REM Cube Model Views for CwM2 REM REM dba$olap2_Cubes create or replace view olapsys.dba$olap2_Cubes as select c.owner owner, c.name cube_name, c.invalid invalid, c.displayname display_name, c.shortdescription short_description, c.description description, nvl(c.MVSummaryCode, 'GS') mv_summarycode from olapsys.CwM2$Cube c with read only / REM dba$olap2_Cube_Measures create or replace view olapsys.dba$olap2_Cube_Measures as select c.owner owner, c.name cube_name, m.name measure_name, m.displayname display_name, m.shortdescription short_description, m.description description from olapsys.CwM2$Cube c, olapsys.CwM2$Measure m where c.irid = m.cube_irid with read only / REM dba$olap2_Cube_Dim_Uses create or replace view olapsys.dba$olap2_Cube_Dim_Uses as select cdu.irid cube_dimension_use_id, c.owner owner, c.name cube_name, d.owner dimension_owner, d.name dimension_name, null dimension_alias, (case when h.hidden = 'N' then h.name else null end) default_calc_hierarchy_name, null dependent_on_dim_use_id from olapsys.CwM2$CubeDimensionUse cdu, olapsys.CwM2$Cube c, olapsys.CwM2$Dimension d, olapsys.CwM2$hierarchy h where c.irid = cdu.cube_irid and d.irid = cdu.dimension_irid and cdu.DefaultCalcHier_IRID = h.irid (+) with read only / REM dba$olap2_Cube_Meas_Dim_Uses create or replace view olapsys.dba$olap2_Cube_Meas_Dim_Uses as select c.owner owner, c.name cube_name, m.name measure_name, d.owner dimension_owner, d.name dimension_name, null dimension_alias, null default_aggr_function_use_id from olapsys.CwM2$Cube c, olapsys.CwM2$Dimension d, olapsys.CwM2$Measure m, olapsys.CwM2$CubeDimensionUse cdu where c.irid = cdu.cube_irid and d.irid = cdu.dimension_irid and m.cube_irid = c.irid with read only / REM REM Dimension Model for CwM2 views REM REM dba$olap2_Dimensions create or replace view olapsys.dba$olap2_Dimensions as select d.owner owner, d.name dimension_name, d.pluralname plural_name, d.displayname display_name, d.shortdescription short_description, d.description description, (case when h.hidden = 'N' then h.name else null end) default_display_hierarchy, d.invalid invalid, d.dimensiontype dimension_type from olapsys.CwM2$dimension d, olapsys.CwM2$hierarchy h where d.DefaultHier_IRID = h.irid (+) with read only / REM dba$olap2_Dim_Hierarchies create or replace view olapsys.dba$olap2_Dim_Hierarchies as select d.owner owner, d.name dimension_name, h.name hierarchy_name, h.displayname display_name, h.shortdescription short_description, h.description description, h.solvedcode solved_code from olapsys.CwM2$dimension d, olapsys.CwM2$hierarchy h where d.irid = h.dimension_irid and h.hidden = 'N' with read only / REM dba$olap2_Dim_Levels create or replace view olapsys.dba$olap2_Dim_Levels as select d.owner owner, d.name dimension_name, l.name level_name, l.displayname display_name, l.shortdescription short_description, l.description description from olapsys.CwM2$dimension d, olapsys.CwM2$level l where d.irid = l.dimension_irid with read only / REM dba$olap2_Dim_Hier_Level_Uses create or replace view olapsys.dba$olap2_Dim_Hier_Level_Uses as select d.owner owner, d.name dimension_name, (case when h.hidden = 'N' then h.name else null end) hierarchy_name, pl.name parent_level_name, cl.name child_level_name, hlr.leveldepth position from olapsys.CwM2$dimension d, olapsys.CwM2$level pl, olapsys.CwM2$level cl, olapsys.CwM2$HierLevelRel hlr, olapsys.CwM2$Hierarchy h where d.irid = pl.dimension_irid and d.irid = cl.dimension_irid and h.dimension_irid = d.irid and hlr.hierarchy_IRID = h.irid and hlr.parentlevel_irid = pl.irid and hlr.childlevel_irid = cl.irid with read only / REM dba$olap2_Dim_Attributes create or replace view olapsys.dba$olap2_Dim_Attributes as select d.owner owner, d.name dimension_name, da.name attribute_name, da.displayname display_name, da.shortdescription short_description, da.description description, ce.classification_irid desc_id from olapsys.CwM2$dimension d, olapsys.CwM2$dimensionattribute da, olapsys.cwm$classificationentry ce where da.dimension_irid = d.irid and da.irid = ce.element_irid (+) and ce.name (+) = 'DIMENSION ATTRIBUTE2' with read only / REM dba$olap2_Dim_Attr_Uses create or replace view olapsys.dba$olap2_Dim_Attr_Uses as select d.owner owner, d.name dimension_name, da.name dim_attribute_name, l.name level_name, la.name lvl_attribute_name from olapsys.CwM2$dimension d, olapsys.CwM2$dimensionattribute da, olapsys.CwM2$levelattribute la, olapsys.CwM2$level l where da.dimension_irid = d.irid and la.dimattr_irid = da.irid and la.level_irid = l.irid with read only / REM dba$olap2_Dim_Level_Attributes create or replace view olapsys.dba$olap2_Dim_Level_Attributes as select d.owner owner, d.name dimension_name, la.name attribute_name, la.displayname display_name, la.shortdescription short_description, la.description description, l.name determined_by_level_name from olapsys.CwM2$dimension d, olapsys.CwM2$levelattribute la, olapsys.CwM2$Level l where d.irid = la.dimension_irid and la.level_irid = l.irid and l.dimension_irid = d.irid with read only / REM dba$olap2_Cube_Measure_Maps create or replace view olapsys.dba$olap2_Cube_Measure_Maps as select distinct c.owner owner, c.name cube_name, m.name measure_name, fdhm.irid dim_hier_combo_id, u.username fact_table_owner, o.name fact_table_name, col.name column_name from olapsys.CwM2$Cube c, olapsys.CwM2$Measure m, olapsys.CwM2$MeasureTableMap mtm, olapsys.CwM2$Dimension d, olapsys.CwM2$CubeDimensionUse cdu, olapsys.CwM2$FactDimHierMap fdhm, sys.obj$ o, dba_users u, sys.col$ col where c.irid = m.cube_irid and d.irid = cdu.dimension_irid and c.irid = cdu.cube_irid and m.irid = mtm.measure_irid and fdhm.cube_irid = c.irid and mtm.FactDimHier_IRID = fdhm.irid and mtm.factcolumn_ID = col.col# and mtm.facttablename_ID = o.obj# and o.owner# = u.user_id and o.obj# = col.obj# with read only / REM dba$olap2_Fact_Level_Uses create or replace view olapsys.dba$olap2_fact_level_uses as select c.owner owner, c.name cube_name, d.owner dimension_owner, d.name dimension_name, null dimension_alias, /* dimension alias */ (case when h.hidden = 'N' then h.name else null end) hierarchy_name, fdhm.irid dim_hier_combo_id, l.name level_name, u.username fact_table_owner, o.name fact_table_name, col.name column_name, decode(col.type#, 1, decode(col.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(col.scale, null, decode(col.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(col.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 96, decode(col.charsetform, 2, 'NCHAR', 'CHAR'), 105, 'MLSLABEL', 106, 'MLSLABEL', 112, decode(col.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 178, 'TIME(' ||col.spare1|| ')', 179, 'TIME(' ||col.spare1|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||col.spare1|| ')', 181, 'TIMESTAMP(' ||col.spare1|| ')' || ' WITH TIME ZONE', 182, 'INTERVAL YEAR(' ||col.spare2||') TO MONTH', 183, 'INTERVAL DAY(' ||col.spare2||') TO SECOND(' || col.spare1 || ')', 208, 'UROWID', 'UNDEFINED') data_type , decode(col.length, null, 0, col.length) data_length , decode(col.precision#, null, 0, col.precision#) data_precision, fkdhlm.position, fkdhm.dimensionkeymaptype dimension_keymap_type, null foreign_key_name from olapsys.CwM2$Cube c, olapsys.CwM2$Dimension d, olapsys.CwM2$Level l, olapsys.CwM2$CubeDimensionUse cdu, olapsys.CwM2$FactDimHierMap fdhm, olapsys.CwM2$FactDimHierTplsDtl fdhtd, olapsys.CwM2$FactKeyDimHierMap fkdhm, olapsys.CwM2$FactKeyDimHierLvlMap fkdhlm, olapsys.CwM2$Hierarchy h, sys.obj$ o, sys.col$ col, dba_users u where c.irid = cdu.cube_irid and d.irid = cdu.dimension_irid and l.dimension_irid = d.irid and h.dimension_irid = d.irid and fdhm.cube_irid = c.irid and fkdhm.factDimHier_IRID = fdhm.irid and fdhm.irid = fdhtd.factdimhier_irid and fkdhlm.factkeyDimHier_IRID = fkdhm.irid and fdhm.FactTableName_ID = o.obj# and o.owner# = u.user_id and fkdhlm.columnName_ID = col.col# and o.obj# = col.obj# and fkdhlm.level_irid = l.irid and fkdhlm.hierarchy_irid = fdhtd.hier_irid and fdhtd.hier_irid = h.irid with read only / REM REM Aggregation Operator view for 9i2 REM create or replace view olapsys.dba$olap2_agg_operator_uses as select c.owner owner, c.name cube_name, d.owner dimension_owner, d.name dimension_name, (case when h.hidden = 'N' then h.name else null end) hierarchy_name, fdhm.irid dim_hier_combo_id, f.name aggregation_name, fkdhm.aggorder aggregation_order from olapsys.CwM2$Cube c, olapsys.CwM2$Dimension d, olapsys.CwM2$CubeDimensionUse cdu, olapsys.CwM2$FactDimHierMap fdhm, olapsys.CwM2$FactDimHierTplsDtl fdhtd, olapsys.CwM2$FactKeyDimHierMap fkdhm, olapsys.CwM2$Hierarchy h, olapsys.cwm$Function f where c.irid = cdu.cube_irid and d.irid = cdu.dimension_irid and h.dimension_irid = d.irid and fdhm.cube_irid = c.irid and fkdhm.factDimHier_IRID = fdhm.irid and fkdhm.dimension_irid = d.irid and fdhm.irid = fdhtd.factdimhier_irid and h.irid = fdhtd.hier_irid and fkdhm.aggoperator_irid = f.irid with read only / REM REM Aggregation Weight By view for 9i2 REM create or replace view olapsys.dba$olap2_agg_weightby_uses as select c.owner owner, c.name cube_name, d.owner dimension_owner, d.name dimension_name, (case when h.hidden = 'N' then h.name else null end) hierarchy_name, fdhm.irid dim_hier_combo_id, f.name aggregation_name, fkdhm.aggorder aggregation_order, u.username table_owner, o.name table_name, col.name column_name from olapsys.CwM2$Cube c, olapsys.CwM2$Dimension d, olapsys.CwM2$CubeDimensionUse cdu, olapsys.CwM2$FactDimHierMap fdhm, olapsys.CwM2$FactDimHierTplsDtl fdhtd, olapsys.CwM2$FactKeyDimHierMap fkdhm, olapsys.CwM2$Hierarchy h, olapsys.cwm$Function f, sys.obj$ o, dba_users u, sys.col$ col where c.irid = cdu.cube_irid and d.irid = cdu.dimension_irid and h.dimension_irid = d.irid and fdhm.cube_irid = c.irid and fkdhm.factDimHier_IRID = fdhm.irid and fkdhm.dimension_irid = d.irid and fdhm.irid = fdhtd.factdimhier_irid and h.irid = fdhtd.hier_irid and fkdhm.aggoperator_irid = f.irid and u.user_id = o.owner# and o.obj# = col.obj# and col.col# = fkdhm.AggWeightCol_ID and col.obj# = fkdhm.AggWeightTbl_ID with read only / REM REM Aggregation Complete view REM create or replace view olapsys.dba$olap2_aggregation_uses as select aggop.owner owner, aggop.cube_name cube_name, aggop.dimension_owner dimension_owner, aggop.dimension_name dimension_name, aggop.hierarchy_name hierarchy_name, aggop.dim_hier_combo_id dim_hier_combo_id, aggop.aggregation_name aggregation_name, aggop.aggregation_order aggregation_order, aggwb.table_owner table_owner, aggwb.table_name table_name, aggwb.column_name column_name from olapsys.dba$olap2_agg_operator_uses aggop, olapsys.dba$olap2_agg_weightby_uses aggwb where aggop.owner = aggwb.owner (+) and aggop.cube_name = aggwb.cube_name (+) and aggop.dimension_owner = aggwb.dimension_owner (+) and aggop.dimension_name = aggwb.dimension_name (+) and aggop.hierarchy_name = aggwb.hierarchy_name (+) with read only / REM REM Dimension Mapping Model CwM2 views REM REM REM dba$olap2_join_key_column_uses create or replace view olapsys.dba$olap2_join_key_column_uses as select d.owner owner, d.name dimension_name, (case when h.hidden = 'N' then h.name else null end) hierarchy_name, l.name child_level_name, u.username table_owner, o.name table_name, c.name column_name, dhlm.position position, 'KEY' join_key_type /* join key type */ from olapsys.CwM2$dimension d, olapsys.CwM2$hierarchy h, olapsys.CwM2$level l, olapsys.CwM2$HierLevelRel hlr, olapsys.CwM2$DimHierLvlMap dhlm, dba_users u, sys.obj$ o, sys.col$ c where h.dimension_irid = d.irid and h.irid = hlr.hierarchy_irid and l.irid = hlr.childlevel_irid and dhlm.DimHierLvl_IRID = hlr.irid and dhlm.object_ID = o.obj# and dhlm.column_id = c.col# and o.obj# = c.obj# and o.owner# = u.user_id and dhlm.style = 'STAR' UNION ALL select d.owner owner, d.name dimension_name, (case when h.hidden = 'N' then h.name else null end) hierarchy_name, l.name child_level_name, u.username table_owner, o.name table_name, c.name column_name, dhlm.position position, 'FOREIGN KEY' join_key_type /* join key type */ from olapsys.CwM2$dimension d, olapsys.CwM2$hierarchy h, olapsys.CwM2$level l, olapsys.CwM2$HierLevelRel hlr, olapsys.CwM2$DimHierLvlMap dhlm, dba_users u, sys.obj$ o, sys.col$ c where h.dimension_irid = d.irid and h.irid = hlr.hierarchy_irid and l.irid = hlr.parentlevel_irid and dhlm.DimHierLvl_IRID = hlr.irid and dhlm.object_ID = o.obj# and dhlm.parentcolumn_id = c.col# and o.obj# = c.obj# and o.owner# = u.user_id and dhlm.style = 'SNOWFLAKE' with read only / REM dba$olap2_level_key_col_uses create or replace view olapsys.dba$olap2_level_key_col_uses as select d.owner owner, d.name dimension_name, (case when h.hidden = 'N' then h.name else null end) hierarchy_name, l.name child_level_name, u.username table_owner, o.name table_name, c.name column_name, dhlm.position position from olapsys.cwm2$dimension d, olapsys.cwm2$hierarchy h, olapsys.cwm2$level l, olapsys.cwm2$hierlevelrel hlr, olapsys.cwm2$dimhierlvlmap dhlm, dba_users u, sys.obj$ o, sys.col$ c where h.dimension_irid = d.irid and h.irid = hlr.hierarchy_irid and l.irid = hlr.childlevel_irid and dhlm.dimhierlvl_irid = hlr.irid and dhlm.object_id = o.obj# and dhlm.column_id = c.col# and o.obj# = c.obj# and o.owner# = u.user_id with read only / REM dba$olap2_Dim_Level_Attr_Maps create or replace view olapsys.dba$olap2_Dim_Level_Attr_Maps as select attr.owner owner, attr.dimension_name dimension_name, (case when attr.hidden = 'N' then attr.hierarchy_name else null end) hierarchy_name, attr.attribute_name attribute_name, attr.lvl_attribute_name lvl_attribute_name, attr.level_name level_name, attr.table_owner table_owner, attr.table_name table_name, attr.column_name column_name, attr.dtype dtype, attr.data_length data_length, attr.data_precision data_precision, (case when attr.dtype = 'NUMBER' then 0 when attr.dtype = 'DOUBLE' then 5 when attr.dtype = 'FLOAT' then 4 when attr.dtype = 'DATE' then 7 when attr.dtype = 'LONG' then 3 else 1 end) olap_api_data_type from ( select d.owner owner, d.name dimension_name, h.name hierarchy_name, da.name attribute_name, la.name lvl_attribute_name, l.name level_name, u.username table_owner, o.name table_name, col.name column_name, h.hidden hidden, decode(col.type#, 1, decode(col.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(col.scale, null, decode(col.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(col.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 96, decode(col.charsetform, 2, 'NCHAR', 'CHAR'), 105, 'MLSLABEL', 106, 'MLSLABEL', 112, decode(col.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 178, 'TIME(' ||col.spare1|| ')', 179, 'TIME(' ||col.spare1|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||col.spare1|| ')', 181, 'TIMESTAMP(' ||col.spare1|| ')' || ' WITH TIME ZONE', 182, 'INTERVAL YEAR(' ||col.spare2||') TO MONTH', 183, 'INTERVAL DAY(' ||col.spare2||') TO SECOND(' || col.spare1 || ')', 208, 'UROWID', 'UNDEFINED') dtype , decode(col.length, null, 0, col.length) data_length , decode(col.precision#, null, 0, col.precision#) data_precision from olapsys.CwM2$Dimension d, olapsys.cwm2$dimensionattribute da, olapsys.cwm2$level l, olapsys.CwM2$LevelAttribute la, olapsys.CwM2$LevelAttributeMap lam, olapsys.CwM2$Hierarchy h, olapsys.CwM2$HierLevelRel hlr, dba_users u, sys.obj$ o, sys.col$ col where d.irid = da.dimension_irid and la.dimattr_irid = da.irid and la.level_irid = l.irid and la.irid = lam.levelattr_irid and lam.hierlvlrel_irid = hlr.irid and hlr.hierarchy_irid = h.irid and lam.Table_ID = o.obj# and lam.Column_ID = col.col# and o.obj# = col.obj# and o.owner# = u.user_id) attr with read only / REM dba$olap2_dim_levels_keymaps CREATE OR REPLACE VIEW olapsys.dba$olap2_dim_levels_keymaps AS select d.owner owner, d.name dimension_name, (case when h.hidden = 'N' then h.name else null end) hierarchy_name, l.name level_name, NVL(L.DISPLAYNAME, L.NAME) display_name, NVL(L.SHORTDESCRIPTION, L.NAME) short_description, NVL(L.DESCRIPTION, L.NAME) description, hlr.leveldepth pos, u.username table_owner, o.name table_name, c.name column_name, decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(c.scale, null, decode(c.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'), 105, 'MLSLABEL', 106, 'MLSLABEL', 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 178, 'TIME(' ||c.spare1|| ')', 179, 'TIME(' ||c.spare1|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||c.spare1|| ')', 181, 'TIMESTAMP(' ||c.spare1|| ')' || ' WITH TIME ZONE', 182, 'INTERVAL YEAR(' ||c.spare2||') TO MONTH', 183, 'INTERVAL DAY(' ||c.spare2||') TO SECOND(' || c.spare1 || ')', 208, 'UROWID', 'UNDEFINED') data_type , decode(c.length, null, 0, c.length) data_length , decode(c.precision#, null, 0, c.precision#) data_precision, dhlm.position column_position from olapsys.cwm2$dimension d, olapsys.cwm2$hierarchy h, olapsys.cwm2$level l, olapsys.cwm2$hierlevelrel hlr, olapsys.cwm2$dimhierlvlmap dhlm, dba_users u, sys.obj$ o, sys.col$ c where h.dimension_irid = d.irid and h.irid = hlr.hierarchy_irid and l.irid = hlr.childlevel_irid and dhlm.dimhierlvl_irid = hlr.irid and dhlm.object_id = o.obj# and dhlm.column_id = c.col# and o.obj# = c.obj# and o.owner# = u.user_id with read only / REM REM Fact Tbl Fact Col Maps for CWM2 REM create or replace view olapsys.dba$olap2_FactTblFctMaps as select cmm.owner owner, cmm.cube_name cube_name, cmm.measure_name measure_name, cmm.dim_hier_combo_id dim_hier_combo_id, cmm.fact_table_owner fact_table_owner, cmm.fact_table_name fact_table_name, cmm.column_name column_name, c.data_type data_type, c.data_length data_length, c.data_precision data_precision, (case when c.data_type = 'NUMBER' then 0 when c.data_type = 'DOUBLE' then 5 when c.data_type = 'FLOAT' then 4 when c.data_type = 'DATE' then 7 when c.data_type = 'LONG' then 3 else 1 end) olap_api_data_type from olapsys.dba$olap2_cube_measure_maps cmm, dba_olap_columns c where cmm.fact_table_owner = c.owner and cmm.fact_table_name = c.table_name and cmm.column_name = c.column_name / REM Classification views for CwM2 metadata. create or replace view olapsys.dba$olap2_catalogs as select c.irid catalog_id, c.name catalog_name, cp.irid parent_catalog_id, c.description description from cwm$classification c, cwm$classification cp, cwm$classificationtype cty, cwm$classificationentry ce where cty.irid = c.classificationtype_irid and cty.name = 'ORACLE_OLAP2_CATALOG' and c.irid = ce.element_irid and ce.name = 'CATALOG2' and ce.classification_irid = cp.irid UNION ALL select c.irid catalog_id, c.name catalog_name, null parent_catalog_id, c.description description from cwm$classification c, cwm$classificationtype cty where cty.irid = c.classificationtype_irid and cty.name = 'ORACLE_OLAP2_CATALOG' and NOT EXISTS (select null from cwm$classificationentry ce where ce.name = 'CATALOG2' and ce.element_irid = c.irid) with read only / REM dba$olap2_entity_desc_uses create or replace view olapsys.dba$olap2_entity_desc_uses as select ce.classification_irid descriptor_id, cub.owner entity_owner, cub.name entity_name, null child_entity_name, null secondary_child_entity_name from /* CUBE2 */ cwm$classification c, cwm$classificationentry ce, cwm$classificationtype cty, cwm2$cube cub where cty.irid = c.classificationtype_irid and cty.name <> 'ORACLE_OLAP2_CATALOG' and c.irid = ce.classification_irid and ce.name = 'CUBE2' and ce.element_irid = cub.irid UNION ALL select ce.classification_irid descriptor_id, cub.owner entity_owner, cub.name entity_name, meas.name child_entity_name, null secondary_child_entity_name from /* MEASURE2 */ cwm$classification c, cwm$classificationentry ce, cwm$classificationtype cty, cwm2$cube cub, cwm2$measure meas where cty.irid = c.classificationtype_irid and cty.name <> 'ORACLE_OLAP2_CATALOG' and c.irid = ce.classification_irid and ce.name = 'MEASURE2' and ce.element_irid = meas.irid and meas.cube_irid = cub.irid UNION ALL select ce.classification_irid descriptor_id, dim.owner entity_owner, dim.name entity_name, dat.name child_entity_name, null secondary_child_entity_name from /* DIMENSION ATTRIBUTE2 */ cwm$classification c, cwm$classificationentry ce, cwm$classificationtype cty, cwm2$dimension dim, cwm2$dimensionattribute dat where cty.irid = c.classificationtype_irid and cty.name <> 'ORACLE_OLAP2_CATALOG' and c.irid = ce.classification_irid and ce.name = 'DIMENSION ATTRIBUTE2' and ce.element_irid = dat.irid and dat.dimension_irid = dim.irid UNION ALL select ce.classification_irid descriptor_id, dim.owner entity_owner, dim.name entity_name, lvl.name child_entity_name, lat.name secondary_child_entity_name from /* LEVEL ATTRIBUTE2 */ cwm$classification c, cwm$classificationentry ce, cwm$classificationtype cty, cwm2$dimension dim, cwm2$dimensionattribute dat, cwm2$levelattribute lat, cwm2$level lvl where cty.irid = c.classificationtype_irid and cty.name not in ('ORACLE_OLAP2_CATALOG', 'ORACLE_OLAP_CATALOG') and c.irid = ce.classification_irid and ce.name = 'LEVEL ATTRIBUTE2' and ce.element_irid = lat.irid and lat.dimattr_irid = dat.irid and dat.dimension_irid = dim.irid and lvl.irid = lat.level_irid UNION ALL select ce.classification_irid descriptor_id, dim.owner entity_owner, dim.name entity_name, ce.secondary_object_name child_entity_name, null secondary_child_entity_name from /* DIMENSION2 */ cwm$classification c, cwm$classificationentry ce, cwm$classificationtype cty, cwm2$dimension dim where cty.irid = c.classificationtype_irid and cty.name not in ('ORACLE_OLAP2_CATALOG', 'ORACLE_OLAP_CATALOG') and c.irid = ce.classification_irid and ce.name = 'DIMENSION2' and ce.element_irid = dim.irid UNION ALL select ce.classification_irid descriptor_id, dim.owner entity_owner, dim.name entity_name, hier.name child_entity_name, null secondary_child_entity_name from /* HIERARCHY2 */ cwm$classification c, cwm$classificationentry ce, cwm$classificationtype cty, cwm2$dimension dim, cwm2$hierarchy hier where cty.irid = c.classificationtype_irid and cty.name not in ('ORACLE_OLAP2_CATALOG', 'ORACLE_OLAP_CATALOG') and c.irid = ce.classification_irid and ce.name = 'HIERARCHY2' and dim.irid = hier.dimension_irid and ce.element_irid = hier.irid UNION ALL select ce.classification_irid descriptor_id, dim.owner entity_owner, dim.name entity_name, lvl.name child_entity_name, null secondary_child_entity_name from /* LEVEL2 */ cwm$classification c, cwm$classificationentry ce, cwm$classificationtype cty, cwm2$dimension dim, cwm2$level lvl where cty.irid = c.classificationtype_irid and cty.name not in ('ORACLE_OLAP2_CATALOG', 'ORACLE_OLAP_CATALOG') and c.irid = ce.classification_irid and ce.name = 'LEVEL2' and dim.irid = lvl.dimension_irid and ce.element_irid = lvl.irid with read only / REM dba$olap2_catalog_entity_uses create or replace view olapsys.dba$olap2_catalog_entity_uses as select ce.classification_irid catalog_id, cub.owner entity_owner, cub.name entity_name, meas.name child_entity_name from /* MEASURES */ cwm$classification c, cwm$classificationentry ce, cwm$classificationtype cty, cwm2$measure meas, cwm2$cube cub where cty.irid = c.classificationtype_irid and cty.name = 'ORACLE_OLAP2_CATALOG' and c.irid = ce.classification_irid and ce.name = 'MEASURE2' and ce.element_irid = meas.irid and meas.cube_irid = cub.irid with read only / REM dba$olap2_fact_table_gid create or replace view olapsys.dba$olap2_fact_table_gid as select distinct c.owner owner, c.name cube_name, d.owner dimension_owner, d.name dimension_name, (case when h.hidden = 'N' then h.name else null end) hierarchy_name, fdhm.irid dim_hier_combo_id, u.username fact_table_owner, o.name fact_table_name, col.name column_name, decode(col.type#, 1, decode(col.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(col.scale, null, decode(col.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(col.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 96, decode(col.charsetform, 2, 'NCHAR', 'CHAR'), 105, 'MLSLABEL', 106, 'MLSLABEL', 112, decode(col.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 178, 'TIME(' ||col.spare1|| ')', 179, 'TIME(' ||col.spare1|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||col.spare1|| ')', 181, 'TIMESTAMP(' ||col.spare1|| ')' || ' WITH TIME ZONE', 182, 'INTERVAL YEAR(' ||col.spare2||') TO MONTH', 183, 'INTERVAL DAY(' ||col.spare2||') TO SECOND(' || col.spare1 || ')', 208, 'UROWID', 'UNDEFINED') data_type , decode(col.length, null, 0, col.length) data_length , decode(col.precision#, null, 0, col.precision#) data_precision from olapsys.CwM2$Cube c, olapsys.CwM2$Dimension d, olapsys.CwM2$CubeDimensionUse cdu, olapsys.CwM2$FactDimHierMap fdhm, olapsys.CwM2$FactKeyDimHierMap fkdhm, olapsys.cwm2$factdimhiertplsdtl fdhtd, olapsys.cwm2$hierarchy h, sys.obj$ o, sys.col$ col, dba_users u where c.irid = cdu.cube_irid and d.irid = cdu.dimension_irid and fdhm.cube_irid = c.irid and fkdhm.factDimHier_IRID = fdhm.irid and fkdhm.Dimension_IRID = d.irid and fdhtd.factdimhier_irid = fdhm.irid and fdhtd.hier_irid = h.irid and h.dimension_irid = d.irid and fdhm.FactTableName_ID = o.obj# and o.owner# = u.user_id and fkdhm.gidcolumnname_id = col.col# and o.obj# = col.obj# with read only / REM REM Solved-Rollup Style Fact Table Key Column Mappings for CWM2 REM create or replace view olapsys.dba$olap2_ruFctTblKyMaps as select flu.owner owner, flu.cube_name cube_name, flu.dim_hier_combo_id dim_hier_combo_id, flu.dimension_owner dimension_owner, flu.dimension_name dimension_name, flu.hierarchy_name hierarchy_name, flu.fact_table_owner fact_table_owner, flu.fact_table_name fact_table_name, flu.column_name column_name, ftg.column_name gid_column_name from olapsys.dba$olap2_fact_level_uses flu, olapsys.dba$olap2_dim_levels_keymaps lkm, olapsys.dba$olap2_fact_table_gid ftg where flu.dimension_keymap_type = 'RU' and flu.dimension_owner = lkm.owner and flu.dimension_name = lkm.dimension_name and flu.hierarchy_name = lkm.hierarchy_name and flu.level_name = lkm.level_name and flu.owner = ftg.owner and flu.cube_name = ftg.cube_name and flu.dimension_owner = ftg.dimension_owner and flu.dimension_name = ftg.dimension_name and flu.hierarchy_name = ftg.hierarchy_name and flu.dim_hier_combo_id = ftg.dim_hier_combo_id and flu.fact_table_owner = ftg.fact_table_owner and flu.fact_table_name = ftg.fact_table_name order by flu.owner asc, flu.cube_name asc, flu.dim_hier_combo_id asc, flu.dimension_owner asc, flu.dimension_name asc, flu.hierarchy_name asc, lkm.pos desc / REM REM Unsolved and Solved-ET Style Fact Tbl Key Col Maps for CWM2 REM Mark's fixed version with inlining create or replace view olapsys.dba$olap2_FactTblKeyMaps as select flu.owner owner, flu.cube_name cube_name, flu.dim_hier_combo_id dim_hier_combo_id, flu.dimension_owner dimension_owner, flu.dimension_name dimension_name, (case when flu.hidden = 'N' then flu.hierarchy_name else null end) hierarchy_name, flu.level_name level_name, flu.fact_table_owner fact_table_owner, flu.fact_table_name fact_table_name, flu.column_name column_name, flu.data_type column_data_type, flu.data_length column_data_length, flu.data_precision column_data_precision, ftg.column_name gid_column_name, ftg.data_type gid_column_data_type, ftg.data_length gid_column_data_length, ftg.data_precision gid_column_data_precision, (CASE WHEN dimension_keymap_type = 'ET' THEN 1 WHEN dimension_keymap_type = 'LL' THEN 2 ELSE 0 END) dimension_keymap_type, c.mv_summarycode mv_summarycode, flu.position column_position from ( select c.owner owner, c.name cube_name, d.owner dimension_owner, d.name dimension_name, null dimension_alias, /* dimension alias */ h.name hierarchy_name, fdhm.irid dim_hier_combo_id, l.name level_name, u.username fact_table_owner, o.name fact_table_name, col.name column_name, decode(col.type#, 1, decode(col.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(col.scale, null, decode(col.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(col.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 96, decode(col.charsetform, 2, 'NCHAR', 'CHAR'), 105, 'MLSLABEL', 106, 'MLSLABEL', 112, decode(col.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 178, 'TIME(' ||col.spare1|| ')', 179, 'TIME(' ||col.spare1|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||col.spare1|| ')', 181, 'TIMESTAMP(' ||col.spare1|| ')' || ' WITH TIME ZONE', 182, 'INTERVAL YEAR(' ||col.spare2||') TO MONTH', 183, 'INTERVAL DAY(' ||col.spare2||') TO SECOND(' || col.spare1 || ')', 208, 'UROWID', 'UNDEFINED') data_type , decode(col.length, null, 0, col.length) data_length , decode(col.precision#, null, 0, col.precision#) data_precision, fkdhlm.position, fkdhm.dimensionkeymaptype dimension_keymap_type, null foreign_key_name, h.hidden hidden from olapsys.CwM2$Cube c, olapsys.CwM2$Dimension d, olapsys.CwM2$Level l, olapsys.CwM2$CubeDimensionUse cdu, olapsys.CwM2$FactDimHierMap fdhm, olapsys.CwM2$FactDimHierTplsDtl fdhtd, olapsys.CwM2$FactKeyDimHierMap fkdhm, olapsys.CwM2$FactKeyDimHierLvlMap fkdhlm, olapsys.CwM2$Hierarchy h, sys.obj$ o, sys.col$ col, dba_users u where c.irid = cdu.cube_irid and d.irid = cdu.dimension_irid and l.dimension_irid = d.irid and h.dimension_irid = d.irid and fdhm.cube_irid = c.irid and fkdhm.factDimHier_IRID = fdhm.irid and fdhm.irid = fdhtd.factdimhier_irid and fkdhlm.factkeyDimHier_IRID = fkdhm.irid and fdhm.FactTableName_ID = o.obj# and o.owner# = u.user_id and fkdhlm.columnName_ID = col.col# and o.obj# = col.obj# and fkdhlm.level_irid = l.irid and fkdhlm.hierarchy_irid = fdhtd.hier_irid and fdhtd.hier_irid = h.irid ) flu, ( select distinct c.owner owner, c.name cube_name, d.owner dimension_owner, d.name dimension_name, h.name hierarchy_name, fdhm.irid dim_hier_combo_id, u.username fact_table_owner, o.name fact_table_name, col.name column_name, decode(col.type#, 1, decode(col.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(col.scale, null, decode(col.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(col.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 96, decode(col.charsetform, 2, 'NCHAR', 'CHAR'), 105, 'MLSLABEL', 106, 'MLSLABEL', 112, decode(col.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 178, 'TIME(' ||col.spare1|| ')', 179, 'TIME(' ||col.spare1|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||col.spare1|| ')', 181, 'TIMESTAMP(' ||col.spare1|| ')' || ' WITH TIME ZONE', 182, 'INTERVAL YEAR(' ||col.spare2||') TO MONTH', 183, 'INTERVAL DAY(' ||col.spare2||') TO SECOND(' || col.spare1 || ')', 208, 'UROWID', 'UNDEFINED') data_type , decode(col.length, null, 0, col.length) data_length , decode(col.precision#, null, 0, col.precision#) data_precision from olapsys.CwM2$Cube c, olapsys.CwM2$Dimension d, olapsys.CwM2$CubeDimensionUse cdu, olapsys.CwM2$FactDimHierMap fdhm, olapsys.CwM2$FactKeyDimHierMap fkdhm, olapsys.cwm2$factdimhiertplsdtl fdhtd, olapsys.cwm2$hierarchy h, sys.obj$ o, sys.col$ col, dba_users u where c.irid = cdu.cube_irid and d.irid = cdu.dimension_irid and fdhm.cube_irid = c.irid and fkdhm.factDimHier_IRID = fdhm.irid and fkdhm.Dimension_IRID = d.irid and fdhtd.factdimhier_irid = fdhm.irid and fdhtd.hier_irid = h.irid and h.dimension_irid = d.irid and fdhm.FactTableName_ID = o.obj# and o.owner# = u.user_id and fkdhm.gidcolumnname_id = col.col# and o.obj# = col.obj# ) ftg, olapsys.dba$olap2_cubes c where flu.dimension_keymap_type <> 'RU' and flu.owner = ftg.owner (+) and flu.cube_name = ftg.cube_name (+) and flu.dimension_owner = ftg.dimension_owner (+) and flu.dimension_name = ftg.dimension_name (+) and flu.hierarchy_name = ftg.hierarchy_name (+) and flu.dim_hier_combo_id = ftg.dim_hier_combo_id (+) and flu.fact_table_owner = ftg.fact_table_owner (+) and flu.fact_table_name = ftg.fact_table_name (+) and flu.owner = c.owner and flu.cube_name = c.cube_name / REM dba$olap2_hier_custom_sort create or replace view olapsys.dba$olap2_hier_custom_sort as select d.owner owner, d.name dimension_name, (case when h.hidden = 'N' then h.name else null end) hierarchy_name, u.username table_owner, o.name table_name, c.name column_name, hcs.position, hcs.SortPos sort_pos, hcs.SortOrder sort_order, hcs.NullOrder null_order, decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(c.scale, null, decode(c.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'), 105, 'MLSLABEL', 106, 'MLSLABEL', 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 178, 'TIME(' ||c.spare1|| ')', 179, 'TIME(' ||c.spare1|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||c.spare1|| ')', 181, 'TIMESTAMP(' ||c.spare1|| ')' || ' WITH TIME ZONE', 182, 'INTERVAL YEAR(' ||c.spare2||') TO MONTH', 183, 'INTERVAL DAY(' ||c.spare2||') TO SECOND(' || c.spare1 || ')', 208, 'UROWID', 'UNDEFINED') data_type , decode(c.length, null, 0, c.length) data_length , decode(c.precision#, null, 0, c.precision#) data_precision from olapsys.cwm2$hierarchy h, olapsys.cwm2$hiercustomsort hcs, olapsys.cwm2$dimension d, sys.obj$ o, dba_users u, sys.col$ c where h.irid = hcs.hier_irid and h.dimension_irid = d.irid and hcs.tablename_id = o.obj# and u.user_id = o.owner# and c.obj# = o.obj# and c.col# = hcs.columnname_id and hcs.metadataversion = 'TWO' with read only / REM ************************************************************* REM REM All the union api views REM REM Union view for dba$olap2ucubes create or replace view olapsys.dba$olap2ucubes as select owner, cube_name, invalid, display_name, short_description, description, mv_summarycode from olapsys.dba$olap1_cubes union all select owner, cube_name, invalid, display_name, short_description, description, mv_summarycode from olapsys.dba$olap2_cubes with read only / REM Union view for dba$olap2ucube_measures create or replace view olapsys.dba$olap2ucube_measures as SELECT distinct sch.physicalname owner , cub.physicalname cube_name , msr.physicalname measure_name , msr.displayname display_name , msr.description short_description , msr.description description FROM dba_users u , cwm$model sch , cwm$cube cub , cwm$measure msr , cwm$cubedimensionuse cdu , cwm$dimension cd , dba_users du , sys.obj$ do WHERE u.username = sch.physicalname AND sch.irid = cub.datamodel_irid AND cub.irid = msr.itemcontainer_irid AND cdu.cube_irid = cub.irid AND cdu.dimension_owner = du.username AND cdu.dimension_name = do.name AND du.user_id = do.owner# AND do.obj# = cd.irid AND do.type# = 43 AND cd.irid = cdu.abstractdimension_irid union all select owner, cube_name, measure_name, display_name, short_description, description from olapsys.dba$olap2_cube_measures with read only / REM Union view for dba$olap2ucube_dim_uses create or replace view olapsys.dba$olap2ucube_dim_uses as SELECT cdu.irid cube_dimension_use_id , sch.physicalname owner , cub.physicalname cube_name , cdu.dimension_owner , cdu.dimension_name , cdu.name dimension_alias , cdu.calc_hierarchy_name default_calc_hierarchy_name , cdu.cubedimensionuse_irid dependent_on_dim_use_id FROM dba_users u , cwm$model sch , cwm$cube cub , cwm$cubedimensionuse cdu , cwm$dimension cd , dba_users du , sys.obj$ do WHERE u.username = sch.physicalname AND sch.irid = cub.datamodel_irid AND cub.irid = cdu.cube_irid AND cdu.dimension_owner = du.username AND cdu.dimension_name = do.name AND du.user_id = do.owner# AND do.obj# = cd.irid AND do.type# = 43 AND cd.irid = cdu.abstractdimension_irid union all select cube_dimension_use_id, owner, cube_name, dimension_owner, dimension_name, dimension_alias, default_calc_hierarchy_name, dependent_on_dim_use_id from olapsys.dba$olap2_cube_dim_uses with read only / REM Union view for dba$olap2ucube_meas_dim_uses create or replace view olapsys.dba$olap2ucube_meas_dim_uses as SELECT distinct sch.physicalname owner , cub.physicalname cube_name , msr.physicalname measure_name , cdu.dimension_owner , cdu.dimension_name , cdu.name dimension_alias , fu.irid default_aggr_function_use_id FROM dba_users u , cwm$model sch , cwm$cube cub , cwm$measure msr , cwm$measuredimensionuse mdu , cwm$cubedimensionuse cdu , cwm$functionuse fu , cwm$dimension cd , dba_users du , sys.obj$ do WHERE u.username = sch.physicalname AND sch.irid = cub.datamodel_irid AND cub.irid = msr.itemcontainer_irid AND msr.irid = mdu.measure_irid AND mdu.cubedimensionuse_irid = cdu.irid AND mdu.irid = fu.measuredimensionuse_irid (+) AND cub.irid = cdu.cube_irid AND cdu.dimension_owner = du.username AND cdu.dimension_name = do.name AND du.user_id = do.owner# AND do.obj# = cd.irid AND do.type# = 43 AND cd.irid = cdu.abstractdimension_irid union all select owner, cube_name, measure_name, dimension_owner, dimension_name, dimension_alias, default_aggr_function_use_id from olapsys.dba$olap2_cube_meas_dim_uses with read only / REM Union view for dba$olap2udimensions create or replace view olapsys.dba$olap2udimensions as select mrd.owner owner, mrd.dimension_name dimension_name, mrd.plural_name plural_name, mrd.display_name display_name, mrd.short_description short_description, mrd.description description, mrd.default_display_hierarchy default_display_hierarchy, decode(mrd.status, 5, 'Y', 'N') invalid, 'HIER' dimension_type from olapsys.dba$olapmr_dimensions mrd union all select owner, dimension_name, plural_name, display_name, short_description, description, default_display_hierarchy, invalid, dimension_type from olapsys.dba$olap2_dimensions with read only / REM Union View for dba$olap2udim_hierarchies create or replace view olapsys.dba$olap2udim_hierarchies as select dh.owner owner, dh.dimension_name dimension_name, dh.hierarchy_name hierarchy_name, dh.display_name display_name, dh.short_description short_description, dh.long_description description, dh.solved_code solved_code from (SELECT u.username owner , d.name dimension_name , h.hiername hierarchy_name , hie.displayname display_name , hie.description short_description , hie.description long_description , 'UL' solved_code FROM dba_users u , sys.obj$ d , sys.hier$ h , cwm$hierarchy hie WHERE d.type# = 43 /* DIMENSION */ AND u.user_id = d.owner# AND d.obj# = h.dimobj# AND h.dimobj# = hie.dimension_irid AND h.hiername = hie.name (+)) dh union all select owner, dimension_name, hierarchy_name, display_name, short_description, description, solved_code from olapsys.dba$olap2_dim_hierarchies with read only / REM Union view for dba$olap2udim_levels create or replace view olapsys.dba$olap2udim_levels as select dl.owner owner, dl.dimension_name dimension_name, dl.level_name level_name, dl.display_name display_name, dl.short_description short_description, dl.long_description description, dl.level_table_owner level_table_owner, dl.level_table_name level_table_name from (SELECT u.username owner , d.name dimension_name , l.levelname level_name , lev.displayname display_name , lev.description short_description , lev.description long_description , tu.username level_table_owner , t.name level_table_name FROM dba_users u , sys.obj$ d , sys.dimlevel$ l , sys.dimlevelkey$ k , sys.obj$ t , dba_users tu , cwm$level lev WHERE d.type# = 43 /* DIMENSION */ AND u.user_id = d.owner# AND d.obj# = l.dimobj# AND l.dimobj# = k.dimobj# AND l.levelid# = k.levelid# AND k.detailobj# = t.obj# AND t.owner# = tu.user_id AND k.keypos# = 1 AND l.dimobj# = lev.dimension_irid AND l.levelname = lev.physicalname (+)) dl union all select owner, dimension_name, level_name, display_name, short_description, description, null level_table_owner, null level_table_name from olapsys.dba$olap2_dim_levels with read only / REM Union view for dba$olap2udim_hier_level_uses create or replace view olapsys.dba$olap2udim_hier_level_uses as SELECT u.username owner , d.name dimension_name , h.hiername hierarchy_name , pdl.levelname parent_level_name , cdl.levelname child_level_name , chl.pos# position FROM dba_users u , sys.obj$ d , sys.hier$ h , sys.hierlevel$ phl , sys.hierlevel$ chl , sys.dimlevel$ pdl , sys.dimlevel$ cdl , olapsys.cwm$hierarchy ch WHERE d.type# = 43 /* DIMENSION */ AND u.user_id = d.owner# AND d.obj# = h.dimobj# AND d.obj# = phl.dimobj# AND h.hierid# = phl.hierid# AND d.obj# = pdl.dimobj# AND phl.levelid# = pdl.levelid# AND d.obj# = chl.dimobj# AND h.hierid# = chl.hierid# AND chl.levelid# = cdl.levelid# AND d.obj# = cdl.dimobj# AND phl.pos# = chl.pos# + 1 AND ch.dimension_irid = d.obj# AND ch.name = h.hiername union all select owner, dimension_name, hierarchy_name, parent_level_name, child_level_name, position from olapsys.dba$olap2_dim_hier_level_uses with read only / REM Union view for dba$olap2udim_attributes create or replace view olapsys.dba$olap2udim_attributes as select owner, dimension_name, attribute_name, display_name, description short_description, description, desc_id from dba$olapmr_dim_attributes union all select owner, dimension_name, attribute_name, display_name, short_description, description, desc_id from dba$olap2_dim_attributes with read only / REM Union view for dba$olap2udim_attr_uses create or replace view olapsys.dba$olap2udim_attr_uses as select owner, dimension_name, dim_attribute_name, level_name, lvl_attribute_name from olapsys.dba$olap_dim_attr_uses union all select owner, dimension_name, dim_attribute_name, level_name, lvl_attribute_name from dba$olap2_dim_attr_uses with read only / REM Union view for dba$olap2udim_level_attributes create or replace view olapsys.dba$olap2udim_level_attributes as select da.owner owner, da.dimension_name dimension_name, da.attribute_name attribute_name, da.display_name display_name, da.short_description short_description, da.long_description description, da.determined_by_level_name determined_by_level_name from (SELECT a.u_name owner , a.d_name dimension_name , nvl(la.lat_name, a.c_name) attribute_name , la.lat_displayname display_name , la.lat_description short_description , la.lat_description long_description , a.l_levelname determined_by_level_name FROM (SELECT /*+ ORDERED*/ u.username u_name , d.name d_name , l.dimobj# l_dimobj# , l.levelname l_levelname , c.obj# c_obj# , c.name c_name FROM dba_users u , sys.obj$ d , sys.dimlevel$ l , sys.dimattr$ a , sys.col$ c WHERE d.type# = 43 /* DIMENSION */ AND u.user_id = d.owner# AND d.obj# = l.dimobj# AND l.dimobj# = a.dimobj# AND l.levelid# = a.levelid# AND a.detailobj# = c.obj# AND a.col# = c.col#) a , (SELECT lat.name lat_name , lat.displayname lat_displayname , lat.description lat_description , lat.type_irid lat_type_irid , lat.physicalname lat_physicalname , lev.dimension_irid lev_dimension_irid , lev.physicalname lev_physicalname FROM cwm$level lev, cwm$levelattribute lat WHERE lev.irid = lat.itemcontainer_irid) la WHERE a.l_dimobj# = la.lev_dimension_irid AND a.l_levelname = la.lev_physicalname AND a.c_obj# = la.lat_type_irid AND a.c_name = la.lat_physicalname) da union all select owner, dimension_name, attribute_name, display_name, short_description, description, determined_by_level_name from dba$olap2_dim_level_attributes with read only / REM Union view for dba$olap2ucube_measure_maps create or replace view olapsys.dba$olap2ucube_measure_maps as SELECT distinct sch.physicalname owner , cub.physicalname cube_name , msr.physicalname measure_name , 0 dim_hier_combo_id , use2.object_owner fact_table_owner , use2.object_name fact_table_name , use2.secondary_object_name column_name FROM dba_users u , cwm$model sch , cwm$cube cub , cwm$measure msr , cwm$itemuse use1 , cwm$itemuse use2 , cwm$cubedimensionuse cdu , cwm$dimension cd , dba_users du , sys.obj$ do WHERE u.username = sch.physicalname AND sch.irid = cub.datamodel_irid AND cub.irid = msr.itemcontainer_irid AND msr.irid = use1.mappable_irid AND use1.operation_irid = use2.operation_irid_1 AND cdu.cube_irid = cub.irid AND cdu.dimension_owner = du.username AND cdu.dimension_name = do.name AND du.user_id = do.owner# AND do.obj# = cd.irid AND do.type# = 43 AND cd.irid = cdu.abstractdimension_irid union all select owner, cube_name, measure_name, dim_hier_combo_id, fact_table_owner, fact_table_name, column_name from olapsys.dba$olap2_cube_measure_maps with read only / REM Union view for dba$olap2ufact_level_uses create or replace view olapsys.dba$olap2ufact_level_uses as select a.owner owner, a.cube_name cube_name, a.dimension_owner dimension_owner, a.dimension_name dimension_name, a.dimension_alias dimension_alias, b.hierarchy_name hierarchy_name, 0 dim_hier_combo_id, a.level_name level_name, a.fact_table_owner fact_table_owner, a.fact_table_name fact_table_name, fk.column_name column_name, fk.position position, 'LL' dimension_keymap_type, a.foreign_key_name from olapsys.dba$olap1_fact_level_uses a, dba_olap_dim_hierarchies b, (select u.username table_owner, t.name table_name, c.name key_name, col.name column_name, ccol.pos# position from dba_users u, sys.obj$ t, sys.con$ c, sys.cdef$ cd, sys.col$ col, sys.ccol$ ccol where u.user_id = c.owner# and c.con# = cd.con# and cd.con# = ccol.con# and cd.obj# = t.obj# and ccol.intcol# = col.intcol# and col.obj# = t.obj# and cd.type# in (2,3,4) and t.type# in (2,4)) fk where a.dimension_name = b.dimension_name (+) and a.dimension_owner = b.owner (+) and a.fact_table_owner = fk.table_owner and a.fact_table_name = fk.table_name and a.foreign_key_name = fk.key_name union all select owner, cube_name, dimension_owner, dimension_name, dimension_alias, hierarchy_name, dim_hier_combo_id, level_name, fact_table_owner, fact_table_name, column_name, position, dimension_keymap_type, foreign_key_name from olapsys.dba$olap2_fact_level_uses with read only / REM Union view for dba$olap2ulevel_key_col_uses create or replace view olapsys.dba$olap2ulevel_key_col_uses as select distinct lkcu.owner owner, lkcu.dimension_name dimension_name, dhl.hierarchy_name hierarchy_name, lkcu.level_name child_level_name, lkcu.table_owner table_owner, lkcu.table_name table_name, lkcu.column_name column_name, lkcu.position position from (select u.username owner, d.name dimension_name, l.levelname level_name, c.name column_name, k.keypos# position, tu.username table_owner, tn.name table_name from dba_users u, sys.obj$ d, sys.dimlevel$ l, sys.dimlevelkey$ k, sys.col$ c, sys.obj$ tn, dba_users tu, olapsys.cwm$level lev where u.user_id = d.owner# and d.type# = 43 and d.obj# = l.dimobj# and l.dimobj# = k.dimobj# and l.levelid# = k.levelid# and k.detailobj# = c.obj# and k.col# = c.col# and tn.obj# = c.obj# and tu.user_id = tn.owner# and lev.dimension_irid = l.dimobj# and lev.physicalname = l.levelname ) lkcu, (select u.username dim_owner, o.name dim_name, h.hiername hierarchy_name, dl.levelname level_name from dba_users u, sys.obj$ o, sys.dim$ d, sys.dimlevel$ dl, sys.hier$ h, sys.hierlevel$ hl, olapsys.cwm$level lev where u.user_id = o.owner# and o.type# = 43 and o.obj# = d.obj# and d.obj# = dl.dimobj# and d.obj# = h.dimobj# and h.dimobj# = hl.dimobj# and h.hierid# = hl.hierid# and hl.levelid# = dl.levelid# and lev.dimension_irid = dl.dimobj# and lev.physicalname = dl.levelname ) dhl where lkcu.owner = dhl.dim_owner (+) and lkcu.dimension_name = dhl.dim_name (+) and lkcu.level_name = dhl.level_name (+) UNION ALL select owner, dimension_name, hierarchy_name, child_level_name, table_owner, table_name, column_name, position from olapsys.dba$olap2_level_key_col_uses with read only / REM Union view for dba$olap2ujoin_key_column_uses create or replace view olapsys.dba$olap2ujoin_key_column_uses as SELECT du.username owner , d.name dimension_name , h.hiername hierarchy_name , l.levelname child_level_name , tu.username table_owner , t.name table_name , c.name column_name , k.keypos# position , 'FOREIGN KEY' join_key_type FROM dba_users du , sys.obj$ d , sys.hier$ h , sys.hierlevel$ hl , sys.dimlevel$ l , sys.dimjoinkey$ k , dba_users tu , sys.obj$ t , sys.col$ c , olapsys.cwm$level lev WHERE du.user_id = d.owner# AND d.type# = 43 AND d.obj# = h.dimobj# AND h.dimobj# = hl.dimobj# AND h.hierid# = hl.hierid# AND hl.dimobj# = l.dimobj# AND hl.levelid# = l.levelid# AND hl.dimobj# = k.dimobj# AND hl.hierid# = k.hierid# AND hl.joinkeyid# = k.joinkeyid# AND k.detailobj# = t.obj# AND k.col# = c.col# AND t.obj# = c.obj# AND t.owner# = tu.user_id AND lev.dimension_irid = l.dimobj# AND lev.physicalname = l.levelname UNION ALL SELECT du.username owner , d.name dimension_name , h.hiername hierarchy_name , l.levelname child_level_name , tu.username table_owner , t.name table_name , c.name column_name , k.keypos# position , 'KEY' join_key_type FROM dba_users du , sys.obj$ d , sys.hier$ h , sys.hierlevel$ hl , sys.dimlevel$ l , sys.dimlevelkey$ k , dba_users tu , sys.obj$ t , sys.col$ c , olapsys.cwm$level lev WHERE du.user_id = d.owner# AND d.type# = 43 AND d.obj# = h.dimobj# AND h.dimobj# = hl.dimobj# AND h.hierid# = hl.hierid# AND hl.dimobj# = l.dimobj# AND hl.levelid# = l.levelid# AND hl.joinkeyid# = 0 AND l.dimobj# = k.dimobj# AND l.levelid# = k.levelid# AND k.detailobj# = t.obj# AND k.col# = c.col# AND t.obj# = c.obj# AND t.owner# = tu.user_id AND lev.dimension_irid = l.dimobj# AND lev.physicalname = l.levelname union all select owner, dimension_name, hierarchy_name, child_level_name, table_owner, table_name, column_name, position, join_key_type from olapsys.dba$olap2_join_key_column_uses with read only / REM Union view for dba$olap2udim_level_attr_maps create or replace view olapsys.dba$olap2udim_level_attr_maps as select attr.dim_owner owner, attr.dim_name dimension_name, attr.hierarchy_name hierarchy_name, attr.dim_attribute_name attribute_name, attr.lvl_attribute_name lvl_attribute_name, attr.levelname level_name, attr.col_owner table_owner, attr.col_table table_name, attr.col_name column_name, attr.dtype dtype, attr.data_length data_length, attr.data_precision data_precision, (case when attr.dtype = 'NUMBER' then 0 when attr.dtype = 'DOUBLE' then 5 when attr.dtype = 'FLOAT' then 4 when attr.dtype = 'DATE' then 7 when attr.dtype = 'LONG' then 3 else 1 end) olap_api_data_type from (select u.username dim_owner, o.name dim_name, h.hiername hierarchy_name, dat.physicalname dim_attribute_name, nvl(lat.name, c.name) lvl_attribute_name, l.levelname levelname, decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(c.scale, null, decode(c.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'), 105, 'MLSLABEL', 106, 'MLSLABEL', 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 178, 'TIME(' ||c.spare1|| ')', 179, 'TIME(' ||c.spare1|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||c.spare1|| ')', 181, 'TIMESTAMP(' ||c.spare1|| ')' || ' WITH TIME ZONE', 182, 'INTERVAL YEAR(' ||c.spare2||') TO MONTH', 183, 'INTERVAL DAY(' ||c.spare2||') TO SECOND(' || c.spare1 || ')', 208, 'UROWID', 'UNDEFINED') dtype , decode(c.length, null, 0, c.length) data_length , decode(c.precision#, null, 0, c.precision#) data_precision, cu.username col_owner, ct.name col_table, c.name col_name from dba_users u, sys.obj$ o, sys.dim$ d, sys.dimlevel$ l, sys.dimattr$ a, sys.hierlevel$ hl, sys.hier$ h, sys.col$ c, sys.obj$ ct, dba_users cu, cwm$levelattribute lat, cwm$level lvl, cwm$itemuse iu1, cwm$itemuse iu2, cwm$dimensionattribute dat where u.user_id = o.owner# and o.type# = 43 and o.obj# = d.obj# and d.obj# = l.dimobj# and d.obj# = dat.itemcontainer_irid and d.obj# = h.dimobj# and d.obj# = hl.dimobj# and h.hierid# = hl.hierid# and hl.levelid# = l.levelid# and l.dimobj# = a.dimobj# and l.levelid# = a.levelid# and a.detailobj# = c.obj# and a.col# = c.col# and c.obj# = ct.obj# and ct.owner# = cu.user_id and c.obj# = lat.type_irid and c.name = lat.physicalname and lvl.dimension_irid = l.dimobj# and lat.itemcontainer_irid = lvl.irid and lat.irid = iu2.mappable_irid and iu2.operation_irid_1 = iu1.operation_irid and iu1.mappable_irid = dat.irid ) attr union all select attr1.dim_owner owner, attr1.dim_name dimension_name, null hierarchy_name, attr1.dim_attribute_name attribute_name, attr1.lvl_attribute_name lvl_attribute_name, attr1.levelname level_name, attr1.col_owner table_owner, attr1.col_table table_name, attr1.col_name column_name, attr1.dtype dtype, attr1.data_length data_length, attr1.data_precision data_precision, (case when attr1.dtype = 'NUMBER' then 0 when attr1.dtype = 'DOUBLE' then 5 when attr1.dtype = 'FLOAT' then 4 when attr1.dtype = 'DATE' then 7 when attr1.dtype = 'LONG' then 3 else 1 end) olap_api_data_type from (select u.username dim_owner, o.name dim_name, dat.physicalname dim_attribute_name, nvl(lat.name, c.name) lvl_attribute_name, l.levelname levelname, decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(c.scale, null, decode(c.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'), 105, 'MLSLABEL', 106, 'MLSLABEL', 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 178, 'TIME(' ||c.spare1|| ')', 179, 'TIME(' ||c.spare1|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||c.spare1|| ')', 181, 'TIMESTAMP(' ||c.spare1|| ')' || ' WITH TIME ZONE', 182, 'INTERVAL YEAR(' ||c.spare2||') TO MONTH', 183, 'INTERVAL DAY(' ||c.spare2||') TO SECOND(' || c.spare1 || ')', 208, 'UROWID', 'UNDEFINED') dtype , decode(c.length, null, 0, c.length) data_length , decode(c.precision#, null, 0, c.precision#) data_precision, cu.username col_owner, ct.name col_table, c.name col_name, l.dimobj# l_dimobj, l.levelid# l_levelid from dba_users u, sys.obj$ o, sys.dim$ d, sys.dimlevel$ l, sys.dimattr$ a, sys.col$ c, sys.obj$ ct, dba_users cu, cwm$levelattribute lat, cwm$level lvl, cwm$itemuse iu1, cwm$itemuse iu2, cwm$dimensionattribute dat where u.user_id = o.owner# and o.type# = 43 and o.obj# = d.obj# and d.obj# = l.dimobj# and d.obj# = dat.itemcontainer_irid and l.dimobj# = a.dimobj# and l.levelid# = a.levelid# and a.detailobj# = c.obj# and a.col# = c.col# and c.obj# = ct.obj# and ct.owner# = cu.user_id and c.obj# = lat.type_irid and c.name = lat.physicalname and lvl.dimension_irid = l.dimobj# and lat.itemcontainer_irid = lvl.irid and lat.irid = iu2.mappable_irid and iu2.operation_irid_1 = iu1.operation_irid and iu1.mappable_irid = dat.irid ) attr1, (select dl1.dimobj# l_dimobj, dl1.levelid# l_levelid from sys.dimlevel$ dl1 where to_char(dl1.dimobj#) || '_' || to_char(dl1.levelid#) not in (select to_char(hl.dimobj#) || '_' || to_char(hl.levelid#) from sys.hierlevel$ hl) ) nohierlvl where nohierlvl.l_dimobj = attr1.l_dimobj and nohierlvl.l_levelid = attr1.l_levelid union all select owner, dimension_name, hierarchy_name, attribute_name, lvl_attribute_name, level_name, table_owner, table_name, column_name, dtype, data_length, data_precision, olap_api_data_type from olapsys.dba$olap2_dim_level_attr_maps with read only / REM Union view for dba$olap2ucatalogs create or replace view olapsys.dba$olap2ucatalogs as select catalog_id, catalog_name, parent_catalog_id, description from olapsys.dba$olap_catalogs union all select catalog_id, catalog_name, parent_catalog_id, description from olapsys.dba$olap2_catalogs with read only / REM Union view for dba$olap2uentity_desc_uses create or replace view olapsys.dba$olap2uentity_desc_uses as select distinct ce.classification_irid descriptor_id , sch.physicalname entity_owner , cub.physicalname entity_name , null child_entity_name , NULL secondary_child_entity_name FROM /* CUBE */ cwm$classification c , cwm$classificationentry ce , cwm$classificationtype cty , cwm$cube cub , cwm$model sch , dba_users u , cwm$cubedimensionuse cdu , cwm$dimension cd , dba_users du , sys.obj$ do WHERE cty.irid = c.classificationtype_irid AND cty.name <> 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.name = 'CUBE' AND ce.element_irid = cub.irid AND cub.datamodel_irid = sch.irid AND sch.physicalname = u.username AND cdu.cube_irid = cub.irid AND cdu.dimension_owner = du.username AND cdu.dimension_name = do.name AND du.user_id = do.owner# AND cd.irid = do.obj# AND do.type# = 43 AND cd.irid = cdu.abstractdimension_irid UNION ALL SELECT DISTINCT ce.classification_irid descriptor_id , sch.physicalname entity_owner , cub.physicalname entity_name , msr.physicalname child_entity_name , NULL secondary_child_entity_name FROM /* MEASURE */ cwm$classification c , cwm$classificationentry ce , cwm$classificationtype cty , cwm$measure msr , cwm$cube cub , cwm$model sch , dba_users u , cwm$cubedimensionuse cdu , cwm$dimension cd , dba_users du , sys.obj$ do WHERE cty.irid = c.classificationtype_irid AND cty.name <> 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.name = 'MEASURE' AND ce.element_irid = msr.irid AND msr.itemcontainer_irid = cub.irid AND cub.datamodel_irid = sch.irid AND sch.physicalname = u.username AND cdu.cube_irid = cub.irid AND cdu.dimension_owner = du.username AND cdu.dimension_name = do.name AND du.user_id = do.owner# AND cd.irid = do.obj# AND do.type# = 43 AND cd.irid = cdu.abstractdimension_irid UNION ALL SELECT ce.classification_irid descriptor_id , u.username entity_owner , d.name entity_name , dat.physicalname child_entity_name , NULL secondary_child_entity_name FROM /* DIMENSIONATTRIBUTE */ cwm$classification c , cwm$classificationentry ce , cwm$classificationtype cty , cwm$dimensionattribute dat , sys.obj$ d , dba_users u WHERE cty.irid = c.classificationtype_irid AND cty.name <> 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.name = 'DIMENSION ATTRIBUTE' AND ce.element_irid = dat.irid AND dat.itemcontainer_irid = d.obj# AND d.type# = 43 /* DIMENSION */ AND d.owner# = u.user_id UNION ALL SELECT ce.classification_irid descriptor_id , u.username entity_owner , d.name entity_name , ce.secondary_object_name child_entity_name , ce.tertiary_object_name secondary_child_entity_name FROM /* LEVELATTRIBUTE */ cwm$classification c , cwm$classificationentry ce , cwm$classificationtype cty , cwm$levelattribute lat , cwm$level lvl , sys.obj$ d , dba_users u WHERE cty.irid = c.classificationtype_irid AND cty.name <> 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.name = 'LEVEL ATTRIBUTE' AND ce.element_irid = lat.irid AND lat.itemcontainer_irid = lvl.irid AND lvl.dimension_irid = d.obj# AND d.type# = 43 /* DIMENSION */ AND d.owner# = u.user_id UNION ALL SELECT ce.classification_irid descriptor_id , u.username entity_owner , d.name entity_name , ce.secondary_object_name child_entity_name , null secondary_child_entity_name FROM /* DIMENSION, LEVEL, HIERARCHY */ cwm$classification c , cwm$classificationentry ce , cwm$classificationtype cty , sys.obj$ d , dba_users u WHERE cty.irid = c.classificationtype_irid AND cty.name <> 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.name IN ('DIMENSION', 'LEVEL', 'HIERARCHY') AND ce.element_irid = d.obj# AND d.type# = 43 /* DIMENSION */ AND d.owner# = u.user_id UNION ALL SELECT ce.classification_irid descriptor_id , f.name entity_owner , p.name entity_name , null child_entity_name , NULL secondary_child_entity_name FROM /* FUNCTION PARAMETER */ cwm$classification c , cwm$classificationentry ce , cwm$classificationtype cty , cwm$function f , cwm$parameter p WHERE cty.irid = c.classificationtype_irid AND cty.name <> 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.name = 'PARAMETER' AND ce.element_irid = p.irid AND p.operation_irid = f.irid union all select descriptor_id, entity_owner, entity_name, child_entity_name, secondary_child_entity_name from olapsys.dba$olap2_entity_desc_uses with read only / REM Union view for new 10g dba$olap2uentity_parameters CREATE OR REPLACE VIEW dba$olap2uentity_parameters AS SELECT DISTINCT ce.classification_irid descriptor_id , c.name descriptor_name , sch.physicalname entity_owner , cub.physicalname entity_name , null child_entity_name , NULL secondary_child_entity_name , cvp.parametername parameter_name , cvp.parametervalue parameter_value FROM /* CUBE */ cwm$classification c , cwm$classificationentry ce , cwm2$classificationvaluepair cvp , cwm$classificationtype cty , cwm$cube cub , cwm$model sch , dba_users u , cwm$cubedimensionuse cdu , cwm$dimension cd , dba_users du , sys.obj$ do WHERE cty.irid = c.classificationtype_irid AND cty.name <> 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.name = 'CUBE' AND ce.element_irid = cub.irid AND ce.irid = cvp.classentry_irid (+) AND cub.datamodel_irid = sch.irid AND sch.physicalname = u.username AND cdu.cube_irid = cub.irid AND cdu.dimension_owner = du.username AND cdu.dimension_name = do.name AND du.user_id = do.owner# AND do.obj# = cd.irid AND do.type# = 43 AND cd.irid = cdu.abstractdimension_irid UNION ALL SELECT DISTINCT ce.classification_irid descriptor_id , c.name descriptor_name , sch.physicalname entity_owner , cub.physicalname entity_name , msr.physicalname child_entity_name , NULL secondary_child_entity_name , cvp.parametername parameter_name , cvp.parametervalue parameter_value FROM /* MEASURE */ cwm$classification c , cwm$classificationentry ce , cwm$classificationtype cty , cwm2$classificationvaluepair cvp , cwm$measure msr , cwm$cube cub , cwm$model sch , dba_users u , cwm$cubedimensionuse cdu , cwm$dimension cd , dba_users du , sys.obj$ do WHERE cty.irid = c.classificationtype_irid AND cty.name <> 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.name = 'MEASURE' AND ce.element_irid = msr.irid AND ce.irid = cvp.classentry_irid (+) AND msr.itemcontainer_irid = cub.irid AND cub.datamodel_irid = sch.irid AND sch.physicalname = u.username AND cdu.cube_irid = cub.irid AND cdu.dimension_owner = du.username AND cdu.dimension_name = do.name AND du.user_id = do.owner# AND do.obj# = cd.irid AND do.type# = 43 AND cd.irid = cdu.abstractdimension_irid UNION ALL SELECT ce.classification_irid descriptor_id , c.name descriptor_name , u.username entity_owner , d.name entity_name , dat.physicalname child_entity_name , NULL secondary_child_entity_name , cvp.parametername parameter_name , cvp.parametervalue parameter_value FROM /* DIMENSIONATTRIBUTE */ cwm$classification c , cwm$classificationentry ce , cwm2$classificationvaluepair cvp , cwm$classificationtype cty , cwm$dimensionattribute dat , sys.obj$ d , dba_users u WHERE cty.irid = c.classificationtype_irid AND cty.name <> 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.name = 'DIMENSION ATTRIBUTE' AND ce.irid = cvp.classentry_irid (+) AND ce.element_irid = dat.irid AND dat.itemcontainer_irid = d.obj# AND d.type# = 43 /* DIMENSION */ AND d.owner# = u.user_id UNION ALL SELECT ce.classification_irid descriptor_id , c.name descriptor_name , u.username entity_owner , d.name entity_name , ce.secondary_object_name child_entity_name , ce.tertiary_object_name secondary_child_entity_name , cvp.parametername parameter_name , cvp.parametervalue parameter_value FROM /* LEVELATTRIBUTE */ cwm$classification c , cwm$classificationentry ce , cwm2$classificationvaluepair cvp , cwm$classificationtype cty , cwm$levelattribute lat , cwm$level lvl , sys.obj$ d , dba_users u WHERE cty.irid = c.classificationtype_irid AND cty.name <> 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.name = 'LEVEL ATTRIBUTE' AND ce.irid = cvp.classentry_irid (+) AND ce.element_irid = lat.irid AND lat.itemcontainer_irid = lvl.irid AND lvl.dimension_irid = d.obj# AND d.type# = 43 /* DIMENSION */ AND d.owner# = u.user_id UNION ALL SELECT ce.classification_irid descriptor_id , c.name descriptor_name , u.username entity_owner , d.name entity_name , ce.secondary_object_name child_entity_name , null secondary_child_entity_name , cvp.parametername parameter_name , cvp.parametervalue parameter_value FROM /* DIMENSION, LEVEL, HIERARCHY */ cwm$classification c , cwm$classificationentry ce , cwm$classificationtype cty , cwm2$classificationvaluepair cvp , sys.obj$ d , dba_users u WHERE cty.irid = c.classificationtype_irid AND cty.name <> 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.irid = cvp.classentry_irid (+) AND ce.name IN ('DIMENSION', 'LEVEL', 'HIERARCHY') AND ce.element_irid = d.obj# AND d.type# = 43 /* DIMENSION */ AND d.owner# = u.user_id UNION ALL SELECT ce.classification_irid descriptor_id , c.name descriptor_name , f.name entity_owner , p.name entity_name , null child_entity_name , NULL secondary_child_entity_name , null parameter_name , null parameter_value FROM /* FUNCTION PARAMETER */ cwm$classification c , cwm$classificationentry ce , cwm$classificationtype cty , cwm$function f , cwm$parameter p WHERE cty.irid = c.classificationtype_irid AND cty.name <> 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.name = 'PARAMETER' AND ce.element_irid = p.irid AND p.operation_irid = f.irid UNION ALL select ce.classification_irid descriptor_id, c.name descriptor_name, cub.owner entity_owner, cub.name entity_name, null child_entity_name, null secondary_child_entity_name, cvp.parametername parameter_name, cvp.parametervalue parameter_value from /* CUBE2 */ cwm$classification c, cwm$classificationentry ce, cwm2$classificationvaluepair cvp, cwm$classificationtype cty, cwm2$cube cub where cty.irid = c.classificationtype_irid and cty.name <> 'ORACLE_OLAP2_CATALOG' and c.irid = ce.classification_irid and ce.name = 'CUBE2' and ce.element_irid = cub.irid and ce.irid = cvp.classentry_irid (+) UNION ALL select ce.classification_irid descriptor_id, c.name descriptor_name, cub.owner entity_owner, cub.name entity_name, meas.name child_entity_name, null secondary_child_entity_name, cvp.parametername parameter_name, cvp.parametervalue parameter_value from /* MEASURE2 */ cwm$classification c, cwm$classificationentry ce, cwm2$classificationvaluepair cvp, cwm$classificationtype cty, cwm2$cube cub, cwm2$measure meas where cty.irid = c.classificationtype_irid and cty.name <> 'ORACLE_OLAP2_CATALOG' and c.irid = ce.classification_irid and ce.name = 'MEASURE2' and ce.irid = cvp.classentry_irid (+) and ce.element_irid = meas.irid and meas.cube_irid = cub.irid UNION ALL select ce.classification_irid descriptor_id, c.name descriptor_name, dim.owner entity_owner, dim.name entity_name, dat.name child_entity_name, null secondary_child_entity_name, cvp.parametername parameter_name, cvp.parametervalue parameter_value from /* DIMENSION ATTRIBUTE2 */ cwm$classification c, cwm$classificationentry ce, cwm2$classificationvaluepair cvp, cwm$classificationtype cty, cwm2$dimension dim, cwm2$dimensionattribute dat where cty.irid = c.classificationtype_irid and cty.name <> 'ORACLE_OLAP2_CATALOG' and c.irid = ce.classification_irid and ce.name = 'DIMENSION ATTRIBUTE2' and ce.element_irid = dat.irid and ce.irid = cvp.classentry_irid (+) and dat.dimension_irid = dim.irid UNION ALL select ce.classification_irid descriptor_id, c.name descriptor_name, dim.owner entity_owner, dim.name entity_name, lvl.name child_entity_name, lat.name secondary_child_entity_name, cvp.parametername parameter_name, cvp.parametervalue parameter_value from /* LEVEL ATTRIBUTE2 */ cwm$classification c, cwm$classificationentry ce, cwm2$classificationvaluepair cvp, cwm$classificationtype cty, cwm2$dimension dim, cwm2$dimensionattribute dat, cwm2$levelattribute lat, cwm2$level lvl where cty.irid = c.classificationtype_irid and cty.name not in ('ORACLE_OLAP2_CATALOG', 'ORACLE_OLAP_CATALOG') and c.irid = ce.classification_irid and ce.name = 'LEVEL ATTRIBUTE2' and ce.element_irid = lat.irid and ce.irid = cvp.classentry_irid (+) and lat.dimattr_irid = dat.irid and dat.dimension_irid = dim.irid and lvl.irid = lat.level_irid UNION ALL select ce.classification_irid descriptor_id, c.name descriptor_name, dim.owner entity_owner, dim.name entity_name, ce.secondary_object_name child_entity_name, null secondary_child_entity_name, cvp.parametername parameter_name, cvp.parametervalue parameter_value from /* DIMENSION2 */ cwm$classification c, cwm$classificationentry ce, cwm2$classificationvaluepair cvp, cwm$classificationtype cty, cwm2$dimension dim where cty.irid = c.classificationtype_irid and cty.name not in ('ORACLE_OLAP2_CATALOG', 'ORACLE_OLAP_CATALOG') and c.irid = ce.classification_irid and ce.irid = cvp.classentry_irid (+) and ce.name = 'DIMENSION2' and ce.element_irid = dim.irid UNION ALL select ce.classification_irid descriptor_id, c.name descriptor_name, dim.owner entity_owner, dim.name entity_name, ce.secondary_object_name child_entity_name, null secondary_child_entity_name, cvp.parametername parameter_name, cvp.parametervalue parameter_value from /* HIERARCHY2 */ cwm$classification c, cwm$classificationentry ce, cwm2$classificationvaluepair cvp, cwm$classificationtype cty, cwm2$dimension dim, cwm2$hierarchy hier where cty.irid = c.classificationtype_irid and cty.name not in ('ORACLE_OLAP2_CATALOG', 'ORACLE_OLAP_CATALOG') and c.irid = ce.classification_irid and ce.irid = cvp.classentry_irid (+) and ce.name = 'HIERARCHY2' and dim.irid = hier.dimension_irid and ce.element_irid = hier.irid UNION ALL select ce.classification_irid descriptor_id, c.name descriptor_name, dim.owner entity_owner, dim.name entity_name, ce.secondary_object_name child_entity_name, null secondary_child_entity_name, cvp.parametername parameter_name, cvp.parametervalue parameter_value from /* LEVEL2 */ cwm$classification c, cwm$classificationentry ce, cwm2$classificationvaluepair cvp, cwm$classificationtype cty, cwm2$dimension dim, cwm2$level lvl where cty.irid = c.classificationtype_irid and cty.name not in ('ORACLE_OLAP2_CATALOG', 'ORACLE_OLAP_CATALOG') and c.irid = ce.classification_irid and ce.irid = cvp.classentry_irid (+) and ce.name = 'LEVEL2' and dim.irid = lvl.dimension_irid and ce.element_irid = lvl.irid with read only / REM Union view for new 10g dba$olap2uentity_ext_parms REM provides extension level descriptor information Rem Default Member has 2 parts for cwm1 for list dim support CREATE OR REPLACE VIEW dba$olap2uentity_ext_parms AS SELECT ce.classification_irid descriptor_id , c.name descriptor_name , sch.physicalname entity_owner , cub.physicalname entity_name , ce.secondary_object_name child_entity_name , ce.tertiary_object_name secondary_child_entity_name , cvp.parametername parameter_name , cvp.parametervalue parameter_value , null parameter_value2 , null parameter_value3 , null parameter_value4 , null position FROM /* DENSE INDICATOR */ cwm$classification c , cwm$classificationentry ce , cwm2$classificationvaluepair cvp , cwm$classificationtype cty , cwm$cube cub , cwm$cubedimensionuse cdu , cwm$model sch , dba_users u , cwm$dimension cd , dba_users du , sys.obj$ do WHERE cty.irid = c.classificationtype_irid AND cty.name <> 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.name = 'DENSE INDICATOR' AND ce.element_irid = cdu.irid AND cub.irid = cdu.cube_irid AND cdu.dimension_name = ce.tertiary_object_name AND ce.irid = cvp.classentry_irid (+) AND cub.datamodel_irid = sch.irid AND sch.physicalname = u.username AND cdu.dimension_owner = du.username AND cdu.dimension_name = do.name AND du.user_id = do.owner# AND do.obj# = cd.irid AND do.type# = 43 AND cd.irid = cdu.abstractdimension_irid UNION ALL select ce.classification_irid descriptor_id, c.name descriptor_name, cub.owner entity_owner, cub.name entity_name, ce.secondary_object_name child_entity_name, ce.tertiary_object_name secondary_child_entity_name, cvp.parametername parameter_name, cvp.parametervalue parameter_value, null parameter_value2, null parameter_value3, null parameter_value4, null position from /* DENSE INDICATOR2 */ cwm$classification c, cwm$classificationentry ce, cwm2$classificationvaluepair cvp, cwm$classificationtype cty, cwm2$cube cub, cwm2$dimension dim, cwm2$cubedimensionuse cdu where cty.irid = c.classificationtype_irid and cty.name <> 'ORACLE_OLAP2_CATALOG' and c.irid = ce.classification_irid and ce.name = 'DENSE INDICATOR2' and ce.element_irid = cdu.irid and cub.irid = cdu.cube_irid and ce.tertiary_object_name = dim.name and dim.irid = cdu.dimension_irid and ce.irid = cvp.classentry_irid (+) UNION ALL SELECT ce.classification_irid descriptor_id , c.name descriptor_name , u.username entity_owner , d.name entity_name , ce.secondary_object_name child_entity_name , null secondary_child_entity_name , cvp.parametername parameter_name , cvp.parametervalue parameter_value , cvp.parametervalue2 parameter_value2 , null parameter_value3 , null parameter_value4 , cvp.position position FROM /* DEFAULT MEMBER */ cwm$classification c , cwm$classificationentry ce , cwm$classificationtype cty , cwm2$classificationvaluepair cvp , cwm$hierarchy hier , sys.obj$ d , dba_users u WHERE cty.irid = c.classificationtype_irid AND cty.name <> 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.irid = cvp.classentry_irid (+) AND ce.name = 'DEFAULT MEMBER' AND ce.element_irid = hier.irid AND hier.dimension_irid = d.obj# AND d.type# = 43 /* DIMENSION */ AND d.owner# = u.user_id UNION ALL SELECT ce.classification_irid descriptor_id , c.name descriptor_name , u.username entity_owner , d.name entity_name , ce.secondary_object_name child_entity_name , null secondary_child_entity_name , cvp.parametername parameter_name , cvp.parametervalue parameter_value , cvp.parametervalue2 parameter_value2 , null parameter_value3 , null parameter_value4 , cvp.position position FROM /* DEFAULT MEMBER */ cwm$classification c , cwm$classificationentry ce , cwm$classificationtype cty , cwm2$classificationvaluepair cvp , sys.obj$ d , dba_users u WHERE cty.irid = c.classificationtype_irid AND cty.name <> 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.irid = cvp.classentry_irid (+) AND ce.name = 'DEFAULT MEMBER' AND ce.element_irid = d.obj# AND d.type# = 43 /* DIMENSION */ AND d.owner# = u.user_id UNION ALL SELECT ce.classification_irid descriptor_id, c.name descriptor_name, dim.owner entity_owner, dim.name entity_name, ce.secondary_object_name child_entity_name, null secondary_child_entity_name, cvp.parametername parameter_name, cvp.parametervalue parameter_value, cvp.parametervalue2 parameter_value2, null parameter_value3, null parameter_value4, cvp.position position from /* DEFAULT MEMBER2 */ cwm$classification c, cwm$classificationentry ce, cwm2$classificationvaluepair cvp, cwm$classificationtype cty, cwm2$dimension dim, cwm2$hierarchy hier where cty.irid = c.classificationtype_irid and cty.name not in ('ORACLE_OLAP2_CATALOG', 'ORACLE_OLAP_CATALOG') and c.irid = ce.classification_irid and ce.irid = cvp.classentry_irid (+) and ce.name = 'DEFAULT MEMBER2' and ce.element_irid = hier.irid and hier.dimension_irid = dim.irid UNION ALL SELECT ce.classification_irid descriptor_id , c.name descriptor_name , u.username entity_owner , d.name entity_name , ce.secondary_object_name child_entity_name , ce.tertiary_object_name secondary_child_entity_name , cvp.parametername parameter_name , cvp.parametervalue parameter_value , null parameter_value2 , null parameter_value3 , null parameter_value4 , null position FROM /* ESTIMATED CARDINALITY */ cwm$classification c , cwm$classificationentry ce , cwm$classificationtype cty , cwm2$classificationvaluepair cvp , cwm$level lev , sys.obj$ d , dba_users u WHERE cty.irid = c.classificationtype_irid AND cty.name <> 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.irid = cvp.classentry_irid (+) AND ce.name = 'ESTIMATED CARDINALITY' AND ce.element_irid = lev.irid AND lev.dimension_irid = d.obj# AND d.type# = 43 /* DIMENSION */ AND d.owner# = u.user_id UNION ALL SELECT ce.classification_irid descriptor_id, c.name descriptor_name, dim.owner entity_owner, dim.name entity_name, ce.secondary_object_name child_entity_name, ce.tertiary_object_name secondary_child_entity_name, cvp.parametername parameter_name, cvp.parametervalue parameter_value, null parameter_value2, null parameter_value3, null parameter_value4, null position from /* ESTIMATED CARDINALITY2 */ cwm$classification c, cwm$classificationentry ce, cwm2$classificationvaluepair cvp, cwm$classificationtype cty, cwm2$dimension dim, cwm2$hierlevelrel hlr where cty.irid = c.classificationtype_irid and cty.name not in ('ORACLE_OLAP2_CATALOG', 'ORACLE_OLAP_CATALOG') and c.irid = ce.classification_irid and ce.irid = cvp.classentry_irid (+) and ce.name = 'ESTIMATED CARDINALITY2' and ce.element_irid = hlr.irid and hlr.dimension_irid = dim.irid UNION ALL select ce.classification_irid descriptor_id, c.name descriptor_name, cub.owner entity_owner, cub.name entity_name, ce.secondary_object_name child_entity_name, ce.tertiary_object_name secondary_child_entity_name, cvp.parametername parameter_name, cvp.parametervalue parameter_value, cvp.parametervalue2 parameter_value2, cvp.parametervalue3 parameter_value3, cvp.parametervalue4 parameter_value4, cvp.position position from /* FACT TABLE JOIN2 */ cwm$classification c, cwm$classificationentry ce, cwm2$classificationvaluepair cvp, cwm$classificationtype cty, cwm2$cube cub, cwm2$dimension dim, cwm2$cubedimensionuse cdu where cty.irid = c.classificationtype_irid and cty.name <> 'ORACLE_OLAP2_CATALOG' and c.irid = ce.classification_irid and ce.name = 'FACT TABLE JOIN2' and ce.element_irid = cdu.irid and cub.irid = cdu.cube_irid and ce.tertiary_object_name = dim.name and dim.irid = cdu.dimension_irid and ce.irid = cvp.classentry_irid (+) with read only / REM Union View for dba$olap2ucatalog_entity_uses create or replace view olapsys.dba$olap2ucatalog_entity_uses as select distinct ce.classification_irid catalog_id , sch.physicalname entity_owner , cub.physicalname entity_name , msr.physicalname child_entity_name FROM cwm$classification c , cwm$classificationentry ce , cwm$classificationtype cty , cwm$measure msr , cwm$cube cub , cwm$model sch , dba_users u , cwm$cubedimensionuse cdu , cwm$dimension cd , dba_users du , sys.obj$ do WHERE cty.irid = c.classificationtype_irid AND cty.name = 'ORACLE_OLAP_CATALOG' AND c.irid = ce.classification_irid AND ce.name = 'MEASURE' AND ce.element_irid = msr.irid AND msr.itemcontainer_irid = cub.irid AND sch.irid = cub.datamodel_irid AND sch.physicalname = u.username AND cdu.cube_irid = cub.irid AND cdu.dimension_owner = du.username AND cdu.dimension_name = do.name AND du.user_id = do.owner# AND do.obj# = cd.irid AND do.type# = 43 AND cd.irid = cdu.abstractdimension_irid union all select catalog_id, entity_owner, entity_name, child_entity_name from olapsys.dba$olap2_catalog_entity_uses with read only / REM Union view for dba$olap2ufact_table_gid create or replace view olapsys.dba$olap2ufact_table_gid as select owner, cube_name, dimension_owner, dimension_name, hierarchy_name, dim_hier_combo_id, fact_table_owner, fact_table_name, column_name from dba$olap2_fact_table_gid with read only / REM Union view for dba$olap2uhier_custom_sort create or replace view olapsys.dba$olap2uhier_custom_sort as select u.username owner, o.name dimension_name, h.hiername hierarchy_name, tu.username table_owner, tn.name table_name, c.name column_name, hcs.position position, hcs.sortpos sort_pos, hcs.sortorder sort_order, hcs.nullorder null_order, decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(c.scale, null, decode(c.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'), 105, 'MLSLABEL', 106, 'MLSLABEL', 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 178, 'TIME(' ||c.spare1|| ')', 179, 'TIME(' ||c.spare1|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||c.spare1|| ')', 181, 'TIMESTAMP(' ||c.spare1|| ')' || ' WITH TIME ZONE', 182, 'INTERVAL YEAR(' ||c.spare2||') TO MONTH', 183, 'INTERVAL DAY(' ||c.spare2||') TO SECOND(' || c.spare1 || ')', 208, 'UROWID', 'UNDEFINED') data_type , decode(c.length, null, 0, c.length) data_length , decode(c.precision#, null, 0, c.precision#) data_precision from dba_users u, sys.obj$ o, sys.dim$ d, sys.hier$ h, cwm2$hiercustomsort hcs, dba_users tu, sys.obj$ tn, sys.col$ c where u.user_id = o.owner# and o.type# = 43 and o.obj# = d.obj# and d.obj# = h.dimobj# and hcs.dimension_irid = d.obj# and hcs.hier_irid = h.hierid# and hcs.metadataversion = 'ONE' and hcs.tablename_id = tn.obj# and tu.user_id = tn.owner# and c.obj# = tn.obj# and c.col# = hcs.columnname_id union select u.username owner, o.name dimension_name, null hierarchy_name, tu.username table_owner, tn.name table_name, c.name column_name, hcs.position position, hcs.sortpos sort_pos, hcs.sortorder sort_order, hcs.nullorder null_order, decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(c.scale, null, decode(c.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'), 105, 'MLSLABEL', 106, 'MLSLABEL', 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 178, 'TIME(' ||c.spare1|| ')', 179, 'TIME(' ||c.spare1|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||c.spare1|| ')', 181, 'TIMESTAMP(' ||c.spare1|| ')' || ' WITH TIME ZONE', 182, 'INTERVAL YEAR(' ||c.spare2||') TO MONTH', 183, 'INTERVAL DAY(' ||c.spare2||') TO SECOND(' || c.spare1 || ')', 208, 'UROWID', 'UNDEFINED') data_type , decode(c.length, null, 0, c.length) data_length , decode(c.precision#, null, 0, c.precision#) data_precision from dba_users u, sys.obj$ o, sys.dim$ d, cwm2$hiercustomsort hcs, dba_users tu, sys.obj$ tn, sys.col$ c where u.user_id = o.owner# and o.type# = 43 and o.obj# = d.obj# and hcs.dimension_irid = d.obj# and hcs.metadataversion = 'ONE' and hcs.tablename_id = tn.obj# and tu.user_id = tn.owner# and c.obj# = tn.obj# and c.col# = hcs.columnname_id union all select owner, dimension_name, hierarchy_name, table_owner, table_name, column_name, position, sort_pos, sort_order, null_order, data_type, data_length, data_precision from dba$olap2_hier_custom_sort with read only / REM REM Union Hierarchical Dimension Views for CwM2 REM REM dba$olap2uhierdims create or replace view olapsys.dba$olap2uhierdims as select owner, dimension_name, plural_name, display_name, description, default_display_hierarchy, descriptor_value from olapsys.dba$olap9i1_hier_dimensions union all select owner, dimension_name, plural_name, display_name, description, default_display_hierarchy, descriptor_value from olapsys.dba$olap9i2_hier_dimensions with read only / REM REM Union List Dimension Views with Dimension Descriptor type for CwM2 REM create or replace view olapsys.dba$olap2ulistdims as select owner, dimension_name, nvl(plural_name, dimension_name) plural_name, nvl(display_name, dimension_name) display_name, nvl(short_description, dimension_name) short_description, nvl(description, dimension_name) description, descriptor_value, table_owner table_owner, table_name table_name, column_name column_name, data_type data_type, data_length data_length, data_precision data_precision, position column_position from ( select u.username owner, d.name dimension_name, dim.pluralname plural_name, dim.displayname display_name, dim.description short_description, dim.description description, decode(o.status, 5, 'Y', 'N') invalid, tu.username table_owner, tn.name table_name, c.name column_name, decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(c.scale, null, decode(c.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'), 105, 'MLSLABEL', 106, 'MLSLABEL', 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 178, 'TIME(' ||c.spare1|| ')', 179, 'TIME(' ||c.spare1|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||c.spare1|| ')', 181, 'TIMESTAMP(' ||c.spare1|| ')' || ' WITH TIME ZONE', 182, 'INTERVAL YEAR(' ||c.spare2||') TO MONTH', 183, 'INTERVAL DAY(' ||c.spare2||') TO SECOND(' || c.spare1 || ')', 208, 'UROWID', 'UNDEFINED') data_type , decode(c.length, null, 0, c.length) data_length , decode(c.precision#, null, 0, c.precision#) data_precision, k.keypos# position, (case when ce.classification_irid = 28 then 'Time' else 'Other' end) descriptor_value from dba_users u, sys.obj$ d, sys.dimlevel$ l, sys.dimlevelkey$ k, sys.col$ c, sys.obj$ tn, dba_users tu, cwm$dimension dim, sys.obj$ o, sys.dim$ sd, olapsys.cwm$classificationentry ce where u.user_id = d.owner# and d.type# = 43 and o.obj# = sd.obj# and sd.obj# = dim.irid and d.obj# = dim.irid and d.obj# = l.dimobj# and l.dimobj# = k.dimobj# and l.levelid# = k.levelid# and k.detailobj# = c.obj# and k.col# = c.col# and tn.obj# = c.obj# and tu.user_id = tn.owner# and (not exists (select * from cwm$hierarchy h where dim.irid = h.dimension_irid)) AND dim.irid = ce.element_irid (+) and ce.name (+) = 'DIMENSION' UNION ALL select d.owner owner, d.name dimension_name, d.pluralname plural_name, d.displayname display_name, d.shortdescription short_description, d.description description, d.invalid invalid, u.username table_owner, o.name table_name, c.name column_name, decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(c.scale, null, decode(c.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'), 105, 'MLSLABEL', 106, 'MLSLABEL', 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 178, 'TIME(' ||c.spare1|| ')', 179, 'TIME(' ||c.spare1|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||c.spare1|| ')', 181, 'TIMESTAMP(' ||c.spare1|| ')' || ' WITH TIME ZONE', 182, 'INTERVAL YEAR(' ||c.spare2||') TO MONTH', 183, 'INTERVAL DAY(' ||c.spare2||') TO SECOND(' || c.spare1 || ')', 208, 'UROWID', 'UNDEFINED') data_type , decode(c.length, null, 0, c.length) data_length , decode(c.precision#, null, 0, c.precision#) data_precision, dhlm.position position, (case when ce.classification_irid = 28 then 'Time' else 'Other' end) descriptor_value from olapsys.cwm2$dimension d, olapsys.cwm2$hierarchy h, olapsys.cwm2$level l, olapsys.cwm2$hierlevelrel hlr, olapsys.cwm2$dimhierlvlmap dhlm, dba_users u, sys.obj$ o, sys.col$ c, olapsys.cwm$classificationentry ce where h.dimension_irid = d.irid and (h.hidden = 'Y' or h.hidden is null) and h.irid = hlr.hierarchy_irid and l.irid = hlr.childlevel_irid and dhlm.dimhierlvl_irid = hlr.irid and dhlm.object_id = o.obj# and dhlm.column_id = c.col# and o.obj# = c.obj# and o.owner# = u.user_id and d.irid = ce.element_irid (+) and ce.name (+) = 'DIMENSION2' ) with read only / REM REM Union Views for Hierarchical Dimension key column mappings CwM2 REM create or replace view olapsys.dba$olap2uhierdim_keycol_map as select owner owner, dimension_name dimension_name, hierarchy_name hierarchy_name, level_name level_name, display_name display_name, short_description short_description, description description, pos hierarchy_position, table_owner table_owner, table_name table_name, column_name column_name, column_position column_position, data_type data_type, data_length data_length, data_precision data_precision from olapsys.dba$olap2_dim_levels_keymaps union all select owner owner, dimension_name dimension_name, hierarchy_name hierarchy_name, level_name level_name, display_name display_name, short_description short_description, description description, pos hierarchy_position, table_owner table_owner, table_name table_name, column_name column_name, column_position column_position, data_type data_type, data_length data_length, data_precision data_precision from olapsys.dba$olapmr_dim_levels_keymaps with read only / REM REM Union View for Hierarchies REM create or replace view olapsys.dba$olap2udim_hiers as select h.owner owner, h.dimension_name dimension_name, h.hierarchy_name hierarchy_name, h.display_name display_name, h.short_description short_description, h.description description, h.solved_code solved_code, 'Y' is_default from olapsys.dba$olap2udimensions d, olapsys.dba$olap2udim_hierarchies h where d.owner = h.owner and d.dimension_name = h.dimension_name and d.default_display_hierarchy = h.hierarchy_name union select h.owner owner, h.dimension_name dimension_name, h.hierarchy_name hierarchy_name, h.display_name display_name, h.short_description short_description, h.description description, h.solved_code solved_code, 'N' is_default from olapsys.dba$olap2udim_hierarchies h where not exists (select * from olapsys.dba$olap2udimensions d where d.owner = h.owner and d.dimension_name = h.dimension_name and d.default_display_hierarchy = h.hierarchy_name) / rem MeasureDimensionView REM NEEDS WORK... REM ..very OLAPI specific for measure-dimension...can possibly be removed REM when OLAPI metadatareader supports multi-column keys on REM List Dimensions... create or replace view olapsys.dba$olap2_mr_measdimview as select owner || '.' || cube_name || '.' || measure_name measid, display_name display_name, description description from olapsys.dba$olap2ucube_measures with read only / REM REM Fact Table Fact Column Mappings for CWM1 REM REM NEEDS WORK...It would seem this query would perform REM faster by changing REM olapsys.dba$olap2ucube_measure_maps to REM olapsys.dba$olap_cube_measure_maps and then also REM removing the where condition for REM a.dim_hier_combo_id = 0..... REM However, this was not at all the case....when trying REM the above, the query performed much worse in SQLPlus ?????? REM REM If the above is resolved to use olapsys.dba$olap_cube_measure_maps REM instead of olapsys.dba$olap2ucube_measure_maps, then this query REM could be moved to the top of the file to the "new CWM1 API Views" REM section, which is really where it belongs. create or replace view olapsys.dba$olapmr_FactTblFctMaps as select a.owner owner, a.cube_name cube_name, a.measure_name measure_name, a.fact_table_owner fact_table_owner, a.fact_table_name fact_table_name, a.column_name column_name, c.data_type data_type, c.data_length data_length, c.data_precision data_precision, (case when c.data_type = 'NUMBER' then 0 when c.data_type = 'DOUBLE' then 5 when c.data_type = 'FLOAT' then 4 when c.data_type = 'DATE' then 7 when c.data_type = 'LONG' then 3 else 1 end) olap_api_data_type from olapsys.dba$olap2ucube_measure_maps a, dba_olap_columns c where a.dim_hier_combo_id = 0 and a.fact_table_owner = c.owner and a.fact_table_name = c.table_name and a.column_name = c.column_name with read only /