REM
REM Copyright (c) 2002, 2003, Oracle Corporation.  All rights reserved.  
REM
REM   Name
REM     cwm2awlv.sql
REM
REM   Description
REM     AW Load View definitions
REM
REM   Notes
REM
REM   MODIFIED  (MM/DD/YY)
REM      glyon   06/24/08 - bug 7204558: eliminate references to sys.user$
REM      awesley 08/25/03 - bug 2884295
REM      mstasie 04/24/03 - 
REM      mstasie 03/13/03 - 
REM      dbardwe 02/12/03 - 
REM      mstasie 01/07/03 - 
REM      mstasie 01/06/03 - 
REM      awesley 12/17/02 - 
REM      dbardwe 11/20/02 - dbardwel_txn105264 finalize 9.2.0.2.1
REM      mstasie 10/22/02 - 
REM      mstasie 08/19/02 - mstasiew_txn103605
REM    mstasiew   08/14/02  
REM

create or replace view olapsys.all$aw_load_dim_map as
 select 
  d.owner  OWNER,
  d.name  DIMENSION_NAME,
  l.name  LOAD_NAME,
  d.irid  DIM_IRID,
  l.irid  LOAD_IRID,
  l.version_id VERSION_ID
 from
   olapsys.cwm2$dimension d,
   olapsys.cwm2$awdimload l
 where
   d.irid = l.dim_irid and
   l.version_id = 'CWM2' 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
  u.username  owner,
  o.name  dimension_name,
  l.name  LOAD_NAME,
  dim.irid DIM_IRID,
  l.irid LOAD_IRID,
  l.version_id VERSION_ID
FROM
  sys.dim$                d,
  dba_users               u,
  sys.obj$                o,
  cwm$dimension           dim,
   olapsys.cwm2$awdimload l
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
        dim.irid = l.dim_irid and
        l.version_id = 'CWM'
with read only
/


create or replace view olapsys.all$aw_load_dim as
  select
    ldm.owner owner,
    ldm.dimension_name dimension_name,
    ldm.load_name load_name,
    lt.name load_type
  from olapsys.all$aw_load_dim_map ldm,
       olapsys.cwm2$awdimload l,
       olapsys.cwm2$awdimloadtype lt
  where ldm.dim_irid = l.dim_irid and
        ldm.load_irid = l.irid and
        l.loadtype_irid = lt.irid
  with read only
/

create or replace view olapsys.all$aw_load_dim_filt as
 select
    ldm.owner owner,
    ldm.dimension_name dimension_name,
    ldm.load_name load_name,
    lf.lookup_table_owner table_owner,
    lf.lookup_table_name  table_name,
    lf.filtercondition filter_condition
 from olapsys.all$aw_load_dim_map ldm,
      olapsys.cwm2$awdimloadfilter lf
 where ldm.load_irid = lf.dimload_irid
 with read only
/

create or replace view olapsys.all$aw_load_dim_parm as
 select
    ldm.owner owner,
    ldm.dimension_name dimension_name,
    ldm.load_name load_name,
    dlp.name parm_name,
    dlpv.value parm_value
 from olapsys.all$aw_load_dim_map ldm,
      olapsys.cwm2$awdimloadparm dlp,
      olapsys.cwm2$awdimloadparmvalue dlpv
 where ldm.load_irid = dlpv.dimload_irid
   and dlpv.parmname_irid = dlp.irid
with read only
/


create or replace view olapsys.all$aw_load_cube_map as
  select
   c.owner owner,
   c.name cube_name,
   c.irid cube_irid,
   l.name load_name,
   l.irid load_irid,
   l.version_id version_id
  from olapsys.CwM2$Cube c,
       olapsys.cwm2$awcubeload l
    where (c.invalid = 'N' OR c.invalid = 'O') and
    l.version_id = 'CWM2' and
    (cwm2$security.fact_table_visible(c.irid) = 'Y'
       OR EXISTS (select null from v$enabledprivs
                  where priv_number in (-47)))
    and
      c.irid = l.cube_irid
union all
SELECT
   sch.physicalname owner
   , cub.physicalname cube_name
   , cub.irid cube_irid,
   l.name load_name,
   l.irid load_irid,
   l.version_id version_id
FROM
   dba_users u
  , cwm$model sch
  , cwm$cube  cub
  , olapsys.cwm2$awcubeload l
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 = l.cube_irid and
       l.version_id = 'CWM'
with read only
/


create or replace view olapsys.all$aw_load_cube_filt as
 select
   lcm.owner owner,
   lcm.cube_name cube_name,
   lcm.load_name load_name,
   lf.Fact_Table_Owner table_owner,
   lf.Fact_Table_Name  table_name,
   lf.filtercondition filter_condition
 from olapsys.all$aw_load_cube_map lcm,
      olapsys.cwm2$awcubeloadfilter lf
 where lcm.load_irid = lf.cubeload_irid
with read only
/


create or replace view olapsys.all$aw_load_cube_parm as
 select
   lcm.owner owner,
   lcm.cube_name cube_name,
   lcm.load_name load_name,
   clp.name parm_name,
   clpv.value parm_value
 from olapsys.all$aw_load_cube_map lcm,
      olapsys.cwm2$awcubeloadparm clp,
      olapsys.cwm2$awcubeloadparmvalue clpv
 where lcm.load_irid = clpv.cubeload_irid
   and clpv.parmname_irid = clp.irid
with read only
/


create or replace view olapsys.all$aw_load_cube_meas as
 select
  lcm.owner owner,
  lcm.cube_name cube_name,
  lcm.load_name load_name,
  m.name measure_name,
  clm.name measure_target_name,
  clm.displayname measure_target_display_name,
  clm.description measure_target_description
 from
   olapsys.all$aw_load_cube_map lcm,
   olapsys.cwm2$measure m,
   olapsys.cwm2$awcubeloadmeasure clm
 where
   lcm.cube_irid = m.cube_irid and
   lcm.load_irid = clm.cubeload_irid and
   m.irid = clm.measure_irid and
   lcm.version_id = 'CWM2'
union all
 select
  lcm.owner owner,
  lcm.cube_name cube_name,
  lcm.load_name load_name,
  m.name measure_name,
  clm.name measure_target_name,
  clm.displayname measure_display_name,
  clm.description measure_target_description
 from
   olapsys.all$aw_load_cube_map lcm,
   olapsys.cwm$measure m,
   olapsys.cwm2$awcubeloadmeasure clm
 where
   lcm.cube_irid = m.itemcontainer_irid and
   lcm.load_irid = clm.cubeload_irid and
   m.irid = clm.measure_irid and
   lcm.version_id = 'CWM'
with read only
/


REM
REM This view provides composite information for a
REM cube.
REM

create or replace view all$aw_load_cube_dims as
  /* For 9i2 - Get Composite dimensions first */
  select cub2.owner cube_owner,
         cub2.name cube_name,
         cl.name cubeload_name,
         cs.name compspec_name,
         csmCOM.name composite_name,
         csmCOM.segwidth segwidth,
         csmCOM.compspec_position compspec_position,
         dim2.owner dimension_owner,
         dim2.name dimension_name,
         csmMEM.composite_position composite_position,
         csmCOM.composite_level+1 nested_level,
         csmMEM.membertype nested_type,
         csmMEM.name nested_name
  from cwm2$cube cub2,
       cwm2$awcubeload cl,
       cwm2$awcubecompplan ccp,
       cwm2$awcompositeSpec cs,
       (select level composite_level,
               IRID,
               CompSpec_IRID,
               MemberType,
               Name,
               SegWidth,
               CompSpec_Position,
               Dim_IRID,
               Composite_IRID,
               Composite_Position     
          from cwm2$awcompspecmembership
          start with membertype = 'COMPOSITE'
            and      composite_irid is null
          connect by composite_irid = prior irid
       ) csmCOM, -- composite
       cwm2$awcompspecmembership csmMEM, -- member
       cwm2$dimension dim2
  where cl.cube_irid = cub2.irid and
       cl.version_id = 'CWM2' and
       (cub2.invalid = 'N' or cub2.invalid = 'O') and
       ccp.cubecompspec_irid = cs.irid and
       ccp.cubeload_irid = cl.irid and
       csmCOM.compspec_irid = cs.irid and
       csmMEM.composite_irid = csmCOM.irid and
       csmMEM.dim_irid = dim2.irid(+) and
       (cwm2$security.fact_table_visible(cub2.irid) = 'Y'
          OR EXISTS (select null from v$enabledprivs
                     where priv_number in (-47)))
 union all
   /* For 9i2 - Get Dimensions outside composite next */
  select cub2.owner cube_owner,
         cub2.name cube_name,
         cl.name cubeload_name,
         cs.name compspec_name,
         NULL composite_name,
         csm.segwidth segwidth,
         csm.compspec_position compspec_position,
         dim2.owner dimension_owner,
         dim2.name dimension_name,
         null composite_position,
         null nested_level,
         null nested_type,
         null nested_name
  from cwm2$cube cub2,
       cwm2$awcubeload cl,
       cwm2$awcubecompplan ccp,
       cwm2$awcompositespec cs,
       cwm2$awcompspecmembership csm,
       cwm2$dimension dim2
  where cl.cube_irid = cub2.irid and
        cl.version_id = 'CWM2' and
        (cub2.invalid = 'N' or cub2.invalid = 'O') and
        ccp.cubecompspec_irid = cs.irid and
        ccp.cubeload_irid = cl.irid and
        csm.compspec_irid = cs.irid and
        csm.membertype = 'DIMENSION' and
        csm.composite_irid is null and
        csm.dim_IRID = dim2.irid and
        (cwm2$security.fact_table_visible(cub2.irid) = 'Y'
          OR EXISTS (select null from v$enabledprivs
                     where priv_number in (-47)))
  union all
  /* For 9i1 - Get Dimensions within a composite */
 select sch.physicalname cube_owner,
        cub1.name cube_name,
        cl.name cubeload_name,
        cs.name compspec_name,
        csmCOM.name composite_name,
        csmCOM.segwidth segwidth,
        csmCOM.compspec_position compspec_position,
        u.username dimension_owner,
        dim1.name dimension_name,
        csmMEM.composite_position composite_position,
        csmCOM.composite_level+1 nested_level,
        csmMEM.membertype nested_type,
        csmMEM.name nested_name
 from cwm$cube cub1,
      cwm2$awcubeload cl,
      cwm2$awcubecompplan ccp,
      cwm2$awcompositeSpec cs,
      (select level composite_level,
              IRID,
              CompSpec_IRID,
              MemberType,
              Name,
              SegWidth,
              CompSpec_Position,
              Dim_IRID,
              Composite_IRID,
              Composite_Position     
         from cwm2$awcompspecmembership
         start with membertype = 'COMPOSITE'
           and      composite_irid is null
         connect by composite_irid = prior irid
      ) csmCOM, -- composite
      cwm2$awcompspecmembership csmMEM, -- member
      cwm$dimension dim1,
      sys.dim$ d,
      sys.obj$ o,
      dba_users u,
      cwm$model sch
 where cl.cube_irid = cub1.irid and
       cl.version_id = 'CWM' and
       ccp.cubecompspec_irid = cs.irid and
       ccp.cubeload_irid = cl.irid and
       csmCOM.compspec_irid = cs.irid and
       csmMEM.composite_irid = csmCOM.irid and
       csmMEM.dim_irid = dim1.irid(+) and
       d.obj# = dim1.irid and
       o.obj# = d.obj# and
       u.user_id = o.owner# and
       sch.irid = cub1.datamodel_irid and
       (cwm$util.fact_table_visible(cub1.irid) = 'Y'
          OR EXISTS (select null from v$enabledprivs
                     where priv_number in (-47)))
  union all
   /* For 9i1 - Get Dimensions outside composite next */
  select sch.physicalname cube_owner ,
         cub1.physicalname cube_name,
         cl.name cubeload_name,
         cs.name compspec_name,
         NULL composite_name,
         csm.segwidth segwidth,
         csm.compspec_position compspec_position,
         u.username dimension_owner,
         dim1.name dimension_name,
         null composite_position,
         null nested_level,
         null nested_type,
         null nested_name
  from cwm$cube cub1,
       cwm2$awcubeload cl,
       cwm2$awcubecompplan ccp,
       cwm2$awcompositespec cs,
       cwm2$awcompspecmembership csm,
       cwm$dimension dim1,
       sys.dim$ d,
       sys.obj$ o,
       dba_users u,
       cwm$model sch
  where cl.cube_irid = cub1.irid and
        cl.version_id = 'CWM' and
        ccp.cubecompspec_irid = cs.irid and
        ccp.cubeload_irid = cl.irid and
        csm.compspec_irid = cs.irid and
        csm.membertype = 'DIMENSION' and
        csm.composite_irid is null and
        csm.dim_IRID = dim1.irid and
        d.obj# = dim1.irid and
        o.obj# = d.obj# and
        u.user_id = o.owner# and
        sch.irid = cub1.datamodel_irid and
        (cwm$util.fact_table_visible(cub1.irid) = 'Y'
           OR EXISTS (select null from v$enabledprivs
                      where priv_number in (-47)))
with read only
/



REM
REM This view provides the loads for a cube with its type
REM

create or replace view all$aw_load_cubes as 
  select c.owner cube_owner,
         c.name cube_name,
         cl.name load_name,
         clt.name load_type
  from
    cwm2$awcubeload cl,
    cwm2$awcubeloadtype clt,
    cwm2$cube c
  where
    cl.loadtype_irid = clt.irid and
    cl.cube_irid = c.irid and
    cl.version_id = 'CWM2' 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)))
 union all
  select sch.physicalname cube_owner,
         c.physicalname cube_name,
         cl.name load_name,
         clt.name load_type
  from
    cwm2$awcubeload cl,
    cwm2$awcubeloadtype clt,
    cwm$cube c,
    cwm$model sch
  where
   cl.loadtype_irid = clt.irid and
   cl.cube_irid = c.irid and
   cl.version_id = 'CWM' and
   sch.irid = c.datamodel_irid and
   (cwm$util.fact_table_visible(c.irid) = 'Y'
      OR EXISTS (select null from v$enabledprivs
                 where priv_number in (-47)))
with read only
/



REM
REM This view provides the measures that participate in a 
REM given aggregation for a load.
REM

create or replace view all$aw_load_cube_agg_measures as 
   select c.owner cube_owner,
          c.name cube_name,
          cl.name load_name,
          cagg.name aggregation_name,
          m.name measure_name
   from
     cwm2$cube c,
     cwm2$measure m,
     cwm2$awcubeload cl,
     cwm2$awcubeagg cagg,
     cwm2$awcubeaggmeasure cagm,
     cwm2$awcubeloadaggplan clap
   where
     cl.cube_irid = c.irid and
     cl.version_id = 'CWM2' and
     (c.invalid = 'N' or c.invalid = 'O') and
     cagm.cubeagg_irid = cagg.irid and
     clap.cubeagg_irid = cagg.irid and
     clap.cubeload_irid = cl.irid and
     cagm.measure_irid = m.irid and
     m.cube_irid = c.irid and
     (cwm2$security.fact_table_visible(c.irid) = 'Y'
        OR EXISTS (select null from v$enabledprivs
                   where priv_number in (-47)))
  union all
   select sch.physicalname cube_owner,
          c.physicalname cube_name,
          cl.name load_name,
          cagg.name aggregation_name, 
          m.physicalname measure_name
   from 
      cwm$cube c,
      cwm$measure m,
      cwm$model sch,
      cwm2$awcubeload cl,
      cwm2$awcubeagg cagg,
      cwm2$awcubeaggmeasure cagm,
      cwm2$awcubeloadaggplan clap
   where
      cl.cube_irid = c.irid and
      cl.version_id = 'CWM' and
      cagm.cubeagg_irid = cagg.irid and
      clap.cubeagg_irid = cagg.irid and
      clap.cubeload_irid = cl.irid and
      cagm.measure_irid = m.irid and
      m.itemcontainer_irid = c.irid and
      sch.irid = c.datamodel_irid and 
      (cwm$util.fact_table_visible(c.irid) = 'Y'
        OR EXISTS (select null from v$enabledprivs
                   where priv_number in (-47)))
with read only
/



REM
REM This view provides the details of how to do an aggregation
REM for aggregations in a load
REM

create or replace view all$aw_load_cube_agg_levels as
   select c.owner owner,
          c.name cube_name,
          cl.name load_name,
          cagg.name aggregation_name,
          d.owner dimension_owner, 
          d.name dimension_name,
          l.name level_name
   from cwm2$awcubeload cl,
        cwm2$awcubeagg cagg,
        cwm2$awcubeagglevel caggl,
        cwm2$awcubeloadaggplan clap,
        cwm2$cube c, 
        cwm2$dimension d,
        cwm2$level l
   where cl.cube_irid = c.irid and
         cl.version_id = 'CWM2' and
         (c.invalid = 'N' or c.invalid = 'O') and
         caggl.cubeagg_irid = cagg.irid and
         caggl.dim_irid = d.irid and
         caggl.level_irid = l.irid and
         clap.cubeagg_irid = cagg.irid and
         clap.cubeload_irid = cl.irid  and
         (cwm2$security.fact_table_visible(c.irid) = 'Y'
            OR EXISTS (select null from v$enabledprivs
                       where priv_number in (-47)))
  union all
   select sch.physicalname owner,
          c.physicalname cube_name,
          cl.name load_name,
          cagg.name aggregation_name,
          u.username dimension_owner,
          dim1.name dimension_name,
          l.name level_name
   from cwm$model sch,
        cwm$cube c,
        cwm$dimension dim1,
        dba_users u,
        sys.obj$ o,
        sys.dim$ d,
        cwm2$awcubeload cl,
        cwm2$awcubeagg cagg,
        cwm2$awcubeagglevel caggl,
        cwm2$awcubeloadaggplan clap,
        cwm$level l
   where sch.irid = c.datamodel_irid and
         cl.cube_irid = c.irid and
         cl.version_id = 'CWM' and
         caggl.cubeagg_irid = cagg.irid and
         caggl.dim_irid = dim1.irid and
         caggl.level_irid = l.irid and
         clap.cubeagg_irid = cagg.irid and
         clap.cubeload_irid = cl.irid and
         d.obj# = dim1.irid and
         o.obj# = d.obj# and
         u.user_id = o.owner# and
         (cwm$util.fact_table_visible(c.irid) = 'Y'
            OR EXISTS (select null from v$enabledprivs
                       where priv_number in (-47)))
with read only
/
          

REM
REM This view provides segwidth information for a composite
REM in a load.
REM

create or replace view all$aw_load_cube_segwidth as
   select c.owner owner,
          c.name cube_name,
          cl.name load_name,
          cp.name compspec_name,
          csm.compspec_position vrb_position,
          csm.segwidth segwidth
   from cwm2$awcubeload cl,
        cwm2$awcompositespec cp,
        cwm2$awcompspecmembership csm,
        cwm2$awcubecompplan ccp,
        cwm2$cube c
   where cl.cube_irid = c.irid and
         cl.version_id = 'CWM2' and
         (c.invalid = 'N' or c.invalid = 'O') and
         csm.compspec_irid = cp.irid and
         csm.composite_irid is null and
         ccp.cubecompspec_irid = cp.irid and
         ccp.cubeload_irid = cl.irid  and
         (cwm2$security.fact_table_visible(c.irid) = 'Y'
            OR EXISTS (select null from v$enabledprivs
                       where priv_number in (-47)))
  union all
   select sch.physicalname owner,
          c.physicalname cube_name,
          cl.name load_name,
          cp.name compspec_name,
          csm.compspec_position vrb_position,
          csm.segwidth segwidth
   from cwm2$awcubeload cl,
        cwm2$awcompositespec cp,
        cwm2$awcompspecmembership csm,
        cwm2$awcubecompplan ccp,
        cwm$cube c,
        cwm$model sch
   where cl.cube_irid = c.irid and
         cl.version_id = 'CWM' and
         csm.compspec_irid = cp.irid and
         csm.composite_irid is null and
         ccp.cubecompspec_irid = cp.irid and
         ccp.cubeload_irid = cl.irid and
         sch.irid = c.datamodel_irid and
         (cwm$util.fact_table_visible(c.irid) = 'Y'
            OR EXISTS (select null from v$enabledprivs
                       where priv_number in (-47)))
with read only
/


REM
REM This view provides the list of aggregation plans for a cube
REM

create or replace view all$aw_cube_agg_plans as
   select c.owner owner,
          c.name cube_name,
          cagg.name aggregation_name
   from
     cwm2$cube c,
     cwm2$awcubeagg cagg
   where
     cagg.cube_irid = c.irid and
     cagg.version_id = 'CWM2' 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)))
  union all
   select sch.physicalname owner,
          c.physicalname cube_name,
          cagg.name aggregation_name
   from
      cwm$cube c,
      cwm$model sch,
      cwm2$awcubeagg cagg
   where
     sch.irid = c.datamodel_irid and
     cagg.cube_irid = c.irid and
     cagg.version_id = 'CWM' and
     (cwm$util.fact_table_visible(c.irid) = 'Y'
        OR EXISTS (select null from v$enabledprivs
                   where priv_number in (-47)))
with read only
/


REM
REM This view provides the list of measure in an
REM aggregation plan for a cube
REM

create or replace view all$aw_cube_agg_measures as
   select c.owner cube_owner,
          c.name cube_name,
          cagg.name aggregation_name,
          m.name measure_name
   from
     cwm2$cube c,
     cwm2$awcubeagg cagg,
     cwm2$awcubeaggmeasure caggm,
     cwm2$measure m
   where
     cagg.cube_irid = c.irid and
     cagg.version_id = 'CWM2' and
     (c.invalid = 'N' or c.invalid = 'O') and
     caggm.cubeagg_irid = cagg.irid and
     m.irid = caggm.measure_irid and
     (cwm2$security.fact_table_visible(c.irid) = 'Y'
        OR EXISTS (select null from v$enabledprivs
                   where priv_number in (-47)))
  union all
   select sch.physicalname cube_owner,
          c.physicalname cube_name,
          cagg.name aggregation_name,
          m.physicalname measure_name 
   from
      cwm$cube c,
      cwm$model sch,
      cwm2$awcubeagg cagg,
      cwm2$awcubeaggmeasure caggm,
      cwm$measure m
   where
     sch.irid = c.datamodel_irid and
     cagg.cube_irid = c.irid and
     cagg.version_id = 'CWM' and
     caggm.cubeagg_irid = cagg.irid and
     caggm.measure_irid = m.irid and
     (cwm$util.fact_table_visible(c.irid) = 'Y'
        OR EXISTS (select null from v$enabledprivs
                   where priv_number in (-47)))
with read only
/


REM
REM This view provides a list of levels that partipate in
REM an aggregation.
REM

create or replace view all$aw_cube_agg_levels as
   select c.owner owner,
          c.name cube_name,
          cagg.name aggregation_name,
          d.owner dimension_owner,
          d.name dimension_name,
          l.name level_name
   from
        cwm2$awcubeagg cagg,
        cwm2$awcubeagglevel caggl,
        cwm2$cube c,
        cwm2$dimension d,
        cwm2$level l
   where cagg.cube_irid = c.irid and
         cagg.version_id = 'CWM2' and 
         (c.invalid = 'N' or c.invalid = 'O') and
         caggl.cubeagg_irid = cagg.irid and
         caggl.dim_irid = d.irid and
         caggl.level_irid = l.irid and
         (cwm2$security.fact_table_visible(c.irid) = 'Y'
            OR EXISTS (select null from v$enabledprivs
                       where priv_number in (-47)))
  union all
   select sch.physicalname owner,
          c.physicalname cube_name,
          cagg.name aggregation_name,
          u.username dimension_owner,
          dim1.name dimension_name,
          l.name level_name
   from cwm$model sch,
        cwm$cube c,
        cwm$dimension dim1,
        dba_users u,
        sys.obj$ o,
        sys.dim$ d,
        cwm2$awcubeagg cagg,
        cwm2$awcubeagglevel caggl,
        cwm$level l
   where sch.irid = c.datamodel_irid and
         cagg.cube_irid = c.irid and
         cagg.version_id = 'CWM' and
         caggl.cubeagg_irid = cagg.irid and
         caggl.dim_irid = dim1.irid and
         caggl.level_irid = l.irid and
         d.obj# = dim1.irid and
         o.obj# = d.obj# and
         u.user_id = o.owner# and
         (cwm$util.fact_table_visible(c.irid) = 'Y'
            OR EXISTS (select null from v$enabledprivs
                       where priv_number in (-47)))
with read only
/