Use Enterprise Manager to create OLAP Metadata Objects

Create and populate an analytic workspace (AW)

Documentation: Oracle9i OLAP Developer’s Guide to the OLAP DML

High availability, scalability and reliability

The ability to process complex analytics, forecasts and planning in the same database system that supports your Business Intelligence applications.

Security for all data in the database, including multidimensional data.

Open access to both relational and multidimensional data.

Manually using SQL, PL/SQL and OLAP DML commands

Using Enterprise Manager and Analytic Workspace Manager

Using Oracle Warehouse Builder

Modeled from a Star Schema. It contains a fact table with surrounding tables that help put the fact data within a context.

Fact data is contained within each cell of the cube. The contextual data runs along the edges. Multidimensional cubes are stored in an Analytic Workspace (AW). A particular AW can contain more than one cube.

A LOB which is a multidimensional data type, and it is stored in a column in a relational table. Some of the multidimensional objects in an AW represent metadata and contain metadata values. Other objects contain multidimensional data values.

Data about data. It contains both logical descriptions and physical mappings. The logical description of data includes information about dimensions, levels, hierarchies, attributes and relationships. Mappings associate the logical descriptions to the physical location of the data. Metadata information is stored in the OLAP Catalog. The AW also contains metadata.

Edit mksh1.sql and loadaw.sql to make sure the drive and directories are set correctly.

You will need to create a subset of the SH schema so that you can practice creating some dimensions and a cube in Enterprise Manager. Move the scripts mksh1.sql, sh1_main.sql and sh1_pop3.sql to your d:\oracle\ora92\demo\schema\sales_history directory. Open a DOS prompt and execute the following commands:

d:
cd oracle\ora92\demo\schema\sales_history\
set oracle_sid=orcl
sqlplus /nolog
@mksh1

You will need to give SH and SH1 the privileges necessary to log into Enterprise Manager. Execute the following script:

@d:\wkdir\grantpriv

You will need to load an analytic workspace so that you can create a view later in this lesson. Execute the following script:

@d:\wkdir\loadaw

Create the PRODUCTS Dimension

Select Start > Programs > Oracle - OraHome92 > Enterprise Manager Console. Select Launch Standalone and click OK.

Expand your database ORCL.WORLD and login as SH1. Expand Warehouse then OLAP and right-click on Dimensions. Select Create using Wizard...

At the welcome window, click Next.

Make sure Create a dimension object is selected and click Next.

Enter PRODUCTS_DIM for the dimension name, make sure the SH1 schema is selected from the list and click Next.

Enter the name CATEGORY, make sure the type Normal, the SH1 Schema, and the Table PRODUCTS are selected. Select the PROD_CATEGORY column from the Available Columns list and click > to move it to the Select Columns list and click the New button.

Enter the name PRODUCT, make sure the type Normal, the SH1 Schema, and the Table PRODUCTS are selected. Select the PROD_ID column from the Available Columns list and click > to move it to the Select Columns list and click the New button.

Enter the name SUBCATEGORY, make sure the type Normal, the SH1 Schema, and the Table PRODUCTS are selected. Select the PROD_SUBCATEGORY column from the Available Columns list and click > to move it to the Select Columns list and click Next.

Click Next.

You will now define the hierarchies for your dimension. Click New.

Enter the Name PROD_ROLLUP. Then select CATEGORY and click > to move it to the Selected Levels list, then select SUCATEGORY and click >. Notice that it is a sublevel of CATEGORY. Then select PRODUCT then >. Then click Next.

Click Next.

Click Finish to create the dimension.

Click OK.

Right-click again on Dimensions and select Create using Wizard...

At the welcome window, click Next.

Select Create a Time dimension object and click Next.

Enter TIMES_DIM for the dimension name, select the SH1 schema from the list and click Next.

Select the Year level. Make sure the SH1 schema, and the TIMES table are selected. Select the CALENDAR_YEAR from the Available Columns list and click > to move it to the Selected Columns list. Then select the Quarter level.

With the Quarter level selected, make sure the SH1 schema, and the TIMES table are selected. Select the CALENDAR_QUARTER_DESC from the Available Columns list and click > to move it to the Selected Columns list. Then select the Month level.

With the Month level selected, make sure the SH1 schema, and the TIMES table are selected. Select the CALENDAR_MONTH_DESC from the Available Columns list and click > to move it to the Selected Columns list. Then select the Day level.

With the Day level selected, make sure the SH1 schema, and the TIMES table are selected. Select the TIME_ID from the Available Columns list and click > to move it to the Selected Columns list. Then click New.

Enter the name FIS_MONTH, make sure the SH1 schema, and the TIMES table are selected. Select the FISCAL_MONTH_DESC from the Available Columns list and click > to move it to the Selected Columns list. Then click New.

Enter the name FIS_QUARTER, make sure the SH1 schema, and the TIMES table are selected. Select the FISCAL_QUARTER_DESC from the Available Columns list and click > to move it to the Selected Columns list. Then click New.

Enter the name FIS_WEEK, make sure the SH1 schema, and the TIMES table are selected. Select the WEEK_ENDING_DAY from the Available Columns list and click > to move it to the Selected Columns list. Then click New.

Enter the name FIS_YEAR, make sure the SH1 schema, and the TIMES table are selected. Select the FISCAL_YEAR from the Available Columns list and click > to move it to the Selected Columns list. Then click Next.

At the Define Attributes window, select Long_Description, select FIS_MONTH from the list of Select Levels and click < to deselect it. Do the same for FIS_WEEK and Year. Then select Short_Description.

Deselect FIS_WEEK, FIS_YEAR and Year from the list of Select Levels and click < to deselect them. Then click Next.

At the Define Hierarchies window, select Calendar and change the name to CAL_ROLLUP. Select Year from the list of Available Levels and click > to select it. Then select Quarter, Month and Day and select them. Then select the Fiscal hierarchy.

Change the name to FIS_ROLLUP and select the following levels in the following order: FIS_YEAR, FIS_QUARTER, FIS_MONTH, FIS_WEEK, Day. Then click Next.

Click Next.

Click Finish to create the TIMES dimension.

Click OK.

Right-click on Cubes and select Create using Wizard...

At the welcome window, click Next.

Enter the name COST_CUBE, a display name of Cost Analysis, select the SH1 schema and enter the description Unit Cost, Unit Price <TIMES PRODUCTS> then click Next.

You will need to select a Fact table. Expand SH1 then select COSTS and click > to move it to the Select table list and click Next.

Shift-select the PRODUCTS_DIM and TIMES_DIM dimensions and click > to move them to the selected list and click Next.

With the PRODUCTS_DIM dimension(alias) selected, select the FKey Col in Fact Table as PROD_ID. Note, select the field then select the drop down list box and select the column from the list. Then select the TIMES_DIM dimension.

Select the TIME_ID from the drop down list box and click Next. Note: if your Next button is not active, select PRODUCTS_DIM again to make sure your FKey Col is PROD_ID. Both must be specified before the Next button becomes active.

At the Specify Measures window, select UNIT_COST and enter the description Unit Cost Amount, then select UNIT_PRICE.

Enter the description Unit Price Amount and click Next.

At the Summary screen you can view the SQL. Click Show SQL.

This SQL will be executed when you click Finish on the previous window. Click OK.

To see the cube graphically, click Graphical View. Click OK when you are done reviewing the graphic view.

Deselect Launch Summary Advisor Wizard to optimize the cube and click Finish.

Click OK.

You create an object type, a table on that object type and then create the COST_BASE_VIEW view itself. From a SQL*Plus session, execute the following script:

@createview

set serveroutput on
connect sh/sh@orcl.world
execute DBMS_AW.EXECUTE('AW ATTACH cost_aw RW')
execute DBMS_AW.EXECUTE('CDA AW')
execute DBMS_AW.EXECUTE('INFILE limitselect.inf')
execute DBMS_AW.EXECUTE('UPDATE')
execute DBMS_AW.EXECUTE('COMMIT')
/
DROP view cost_base_view
/
DROP type cost_base
/
DROP type cost_base_type
/
CREATE OR REPLACE TYPE cost_base_type AS OBJECT (
   time_id 	VARCHAR2(20),
   time_parent 	VARCHAR2(20),
   product_id   VARCHAR2(40),
   cost         NUMBER)
/
CREATE OR REPLACE TYPE cost_base AS TABLE OF cost_base_type
/
CREATE OR REPLACE VIEW cost_base_view AS
   SELECT * FROM TABLE
	 (OLAP_TABLE
	  ('cost_aw DURATION session',
	   'cost_BASE',
	   '',
	   'DIMENSION time_id FROM sh_times_dim 
	      WITH HIERARCHY time_parent FROM sh_times_dim_member_parentrel
 	    DIMENSION product_id FROM sh_products_dim
	    MEASURE cost FROM sh_cost_cube_unit_cost
      LOOP sh_cost_cube_composite
      PREDMLCMD ''limit_select'''))
   WHERE cost IS NOT NULL

/
COMMIT;

In this case, the DBMS_AW package is used to execute import limitselect.inf which contains a series of OLAP DML Commands. The selectlimit.inf file contains the following:

AW ATTACH COST_AW RW

DELETE LIMIT_SELECT

DEFINE LIMIT_SELECT PROGRAM
PROGRAM
limit sh_products_dim to sh_products_dim_member_levelrel 'PRODUCT'
limit sh_times_dim_hierlist to 'CAL_ROLLUP'
limit sh_times_dim to sh_times_dim_member_levelrel 'DAY'
END

Once the view is created with the imbedded table function, it exists as a persistent SQL object. When a SELECT is issued against the view, SQL first processes the table function, which executes the fetch against the AW based on the information contained within it. In this case you specified that the LIMIT_SELECT program be run in the AW before the data is retrieved by the OLAP engine and passed to the SQL engine. Once the data is returned by the table function, the SQL engine then processes the predicates, and the view is populated with the results.

From a SQL*Plus session, execute the following script:

@showolapview

set serveroutput on
connect sh/sh@orcl.world
column product_id format a20
/
column time_id format a20
/
select time_id, product_id, cost from cost_base_view
where product_id = 'PRODUCT.20'
and time_parent = 'MONTH.1998-01';

The result of this query shows the cost of a particular product in January 1998 is $7.80. As you can see, the price did not fluctuate.

Use Enterprise Manager to create OLAP Metadata Objects

Create and populate an analytic workspace (AW)