Case Study: Creating the Global Analytic Workspace - OLAP

The following case study explains the choices made in creating an analytic workspace from the GLOBAL star schema.

Defining the GLOBAL_AW Workspace User

This example creates the GLOBAL analytic workspace in a different schema from the source tables. Example lists the SQL commands to define the GLOBAL_AW user with sufficient access rights to use Analytic Workspace Manager and to access the GLOBAL star schema. Alternatively, you can define users through Oracle Enterprise Manager.

Example SQL Script for Defining the GLOBAL_AW User

Examining Sparsity Characteristics for GLOBAL

By using SQL SELECT commands with the COUNT and COUNT(DISTINCT) functions, you can estimate how dense the resulting multidimensional cubes will be in the analytic workspace.

The PRICE_AND_COST_HISTORY_FACT table has 1407 rows with values for both UNIT_PRICE and UNIT_COST out of a possible 1728 dimension value combinations (48 months * 36 products). The Price cube (which is mapped to the PRICE_AND_COST_HISTORY_FACT table) is 80% dense, and so a composite will actually slow performance rather than improve it. A dense cube is fairly unusual, and the Create AnalyticWorkspace wizard does not support it, nor does the DBMS_AWM PL/SQL package. Thus, you will need to make some modifications to the workspace object definitions before loading the data.

The UNITS_HISTORY_FACT table has 110,166 rows with values for UNITS out of a possible 316,224 dimension value combinations (48 months * 36 products * 61 customers * 3 channels). This cube is 30% dense, which is sufficiently sparse for a composite.
Because Time is aggregated to the month level, the Time dimension is probably dense. This cube can use the default composite.

Running the Create Analytic Workspace Wizard

Make these choices when running the Create Analytic Workspace wizard:

  • On the Choose Data Loading Options page, do the following:
    • Choose the second build option, Build analytic workspace and load dimensions. This choice enables you to make modifications to the variable definitions before loading the data.
    • Clear the Generate unique keys box. The dimension tables use surrogate keys for all levels to assure unique dimension values.
  • On the Choose Advanced Storage and Naming Options page, no prefix is needed when naming the workspace objects because the analytic workspace is being created in a different schema from the relational tables. The cube name prefix is optional, but may be useful when an analytic workspace contains multiple cubes.

The selected build option causes the Create Analytic Workspace wizard to define all of the workspace objects and fetch all of the data from the dimension tables. The measures are defined but do not have data. You can make changes to the object definitions before loading the data.

(An alternative approach to the one taken in this example is to generate a SQL script and modify the calls to DBMS_AWM, so that the analytic workspace is generated correctly and does not require modification.)

Manually Changing Object Definitions

Measure names such as UNIT_PRICE and UNIT_COST are given to the formulas in the analytic workspace that are used to aggregate the variables. The data itself is stored in variables with names like UNIT_PRICE_VARIABLE and UNIT_CUBE_VARIABLE.

Using OLAP Worksheet and the OLAP DML, you can make two changes to UNIT_ PRICE_ VARIABLE and UNIT_COST_VARIABLE:

  • Remove the composite. The Price cube is 80% dense, as explained in "Examining Sparsity Characteristics for GLOBAL".
    UNIT_PRICE_VARIABLE and UNIT_CUBE_VARIABLE store the data for the Price cube. Because these variables are dense, defining them with a composite would actually slow performance.
  • Change the data type. The data for the Price cube is easily handled by the SHORTDECIMAL data type. Use DECIMAL (8 bytes) and SHORTDECIMAL (4 bytes) whenever possible to save storage space and maximize performance. DECIMAL is the default numeric data type. For a full list of data types, search Analytic Workspace Manager Help.

These are significant changes that require the variables to be redefined, not simply modified.
Take these steps to redefine the variables:

  1. In the Object View, attach the GLOBAL analytic workspace with read/write access.
  2. Choose OLAP Worksheet from the Tools menu.
  3. In the lower pane (the query window) enter this command to display the object definitions for the two variables:
    FULLDSC price_cube_unit_price_variable price_cube_unit_cost_variable
  4. Select the full object definitions from the top pane (the response window) and paste them into a text editor.
  5. Edit the definitions so that they look like the following code example. Note the DELETE command at the beginning, the changes to the data type and dimensionality in the DEFINE commands, the addition of a CONSIDER command after each DEFINE, and the standard form PROPERTY commands listed with the full command on a single line.
  6. Save the file in, or move it to, a disk directory that has been defined as a directory object in the database. Then use the OLAP DML INFILE command to execute the file. INFILE is equivalent to the SQL @ command.
  7. INFILE 'directory_object/filename' Check the response window for errors. To fix them, edit the file and execute it again.
  8. To save these changes, type these commands into the query window and execute them:

Completing the Build

After all of the changes are made to the workspace object definitions, run the Refresh wizard to load the data, as described in "Refreshing the Data in an Analytic Workspace" .The dimensions do not need to be refreshed; only the cubes.
The workspace can be enabled now or after deploying an aggregation plan.

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

OLAP Topics