SELECT distinct
sch.physicalname owner
, cub.physicalname cube_name
, msr.physicalname measure_name
, 0 dim_hier_combo_id
, use2.object_owner fact_table_owner
, use2.object_name fact_table_name
, use2.secondary_object_name column_name
FROM
dba_users u
, cwm$model sch
, cwm$cube cub
, cwm$measure msr
, cwm$itemuse use1
, cwm$itemuse use2
, cwm$cubedimensionuse cdu
, cwm$dimension cd
, dba_users du
, sys.obj$ do
WHERE u.username = sch.physicalname
AND sch.irid = cub.datamodel_irid
AND cub.irid = msr.itemcontainer_irid
AND msr.irid = use1.mappable_irid
AND use1.operation_irid = use2.operation_irid_1
AND cdu.cube_irid = cub.irid
AND cdu.dimension_owner = du.username
AND cdu.dimension_name = do.name
AND du.user_id = do.owner#
AND do.obj# = cd.irid
AND do.type# = 43
AND cd.irid = cdu.abstractdimension_irid
union all
select owner, cube_name, measure_name,
dim_hier_combo_id, fact_table_owner,
fact_table_name, column_name
from olapsys.dba$olap2_cube_measure_maps
with read only
SELECT DISTINCT
SCH.PHYSICALNAME OWNER
, CUB.PHYSICALNAME CUBE_NAME
, MSR.PHYSICALNAME MEASURE_NAME
, 0 DIM_HIER_COMBO_ID
, USE2.OBJECT_OWNER FACT_TABLE_OWNER
, USE2.OBJECT_NAME FACT_TABLE_NAME
, USE2.SECONDARY_OBJECT_NAME COLUMN_NAME
FROM
DBA_USERS U
, CWM$MODEL SCH
, CWM$CUBE CUB
, CWM$MEASURE MSR
, CWM$ITEMUSE USE1
, CWM$ITEMUSE USE2
, CWM$CUBEDIMENSIONUSE CDU
, CWM$DIMENSION CD
, DBA_USERS DU
, SYS.OBJ$ DO
WHERE U.USERNAME = SCH.PHYSICALNAME
AND SCH.IRID = CUB.DATAMODEL_IRID
AND CUB.IRID = MSR.ITEMCONTAINER_IRID
AND MSR.IRID = USE1.MAPPABLE_IRID
AND USE1.OPERATION_IRID = USE2.OPERATION_IRID_1
AND CDU.CUBE_IRID = CUB.IRID
AND CDU.DIMENSION_OWNER = DU.USERNAME
AND CDU.DIMENSION_NAME = DO.NAME
AND DU.USER_ID = DO.OWNER#
AND DO.OBJ# = CD.IRID
AND DO.TYPE# = 43
AND CD.IRID = CDU.ABSTRACTDIMENSION_IRID
UNION ALL
SELECT OWNER
, CUBE_NAME
, MEASURE_NAME
,
DIM_HIER_COMBO_ID
, FACT_TABLE_OWNER
,
FACT_TABLE_NAME
, COLUMN_NAME
FROM OLAPSYS.DBA$OLAP2_CUBE_MEASURE_MAPS
WITH READ ONLY
|
|
|