Sign in or 
Share this
Determine Dimensional Hierarchy Levels Information (11.1.0)
To determine dimensional hierarchy levels in Oracle Database 11g consider using this script.
set lines 110 pages 1000
col dmn for a20 hea DIMENSION
col hierarchy_name for a20 hea HIERARCHY
col level_name for a25 hea LEVEL
break on dmn on hierarchy_name skip 1
select owner||'.'||dimension_name dmn
, hierarchy_name
, '('||order_num||') '||level_name level_name
from all_cube_hier_levels
order by 1,2,order_num;
DIMENSION .... HIERARCHY ....... LEVEL
-------------- ----------------- ---------------------
DM.CHANNEL.... SALES_CHANNEL.... (0) TOTAL_CHANNEL
................................ (1) CLASS
................................ (2) CHANNEL
DM.CUSTOMER... REGIONAL ........ (0) TOTAL_CUSTOMER
................................ (1) CONTINENT
................................ (2) COUNTRY
................................ (3) STATE_PROVINCE
................................ (4) CITY
DM.PRODUCT ....STANDARD .........(0) TOTAL_PRODUCT
................................ (1) DEPARTMENT
................................ (2) GROUPS
................................ (3) TYPE
................................ (4) SUBTYPE
................................ (5) ITEM
DM.TIME .......CALENDAR .........(0) ALL_YEARS
................................ (1) YEAR
................................ (2) QUARTER
................................ (3) MONTH
...............FISCAL ...........(0) ALL_YEARS
................................ (1) FISCAL_YEAR
................................ (2) FISCAL_QUARTER
................................ (3) MONTH
set lines 110 pages 1000
col dmn for a20 hea DIMENSION
col hierarchy_name for a20 hea HIERARCHY
col level_name for a25 hea LEVEL
break on dmn on hierarchy_name skip 1
select owner||'.'||dimension_name dmn
, hierarchy_name
, '('||order_num||') '||level_name level_name
from all_cube_hier_levels
order by 1,2,order_num;
DIMENSION .... HIERARCHY ....... LEVEL
-------------- ----------------- ---------------------
DM.CHANNEL.... SALES_CHANNEL.... (0) TOTAL_CHANNEL
................................ (1) CLASS
................................ (2) CHANNEL
DM.CUSTOMER... REGIONAL ........ (0) TOTAL_CUSTOMER
................................ (1) CONTINENT
................................ (2) COUNTRY
................................ (3) STATE_PROVINCE
................................ (4) CITY
DM.PRODUCT ....STANDARD .........(0) TOTAL_PRODUCT
................................ (1) DEPARTMENT
................................ (2) GROUPS
................................ (3) TYPE
................................ (4) SUBTYPE
................................ (5) ITEM
DM.TIME .......CALENDAR .........(0) ALL_YEARS
................................ (1) YEAR
................................ (2) QUARTER
................................ (3) MONTH
...............FISCAL ...........(0) ALL_YEARS
................................ (1) FISCAL_YEAR
................................ (2) FISCAL_QUARTER
................................ (3) MONTH
olapdba |
Latest page update: made by olapdba
, Oct 26 2009, 10:53 AM EDT
(about this update
About This Update
Edited by olapdba
4 words added 2 words deleted view changes - complete history) |
|
Keyword tags:
None
More Info: links to this page
|
There are no threads for this page.
Be the first to start a new thread.
