VIEW: SYS.ALL_CUBE_ATTR_MAPPINGS

Source

View Text - Preformatted

SELECT
  u.name OWNER,
  o.name DIMENSION_NAME,
  a.attribute_name ATTRIBUTE_NAME,
  decode(owner_map.mapping_owner_type, '12', 'DIMENSION LEVEL',
                            '11', 'PRIMARY DIMENSION',
                            '14', 'HIERARCHY LEVEL',
                            '13', 'HIERARCHY') OWNER_MAP_DIMENSION_TYPE,
  CASE owner_map.mapping_owner_type
  WHEN 14  -- hier_level
  THEN (select h.hierarchy_name
        from olap_dim_levels$ dl,
             olap_hier_levels$ hl, olap_hierarchies$ h
        where owner_map.mapping_owner_id = hl.hierarchy_level_id
              AND hl.dim_level_id = dl.level_id
              AND hl.hierarchy_id = h.hierarchy_id)
  WHEN 13 -- hierarchy
  THEN (select h.hierarchy_name
        from olap_hierarchies$ h
        where owner_map.mapping_owner_id = h.hierarchy_id)
  ELSE NULL
  END AS OWNER_MAP_HIERARCHY_NAME,
  CASE owner_map.mapping_owner_type
  WHEN 12 -- dim_level
  THEN (select dl.level_name
        from olap_dim_levels$ dl
        where owner_map.mapping_owner_id = dl.level_id)
  WHEN 14  -- hier_level
  THEN (select dl.level_name
        from olap_dim_levels$ dl, olap_hier_levels$ hl
        where owner_map.mapping_owner_id = hl.hierarchy_level_id
              AND hl.dim_level_id = dl.level_id)
  ELSE NULL
  END AS OWNER_MAP_LEVEL_NAME,
  owner_map.map_name OWNER_MAP_NAME,
  m.map_name MAP_NAME,
  m.map_id MAP_ID,
  s1.syntax_clob ATTRIBUTE_EXPRESSION,
  i1.option_value LANGUAGE
FROM
  olap_mappings$ m,
  olap_mappings$ owner_map,
  obj$ o,
  user$ u,
  olap_attributes$ a,
  olap_syntax$ s1,
  olap_impl_options$ i1
WHERE
  m.map_type = 17
  AND m.mapping_owner_id = owner_map.map_id
  AND m.mapped_object_id = a.attribute_id
  AND a.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(+) = 2
  AND m.map_id = i1.owning_objectid(+)
  AND i1.option_type(+) = 12
  AND (o.owner# in (userenv('SCHEMAID'), 1)   -- public objects
       or o.obj# in
            ( select obj#  -- directly granted privileges
              from sys.objauth$
              where grantee# in ( select kzsrorol from x$kzsro )
            )
       or   -- user has system privileges
              ( exists (select null from v$enabledprivs
                        where priv_number in (-302, -- ALTER ANY PRIMARY DIMENSION
                                              -304, -- DELETE ANY PRIMARY DIMENSION
                                              -305, -- DROP ANY PRIMARY DIMENSION
                                              -306, -- INSERT ANY PRIMARY DIMENSION
                                              -307) -- SELECT ANY PRIMARY DIMENSION
                        )
              )
            )

View Text - HTML Formatted

SELECT U.NAME OWNER
, O.NAME DIMENSION_NAME
, A.ATTRIBUTE_NAME ATTRIBUTE_NAME
, DECODE(OWNER_MAP.MAPPING_OWNER_TYPE
, '12'
, 'DIMENSION LEVEL'
, '11'
, 'PRIMARY DIMENSION'
, '14'
, 'HIERARCHY LEVEL'
, '13'
, 'HIERARCHY') OWNER_MAP_DIMENSION_TYPE
, CASE OWNER_MAP.MAPPING_OWNER_TYPE WHEN 14 -- HIER_LEVEL THEN (SELECT H.HIERARCHY_NAME
FROM OLAP_DIM_LEVELS$ DL
, OLAP_HIER_LEVELS$ HL
, OLAP_HIERARCHIES$ H
WHERE OWNER_MAP.MAPPING_OWNER_ID = HL.HIERARCHY_LEVEL_ID
AND HL.DIM_LEVEL_ID = DL.LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID) WHEN 13 -- HIERARCHY THEN (SELECT H.HIERARCHY_NAME
FROM OLAP_HIERARCHIES$ H
WHERE OWNER_MAP.MAPPING_OWNER_ID = H.HIERARCHY_ID) ELSE NULL END AS OWNER_MAP_HIERARCHY_NAME
, CASE OWNER_MAP.MAPPING_OWNER_TYPE WHEN 12 -- DIM_LEVEL THEN (SELECT DL.LEVEL_NAME
FROM OLAP_DIM_LEVELS$ DL
WHERE OWNER_MAP.MAPPING_OWNER_ID = DL.LEVEL_ID) WHEN 14 -- HIER_LEVEL THEN (SELECT DL.LEVEL_NAME
FROM OLAP_DIM_LEVELS$ DL
, OLAP_HIER_LEVELS$ HL
WHERE OWNER_MAP.MAPPING_OWNER_ID = HL.HIERARCHY_LEVEL_ID
AND HL.DIM_LEVEL_ID = DL.LEVEL_ID) ELSE NULL END AS OWNER_MAP_LEVEL_NAME
, OWNER_MAP.MAP_NAME OWNER_MAP_NAME
, M.MAP_NAME MAP_NAME
, M.MAP_ID MAP_ID
, S1.SYNTAX_CLOB ATTRIBUTE_EXPRESSION
, I1.OPTION_VALUE LANGUAGE FROM OLAP_MAPPINGS$ M
, OLAP_MAPPINGS$ OWNER_MAP
, OBJ$ O
, USER$ U
, OLAP_ATTRIBUTES$ A
, OLAP_SYNTAX$ S1
, OLAP_IMPL_OPTIONS$ I1 WHERE M.MAP_TYPE = 17
AND M.MAPPING_OWNER_ID = OWNER_MAP.MAP_ID
AND M.MAPPED_OBJECT_ID = A.ATTRIBUTE_ID
AND A.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(+) = 2
AND M.MAP_ID = I1.OWNING_OBJECTID(+)
AND I1.OPTION_TYPE(+) = 12
AND (O.OWNER# IN (USERENV('SCHEMAID')
, 1) -- PUBLIC OBJECTS OR O.OBJ# IN ( SELECT OBJ# -- DIRECTLY GRANTED PRIVILEGES
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR -- USER HAS SYSTEM PRIVILEGES ( EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-302
, -- ALTER ANY PRIMARY DIMENSION -304
, -- DELETE ANY PRIMARY DIMENSION -305
, -- DROP ANY PRIMARY DIMENSION -306
, -- INSERT ANY PRIMARY DIMENSION -307) -- SELECT ANY PRIMARY DIMENSION ) ) )