eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: SYS.DBA_CUBE_DIM_MAPPINGS

Source

View Text - Preformatted


SELECT
  u.name OWNER,
  o.name DIMENSION_NAME,
  null HIERARCHY_NAME,
  dl.level_name LEVEL_NAME,
  m.map_name MAP_NAME,
  m.map_id MAP_ID,
  'DIMENSION LEVEL' MAPPED_DIMENSION_TYPE,
  null MAPPED_HIERARCHY_TYPE,
  s1.syntax_clob QUERY,
  s2.syntax_clob KEY_EXPRESSION,
  s3.syntax_clob FROM_CLAUSE,
  s4.syntax_clob WHERE_CLAUSE,
  null JOIN_CONDITION,
  null LEVEL_ID_EXPRESSION,
  null PARENT_EXPRESSION,
  null PARENT_LEVEL_ID_EXPRESSION,
  null LEVEL_EXPRESSION
FROM
  olap_mappings$ m,
  user$ u,
  obj$ o,
  olap_dim_levels$ dl,
  olap_syntax$ s1,
  olap_syntax$ s2,
  olap_syntax$ s3,
  olap_syntax$ s4
WHERE
  m.map_type in (18, 19, 20, 21)
  AND m.mapping_owner_type = 12 -- dim_level
  AND m.mapping_owner_id = dl.level_id
  AND dl.dim_obj# = o.obj#
  AND o.owner#=u.user#
  AND m.map_id = s1.owner_id(+)
  AND m.map_type = s1.owner_type(+)
  AND s1.ref_role(+) = 3
  AND m.map_id = s2.owner_id(+)
  AND m.map_type = s2.owner_type(+)
  AND s2.ref_role(+) = 6
  AND m.map_id = s3.owner_id(+)
  AND m.map_type = s3.owner_type(+)
  AND s3.ref_role(+) = 22
  AND m.map_id = s4.owner_id(+)
  AND m.map_type = s4.owner_type(+)
  AND s4.ref_role(+) = 21
UNION ALL
SELECT
  u.name OWNER,
  o.name DIMENSION_NAME,
  h.hierarchy_name HIERARCHY_NAME,
  dl.level_name LEVEL_NAME,
  m.map_name MAP_NAME,
  m.map_id MAP_ID,
  'HIERARCHY LEVEL' MAPPED_DIMENSION_TYPE,
  DECODE(h.hierarchy_type, 1, 'LEVEL', 2, 'VALUE') MAPPED_HIERARCHY_TYPE,
  s1.syntax_clob QUERY,
  s2.syntax_clob KEY_EXPRESSION,
  s3.syntax_clob FROM_CLAUSE,
  s4.syntax_clob WHERE_CLAUSE,
  s5.syntax_clob JOIN_CONDITION,
  null LEVEL_ID_EXPRESSION,
  null PARENT_EXPRESSION,
  null PARENT_LEVEL_ID_EXPRESSION,
  null LEVEL_EXPRESSION
FROM
  olap_mappings$ m,
  user$ u,
  obj$ o,
  olap_dim_levels$ dl,
  olap_hier_levels$ hl,
  olap_hierarchies$ h,
  olap_syntax$ s1,
  olap_syntax$ s2,
  olap_syntax$ s3,
  olap_syntax$ s4,
  olap_syntax$ s5
WHERE
  m.map_type in (18, 19, 20, 21)
  AND m.mapping_owner_type = 14 -- hier_level
  AND m.mapping_owner_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#
  AND o.owner#=u.user#
  AND m.map_id = s1.owner_id(+)
  AND m.map_type = s1.owner_type(+)
  AND s1.ref_role(+) = 3
  AND m.map_id = s2.owner_id(+)
  AND m.map_type = s2.owner_type(+)
  AND s2.ref_role(+) = 6
  AND m.map_id = s3.owner_id(+)
  AND m.map_type = s3.owner_type(+)
  AND s3.ref_role(+) = 22
  AND m.map_id = s4.owner_id(+)
  AND m.map_type = s4.owner_type(+)
  AND s4.ref_role(+) = 21
  AND m.map_id = s5.owner_id(+)
  AND m.map_type = s5.owner_type(+)
  AND s5.ref_role(+) = 7
UNION ALL
SELECT
  u.name OWNER,
  o.name DIMENSION_NAME,
  h.hierarchy_name HIERARCHY_NAME,
  null LEVEL_NAME,
  m.map_name MAP_NAME,
  m.map_id MAP_ID,
  'HIERARCHY' MAPPED_DIMENSION_TYPE,
  DECODE(h.hierarchy_type, 1, 'LEVEL', 2, 'VALUE') MAPPED_HIERARCHY_TYPE,
  s1.syntax_clob QUERY,
  s2.syntax_clob KEY_EXPRESSION,
  s3.syntax_clob FROM_CLAUSE,
  s4.syntax_clob WHERE_CLAUSE,
  null JOIN_CONDITION,
  s5.syntax_clob LEVEL_ID_EXPRESSION,
  s6.syntax_clob PARENT_EXPRESSION,
  s7.syntax_clob PARENT_LEVEL_ID_EXPRESSION,
  s8.syntax_clob LEVEL_EXPRESSION
FROM
  olap_mappings$ m,
  user$ u,
  obj$ o,
  olap_hierarchies$ h,
  olap_syntax$ s1,
  olap_syntax$ s2,
  olap_syntax$ s3,
  olap_syntax$ s4,
  olap_syntax$ s5,
  olap_syntax$ s6,
  olap_syntax$ s7,
  olap_syntax$ s8
WHERE
  m.map_type in (18, 19, 20, 21)
  AND m.mapping_owner_type = 13 -- hierarchy
  AND m.mapping_owner_id = h.hierarchy_id
  AND h.dim_obj# = o.obj#
  AND o.owner#=u.user#
  AND m.map_id = s1.owner_id(+)
  AND m.map_type = s1.owner_type(+)
  AND s1.ref_role(+) = 3
  AND m.map_id = s2.owner_id(+)
  AND m.map_type = s2.owner_type(+)
  AND s2.ref_role(+) = 6
  AND m.map_id = s3.owner_id(+)
  AND m.map_type = s3.owner_type(+)
  AND s3.ref_role(+) = 22
  AND m.map_id = s4.owner_id(+)
  AND m.map_type = s4.owner_type(+)
  AND s4.ref_role(+) = 21
  AND m.map_id = s5.owner_id(+)
  AND m.map_type = s5.owner_type(+)
  AND s5.ref_role(+) = 8
  AND m.map_id = s6.owner_id(+)
  AND m.map_type = s6.owner_type(+)
  AND s6.ref_role(+) = 9
  AND m.map_id = s7.owner_id(+)
  AND m.map_type = s7.owner_type(+)
  AND s7.ref_role(+) = 10
  AND m.map_id = s8.owner_id(+)
  AND m.map_type = s8.owner_type(+)
  AND s8.ref_role(+) = 11
UNION ALL
SELECT
  u.name OWNER,
  o.name DIMENSION_NAME,
  null HIERARCHY_NAME,
  null LEVEL_NAME,
  m.map_name MAP_NAME,
  m.map_id MAP_ID,
  'PRIMARY DIMENSION' MAPPED_DIMENSION_TYPE,
  null MAPPED_HIERARCHY_TYPE,
  s1.syntax_clob QUERY,
  s2.syntax_clob KEY_EXPRESSION,
  s3.syntax_clob FROM_CLAUSE,
  s4.syntax_clob WHERE_CLAUSE,
  null JOIN_CONDITION,
  null LEVEL_ID_EXPRESSION,
  null PARENT_EXPRESSION,
  null PARENT_LEVEL_ID_EXPRESSION,
  null LEVEL_EXPRESSION
FROM
  olap_mappings$ m,
  user$ u,
  obj$ o,
  olap_syntax$ s1,
  olap_syntax$ s2,
  olap_syntax$ s3,
  olap_syntax$ s4
WHERE
  m.map_type in (18, 19, 20, 21)
  AND m.mapping_owner_type = 11 -- dimension
  AND m.mapping_owner_id = o.obj#
  AND o.owner#=u.user#
  AND m.map_id = s1.owner_id(+)
  AND m.map_type = s1.owner_type(+)
  AND s1.ref_role(+) = 3
  AND m.map_id = s2.owner_id(+)
  AND m.map_type = s2.owner_type(+)
  AND s2.ref_role(+) = 6
  AND m.map_id = s3.owner_id(+)
  AND m.map_type = s3.owner_type(+)
  AND s3.ref_role(+) = 22
  AND m.map_id = s4.owner_id(+)
  AND m.map_type = s4.owner_type(+)
  AND s4.ref_role(+) = 21

View Text - HTML Formatted

SELECT U.NAME OWNER
, O.NAME DIMENSION_NAME
, NULL HIERARCHY_NAME
, DL.LEVEL_NAME LEVEL_NAME
, M.MAP_NAME MAP_NAME
, M.MAP_ID MAP_ID
, 'DIMENSION LEVEL' MAPPED_DIMENSION_TYPE
, NULL MAPPED_HIERARCHY_TYPE
, S1.SYNTAX_CLOB QUERY
, S2.SYNTAX_CLOB KEY_EXPRESSION
, S3.SYNTAX_CLOB FROM_CLAUSE
, S4.SYNTAX_CLOB WHERE_CLAUSE
, NULL JOIN_CONDITION
, NULL LEVEL_ID_EXPRESSION
, NULL PARENT_EXPRESSION
, NULL PARENT_LEVEL_ID_EXPRESSION
, NULL LEVEL_EXPRESSION FROM OLAP_MAPPINGS$ M
, USER$ U
, OBJ$ O
, OLAP_DIM_LEVELS$ DL
, OLAP_SYNTAX$ S1
, OLAP_SYNTAX$ S2
, OLAP_SYNTAX$ S3
, OLAP_SYNTAX$ S4 WHERE M.MAP_TYPE IN (18
, 19
, 20
, 21)
AND M.MAPPING_OWNER_TYPE = 12 -- DIM_LEVEL
AND M.MAPPING_OWNER_ID = DL.LEVEL_ID
AND DL.DIM_OBJ# = O.OBJ#
AND O.OWNER#=U.USER#
AND M.MAP_ID = S1.OWNER_ID(+)
AND M.MAP_TYPE = S1.OWNER_TYPE(+)
AND S1.REF_ROLE(+) = 3
AND M.MAP_ID = S2.OWNER_ID(+)
AND M.MAP_TYPE = S2.OWNER_TYPE(+)
AND S2.REF_ROLE(+) = 6
AND M.MAP_ID = S3.OWNER_ID(+)
AND M.MAP_TYPE = S3.OWNER_TYPE(+)
AND S3.REF_ROLE(+) = 22
AND M.MAP_ID = S4.OWNER_ID(+)
AND M.MAP_TYPE = S4.OWNER_TYPE(+)
AND S4.REF_ROLE(+) = 21 UNION ALL SELECT U.NAME OWNER
, O.NAME DIMENSION_NAME
, H.HIERARCHY_NAME HIERARCHY_NAME
, DL.LEVEL_NAME LEVEL_NAME
, M.MAP_NAME MAP_NAME
, M.MAP_ID MAP_ID
, 'HIERARCHY LEVEL' MAPPED_DIMENSION_TYPE
, DECODE(H.HIERARCHY_TYPE
, 1
, 'LEVEL'
, 2
, 'VALUE') MAPPED_HIERARCHY_TYPE
, S1.SYNTAX_CLOB QUERY
, S2.SYNTAX_CLOB KEY_EXPRESSION
, S3.SYNTAX_CLOB FROM_CLAUSE
, S4.SYNTAX_CLOB WHERE_CLAUSE
, S5.SYNTAX_CLOB JOIN_CONDITION
, NULL LEVEL_ID_EXPRESSION
, NULL PARENT_EXPRESSION
, NULL PARENT_LEVEL_ID_EXPRESSION
, NULL LEVEL_EXPRESSION FROM OLAP_MAPPINGS$ M
, USER$ U
, OBJ$ O
, OLAP_DIM_LEVELS$ DL
, OLAP_HIER_LEVELS$ HL
, OLAP_HIERARCHIES$ H
, OLAP_SYNTAX$ S1
, OLAP_SYNTAX$ S2
, OLAP_SYNTAX$ S3
, OLAP_SYNTAX$ S4
, OLAP_SYNTAX$ S5 WHERE M.MAP_TYPE IN (18
, 19
, 20
, 21)
AND M.MAPPING_OWNER_TYPE = 14 -- HIER_LEVEL
AND M.MAPPING_OWNER_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#
AND O.OWNER#=U.USER#
AND M.MAP_ID = S1.OWNER_ID(+)
AND M.MAP_TYPE = S1.OWNER_TYPE(+)
AND S1.REF_ROLE(+) = 3
AND M.MAP_ID = S2.OWNER_ID(+)
AND M.MAP_TYPE = S2.OWNER_TYPE(+)
AND S2.REF_ROLE(+) = 6
AND M.MAP_ID = S3.OWNER_ID(+)
AND M.MAP_TYPE = S3.OWNER_TYPE(+)
AND S3.REF_ROLE(+) = 22
AND M.MAP_ID = S4.OWNER_ID(+)
AND M.MAP_TYPE = S4.OWNER_TYPE(+)
AND S4.REF_ROLE(+) = 21
AND M.MAP_ID = S5.OWNER_ID(+)
AND M.MAP_TYPE = S5.OWNER_TYPE(+)
AND S5.REF_ROLE(+) = 7 UNION ALL SELECT U.NAME OWNER
, O.NAME DIMENSION_NAME
, H.HIERARCHY_NAME HIERARCHY_NAME
, NULL LEVEL_NAME
, M.MAP_NAME MAP_NAME
, M.MAP_ID MAP_ID
, 'HIERARCHY' MAPPED_DIMENSION_TYPE
, DECODE(H.HIERARCHY_TYPE
, 1
, 'LEVEL'
, 2
, 'VALUE') MAPPED_HIERARCHY_TYPE
, S1.SYNTAX_CLOB QUERY
, S2.SYNTAX_CLOB KEY_EXPRESSION
, S3.SYNTAX_CLOB FROM_CLAUSE
, S4.SYNTAX_CLOB WHERE_CLAUSE
, NULL JOIN_CONDITION
, S5.SYNTAX_CLOB LEVEL_ID_EXPRESSION
, S6.SYNTAX_CLOB PARENT_EXPRESSION
, S7.SYNTAX_CLOB PARENT_LEVEL_ID_EXPRESSION
, S8.SYNTAX_CLOB LEVEL_EXPRESSION FROM OLAP_MAPPINGS$ M
, USER$ U
, OBJ$ O
, OLAP_HIERARCHIES$ H
, OLAP_SYNTAX$ S1
, OLAP_SYNTAX$ S2
, OLAP_SYNTAX$ S3
, OLAP_SYNTAX$ S4
, OLAP_SYNTAX$ S5
, OLAP_SYNTAX$ S6
, OLAP_SYNTAX$ S7
, OLAP_SYNTAX$ S8 WHERE M.MAP_TYPE IN (18
, 19
, 20
, 21)
AND M.MAPPING_OWNER_TYPE = 13 -- HIERARCHY
AND M.MAPPING_OWNER_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = O.OBJ#
AND O.OWNER#=U.USER#
AND M.MAP_ID = S1.OWNER_ID(+)
AND M.MAP_TYPE = S1.OWNER_TYPE(+)
AND S1.REF_ROLE(+) = 3
AND M.MAP_ID = S2.OWNER_ID(+)
AND M.MAP_TYPE = S2.OWNER_TYPE(+)
AND S2.REF_ROLE(+) = 6
AND M.MAP_ID = S3.OWNER_ID(+)
AND M.MAP_TYPE = S3.OWNER_TYPE(+)
AND S3.REF_ROLE(+) = 22
AND M.MAP_ID = S4.OWNER_ID(+)
AND M.MAP_TYPE = S4.OWNER_TYPE(+)
AND S4.REF_ROLE(+) = 21
AND M.MAP_ID = S5.OWNER_ID(+)
AND M.MAP_TYPE = S5.OWNER_TYPE(+)
AND S5.REF_ROLE(+) = 8
AND M.MAP_ID = S6.OWNER_ID(+)
AND M.MAP_TYPE = S6.OWNER_TYPE(+)
AND S6.REF_ROLE(+) = 9
AND M.MAP_ID = S7.OWNER_ID(+)
AND M.MAP_TYPE = S7.OWNER_TYPE(+)
AND S7.REF_ROLE(+) = 10
AND M.MAP_ID = S8.OWNER_ID(+)
AND M.MAP_TYPE = S8.OWNER_TYPE(+)
AND S8.REF_ROLE(+) = 11 UNION ALL SELECT U.NAME OWNER
, O.NAME DIMENSION_NAME
, NULL HIERARCHY_NAME
, NULL LEVEL_NAME
, M.MAP_NAME MAP_NAME
, M.MAP_ID MAP_ID
, 'PRIMARY DIMENSION' MAPPED_DIMENSION_TYPE
, NULL MAPPED_HIERARCHY_TYPE
, S1.SYNTAX_CLOB QUERY
, S2.SYNTAX_CLOB KEY_EXPRESSION
, S3.SYNTAX_CLOB FROM_CLAUSE
, S4.SYNTAX_CLOB WHERE_CLAUSE
, NULL JOIN_CONDITION
, NULL LEVEL_ID_EXPRESSION
, NULL PARENT_EXPRESSION
, NULL PARENT_LEVEL_ID_EXPRESSION
, NULL LEVEL_EXPRESSION FROM OLAP_MAPPINGS$ M
, USER$ U
, OBJ$ O
, OLAP_SYNTAX$ S1
, OLAP_SYNTAX$ S2
, OLAP_SYNTAX$ S3
, OLAP_SYNTAX$ S4 WHERE M.MAP_TYPE IN (18
, 19
, 20
, 21)
AND M.MAPPING_OWNER_TYPE = 11 -- DIMENSION
AND M.MAPPING_OWNER_ID = O.OBJ#
AND O.OWNER#=U.USER#
AND M.MAP_ID = S1.OWNER_ID(+)
AND M.MAP_TYPE = S1.OWNER_TYPE(+)
AND S1.REF_ROLE(+) = 3
AND M.MAP_ID = S2.OWNER_ID(+)
AND M.MAP_TYPE = S2.OWNER_TYPE(+)
AND S2.REF_ROLE(+) = 6
AND M.MAP_ID = S3.OWNER_ID(+)
AND M.MAP_TYPE = S3.OWNER_TYPE(+)
AND S3.REF_ROLE(+) = 22
AND M.MAP_ID = S4.OWNER_ID(+)
AND M.MAP_TYPE = S4.OWNER_TYPE(+)
AND S4.REF_ROLE(+) = 21