select cmm.owner owner,
cmm.cube_name cube_name,
cmm.measure_name measure_name,
cmm.dim_hier_combo_id dim_hier_combo_id,
cmm.fact_table_owner fact_table_owner,
cmm.fact_table_name fact_table_name,
cmm.column_name column_name,
c.data_type data_type,
c.data_length data_length,
c.data_precision data_precision,
(case when c.data_type = 'NUMBER' then 0
when c.data_type = 'DOUBLE' then 5
when c.data_type = 'FLOAT' then 4
when c.data_type = 'DATE' then 7
when c.data_type = 'LONG' then 3
else 1 end) olap_api_data_type
from olapsys.dba$olap2_cube_measure_maps cmm,
dba_olap_columns c
where
cmm.fact_table_owner = c.owner
and cmm.fact_table_name = c.table_name
and cmm.column_name = c.column_name
SELECT CMM.OWNER OWNER
,
CMM.CUBE_NAME CUBE_NAME
,
CMM.MEASURE_NAME MEASURE_NAME
,
CMM.DIM_HIER_COMBO_ID DIM_HIER_COMBO_ID
,
CMM.FACT_TABLE_OWNER FACT_TABLE_OWNER
,
CMM.FACT_TABLE_NAME FACT_TABLE_NAME
,
CMM.COLUMN_NAME COLUMN_NAME
,
C.DATA_TYPE DATA_TYPE
,
C.DATA_LENGTH DATA_LENGTH
,
C.DATA_PRECISION DATA_PRECISION
,
(CASE WHEN C.DATA_TYPE = 'NUMBER' THEN 0
WHEN C.DATA_TYPE = 'DOUBLE' THEN 5
WHEN C.DATA_TYPE = 'FLOAT' THEN 4
WHEN C.DATA_TYPE = 'DATE' THEN 7
WHEN C.DATA_TYPE = 'LONG' THEN 3
ELSE 1 END) OLAP_API_DATA_TYPE
FROM OLAPSYS.DBA$OLAP2_CUBE_MEASURE_MAPS CMM
,
DBA_OLAP_COLUMNS C
WHERE
CMM.FACT_TABLE_OWNER = C.OWNER
AND CMM.FACT_TABLE_NAME = C.TABLE_NAME
AND CMM.COLUMN_NAME = C.COLUMN_NAME
|
|
|