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