How to Manually Create a Standard Form Analytic Workspace - OLAP

The steps that you take to create a standard form analytic workspace from alternative sources is basically the same as from a star or snowflake schema. There are two primary differences:

  • Instead of loading your data into the star or snowflake schema of a data warehouse, you simply create the empty tables. These tables provide the basis for defining the OLAP Catalog metadata required by the DBMS_AWM package to create an analytic workspace. The data remains in its original form until it is loaded directly into an analytic workspace.
  • The DBMS_AWM package uses characteristics of the data in the initial load to make default choices such as dimension order and segment size. Since there is no data from which it can make appropriate choices, you must specify the correct values. Using the DBMS_AWM package directly provides you with the most control. However, you can still use Analytic Workspace Manager or Oracle Warehouse Builder if you wish, and modify the results where necessary before loading the data.

Take these steps to generate a standard form analytic workspace from flat files, relational tables, or an Express database with no Oracle Express Objects metadata. (If you are converting an Oracle Express Objects database, skip these instructions and go to Appendix B.)

  1. Identify the dimensions, attributes, measures, and cubes for your data, and use this information to design a star schema.
    You can use pencil and paper, a database design software package, or any other method that suites you.
  2. Implement your design by creating the dimension tables and fact tables. You can issue SQL CREATE TABLE statements directly in SQL*Plus, or use a graphical interface such as Oracle Enterprise Manager to create the tables. Note that you are creating the tables, but not populating them.
  3. Create OLAP Catalog metadata for the star schema.
    Use any of these methods for creating either CWM1 or CWM2 metadata: the OLAP Management tool in Oracle Enterprise Manager; the OLAP Bridge in Oracle Warehouse Builder; or the CWM2 PL/SQL package.
  4. Create a standard form analytic workspace from the OLAP Catalog metadata. Use any of these methods: the Create Analytic Workspace wizard in Analytic Workspace Manager; the OLAP Bridge in Oracle Warehouse Builder; or the DBMS_AWM PL/SQL package. Specify a full load, even though the tables do not contain data, so that all catalogs are populated correctly in the analytic workspace.
  5. Review the analytic workspace and make any changes to the object definitions.
    In particular, look at the dimension order for composites and data variables, and set an appropriate segment size on the target variables.
  6. Load data into the dimensions, relations, and variables of the standard form analytic workspace.
  7. Make any additional changes to the workspace metadata.

You now have a standard form analytic workspace, and you can use any of the tools for aggregation and deployment provided for standard form workspaces. However, you must refresh the data using whatever OLAP DML programs you created for that purpose.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

OLAP Topics