Case Study: Creating the Sales History Analytic Workspace - OLAP

Although Global is used for most of the examples in this manual, Sales History has a very different set of data characteristics and demonstrates a correspondingly different set of build choices.

Sales History (SH) is a sample star schema that is delivered with your Oracle Database, along with a fully defined logical model stored in the OLAP Catalog. The SH schema has two cubes, SALES and COSTS. The SALES cube has five dimensions, and the COSTS cube uses two of these dimensions. This case study uses only the SALES cube.

Defining Startup Parameters for the SH Build

When building a large analytic workspace, the startup parameters for the Oracle Database affect how quickly the build proceeds. Example shows a few of the settings in the init.ora file for building Sales History.

Example Startup Parameters for Building Sales History

Defining Tablespaces for SH

While the GLOBAL analytic workspace has less than a million cells for base-level data in its largest cube, the Sales History COST cube has over 235 trillion. This makes Sales History quite large for a sample schema, yet it is small to average for a real application. It is sufficiently large for the build to fail unless resources have been allocated specifically for its use. The build needs adequate temporary and permanent tablespaces:

  • Define a tablespace just for use by the Sales History analytic workspace, which is sufficiently large to hold the base-level data, stored aggregates, forecast data, and so forth. If possible, define extension files on separate physical disks. For the best performance, do not use the same tablespace as the star schema.
  • Define a temporary tablespace that is sufficiently large to hold the data for the SALES cube. Use a small EXTENT MANAGEMENT SIZE value, such as 256K. Example shows how the tablespaces might be defined for Sales History.

Example SQL Script for Defining Tablespaces for the Sales History Analytic Workspace

Examining the Sparsity Characteristics of SH Data

The data in the SH relational schema is extremely sparse. Many dimension keys are never used as foreign keys in the SALES fact table, much less used in all possible combinations with the other four dimensions. For example, CUSTOMERS.CUST_ID has 5100 values, of which only 2557 are used in the SALES.CUST_ID column. Time is also a sparse dimension, with only 1075 of 1826 dimension members used. Thus, TIMES_DIM must be included in the composite. You can define a composite with all five dimensions by choosing Advanced Storage Options. List TIMES as the first dimension (the fastest varying) in the composite, to facilitate time-based analysis and data maintenance, even though it is smaller than PRODUCTS and CUSTOMERS. List the other dimensions from largest to smallest. This information is easily obtained by issuing a SELECT COUNT(*) on the dimension tables.

Managing the SH Build

Because SH is large, you may want to manage these aspects of the build:

  • Time: Execute the build during off-peak hours. To do this, generate a SQL script for the build instead of creating it immediately.
  • Progress Monitor: Add comments to the SQL script so that you can monitor its progress. If the build fails for any reason, or if you need to interrupt it, you can restart the script from where the build stopped.
  • Permanent Tablespace Size: If possible, define some measures with smaller data types, as described in "Manually Changing Object Definitions".
    This type of change requires you to choose one of the partial build options. For a description of data types, search Help in Analytic Workspace Manager.

Running the Create Analytic Workspace Wizard

Make these choices in the Create Analytic Workspace wizard for building Sales History, based on the previous discussion:

  • 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, select Advanced Storage Options. This choice enables you to define a composite that includes the TIMES dimension.
    No prefix is needed 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.
  • When creating the composite, include all of the dimensions and put them in this order. Do not specify a segment size, because a very large segment is allocated automatically for composites.
  • On the Choose Create and Enablement Options page, select Save Script to File. You do not need to enable the workspace at this time.

Building the Sales History Analytic Workspace

Take these steps to build the Sales History analytic workspace:

  1. Run the build script. After it has completed successfully, you have an analytic workspace with all of the dimensions, hierarchies, levels, and attributes from the dimension tables.
  2. Make any changes or additions to the object definitions.
  3. 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 © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

OLAP Topics