Rem
Rem $Header: cwmlite/admin/cwm2mrv2.sql /main/12 2008/07/28 13:25:50 glyon Exp $
Rem
Rem cwm2mrv2.sql
Rem
Rem Copyright (c) 2002, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      cwm2mrv2.sql - script contains view for performance enhanced
Rem                     metadata reader views
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    glyon       06/24/08 - bug 7204558: eliminate references to sys.user$
Rem    mstasiew    08/18/04 - 3808848
Rem    mstasiew    09/23/03 - 
Rem    mstasiew    09/09/03 - 
Rem    mstasiew    08/27/03 - 
Rem    dbardwel    09/02/03 - Remove outer-join from cwm1 view
Rem    mstasiew    06/10/03 - 
Rem    mstasiew    06/09/03 - 
Rem    mstasiew    05/16/03 - 
Rem    mstasiew    03/18/03 - 
Rem    dbardwel    01/27/03 - 
Rem    mstasiew    12/16/02 - 
Rem    mstasiew    09/24/02 - mstasiew_txn104178
Rem    mstasiew    09/03/02 - 
Rem    mstasiew    08/16/02 - 
Rem    mstasiew    09/03/02 - grabtrans 'mstasiew_txn104011'
Rem    dbardwel    07/25/02 - 
Rem    dbardwel    07/23/02 - Created
Rem


Rem
Rem Need 4 special views which will be used to populate special purpose
Rem dimension and cube olap session tables for the current user. The 
Rem views need to expose the IRID which the current views do not do.
Rem


CREATE OR REPLACE VIEW olapsys.mrv$olap1_pop_dimensions as select
  dim.irid id
FROM
  dba_users u,
  sys.obj$ o,
  sys.dim$ d, 
  cwm$dimension dim
WHERE u.user_id = o.owner# and
      o.obj# = d.obj# and
      d.obj# = dim.irid and
      cwm$util.dimension_tables_visible(d.obj#) = 'Y'
/


CREATE OR REPLACE VIEW olapsys.mrv$olap1_pop_cubes as select
 cub.irid id
FROM 
  cwm$model sch,
  cwm$cube cub
WHERE sch.irid = cub.datamodel_irid and
      cwm$util.fact_table_visible(cub.irid) = 'Y'
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_pop_dimensions as select
 d.irid id
FROM
 cwm2$dimension d
WHERE cwm2$security.dimension_tables_visible(d.irid) = 'Y'
/

CREATE OR REPLACE VIEW olapsys.mrv$olap2_pop_cubes as select
 c.irid id
FROM 
 cwm2$cube c
WHERE cwm2$security.fact_table_visible(c.irid) = 'Y'
/


Rem
Rem The views below will read from the REFRESHED metadata tables and join in
Rem most cases to the olap_session_objects table for security
Rem

create or replace view olapsys.mrv$olap2_awviews as select
 VIEW_OWNER,
 VIEW_NAME,
 ROWTOCELLCOL_NAME,
 AW_OWNER,
 AW_NAME
 from olapsys.cwm2$mrall_awviews
/

create or replace view olapsys.mrv$olap2_awviewcols as select
 VIEW_OWNER,
 VIEW_NAME,
 COLUMN_NAME,
 AWOBJECT
 from olapsys.cwm2$mrall_awviewcols
/  

CREATE OR REPLACE VIEW olapsys.mrv$olap2_catalogs as select
 CATALOG_ID,
 CATALOG_NAME,
 PARENT_CATALOG_ID,
 DESCRIPTION 
 from olapsys.cwm2$mrall_catalogs mrcat
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_catalog_entity_uses as select
  ceu.catalog_id catalog_id,
  ceu.entity_owner entity_owner,
  ceu.entity_name entity_name,
  ceu.child_entity_name child_entity_name
 from olapsys.cwm2$mrall_catalog_entity_uses ceu,
      olapsys.olap_session_objects oso
 where oso.version_id = ceu.version_id and
       oso.id = ceu.id
/

CREATE OR REPLACE VIEW olapsys.mrv$olap2_listdims_cc as select
  ld.owner owner,
  ld.dimension_name dimension_name,
  ld.plural_name plural_name,
  ld.display_name display_name,
  ld.shortdescription short_description,
  ld.description description,
  ld.descriptor_value descriptor_value,
  ld.table_owner table_owner,
  ld.table_name table_name,
  ld.column_name column_name,
  ld.column_position column_position,
  ld.data_type data_type,
  ld.data_length data_length,
  ld.data_precision data_precision,
  ld.level_name level_name
 from olapsys.cwm2$mrall_listdims ld
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_listdims as select
  ld.owner owner,
  ld.dimension_name dimension_name,
  ld.plural_name plural_name,
  ld.display_name display_name,
  ld.shortdescription short_description,
  ld.description description,
  ld.descriptor_value descriptor_value,
  ld.table_owner table_owner,
  ld.table_name table_name,
  ld.column_name column_name,
  ld.column_position column_position,
  ld.data_type data_type,
  ld.data_length data_length,
  ld.data_precision data_precision,
  ld.level_name level_name
 from olapsys.cwm2$mrall_listdims ld,
      olapsys.olap_session_objects oso
 where oso.version_id = ld.version_id and
       oso.id = ld.id
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_dim_hiers as select
  dh.owner owner,
  dh.dimension_name dimension_name,
  dh.hierarchy_name hierarchy_name,
  dh.display_name display_name,
  dh.shortdescription short_description,
  dh.description description,
  dh.solved_code solved_code,
  dh.is_default is_default
 from olapsys.cwm2$mrall_dim_hiers dh,
      olapsys.olap_session_objects oso
 where oso.version_id = dh.version_id and
       oso.id = dh.id
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_hierdims_cc as select
  hd.owner owner,
  hd.dimension_name dimension_name,
  hd.plural_name plural_name,
  hd.display_name display_name,
  hd.shortdescription short_description,
  hd.description description,
  hd.default_display_hierarchy default_display_hierarchy,
  hd.descriptor_value descriptor_value
 from olapsys.cwm2$mrall_hierdims hd
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_hierdims as select
  hd.owner owner,
  hd.dimension_name dimension_name,
  hd.plural_name plural_name,
  hd.display_name display_name,
  hd.shortdescription short_description,
  hd.description description,
  hd.default_display_hierarchy default_display_hierarchy,
  hd.descriptor_value descriptor_value
 from olapsys.cwm2$mrall_hierdims hd,
      olapsys.olap_session_objects oso
 where oso.version_id = hd.version_id and
       oso.id = hd.id
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_dim_hier_level_uses as select
  dhlu.owner owner,
  dhlu.dimension_name dimension_name,
  dhlu.hierarchy_name hierarchy_name,
  dhlu.parent_level_name parent_level_name,
  dhlu.child_level_name child_level_name,
  dhlu.position position
 from olapsys.cwm2$mrall_dim_hier_level_uses dhlu,
      olapsys.olap_session_objects oso
 where oso.version_id = dhlu.version_id and
       oso.id = dhlu.id
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_join_key_col_uses as select
  jkcu.owner owner,
  jkcu.dimension_name dimension_name,
  jkcu.hierarchy_name hierarchy_name,
  jkcu.child_level_name child_level_name,
  jkcu.table_owner table_owner,
  jkcu.table_name table_name,
  jkcu.column_name column_name,
  jkcu.position position,
  jkcu.join_key_type join_key_type
 from olapsys.cwm2$mrall_join_key_col_uses jkcu,
      olapsys.olap_session_objects oso
 where oso.version_id = jkcu.version_id and
       oso.id = jkcu.id
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_dim_attributes as select
  da.owner owner,
  da.dimension_name dimension_name,
  da.attribute_name attribute_name,
  da.display_name display_name,
  da.shortdescription short_description,
  da.description description,
  da.desc_id desc_id
 from olapsys.cwm2$mrall_dim_attributes da,
      olapsys.olap_session_objects oso
 where oso.version_id = da.version_id and
       oso.id = da.id
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_entity_desc_uses as select
  edu.descriptor_id descriptor_id,
  edu.entity_owner entity_owner,
  edu.entity_name entity_name,
  edu.child_entity_name child_entity_name,
  edu.secondary_child_entity_name secondary_child_entity_name
 from olapsys.cwm2$mrall_entity_desc_uses edu,
      olapsys.olap_session_objects oso
 where oso.version_id = edu.version_id and
       oso.id = edu.id
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_descriptors as select
  d.descriptor_id descriptor_id,
  d.descriptor_value descriptor_value,
  d.descriptor_type descriptor_type,
  d.description description
 from olapsys.cwm2$mrall_descriptors d
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_dim_level_attr_maps as select
  dlam.owner owner,
  dlam.dimension_name dimension_name,
  dlam.hierarchy_name hierarchy_name,
  dlam.attribute_name attribute_name,  
  dlam.lvl_attribute_name lvl_attribute_name,
  dlam.level_name level_name,
  dlam.table_owner table_owner,
  dlam.table_name table_name,
  dlam.column_name column_name,
  dlam.dtype dtype,
  dlam.data_length data_length,
  dlam.data_precision data_precision,
  dlam.olap_api_data_type olap_api_data_type
 from olapsys.cwm2$mrall_dim_level_attr_maps dlam,
      olapsys.olap_session_objects oso
 where oso.version_id = dlam.version_id and
       oso.id = dlam.id
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_cube_measures as select
  cm.owner owner,
  cm.cube_name cube_name,
  cm.measure_name measure_name,
  cm.display_name display_name,
  cm.shortdescription short_description,
  cm.description description,
  cm.cube_description cube_description
 from olapsys.cwm2$mrall_cube_measures cm,
      olapsys.olap_session_objects oso
 where oso.version_id = cm.version_id and
       oso.id = cm.id
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_facttblkeymaps as select
  ftkm.owner owner,
  ftkm.cube_name cube_name,
  ftkm.dim_hier_combo_id dim_hier_combo_id,
  ftkm.dimension_owner dimension_owner,
  ftkm.dimension_name dimension_name,
  ftkm.hierarchy_name hierarchy_name,
  ftkm.level_name level_name,
  ftkm.fact_table_owner fact_table_owner,
  ftkm.fact_table_name fact_table_name,
  ftkm.column_name column_name,
  ftkm.column_data_type column_data_type,
  ftkm.column_data_length column_data_length,
  ftkm.column_data_precision column_data_precision,
  ftkm.gid_column_name gid_column_name,
  ftkm.gid_column_data_type gid_column_data_type,
  ftkm.gid_column_data_length gid_column_data_length,
  ftkm.gid_column_data_precision gid_column_data_precision,
  ftkm.dimension_keymap_type dimension_keymap_type,
  ftkm.mv_summarycode mv_summarycode,
  ftkm.column_position column_position
 from olapsys.cwm2$mrall_facttblkeymaps ftkm,
      olapsys.olap_session_objects oso
 where oso.version_id = ftkm.version_id and
       oso.id = ftkm.id
/


CREATE OR REPLACE VIEW olapsys.mrv$olap1_facttblkeymaps as select
  ftkm.owner owner,
  ftkm.cube_name cube_name,
  ftkm.dimension_owner dimension_owner,
  ftkm.dimension_name dimension_name,
  ftkm.level_name level_name,
  ftkm.fact_table_owner fact_table_owner,
  ftkm.fact_table_name fact_table_name,
  ftkm.column_name column_name,
  ftkm.column_position column_position,
  ftkm.mv_summary_code mv_summary_code,
  ftkm.data_type data_type,
  ftkm.data_length data_length,
  ftkm.data_precision data_precision
 from olapsys.cwm2$mrfacttblkeymaps ftkm,
      olapsys.olap_session_objects oso
 where oso.version_id = ftkm.version_id and
       oso.id = ftkm.id
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_facttblfctmaps as select
  ftfm.owner owner,
  ftfm.cube_name cube_name,
  ftfm.measure_name measure_name,
  ftfm.dim_hier_combo_id dim_hier_combo_id,
  ftfm.fact_table_owner fact_table_owner,
  ftfm.fact_table_name fact_table_name,
  ftfm.column_name column_name,
  ftfm.data_type data_type,
  ftfm.data_length data_length,
  ftfm.data_precision data_precision,
  ftfm.olap_api_data_type olap_api_data_type
 from olapsys.cwm2$mrall_facttblfctmaps ftfm,
      olapsys.olap_session_objects oso
 where oso.version_id = ftfm.version_id and
       oso.id = ftfm.id
/


CREATE OR REPLACE VIEW olapsys.mrv$olap1_facttblfctmaps as select
  ftfm.owner owner,
  ftfm.cube_name cube_name,
  ftfm.measure_name measure_name,
  ftfm.fact_table_owner fact_table_owner,
  ftfm.fact_table_name fact_table_name,
  ftfm.column_name column_name,
  ftfm.data_type data_type,
  ftfm.data_length data_length,
  ftfm.data_precision data_precision,
  ftfm.olap_api_data_type olap_api_data_type
 from olapsys.cwm2$mrfacttblfctmaps ftfm,
      olapsys.olap_session_objects oso
 where oso.version_id = ftfm.version_id and
       oso.id = ftfm.id
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_hierdim_keycol_map as select
  hkm.owner owner,
  hkm.dimension_name dimension_name,
  hkm.hierarchy_name hierarchy_name,
  hkm.level_name level_name,
  hkm.display_name display_name,
  hkm.shortdescription short_description,
  hkm.description description,
  hkm.hierarchy_position hierarchy_position,
  hkm.table_owner table_owner,
  hkm.table_name table_name,
  hkm.column_name column_name,
  hkm.column_position column_position,
  hkm.data_type data_type,
  hkm.data_length data_length,
  hkm.data_precision data_precision
 from olapsys.cwm2$mrall_hierdim_keycol_map hkm,
      olapsys.olap_session_objects oso
 where oso.version_id = hkm.version_id and
       oso.id = hkm.id
/


CREATE OR REPLACE VIEW olapsys.mrv$olap2_hier_custom_sort as select
  hcs.owner owner,
  hcs.dimension_name dimension_name,
  hcs.hierarchy_name hierarchy_name,
  hcs.table_owner table_owner,
  hcs.table_name table_name,
  hcs.column_name column_name,
  hcs.position position,
  hcs.sort_pos sort_pos,
  hcs.sort_order sort_order,
  hcs.null_order null_order,
  hcs.data_type data_type,
  hcs.data_length data_length,
  hcs.data_precision data_precision
 from olapsys.cwm2$mrall_hier_custom_sort hcs,
      olapsys.olap_session_objects oso
 where oso.version_id = hcs.version_id and
       oso.id = hcs.id
/

CREATE OR REPLACE VIEW olapsys.mrv$olap2_agg_uses as select
 au.OWNER,
 au.CUBE_NAME,
 au.DIMENSION_OWNER,
 au.DIMENSION_NAME,
 au.HIERARCHY_NAME,
 au.DIM_HIER_COMBO_ID,
 au.AGGREGATION_NAME,
 au.AGGREGATION_ORDER,
 au.TABLE_OWNER,
 au.TABLE_NAME,
 au.COLUMN_NAME
 from olapsys.CWM2$MRALL_OLAP2_AGG_USES au,
      olapsys.olap_session_objects oso
 where oso.version_id = au.version_id and
       oso.id = au.id
/

create or replace view olapsys.mrv$mrall_cwm1_aggop as select
 c1aop.owner,
 c1aop.cube_name,
 c1aop.measure_name,
 c1aop.dimension_owner,
 c1aop.dimension_name,
 c1aop.func_name,
 c1aop.table_owner,
 c1aop.table_name,
 c1aop.column_name
 from olapsys.cwm2$mrall_cwm1_aggop c1aop,
      olapsys.olap_session_objects oso
 where oso.version_id = c1aop.version_id and
       oso.id = c1aop.id
/

create or replace view olapsys.mrv$mrall_cwm1_aggord as select
 c1aord.owner,
 c1aord.cube_name,
 c1aord.dimension_owner,
 c1aord.dimension_name,
 c1aord.position
 from olapsys.cwm2$mrall_cwm1_aggord c1aord,
      olapsys.olap_session_objects oso
 where oso.version_id = c1aord.version_id and
       oso.id = c1aord.id
/

create or replace view olapsys.mrv$mrall_entity_parameters as select
 ep.DESCRIPTOR_ID,
 ep.DESCRIPTOR_NAME,
 ep.ENTITY_OWNER,
 ep.ENTITY_NAME,
 ep.CHILD_ENTITY_NAME,
 ep.SECONDARY_CHILD_ENTITY_NAME,
 ep.PARAMETER_NAME,
 ep.PARAMETER_VALUE
from olapsys.cwm2$mrall_entity_parameters ep,
     olapsys.olap_session_objects oso
 where oso.version_id = ep.version_id and
       oso.id = ep.id
/

create or replace view olapsys.mrv$mrall_entity_ext_parms as select
 eep.DESCRIPTOR_ID,
 eep.DESCRIPTOR_NAME,
 eep.ENTITY_OWNER,
 eep.ENTITY_NAME,
 eep.CHILD_ENTITY_NAME,
 eep.SECONDARY_CHILD_ENTITY_NAME,
 eep.PARAMETER_NAME,
 eep.PARAMETER_VALUE,
 eep.PARAMETER_VALUE2,
 eep.PARAMETER_VALUE3,
 eep.PARAMETER_VALUE4,
 eep.POSITION
from olapsys.cwm2$mrall_entity_ext_parms eep,
     olapsys.olap_session_objects oso
 where oso.version_id = eep.version_id and
       oso.id = eep.id
/