VIEW: SYS.DBA_CUBE_DIMNL_MAPPINGS

Source

View Text - Preformatted

SELECT
  u.name OWNER,
  o.name CUBE_NAME,
  owner_map.map_name CUBE_MAP_NAME,
  m.map_name MAP_NAME,
  m.map_id MAP_ID,
  o2.name MAPPED_DIMENSION_NAME,
  CASE m.mapped_dim_type
  WHEN 14 -- hier_level
  THEN (SELECT h.hierarchy_name
        FROM olap_hier_levels$ hl, olap_hierarchies$ h
        WHERE m.mapped_dim_id = hl.hierarchy_level_id
              AND hl.hierarchy_id = h.hierarchy_id
        )
  WHEN 13 -- hierarchy
  THEN (SELECT h.hierarchy_name
        FROM olap_hierarchies$ h
        WHERE m.mapped_dim_id = h.hierarchy_id
       )
  ELSE null END AS MAPPED_HIERARCHY_NAME,
  CASE m.mapped_dim_type
  WHEN 12 -- dim_level
  THEN (SELECT dl.level_name
        FROM olap_dim_levels$ dl
        WHERE m.mapped_dim_id = dl.level_id
        )
  WHEN 14 -- hier_level
  THEN (SELECT dl.level_name
        FROM olap_dim_levels$ dl, olap_hier_levels$ hl, olap_hierarchies$ h
        WHERE m.mapped_dim_id = hl.hierarchy_level_id
              AND hl.hierarchy_id = h.hierarchy_id
              AND hl.dim_level_id = dl.level_id
        )
  ELSE null END AS MAPPED_LEVEL_NAME,
  decode(m.mapped_dim_type, '12', 'DIMENSION LEVEL',
                            '11', 'PRIMARY DIMENSION',
                            '14', 'HIERARCHY LEVEL',
                            '13', 'HIERARCHY') MAPPED_DIMENSION_TYPE,
  s1.syntax_clob JOIN_CONDITION,
  s2.syntax_clob LEVEL_ID_EXPRESSION,
  s3.syntax_clob DIMENSIONALITY_EXPRESSION
FROM
  olap_mappings$ m,
  olap_mappings$ owner_map,
  user$ u,
  olap_dimensionality$ diml,
  obj$ o,
  obj$ o2,
  olap_syntax$ s1,
  olap_syntax$ s2,
  olap_syntax$ s3
WHERE
  m.map_type = 23
  AND m.mapped_object_id = diml.dimensionality_id
  AND m.mapping_owner_id = owner_map.map_id
  AND diml.dimensioned_object_id = o.obj#
  AND o.owner# = u.user#
  AND diml.dimension_id = o2.obj#(+)
  AND m.map_id = s1.owner_id(+)
  AND m.map_type = s1.owner_type(+)
  AND s1.ref_role(+) = 7
  AND m.map_id = s2.owner_id(+)
  AND m.map_type = s2.owner_type(+)
  AND s2.ref_role(+) = 8
  AND m.map_id = s3.owner_id(+)
  AND m.map_type = s3.owner_type(+)
  AND s3.ref_role(+) = 15

View Text - HTML Formatted

SELECT U.NAME OWNER
, O.NAME CUBE_NAME
, OWNER_MAP.MAP_NAME CUBE_MAP_NAME
, M.MAP_NAME MAP_NAME
, M.MAP_ID MAP_ID
, O2.NAME MAPPED_DIMENSION_NAME
, CASE M.MAPPED_DIM_TYPE WHEN 14 -- HIER_LEVEL THEN (SELECT H.HIERARCHY_NAME
FROM OLAP_HIER_LEVELS$ HL
, OLAP_HIERARCHIES$ H
WHERE M.MAPPED_DIM_ID = HL.HIERARCHY_LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID ) WHEN 13 -- HIERARCHY THEN (SELECT H.HIERARCHY_NAME
FROM OLAP_HIERARCHIES$ H
WHERE M.MAPPED_DIM_ID = H.HIERARCHY_ID ) ELSE NULL END AS MAPPED_HIERARCHY_NAME
, CASE M.MAPPED_DIM_TYPE WHEN 12 -- DIM_LEVEL THEN (SELECT DL.LEVEL_NAME
FROM OLAP_DIM_LEVELS$ DL
WHERE M.MAPPED_DIM_ID = DL.LEVEL_ID ) WHEN 14 -- HIER_LEVEL THEN (SELECT DL.LEVEL_NAME
FROM OLAP_DIM_LEVELS$ DL
, OLAP_HIER_LEVELS$ HL
, OLAP_HIERARCHIES$ H
WHERE M.MAPPED_DIM_ID = HL.HIERARCHY_LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID
AND HL.DIM_LEVEL_ID = DL.LEVEL_ID ) ELSE NULL END AS MAPPED_LEVEL_NAME
, DECODE(M.MAPPED_DIM_TYPE
, '12'
, 'DIMENSION LEVEL'
, '11'
, 'PRIMARY DIMENSION'
, '14'
, 'HIERARCHY LEVEL'
, '13'
, 'HIERARCHY') MAPPED_DIMENSION_TYPE
, S1.SYNTAX_CLOB JOIN_CONDITION
, S2.SYNTAX_CLOB LEVEL_ID_EXPRESSION
, S3.SYNTAX_CLOB DIMENSIONALITY_EXPRESSION FROM OLAP_MAPPINGS$ M
, OLAP_MAPPINGS$ OWNER_MAP
, USER$ U
, OLAP_DIMENSIONALITY$ DIML
, OBJ$ O
, OBJ$ O2
, OLAP_SYNTAX$ S1
, OLAP_SYNTAX$ S2
, OLAP_SYNTAX$ S3 WHERE M.MAP_TYPE = 23
AND M.MAPPED_OBJECT_ID = DIML.DIMENSIONALITY_ID
AND M.MAPPING_OWNER_ID = OWNER_MAP.MAP_ID
AND DIML.DIMENSIONED_OBJECT_ID = O.OBJ#
AND O.OWNER# = U.USER#
AND DIML.DIMENSION_ID = O2.OBJ#(+)
AND M.MAP_ID = S1.OWNER_ID(+)
AND M.MAP_TYPE = S1.OWNER_TYPE(+)
AND S1.REF_ROLE(+) = 7
AND M.MAP_ID = S2.OWNER_ID(+)
AND M.MAP_TYPE = S2.OWNER_TYPE(+)
AND S2.REF_ROLE(+) = 8
AND M.MAP_ID = S3.OWNER_ID(+)
AND M.MAP_TYPE = S3.OWNER_TYPE(+)
AND S3.REF_ROLE(+) = 15