SELECT
o.name as D_TOP_OBJ_NAME,
case md.D_OBJ_TYPE /* BEGIN COLUMN D_SUB_OBJ_NAME1 */
WHEN 4 -- ASSIGNMENT
THEN (SELECT m.model_name
FROM olap_model_assignments$ a, olap_models$ m
WHERE md.d_sub_obj# = a.assignment_id
AND m.model_id = a.model_id
AND m.owning_obj_type = 11
AND m.owning_obj_id = md.d_top_obj#
)
WHEN 3 -- model
THEN (SELECT m.model_name
FROM olap_models$ m
WHERE md.d_sub_obj# = m.model_id
AND m.owning_obj_type = 11
AND m.owning_obj_id = md.d_top_obj#
)
WHEN 14 -- hier_level
THEN (SELECT h.hierarchy_name
FROM olap_hier_levels$ hl, olap_hierarchies$ h
WHERE md.d_sub_obj# = hl.hierarchy_level_id
AND hl.hierarchy_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#
)
WHEN 13 -- hierarchy
THEN (SELECT h.hierarchy_name
FROM olap_hierarchies$ h
WHERE md.d_sub_obj# = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#
)
WHEN 12 -- dim_level
THEN (SELECT dl.level_name
FROM olap_dim_levels$ dl
WHERE md.d_sub_obj# = dl.level_id
AND dl.dim_obj# = md.d_top_obj#
)
WHEN 15 -- attribute
THEN (SELECT a.attribute_name
FROM olap_attributes$ a
WHERE md.d_sub_obj# = a.attribute_id
AND a.dim_obj# = md.d_top_obj#
)
WHEN 6 -- calc_member
THEN (SELECT c.member_name
FROM OLAP_CALCULATED_MEMBERS$ c
WHERE md.d_sub_obj# = c.member_id
AND c.dim_obj# = md.d_top_obj#
)
WHEN 18 -- hier_level_map
THEN (SELECT h.hierarchy_name
FROM olap_mappings$ m, olap_hierarchies$ h, olap_hier_levels$ hl
WHERE m.map_id = md.d_sub_obj#
AND m.mapping_owner_id = hl.hierarchy_level_id
AND hl.hierarchy_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#
)
WHEN 19 -- solved_level_hier_map
THEN (SELECT h.hierarchy_name
FROM olap_mappings$ m, olap_hierarchies$ h
WHERE m.map_id = md.d_sub_obj#
AND m.mapping_owner_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#
)
WHEN 20 -- solved_value_hier_map
THEN (SELECT h.hierarchy_name
FROM olap_mappings$ m, olap_hierarchies$ h
WHERE m.map_id = md.d_sub_obj#
AND m.mapping_owner_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#
)
WHEN 21 -- member_list_map
THEN (SELECT
(CASE m.mapping_owner_type
WHEN 11 -- primary_dim
THEN (select m.map_name
from olap_cube_dimensions$ d
where d.obj# = md.d_top_obj#
AND d.obj# = m.mapping_owner_id)
WHEN 12 -- dim_level
THEN (select dl.level_name
from olap_dim_levels$ dl
where m.mapping_owner_id = dl.level_id
AND dl.dim_obj# = md.d_top_obj#)
WHEN 14 -- hier_level
THEN (select h.hierarchy_name
from olap_hier_levels$ hl, olap_hierarchies$ h
where m.mapping_owner_id = hl.hierarchy_level_id
AND hl.hierarchy_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#)
WHEN 13 -- hierarchy
THEN (select h.hierarchy_name
from olap_hierarchies$ h
where m.mapping_owner_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#)
ELSE null
END) AS D_SUB_OBJ_NAME1
FROM olap_mappings$ m
WHERE m.map_id = md.d_sub_obj#
)
WHEN 17 -- attribute_map
THEN (SELECT
(CASE owner_map.mapping_owner_type
WHEN 14 -- hier_level
THEN (select h.hierarchy_name
from olap_hier_levels$ hl, olap_hierarchies$ h
where owner_map.mapping_owner_id = hl.hierarchy_level_id
AND hl.hierarchy_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#)
WHEN 13 -- hierarchy
THEN (select h.hierarchy_name
from olap_hierarchies$ h
where owner_map.mapping_owner_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#)
WHEN 12 -- dim_level
THEN (select dl.level_name
from olap_dim_levels$ dl
where owner_map.mapping_owner_id = dl.level_id
AND dl.dim_obj# = md.d_top_obj#)
WHEN 11 -- primary dimension
THEN (select owner_map.map_name
from olap_cube_dimensions$ d
where d.obj# = md.d_top_obj#
AND owner_map.mapping_owner_id = d.obj#)
ELSE null
END) AS D_SUB_OBJ_NAME1
FROM olap_mappings$ m, olap_mappings$ owner_map
WHERE m.map_id = md.d_sub_obj#
AND m.mapping_owner_id = owner_map.map_id
)
WHEN 2 -- measure
THEN (SELECT meas.measure_name
FROM olap_measures$ meas
WHERE md.d_sub_obj# = meas.measure_id
AND meas.cube_obj# = md.d_top_obj#
)
WHEN 22 -- cube_map
THEN (SELECT m.map_name
FROM olap_mappings$ m
WHERE m.mapping_owner_id = md.d_top_obj#
AND m.map_id = md.d_sub_obj#
)
WHEN 23 -- cube_dimnl_map
THEN (SELECT owner_map.map_name
FROM olap_mappings$ owner_map, olap_mappings$ m
WHERE m.mapping_owner_id = owner_map.map_id
AND m.map_id = md.d_sub_obj#
AND owner_map.mapping_owner_id = md.d_top_obj#
)
WHEN 24 -- cube_meas_map
THEN (SELECT owner_map.map_name
FROM olap_mappings$ owner_map, olap_mappings$ m
WHERE m.mapping_owner_id = owner_map.map_id
AND m.map_id = md.d_sub_obj#
AND owner_map.mapping_owner_id = md.d_top_obj#
)
WHEN 27 -- aw_dim_org
THEN '$AW_ORGANIZATION'
WHEN 28 -- aw_cube_org
THEN '$AW_ORGANIZATION'
WHEN 16 -- dimensionality
THEN (SELECT io_diml.option_value
FROM olap_dimensionality$ diml, olap_impl_options$ io_diml
WHERE diml.DIMENSIONED_OBJECT_ID = md.d_top_obj#
AND diml.DIMENSIONALITY_ID = md.d_sub_obj#
AND io_diml.object_type = 16 -- DIMENSIONALITY
AND io_diml.owning_objectid = diml.dimensionality_id
AND io_diml.option_type = 33 -- DIMENSIONALITY NAME
)
WHEN 30 -- secondary_partition_level
THEN '$AW_ORGANIZATION'
ELSE null
END AS D_SUB_OBJ_NAME1, /* END COLUMN D_SUB_OBJ_NAME1 */
case md.D_OBJ_TYPE /* BEGIN COLUMN D_SUB_OBJ_NAME2 */
WHEN 4 -- ASSIGNMENT
THEN (SELECT a.member_name
FROM olap_model_assignments$ a, olap_models$ m
WHERE md.d_sub_obj# = a.assignment_id
AND m.model_id = a.model_id
AND m.owning_obj_type = 11
AND m.owning_obj_id = md.d_top_obj#
)
WHEN 14 -- hier_level
THEN (SELECT dl.level_name
FROM olap_hier_levels$ hl, olap_dim_levels$ dl,
olap_hierarchies$ h
WHERE md.d_sub_obj# = hl.hierarchy_level_id
AND hl.dim_level_id = dl.level_id
AND hl.hierarchy_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#
)
WHEN 18 -- hier_level_map
THEN (SELECT dl.level_name
FROM olap_mappings$ m, olap_hierarchies$ h, olap_hier_levels$ hl,
olap_dim_levels$ dl
WHERE m.map_id = md.d_sub_obj#
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# = md.d_top_obj#
)
WHEN 19 -- solved_level_hier_map
THEN (SELECT m.map_name
FROM olap_mappings$ m, olap_hierarchies$ h
WHERE m.map_id = md.d_sub_obj#
AND m.mapping_owner_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#
)
WHEN 20 -- solved_value_hier_map
THEN (SELECT m.map_name
FROM olap_mappings$ m, olap_hierarchies$ h
WHERE m.map_id = md.d_sub_obj#
AND m.mapping_owner_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#
)
WHEN 21 -- member_list_map
THEN (SELECT
(CASE m.mapping_owner_type
WHEN 12 -- dim_level
THEN (select m.map_name
from olap_dim_levels$ dl
where m.mapping_owner_id = dl.level_id
AND dl.dim_obj# = md.d_top_obj#)
WHEN 14 -- hier_level
THEN (select dl.level_name
from olap_hier_levels$ hl, olap_hierarchies$ h,
olap_dim_levels$ dl
where m.mapping_owner_id = hl.hierarchy_level_id
AND hl.hierarchy_id = h.hierarchy_id
AND hl.dim_level_id = dl.level_id
AND h.dim_obj# = md.d_top_obj#)
WHEN 13 -- hierarchy
THEN (select m.map_name
from olap_hierarchies$ h
where m.mapping_owner_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#)
ELSE null
END) AS D_SUB_OBJ_NAME1
FROM olap_mappings$ m
WHERE m.map_id = md.d_sub_obj#
)
WHEN 17 -- attribute_map
THEN (SELECT
(CASE owner_map.mapping_owner_type
WHEN 14 -- hier_level
THEN (select dl.level_name
from olap_hier_levels$ hl, olap_hierarchies$ h,
olap_dim_levels$ dl
where owner_map.mapping_owner_id = hl.hierarchy_level_id
AND hl.hierarchy_id = h.hierarchy_id
AND hl.dim_level_id = dl.level_id
AND h.dim_obj# = md.d_top_obj#)
WHEN 13 -- hierarchy
THEN (select owner_map.map_name
from olap_hierarchies$ h
where owner_map.mapping_owner_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#)
WHEN 12 -- dim_level
THEN (select owner_map.map_name
from olap_dim_levels$ dl
where owner_map.mapping_owner_id = dl.level_id
AND dl.dim_obj# = md.d_top_obj#)
WHEN 11 -- primary dimension
THEN (select m.map_name
from olap_cube_dimensions$ d
where d.obj# = md.d_top_obj#
AND owner_map.mapping_owner_id = d.obj#)
ELSE null
END) AS D_SUB_OBJ_NAME2
FROM olap_mappings$ m, olap_mappings$ owner_map
WHERE m.map_id = md.d_sub_obj#
AND m.mapping_owner_id = owner_map.map_id
)
WHEN 23 -- cube_dimnl_map
THEN (SELECT m.map_name
FROM olap_mappings$ owner_map, olap_mappings$ m
WHERE m.mapping_owner_id = owner_map.map_id
AND m.map_id = md.d_sub_obj#
AND owner_map.mapping_owner_id = md.d_top_obj#
)
WHEN 24 -- cube_meas_map
THEN (SELECT m.map_name
FROM olap_mappings$ owner_map, olap_mappings$ m
WHERE m.mapping_owner_id = owner_map.map_id
AND m.map_id = md.d_sub_obj#
AND owner_map.mapping_owner_id = md.d_top_obj#
)
WHEN 30 -- secondary_partition_level
THEN (SELECT
CASE
WHEN md.d_sub_obj# < 3
THEN (SELECT io.option_value
FROM olap_impl_options$ io
WHERE io.owning_objectid = md.d_top_obj#
AND io.object_type = 1
AND io.option_type =
(case when d_sub_obj# = 0 then 38
when d_sub_obj# = 1 then 41
else 44 end))
ELSE (SELECT mo.option_value
FROM olap_multi_options$ mo
WHERE mo.owning_objectid = md.d_top_obj#
AND mo.object_type = 1
AND mo.option_type = 5
AND mo.option_order = md.d_sub_obj#)
END
FROM dual
)
ELSE null
END AS D_SUB_OBJ_NAME2, /* END COLUMN D_SUB_OBJ_NAME2 */
case md.D_OBJ_TYPE /* BEGIN COLUMN D_SUB_OBJ_NAME3 */
WHEN 18 -- hier_level_map
THEN (SELECT m.map_name
FROM olap_mappings$ m, olap_hierarchies$ h, olap_hier_levels$ hl
WHERE m.map_id = md.d_sub_obj#
AND m.mapping_owner_id = hl.hierarchy_level_id
AND hl.hierarchy_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#
)
WHEN 21 -- member_list_map
THEN (SELECT
(CASE m.mapping_owner_type
WHEN 14 -- hier_level
THEN (select m.map_name
from olap_hier_levels$ hl, olap_hierarchies$ h,
olap_dim_levels$ dl
where m.mapping_owner_id = hl.hierarchy_level_id
AND hl.hierarchy_id = h.hierarchy_id
AND hl.dim_level_id = dl.level_id
AND h.dim_obj# = md.d_top_obj#)
ELSE null
END) AS D_SUB_OBJ_NAME1
FROM olap_mappings$ m
WHERE m.map_id = md.d_sub_obj#
)
WHEN 17 -- attribute_map
THEN (SELECT
(CASE owner_map.mapping_owner_type
WHEN 14 -- hier_level
THEN (select owner_map.map_name
from olap_hier_levels$ hl, olap_hierarchies$ h
where owner_map.mapping_owner_id = hl.hierarchy_level_id
AND hl.hierarchy_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#)
WHEN 13 -- hierarchy
THEN (select m.map_name
from olap_hierarchies$ h
where owner_map.mapping_owner_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#)
WHEN 12 -- dim_level
THEN (select m.map_name
from olap_dim_levels$ dl
where owner_map.mapping_owner_id = dl.level_id
AND dl.dim_obj# = md.d_top_obj#)
ELSE null
END) AS D_SUB_OBJ_NAME3
FROM olap_mappings$ m, olap_mappings$ owner_map
WHERE m.map_id = md.d_sub_obj#
AND m.mapping_owner_id = owner_map.map_id
)
ELSE null
END AS D_SUB_OBJ_NAME3, /* END COLUMN D_SUB_OBJ_NAME3 */
case md.D_OBJ_TYPE /* BEGIN COLUMN D_SUB_OBJ_NAME4 */
WHEN 17 -- attribute_map
THEN (SELECT
(CASE owner_map.mapping_owner_type
WHEN 14 -- hier_level
THEN (select m.map_name
from olap_hier_levels$ hl, olap_hierarchies$ h
where owner_map.mapping_owner_id = hl.hierarchy_level_id
AND hl.hierarchy_id = h.hierarchy_id
AND h.dim_obj# = md.d_top_obj#)
ELSE null
END) AS D_SUB_OBJ_NAME3
FROM olap_mappings$ m, olap_mappings$ owner_map
WHERE m.map_id = md.d_sub_obj#
AND m.mapping_owner_id = owner_map.map_id
)
ELSE null
END AS D_SUB_OBJ_NAME4, /* END COLUMN D_SUB_OBJ_NAME4 */
decode(md.d_obj_type, '1', 'CUBE',
'2', 'MEASURE',
'3', 'MODEL',
'4', 'ASSIGNMENT',
'6', 'CALCULATION MEMBER',
'10', 'MEASURE FOLDER',
'8', 'BUILD PROCESS',
'11', 'DIMENSION',
'12', 'DIMENSION LEVEL',
'13', 'HIERARCHY',
'14', 'HIERARCHY LEVEL',
'15', 'ATTRIBUTE',
'16', 'DIMENSIONALITY',
'17', 'ATTRIBUTE MAP',
'18', 'HIER LEVEL MAP',
'19', 'SOLVED LEVEL HIER MAP',
'20', 'SOLVED VALUE HIER MAP',
'21', 'MEMBER LIST MAP',
'22', 'CUBE MAP',
'23', 'CUBE DIMENSIONALITY MAP',
'24', 'MEASURE MAP',
'25', 'TABLE OR VIEW',
'26', 'COLUMN',
'27', 'AW DIM ORGANIZATION',
'28', 'AW CUBE ORGANIZATION',
'29', 'AW',
'30', 'SECONDARY PARTITION LEVEL') D_OBJ_TYPE,
md.P_OWNER P_OBJ_OWNER,
md.P_TOP_OBJ_NAME P_TOP_OBJ_NAME,
md.P_SUB_OBJ_NAME1 P_SUB_OBJ_NAME1,
md.P_SUB_OBJ_NAME2 P_SUB_OBJ_NAME2,
md.P_SUB_OBJ_NAME3 P_SUB_OBJ_NAME3,
md.P_SUB_OBJ_NAME4 P_SUB_OBJ_NAME4,
case md.p_obj_type
WHEN 25 -- TABLE OR VIEW
THEN (SELECT decode(o.type#, '4', 'VIEW', 'TABLE')
FROM obj$ o
WHERE o.obj# = md.p_obj#
)
ELSE decode(md.p_obj_type, '1', 'CUBE',
'2', 'MEASURE',
'3', 'MODEL',
'4', 'ASSIGNMENT',
'6', 'CALCULATION MEMBER',
'8', 'BUILD PROCESS',
'10', 'MEASURE FOLDER',
'11', 'DIMENSION',
'12', 'DIMENSION LEVEL',
'13', 'HIERARCHY',
'14', 'HIERARCHY LEVEL',
'15', 'ATTRIBUTE',
'16', 'DIMENSIONALITY',
'17', 'ATTRIBUTE MAP',
'18', 'HIER LEVEL MAP',
'19', 'SOLVED LEVEL HIER MAP',
'20', 'SOLVED VALUE HIER MAP',
'21', 'MEMBER LIST MAP',
'22', 'CUBE MAP',
'23', 'CUBE DIMENSIONALITY MAP',
'24', 'MEASURE MAP',
'26', 'COLUMN',
'27', 'AW DIM ORGANIZATION',
'28', 'AW CUBE ORGANIZATION',
'29', 'AW') END AS P_OBJ_TYPE,
decode(md.dep_type, '1', 'CONSISTENT SOLVE SPEC',
'2', 'DEFAULT BUILD SPEC',
'3', 'BUILD SPEC',
'4', 'BUILD PROCESS',
'6', 'MEASURE IN MEASURE DIM',
'7', 'CUSTOM ORDER',
'9', 'TARGET ATTRIBUTE',
'10', 'TARGET DIMENSION',
'11', 'MEMBER EXPRESSION',
'12', 'EXPLICIT DIMENSION',
'13', 'PRIMARY DIMENSION',
'15', 'MEASURE IN MEASURE FOLDER',
'16', 'MEASURE FOLDER SUBFOLDER',
'17', 'MAPPED DIMENSION',
'18', 'QUERY',
'19', 'FROM CLAUSE',
'20', 'WHERE CLAUSE',
'21', 'JOIN CONDITION',
'22', 'LEVEL_ID EXPRESSION',
'23', 'KEY EXPRESSION',
'24', 'VALUE MAP EXPRESSION',
'25', 'LEVEL EXPRESSION',
'26', 'PARENT KEY EXPRESSION',
'27', 'PARENT LEVEL_ID EXPRESSION',
'28', 'MEASURE EXPRESSION',
'29', 'NVL EXPRESSION',
'30', 'AW',
'31', 'AW TABLE',
'32', 'PARTITION LEVEL',
'33', 'SECONDARY PARTITION LEVEL',
'34', 'PRECOMPUTE CONDITION'
) DEPENDENCY_TYPE
FROM
olap_metadata_dependencies$ md,
obj$ o
WHERE
o.obj# = md.d_top_obj#
AND o.owner#=USERENV('SCHEMAID')
SELECT
O.NAME AS D_TOP_OBJ_NAME
,
CASE MD.D_OBJ_TYPE /* BEGIN COLUMN D_SUB_OBJ_NAME1 */
WHEN 4 -- ASSIGNMENT
THEN (SELECT M.MODEL_NAME
FROM OLAP_MODEL_ASSIGNMENTS$ A
, OLAP_MODELS$ M
WHERE MD.D_SUB_OBJ# = A.ASSIGNMENT_ID
AND M.MODEL_ID = A.MODEL_ID
AND M.OWNING_OBJ_TYPE = 11
AND M.OWNING_OBJ_ID = MD.D_TOP_OBJ#
)
WHEN 3 -- MODEL
THEN (SELECT M.MODEL_NAME
FROM OLAP_MODELS$ M
WHERE MD.D_SUB_OBJ# = M.MODEL_ID
AND M.OWNING_OBJ_TYPE = 11
AND M.OWNING_OBJ_ID = MD.D_TOP_OBJ#
)
WHEN 14 -- HIER_LEVEL
THEN (SELECT H.HIERARCHY_NAME
FROM OLAP_HIER_LEVELS$ HL
, OLAP_HIERARCHIES$ H
WHERE MD.D_SUB_OBJ# = HL.HIERARCHY_LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#
)
WHEN 13 -- HIERARCHY
THEN (SELECT H.HIERARCHY_NAME
FROM OLAP_HIERARCHIES$ H
WHERE MD.D_SUB_OBJ# = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#
)
WHEN 12 -- DIM_LEVEL
THEN (SELECT DL.LEVEL_NAME
FROM OLAP_DIM_LEVELS$ DL
WHERE MD.D_SUB_OBJ# = DL.LEVEL_ID
AND DL.DIM_OBJ# = MD.D_TOP_OBJ#
)
WHEN 15 -- ATTRIBUTE
THEN (SELECT A.ATTRIBUTE_NAME
FROM OLAP_ATTRIBUTES$ A
WHERE MD.D_SUB_OBJ# = A.ATTRIBUTE_ID
AND A.DIM_OBJ# = MD.D_TOP_OBJ#
)
WHEN 6 -- CALC_MEMBER
THEN (SELECT C.MEMBER_NAME
FROM OLAP_CALCULATED_MEMBERS$ C
WHERE MD.D_SUB_OBJ# = C.MEMBER_ID
AND C.DIM_OBJ# = MD.D_TOP_OBJ#
)
WHEN 18 -- HIER_LEVEL_MAP
THEN (SELECT H.HIERARCHY_NAME
FROM OLAP_MAPPINGS$ M
, OLAP_HIERARCHIES$ H
, OLAP_HIER_LEVELS$ HL
WHERE M.MAP_ID = MD.D_SUB_OBJ#
AND M.MAPPING_OWNER_ID = HL.HIERARCHY_LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#
)
WHEN 19 -- SOLVED_LEVEL_HIER_MAP
THEN (SELECT H.HIERARCHY_NAME
FROM OLAP_MAPPINGS$ M
, OLAP_HIERARCHIES$ H
WHERE M.MAP_ID = MD.D_SUB_OBJ#
AND M.MAPPING_OWNER_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#
)
WHEN 20 -- SOLVED_VALUE_HIER_MAP
THEN (SELECT H.HIERARCHY_NAME
FROM OLAP_MAPPINGS$ M
, OLAP_HIERARCHIES$ H
WHERE M.MAP_ID = MD.D_SUB_OBJ#
AND M.MAPPING_OWNER_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#
)
WHEN 21 -- MEMBER_LIST_MAP
THEN (SELECT
(CASE M.MAPPING_OWNER_TYPE
WHEN 11 -- PRIMARY_DIM
THEN (SELECT M.MAP_NAME
FROM OLAP_CUBE_DIMENSIONS$ D
WHERE D.OBJ# = MD.D_TOP_OBJ#
AND D.OBJ# = M.MAPPING_OWNER_ID)
WHEN 12 -- DIM_LEVEL
THEN (SELECT DL.LEVEL_NAME
FROM OLAP_DIM_LEVELS$ DL
WHERE M.MAPPING_OWNER_ID = DL.LEVEL_ID
AND DL.DIM_OBJ# = MD.D_TOP_OBJ#)
WHEN 14 -- HIER_LEVEL
THEN (SELECT H.HIERARCHY_NAME
FROM OLAP_HIER_LEVELS$ HL
, OLAP_HIERARCHIES$ H
WHERE M.MAPPING_OWNER_ID = HL.HIERARCHY_LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#)
WHEN 13 -- HIERARCHY
THEN (SELECT H.HIERARCHY_NAME
FROM OLAP_HIERARCHIES$ H
WHERE M.MAPPING_OWNER_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#)
ELSE NULL
END) AS D_SUB_OBJ_NAME1
FROM OLAP_MAPPINGS$ M
WHERE M.MAP_ID = MD.D_SUB_OBJ#
)
WHEN 17 -- ATTRIBUTE_MAP
THEN (SELECT
(CASE OWNER_MAP.MAPPING_OWNER_TYPE
WHEN 14 -- HIER_LEVEL
THEN (SELECT H.HIERARCHY_NAME
FROM OLAP_HIER_LEVELS$ HL
, OLAP_HIERARCHIES$ H
WHERE OWNER_MAP.MAPPING_OWNER_ID = HL.HIERARCHY_LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#)
WHEN 13 -- HIERARCHY
THEN (SELECT H.HIERARCHY_NAME
FROM OLAP_HIERARCHIES$ H
WHERE OWNER_MAP.MAPPING_OWNER_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#)
WHEN 12 -- DIM_LEVEL
THEN (SELECT DL.LEVEL_NAME
FROM OLAP_DIM_LEVELS$ DL
WHERE OWNER_MAP.MAPPING_OWNER_ID = DL.LEVEL_ID
AND DL.DIM_OBJ# = MD.D_TOP_OBJ#)
WHEN 11 -- PRIMARY DIMENSION
THEN (SELECT OWNER_MAP.MAP_NAME
FROM OLAP_CUBE_DIMENSIONS$ D
WHERE D.OBJ# = MD.D_TOP_OBJ#
AND OWNER_MAP.MAPPING_OWNER_ID = D.OBJ#)
ELSE NULL
END) AS D_SUB_OBJ_NAME1
FROM OLAP_MAPPINGS$ M
, OLAP_MAPPINGS$ OWNER_MAP
WHERE M.MAP_ID = MD.D_SUB_OBJ#
AND M.MAPPING_OWNER_ID = OWNER_MAP.MAP_ID
)
WHEN 2 -- MEASURE
THEN (SELECT MEAS.MEASURE_NAME
FROM OLAP_MEASURES$ MEAS
WHERE MD.D_SUB_OBJ# = MEAS.MEASURE_ID
AND MEAS.CUBE_OBJ# = MD.D_TOP_OBJ#
)
WHEN 22 -- CUBE_MAP
THEN (SELECT M.MAP_NAME
FROM OLAP_MAPPINGS$ M
WHERE M.MAPPING_OWNER_ID = MD.D_TOP_OBJ#
AND M.MAP_ID = MD.D_SUB_OBJ#
)
WHEN 23 -- CUBE_DIMNL_MAP
THEN (SELECT OWNER_MAP.MAP_NAME
FROM OLAP_MAPPINGS$ OWNER_MAP
, OLAP_MAPPINGS$ M
WHERE M.MAPPING_OWNER_ID = OWNER_MAP.MAP_ID
AND M.MAP_ID = MD.D_SUB_OBJ#
AND OWNER_MAP.MAPPING_OWNER_ID = MD.D_TOP_OBJ#
)
WHEN 24 -- CUBE_MEAS_MAP
THEN (SELECT OWNER_MAP.MAP_NAME
FROM OLAP_MAPPINGS$ OWNER_MAP
, OLAP_MAPPINGS$ M
WHERE M.MAPPING_OWNER_ID = OWNER_MAP.MAP_ID
AND M.MAP_ID = MD.D_SUB_OBJ#
AND OWNER_MAP.MAPPING_OWNER_ID = MD.D_TOP_OBJ#
)
WHEN 27 -- AW_DIM_ORG
THEN '$AW_ORGANIZATION'
WHEN 28 -- AW_CUBE_ORG
THEN '$AW_ORGANIZATION'
WHEN 16 -- DIMENSIONALITY
THEN (SELECT IO_DIML.OPTION_VALUE
FROM OLAP_DIMENSIONALITY$ DIML
, OLAP_IMPL_OPTIONS$ IO_DIML
WHERE DIML.DIMENSIONED_OBJECT_ID = MD.D_TOP_OBJ#
AND DIML.DIMENSIONALITY_ID = MD.D_SUB_OBJ#
AND IO_DIML.OBJECT_TYPE = 16 -- DIMENSIONALITY
AND IO_DIML.OWNING_OBJECTID = DIML.DIMENSIONALITY_ID
AND IO_DIML.OPTION_TYPE = 33 -- DIMENSIONALITY NAME
)
WHEN 30 -- SECONDARY_PARTITION_LEVEL
THEN '$AW_ORGANIZATION'
ELSE NULL
END AS D_SUB_OBJ_NAME1
, /* END COLUMN D_SUB_OBJ_NAME1 */
CASE MD.D_OBJ_TYPE /* BEGIN COLUMN D_SUB_OBJ_NAME2 */
WHEN 4 -- ASSIGNMENT
THEN (SELECT A.MEMBER_NAME
FROM OLAP_MODEL_ASSIGNMENTS$ A
, OLAP_MODELS$ M
WHERE MD.D_SUB_OBJ# = A.ASSIGNMENT_ID
AND M.MODEL_ID = A.MODEL_ID
AND M.OWNING_OBJ_TYPE = 11
AND M.OWNING_OBJ_ID = MD.D_TOP_OBJ#
)
WHEN 14 -- HIER_LEVEL
THEN (SELECT DL.LEVEL_NAME
FROM OLAP_HIER_LEVELS$ HL
, OLAP_DIM_LEVELS$ DL
,
OLAP_HIERARCHIES$ H
WHERE MD.D_SUB_OBJ# = HL.HIERARCHY_LEVEL_ID
AND HL.DIM_LEVEL_ID = DL.LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#
)
WHEN 18 -- HIER_LEVEL_MAP
THEN (SELECT DL.LEVEL_NAME
FROM OLAP_MAPPINGS$ M
, OLAP_HIERARCHIES$ H
, OLAP_HIER_LEVELS$ HL
,
OLAP_DIM_LEVELS$ DL
WHERE M.MAP_ID = MD.D_SUB_OBJ#
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# = MD.D_TOP_OBJ#
)
WHEN 19 -- SOLVED_LEVEL_HIER_MAP
THEN (SELECT M.MAP_NAME
FROM OLAP_MAPPINGS$ M
, OLAP_HIERARCHIES$ H
WHERE M.MAP_ID = MD.D_SUB_OBJ#
AND M.MAPPING_OWNER_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#
)
WHEN 20 -- SOLVED_VALUE_HIER_MAP
THEN (SELECT M.MAP_NAME
FROM OLAP_MAPPINGS$ M
, OLAP_HIERARCHIES$ H
WHERE M.MAP_ID = MD.D_SUB_OBJ#
AND M.MAPPING_OWNER_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#
)
WHEN 21 -- MEMBER_LIST_MAP
THEN (SELECT
(CASE M.MAPPING_OWNER_TYPE
WHEN 12 -- DIM_LEVEL
THEN (SELECT M.MAP_NAME
FROM OLAP_DIM_LEVELS$ DL
WHERE M.MAPPING_OWNER_ID = DL.LEVEL_ID
AND DL.DIM_OBJ# = MD.D_TOP_OBJ#)
WHEN 14 -- HIER_LEVEL
THEN (SELECT DL.LEVEL_NAME
FROM OLAP_HIER_LEVELS$ HL
, OLAP_HIERARCHIES$ H
,
OLAP_DIM_LEVELS$ DL
WHERE M.MAPPING_OWNER_ID = HL.HIERARCHY_LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID
AND HL.DIM_LEVEL_ID = DL.LEVEL_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#)
WHEN 13 -- HIERARCHY
THEN (SELECT M.MAP_NAME
FROM OLAP_HIERARCHIES$ H
WHERE M.MAPPING_OWNER_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#)
ELSE NULL
END) AS D_SUB_OBJ_NAME1
FROM OLAP_MAPPINGS$ M
WHERE M.MAP_ID = MD.D_SUB_OBJ#
)
WHEN 17 -- ATTRIBUTE_MAP
THEN (SELECT
(CASE OWNER_MAP.MAPPING_OWNER_TYPE
WHEN 14 -- HIER_LEVEL
THEN (SELECT DL.LEVEL_NAME
FROM OLAP_HIER_LEVELS$ HL
, OLAP_HIERARCHIES$ H
,
OLAP_DIM_LEVELS$ DL
WHERE OWNER_MAP.MAPPING_OWNER_ID = HL.HIERARCHY_LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID
AND HL.DIM_LEVEL_ID = DL.LEVEL_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#)
WHEN 13 -- HIERARCHY
THEN (SELECT OWNER_MAP.MAP_NAME
FROM OLAP_HIERARCHIES$ H
WHERE OWNER_MAP.MAPPING_OWNER_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#)
WHEN 12 -- DIM_LEVEL
THEN (SELECT OWNER_MAP.MAP_NAME
FROM OLAP_DIM_LEVELS$ DL
WHERE OWNER_MAP.MAPPING_OWNER_ID = DL.LEVEL_ID
AND DL.DIM_OBJ# = MD.D_TOP_OBJ#)
WHEN 11 -- PRIMARY DIMENSION
THEN (SELECT M.MAP_NAME
FROM OLAP_CUBE_DIMENSIONS$ D
WHERE D.OBJ# = MD.D_TOP_OBJ#
AND OWNER_MAP.MAPPING_OWNER_ID = D.OBJ#)
ELSE NULL
END) AS D_SUB_OBJ_NAME2
FROM OLAP_MAPPINGS$ M
, OLAP_MAPPINGS$ OWNER_MAP
WHERE M.MAP_ID = MD.D_SUB_OBJ#
AND M.MAPPING_OWNER_ID = OWNER_MAP.MAP_ID
)
WHEN 23 -- CUBE_DIMNL_MAP
THEN (SELECT M.MAP_NAME
FROM OLAP_MAPPINGS$ OWNER_MAP
, OLAP_MAPPINGS$ M
WHERE M.MAPPING_OWNER_ID = OWNER_MAP.MAP_ID
AND M.MAP_ID = MD.D_SUB_OBJ#
AND OWNER_MAP.MAPPING_OWNER_ID = MD.D_TOP_OBJ#
)
WHEN 24 -- CUBE_MEAS_MAP
THEN (SELECT M.MAP_NAME
FROM OLAP_MAPPINGS$ OWNER_MAP
, OLAP_MAPPINGS$ M
WHERE M.MAPPING_OWNER_ID = OWNER_MAP.MAP_ID
AND M.MAP_ID = MD.D_SUB_OBJ#
AND OWNER_MAP.MAPPING_OWNER_ID = MD.D_TOP_OBJ#
)
WHEN 30 -- SECONDARY_PARTITION_LEVEL
THEN (SELECT
CASE
WHEN MD.D_SUB_OBJ# < 3
THEN (SELECT IO.OPTION_VALUE
FROM OLAP_IMPL_OPTIONS$ IO
WHERE IO.OWNING_OBJECTID = MD.D_TOP_OBJ#
AND IO.OBJECT_TYPE = 1
AND IO.OPTION_TYPE =
(CASE WHEN D_SUB_OBJ# = 0 THEN 38
WHEN D_SUB_OBJ# = 1 THEN 41
ELSE 44 END))
ELSE (SELECT MO.OPTION_VALUE
FROM OLAP_MULTI_OPTIONS$ MO
WHERE MO.OWNING_OBJECTID = MD.D_TOP_OBJ#
AND MO.OBJECT_TYPE = 1
AND MO.OPTION_TYPE = 5
AND MO.OPTION_ORDER = MD.D_SUB_OBJ#)
END
FROM DUAL
)
ELSE NULL
END AS D_SUB_OBJ_NAME2
, /* END COLUMN D_SUB_OBJ_NAME2 */
CASE MD.D_OBJ_TYPE /* BEGIN COLUMN D_SUB_OBJ_NAME3 */
WHEN 18 -- HIER_LEVEL_MAP
THEN (SELECT M.MAP_NAME
FROM OLAP_MAPPINGS$ M
, OLAP_HIERARCHIES$ H
, OLAP_HIER_LEVELS$ HL
WHERE M.MAP_ID = MD.D_SUB_OBJ#
AND M.MAPPING_OWNER_ID = HL.HIERARCHY_LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#
)
WHEN 21 -- MEMBER_LIST_MAP
THEN (SELECT
(CASE M.MAPPING_OWNER_TYPE
WHEN 14 -- HIER_LEVEL
THEN (SELECT M.MAP_NAME
FROM OLAP_HIER_LEVELS$ HL
, OLAP_HIERARCHIES$ H
,
OLAP_DIM_LEVELS$ DL
WHERE M.MAPPING_OWNER_ID = HL.HIERARCHY_LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID
AND HL.DIM_LEVEL_ID = DL.LEVEL_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#)
ELSE NULL
END) AS D_SUB_OBJ_NAME1
FROM OLAP_MAPPINGS$ M
WHERE M.MAP_ID = MD.D_SUB_OBJ#
)
WHEN 17 -- ATTRIBUTE_MAP
THEN (SELECT
(CASE OWNER_MAP.MAPPING_OWNER_TYPE
WHEN 14 -- HIER_LEVEL
THEN (SELECT OWNER_MAP.MAP_NAME
FROM OLAP_HIER_LEVELS$ HL
, OLAP_HIERARCHIES$ H
WHERE OWNER_MAP.MAPPING_OWNER_ID = HL.HIERARCHY_LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#)
WHEN 13 -- HIERARCHY
THEN (SELECT M.MAP_NAME
FROM OLAP_HIERARCHIES$ H
WHERE OWNER_MAP.MAPPING_OWNER_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#)
WHEN 12 -- DIM_LEVEL
THEN (SELECT M.MAP_NAME
FROM OLAP_DIM_LEVELS$ DL
WHERE OWNER_MAP.MAPPING_OWNER_ID = DL.LEVEL_ID
AND DL.DIM_OBJ# = MD.D_TOP_OBJ#)
ELSE NULL
END) AS D_SUB_OBJ_NAME3
FROM OLAP_MAPPINGS$ M
, OLAP_MAPPINGS$ OWNER_MAP
WHERE M.MAP_ID = MD.D_SUB_OBJ#
AND M.MAPPING_OWNER_ID = OWNER_MAP.MAP_ID
)
ELSE NULL
END AS D_SUB_OBJ_NAME3
, /* END COLUMN D_SUB_OBJ_NAME3 */
CASE MD.D_OBJ_TYPE /* BEGIN COLUMN D_SUB_OBJ_NAME4 */
WHEN 17 -- ATTRIBUTE_MAP
THEN (SELECT
(CASE OWNER_MAP.MAPPING_OWNER_TYPE
WHEN 14 -- HIER_LEVEL
THEN (SELECT M.MAP_NAME
FROM OLAP_HIER_LEVELS$ HL
, OLAP_HIERARCHIES$ H
WHERE OWNER_MAP.MAPPING_OWNER_ID = HL.HIERARCHY_LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = MD.D_TOP_OBJ#)
ELSE NULL
END) AS D_SUB_OBJ_NAME3
FROM OLAP_MAPPINGS$ M
, OLAP_MAPPINGS$ OWNER_MAP
WHERE M.MAP_ID = MD.D_SUB_OBJ#
AND M.MAPPING_OWNER_ID = OWNER_MAP.MAP_ID
)
ELSE NULL
END AS D_SUB_OBJ_NAME4
, /* END COLUMN D_SUB_OBJ_NAME4 */
DECODE(MD.D_OBJ_TYPE
, '1'
, 'CUBE'
,
'2'
, 'MEASURE'
,
'3'
, 'MODEL'
,
'4'
, 'ASSIGNMENT'
,
'6'
, 'CALCULATION MEMBER'
,
'10'
, 'MEASURE FOLDER'
,
'8'
, 'BUILD PROCESS'
,
'11'
, 'DIMENSION'
,
'12'
, 'DIMENSION LEVEL'
,
'13'
, 'HIERARCHY'
,
'14'
, 'HIERARCHY LEVEL'
,
'15'
, 'ATTRIBUTE'
,
'16'
, 'DIMENSIONALITY'
,
'17'
, 'ATTRIBUTE MAP'
,
'18'
, 'HIER LEVEL MAP'
,
'19'
, 'SOLVED LEVEL HIER MAP'
,
'20'
, 'SOLVED VALUE HIER MAP'
,
'21'
, 'MEMBER LIST MAP'
,
'22'
, 'CUBE MAP'
,
'23'
, 'CUBE DIMENSIONALITY MAP'
,
'24'
, 'MEASURE MAP'
,
'25'
, 'TABLE OR VIEW'
,
'26'
, 'COLUMN'
,
'27'
, 'AW DIM ORGANIZATION'
,
'28'
, 'AW CUBE ORGANIZATION'
,
'29'
, 'AW'
,
'30'
, 'SECONDARY PARTITION LEVEL') D_OBJ_TYPE
,
MD.P_OWNER P_OBJ_OWNER
,
MD.P_TOP_OBJ_NAME P_TOP_OBJ_NAME
,
MD.P_SUB_OBJ_NAME1 P_SUB_OBJ_NAME1
,
MD.P_SUB_OBJ_NAME2 P_SUB_OBJ_NAME2
,
MD.P_SUB_OBJ_NAME3 P_SUB_OBJ_NAME3
,
MD.P_SUB_OBJ_NAME4 P_SUB_OBJ_NAME4
,
CASE MD.P_OBJ_TYPE
WHEN 25 -- TABLE OR VIEW
THEN (SELECT DECODE(O.TYPE#
, '4'
, 'VIEW'
, 'TABLE')
FROM OBJ$ O
WHERE O.OBJ# = MD.P_OBJ#
)
ELSE DECODE(MD.P_OBJ_TYPE
, '1'
, 'CUBE'
,
'2'
, 'MEASURE'
,
'3'
, 'MODEL'
,
'4'
, 'ASSIGNMENT'
,
'6'
, 'CALCULATION MEMBER'
,
'8'
, 'BUILD PROCESS'
,
'10'
, 'MEASURE FOLDER'
,
'11'
, 'DIMENSION'
,
'12'
, 'DIMENSION LEVEL'
,
'13'
, 'HIERARCHY'
,
'14'
, 'HIERARCHY LEVEL'
,
'15'
, 'ATTRIBUTE'
,
'16'
, 'DIMENSIONALITY'
,
'17'
, 'ATTRIBUTE MAP'
,
'18'
, 'HIER LEVEL MAP'
,
'19'
, 'SOLVED LEVEL HIER MAP'
,
'20'
, 'SOLVED VALUE HIER MAP'
,
'21'
, 'MEMBER LIST MAP'
,
'22'
, 'CUBE MAP'
,
'23'
, 'CUBE DIMENSIONALITY MAP'
,
'24'
, 'MEASURE MAP'
,
'26'
, 'COLUMN'
,
'27'
, 'AW DIM ORGANIZATION'
,
'28'
, 'AW CUBE ORGANIZATION'
,
'29'
, 'AW') END AS P_OBJ_TYPE
,
DECODE(MD.DEP_TYPE
, '1'
, 'CONSISTENT SOLVE SPEC'
,
'2'
, 'DEFAULT BUILD SPEC'
,
'3'
, 'BUILD SPEC'
,
'4'
, 'BUILD PROCESS'
,
'6'
, 'MEASURE IN MEASURE DIM'
,
'7'
, 'CUSTOM ORDER'
,
'9'
, 'TARGET ATTRIBUTE'
,
'10'
, 'TARGET DIMENSION'
,
'11'
, 'MEMBER EXPRESSION'
,
'12'
, 'EXPLICIT DIMENSION'
,
'13'
, 'PRIMARY DIMENSION'
,
'15'
, 'MEASURE IN MEASURE FOLDER'
,
'16'
, 'MEASURE FOLDER SUBFOLDER'
,
'17'
, 'MAPPED DIMENSION'
,
'18'
, 'QUERY'
,
'19'
, 'FROM CLAUSE'
,
'20'
, 'WHERE CLAUSE'
,
'21'
, 'JOIN CONDITION'
,
'22'
, 'LEVEL_ID EXPRESSION'
,
'23'
, 'KEY EXPRESSION'
,
'24'
, 'VALUE MAP EXPRESSION'
,
'25'
, 'LEVEL EXPRESSION'
,
'26'
, 'PARENT KEY EXPRESSION'
,
'27'
, 'PARENT LEVEL_ID EXPRESSION'
,
'28'
, 'MEASURE EXPRESSION'
,
'29'
, 'NVL EXPRESSION'
,
'30'
, 'AW'
,
'31'
, 'AW TABLE'
,
'32'
, 'PARTITION LEVEL'
,
'33'
, 'SECONDARY PARTITION LEVEL'
,
'34'
, 'PRECOMPUTE CONDITION'
) DEPENDENCY_TYPE
FROM
OLAP_METADATA_DEPENDENCIES$ MD
,
OBJ$ O
WHERE
O.OBJ# = MD.D_TOP_OBJ#
AND O.OWNER#=USERENV('SCHEMAID')
|
|
|