Oracle9i OLAP User's Guide Release 2 (9.2) Part Number A95295-01 |
|
OLAP_TABLE Function, 6 of 6
In the discussion on working with relational tables in Oracle9i OLAP Developer's Guide to the OLAP DML there is an example of creating an analytic workspace named awsh
from the sample Sales History database. Assume that you want to create relational views of the cost data in the awsh
analytic workspace
For cost data, the Sales History database (which is fully described in Oracle9i Sample Schemas) has a fact table named costs
that contains columns for its keys (product_id
and time_id
) and columns for facts (unit_cost
and unit_price
). The keys of costs
are primary keys of the products
dimension table and the times
dimension table. The products
table represents one hierarchy with four levels (prod_id
, prod_subcategory
, prod_category
, and products_all
). The times
table represents two hierarchies: Calendar Time and Fiscal. The Calendar Time hierarchy has five levels: date, calendar week, calendar month, calendar quarter, and calendar year. The Fiscal Time hierarchy has five levels: date, fiscal week, fiscal month, fiscal quarter, and fiscal year.
In the awsh
analytic workspace, the Products and Times hierarchies are defined as a number of analytic workspace objects as illustrated in Example 11-1, "Definitions for Cost Data in the awsh Analytic Workspace". For the Products hierarchy, there is a dimension for each level of a hierarchy, a concat dimension named aw_products
for all of the levels, and a child-parent self-relation for the concat dimension. When designing awsh
, it was determined that applications only need to summarize or aggregate data from at the year level. Consequently, there are only three levels in the Time hierarchies in the analytic workspace: time id, fiscal year, and calendar year. To define for the Time hierarchies, there is an analytic workspace dimension containing the names of the two hierarchies (Calendar and Fiscal), a dimensions for each of the three levels (time, fiscal year, and calendar year), a concat dimension named aw_times
for all of the levels, and a child-parent self-relation for the concat dimension. Since there are two time hierarchies the child-parent self-relation created for aw_times
is dimensioned by both the concat dimension and the hierarchies (by name). The facts in the costs table are defined as analytic workspace variables dimensioned by a composite (named aw_costsdims
) of aw_products
and aw_times
.
To prepare awsh
for access by OLAP_TABLE
, you need to add the analytic workspace object definitions in Example 11-2, "Definitions for Additional Analytic Workspace Objects". Example 11-3, "Preparing the awsh Analytic Workspace for OLAP_TABLE" illustrates an OLAP DML program that populates these analytic workspace objects using the HEIRHEIGHT
command, the GROUPINGID
command, and other OLAP DML commands.
After all of the necessary analytic workspace objects are defined and populated you can use of the OLAP_TABLE
function to create a relational view of the cost data in awsh
. Example 11-4, "Creating Views Using the OLAP_TABLE Function" is a script that creates these views.
aw_products_view
is a view of the Products hierarchy. It includes a column for all of the values in the hierarchy, a column for the parent of each of the values in the hierarchy, columns for the values of each level in the hierarchy and, a column for the grouping id of each value in the hierarchy. The columns that contain the values of the levels are mapped to analytic workspace data using the LEVELREL
clause of the limit-map
parameter of the OLAP_TABLE
function.aw_times_view
is a view of the Times hierarchies. It includes a column for all of the values in the hierarchy, a column for the parent of each values in the hierarchy, and columns for the values of each level in the hierarchy. The columns that contain the values of the levels are mapped to analytic workspace data using the ATTRIBUTE
phrases in the limit-map
parameter of the OLAP_TABLE
function. Each ATTRIBUTE
phrase maps a column in the view to a analytic workspace formula that retrieves the values of one level of the aw_times
dimension.aw_costs_view
is a view of the actual cost facts (unit_price
and unit_cost
) and the analytic workspace dimensions that act as the keys to these facts.DEFINE aw_prod_id DIMENSION NUMBER (6) DEFINE aw_prod_subcategory DIMENSION TEXT DEFINE aw_prod_category DIMENSION TEXT DEFINE aw_products_all DIMENSION TEXT DEFINE aw_products DIMENSION CONCAT (aw_products_all - aw_prod_category - aw_prod_subcategory - aw_prod_id) DEFINE aw_products.parents RELATION aw_products <aw_products> DEFINE aw_time_id DIMENSION TEXT DEFINE aw_cal_year DIMENSION NUMBER(4) DEFINE aw_fis_year DIMENSION NUMBER(4) DEFINE aw_times DIMENSION CONCAT (aw_cal_year - aw_fis_year - aw_time_id) DEFINE aw_times_hiernames DIMENSION TEXT DEFINE aw_times.parents RELATION aw_times <aw_times aw_times_hiernames> DEFINE aw_costsdims COMPOSITE <aw_products aw_times> DEFINE aw_unit_cost VARIABLE NUMBER (10,2) <aw_costsdims - <aw_products aw_times>> DEFINE aw_unit_price VARIABLE NUMBER (10,2) <aw_costsdims - <aw_products aw_times>>
DEFINE temp_levelnames DIMENSION TEXT LD A dimension used to sort names of levels DEFINE aw_products_levelnames DIMENSION TEXT LD Names of levels of the Products hierarchy DEFINE aw_products_levelnums DIMENSION INTEGER LD Levels of the Products hierarchy identified by number DEFINE aw_times_levelnames DIMENSION TEXT LD Names of levels of the Times hierarchy DEFINE aw_times_levelnums DIMENSION INTEGER LD Levels of the Times hierarchy identified by number DEFINE aw_products_gid VARIABLE INTEGER <aw_products> LD Levels of the Products hierarchy identified by GID DEFINE aw_products.aw_products_levelnums RELATION aw_products <aw_products aw_products_levelnums> LD Concat dimension values for the Products hierarchy by level number DEFINE aw_times.aw_times_levelnums RELATION aw_times <aw_times aw_times_hiernames aw_times_levelnums> LD Concat dimension values for the Times hierarchy by level number and hierarchy DEFINE aw_products_basevalues VARIABLE aw_products <aw_products aw_products_levelnums> DEFINE AW_FISCAL_YEAR_FORM FORMULA - BASEVAL(aw_times.aw_times_levelnums(aw_times_levelnums 2 - aw_times_hiernames 'Fiscal')) LD Formula that returns the base values of fiscal year DEFINE AW_CALENDAR_YEAR_FORM FORMULA - BASEVAL(aw_times.aw_times_levelnums(aw_times_levelnums 1 - aw_times_hiernames 'Calendar')) LD Formula that returns the base values of calendar year DEFINE AW_TIMEID_FORM FORMULA - BASEVAL(aw_times.aw_times_levelnums(aw_times_levelnums 3 - aw_times_hiernames 'Calendar')) LD Formula that returns the values of time_id
" Initialize the levelnames dimension for aw_products " Initialize the temp_levelnames dimension MAINTAIN temp_levelnames DELETE ALL " Populate temp_levelnames with the lowercase names of " the base dimensions of aw_products in the order they were defined " which is top level (world) has position 1 MAINTAIN temp_levelnames ADD LOWCASE(OBJ(DATA 'aw_products')) " Sort levelnames so that bottom level (prod_id) " has position 1 SORT temp_levelnames A temp_levelnames " Populate aw_products_levelnames with sorted levelnames MAINTAIN aw_products_levelnames ADD VALUES (temp_levelnames) " Populate aw_products_levelnums with integers reprsenting levels " value of 1 represents bottom level MAINTAIN aw_products_levelnums ADD STATLEN (aw_products_levelnames) " Initialize the levelnames dimension for aw_times " Initialize the temp_levelnames dimension MAINTAIN temp_levelnames DELETE ALL " Populate temp_levelnames with the lowercase names of " the base dimensions of aw_times in the order they were defined MAINTAIN temp_levelnames ADD LOWCASE(OBJ(DATA 'aw_times')) " Sort levelnames so that bottom level has position 1 SORT temp_levelnames A temp_levelnames " Populate aw_times_levelnames with sorted levelnames MAINTAIN aw_times_levelnames ADD VALUES (temp_levelnames) " Populate aw_products_levelnums with integers reprsenting levels " value of 1 represents bottom level MAINTAIN aw_times_levelnums ADD STATLEN (aw_times_levelnames) " Populate aw_products_gid with grouping ids " for levels of products GROUPINGID aw_products.parents INTO aw_products_gid " Populate relation with concat dimension values for the Products hierarchy HIERHEIGHT aw_products.parents INTO aw_products.aw_products_levelnums " Populate relation with concat dimension values for the Times hierarchy HIERHEIGHT aw_times.parents INTO aw_times.aw_times_levelnums " Populate variable with base dimension values for the Products hierarchy aw_products_basevals = BASEVAL(aw_products.aw_products_levelnums) " Populate variable with base dimension values for the Times hierarchy aw_times_basevals = BASEVAL(aw_times.aw_times_levelnums) "Update the analytic workspace and make changes permanent UPDATE COMMIT
AW CONNECT / as sysdba SET ECHO ON SET SERVEROUT ON DROP TYPE aw_products_tbl; DROP TYPE aw_products_obj; DROP TYPE aw_times_tbl; DROP TYPE aw_times_obj; DROP TYPE aw_costs_tbl; DROP TYPE aw_costs_obj; -- Create objects and tables -- Define an object that identifies the columns for product data CREATE TYPE aw_products_obj AS OBJECT ( prod_hier_value VARCHAR2(35), prod_hier_parent VARCHAR2(35), prod_id VARCHAR2(10), prod_subcategory VARCHAR2(20), prod_category VARCHAR2(5), prod_all VARCHAR2(15), prod_hier_gid NUMBER(10)); -- Define an object that identifies the columns for times data CREATE TYPE aw_times_obj AS OBJECT ( time_hier_value VARCHAR2(20), time_hier_parent VARCHAR2(20), calendar_year NUMBER(4,0), fiscal_year NUMBER(4,0), date_id VARCHAR2(10)); -- Define an object that identifies the columns for cost data CREATE TYPE aw_costs_obj AS OBJECT ( prod_hier_value VARCHAR2(35), prod_hier_parent VARCHAR2(35), time_hier_value VARCHAR2(20), time_hier_parent VARCHAR2(20), unit_cost NUMBER(10,2), unit_price NUMBER(10,2)); -- Define a table of objects for products data CREATE TYPE aw_products_tbl AS TABLE OF aw_products_obj; -- Define a table of objects for times data CREATE TYPE aw_times_tbl AS TABLE OF aw_times_obj; -- Define a table of objects for cost data CREATE TYPE aw_costs_tbl AS TABLE OF aw_costs_obj; -- Define a view of products data CREATE OR REPLACE VIEW aw_products_view AS SELECT * FROM TABLE (CAST (OLAP_TABLE ( 'awsh duration session', 'aw_products_tbl', '', 'DIMENSION prod_hier_value FROM aw_products WITH HIERARCHY prod_hier_parent FROM aw_products.parents INHIERARCHY aw_products_inhier GID prod_hier_gid FROM aw_products_gid LEVELREL prod_id, prod_subcategory, prod_category, prod_all FROM aw_products_basevalues USING aw_products_levelnums') AS aw_products_tbl)); -- Define a view of times data CREATE OR REPLACE VIEW aw_times_view AS SELECT * FROM TABLE (CAST (OLAP_TABLE ( 'awsh duration session', 'aw_times_tbl', '', 'DIMENSION time_hier_value FROM aw_times WITH HIERARCHY time_hier_parent FROM aw_times.parents ATTRIBUTE calendar_year FROM aw_calendar_year_form ATTRIBUTE fiscal_year FROM aw_fiscal_year_form ATTRIBUTE date_id FROM aw_timeid_form ') AS aw_times_tbl)); -- Define a view of cost data CREATE OR REPLACE VIEW aw_costs_view AS SELECT * FROM TABLE (CAST (OLAP_TABLE ( 'awsh duration session', 'aw_costs_tbl', '', 'MEASURE unit_cost FROM aw_unit_cost MEASURE unit_price FROM aw_unit_price DIMENSION prod_hier_value FROM aw_products WITH HIERARCHY prod_hier_parent FROM aw_products.parents DIMENSION time_hier_value FROM aw_times WITH HIERARCHY time_hier_parent FROM aw_times.parents') AS aw_costs_tbl)); -- Grant selection rights to the views GRANT SELECT ON aw_products_view TO PUBLIC; GRANT SELECT ON aw_times_view TO PUBLIC; GRANT SELECT ON aw_costs_view TO PUBLIC; -- Define a view of sales data CREATE OR REPLACE VIEW olap_sales_view AS SELECT * FROM TABLE(OLAP_TABLE('XADEMO DURATION SESSION', 'XASALES_T', '', 'MEASURE sales FROM aw_f.sales DIMENSION et_chan FROM aw_channel WITH HIERARCHY aw_channel.parent GID gid_chan FROM aw_channel.gid DIMENSION et_prod FROM aw_product WITH HIERARCHY aw_product.parent GID gid_prod FROM aw_prod.gid DIMENSION et_geog FROM aw_geography WITH HIERARCHY aw_geography.parent GID gid_geog FROM aw_geog.gid DIMENSION et_time FROM aw_time WITH HIERARCHY time.parent GID gid_time FROM aw_time.gid'));
|
Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|