VIEW: OLAPSYS.DBA$OLAP2UDIM_LEVEL_ATTRIBUTES

Source

View Text - Preformatted

select da.owner owner,
       da.dimension_name dimension_name,
       da.attribute_name attribute_name,
       da.display_name display_name,
       da.short_description short_description,
       da.long_description description,
       da.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 short_description
, la.lat_description long_description
, 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 d.type# = 43 /* DIMENSION */
  AND u.user_id = d.owner#
  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) da
union all
select owner, dimension_name, attribute_name, display_name, short_description,
       description, determined_by_level_name
from dba$olap2_dim_level_attributes
with read only

View Text - HTML Formatted

SELECT DA.OWNER OWNER
, DA.DIMENSION_NAME DIMENSION_NAME
, DA.ATTRIBUTE_NAME ATTRIBUTE_NAME
, DA.DISPLAY_NAME DISPLAY_NAME
, DA.SHORT_DESCRIPTION SHORT_DESCRIPTION
, DA.LONG_DESCRIPTION DESCRIPTION
, DA.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 SHORT_DESCRIPTION
, LA.LAT_DESCRIPTION LONG_DESCRIPTION
, 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 D.TYPE# = 43 /* DIMENSION */
AND U.USER_ID = D.OWNER#
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) DA UNION ALL SELECT OWNER
, DIMENSION_NAME
, ATTRIBUTE_NAME
, DISPLAY_NAME
, SHORT_DESCRIPTION
, DESCRIPTION
, DETERMINED_BY_LEVEL_NAME FROM DBA$OLAP2_DIM_LEVEL_ATTRIBUTES WITH READ ONLY