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.
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'';
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_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;