VIEW: OLAPSYS.CWM2$MRTMPCWM1AGGORD

Source

View Text - Preformatted

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

View Text - HTML Formatted

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