VIEW: SYS.DBA_CUBE_CLASSIFICATIONS

Source

View Text - Preformatted

SELECT
  u.name OWNER,
  CASE d.owning_object_type
  WHEN 4 -- ASSIGNMENT
  THEN (SELECT o.name || '.' || m.model_name || '.' || a.member_name
        FROM olap_model_assignments$ a, olap_models$ m, obj$ o
        WHERE d.owning_object_id = a.assignment_id
              AND m.model_id = a.model_id
              AND m.owning_obj_type = 11
              AND m.owning_obj_id = o.obj#
        )
  WHEN 3 -- model
  THEN (SELECT o.name || '.' || m.model_name
        FROM olap_models$ m, obj$ o
        WHERE d.owning_object_id = m.model_id
              AND m.owning_obj_type = 11
              AND m.owning_obj_id = o.obj#
        )
  WHEN 14 -- hier_level
  THEN (SELECT o.name || '.' || h.hierarchy_name || '.' || dl.level_name
        FROM olap_hier_levels$ hl, olap_dim_levels$ dl,
             olap_hierarchies$ h, obj$ o
        WHERE d.owning_object_id = hl.hierarchy_level_id
              AND hl.dim_level_id = dl.level_id
              AND hl.hierarchy_id = h.hierarchy_id
              AND h.dim_obj# = o.obj#
        )
  WHEN 13 -- hierarchy
  THEN (SELECT o.name || '.' || h.hierarchy_name
        FROM olap_hierarchies$ h, obj$ o
        WHERE d.owning_object_id = h.hierarchy_id
              AND h.dim_obj# = o.obj#
       )
  WHEN 12 -- dim_level
  THEN (SELECT o.name || '.' || dl.level_name
        FROM olap_dim_levels$ dl, obj$ o
        WHERE d.owning_object_id = dl.level_id
              AND dl.dim_obj# = o.obj#
       )
  WHEN 15 -- attribute
  THEN (SELECT o.name || '.' || a.attribute_name
        FROM olap_attributes$ a, obj$ o
        WHERE d.owning_object_id = a.attribute_id
              AND a.dim_obj# = o.obj#
       )
  WHEN 6 -- calc_member
  THEN (SELECT o.name || '.' || c.member_name
        FROM OLAP_CALCULATED_MEMBERS$ c, obj$ o
        WHERE d.owning_object_id = c.member_id
              AND c.dim_obj# = o.obj#
       )
  WHEN 11 -- dimension
  THEN (SELECT o.name
        FROM obj$ o
        WHERE d.owning_object_id = o.obj#
       )
  WHEN 2 -- measure
  THEN (SELECT o.name || '.' || m.measure_name
        FROM olap_measures$ m, olap_cubes$ c, obj$ o
        WHERE d.owning_object_id = m.measure_id
              AND m.cube_obj# = c.obj#
              AND c.obj# = o.obj#
        )
  WHEN 1 -- cube
  THEN (SELECT o.name
        FROM obj$ o
        WHERE d.owning_object_id = o.obj#
       )
  WHEN 10 -- measure_folder
  THEN (SELECT o.name
        FROM obj$ o
        WHERE d.owning_object_id = o.obj#
       )
  WHEN 8 -- interaction
  THEN (SELECT o.name
        FROM obj$ o
        WHERE d.owning_object_id = o.obj#
       )
  ELSE null
  END AS OBJECT_NAME,
  decode(d.owning_object_type, '4', 'ASSIGNMENT',
                               '3', 'MODEL',
                               '14', 'HIERARCHY LEVEL',
                               '13', 'HIERARCHY',
                               '12', 'DIMENSION LEVEL',
                               '15', 'ATTRIBUTE',
                               '6', 'CALCULATION MEMBER',
                               '11', 'DIMENSION',
                               '2', 'MEASURE',
                               '1', 'CUBE',
                               '10', 'MEASURE FOLDER',
                               '8', 'BUILD PROCESS') OBJECT_TYPE,
  d.language LANGUAGE,
  d.description_value CLASSIFICATION,
  d.description_order ORDER_NUM
FROM
  olap_descriptions$ d,
  obj$ o,
  user$ u
WHERE
  d.description_class = 1
  AND d.obj# = o.obj#
  AND o.owner#=u.user#

View Text - HTML Formatted

SELECT U.NAME OWNER
, CASE D.OWNING_OBJECT_TYPE WHEN 4 -- ASSIGNMENT THEN (SELECT O.NAME || '.' || M.MODEL_NAME || '.' || A.MEMBER_NAME
FROM OLAP_MODEL_ASSIGNMENTS$ A
, OLAP_MODELS$ M
, OBJ$ O
WHERE D.OWNING_OBJECT_ID = A.ASSIGNMENT_ID
AND M.MODEL_ID = A.MODEL_ID
AND M.OWNING_OBJ_TYPE = 11
AND M.OWNING_OBJ_ID = O.OBJ# ) WHEN 3 -- MODEL THEN (SELECT O.NAME || '.' || M.MODEL_NAME
FROM OLAP_MODELS$ M
, OBJ$ O
WHERE D.OWNING_OBJECT_ID = M.MODEL_ID
AND M.OWNING_OBJ_TYPE = 11
AND M.OWNING_OBJ_ID = O.OBJ# ) WHEN 14 -- HIER_LEVEL THEN (SELECT O.NAME || '.' || H.HIERARCHY_NAME || '.' || DL.LEVEL_NAME
FROM OLAP_HIER_LEVELS$ HL
, OLAP_DIM_LEVELS$ DL
, OLAP_HIERARCHIES$ H
, OBJ$ O
WHERE D.OWNING_OBJECT_ID = HL.HIERARCHY_LEVEL_ID
AND HL.DIM_LEVEL_ID = DL.LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = O.OBJ# ) WHEN 13 -- HIERARCHY THEN (SELECT O.NAME || '.' || H.HIERARCHY_NAME
FROM OLAP_HIERARCHIES$ H
, OBJ$ O
WHERE D.OWNING_OBJECT_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = O.OBJ# ) WHEN 12 -- DIM_LEVEL THEN (SELECT O.NAME || '.' || DL.LEVEL_NAME
FROM OLAP_DIM_LEVELS$ DL
, OBJ$ O
WHERE D.OWNING_OBJECT_ID = DL.LEVEL_ID
AND DL.DIM_OBJ# = O.OBJ# ) WHEN 15 -- ATTRIBUTE THEN (SELECT O.NAME || '.' || A.ATTRIBUTE_NAME
FROM OLAP_ATTRIBUTES$ A
, OBJ$ O
WHERE D.OWNING_OBJECT_ID = A.ATTRIBUTE_ID
AND A.DIM_OBJ# = O.OBJ# ) WHEN 6 -- CALC_MEMBER THEN (SELECT O.NAME || '.' || C.MEMBER_NAME
FROM OLAP_CALCULATED_MEMBERS$ C
, OBJ$ O
WHERE D.OWNING_OBJECT_ID = C.MEMBER_ID
AND C.DIM_OBJ# = O.OBJ# ) WHEN 11 -- DIMENSION THEN (SELECT O.NAME
FROM OBJ$ O
WHERE D.OWNING_OBJECT_ID = O.OBJ# ) WHEN 2 -- MEASURE THEN (SELECT O.NAME || '.' || M.MEASURE_NAME
FROM OLAP_MEASURES$ M
, OLAP_CUBES$ C
, OBJ$ O
WHERE D.OWNING_OBJECT_ID = M.MEASURE_ID
AND M.CUBE_OBJ# = C.OBJ#
AND C.OBJ# = O.OBJ# ) WHEN 1 -- CUBE THEN (SELECT O.NAME
FROM OBJ$ O
WHERE D.OWNING_OBJECT_ID = O.OBJ# ) WHEN 10 -- MEASURE_FOLDER THEN (SELECT O.NAME
FROM OBJ$ O
WHERE D.OWNING_OBJECT_ID = O.OBJ# ) WHEN 8 -- INTERACTION THEN (SELECT O.NAME
FROM OBJ$ O
WHERE D.OWNING_OBJECT_ID = O.OBJ# ) ELSE NULL END AS OBJECT_NAME
, DECODE(D.OWNING_OBJECT_TYPE
, '4'
, 'ASSIGNMENT'
, '3'
, 'MODEL'
, '14'
, 'HIERARCHY LEVEL'
, '13'
, 'HIERARCHY'
, '12'
, 'DIMENSION LEVEL'
, '15'
, 'ATTRIBUTE'
, '6'
, 'CALCULATION MEMBER'
, '11'
, 'DIMENSION'
, '2'
, 'MEASURE'
, '1'
, 'CUBE'
, '10'
, 'MEASURE FOLDER'
, '8'
, 'BUILD PROCESS') OBJECT_TYPE
, D.LANGUAGE LANGUAGE
, D.DESCRIPTION_VALUE CLASSIFICATION
, D.DESCRIPTION_ORDER ORDER_NUM FROM OLAP_DESCRIPTIONS$ D
, OBJ$ O
, USER$ U WHERE D.DESCRIPTION_CLASS = 1
AND D.OBJ# = O.OBJ#
AND O.OWNER#=U.USER#