VIEW: OLAPSYS.ODM$OLAP2UDIM_HIER_LEVEL_USES

Source

View Text - Preformatted

SELECT distinct
  udl.owner
, udl.dimension_name
, nvl(h.hierarchy_name ,'NONE') hierarchy_name
, 'UL' solvedcode  -- ADD SOLVEDCODE
-- , pl.levelname parent_level_name
, udl.child_level_name
, h.position
FROM
  (SELECT
    u.username owner
  , d.obj# dimobj#
  , d.name dimension_name
  , dl.levelid#
  , dl.levelname child_level_name
   FROM
    dba_users     u
  , sys.obj$      d
  , sys.dimlevel$ dl
  , olapsys.cwm$level lev  -- used to insure that it is a cwm1 dimension
  WHERE u.user_id = d.owner#
  AND d.type# = 43 /* DIMENSION */
  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 d.obj# = dl.dimobj#
  AND dl.dimobj#   = lev.dimension_irid
  AND dl.levelname = lev.physicalname
  ) udl
--
, (SELECT
    h.dimobj#
  , h.hierid#
  , h.hiername hierarchy_name
  , hl.pos# position
  , hl.levelid#
  FROM
    sys.hier$             h
  , sys.hierlevel$        hl
  , olapsys.cwm$hierarchy ch
  WHERE h.dimobj#  = hl.dimobj#
  AND   h.hierid#  = hl.hierid#
  AND   h.dimobj#  = ch.dimension_irid
  AND   h.hiername = ch.name
  ) h
WHERE
    udl.dimobj#  = h.dimobj#(+)
AND udl.levelid# = h.levelid#(+)
--   dba_users u
-- , sys.obj$  d
-- , sys.hier$ h
-- , olapsys.cwm$hierarchy ch
-- , (SELECT
--    pdl.dimobj#
--  , pdl.levelname
--  , phl.hierid#
--  , phl.pos#
--  FROM
--    sys.dimlevel$  pdl
--  , sys.hierlevel$ phl
--  WHERE pdl.dimobj#  = phl.dimobj#
--  AND   pdl.levelid# = phl.levelid#
--  ) pl
-- , (SELECT
--     cdl.dimobj#
--   , cdl.levelname
--   , chl.hierid#
--   , chl.pos#
--   FROM
--     sys.dimlevel$  cdl
--   , sys.hierlevel$ chl
--   WHERE cdl.dimobj#  = chl.dimobj#
--   AND   cdl.levelid# = chl.levelid#
--   ) cl
-- WHERE u.user_id = d.owner#
-- AND d.type# = 43 /* DIMENSION */
-- 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 d.obj# = h.dimobj#
-- AND d.obj# = ch.dimension_irid
-- AND h.hiername = ch.name
--
-- AND d.obj#        = cl.dimobj#
-- AND h.hierid#     = cl.hierid#
--
-- AND pl.pos#(+)    = cl.pos# + 1
-- AND pl.dimobj#(+) = cl.dimobj#
-- AND pl.hierid#(+) = cl.hierid#
--
union all
select owner, dimension_name, hierarchy_name, solvedcode, -- parent_level_name,  ADD SOLVEDCODE
       child_level_name, position
from olapsys.ODM$olap2_dim_hier_level_uses  -- SEE ABOVE
with read only

View Text - HTML Formatted

SELECT DISTINCT UDL.OWNER
, UDL.DIMENSION_NAME
, NVL(H.HIERARCHY_NAME
, 'NONE') HIERARCHY_NAME
, 'UL' SOLVEDCODE -- ADD SOLVEDCODE --
, PL.LEVELNAME PARENT_LEVEL_NAME
, UDL.CHILD_LEVEL_NAME
, H.POSITION FROM (SELECT U.USERNAME OWNER
, D.OBJ# DIMOBJ#
, D.NAME DIMENSION_NAME
, DL.LEVELID#
, DL.LEVELNAME CHILD_LEVEL_NAME FROM DBA_USERS U
, SYS.OBJ$ D
, SYS.DIMLEVEL$ DL
, OLAPSYS.CWM$LEVEL LEV -- USED TO INSURE THAT IT IS A CWM1 DIMENSION
WHERE U.USER_ID = D.OWNER#
AND D.TYPE# = 43 /* DIMENSION */
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 D.OBJ# = DL.DIMOBJ#
AND DL.DIMOBJ# = LEV.DIMENSION_IRID
AND DL.LEVELNAME = LEV.PHYSICALNAME ) UDL --
, (SELECT H.DIMOBJ#
, H.HIERID#
, H.HIERNAME HIERARCHY_NAME
, HL.POS# POSITION
, HL.LEVELID# FROM SYS.HIER$ H
, SYS.HIERLEVEL$ HL
, OLAPSYS.CWM$HIERARCHY CH
WHERE H.DIMOBJ# = HL.DIMOBJ#
AND H.HIERID# = HL.HIERID#
AND H.DIMOBJ# = CH.DIMENSION_IRID
AND H.HIERNAME = CH.NAME ) H WHERE UDL.DIMOBJ# = H.DIMOBJ#(+) AND UDL.LEVELID# = H.LEVELID#(+) -- DBA_USERS U --
, SYS.OBJ$ D --
, SYS.HIER$ H --
, OLAPSYS.CWM$HIERARCHY CH --
, (SELECT -- PDL.DIMOBJ# --
, PDL.LEVELNAME --
, PHL.HIERID# --
, PHL.POS# -- FROM -- SYS.DIMLEVEL$ PDL --
, SYS.HIERLEVEL$ PHL --
WHERE PDL.DIMOBJ# = PHL.DIMOBJ# --
AND PDL.LEVELID# = PHL.LEVELID# -- ) PL --
, (SELECT -- CDL.DIMOBJ# --
, CDL.LEVELNAME --
, CHL.HIERID# --
, CHL.POS# -- FROM -- SYS.DIMLEVEL$ CDL --
, SYS.HIERLEVEL$ CHL --
WHERE CDL.DIMOBJ# = CHL.DIMOBJ# --
AND CDL.LEVELID# = CHL.LEVELID# -- ) CL --
WHERE U.USER_ID = D.OWNER# --
AND D.TYPE# = 43 /* DIMENSION */ --
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 D.OBJ# = H.DIMOBJ# --
AND D.OBJ# = CH.DIMENSION_IRID --
AND H.HIERNAME = CH.NAME -- --
AND D.OBJ# = CL.DIMOBJ# --
AND H.HIERID# = CL.HIERID# -- --
AND PL.POS#(+) = CL.POS# + 1 --
AND PL.DIMOBJ#(+) = CL.DIMOBJ# --
AND PL.HIERID#(+) = CL.HIERID# -- UNION ALL SELECT OWNER
, DIMENSION_NAME
, HIERARCHY_NAME
, SOLVEDCODE
, -- PARENT_LEVEL_NAME
, ADD SOLVEDCODE CHILD_LEVEL_NAME
, POSITION FROM OLAPSYS.ODM$OLAP2_DIM_HIER_LEVEL_USES -- SEE ABOVE WITH READ ONLY