Oracle OLAP Functions
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Have you viewed the Library's main Table of Contents? If not ... [Click Here] ... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
Purpose OLAP functions can be usde to extract multidimensional data from an analytic workspace and present it in the two-dimensional format of a relational table.
Dependencies

DBMS_AW

OLAP_CALCULATED_MEMBERS$ OLAP_MEASURE_FOLDERS$
DBMS_XDS

OLAP_CONDITION

OLAP_MEAS_FOLDER_CONTENTS$
GENOLAPIEXCEPTION OLAP_CUBES$ OLAP_METADATA_DEPENDENCIES$
GV_$AW_OLAP OLAP_CUBE_BUILD_PROCESSES$ OLAP_METADATA_PROPERTIES$
KU$_XSOLAP_POLICY_T OLAP_CUBE_DIMENSIONS$ OLAP_MODELS$
KU$_XSOLAP_POLICY_VIEW OLAP_DATE_SRF OLAP_MODEL_ASSIGNMENTS$
OLAPIBOOTSTRAP2 OLAP_DESCRIPTIONS$ OLAP_MODEL_PARENTS$
OLAPIHANDSHAKE2 OLAP_DIMENSIONALITY$ OLAP_MULTI_OPTIONS$
OLAPIMPL_T OLAP_DIM_LEVELS$ OLAP_NUMBER_SRF
OLAPRANCURIMPL_T OLAP_EXPRESSION OLAP_SRF_T
OLAPRC_TABLE OLAP_EXPRESSION_BOOL OLAP_SYNTAX$
OLAP_ATTRIBUTES$ OLAP_EXPRESSION_DATE OLAP_TAB$
OLAP_ATTRIBUTE_VISIBILITY$ OLAP_EXPRESSION_TEXT

OLAP_TABLE

OLAP_AW_DEPLOYMENT_CONTROLS$ OLAP_HIERARCHIES$ OLAP_TAB_COL$
OLAP_AW_VIEWS$ OLAP_HIER_LEVELS$ OLAP_TAB_HIER$
OLAP_AW_VIEW_COLUMNS$ OLAP_IMPL_OPTIONS$ OLAP_TEXT_SRF
OLAP_BOOL_SRF OLAP_MAPPINGS$ V_$AW_OLAP
OLAPRANCURIMPL_T OLAP_MEASURES$ XS$OLAP_POLICY
 
OLAP_CONDITION
Executes an OLAP DML command at one of three entry points in the limit map used in a call to OLAP_TABLE OLAP_CONDITION(
r2c        IN RAW(32),
expression IN VARCHAR2,
event      IN NUMBER DEFAULT 1);
RETURN NUMBER;
SELECT TRUNC(IND-TO_DATE('01011900','ddmmyyyy')+2),V1,V2,V3
FROM (
  SELECT *
  FROM TABLE(OLAP_TABLE( 'OLAPFAME.MAG_CRD DURATION SESSION', '', '',
    'DIMENSION IND AS DATE FROM DAY
    MEASURE V1 AS NUMBER FROM AW_EXPR DAILY.DATA(SERIES STATVAL(SERIES, 1))
    MEASURE V2 AS NUMBER FROM AW_EXPR DAILY.DATA(SERIES STATVAL(SERIES, 2))
    MEASURE V3 AS NUMBER FROM AW_EXPR DAILY.DATA(SERIES STATVAL(SERIES, 3))
nbsp;   ROW2CELL R2C1' ))
    MODEL DIMENSION BY(IND)
    MEASURES(V1,V2,V3,R2C1)
    RULES UPDATE SEQUENTIAL ORDER())
WHERE OLAP_CONDITION( R2C1, 'LIMIT SERIES TO ''CREDIT.MYSERIES1'',
  ''CREDIT.MYSERIES2'', ''CREDIT.MYSERIES3'';
 
OLAP_EXPRESSION
Dynamically executes an OLAP DML boolean expression within the context of an OLAP_TABLE function OLAP_EXPRESSION(r2c IN RAW(32), numeric_expression IN VARCHAR2) RETURN NUMBER;
TBD
 
OLAP_EXPRESSION_BOOL
Dynamically executes an OLAP DML boolean expression within the context of an OLAP_TABLE function OLAP_EXPRESSION_BOOL(r2c IN RAW(32), boolean_expression IN VARCHAR2) RETURN NUMBER;
TBD
 
OLAP_EXPRESSION_DATE
Dynamically executes an OLAP DML date expression within the context of an OLAP_TABLE function OLAP_EXPRESSION_DATE(r2c IN RAW(32), date_expression IN VARCHAR2) RETURN NUMBER;
TBD
 
OLAP_EXPRESSION_TEXT
Dynamically executes an OLAP DML string  expression within the context of an OLAP_TABLE function OLAP_EXPRESSION_TEXT(LAP_EXPRESSION_TEXT(r2c IN RAW(32), text_expression IN VARCHAR2)
RETURN NUMBER;
TBD
 
OLAP_TABLE
Returns a table of objects that can be joined to relational tables and views, and to other tables of objects populated by OLAP_TABLE

This demo comes from Oracle's online documentation which is inadequate in that it does not provide the DDL to create the MYAW analytic workspace. I will try to obtain and post it.
OLAP_TABLE(OLAP_TABLE(
analytic_workspace IN VARCHAR2,
table_object       IN VARCHAR2,
olap_command       IN VARCHAR2,
limit_map1         IN VARCHAR2,
limit_map2         IN VARCHAR2,
.
.
.
limit_map8         IN VARCHAR2)
RETURN TYPE;
CREATE TYPE unit_cost_price_row AS OBJECT (
aw_unit_cost   NUMBER,
aw_unit_price  NUMBER,
aw_product     VARCHAR2(50),
aw_product_gid NUMBER(10),
aw_time        VARCHAR2(20),
aw_time_gid    NUMBER(10),
r2c            RAW(32));
/

CREATE TYPE unit_cost_price_table AS TABLE OF unit_cost_price_row;
/

CREATE OR REPLACE VIEW unit_cost_price_view AS
SELECT aw_unit_cost, aw_unit_price, aw_product, aw_product_gid,
aw_time, aw_time_gid, r2c
FROM TABLE(OLAP_TABLE('myaw DURATION SESSION', 'unit_cost_price_table', '',
 'MEASURE aw_unit_cost FROM price_cube_unit_cost
 MEASURE aw_unit_price FROM price_cube_unit_price
 DIMENSION product WITH HIERARCHY product_parentrel
 INHIERARCHY product_inhier GID aw_product_gid FROM product_gid
 ATTRIBUTE aw_product FROM product_short_description
 DIMENSION time WITH HIERARCHY time_parentrel
 INHIERARCHY time_inhier GID aw_time_gid FROM time_gid
ATTRIBUTE aw_time FROM time_short_description ROW2CELL r2c'));

SELECT * FROM unit_cost_price_view
WHERE aw_product = 'Hardware'
AND aw_time in ('2000', '2001', '2002', '2003')
ORDER BY aw_time;

Related Topics
Built-in Functions
Built-in Packages
Date Functions
Data Mining Functions
Miscellaneous Functions
Numeric Functions
String Functions
Timestamp
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved