VIEW: OLAPSYS.DBA$OLAP2_FACTTBLKEYMAPS

Source

View Text - Preformatted

select flu.owner owner,
       flu.cube_name cube_name,
       flu.dim_hier_combo_id dim_hier_combo_id,
       flu.dimension_owner dimension_owner,
       flu.dimension_name dimension_name,
       (case when flu.hidden = 'N' then flu.hierarchy_name
        else null end) hierarchy_name,
       flu.level_name level_name,
       flu.fact_table_owner fact_table_owner,
       flu.fact_table_name fact_table_name,
       flu.column_name column_name,
       flu.data_type column_data_type,
       flu.data_length column_data_length,
       flu.data_precision column_data_precision,
       ftg.column_name gid_column_name,
       ftg.data_type gid_column_data_type,
       ftg.data_length gid_column_data_length,
       ftg.data_precision gid_column_data_precision,
      (CASE WHEN dimension_keymap_type = 'ET' THEN 1
       WHEN dimension_keymap_type = 'LL' THEN 2
       ELSE 0 END) dimension_keymap_type,
       c.mv_summarycode mv_summarycode,
       flu.position column_position
from
(
select
  c.owner owner,
  c.name cube_name,
  d.owner dimension_owner,
  d.name dimension_name,
  null dimension_alias, /* dimension alias */
  h.name hierarchy_name,
  fdhm.irid dim_hier_combo_id,
  l.name level_name,
  u.username fact_table_owner,
  o.name fact_table_name,
  col.name column_name,
  decode(col.type#, 1, decode(col.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                 2, decode(col.scale, null,
                           decode(col.precision#, null, 'NUMBER', 'FLOAT'),
                           'NUMBER'),
                 8, 'LONG',
                 9, decode(col.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                 12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
                 69, 'ROWID',
                 96, decode(col.charsetform, 2, 'NCHAR', 'CHAR'),
                 105, 'MLSLABEL',
                 106, 'MLSLABEL',
                 112, decode(col.charsetform, 2, 'NCLOB', 'CLOB'),
                 113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                 178, 'TIME(' ||col.spare1|| ')',
                 179, 'TIME(' ||col.spare1|| ')' || ' WITH TIME ZONE',
                 180, 'TIMESTAMP(' ||col.spare1|| ')',
                 181, 'TIMESTAMP(' ||col.spare1|| ')' || ' WITH TIME ZONE',
                 182, 'INTERVAL YEAR(' ||col.spare2||') TO MONTH',
                 183, 'INTERVAL DAY(' ||col.spare2||') TO SECOND(' ||
                       col.spare1 || ')',
                 208, 'UROWID',
                 'UNDEFINED') data_type
, decode(col.length, null, 0, col.length) data_length
, decode(col.precision#, null, 0, col.precision#) data_precision,
  fkdhlm.position,
  fkdhm.dimensionkeymaptype dimension_keymap_type,
  null foreign_key_name,
  h.hidden hidden
from olapsys.CwM2$Cube c,
     olapsys.CwM2$Dimension d,
     olapsys.CwM2$Level l,
     olapsys.CwM2$CubeDimensionUse cdu,
     olapsys.CwM2$FactDimHierMap fdhm,
     olapsys.CwM2$FactDimHierTplsDtl fdhtd,
     olapsys.CwM2$FactKeyDimHierMap fkdhm,
     olapsys.CwM2$FactKeyDimHierLvlMap fkdhlm,
     olapsys.CwM2$Hierarchy h,
     sys.obj$ o,
     sys.col$ col,
     dba_users u
where c.irid = cdu.cube_irid and
      d.irid = cdu.dimension_irid and
      l.dimension_irid = d.irid and
      h.dimension_irid = d.irid and
      fdhm.cube_irid = c.irid and
      fkdhm.factDimHier_IRID = fdhm.irid and
      fdhm.irid = fdhtd.factdimhier_irid and
      fkdhlm.factkeyDimHier_IRID = fkdhm.irid and
      fdhm.FactTableName_ID = o.obj# and
      o.owner# = u.user_id and
      fkdhlm.columnName_ID = col.col# and
      o.obj# = col.obj# and
      fkdhlm.level_irid = l.irid and
      fkdhlm.hierarchy_irid = fdhtd.hier_irid and
      fdhtd.hier_irid = h.irid
)
flu,
(
select distinct
  c.owner owner,
  c.name cube_name,
  d.owner dimension_owner,
  d.name dimension_name,
  h.name hierarchy_name,
  fdhm.irid dim_hier_combo_id,
  u.username fact_table_owner,
  o.name fact_table_name,
  col.name column_name,
  decode(col.type#, 1, decode(col.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                 2, decode(col.scale, null,
                           decode(col.precision#, null, 'NUMBER', 'FLOAT'),
                           'NUMBER'),
                 8, 'LONG',
                 9, decode(col.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                 12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
                 69, 'ROWID',
                 96, decode(col.charsetform, 2, 'NCHAR', 'CHAR'),
                 105, 'MLSLABEL',
                 106, 'MLSLABEL',
                 112, decode(col.charsetform, 2, 'NCLOB', 'CLOB'),
                 113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                 178, 'TIME(' ||col.spare1|| ')',
                 179, 'TIME(' ||col.spare1|| ')' || ' WITH TIME ZONE',
                 180, 'TIMESTAMP(' ||col.spare1|| ')',
                 181, 'TIMESTAMP(' ||col.spare1|| ')' || ' WITH TIME ZONE',
                 182, 'INTERVAL YEAR(' ||col.spare2||') TO MONTH',
                 183, 'INTERVAL DAY(' ||col.spare2||') TO SECOND(' ||
                       col.spare1 || ')',
                 208, 'UROWID',
                 'UNDEFINED') data_type
, decode(col.length, null, 0, col.length) data_length
, decode(col.precision#, null, 0, col.precision#) data_precision
from olapsys.CwM2$Cube c,
     olapsys.CwM2$Dimension d,
     olapsys.CwM2$CubeDimensionUse cdu,
     olapsys.CwM2$FactDimHierMap fdhm,
     olapsys.CwM2$FactKeyDimHierMap fkdhm,
     olapsys.cwm2$factdimhiertplsdtl fdhtd,
     olapsys.cwm2$hierarchy h,
     sys.obj$ o,
     sys.col$ col,
     dba_users u
where c.irid = cdu.cube_irid and
      d.irid = cdu.dimension_irid and
      fdhm.cube_irid = c.irid and
      fkdhm.factDimHier_IRID = fdhm.irid and
      fkdhm.Dimension_IRID = d.irid and
      fdhtd.factdimhier_irid = fdhm.irid and
      fdhtd.hier_irid = h.irid and
      h.dimension_irid = d.irid and
      fdhm.FactTableName_ID = o.obj# and
      o.owner# = u.user_id and
      fkdhm.gidcolumnname_id = col.col# and
      o.obj# = col.obj#
)
ftg,
     olapsys.dba$olap2_cubes c
where
flu.dimension_keymap_type <> 'RU'
and flu.owner = ftg.owner (+)
and flu.cube_name = ftg.cube_name (+)
and flu.dimension_owner = ftg.dimension_owner (+)
and flu.dimension_name = ftg.dimension_name (+)
and flu.hierarchy_name = ftg.hierarchy_name (+)
and flu.dim_hier_combo_id = ftg.dim_hier_combo_id (+)
and flu.fact_table_owner = ftg.fact_table_owner (+)
and flu.fact_table_name = ftg.fact_table_name (+)
and flu.owner = c.owner
and flu.cube_name = c.cube_name

View Text - HTML Formatted

SELECT FLU.OWNER OWNER
, FLU.CUBE_NAME CUBE_NAME
, FLU.DIM_HIER_COMBO_ID DIM_HIER_COMBO_ID
, FLU.DIMENSION_OWNER DIMENSION_OWNER
, FLU.DIMENSION_NAME DIMENSION_NAME
, (CASE WHEN FLU.HIDDEN = 'N' THEN FLU.HIERARCHY_NAME ELSE NULL END) HIERARCHY_NAME
, FLU.LEVEL_NAME LEVEL_NAME
, FLU.FACT_TABLE_OWNER FACT_TABLE_OWNER
, FLU.FACT_TABLE_NAME FACT_TABLE_NAME
, FLU.COLUMN_NAME COLUMN_NAME
, FLU.DATA_TYPE COLUMN_DATA_TYPE
, FLU.DATA_LENGTH COLUMN_DATA_LENGTH
, FLU.DATA_PRECISION COLUMN_DATA_PRECISION
, FTG.COLUMN_NAME GID_COLUMN_NAME
, FTG.DATA_TYPE GID_COLUMN_DATA_TYPE
, FTG.DATA_LENGTH GID_COLUMN_DATA_LENGTH
, FTG.DATA_PRECISION GID_COLUMN_DATA_PRECISION
, (CASE WHEN DIMENSION_KEYMAP_TYPE = 'ET' THEN 1 WHEN DIMENSION_KEYMAP_TYPE = 'LL' THEN 2 ELSE 0 END) DIMENSION_KEYMAP_TYPE
, C.MV_SUMMARYCODE MV_SUMMARYCODE
, FLU.POSITION COLUMN_POSITION FROM ( SELECT C.OWNER OWNER
, C.NAME CUBE_NAME
, D.OWNER DIMENSION_OWNER
, D.NAME DIMENSION_NAME
, NULL DIMENSION_ALIAS
, /* DIMENSION ALIAS */ H.NAME HIERARCHY_NAME
, FDHM.IRID DIM_HIER_COMBO_ID
, L.NAME LEVEL_NAME
, U.USERNAME FACT_TABLE_OWNER
, O.NAME FACT_TABLE_NAME
, COL.NAME COLUMN_NAME
, DECODE(COL.TYPE#
, 1
, DECODE(COL.CHARSETFORM
, 2
, 'NVARCHAR2'
, 'VARCHAR2')
, 2
, DECODE(COL.SCALE
, NULL
, DECODE(COL.PRECISION#
, NULL
, 'NUMBER'
, 'FLOAT')
, 'NUMBER')
, 8
, 'LONG'
, 9
, DECODE(COL.CHARSETFORM
, 2
, 'NCHAR VARYING'
, 'VARCHAR')
, 12
, 'DATE'
, 23
, 'RAW'
, 24
, 'LONG RAW'
, 69
, 'ROWID'
, 96
, DECODE(COL.CHARSETFORM
, 2
, 'NCHAR'
, 'CHAR')
, 105
, 'MLSLABEL'
, 106
, 'MLSLABEL'
, 112
, DECODE(COL.CHARSETFORM
, 2
, 'NCLOB'
, 'CLOB')
, 113
, 'BLOB'
, 114
, 'BFILE'
, 115
, 'CFILE'
, 178
, 'TIME(' ||COL.SPARE1|| ')'
, 179
, 'TIME(' ||COL.SPARE1|| ')' || ' WITH TIME ZONE'
, 180
, 'TIMESTAMP(' ||COL.SPARE1|| ')'
, 181
, 'TIMESTAMP(' ||COL.SPARE1|| ')' || ' WITH TIME ZONE'
, 182
, 'INTERVAL YEAR(' ||COL.SPARE2||') TO MONTH'
, 183
, 'INTERVAL DAY(' ||COL.SPARE2||') TO SECOND(' || COL.SPARE1 || ')'
, 208
, 'UROWID'
, 'UNDEFINED') DATA_TYPE
, DECODE(COL.LENGTH
, NULL
, 0
, COL.LENGTH) DATA_LENGTH
, DECODE(COL.PRECISION#
, NULL
, 0
, COL.PRECISION#) DATA_PRECISION
, FKDHLM.POSITION
, FKDHM.DIMENSIONKEYMAPTYPE DIMENSION_KEYMAP_TYPE
, NULL FOREIGN_KEY_NAME
, H.HIDDEN HIDDEN FROM OLAPSYS.CWM2$CUBE C
, OLAPSYS.CWM2$DIMENSION D
, OLAPSYS.CWM2$LEVEL L
, OLAPSYS.CWM2$CUBEDIMENSIONUSE CDU
, OLAPSYS.CWM2$FACTDIMHIERMAP FDHM
, OLAPSYS.CWM2$FACTDIMHIERTPLSDTL FDHTD
, OLAPSYS.CWM2$FACTKEYDIMHIERMAP FKDHM
, OLAPSYS.CWM2$FACTKEYDIMHIERLVLMAP FKDHLM
, OLAPSYS.CWM2$HIERARCHY H
, SYS.OBJ$ O
, SYS.COL$ COL
, DBA_USERS U WHERE C.IRID = CDU.CUBE_IRID AND D.IRID = CDU.DIMENSION_IRID AND L.DIMENSION_IRID = D.IRID AND H.DIMENSION_IRID = D.IRID AND FDHM.CUBE_IRID = C.IRID AND FKDHM.FACTDIMHIER_IRID = FDHM.IRID AND FDHM.IRID = FDHTD.FACTDIMHIER_IRID AND FKDHLM.FACTKEYDIMHIER_IRID = FKDHM.IRID AND FDHM.FACTTABLENAME_ID = O.OBJ# AND O.OWNER# = U.USER_ID AND FKDHLM.COLUMNNAME_ID = COL.COL# AND O.OBJ# = COL.OBJ# AND FKDHLM.LEVEL_IRID = L.IRID AND FKDHLM.HIERARCHY_IRID = FDHTD.HIER_IRID AND FDHTD.HIER_IRID = H.IRID ) FLU
, ( SELECT DISTINCT C.OWNER OWNER
, C.NAME CUBE_NAME
, D.OWNER DIMENSION_OWNER
, D.NAME DIMENSION_NAME
, H.NAME HIERARCHY_NAME
, FDHM.IRID DIM_HIER_COMBO_ID
, U.USERNAME FACT_TABLE_OWNER
, O.NAME FACT_TABLE_NAME
, COL.NAME COLUMN_NAME
, DECODE(COL.TYPE#
, 1
, DECODE(COL.CHARSETFORM
, 2
, 'NVARCHAR2'
, 'VARCHAR2')
, 2
, DECODE(COL.SCALE
, NULL
, DECODE(COL.PRECISION#
, NULL
, 'NUMBER'
, 'FLOAT')
, 'NUMBER')
, 8
, 'LONG'
, 9
, DECODE(COL.CHARSETFORM
, 2
, 'NCHAR VARYING'
, 'VARCHAR')
, 12
, 'DATE'
, 23
, 'RAW'
, 24
, 'LONG RAW'
, 69
, 'ROWID'
, 96
, DECODE(COL.CHARSETFORM
, 2
, 'NCHAR'
, 'CHAR')
, 105
, 'MLSLABEL'
, 106
, 'MLSLABEL'
, 112
, DECODE(COL.CHARSETFORM
, 2
, 'NCLOB'
, 'CLOB')
, 113
, 'BLOB'
, 114
, 'BFILE'
, 115
, 'CFILE'
, 178
, 'TIME(' ||COL.SPARE1|| ')'
, 179
, 'TIME(' ||COL.SPARE1|| ')' || ' WITH TIME ZONE'
, 180
, 'TIMESTAMP(' ||COL.SPARE1|| ')'
, 181
, 'TIMESTAMP(' ||COL.SPARE1|| ')' || ' WITH TIME ZONE'
, 182
, 'INTERVAL YEAR(' ||COL.SPARE2||') TO MONTH'
, 183
, 'INTERVAL DAY(' ||COL.SPARE2||') TO SECOND(' || COL.SPARE1 || ')'
, 208
, 'UROWID'
, 'UNDEFINED') DATA_TYPE
, DECODE(COL.LENGTH
, NULL
, 0
, COL.LENGTH) DATA_LENGTH
, DECODE(COL.PRECISION#
, NULL
, 0
, COL.PRECISION#) DATA_PRECISION FROM OLAPSYS.CWM2$CUBE C
, OLAPSYS.CWM2$DIMENSION D
, OLAPSYS.CWM2$CUBEDIMENSIONUSE CDU
, OLAPSYS.CWM2$FACTDIMHIERMAP FDHM
, OLAPSYS.CWM2$FACTKEYDIMHIERMAP FKDHM
, OLAPSYS.CWM2$FACTDIMHIERTPLSDTL FDHTD
, OLAPSYS.CWM2$HIERARCHY H
, SYS.OBJ$ O
, SYS.COL$ COL
, DBA_USERS U WHERE C.IRID = CDU.CUBE_IRID AND D.IRID = CDU.DIMENSION_IRID AND FDHM.CUBE_IRID = C.IRID AND FKDHM.FACTDIMHIER_IRID = FDHM.IRID AND FKDHM.DIMENSION_IRID = D.IRID AND FDHTD.FACTDIMHIER_IRID = FDHM.IRID AND FDHTD.HIER_IRID = H.IRID AND H.DIMENSION_IRID = D.IRID AND FDHM.FACTTABLENAME_ID = O.OBJ# AND O.OWNER# = U.USER_ID AND FKDHM.GIDCOLUMNNAME_ID = COL.COL# AND O.OBJ# = COL.OBJ# ) FTG
, OLAPSYS.DBA$OLAP2_CUBES C WHERE FLU.DIMENSION_KEYMAP_TYPE <> 'RU' AND FLU.OWNER = FTG.OWNER (+) AND FLU.CUBE_NAME = FTG.CUBE_NAME (+) AND FLU.DIMENSION_OWNER = FTG.DIMENSION_OWNER (+) AND FLU.DIMENSION_NAME = FTG.DIMENSION_NAME (+) AND FLU.HIERARCHY_NAME = FTG.HIERARCHY_NAME (+) AND FLU.DIM_HIER_COMBO_ID = FTG.DIM_HIER_COMBO_ID (+) AND FLU.FACT_TABLE_OWNER = FTG.FACT_TABLE_OWNER (+) AND FLU.FACT_TABLE_NAME = FTG.FACT_TABLE_NAME (+) AND FLU.OWNER = C.OWNER AND FLU.CUBE_NAME = C.CUBE_NAME