VIEW: SYS.USER_CUBE_HIERARCHIES

Source

View Text - Preformatted

SELECT
   o.name DIMENSION_NAME,
   h.hierarchy_name HIERARCHY_NAME,
   h.hierarchy_id HIERARCHY_ID,
   DECODE(h.hierarchy_type, 1, 'LEVEL', 2, 'VALUE') HIERARCHY_TYPE,
   d.description_value DESCRIPTION,
   (case
     when io.option_num_value is null then 0
     else io.option_num_value
    end) IS_RAGGED,
   (case
     when io2.option_num_value is null then 0
     else io2.option_num_value
    end) IS_SKIP_LEVEL,
   io3.option_value REFRESH_MVIEW_NAME,
   syn.syntax_clob CUSTOM_ORDER
FROM
  olap_hierarchies$ h,
  obj$ o,
  (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 = 13 --HIERARCHY
        and (d.language = n.value
             or d.language like n.value || '\_%' escape '\')) d,
  olap_impl_options$ io,
  olap_impl_options$ io2,
  olap_impl_options$ io3,
  olap_syntax$ syn
WHERE
  h.dim_obj#=o.obj# AND o.owner#=USERENV('SCHEMAID')
  AND d.owning_object_id(+)=h.hierarchy_id
  AND io.object_type(+) = 13 -- HIERARCHY
  AND io.owning_objectid(+) = h.hierarchy_id
  AND io.option_type(+) = 6 -- IS_RAGGED
  AND io2.object_type(+) = 13 -- HIERARCHY
  AND io2.owning_objectid(+) = h.hierarchy_id
  AND io2.option_type(+) = 1 -- IS_SKIP_LEVEL
  AND io3.object_type(+) = 13 -- HIERARCHY
  AND io3.owning_objectid(+) = h.hierarchy_id
  AND io3.option_type(+) = 30 -- refresh MV name
  AND syn.owner_id(+) = h.hierarchy_id
  AND syn.owner_type(+) = 13
  AND syn.ref_role(+) = 23 -- CustomOrder

View Text - HTML Formatted

SELECT O.NAME DIMENSION_NAME
, H.HIERARCHY_NAME HIERARCHY_NAME
, H.HIERARCHY_ID HIERARCHY_ID
, DECODE(H.HIERARCHY_TYPE
, 1
, 'LEVEL'
, 2
, 'VALUE') HIERARCHY_TYPE
, D.DESCRIPTION_VALUE DESCRIPTION
, (CASE WHEN IO.OPTION_NUM_VALUE IS NULL THEN 0 ELSE IO.OPTION_NUM_VALUE END) IS_RAGGED
, (CASE WHEN IO2.OPTION_NUM_VALUE IS NULL THEN 0 ELSE IO2.OPTION_NUM_VALUE END) IS_SKIP_LEVEL
, IO3.OPTION_VALUE REFRESH_MVIEW_NAME
, SYN.SYNTAX_CLOB CUSTOM_ORDER FROM OLAP_HIERARCHIES$ H
, OBJ$ O
, (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 = 13 --HIERARCHY
AND (D.LANGUAGE = N.VALUE OR D.LANGUAGE LIKE N.VALUE || '\_%' ESCAPE '\')) D
, OLAP_IMPL_OPTIONS$ IO
, OLAP_IMPL_OPTIONS$ IO2
, OLAP_IMPL_OPTIONS$ IO3
, OLAP_SYNTAX$ SYN WHERE H.DIM_OBJ#=O.OBJ#
AND O.OWNER#=USERENV('SCHEMAID')
AND D.OWNING_OBJECT_ID(+)=H.HIERARCHY_ID
AND IO.OBJECT_TYPE(+) = 13 -- HIERARCHY
AND IO.OWNING_OBJECTID(+) = H.HIERARCHY_ID
AND IO.OPTION_TYPE(+) = 6 -- IS_RAGGED
AND IO2.OBJECT_TYPE(+) = 13 -- HIERARCHY
AND IO2.OWNING_OBJECTID(+) = H.HIERARCHY_ID
AND IO2.OPTION_TYPE(+) = 1 -- IS_SKIP_LEVEL
AND IO3.OBJECT_TYPE(+) = 13 -- HIERARCHY
AND IO3.OWNING_OBJECTID(+) = H.HIERARCHY_ID
AND IO3.OPTION_TYPE(+) = 30 -- REFRESH MV NAME
AND SYN.OWNER_ID(+) = H.HIERARCHY_ID
AND SYN.OWNER_TYPE(+) = 13
AND SYN.REF_ROLE(+) = 23 -- CUSTOMORDER