VIEW: SYS.USER_CUBE_DIMENSIONS

Source

View Text - Preformatted

SELECT
  o.name DIMENSION_NAME,
  dim.obj# DIMENSION_ID,
  DECODE(dim.dimension_type,1, 'STANDARD',
                            2, 'TIME',
                            3, 'LINEITEM',
                            4, 'MEASURE',
                            5, 'LANGUAGE',
                            6, 'FINANCIAL_ELEMENT',
                            7, 'SPATIAL') DIMENSION_TYPE,
  a.awname AW_NAME,
  h.hierarchy_name DEFAULT_HIERARCHY_NAME,
  d.description_value DESCRIPTION,
  io.option_value HIERARCHY_CONSISTENCY_RULE,
  DECODE(io2.option_num_value, 1, 'YES', 'NO') ADD_UNIQUE_KEY_PREFIX,
  syn.syntax_clob CUSTOM_ORDER
FROM
   olap_cube_dimensions$ dim,
   aw$ a,
   obj$ o,
   olap_hierarchies$ h,
   olap_syntax$ syn,
  (select d.* from olap_descriptions$ d, nls_session_parameters n where
	n.parameter = 'NLS_LANGUAGE'
	and d.description_type = 'Description'
	and d.owning_object_type = 11 --DIMENSION
	and (d.language = n.value
             or d.language like n.value || '\_%' escape '\')) d,
  olap_impl_options$ io, -- For HIERARCHY_CONSISTENCY_RULE
  olap_impl_options$ io2 -- For ADD_UNIQUE_KEY_PREFIX
WHERE
   o.obj#=dim.obj# AND o.owner#=USERENV('SCHEMAID')
   AND dim.awseq#=a.awseq#(+)
   AND h.hierarchy_id(+)=dim.default_hierarchy_id
   AND d.owning_object_id(+)=dim.obj#
   AND io.object_type(+) = 11 -- DIMENSION
   AND io.owning_objectid(+) = dim.obj#
   AND io.option_type(+) = 26 -- Hierarchy Consistency Rule
   AND dim.obj# = io2.owning_objectid(+)
   AND io2.object_type(+) = 11 -- DIMENSION
   AND io2.option_type(+) = 29 -- Add_Unique_Key_Prefix
   AND syn.owner_id(+) = dim.obj#
   AND syn.owner_type(+) = 11
   AND syn.ref_role(+) = 23 -- CustomOrder

View Text - HTML Formatted

SELECT O.NAME DIMENSION_NAME
, DIM.OBJ# DIMENSION_ID
, DECODE(DIM.DIMENSION_TYPE
, 1
, 'STANDARD'
, 2
, 'TIME'
, 3
, 'LINEITEM'
, 4
, 'MEASURE'
, 5
, 'LANGUAGE'
, 6
, 'FINANCIAL_ELEMENT'
, 7
, 'SPATIAL') DIMENSION_TYPE
, A.AWNAME AW_NAME
, H.HIERARCHY_NAME DEFAULT_HIERARCHY_NAME
, D.DESCRIPTION_VALUE DESCRIPTION
, IO.OPTION_VALUE HIERARCHY_CONSISTENCY_RULE
, DECODE(IO2.OPTION_NUM_VALUE
, 1
, 'YES'
, 'NO') ADD_UNIQUE_KEY_PREFIX
, SYN.SYNTAX_CLOB CUSTOM_ORDER FROM OLAP_CUBE_DIMENSIONS$ DIM
, AW$ A
, OBJ$ O
, OLAP_HIERARCHIES$ H
, OLAP_SYNTAX$ SYN
, (SELECT D.*
FROM OLAP_DESCRIPTIONS$ D
, NLS_SESSION_PARAMETERS N WHERE N.PARAMETER = 'NLS_LANGUAGE' AND D.DESCRIPTION_TYPE = 'DESCRIPTION' AND D.OWNING_OBJECT_TYPE = 11 --DIMENSION AND (D.LANGUAGE = N.VALUE OR D.LANGUAGE LIKE N.VALUE || '\_%' ESCAPE '\')) D
, OLAP_IMPL_OPTIONS$ IO
, -- FOR HIERARCHY_CONSISTENCY_RULE OLAP_IMPL_OPTIONS$ IO2 -- FOR ADD_UNIQUE_KEY_PREFIX WHERE O.OBJ#=DIM.OBJ#
AND O.OWNER#=USERENV('SCHEMAID')
AND DIM.AWSEQ#=A.AWSEQ#(+)
AND H.HIERARCHY_ID(+)=DIM.DEFAULT_HIERARCHY_ID
AND D.OWNING_OBJECT_ID(+)=DIM.OBJ#
AND IO.OBJECT_TYPE(+) = 11 -- DIMENSION
AND IO.OWNING_OBJECTID(+) = DIM.OBJ#
AND IO.OPTION_TYPE(+) = 26 -- HIERARCHY CONSISTENCY RULE
AND DIM.OBJ# = IO2.OWNING_OBJECTID(+)
AND IO2.OBJECT_TYPE(+) = 11 -- DIMENSION
AND IO2.OPTION_TYPE(+) = 29 -- ADD_UNIQUE_KEY_PREFIX
AND SYN.OWNER_ID(+) = DIM.OBJ#
AND SYN.OWNER_TYPE(+) = 11
AND SYN.REF_ROLE(+) = 23 -- CUSTOMORDER