Process Overview

This chapter primarily describes a tool for creating analytic workspaces. The OLAP Catalog metadata defines a logical analysis model and a map to a physical source. If you cannot create OLAP Catalog metadata because your database does not conform to its requirements, you can still create an analytic workspace manually. The basic steps that parallel the stages performed by the CNV_CWM.TO.ECM program are also described in this chapter.

The process that you will follow to create an analytic workspace from relational tables is contingent on the design of your schema.

If you have a star or snowflake schema, then you can use the tools described in "Creating an Analytic Workspace Using the CNV_CWM.TO.ECM Program".

Otherwise, follow the instructions in "Manually Creating an Analytic Workspace".

See Also: Oracle9i OLAP Developer's Guide to the OLAP DML for information about OLAP Worksheet, creating analytic workspaces, aggregating data, and other multidimensional data manipulation techniques.

Creating an Analytic Workspace Using the CNV_CWM.TO.ECM Program

The following steps describe how you might approach the task of creating an analytic workspace using the CNV_CWM.TO.ECM program.

Note: To run the GENSQLOBJS program, which writes a SQL file, you must have read/write access to a directory alias, as described in "Controlling Access to External Files".

1. If you have not yet created OLAP metadata, then you must do that first, as described in Chapter 5, "Creating OLAP Catalog Metadata". The metadata maps the fact tables and dimension tables of your database to multidimensional objects: measures, dimensions, attributes, levels, and so forth. These object types are defined in Chapter 4, "Designing Your Database for OLAP".
2. If you plan to fetch only part of your relational schema into your analytic workspace, then identify exactly which objects you want to use.
3. Open an OLAP session using one of the methods described in "Methods of Executing OLAP DML Commands".
4. Create and detach an analytic workspace. Unless you have strong reasons for doing otherwise, you should begin with an empty workspace. The following command creates an analytic workspace named sales in the olapts tablespace:

   AW CREATE sales TABLESPACE olapts
   AW DETACH sales
   
   

   CNV_CWM.TO.ECM will create an analytic workspace if one does not exist already, but it will be created in the default tablespace. The performance of an analytic workspace is better if it is stored in a tablespace that has been created specifically for that purpose.

5. Execute CNV_CWM.TO.ECM.

   The following example attaches an analytic workspace named sales and creates the dimensions, attributes, and hierarchies associated with the SALES_QUANTITY measure.

   CALL CNV_CWM.TO.ECM('sales' 'na' 'na' -
   

            'MEASURE::SH::SALES_CUBE::SALES_QUANTITY')
   
   

   See "CNV_CWM.TO.ECM Program" for the complete syntax.

   The CNV_CWM.TO.ECM utility loads only the base-level data stored in relational tables. It does not load any aggregate data.

6. Create aggregation maps and generate aggregate data by using the AGGREGATE command.

   You now have an analytic workspace. SQL-based applications can use the OLAP_TABLE function for direct access to the data.

7. Run the GENSQLOBJS program to generate SQL scripts for creating relational views of the analytic workspace data. (Optional)
8. Generate relational views of the multidimensional data by running the SQL scripts. (Optional)

   SQL-based applications can run directly against these views using standard SQL commands, and thus have access to the workspace data.

9. You can now create OLAP catalog metadata if you wish, so that the OLAP API can access the multidimensional data in the analytic workspace. Refer to Chapter 5, "Creating OLAP Catalog Metadata". (Optional)

Manually Creating an Analytic Workspace

If your database design does not allow you to use the CNV_CWM.TO.ECM program, then you can still develop an analytic workspace from relational tables. However, you will need to use the various programs and procedures that underlie CNV_CWM.TO.ECM. Their use requires greater familiarity with the OLAP DML.

See Also: Oracle9i OLAP Developer's Guide to the OLAP DML for information about creating analytic workspaces using the SQL command in the OLAP DML.

The following are the basic steps.

1. Browse your database schema and identify the names of the tables and columns whose data you want to fetch into an analytic workspace. Determine which columns will be used as measures, dimensions, and attributes. Refer to Chapter 4, "Designing Your Database for OLAP" for descriptions of multidimensional objects.
2. Open an OLAP session, using one of the methods described in "Methods of Executing OLAP DML Commands"
3. Create an analytic workspace or attach an existing workspace. The following command creates an analytic workspace named sales in the olapts tablespace:

   AW CREATE sales TABLESPACE olapts
   
   

4. Define the workspace objects in which you will store the data by using the DEFINE command.
5. Fetch data from the relational tables into workspace objects by using the SQL FETCH or the SQL IMPORT commands.
6. Create aggregation maps and generate aggregate data by using the AGGREGATE command.

   You now have an analytic workspace. SQL-based applications can use the OLAP_TABLE function for direct access to the data.

7. Generate relational views of the multidimensional data by using the CWM2_OLAP_AW_ACCESS PL/SQL package, as described in Chapter 15, "CWM2_OLAP_AW_ACCESS". (Optional)

   SQL-based applications can run directly against these views using standard SQL commands, and thus have access to the workspace data.

8. You can now create OLAP catalog metadata so that the OLAP API can access the workspace data, as described in Chapter 5, "Creating OLAP Catalog Metadata". (Optional)