Global Sample Schema for Oracle® OLAP 11g

The Global sample schema is used by the examples in the following OLAP documentation for Oracle OLAP 11g Release 1 (11.1) and Oracle OLAP 11g Release 2 (11.2).

Oracle OLAP User's Guide
Oracle OLAP Java API Developer's Guide
Oracle OLAP Java API Reference

These documents are in the Online Analytical Processing (OLAP) section of the Oracle Database Documentation Library.

For the 11g Release 1 (11.1) documentation, see OLAP Documentation for 11.1.
For the 11g Release 2 (11.2) documentation, see OLAP Documentation for 11.2.

The Global schema contains eight tables. Examples in the Oracle OLAP documentation create OLAP dimensions, attributes, cubes, and measures and map them to columns in the relational tables.

For information on using Analytic Workspace Manager to define an OLAP dimensional data model and create an analytic workspace, see Oracle OLAP User's Guide. For information on defining an OLAP dimensional data model and creating analytic workspaces using the OLAP Java API, see Oracle OLAP Java API Developer's Guide.

Prerequisites

For access to the OLAP option, you need the Enterprise Edition of the Oracle Database 11g release that you are using.

For the Oracle OLAP User's Guide documents, you also need Analytic Workspace Manager 11g for the release that you are using.

To develop programs using the OLAP Java API, you also need a JDK or IDE, such as JDeveloper, that is compatible with the release that you are using. For those prerequisites, see the Oracle OLAP Java API Developer's Guide or Oracle OLAP Java API Reference documents.

Sample Kit Contents

The sample kit is in the compressed file global_11g_schema.zip. The sample kit includes:

A script that installs the Global schema, global_11g_install.sql. This script does the following:
- Prompts for a password for the GLOBAL user.
- Creates the GLOBAL user and grants to GLOBAL the privileges required to create and use analytic workspaces.
- Connects as the GLOBAL user.
- Imports the tables and data of the schema.
A script that creates the GLOBAL analytic workspace in the GLOBAL schema, global_11g_create_cubes.sql. This script does the following:
- Prompts for the file system directory where the installation files are found. This file system directory is used in the definition of an Oracle directory object.
- Prompts for a GLOBAL user password
- Connects as the GLOBAL user
- Drops the GLOBAL analytic workspace if it already exists
- Creates the GLOBAL analytic workspace, including all the cubes and dimensions. Views and materialized views are created over the cubes and dimensions.
The global_11g_source.dmp file, which contains the GLOBAL tables and relational metadata that are required to enable general query refresh and rewrite for cube materialized views. This includes primary key constraints, foreign key constraints, and relational dimension objects. For more details on the required metadata, please refer to Oracle Database Data Warehousing Guide, which is available in the Supporting Documentation section of the Oracle Database Documentation Library. For 11g Release 1, see Supporting Documentation for 11.1. For 11.2, see Supporting Documentation for 11.2.
A script that uninstalls the Global schema, global_11g_remove.sql. This script drops the GLOBAL user and all of the objects created by the global_11g_install.sql script.
Templates that contain XML definitions of OLAP metadata objects. For information on the templates, see Templates in the Sample Kit.

Installing the Global Schema

To install the Global sample schema, do the following:

Download the global_11g_schema.zip file.
Extract the contents of the file.
Change directories to the directory containing the extracted files.
Start SQL*Plus and connect as the SYSTEM user.
Run the installation script. For example, enter the following command.
SQL> @global_11g_install;
Optionally, create the GLOBAL analytic workspace.
SQL> @global_11g_create_cubes.sql;

Tables in the Global Schema

The Global schema has four dimension tables, two fact tables, and two other tables. The following table lists the tables and provides brief descriptions of them.

Table Description

CHANNEL_DIM Contains sales channel information that is the basis of an OLAP dimension that has one hierarchy with two levels. The table supports description attributes in three languages.

CUSTOMER_DIM Contains customer information that is the basis of an OLAP dimension that has two hierarchies. Each hierarchy has four levels. The table supports description attributes in three languages.

PRODUCT_DIM Contains product information that is the basis of an OLAP dimension that has one hierarchy with four levels. The table supports description attributes in three languages. It also supports attributes for packages of products, buyers, and marketing managers.

TIME_DIM Contains time period information that is the basis of an OLAP dimension that has two hierarchies. Each hierarchy has four levels. The table supports end date and time span attributes.

PRICE_FACT Contains data for unit prices and unit costs by product and time period. This table is the basis for an OLAP cube that has two measures that are dimensioned by the product and time dimensions.

UNITS_FACT Contains data for the number of units sold, the cost of the units sold, and the amount of gross sales revenue received by channel, customer, product, and time period. This table is the basis for an OLAP cube that has three measures that are dimensioned by the channel, customer, product and time dimensions.

ACCOUNT Contains additional information about customers by account. It is related to the CUSTOMER_DIM table by ACCOUNT_ID.

PRODUCT_CHILD_PARENT Contains much of the same data as the PRODUCT_DIM table. This table is the basis for an OLAP dimension that has a value-based hierarchy rather than a hierarchy based on levels.

Table	Description
`CHANNEL_DIM`	Contains sales channel information that is the basis of an OLAP dimension that has one hierarchy with two levels. The table supports description attributes in three languages.
`CUSTOMER_DIM`	Contains customer information that is the basis of an OLAP dimension that has two hierarchies. Each hierarchy has four levels. The table supports description attributes in three languages.
`PRODUCT_DIM`	Contains product information that is the basis of an OLAP dimension that has one hierarchy with four levels. The table supports description attributes in three languages. It also supports attributes for packages of products, buyers, and marketing managers.
`TIME_DIM`	Contains time period information that is the basis of an OLAP dimension that has two hierarchies. Each hierarchy has four levels. The table supports end date and time span attributes.
`PRICE_FACT`	Contains data for unit prices and unit costs by product and time period. This table is the basis for an OLAP cube that has two measures that are dimensioned by the product and time dimensions.
`UNITS_FACT`	Contains data for the number of units sold, the cost of the units sold, and the amount of gross sales revenue received by channel, customer, product, and time period. This table is the basis for an OLAP cube that has three measures that are dimensioned by the channel, customer, product and time dimensions.
`ACCOUNT`	Contains additional information about customers by account. It is related to the `CUSTOMER_DIM` table by `ACCOUNT_ID`.
`PRODUCT_CHILD_PARENT`	Contains much of the same data as the `PRODUCT_DIM` table. This table is the basis for an OLAP dimension that has a value-based hierarchy rather than a hierarchy based on levels.

Templates in the Sample Kit

In Analytic Workspace Manager or in an OLAP Java API program, you can import or export OLAP metadata objects as XML templates. In the Templates directory, the sample kit contains the following XML templates that you can import into an analytic workspace.

Template Description

GLOBAL.XML Defines an analytic workspace name Global that has two cubes and four dimensions but does not have materialized views.

GLOBAL_MV.XML Defines an analytic workspace name Global that has two cubes and four dimensions. The Units cube and the four dimensions are enabled as materialized views. The Price cube is not enabled as a materialized view. This template is used by global_11g_create_cubes.sql when building the analytic workspace using the script.

TIME.XML Defines a Time dimension that has two level-based hierarchies.

PRODUCT.XML Defines a Product dimension that has one level-based hierarchy.

CHANNEL.XML Defines a Channel dimension that has one level-based hierarchy.

CUSTOMER.XML Defines a Customer dimension that has two level-based hierarchies.

UNITS_CUBE.XML Defines a Units cube without materialized view capabilities.

PRICE_CUBE.XML Defines a Price cube without materialized view capabilities.

Template	Description
`GLOBAL.XML`	Defines an analytic workspace name Global that has two cubes and four dimensions but does not have materialized views.
`GLOBAL_MV.XML`	Defines an analytic workspace name Global that has two cubes and four dimensions. The Units cube and the four dimensions are enabled as materialized views. The Price cube is not enabled as a materialized view. This template is used by `global_11g_create_cubes.sql` when building the analytic workspace using the script.
`TIME.XML`	Defines a Time dimension that has two level-based hierarchies.
`PRODUCT.XML`	Defines a Product dimension that has one level-based hierarchy.
`CHANNEL.XML`	Defines a Channel dimension that has one level-based hierarchy.
`CUSTOMER.XML`	Defines a Customer dimension that has two level-based hierarchies.
`UNITS_CUBE.XML`	Defines a Units cube without materialized view capabilities.
`PRICE_CUBE.XML`	Defines a Price cube without materialized view capabilities.

Uninstalling the Global Schema

To uninstall the Global schema, run the global_11g_remove.sql script as the SYSTEM user.