VIEW: OLAPSYS.ODM$OLAP2_FACT_LEVEL_USES

Source

View Text - Preformatted

select
  c.owner owner,
  c.name cube_name,
  d.owner dimension_owner,
  d.name dimension_name,
  null dimension_alias, /* dimension alias */
  --(case when h.hidden = 'N'
  --        then h.name else null end) hierarchy_name,
  nvl(h.name ,'NONE') hierarchy_name,  -- ALWAYS RETURN 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
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 and
       (c.invalid = 'N' or c.invalid = 'O') and
       (cwm2$security.fact_table_visible(c.irid) = 'Y'
       OR EXISTS (select null from v$enabledprivs
                  where priv_number in (-47)))
with read only

View Text - HTML Formatted

SELECT C.OWNER OWNER
, C.NAME CUBE_NAME
, D.OWNER DIMENSION_OWNER
, D.NAME DIMENSION_NAME
, NULL DIMENSION_ALIAS
, /* DIMENSION ALIAS */ --(CASE WHEN H.HIDDEN = 'N' -- THEN H.NAME ELSE NULL END) HIERARCHY_NAME
, NVL(H.NAME
, 'NONE') HIERARCHY_NAME
, -- ALWAYS RETURN 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 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 AND (C.INVALID = 'N' OR C.INVALID = 'O') AND (CWM2$SECURITY.FACT_TABLE_VISIBLE(C.IRID) = 'Y' OR EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-47))) WITH READ ONLY