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

Related Topics
Built-in Functions
Built-in Packages
Date Functions
Data Mining Functions
Miscellaneous Functions
Numeric Functions
String Functions
Timestamp
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx