Sign in or 
| Version | User | Scope of changes |
|---|---|---|
| Mar 12 2008, 10:08 AM EDT (current) | olapdba | |
| Jan 9 2008, 1:47 PM EST | olapdba | 1 word added |
Changes
Key: Additions Deletions
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)
...
