VIEW: OLAPSYS.DBA$OLAP2UDIM_LEVEL_ATTR_MAPS

Source

View Text - Preformatted

select attr.dim_owner owner,
       attr.dim_name dimension_name,
       attr.hierarchy_name hierarchy_name,
       attr.dim_attribute_name attribute_name,
       attr.lvl_attribute_name lvl_attribute_name,
       attr.levelname level_name,
       attr.col_owner table_owner,
       attr.col_table table_name,
       attr.col_name column_name,
       attr.dtype dtype,
       attr.data_length data_length,
       attr.data_precision data_precision,
 (case when attr.dtype = 'NUMBER' then 0
              when attr.dtype = 'DOUBLE' then 5
              when attr.dtype = 'FLOAT' then 4
              when attr.dtype = 'DATE' then 7
              when attr.dtype = 'LONG' then 3
              else 1 end) olap_api_data_type
from
     (select
         u.username                  dim_owner,
         o.name                  dim_name,
         h.hiername              hierarchy_name,
         dat.physicalname        dim_attribute_name,
         nvl(lat.name, c.name)   lvl_attribute_name,
         l.levelname             levelname,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                 2, decode(c.scale, null,
                           decode(c.precision#, null, 'NUMBER', 'FLOAT'),
                           'NUMBER'),
                 8, 'LONG',
                 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                 12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
                 69, 'ROWID',
                 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
                 105, 'MLSLABEL',
                 106, 'MLSLABEL',
                 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
                 113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                 178, 'TIME(' ||c.spare1|| ')',
                 179, 'TIME(' ||c.spare1|| ')' || ' WITH TIME ZONE',
                 180, 'TIMESTAMP(' ||c.spare1|| ')',
                 181, 'TIMESTAMP(' ||c.spare1|| ')' || ' WITH TIME ZONE',
                 182, 'INTERVAL YEAR(' ||c.spare2||') TO MONTH',
                 183, 'INTERVAL DAY(' ||c.spare2||') TO SECOND(' ||
                       c.spare1 || ')',
                 208, 'UROWID',
                 'UNDEFINED') dtype
, decode(c.length, null, 0, c.length) data_length
, decode(c.precision#, null, 0, c.precision#) data_precision,
         cu.username                 col_owner,
         ct.name                 col_table,
         c.name                  col_name
      from
         dba_users               u,
         sys.obj$                o,
         sys.dim$                d,
         sys.dimlevel$           l,
         sys.dimattr$            a,
         sys.hierlevel$          hl,
         sys.hier$               h,
         sys.col$                c,
         sys.obj$                ct,
         dba_users               cu,
         cwm$levelattribute      lat,
         cwm$level               lvl,
         cwm$itemuse             iu1,
         cwm$itemuse             iu2,
         cwm$dimensionattribute  dat
      where u.user_id = o.owner# and
            o.type# = 43 and
            o.obj# = d.obj# and
            d.obj# = l.dimobj# and
            d.obj# = dat.itemcontainer_irid and
            d.obj# = h.dimobj# and
            d.obj# = hl.dimobj# and
            h.hierid# = hl.hierid# and
            hl.levelid# = l.levelid# and
            l.dimobj# = a.dimobj# and
            l.levelid# = a.levelid# and
            a.detailobj# = c.obj# and
            a.col# = c.col# and
            c.obj# = ct.obj# and
            ct.owner# = cu.user_id and
            c.obj# = lat.type_irid and
            c.name = lat.physicalname and
            lvl.dimension_irid = l.dimobj# and
            lat.itemcontainer_irid = lvl.irid and
            lat.irid = iu2.mappable_irid and
            iu2.operation_irid_1 = iu1.operation_irid and
            iu1.mappable_irid = dat.irid
     ) attr
union all
select attr1.dim_owner owner,
       attr1.dim_name dimension_name,
       null hierarchy_name,
       attr1.dim_attribute_name attribute_name,
       attr1.lvl_attribute_name lvl_attribute_name,
       attr1.levelname level_name,
       attr1.col_owner table_owner,
       attr1.col_table table_name,
       attr1.col_name column_name,
       attr1.dtype dtype,
       attr1.data_length data_length,
       attr1.data_precision data_precision,
 (case when attr1.dtype = 'NUMBER' then 0
              when attr1.dtype = 'DOUBLE' then 5
              when attr1.dtype = 'FLOAT' then 4
              when attr1.dtype = 'DATE' then 7
              when attr1.dtype = 'LONG' then 3
              else 1 end) olap_api_data_type
from
     (select
         u.username                  dim_owner,
         o.name                  dim_name,
         dat.physicalname        dim_attribute_name,
         nvl(lat.name, c.name)   lvl_attribute_name,
         l.levelname             levelname,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                 2, decode(c.scale, null,
                           decode(c.precision#, null, 'NUMBER', 'FLOAT'),
                           'NUMBER'),
                 8, 'LONG',
                 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                 12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
                 69, 'ROWID',
                 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
                 105, 'MLSLABEL',
                 106, 'MLSLABEL',
                 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
                 113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                 178, 'TIME(' ||c.spare1|| ')',
                 179, 'TIME(' ||c.spare1|| ')' || ' WITH TIME ZONE',
                 180, 'TIMESTAMP(' ||c.spare1|| ')',
                 181, 'TIMESTAMP(' ||c.spare1|| ')' || ' WITH TIME ZONE',
                 182, 'INTERVAL YEAR(' ||c.spare2||') TO MONTH',
                 183, 'INTERVAL DAY(' ||c.spare2||') TO SECOND(' ||
                       c.spare1 || ')',
                 208, 'UROWID',
                 'UNDEFINED') dtype
, decode(c.length, null, 0, c.length) data_length
, decode(c.precision#, null, 0, c.precision#) data_precision,
         cu.username                 col_owner,
         ct.name                 col_table,
         c.name                  col_name,
         l.dimobj#               l_dimobj,
         l.levelid#              l_levelid
      from
         dba_users               u,
         sys.obj$                o,
         sys.dim$                d,
         sys.dimlevel$           l,
         sys.dimattr$            a,
         sys.col$                c,
         sys.obj$                ct,
         dba_users               cu,
         cwm$levelattribute      lat,
         cwm$level               lvl,
         cwm$itemuse             iu1,
         cwm$itemuse             iu2,
         cwm$dimensionattribute  dat
      where u.user_id = o.owner# and
            o.type# = 43 and
            o.obj# = d.obj# and
            d.obj# = l.dimobj# and
            d.obj# = dat.itemcontainer_irid and
            l.dimobj# = a.dimobj# and
            l.levelid# = a.levelid# and
            a.detailobj# = c.obj# and
            a.col# = c.col# and
            c.obj# = ct.obj# and
            ct.owner# = cu.user_id and
            c.obj# = lat.type_irid and
            c.name = lat.physicalname and
            lvl.dimension_irid = l.dimobj# and
            lat.itemcontainer_irid = lvl.irid and
            lat.irid = iu2.mappable_irid and
            iu2.operation_irid_1 = iu1.operation_irid and
            iu1.mappable_irid = dat.irid
     ) attr1,
     (select dl1.dimobj# l_dimobj,
             dl1.levelid# l_levelid
       from sys.dimlevel$ dl1
       where to_char(dl1.dimobj#) || '_' || to_char(dl1.levelid#) not in
        (select to_char(hl.dimobj#) || '_' || to_char(hl.levelid#)
         from sys.hierlevel$ hl)
     ) nohierlvl
     where nohierlvl.l_dimobj = attr1.l_dimobj and
           nohierlvl.l_levelid = attr1.l_levelid
union all
select owner, dimension_name, hierarchy_name, attribute_name,
       lvl_attribute_name, level_name,
       table_owner, table_name, column_name, dtype, data_length,
       data_precision, olap_api_data_type
from olapsys.dba$olap2_dim_level_attr_maps
with read only

View Text - HTML Formatted

SELECT ATTR.DIM_OWNER OWNER
, ATTR.DIM_NAME DIMENSION_NAME
, ATTR.HIERARCHY_NAME HIERARCHY_NAME
, ATTR.DIM_ATTRIBUTE_NAME ATTRIBUTE_NAME
, ATTR.LVL_ATTRIBUTE_NAME LVL_ATTRIBUTE_NAME
, ATTR.LEVELNAME LEVEL_NAME
, ATTR.COL_OWNER TABLE_OWNER
, ATTR.COL_TABLE TABLE_NAME
, ATTR.COL_NAME COLUMN_NAME
, ATTR.DTYPE DTYPE
, ATTR.DATA_LENGTH DATA_LENGTH
, ATTR.DATA_PRECISION DATA_PRECISION
, (CASE WHEN ATTR.DTYPE = 'NUMBER' THEN 0 WHEN ATTR.DTYPE = 'DOUBLE' THEN 5 WHEN ATTR.DTYPE = 'FLOAT' THEN 4 WHEN ATTR.DTYPE = 'DATE' THEN 7 WHEN ATTR.DTYPE = 'LONG' THEN 3 ELSE 1 END) OLAP_API_DATA_TYPE FROM (SELECT U.USERNAME DIM_OWNER
, O.NAME DIM_NAME
, H.HIERNAME HIERARCHY_NAME
, DAT.PHYSICALNAME DIM_ATTRIBUTE_NAME
, NVL(LAT.NAME
, C.NAME) LVL_ATTRIBUTE_NAME
, L.LEVELNAME LEVELNAME
, DECODE(C.TYPE#
, 1
, DECODE(C.CHARSETFORM
, 2
, 'NVARCHAR2'
, 'VARCHAR2')
, 2
, DECODE(C.SCALE
, NULL
, DECODE(C.PRECISION#
, NULL
, 'NUMBER'
, 'FLOAT')
, 'NUMBER')
, 8
, 'LONG'
, 9
, DECODE(C.CHARSETFORM
, 2
, 'NCHAR VARYING'
, 'VARCHAR')
, 12
, 'DATE'
, 23
, 'RAW'
, 24
, 'LONG RAW'
, 69
, 'ROWID'
, 96
, DECODE(C.CHARSETFORM
, 2
, 'NCHAR'
, 'CHAR')
, 105
, 'MLSLABEL'
, 106
, 'MLSLABEL'
, 112
, DECODE(C.CHARSETFORM
, 2
, 'NCLOB'
, 'CLOB')
, 113
, 'BLOB'
, 114
, 'BFILE'
, 115
, 'CFILE'
, 178
, 'TIME(' ||C.SPARE1|| ')'
, 179
, 'TIME(' ||C.SPARE1|| ')' || ' WITH TIME ZONE'
, 180
, 'TIMESTAMP(' ||C.SPARE1|| ')'
, 181
, 'TIMESTAMP(' ||C.SPARE1|| ')' || ' WITH TIME ZONE'
, 182
, 'INTERVAL YEAR(' ||C.SPARE2||') TO MONTH'
, 183
, 'INTERVAL DAY(' ||C.SPARE2||') TO SECOND(' || C.SPARE1 || ')'
, 208
, 'UROWID'
, 'UNDEFINED') DTYPE
, DECODE(C.LENGTH
, NULL
, 0
, C.LENGTH) DATA_LENGTH
, DECODE(C.PRECISION#
, NULL
, 0
, C.PRECISION#) DATA_PRECISION
, CU.USERNAME COL_OWNER
, CT.NAME COL_TABLE
, C.NAME COL_NAME FROM DBA_USERS U
, SYS.OBJ$ O
, SYS.DIM$ D
, SYS.DIMLEVEL$ L
, SYS.DIMATTR$ A
, SYS.HIERLEVEL$ HL
, SYS.HIER$ H
, SYS.COL$ C
, SYS.OBJ$ CT
, DBA_USERS CU
, CWM$LEVELATTRIBUTE LAT
, CWM$LEVEL LVL
, CWM$ITEMUSE IU1
, CWM$ITEMUSE IU2
, CWM$DIMENSIONATTRIBUTE DAT
WHERE U.USER_ID = O.OWNER# AND O.TYPE# = 43 AND O.OBJ# = D.OBJ# AND D.OBJ# = L.DIMOBJ# AND D.OBJ# = DAT.ITEMCONTAINER_IRID AND D.OBJ# = H.DIMOBJ# AND D.OBJ# = HL.DIMOBJ# AND H.HIERID# = HL.HIERID# AND HL.LEVELID# = L.LEVELID# AND L.DIMOBJ# = A.DIMOBJ# AND L.LEVELID# = A.LEVELID# AND A.DETAILOBJ# = C.OBJ# AND A.COL# = C.COL# AND C.OBJ# = CT.OBJ# AND CT.OWNER# = CU.USER_ID AND C.OBJ# = LAT.TYPE_IRID AND C.NAME = LAT.PHYSICALNAME AND LVL.DIMENSION_IRID = L.DIMOBJ# AND LAT.ITEMCONTAINER_IRID = LVL.IRID AND LAT.IRID = IU2.MAPPABLE_IRID AND IU2.OPERATION_IRID_1 = IU1.OPERATION_IRID AND IU1.MAPPABLE_IRID = DAT.IRID ) ATTR UNION ALL SELECT ATTR1.DIM_OWNER OWNER
, ATTR1.DIM_NAME DIMENSION_NAME
, NULL HIERARCHY_NAME
, ATTR1.DIM_ATTRIBUTE_NAME ATTRIBUTE_NAME
, ATTR1.LVL_ATTRIBUTE_NAME LVL_ATTRIBUTE_NAME
, ATTR1.LEVELNAME LEVEL_NAME
, ATTR1.COL_OWNER TABLE_OWNER
, ATTR1.COL_TABLE TABLE_NAME
, ATTR1.COL_NAME COLUMN_NAME
, ATTR1.DTYPE DTYPE
, ATTR1.DATA_LENGTH DATA_LENGTH
, ATTR1.DATA_PRECISION DATA_PRECISION
, (CASE WHEN ATTR1.DTYPE = 'NUMBER' THEN 0 WHEN ATTR1.DTYPE = 'DOUBLE' THEN 5 WHEN ATTR1.DTYPE = 'FLOAT' THEN 4 WHEN ATTR1.DTYPE = 'DATE' THEN 7 WHEN ATTR1.DTYPE = 'LONG' THEN 3 ELSE 1 END) OLAP_API_DATA_TYPE FROM (SELECT U.USERNAME DIM_OWNER
, O.NAME DIM_NAME
, DAT.PHYSICALNAME DIM_ATTRIBUTE_NAME
, NVL(LAT.NAME
, C.NAME) LVL_ATTRIBUTE_NAME
, L.LEVELNAME LEVELNAME
, DECODE(C.TYPE#
, 1
, DECODE(C.CHARSETFORM
, 2
, 'NVARCHAR2'
, 'VARCHAR2')
, 2
, DECODE(C.SCALE
, NULL
, DECODE(C.PRECISION#
, NULL
, 'NUMBER'
, 'FLOAT')
, 'NUMBER')
, 8
, 'LONG'
, 9
, DECODE(C.CHARSETFORM
, 2
, 'NCHAR VARYING'
, 'VARCHAR')
, 12
, 'DATE'
, 23
, 'RAW'
, 24
, 'LONG RAW'
, 69
, 'ROWID'
, 96
, DECODE(C.CHARSETFORM
, 2
, 'NCHAR'
, 'CHAR')
, 105
, 'MLSLABEL'
, 106
, 'MLSLABEL'
, 112
, DECODE(C.CHARSETFORM
, 2
, 'NCLOB'
, 'CLOB')
, 113
, 'BLOB'
, 114
, 'BFILE'
, 115
, 'CFILE'
, 178
, 'TIME(' ||C.SPARE1|| ')'
, 179
, 'TIME(' ||C.SPARE1|| ')' || ' WITH TIME ZONE'
, 180
, 'TIMESTAMP(' ||C.SPARE1|| ')'
, 181
, 'TIMESTAMP(' ||C.SPARE1|| ')' || ' WITH TIME ZONE'
, 182
, 'INTERVAL YEAR(' ||C.SPARE2||') TO MONTH'
, 183
, 'INTERVAL DAY(' ||C.SPARE2||') TO SECOND(' || C.SPARE1 || ')'
, 208
, 'UROWID'
, 'UNDEFINED') DTYPE
, DECODE(C.LENGTH
, NULL
, 0
, C.LENGTH) DATA_LENGTH
, DECODE(C.PRECISION#
, NULL
, 0
, C.PRECISION#) DATA_PRECISION
, CU.USERNAME COL_OWNER
, CT.NAME COL_TABLE
, C.NAME COL_NAME
, L.DIMOBJ# L_DIMOBJ
, L.LEVELID# L_LEVELID FROM DBA_USERS U
, SYS.OBJ$ O
, SYS.DIM$ D
, SYS.DIMLEVEL$ L
, SYS.DIMATTR$ A
, SYS.COL$ C
, SYS.OBJ$ CT
, DBA_USERS CU
, CWM$LEVELATTRIBUTE LAT
, CWM$LEVEL LVL
, CWM$ITEMUSE IU1
, CWM$ITEMUSE IU2
, CWM$DIMENSIONATTRIBUTE DAT
WHERE U.USER_ID = O.OWNER# AND O.TYPE# = 43 AND O.OBJ# = D.OBJ# AND D.OBJ# = L.DIMOBJ# AND D.OBJ# = DAT.ITEMCONTAINER_IRID AND L.DIMOBJ# = A.DIMOBJ# AND L.LEVELID# = A.LEVELID# AND A.DETAILOBJ# = C.OBJ# AND A.COL# = C.COL# AND C.OBJ# = CT.OBJ# AND CT.OWNER# = CU.USER_ID AND C.OBJ# = LAT.TYPE_IRID AND C.NAME = LAT.PHYSICALNAME AND LVL.DIMENSION_IRID = L.DIMOBJ# AND LAT.ITEMCONTAINER_IRID = LVL.IRID AND LAT.IRID = IU2.MAPPABLE_IRID AND IU2.OPERATION_IRID_1 = IU1.OPERATION_IRID AND IU1.MAPPABLE_IRID = DAT.IRID ) ATTR1
, (SELECT DL1.DIMOBJ# L_DIMOBJ
, DL1.LEVELID# L_LEVELID
FROM SYS.DIMLEVEL$ DL1
WHERE TO_CHAR(DL1.DIMOBJ#) || '_' || TO_CHAR(DL1.LEVELID#) NOT IN (SELECT TO_CHAR(HL.DIMOBJ#) || '_' || TO_CHAR(HL.LEVELID#)
FROM SYS.HIERLEVEL$ HL) ) NOHIERLVL
WHERE NOHIERLVL.L_DIMOBJ = ATTR1.L_DIMOBJ AND NOHIERLVL.L_LEVELID = ATTR1.L_LEVELID UNION ALL SELECT OWNER
, DIMENSION_NAME
, HIERARCHY_NAME
, ATTRIBUTE_NAME
, LVL_ATTRIBUTE_NAME
, LEVEL_NAME
, TABLE_OWNER
, TABLE_NAME
, COLUMN_NAME
, DTYPE
, DATA_LENGTH
, DATA_PRECISION
, OLAP_API_DATA_TYPE FROM OLAPSYS.DBA$OLAP2_DIM_LEVEL_ATTR_MAPS WITH READ ONLY