Sign in or 
Share this
Determine Cube Derived Measures Information (11.1.0)
To determine derived cube measures in Oracle Database 11g consider using this script.
set lines 110 pages 1000
col cb for a16 hea CUBE
col measure_name for a30 hea MEASURE
col expression for a51 wrap
bre on owner on cube_name
select owner||'.'||cube_name cb, measure_name, to_char(expression) expression
from all_cube_measures
where measure_type='DERIVED'
order by 1,2,3;
CUBE ........... MEASURE ...................... EXPRESSION
---------------- ------------------------------ ---------------------------------------------------
..
DM.SALES_CUBE .. QUANTITY_3_MONTH_MOVING_AVG .. AVG(DM.SALES_CUBE.QUANTITY)
................................................. OVER HIERARCHY (DM.TIME.CALENDAR BETWEEN 2
................................................... PRECEDING AND CURRENT MEMBER WITHIN LEVEL)
DM.SALES_CUBE .. QUANTITY_3_MONTH_MOVING_MAX .. MAX(DM.SALES_CUBE.QUANTITY)
................................................. OVER HIERARCHY (DM.TIME.CALENDAR BETWEEN 2
................................................... PRECEDING AND CURRENT MEMBER WITHIN LEVEL)
DM.SALES_CUBE .. QUANTITY_PR_PERIOD ........... LAG(DM.SALES_CUBE.QUANTITY, 1)
..................................................OVER HIERARCHY (DM.TIME.CALENDAR)
DM.SALES_CUBE .. QUANTITY_PR_PERIOD_PCT_CHG ... LAG_VARIANCE_PERCENT(DM.SALES_CUBE.QUANTITY, 1)
..................................................OVER HIERARCHY (DM.TIME.CALENDAR)
DM.SALES_CUBE .. QUANTITY_PR_YEAR ............. LAG(DM.SALES_CUBE.QUANTITY, 1)
..................................................OVER HIERARCHY (DM.TIME.CALENDAR BY ANCESTOR
................................................... AT LEVEL DM.TIME.YEAR POSITION FROM BEGINNING)
DM.SALES_CUBE .. QUANTITY_PR_YEAR_PCT_CHG ..... LAG_VARIANCE_PERCENT(DM.SALES_CUBE.QUANTITY, 1)
..................................................OVER HIERARCHY (DM.TIME.CALENDAR BY ANCESTOR
................................................... AT LEVEL DM.TIME.YEAR POSITION FROM BEGINNING)
DM.SALES_CUBE .. QUANTITY_RANK_BY_PRODUCT ..... RANK() OVER HIERARCHY (DM.PRODUCT.STANDARD
..................................................ORDER BY DM.SALES_CUBE.QUANTITY DESC NULLS LAST
................................................. WITHIN PARENT)
...
set lines 110 pages 1000
col cb for a16 hea CUBE
col measure_name for a30 hea MEASURE
col expression for a51 wrap
bre on owner on cube_name
select owner||'.'||cube_name cb, measure_name, to_char(expression) expression
from all_cube_measures
where measure_type='DERIVED'
order by 1,2,3;
CUBE ........... MEASURE ...................... EXPRESSION
---------------- ------------------------------ ---------------------------------------------------
..
DM.SALES_CUBE .. QUANTITY_3_MONTH_MOVING_AVG .. AVG(DM.SALES_CUBE.QUANTITY)
................................................. OVER HIERARCHY (DM.TIME.CALENDAR BETWEEN 2
................................................... PRECEDING AND CURRENT MEMBER WITHIN LEVEL)
DM.SALES_CUBE .. QUANTITY_3_MONTH_MOVING_MAX .. MAX(DM.SALES_CUBE.QUANTITY)
................................................. OVER HIERARCHY (DM.TIME.CALENDAR BETWEEN 2
................................................... PRECEDING AND CURRENT MEMBER WITHIN LEVEL)
DM.SALES_CUBE .. QUANTITY_PR_PERIOD ........... LAG(DM.SALES_CUBE.QUANTITY, 1)
..................................................OVER HIERARCHY (DM.TIME.CALENDAR)
DM.SALES_CUBE .. QUANTITY_PR_PERIOD_PCT_CHG ... LAG_VARIANCE_PERCENT(DM.SALES_CUBE.QUANTITY, 1)
..................................................OVER HIERARCHY (DM.TIME.CALENDAR)
DM.SALES_CUBE .. QUANTITY_PR_YEAR ............. LAG(DM.SALES_CUBE.QUANTITY, 1)
..................................................OVER HIERARCHY (DM.TIME.CALENDAR BY ANCESTOR
................................................... AT LEVEL DM.TIME.YEAR POSITION FROM BEGINNING)
DM.SALES_CUBE .. QUANTITY_PR_YEAR_PCT_CHG ..... LAG_VARIANCE_PERCENT(DM.SALES_CUBE.QUANTITY, 1)
..................................................OVER HIERARCHY (DM.TIME.CALENDAR BY ANCESTOR
................................................... AT LEVEL DM.TIME.YEAR POSITION FROM BEGINNING)
DM.SALES_CUBE .. QUANTITY_RANK_BY_PRODUCT ..... RANK() OVER HIERARCHY (DM.PRODUCT.STANDARD
..................................................ORDER BY DM.SALES_CUBE.QUANTITY DESC NULLS LAST
................................................. WITHIN PARENT)
...
olapdba |
Latest page update: made by olapdba
, Mar 12 2008, 10:08 AM EDT
(about this update
About This Update
Edited by olapdba
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.
