Oracle | OLAP Functionality

Oracle OLAP Functions Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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.

This page does not replicate Oracle's definition of OLAP functions which includes only CUBE_TABLE. This page includes other functions that are clearly intended for use with OLAP statements.

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

CUBE_TABLE

Extracts data from a cube or dimension and returns it in the two-dimensional format of a relational table See Data Mining Functions

OLAPIBOOTSTRAP2

Undocumented olapibootstrap2( drierversion IN VARCHAR2, ret OUT VARCHAR2, serverversion OUT VARCHAR2) RETURN NUMBER;

TBD

OLAPRC_TABLE

Undocumented olaprc_table( cache_key IN NUMBER, column_map IN VARCHAR2) RETURN XMLTYPE;

TBD

OLAP_BOOL_SRF

Undocumented olap_bool_srf( R2C IN RAW, EXP IN VARCHAR2, IA IN sys.odciindexctx, CTX IN OUT sys.olap_srf_t, SFLG IN NUMBER) RETURN NUMBER;

TBD

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))

          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_DATE_SRF

Undocumented olap_date_srf( R2C IN RAW, EXP IN VARCHAR2, IA IN sys.odciindexctx, CTX IN OUT sys.olap_srf_t, SFLG IN NUMBER) RETURN DATE;

TBD

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_NUMBER_SRF

Undocumented olap_number_srf( olap_date_srf( R2C IN RAW, EXP IN VARCHAR2, IA IN sys.odciindexctx, CTX IN OUT sys.olap_srf_t, SFLG IN NUMBER) 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 ('2018', '2019', '2020', '2021')

      ORDER BY aw_time;

OLAP_TEXT_SRF

Undocumented olap_text_srf( olap_date_srf( R2C IN RAW, EXP IN VARCHAR2, IA IN sys.odciindexctx, CTX IN OUT sys.olap_srf_t, SFLG IN NUMBER) RETURN VARCHAR2;

TBD