Oracle OLAP Functions
Version 11.2.0.3
 
Have you seen the Functions page? If not ... Click Here ... for information on all Oracle functions
 
Note: The following SQL functions that you can use to extract multidimensional data from an analytic workspace and present it in the two-dimensional format of a relational table.
 
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_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;
 
 
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-2014 Daniel A. Morgan All Rights Reserved