eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: OLAPSYS.ALL$AW_LOAD_CUBE_DIMS

Source

View Text - Preformatted


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

View Text - HTML Formatted

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