Rem
Rem $Header: cwmlite/admin/cwm2mrav.sql /main/5 2009/08/25 09:54:37 dbardwel Exp $
Rem
Rem cwm2mrav.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      cwm2mrav.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    dbardwel    08/24/09 - Fix bug 8491035 by explicitly listing columns in 4 MRV views
Rem    dbardwel    11/09/05 - Fix 10.2 active catalog bug 4722589 
Rem    dbardwel    05/20/04 - For BI Beans, always run with norefresh for MRV views
Rem    dbardwel    04/12/04 - Make union views union not union all per review
Rem    dbardwel    03/26/04 - Work for 10.1.0.3 - three views now
Rem                           get 10.1.0.3 information from active catalog
Rem    dbardwel    06/25/03 - dbardwel_txn107256
Rem    dbardwel    05/05/03 - dbardwel_txn107026
Rem    mstasiew    04/15/03 - 
Rem    mstasiew    04/14/03 - mstasiew_txn106951
Rem    mstasiew    04/14/03 - 
Rem

create or replace view olapsys.MRAC_OLAP2_AWS_V as
 select * from olapsys.MRAC_OLAP2_AWS_T
/
create or replace view olapsys.MRAC_OLAP2_AW_MAP_DIM_USE_V as
 select * from olapsys.MRAC_OLAP2_AW_MAP_DIM_USE_T
/
create or replace view olapsys.MRAC_OLAP2_AW_PHYS_OBJ_V as
 select * from olapsys.MRAC_OLAP2_AW_PHYS_OBJ_T
/
create or replace view olapsys.MRAC_OLAP2_AW_PHYS_OBJ_PROP_V as
 select * from olapsys.MRAC_OLAP2_AW_PHYS_OBJ_PROP_T
/
create or replace view olapsys.MRAC_OLAP2_AW_MAP_MEAS_USE_V as
 select * from olapsys.MRAC_OLAP2_AW_MAP_MEAS_USE_T
/
create or replace view olapsys.MRAC_OLAP2_AW_MAP_ATTR_USE_V as
 select * from olapsys.MRAC_OLAP2_AW_MAP_ATTR_USE_T
/

create or replace view olapsys.MRAC_OLAP2_AW_DIMENSIONS_V as
  select AW_OWNER,
         AW_NAME,
         AW_LOGICAL_NAME,
         AW_PHYSICAL_OBJECT,
         SOURCE_OWNER,
         SOURCE_NAME  
    from olapsys.MRAC_OLAP2_AW_DIMENSIONS_T
 union
SELECT AW.AWOWNER as AW_OWNER,
       AW.AWNAME as AW_NAME,
       AW.AWOBJECT as AW_LOGICAL_NAME,
       AW.COL3 as AW_PHYSICAL_OBJECT,
       AW.COL1 as SOURCE_OWNER,
       AW.COL2 as SOURCE_NAME
FROM
TABLE(CAST (OLAP_TABLE('SYS.AWMD duration query', 'olapsys.ALL_OLAP2_AW_METADATA_T',
                       'ACTIVE_CATALOG ''ALL_DIMENSIONS'' ''MRAC_QUERY'' ''NO''',
                       'MEASURE AWOWNER FROM SYS.AWMD!DIM_OWNER
                        MEASURE AWNAME FROM SYS.AWMD!DIM_AWNAME
                        MEASURE AWOBJECT FROM SYS.AWMD!DIM_DIMENSION_NAME
                        MEASURE COL1 FROM SYS.AWMD!DIM_SOURCE_OWNER
                        MEASURE COL2 FROM SYS.AWMD!DIM_SOURCE_NAME
                        MEASURE COL3 from SYS.AWMD!DIM_AW_PHYSICAL_OBJECT
                        DIMENSION AWMDKEY FROM SYS.AWMD!AWMDKEY_DIM'
                        )
                        AS olapsys.ALL_OLAP2_AW_METADATA_T)) AW
/


create or replace view olapsys.MRAC_OLAP2_AW_ATTRIBUTES_V as
  select AW_OWNER,
         AW_NAME,
         AW_DIMENSION_NAME,
         AW_LOGICAL_NAME,
         AW_PHYSICAL_OBJECT,
         DISPLAY_NAME,
         DESCRIPTION,
         ATTRIBUTE_TYPE,
         SOURCE_OWNER,
         SOURCE_DIMENSION_NAME,
         SOURCE_NAME 
      from olapsys.MRAC_OLAP2_AW_ATTRIBUTES_T
union
SELECT AW.AWOWNER as AW_OWNER,
       AW.AWNAME as AW_NAME,
       AW.COL4 as AW_DIMENSION_NAME,
       AW.AWOBJECT as AW_LOGICAL_NAME,
       AW.COL3 as AW_PHYSICAL_OBJECT,
       AW.COL7 as DISPLAY_NAME,
       AW.COL8 as DESCRIPTION,
       AW.COL9 as ATTRIBUTE_TYPE,
       AW.COL1 as SOURCE_OWNER,
       AW.COL6 as SOURCE_DIMENSION_NAME,
       AW.COL2 as SOURCE_NAME
FROM
TABLE(CAST (OLAP_TABLE('SYS.AWMD duration query', 'olapsys.ALL_OLAP2_AW_METADATA_T',
                       'ACTIVE_CATALOG ''ALL_ATTRIBUTES'' ''MRAC_QUERY'' ''NO''',
                       'MEASURE AWOWNER FROM SYS.AWMD!ATTR_OWNER
                        MEASURE AWNAME FROM SYS.AWMD!ATTR_AWNAME
                        MEASURE AWOBJECT FROM SYS.AWMD!ATTR_ATTRIBUTE_NAME
                        MEASURE COL1 FROM SYS.AWMD!ATTR_SOURCE_OWNER
                        MEASURE COL2 FROM SYS.AWMD!ATTR_SOURCE_NAME
                        MEASURE COL3 from SYS.AWMD!ATTR_AW_PHYSICAL_OBJECT
                        MEASURE COL6 FROM SYS.AWMD!ATTR_DIMENSION_SOURCE_NAME
                        MEASURE COL4 FROM SYS.AWMD!ATTR_DIMENSION_NAME
                        MEASURE COL7 FROM SYS.AWMD!ATTR_DISPLAY_NAME
                        MEASURE COL8 FROM SYS.AWMD!ATTR_DESCRIPTION
                        MEASURE COL9 FROM SYS.AWMD!ATTR_CF_TYPE
                        DIMENSION AWMDKEY FROM SYS.AWMD!AWMDKEY_ATTR'
                        )
                        AS olapsys.ALL_OLAP2_AW_METADATA_T)) AW
/

create or replace view olapsys.MRAC_OLAP2_AW_CUBES_V as
  select AW_OWNER,
         AW_NAME,
         AW_LOGICAL_NAME,
         AW_PHYSICAL_OBJECT,
         SOURCE_OWNER,
         SOURCE_NAME 
  from olapsys.MRAC_OLAP2_AW_CUBES_T
union
SELECT AW.AWOWNER as AW_OWNER,
       AW.AWNAME as AW_NAME,
       AW.AWOBJECT as AW_LOGICAL_NAME,
       AW.COL3 as AW_PHYSICAL_OBJECT,
       AW.COL1 as SOURCE_OWNER,
       AW.COL2 as SOURCE_NAME
FROM
TABLE(CAST (OLAP_TABLE('SYS.AWMD duration query', 'olapsys.ALL_OLAP2_AW_METADATA_T',
                       'ACTIVE_CATALOG ''ALL_CUBES'' ''MRAC_QUERY'' ''NO''',
                       'MEASURE AWOWNER FROM SYS.AWMD!CUBE_OWNER
                        MEASURE AWNAME FROM SYS.AWMD!CUBE_AWNAME
                        MEASURE AWOBJECT FROM SYS.AWMD!CUBE_CUBE_NAME
                        MEASURE COL1 FROM SYS.AWMD!CUBE_SOURCE_OWNER
                        MEASURE COL2 FROM SYS.AWMD!CUBE_SOURCE_NAME
                        MEASURE COL3 FROM SYS.AWMD!CUBE_AW_PHYSICAL_OBJECT
                        DIMENSION AWMDKEY FROM SYS.AWMD!AWMDKEY_CUBE'
                        )
                        AS olapsys.ALL_OLAP2_AW_METADATA_T)) AW
/

create or replace view olapsys.MRAC_OLAP2_AW_CUBE_DIM_USES_V as
 select * from olapsys.MRAC_OLAP2_AW_CUBE_DIM_USES_T
/
create or replace view olapsys.MRAC_OLAP2_AW_DIM_LEVELS_V as 
 select * from olapsys.MRAC_OLAP2_AW_DIM_LEVELS_T
/

create or replace view olapsys.MRAC_OLAP2_AW_CUBE_MEASURES_V as
  select AW_OWNER,
         AW_NAME,
         AW_CUBE_NAME,
         AW_MEASURE_NAME,
         AW_PHYSICAL_OBJECT,
         MEASURE_SOURCE_NAME,
         DISPLAY_NAME,
         DESCRIPTION,
         IS_AGGREGATEABLE 
  from olapsys.MRAC_OLAP2_AW_CUBE_MEASURES_T
union
SELECT AW.AWOWNER as AW_OWNER,
       AW.AWNAME as AW_NAME,
       AW.AWOBJECT as AW_CUBE_NAME,
       AW.COL1 as AW_MEASURE_NAME,
       AW.COL6 as AW_PHYSICAL_OBJECT,
       AW.COL2 as MEASURE_SOURCE_NAME,
       AW.COL3 as DISPLAY_NAME,
       AW.COL4 as DESCRIPTION,
       AW.COL7 as IS_AGGREGATEABLE
FROM
TABLE(CAST (OLAP_TABLE('SYS.AWMD duration query', 'olapsys.ALL_OLAP2_AW_METADATA_T',
                       'ACTIVE_CATALOG ''ALL_CUBE_MEASURES'' ''MRAC_QUERY'' ''NO''',
                       'MEASURE AWOWNER FROM SYS.AWMD!CM_OWNER
                        MEASURE AWNAME FROM SYS.AWMD!CM_AWNAME
                        MEASURE AWOBJECT FROM SYS.AWMD!CM_CUBE_NAME
                        MEASURE COL1 FROM SYS.AWMD!CM_MEASURE_NAME
                        MEASURE COL2 FROM SYS.AWMD!CM_SOURCE_NAME
                        MEASURE COL3 FROM SYS.AWMD!CM_DISPLAY_NAME
                        MEASURE COL4 FROM SYS.AWMD!CM_DESCRIPTION
                        MEASURE COL6 FROM SYS.AWMD!CM_AW_PHYSICAL_OBJECT
                        MEASURE COL7 FROM SYS.AWMD!CM_ISAGGREGATEABLE
                        DIMENSION AWMDKEY FROM SYS.AWMD!AWMDKEY_CM'
                        )
                        AS olapsys.ALL_OLAP2_AW_METADATA_T)) AW
/

create or replace view olapsys.MRAC_OLAP2_AW_CUBE_AGG_SPECS_V as
 select * from olapsys.MRAC_OLAP2_AW_CUBE_AGG_SPECS_T
/
create or replace view olapsys.MRAC_OLAP2_AW_CUBE_AGG_MEAS_V as
 select * from olapsys.MRAC_OLAP2_AW_CUBE_AGG_MEAS_T
/
create or replace view olapsys.MRAC_OLAP2_AW_CUBE_AGG_LVL_V as
 select * from olapsys.MRAC_OLAP2_AW_CUBE_AGG_LVL_T
/
create or replace view olapsys.MRAC_OLAP2_AW_CUBE_AGG_OP_V as
 select * from olapsys.MRAC_OLAP2_AW_CUBE_AGG_OP_T
/
create or replace view olapsys.MRAC_OLAP2_AW_HIER_LVL_ORD_V as 
 select * from olapsys.MRAC_OLAP2_AW_HIER_LVL_ORD_T
/