REM REM Copyright (c) 2001, 2006, Oracle. All rights reserved. REM REM NAME REM cwm2alvw.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 'all$olap2_' REM This file contains the all$ views for the base-level REM read api and the union read api views. The union api REM views union metadata from the CWM$ metadata tables with REM metadata from the CWM2$ release 2 metadata tables. REM REM NOTES REM REM MODIFIED (MM/DD/YY) REM glyon 06/24/08 - bug 7204558: eliminate references to sys.user$ REM mstasiew 08/23/06 - datatypes for cwm1 dim key cols REM mstasiew 08/18/04 - 3808848 REM mstasiew 12/04/03 - REM mstasiew 12/02/03 - 3002925 REM dbardwel 11/13/03 - Fix missing outer-join in entity_parameters view REM cdalessi 10/21/03 - change refs to cwmlite REM dbardwel 10/13/03 - Further updates to the cwm1 part of the REM cube-related union views to address possible REM metadata anomoly with how a cwm1 cube is REM constructed that is not compatible with OLAPI REM The scenario is that a cwm1 cube is created with REM dimensions that are purely dimension objects. REM dbardwel 09/30/03 - updates for all_olap2_entity_ext_parms REM dbardwel 09/16/03 - REM dbardwel 09/10/03 - 10g descriptor view changes for ODBO support REM mstasiew 08/27/03 - REM dbardwel 06/18/03 - Final fixes for making sure not to include REM standalone RDBMS Dimension object that is not REM tied to olap catalog dimension metadata. REM This is bug 3011438. REM dbardwel 06/16/03 - Merge with Mark's last txn for final version REM mstasiew 06/12/03 - add olapsys.CWM2$MRTMPCWM1AGGORD REM dbardwel 06/11/03 - REM dbardwel 06/10/03 - Hidden Hierarchy should come back as null in mapping views REM dbardwel 06/10/03 - Fix bug 3000955. Cube sys priv override is just -47. REM dbardwel 06/01/03 - Backporting aggregation views for 9i2 metadata REM mstasiew 05/15/03 - REM dbardwel 05/02/03 - Fixed union view level attr map 9i1 portion REM with all the codestreams this fix got lost and REM needed to be applied again. REM mstasiew 03/19/03 - REM mstasiew 03/17/03 - REM dbardwel 01/27/03 - Add selecting short description in main line REM mstasiew 12/17/02 - REM mstasiew 12/16/02 - REM awesley 08/29/02 - REM awesley 11/07/01 awesley_txn100634 REM dbardwel 09/28/01 created for 9i release 2. REM dbardwel 10/15/01 additions/performance changes REM dbardwel 11/01/01 MV_SUMMARY_CODE updates REM dbardwel 11/13/01 Fix for HierCustomSort view REM dbardwel 11/15/01 Fix union view for attribute maps 9i1 portion REM dbardwel 12/03/01 Fix bug in fact_level_uses view needed to modify REM hierarchical join condition REM mstasiew 11/19/01 mstasiew_txn100734..add new views primarily for REM metadatareaders REM dbardwel 12/07/01 dbardwel_txn101018 remove reference of hidden from REM hierarchy and level views. REM mstasiew 12/12/01 mstasiew_txn101056..fix Olapi measuredim view REM dbardwel 12/19/01 do not allow hidden hierarchies REM to show up in dim_hierarchies view REM mstasiew 01/03/02 mstasiew_txn101058. exclude hidden hierarchies REM dbardwel 01/10/02 added new version of 9i1 olap_cube view to REM support mv_summarycode REM dbardwel 01/18/02 bug fix for union map attribute view missing REM security filter. REM dbardwel 01/28/02 added correct mv_summarycode support to REM Fact Table Key Column Mappings for CWM1 REM mstasiew 01/29/02 mstasiew_txn101701 REM mstasiew 01/31/02 mstasiew_txn101768 REM mstasiew 02/01/02 mstasiew_txn101798 REM dbardwel 02/06/02 fix classification view issue with dimension/dimension attribute REM dbardwel 02/07/02 fixed missed outer-join from last fix REM dbardwel 02/07/02 added replacement for 9i1 specific view all$olapmr_dim_attributes REM dbardwel 02/25/02 adding 3 9i1 Metadata reader views to eliminate any 9i1 to 9i2 REM upgrade issues per Rae Burns at NEDC. Note: already added REM all$olapmr_dim_attributes from an earlier change. REM dbardwel 05/03/02 9.2.0.2 patch. Fix 9i2 all$olap2_join_key_column_uses to REM return correct level in case of snowflake schema. The 9i2 REM view was not consist with the 9i1 view on this. REM REM ************************************************************* CREATE OR REPLACE VIEW all$olapmr_attributes AS SELECT u.username dim_owner, d.name dim_name, dat.PHYSICALNAME dim_attribute_name, l.levelname levelname, decode(c.type#, 2, decode(c.scale,NULL, decode(c.precision#, NULL, 'DOUBLE', 'FLOAT'), 'DOUBLE'), 8, 'DOUBLE', 12, 'DATE', 'STRING') dtype, cu.username col_owner, ct.name col_table, c.name col_name FROM dba_users u, sys.obj$ d, sys.dimlevel$ l, sys.dimattr$ a, sys.col$ c, sys.obj$ ct, dba_users cu, cwm$levelattribute lat, cwm$itemuse iu1, cwm$itemuse iu2, cwm$dimensionattribute dat WHERE u.user_id = d.owner# AND d.type# = 43 AND /* DIMENSION */ ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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# AND c.obj# = ct.obj# AND ct.owner# = cu.user_id AND c.obj# = lat.type_irid AND c.name = lat.physicalname AND lat.irid = iu2.mappable_irid AND iu2.operation_irid_1 = iu1.operation_irid AND iu1.mappable_irid = dat.irid ORDER BY u.username, d.name, dat.physicalname / CREATE OR REPLACE VIEW all$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 AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) WITH READ ONLY / CREATE OR REPLACE VIEW all$olapmr_dim_levels 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 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 WHERE d.type# = 43 AND /* DIMENSION */ ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) AND 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.keypos# = 1 AND /* Restrict composite keys to 1 rec */ k.detailobj# = t.obj# AND t.owner# = tu.user_id AND dl.dimobj# = lev.dimension_irid AND dl.levelname = lev.physicalname (+) ORDER BY owner ASC, dimension_name ASC, hierarchy_name ASC, pos DESC / REM Need to adjust this 9i1 metadata reader view for REM known classification issue CREATE OR REPLACE VIEW all$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' AND ( cwm$util.dimension_tables_visible(o.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) WITH READ ONLY / 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.all$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 do.type# = 43 AND du.user_id = do.owner# AND do.obj# = cd.irid AND cd.irid = cdu.ABSTRACTDIMENSION_IRID AND (cwm$util.fact_table_visible(cub.irid) = 'Y' OR EXISTS /* SELECT ANY TABLE */ (SELECT null FROM v$enabledprivs WHERE priv_number = -47)) WITH READ ONLY / REM REM Level Key Column Mappings for CWM1 Hierarchical Dimensions REM CREATE OR REPLACE VIEW olapsys.all$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 */ ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) AND 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 all$olap1_fact_level_uses CREATE OR REPLACE VIEW all$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 (cwm$util.fact_table_visible(cub.irid) = 'Y' OR EXISTS /* SELECT ANY TABLE */ (SELECT null FROM v$enabledprivs WHERE priv_number = -47)) 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.type# = 43 AND do.obj# = cd.irid AND cd.irid = cdu.abstractdimension_irid AND cd.physicalname = cdu.dimension_name with read only / REM REM Fact Table Key Column Mappings for CWM1 REM create or replace view olapsys.all$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, fk.data_type data_type, fk.data_length data_length, fk.data_precision data_precision from olapsys.all$olap1_fact_level_uses a, olapsys.all$olap1_cubes b, (select u.username table_owner, t.name table_name, c.name key_name, col.name column_name, ccol.pos# position, 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 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 all$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 ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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 REM NEEDS WORK. This view has no security filtering. REM Does it really even need it? create or replace view olapsys.all$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.all$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 all$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 dim.invalid = 'N' and h.hidden = 'N' and (cwm2$security.dimension_tables_visible(dim.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) AND dim.irid = ce.element_irid (+) and ce.name (+) = 'DIMENSION2' WITH READ ONLY / REM REM Cube Model Views for CwM2 REM REM all$olap2_Cubes create or replace view olapsys.all$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, decode(c.invalid, 'N', 'DEFAULT', 'O', 'OLAPI', 'INVALID') type_of_validation from olapsys.CwM2$Cube c where (c.invalid = 'N' or c.invalid = 'O') and (cwm2$security.fact_table_visible(c.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) with read only / REM all$olap2_Cube_Measures create or replace view olapsys.all$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 and (c.invalid = 'N' or c.invalid = 'O') and (cwm2$security.fact_table_visible(c.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) with read only / REM all$olap2_Cube_Dim_Uses create or replace view olapsys.all$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 (+) and (c.invalid = 'N' or c.invalid = 'O') and (cwm2$security.fact_table_visible(c.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) with read only / REM all$olap2_Cube_Meas_Dim_Uses create or replace view olapsys.all$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 and (c.invalid = 'N' or c.invalid = 'O') and (cwm2$security.fact_table_visible(c.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) with read only / REM REM Dimension Model for CwM2 views REM REM all$olap2_Dimensions create or replace view olapsys.all$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 invalid = 'N' and d.DefaultHier_IRID = h.irid (+) and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) with read only / REM all$olap2_Dim_Hierarchies create or replace view olapsys.all$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 d.invalid = 'N' and h.hidden = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) with read only / REM all$olap2_Dim_Levels create or replace view olapsys.all$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 and d.invalid = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) with read only / REM all$olap2_Dim_Hier_Level_Uses create or replace view olapsys.all$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 and d.invalid = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) with read only / REM all$olap2_Dim_Attributes create or replace view olapsys.all$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' and d.invalid = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) with read only / REM all$olap2_Dim_Attr_Uses create or replace view olapsys.all$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 and d.invalid = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) with read only / REM all$olap2_Dim_Level_Attributes create or replace view olapsys.all$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 and d.invalid = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) with read only / REM all$olap2_Cube_Measure_Maps create or replace view olapsys.all$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# and (c.invalid = 'N' or c.invalid = 'O') and (cwm2$security.fact_table_visible(c.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) with read only / REM all$olap2_Fact_Level_Uses create or replace view olapsys.all$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 and (c.invalid = 'N' or c.invalid = 'O') and (cwm2$security.fact_table_visible(c.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) with read only / REM REM Aggregation Operator view for 9i2 REM create or replace view olapsys.all$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 and (c.invalid = 'N' or c.invalid = 'O') and (cwm2$security.fact_table_visible(c.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) with read only / REM REM Aggregation Weight By view for 9i2 REM create or replace view olapsys.all$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 and (c.invalid = 'N' or c.invalid = 'O') and (cwm2$security.fact_table_visible(c.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) with read only / REM REM Aggregation Complete view REM create or replace view olapsys.all$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.all$olap2_agg_operator_uses aggop, olapsys.all$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 all$olap2_join_key_column_uses create or replace view olapsys.all$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' and d.invalid = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) 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' and d.invalid = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) with read only / REM all$olap2_level_key_col_uses create or replace view olapsys.all$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 and d.invalid = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) with read only / REM all$olap2_Dim_Level_Attr_Maps create or replace view olapsys.all$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 and d.invalid = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217)))) attr with read only / REM all$olap2_dim_levels_keymaps CREATE OR REPLACE VIEW olapsys.all$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 and d.invalid = 'N' and h.hidden = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) with read only / REM REM Fact Tbl Fact Col Maps for CWM2 REM create or replace view olapsys.all$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.all$olap2_cube_measure_maps cmm, all_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.all$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 all$olap2_entity_desc_uses create or replace view olapsys.all$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 and (cub.invalid = 'N' or cub.invalid = 'O') and (cwm2$security.fact_table_visible(cub.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) 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 and (cub.invalid = 'N' or cub.invalid = 'O') and (cwm2$security.fact_table_visible(cub.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) 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 and dim.invalid = 'N' and (cwm2$security.dimension_tables_visible(dim.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) 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 and dim.invalid = 'N' and (cwm2$security.dimension_tables_visible(dim.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) 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 and dim.invalid = 'N' and (cwm2$security.dimension_tables_visible(dim.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) 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 and dim.invalid = 'N' and (cwm2$security.dimension_tables_visible(dim.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) 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 and dim.invalid = 'N' and (cwm2$security.dimension_tables_visible(dim.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) with read only / REM all$olap2_catalog_entity_uses create or replace view olapsys.all$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 and (cub.invalid = 'N' or cub.invalid = 'O') and (cwm2$security.fact_table_visible(cub.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) with read only / REM all$olap2_fact_table_gid create or replace view olapsys.all$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# and (c.invalid = 'N' or c.invalid = 'O') and (cwm2$security.fact_table_visible(c.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) with read only / REM REM Solved-Rollup Style Fact Table Key Column Mappings for CWM2 REM create or replace view olapsys.all$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.all$olap2_fact_level_uses flu, olapsys.all$olap2_dim_levels_keymaps lkm, olapsys.all$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 Per Mark's fix in 9.2.0.3.1 need to inline now REM Unsolved and Solved-ET Style Fact Tbl Key Col Maps for CWM2 REM create or replace view olapsys.all$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 and (c.invalid = 'N' or c.invalid = 'O') and (cwm2$security.fact_table_visible(c.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) ) 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# and (c.invalid = 'N' or c.invalid = 'O') and (cwm2$security.fact_table_visible(c.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) ) ftg, olapsys.all$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 all$olap2_hier_custom_sort create or replace view olapsys.all$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' and d.invalid = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) with read only / REM ************************************************************* REM REM All the union api views REM REM Union view for all$olap2ucubes create or replace view olapsys.all$olap2ucubes as select owner, cube_name, invalid, display_name, short_description, description, mv_summarycode from olapsys.all$olap1_cubes union all select owner, cube_name, invalid, display_name, short_description, description, mv_summarycode from olapsys.all$olap2_cubes with read only / REM Union view for all$olap2ucube_measures create or replace view olapsys.all$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.type# = 43 AND do.obj# = cd.irid AND cd.irid = cdu.abstractdimension_irid AND ( cwm$util.fact_table_visible(cub.irid) = 'Y' OR EXISTS /* SELECT ANY TABLE */ (SELECT null FROM v$enabledprivs WHERE priv_number = -47)) union all select owner, cube_name, measure_name, display_name, short_description, description from olapsys.all$olap2_cube_measures with read only / REM Union view for all$olap2ucube_dim_uses REM join in cwm$dimension for this view create or replace view olapsys.all$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 AND (cwm$util.fact_table_visible(cub.irid) = 'Y' OR EXISTS /* SELECT ANY TABLE */ (SELECT null FROM v$enabledprivs WHERE priv_number = -47)) 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.all$olap2_cube_dim_uses with read only / REM Union view for all$olap2ucube_meas_dim_uses create or replace view olapsys.all$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 (cwm$util.fact_table_visible(cub.irid) = 'Y' OR EXISTS /* SELECT ANY TABLE */ (SELECT null FROM v$enabledprivs WHERE priv_number = -47)) 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.type# = 43 AND do.obj# = cd.irid 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.all$olap2_cube_meas_dim_uses with read only / REM Union view for all$olap2udimensions create or replace view olapsys.all$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.all$olapmr_dimensions mrd union all select owner, dimension_name, plural_name, display_name, short_description, description, default_display_hierarchy, invalid, dimension_type from olapsys.all$olap2_dimensions with read only / REM Union View for all$olap2udim_hierarchies create or replace view olapsys.all$olap2udim_hierarchies as select dh.owner, dh.dimension_name, dh.hierarchy_name, dh.display_name, dh.shortdesc short_description, dh.longdesc description, 'UL' solved_code from (SELECT u.username owner , d.name dimension_name , h.hiername hierarchy_name , hie.displayname display_name , hie.description shortdesc , hie.description longdesc FROM dba_users u , sys.obj$ d , sys.hier$ h , cwm$hierarchy hie WHERE u.user_id = d.owner# AND d.type# = 43 /* DIMENSION */ AND (cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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.all$olap2_dim_hierarchies with read only / REM Union view for all$olap2udim_levels create or replace view olapsys.all$olap2udim_levels as select dl1.owner owner, dl1.dimension_name dimension_name, dl1.level_name level_name, dl1.display_name display_name, dl1.shortdesc short_description, dl1.longdesc description, dl1.level_table_owner, dl1.level_table_name from (SELECT u.username owner , d.name dimension_name , l.levelname level_name , lev.displayname display_name , lev.description shortdesc , lev.description longdesc , 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 u.user_id = d.owner# AND d.type# = 43 /* DIMENSION */ AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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 (+)) dl1 union all select owner, dimension_name, level_name, display_name, short_description, description, null level_table_owner, null level_table_name from olapsys.all$olap2_dim_levels with read only / REM Union view for all$olap2udim_hier_level_uses REM Need to incorporate cwm$hierarchy into 9i1 portion of view create or replace view olapsys.all$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 u.user_id = d.owner# AND d.type# = 43 /* DIMENSION */ AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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.all$olap2_dim_hier_level_uses with read only / REM Union view for all$olap2udim_attributes create or replace view olapsys.all$olap2udim_attributes as select owner, dimension_name, attribute_name, display_name, short_description, description, desc_id from all$olapmr_dim_attributes union all select owner, dimension_name, attribute_name, display_name, short_description, description, desc_id from all$olap2_dim_attributes with read only / REM Union view for all$olap2udim_attr_uses create or replace view olapsys.all$olap2udim_attr_uses as select owner, dimension_name, dim_attribute_name, level_name, lvl_attribute_name from olapsys.all$olap_dim_attr_uses union all select owner, dimension_name, dim_attribute_name, level_name, lvl_attribute_name from all$olap2_dim_attr_uses with read only / REM Union view for all$olap2udim_level_attributes create or replace view olapsys.all$olap2udim_level_attributes as select dla1.owner owner, dla1.dimension_name dimension_name, dla1.attribute_name attribute_name, dla1.display_name display_name, dla1.shortdesc short_description, dla1.longdesc description, dla1.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 shortdesc , la.lat_description longdesc , 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 u.user_id = d.owner# AND d.type# = 43 /* DIMENSION */ AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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) dla1 union all select owner, dimension_name, attribute_name, display_name, short_description, description, determined_by_level_name from all$olap2_dim_level_attributes with read only / REM Union view for all$olap2ucube_measure_maps create or replace view olapsys.all$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 ( cwm$util.fact_table_visible(cub.irid) = 'Y' OR EXISTS /* SELECT ANY TABLE */ (SELECT null FROM v$enabledprivs WHERE priv_number = -47)) 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.type# = 43 AND do.obj# = cd.irid 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.all$olap2_cube_measure_maps with read only / REM Union view for all$olap2ufact_level_uses create or replace view olapsys.all$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.all$olap1_fact_level_uses a, all_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.all$olap2_fact_level_uses with read only / REM Union view for all$olap2ulevel_key_col_uses create or replace view olapsys.all$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 (cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) 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.all$olap2_level_key_col_uses with read only / REM Union view for all$olap2ujoin_key_column_uses create or replace view olapsys.all$olap2ujoin_key_column_uses as SELECT u.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 u , 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 u.user_id = d.owner# AND d.type# = 43 /* DIMENSION */ AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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 /*+ORDERED*/ u.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 u , 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 u.user_id = d.owner# AND d.type# = 43 /* DIMENSION */ AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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.all$olap2_join_key_column_uses with read only / REM Union view for all$olap2udim_level_attr_maps create or replace view olapsys.all$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 d.obj# = o.obj# and (cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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 (cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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.all$olap2_dim_level_attr_maps with read only / REM Union view for all$olap2ucatalogs create or replace view olapsys.all$olap2ucatalogs as select catalog_id, catalog_name, parent_catalog_id, description from olapsys.all$olap_catalogs union all select catalog_id, catalog_name, parent_catalog_id, description from olapsys.all$olap2_catalogs with read only / REM Union view for all$olap2uentity_desc_uses create or replace view olapsys.all$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 AND (cwm$util.fact_table_visible(cub.irid) = 'Y' OR EXISTS /* SELECT ANY TABLE */ (SELECT null FROM v$enabledprivs WHERE priv_number = -47)) 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 AND (cwm$util.fact_table_visible(cub.irid) = 'Y' OR EXISTS /* SELECT ANY TABLE */ (SELECT null FROM v$enabledprivs WHERE priv_number = -47)) 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 AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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 AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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 AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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.all$olap2_entity_desc_uses with read only / REM Union View for all$olap2ucatalog_entity_uses create or replace view olapsys.all$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 AND (cwm$util.fact_table_visible(cub.irid) = 'Y' OR EXISTS /* SELECT ANY TABLE */ (SELECT null FROM v$enabledprivs WHERE priv_number = -47)) union all select catalog_id, entity_owner, entity_name, child_entity_name from olapsys.all$olap2_catalog_entity_uses with read only / REM Union view for new 10g all$olap2uentity_parameters CREATE OR REPLACE VIEW all$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 AND (cwm$util.fact_table_visible(cub.irid) = 'Y' OR EXISTS /* SELECT ANY TABLE */ (SELECT null FROM v$enabledprivs WHERE priv_number = -47)) 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 AND (cwm$util.fact_table_visible(cub.irid) = 'Y' OR EXISTS /* SELECT ANY TABLE */ (SELECT null FROM v$enabledprivs WHERE priv_number = -47)) 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 AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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 AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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 AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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 (+) and (cub.invalid = 'N' or cub.invalid = 'O') and (cwm2$security.fact_table_visible(cub.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) 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 and (cub.invalid = 'N' or cub.invalid = 'O') and (cwm2$security.fact_table_visible(cub.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) 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 and dim.invalid = 'N' and (cwm2$security.dimension_tables_visible(dim.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) 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 and dim.invalid = 'N' and (cwm2$security.dimension_tables_visible(dim.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) 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 and dim.invalid = 'N' and (cwm2$security.dimension_tables_visible(dim.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) 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 and dim.invalid = 'N' and (cwm2$security.dimension_tables_visible(dim.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) 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 and dim.invalid = 'N' and (cwm2$security.dimension_tables_visible(dim.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) with read only / REM Union view for new 10g all$olap2uentity_ext_parms REM provides extension level descriptor information REM note that cwm1 default member has 2 parts for list dim support CREATE OR REPLACE VIEW all$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 AND (cwm$util.fact_table_visible(cub.irid) = 'Y' OR EXISTS /* SELECT ANY TABLE */ (SELECT null FROM v$enabledprivs WHERE priv_number = -47)) 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 (+) and (cub.invalid = 'N' or cub.invalid = 'O') and (cwm2$security.fact_table_visible(cub.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) 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 AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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 AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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 and dim.invalid = 'N' and (cwm2$security.dimension_tables_visible(dim.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) 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 AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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 and dim.invalid = 'N' and (cwm2$security.dimension_tables_visible(dim.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) 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 (+) and (cub.invalid = 'N' or cub.invalid = 'O') and (cwm2$security.fact_table_visible(cub.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) with read only / REM Union view for all$olap2ufact_table_gid create or replace view olapsys.all$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 all$olap2_fact_table_gid with read only / REM Union view for all$olap2uhier_custom_sort create or replace view olapsys.all$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 all$olap2_hier_custom_sort with read only / REM REM Union Hierarchical Dimension Views for CwM2 REM REM all$olap2uhierdims create or replace view olapsys.all$olap2uhierdims as select owner, dimension_name, plural_name, display_name, short_description, description, default_display_hierarchy, descriptor_value from olapsys.all$olap9i1_hier_dimensions union all select owner, dimension_name, plural_name, display_name, short_description, description, default_display_hierarchy, descriptor_value from olapsys.all$olap9i2_hier_dimensions with read only / REM REM Union List Dimension Views with Dimension Descriptor type for CwM2 REM create or replace view olapsys.all$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, level_name level_name 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, l.levelname level_name 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 (cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) 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, l.name level_name 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.invalid = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) 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.all$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.all$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.all$olapmr_dim_levels_keymaps with read only / REM REM Union View for Hierarchies REM create or replace view olapsys.all$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.all$olap2udimensions d, olapsys.all$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.all$olap2udim_hierarchies h where not exists (select * from olapsys.all$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.all$olap2_mr_measdimview as select owner || '.' || cube_name || '.' || measure_name measid, display_name display_name, description description from olapsys.all$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.all$olap2ucube_measure_maps to REM olapsys.all$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.all$olap_cube_measure_maps REM instead of olapsys.all$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.all$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.all$olap2ucube_measure_maps a, all_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 / create or replace view olapsys.CWM2$MRTMPCWM1AGGORD as select x.owner owner, x.cube_name cube_name, x.dimension_owner dimension_owner, x.dimension_name dimension_name, x.position position, 'CWM1' version_id, x.id id from ( select rownum position, cdu2.cube_dimension_use_id cube_dimension_use_id, cdu2.dependent_on_dim_use_id dependent_on_dim_use_id, cdu2.owner owner, cdu2.cube_name cube_name, cdu2.dimension_owner dimension_owner, cdu2.dimension_name dimension_name, cdu2.id id from ( SELECT cdu.irid cube_dimension_use_id , sch.physicalname owner , cub.physicalname cube_name , cdu.dimension_owner dimension_owner , cdu.dimension_name dimension_name , cdu.name dimension_alias , cdu.cubedimensionuse_irid dependent_on_dim_use_id , cdu.calc_hierarchy_name default_calc_hierarchy_name , cub.irid 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 cd.irid = do.obj# AND do.type# = 43 AND cd.irid = cdu.abstractdimension_irid ) cdu2 start with cdu2.dependent_on_dim_use_id is null connect by prior cdu2.cube_dimension_use_id = cdu2.dependent_on_dim_use_id order siblings by cdu2.owner, cdu2.cube_name ) x group by x.owner, x.cube_name, x.dimension_owner, x.dimension_name, x.position, x.id having count(x.dependent_on_dim_use_id) > 0 order by x.owner, x.cube_name, x.position ;