VIEW: OLAPSYS.ALL$OLAP2UDIM_LEVEL_ATTRIBUTES

Source

View Text - Preformatted

select dla1.owner owner,
       dla1.dimension_name dimension_name,
       dla1.attribute_name attribute_name,
       dla1.display_name display_name,
       dla1.shortdesc short_description,
       dla1.longdesc description,
       dla1.determined_by_level_name determined_by_level_name
from
(SELECT
  a.u_name owner
, a.d_name dimension_name
, nvl(la.lat_name, a.c_name) attribute_name
, la.lat_displayname display_name
, la.lat_description shortdesc
, la.lat_description longdesc
, a.l_levelname determined_by_level_name
FROM
 (SELECT /*+ORDERED*/
    u.username u_name
  , d.name d_name
  , l.dimobj# l_dimobj#
  , l.levelname l_levelname
  , c.obj# c_obj#
  , c.name c_name
 FROM
    dba_users u
  , sys.obj$ d
  , sys.dimlevel$ l
  , sys.dimattr$ a
  , sys.col$ c
  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# = l.dimobj#
  AND l.dimobj# = a.dimobj#
  AND l.levelid# = a.levelid#
  AND a.detailobj# = c.obj#
  AND a.col# = c.col#) a
, (SELECT
    lat.name lat_name
    , lat.displayname lat_displayname
    , lat.description lat_description
    , lat.type_irid lat_type_irid
    , lat.physicalname lat_physicalname
    , lev.dimension_irid lev_dimension_irid
    , lev.physicalname lev_physicalname
  FROM cwm$level lev, cwm$levelattribute lat
  WHERE lev.irid = lat.itemcontainer_irid) la
WHERE a.l_dimobj# = la.lev_dimension_irid
AND a.l_levelname = la.lev_physicalname
AND a.c_obj# = la.lat_type_irid
AND a.c_name = la.lat_physicalname) dla1
union all
select owner, dimension_name, attribute_name, display_name, short_description,
       description, determined_by_level_name
from all$olap2_dim_level_attributes
with read only

View Text - HTML Formatted

SELECT DLA1.OWNER OWNER
, DLA1.DIMENSION_NAME DIMENSION_NAME
, DLA1.ATTRIBUTE_NAME ATTRIBUTE_NAME
, DLA1.DISPLAY_NAME DISPLAY_NAME
, DLA1.SHORTDESC SHORT_DESCRIPTION
, DLA1.LONGDESC DESCRIPTION
, DLA1.DETERMINED_BY_LEVEL_NAME DETERMINED_BY_LEVEL_NAME FROM (SELECT A.U_NAME OWNER
, A.D_NAME DIMENSION_NAME
, NVL(LA.LAT_NAME
, A.C_NAME) ATTRIBUTE_NAME
, LA.LAT_DISPLAYNAME DISPLAY_NAME
, LA.LAT_DESCRIPTION SHORTDESC
, LA.LAT_DESCRIPTION LONGDESC
, A.L_LEVELNAME DETERMINED_BY_LEVEL_NAME FROM (SELECT /*+ORDERED*/ U.USERNAME U_NAME
, D.NAME D_NAME
, L.DIMOBJ# L_DIMOBJ#
, L.LEVELNAME L_LEVELNAME
, C.OBJ# C_OBJ#
, C.NAME C_NAME FROM DBA_USERS U
, SYS.OBJ$ D
, SYS.DIMLEVEL$ L
, SYS.DIMATTR$ A
, SYS.COL$ C
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# = L.DIMOBJ#
AND L.DIMOBJ# = A.DIMOBJ#
AND L.LEVELID# = A.LEVELID#
AND A.DETAILOBJ# = C.OBJ#
AND A.COL# = C.COL#) A
, (SELECT LAT.NAME LAT_NAME
, LAT.DISPLAYNAME LAT_DISPLAYNAME
, LAT.DESCRIPTION LAT_DESCRIPTION
, LAT.TYPE_IRID LAT_TYPE_IRID
, LAT.PHYSICALNAME LAT_PHYSICALNAME
, LEV.DIMENSION_IRID LEV_DIMENSION_IRID
, LEV.PHYSICALNAME LEV_PHYSICALNAME
FROM CWM$LEVEL LEV
, CWM$LEVELATTRIBUTE LAT
WHERE LEV.IRID = LAT.ITEMCONTAINER_IRID) LA WHERE A.L_DIMOBJ# = LA.LEV_DIMENSION_IRID AND A.L_LEVELNAME = LA.LEV_PHYSICALNAME AND A.C_OBJ# = LA.LAT_TYPE_IRID AND A.C_NAME = LA.LAT_PHYSICALNAME) DLA1 UNION ALL SELECT OWNER
, DIMENSION_NAME
, ATTRIBUTE_NAME
, DISPLAY_NAME
, SHORT_DESCRIPTION
, DESCRIPTION
, DETERMINED_BY_LEVEL_NAME FROM ALL$OLAP2_DIM_LEVEL_ATTRIBUTES WITH READ ONLY