ZSF_DS_METRIC_REALTIME

Details

Schema: FUSION
Object owner: ZSF
Object type: VIEW

Columns

Name
METRIC_ID PERIOD_ID SNAPSHOT_ID METRIC_DIM1_CODE METRIC_DIM1_KEY METRIC_DIM2_CODE METRIC_DIM2_KEY METRIC_DIM3_CODE METRIC_DIM3_KEY METRIC_DIM4_CODE METRIC_DIM4_KEY METRIC_DIM5_CODE METRIC_DIM5_KEY DIM1_ROOT_FLAG DIM2_ROOT_FLAG DIM3_ROOT_FLAG DIM4_ROOT_FLAG DIM5_ROOT_FLAG METRIC_CODE METRIC_LEVEL METRIC_VALUE CREATED_BY CREATION_DATE LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN OBJECT_VERSION_NUMBER

Name

METRIC_ID

PERIOD_ID

SNAPSHOT_ID

METRIC_DIM1_CODE

METRIC_DIM1_KEY

METRIC_DIM2_CODE

METRIC_DIM2_KEY

METRIC_DIM3_CODE

METRIC_DIM3_KEY

METRIC_DIM4_CODE

METRIC_DIM4_KEY

METRIC_DIM5_CODE

METRIC_DIM5_KEY

DIM1_ROOT_FLAG

DIM2_ROOT_FLAG

DIM3_ROOT_FLAG

DIM4_ROOT_FLAG

DIM5_ROOT_FLAG

METRIC_CODE

METRIC_LEVEL

METRIC_VALUE

CREATED_BY

CREATION_DATE

LAST_UPDATE_DATE

LAST_UPDATED_BY

LAST_UPDATE_LOGIN

OBJECT_VERSION_NUMBER

Query

SQL_Statement
select chg.metric_src_chg_log_id metric_id , chg.period_id period_id , chg.period_id snapshot_id , s.metric_dim1_code metric_dim1_code , dim1.metric_dim_parent_key metric_dim1_key , s.metric_dim2_code metric_dim2_code , dim2.metric_dim_parent_key metric_dim2_key , s.metric_dim3_code metric_dim3_code , dim3.metric_dim_parent_key metric_dim3_key , s.metric_dim4_code metric_dim4_code , dim4.metric_dim_parent_key metric_dim4_key , s.metric_dim5_code metric_dim5_code , dim5.metric_dim_parent_key metric_dim5_key , dim1.root_parent_flag dim1_root_flag , dim2.root_parent_flag dim2_root_flag , dim3.root_parent_flag dim3_root_flag , dim4.root_parent_flag dim4_root_flag , dim5.root_parent_flag dim5_root_flag , chg.metric_code metric_code , decode(mlvl.col , 1, 'TOTAL' , 2, decode( dim1.metric_dim_key , dim1.metric_dim_parent_key , 'OWN' , 'SUB')) metric_level , chg.diff_amount metric_value , chg.created_by created_by , chg.creation_date creation_date , chg.last_update_date last_update_date , chg.last_updated_by last_updated_by , chg.last_update_login last_update_login , chg.object_version_number object_version_number from zsf_ds_metric_src_chg_log chg , ( select 1 as col from dual d1 where rownum = 1 union all select 2 as col from dual d2 where rownum = 1) mlvl , zsf_ds_metric_dim_rf_dn dim1 , zsf_ds_metric_dim_rf_dn dim2 , zsf_ds_metric_dim_rf_dn dim3 , zsf_ds_metric_dim_rf_dn dim4 , zsf_ds_metric_dim_rf_dn dim5 , zsf_ds_metric_defn d , zsf_ds_metric_src s where 0 = 0 and d.metric_code = chg.metric_code and s.metric_src_code = d.metric_src_code and NVL(chg.metric_level,1) = 'NRT' and d.status_code = 'ENABLED' and chg.metric_dim1_key = dim1.metric_dim_key and chg.metric_dim2_key = dim2.metric_dim_key (+) and chg.metric_dim3_key = dim3.metric_dim_key (+) and chg.metric_dim4_key = dim4.metric_dim_key (+) and chg.metric_dim5_key = dim5.metric_dim_key (+) and dim1.metric_dim_code(+) = s.metric_dim1_code and dim2.metric_dim_code(+) = s.metric_dim2_code and dim3.metric_dim_code(+) = s.metric_dim3_code and dim4.metric_dim_code(+) = s.metric_dim4_code and dim5.metric_dim_code(+) = s.metric_dim5_code and s.metric_src_code = (SELECT param_value from zsf_ds_param where param_name='ACTIVE_METRIC_SOURCE') union all select chg.metric_src_chg_log_id metric_id , chg.period_id period_id , chg.period_id snapshot_id , s.metric_dim1_code metric_dim1_code , dim1.metric_dim_parent_key metric_dim1_key , s.metric_dim2_code metric_dim2_code , dim2.metric_dim_parent_key metric_dim2_key , s.metric_dim3_code metric_dim3_code , dim3.metric_dim_parent_key metric_dim3_key , s.metric_dim4_code metric_dim4_code , dim4.metric_dim_parent_key metric_dim4_key , s.metric_dim5_code metric_dim5_code , dim5.metric_dim_parent_key metric_dim5_key , dim1.root_parent_flag dim1_root_flag , dim2.root_parent_flag dim2_root_flag , dim3.root_parent_flag dim3_root_flag , dim4.root_parent_flag dim4_root_flag , dim5.root_parent_flag dim5_root_flag , chg.metric_code metric_code , decode(mlvl.col , 1, 'TOTAL' , 2, decode( dim1.metric_dim_key , dim1.metric_dim_parent_key , 'OWN' , 'SUB')) metric_level , chg.diff_amount metric_value , chg.created_by created_by , chg.creation_date creation_date , chg.last_update_date last_update_date , chg.last_updated_by last_updated_by , chg.last_update_login last_update_login , chg.object_version_number object_version_number from zsf_ds_metric_src_chg_log chg , ( select 1 as col from dual d1 where rownum = 1 union all select 2 as col from dual d2 where rownum = 1) mlvl , zsf_ds_met_dim_rf_dn_stg dim1 , zsf_ds_met_dim_rf_dn_stg dim2 , zsf_ds_met_dim_rf_dn_stg dim3 , zsf_ds_met_dim_rf_dn_stg dim4 , zsf_ds_met_dim_rf_dn_stg dim5 , zsf_ds_metric_defn d , zsf_ds_metric_src s where 0 = 0 and d.metric_code = chg.metric_code and s.metric_src_code = d.metric_src_code and chg.metric_level = 'DEL' and dim1.dim_data_set(+) = 'OLD' and dim2.dim_data_set(+) = 'OLD' and dim3.dim_data_set(+) = 'OLD' and dim4.dim_data_set(+) = 'OLD' and dim5.dim_data_set(+) = 'OLD' and chg.metric_dim1_key = dim1.metric_dim_key and chg.metric_dim2_key = dim2.metric_dim_key (+) and chg.metric_dim3_key = dim3.metric_dim_key (+) and chg.metric_dim4_key = dim4.metric_dim_key (+) and chg.metric_dim5_key = dim5.metric_dim_key (+) and NVL(dim1.metric_dim_key,1) = NVL(dim1.metric_dim_parent_key,1) and NVL(dim2.metric_dim_key,1) = NVL(dim2.metric_dim_parent_key,1) and NVL(dim3.metric_dim_key,1) = NVL(dim3.metric_dim_parent_key,1) and NVL(dim4.metric_dim_key,1) = NVL(dim4.metric_dim_parent_key,1) and NVL(dim5.metric_dim_key,1) = NVL(dim5.metric_dim_parent_key,1) and dim1.metric_dim_code(+) = s.metric_dim1_code and dim2.metric_dim_code(+) = s.metric_dim2_code and dim3.metric_dim_code(+) = s.metric_dim3_code and dim4.metric_dim_code(+) = s.metric_dim4_code and dim5.metric_dim_code(+) = s.metric_dim5_code and s.metric_src_code = (SELECT param_value from zsf_ds_param where param_name='ACTIVE_METRIC_SOURCE') union all select m.metric_id , m.period_id , m.snapshot_id , s.metric_dim1_code , m.metric_dim1_key , s.metric_dim2_code , m.metric_dim2_key , s.metric_dim3_code , m.metric_dim3_key , s.metric_dim4_code , m.metric_dim4_key , s.metric_dim5_code , m.metric_dim5_key , m.dim1_root_flag , m.dim2_root_flag , m.dim3_root_flag , m.dim4_root_flag , m.dim5_root_flag , m.metric_code , m.metric_level , m.metric_value , m.created_by , m.creation_date , m.last_update_date , m.last_updated_by , m.last_update_login , m.object_version_number from zsf_ds_metric m , zsf_ds_metric_defn d , zsf_ds_metric_src s where d.metric_code = m.metric_code and s.metric_src_code = d.metric_src_code and d.status_code = 'ENABLED' and s.metric_src_code = (SELECT param_value from zsf_ds_param where param_name='ACTIVE_METRIC_SOURCE') and m.metric_level not like 'ADJUSTED:%'

SQL_Statement

select

chg.metric_src_chg_log_id metric_id

, chg.period_id period_id

, chg.period_id snapshot_id

, s.metric_dim1_code metric_dim1_code

, dim1.metric_dim_parent_key metric_dim1_key

, s.metric_dim2_code metric_dim2_code

, dim2.metric_dim_parent_key metric_dim2_key

, s.metric_dim3_code metric_dim3_code

, dim3.metric_dim_parent_key metric_dim3_key

, s.metric_dim4_code metric_dim4_code

, dim4.metric_dim_parent_key metric_dim4_key

, s.metric_dim5_code metric_dim5_code

, dim5.metric_dim_parent_key metric_dim5_key

, dim1.root_parent_flag dim1_root_flag

, dim2.root_parent_flag dim2_root_flag

, dim3.root_parent_flag dim3_root_flag

, dim4.root_parent_flag dim4_root_flag

, dim5.root_parent_flag dim5_root_flag

, chg.metric_code metric_code

, decode(mlvl.col

, 1, 'TOTAL'

, 2, decode(

dim1.metric_dim_key

, dim1.metric_dim_parent_key

, 'OWN'

, 'SUB')) metric_level

, chg.diff_amount metric_value

, chg.created_by created_by

, chg.creation_date creation_date

, chg.last_update_date last_update_date

, chg.last_updated_by last_updated_by

, chg.last_update_login last_update_login

, chg.object_version_number object_version_number

from

zsf_ds_metric_src_chg_log chg

, ( select 1 as col from dual d1 where rownum = 1 union all

select 2 as col from dual d2 where rownum = 1) mlvl

, zsf_ds_metric_dim_rf_dn dim1

, zsf_ds_metric_dim_rf_dn dim2

, zsf_ds_metric_dim_rf_dn dim3

, zsf_ds_metric_dim_rf_dn dim4

, zsf_ds_metric_dim_rf_dn dim5

, zsf_ds_metric_defn d

, zsf_ds_metric_src s

where 0 = 0

and d.metric_code = chg.metric_code

and s.metric_src_code = d.metric_src_code

and NVL(chg.metric_level,1) = 'NRT'

and d.status_code = 'ENABLED'

and chg.metric_dim1_key = dim1.metric_dim_key

and chg.metric_dim2_key = dim2.metric_dim_key (+)

and chg.metric_dim3_key = dim3.metric_dim_key (+)

and chg.metric_dim4_key = dim4.metric_dim_key (+)

and chg.metric_dim5_key = dim5.metric_dim_key (+)

and dim1.metric_dim_code(+) = s.metric_dim1_code

and dim2.metric_dim_code(+) = s.metric_dim2_code

and dim3.metric_dim_code(+) = s.metric_dim3_code

and dim4.metric_dim_code(+) = s.metric_dim4_code

and dim5.metric_dim_code(+) = s.metric_dim5_code

and s.metric_src_code = (SELECT param_value from zsf_ds_param where param_name='ACTIVE_METRIC_SOURCE')

union all

select

chg.metric_src_chg_log_id metric_id

, chg.period_id period_id

, chg.period_id snapshot_id

, s.metric_dim1_code metric_dim1_code

, dim1.metric_dim_parent_key metric_dim1_key

, s.metric_dim2_code metric_dim2_code

, dim2.metric_dim_parent_key metric_dim2_key

, s.metric_dim3_code metric_dim3_code

, dim3.metric_dim_parent_key metric_dim3_key

, s.metric_dim4_code metric_dim4_code

, dim4.metric_dim_parent_key metric_dim4_key

, s.metric_dim5_code metric_dim5_code

, dim5.metric_dim_parent_key metric_dim5_key

, dim1.root_parent_flag dim1_root_flag

, dim2.root_parent_flag dim2_root_flag

, dim3.root_parent_flag dim3_root_flag

, dim4.root_parent_flag dim4_root_flag

, dim5.root_parent_flag dim5_root_flag

, chg.metric_code metric_code

, decode(mlvl.col

, 1, 'TOTAL'

, 2, decode(

dim1.metric_dim_key

, dim1.metric_dim_parent_key

, 'OWN'

, 'SUB')) metric_level

, chg.diff_amount metric_value

, chg.created_by created_by

, chg.creation_date creation_date

, chg.last_update_date last_update_date

, chg.last_updated_by last_updated_by

, chg.last_update_login last_update_login

, chg.object_version_number object_version_number

from

zsf_ds_metric_src_chg_log chg

, ( select 1 as col from dual d1 where rownum = 1 union all

select 2 as col from dual d2 where rownum = 1) mlvl

, zsf_ds_met_dim_rf_dn_stg dim1

, zsf_ds_met_dim_rf_dn_stg dim2

, zsf_ds_met_dim_rf_dn_stg dim3

, zsf_ds_met_dim_rf_dn_stg dim4

, zsf_ds_met_dim_rf_dn_stg dim5

, zsf_ds_metric_defn d

, zsf_ds_metric_src s

where 0 = 0

and d.metric_code = chg.metric_code

and s.metric_src_code = d.metric_src_code

and chg.metric_level = 'DEL'

and dim1.dim_data_set(+) = 'OLD'

and dim2.dim_data_set(+) = 'OLD'

and dim3.dim_data_set(+) = 'OLD'

and dim4.dim_data_set(+) = 'OLD'

and dim5.dim_data_set(+) = 'OLD'

and chg.metric_dim1_key = dim1.metric_dim_key

and chg.metric_dim2_key = dim2.metric_dim_key (+)

and chg.metric_dim3_key = dim3.metric_dim_key (+)

and chg.metric_dim4_key = dim4.metric_dim_key (+)

and chg.metric_dim5_key = dim5.metric_dim_key (+)

and NVL(dim1.metric_dim_key,1) = NVL(dim1.metric_dim_parent_key,1)

and NVL(dim2.metric_dim_key,1) = NVL(dim2.metric_dim_parent_key,1)

and NVL(dim3.metric_dim_key,1) = NVL(dim3.metric_dim_parent_key,1)

and NVL(dim4.metric_dim_key,1) = NVL(dim4.metric_dim_parent_key,1)

and NVL(dim5.metric_dim_key,1) = NVL(dim5.metric_dim_parent_key,1)

and dim1.metric_dim_code(+) = s.metric_dim1_code

and dim2.metric_dim_code(+) = s.metric_dim2_code

and dim3.metric_dim_code(+) = s.metric_dim3_code

and dim4.metric_dim_code(+) = s.metric_dim4_code

and dim5.metric_dim_code(+) = s.metric_dim5_code

and s.metric_src_code = (SELECT param_value from zsf_ds_param

where param_name='ACTIVE_METRIC_SOURCE')

union all

select

m.metric_id

, m.period_id

, m.snapshot_id

, s.metric_dim1_code

, m.metric_dim1_key

, s.metric_dim2_code

, m.metric_dim2_key

, s.metric_dim3_code

, m.metric_dim3_key

, s.metric_dim4_code

, m.metric_dim4_key

, s.metric_dim5_code

, m.metric_dim5_key

, m.dim1_root_flag

, m.dim2_root_flag

, m.dim3_root_flag

, m.dim4_root_flag

, m.dim5_root_flag

, m.metric_code

, m.metric_level

, m.metric_value

, m.created_by

, m.creation_date

, m.last_update_date

, m.last_updated_by

, m.last_update_login

, m.object_version_number

from

zsf_ds_metric m

, zsf_ds_metric_defn d

, zsf_ds_metric_src s

where

d.metric_code = m.metric_code

and s.metric_src_code = d.metric_src_code

and d.status_code = 'ENABLED'

and s.metric_src_code = (SELECT param_value from zsf_ds_param where param_name='ACTIVE_METRIC_SOURCE')

and m.metric_level not like 'ADJUSTED:%'