5 Creating an Analytic Workspace

Model View

The Model View enables you to define a logical dimensional model composed of dimensions, levels, hierarchies, attributes, measures, calculated measures, and measure folders. The model is stored in the analytic workspace as database standard form metadata.

A drag-and-drop user interface facilitates mapping of the logical objects to columns in relational tables, views, and synonyms in Oracle Database. The source columns can be star, snowflake, or any other schema design that supports the logical model.

Figure 5-1 shows the logical objects created in the GLOBAL analytic workspace.

Figure 5-1 Model View in Analytic Workspace Manager

Description of "Figure 5-1 Model View in Analytic Workspace Manager"

Object View

The Object View provides a graphical user interface to the OLAP DML. You can create, modify, and delete individual workspace objects. This view is provided for users who are familiar with the OLAP DML and want to upgrade from Express databases or modify custom applications. Do not use this view to manually change a standard form analytic workspace, because you may create inconsistencies in the metadata.

Installing Analytic Workspace Manager

The most recent version of Analytic Workspace Manager is available for download from the Oracle Technology Network:

http://www.oracle.com/technology/products/bi/olap/index.html

Follow the installation instructions provided in the README file.

Opening Analytic Workspace Manager

On Windows, open Analytic Workspace Manager from the Start menu. Choose Oracle - Oracle_home, then Integrated Management Tools, and then OLAP Analytic Workspace Manager and Worksheet.

On Linux, open Analytic Workspace Manager from the shell command line:

$ORACLE_HOME/olap/awm/awm.sh

Defining a Database Connection

You can define a connection to each database that you use for OLAP. After you have defined a connection, the database instance is listed in the navigation tree for you to access at any time.

To define a database connection:

1. Right-click the top Databases folder in the navigation tree, then choose Add Database to Tree from the pop-up menu.

2. Complete the Add Database to Tree dialog box.

Opening a Database Connection

To connect to a database:

1. Click the plus icon (+) next to a database in the navigation tree.

2. Complete the Connect to Database dialog box.

Schema Requirements

The analytic workspace that you create must contain the logical objects described in Chapter 2. For the source data to support a logical dimensional data model, these relationships must exist:

• Dimensions. You can map dimensions, levels, and attributes to any collection of tables or views that identify the child-parent relationships and the member-attribute relationships. The tables and views can be in one schema or owned by multiple schemas. When mapping dimensions, you can choose from these categories of schemas:

  • Star Schema

  • Snowflake Schema

  • Other

  You can identify different dimensions as having different schema characteristics, for example, Customer could be a star schema (all levels and their attributes are in one table) and Time could be a snowflake schema (levels are in two or more tables with their attributes).

• Measures. You can map measures to any table or view that contains the appropriate data.

Hierarchies and cubes are strictly metadata objects and are not mapped to data sources.

Tables may contain columns of no importance to your analytic workspace. You can simply omit them from the mappings, and Analytic Workspace Manager will ignore them.

Star Schema

A star schema is the simplest of the three types. It is called a star schema because a diagram of this schema resembles a star, with points radiating from a central table. The center of the star is a fact table and the points of the star are the dimension tables.

• Dimension tables define the dimensions. In a star schema, all of the information for a dimension is stored in one table.

• Fact tables contain foreign keys from each dimension table and a column for each measure.

Figure 5-2 shows the relationships in a star schema using the GLOBAL relational tables. These tables provide the data for the Units Cube. These source tables illustrate different types of schema designs:

• CUSTOMER_DIM and CHANNEL_DIM are level-based dimensions in a star schema.

• PRODUCT_CHILD_PARENT is a parent-child table that supports a value-based hierarchy. There are no level columns.

• TIME_MONTH_DIM is the base-level table of a snowflake schema. The Time tables are described in "Snowflake Schema".

Figure 5-2 Star Schema

Description of "Figure 5-2 Star Schema"

Snowflake Schema

A snowflake schema is a type of star schema. It is called a snowflake schema because a diagram of the schema resembles a snowflake. Snowflake schemas normalize dimensions to eliminate redundancy. That is, the dimension data has been divided into multiple tables instead of one large table. Each level may be in a separate table with its attributes.

Figure 5-3 shows the Time dimension in a snowflake schema, with separate tables for months, quarters, and years.

Note that the other dimensions are shown only partially in this snowflake diagram.

Figure 5-3 Normalized Time Dimension in a Snowflake Schema

Description of "Figure 5-3 Normalized Time Dimension in a Snowflake Schema"

Other

Any schema can be used that contains the parent-child relationships and the member-attribute relationships needed to implement dimensions in a dimensional data model. In the most extreme case, each parent-child and member-attribute value pairs for each hierarchy may be in a different table.

Figure 5-4 shows the Product dimension in a schema that contains the appropriate relationships. Contrast these 11 tables with the single table shown in Figure 5-2 for the Product dimension in a star schema. Whereas in the star schema, the Product dimension has one source table, this schema has been normalized to store each level and each attribute in a separate table.

Note that the other dimensions are not shown in this diagram.

Figure 5-4 Product Dimension in an "Other" Schema Design

Description of "Figure 5-4 Product Dimension in an "Other" Schema Design"

Choosing a Build Tool

Both Analytic Workspace Manager and Warehouse Builder can be used to generate analytic workspaces.

Warehouse Builder is designed for Information Technology (IT) professionals who manage production systems. It is a powerful tool that can generate analytic workspaces as one element in a larger ETL process.

Analytic Workspace Manager is an easy-to-use tool designed for application developers, departmental DBAs, and other nonprofessional DBAs. It enables them to design and develop a data model quickly and interactively based on their reporting needs. After the data model has been developed and its design is stable, the IT department may assume responsibility for generating the analytic workspace using Warehouse Builder. Analytic Workspace Manager can be used to enhance the analytic workspaces created by the IT department, such as by adding custom measures.

How Analytic Workspace Manager Saves Changes

Analytic Workspace Manager saves changes automatically that you make to the analytic workspace. You do not explicitly save your changes.

Saves occur when you take an action such as these:

• Click OK or the equivalent button in a dialog box.

  For example, when you click Import in the Import From EIF File dialog box, the contents are imported, and the revised analytic workspace is committed to the database. Likewise, when you click Create in the Create Dimension dialog box, the new dimension is committed to the database.

• Click Apply in a property sheet.

  For example, when you change the labels on the General property page for an object, the change takes effect when you click Apply.

Basic Steps for Creating an Analytic Workspace

To create an analytic workspace in database standard form:

1. Configure your database instance for OLAP use. Define permanent, temporary, and undo tablespaces as needed, and set the database parameters to values appropriate for data loads. Refer to Chapter 6 for details.

2. Define a database user who will own the analytic workspace. Grant the user the OLAP_USER role and SELECT privileges on the source data tables.

3. Open Analytic Workspace Manager and connect to your database instance as the user you defined earlier for this purpose.

4. Create a new analytic workspace container in your database:

   1. In the Model View navigation tree, expand the folders until you see the schema where you want to create the analytic workspace.

   2. Right-click the schema name, then choose Create Analytic Workspace from the pop-up menu.

   3. Complete the Create Analytic Workspace dialog box, then choose Create.

      The new analytic workspace appears in the Analytic Workspaces folder for the schema.

5. Define the logical dimensions for the data.

   See "Creating Logical Dimensions".

6. Define the logical cubes for the data.

   See "Creating Logical Cubes".

7. Map the logical items to their data sources.

   See "Mapping Logical Objects to Data Sources".

8. Run the Sparsity Advisor to get recommendations for the physical implementation of the cubes.

   See "Using the Sparsity Advisor".

9. Load the data.

   See "Maintaining the Data".

10. Define measure folders to simplify access for end users.

    See "Defining Measure Folders".

When you have finished, you will have an analytic workspace populated with the detail data fetched from relational tables or views. You may also have summarized data and calculated measures.

Adding Functionality to a Standard Form Analytic Workspace

In addition to the basic steps, you can add functionality to an analytic workspace in these ways:

• Support multiple languages by adding translations of metadata and attribute values.

  See "Supporting Multiple Languages".

• Develop one or more calculation plans for the analytic workspace. Calculation plans enable you to generate forecasts, allocate data down the dimension hierarchies, aggregate data up the dimension hierarchies, and specify the order of these calculations.

  See "Creating and Executing Calculation Plans".

Creating Levels

For business analysis, data is typically summarized by level. For example, your database may contain daily snapshots of a transactional database. Days are thus the base level. You might summarize this data at the weekly, quarterly, and yearly levels.

Levels have parent-child or one-to-many relationships, which form a level-based hierarchy. For example, each week summarizes seven days, each quarter summarizes 13 weeks, and each year summarizes four quarters. This hierarchical structure enables analysts to detect trends at the higher levels, then drill down to the lower levels to identify factors that contributed to a trend.

For each level that you define, you must identify a data source for dimension members at that level. Members at all levels are stored in the same dimension. In the previous example, the Time dimension contains members for weeks, quarters, and years.

To create a level:

1. Expand the folder for the dimension.

   A dimension folder contains subfolders named Levels, Hierarchies, and Attributes.

2. Right-click Levels, then choose Create Level from the pop-up menu.

   The Create Level dialog box is displayed.

3. Complete all tabs of the Create Level dialog box.

   Click Help for specific information about these choices.

4. Click Create.

   The new level appears as an item in the Levels folder.

Creating Hierarchies

Dimensions can have one or more hierarchies. Most hierarchies are level-based. Analytic Workspace Manager supports these common types of level-based hierarchies:

• Normal hierarchies consist of one or more levels of aggregation. Members roll up into the next higher level in a many-to-one relationship, and these members roll up into the next higher level, and so forth to the top level.

• Ragged hierarchies contain at least one member with a different base, creating a "ragged" base level for the hierarchy.

• Skip-level hierarchies contain at least one member whose parents are more than one level above it, creating a hole in the hierarchy. An example of a skip-level hierarchy is City-State-Country, where at least one city has a country as its parent (for example, Washington D.C. in the United States).

  In relational source tables, a skip-level hierarchy may contain nulls in the level columns.

You may also have dimensions with parent-child relations that do not support levels. For example, an employee dimension might have a parent-child relation that identifies each employee's supervisor. However, levels that group together first-, second-, and third-level supervisors and so forth may not be meaningful for analysis. Similarly, you might have a line-item dimension with members that cannot be grouped into meaningful levels. In this situation, you can create a value-based hierarchy defined by the parent-child relations, which does not have named levels. You can create value-based hierarchies only for dimensions that use natural keys, because surrogate keys are formed with the names of the levels.

To create a hierarchy:

1. Expand the folder for the dimension.

   A dimension folder contains subfolders named Levels, Hierarchies, and Attributes.

2. Right-click Hierarchies, then choose Create Hierarchy from the pop-up menu.

   The Create Hierarchy dialog box is displayed.

3. Complete all tabs of the Create Hierarchy dialog box.

   If you define multiple hierarchies, be sure to define one of them as the default hierarchy.

   Click Help for specific information about these choices.

4. Click Create.

   The new hierarchy appears as an item in the Hierarchies folder.

Creating Attributes

Attributes provide information about the individual members of a dimension. They are used for labeling crosstabular and graphical data displays, selecting data, organizing dimension members, and so forth.

Creating Cubes

Cubes define the shape of your business measures. They are defined by a set of ordered dimensions. The dimensions form the edges of a cube, and the measures are the cells in the body of the cube.

To create a cube:

1. Expand the folder for the analytic workspace.

   An analytic workspace folder contains subfolders named Dimensions, Cubes, Measure Folders, and Calculation Plans.

2. Right-click Cubes, then choose Create Cube from the pop-up menu.

   The Create Cube dialog box is displayed.

3. Complete all tabs except Implementation Details of the Create Cube dialog box.

   Important: After mapping the cube, run the Sparsity Advisor to see the recommended settings for the Implementation Details tab. For more information about the Summary To tab, refer to Chapter 7.

4. Click Create. The new cube appears as a subfolder under Cubes.

Creating Measures

Measures store the facts collected about your business. Each measure belongs to a particular cube, and thus shares particular characteristics with other measures in the cube, such as the same dimensions.

To create a measure:

1. Expand the folder for the cube that has the dimensions of the new measure.

   A cube folder contains subfolders named Measures and Calculated Measures.

2. Right-click Measures, then choose Create Measure from the pop-up menu.

   The Create Measure dialog box is displayed.

3. Complete the General, Translations, and Implementation Details tabs of the Create Measure dialog box. Complete all tabs if you wish to override the cube settings.

   Click Help for specific information about these choices.

4. Click Create.

   The new measure appears as an item in the Measures folder.

Creating Calculated Measures

Calculated measures add valuable information to an analytic workspace. They are created by performing calculations on the measures stored in an analytic workspace. Oracle OLAP offers an extensive range of functions and operators that can be used to define custom measures. Analytic Workspace Manager provides a Calculation Wizard, as shown in Figure 1-2, which provides these calculations:

• Basic Arithmetic. Addition, subtractions, multiplication, division, ratio

• Advanced Arithmetic. Cumulative total, index, percent markup, percent variance, rank, share, variance

• Prior/Future Comparison. Prior value, difference from prior period, percent difference from prior period, future value

• Time Frame. Moving average, moving maximum, moving minimum, moving total, year to date

Calculated measures are not stored, and so they do not occupy any significant disk space. The data values are calculated in response to individual queries on the calculated measures. In this respect, calculated measures are similar to relational views.

To create a calculated measure:

1. Expand the folder for the cube that contains the base measures that will be used in the calculation.

2. Right-click Calculated Measures, then choose Create Calculated Measure from the pop-up menu.

   The Calculation Wizard Welcome page is displayed.

3. Follow the steps of the wizard.

   Click Help for specific information about these choices. When you are done, the name of the new calculated measure appears as an item in the Calculated Measures folder.

Mapping Dimensions

To map a dimension in the graphical view, take these steps:

1. Define the dimension and its levels, hierarchies, and attributes.

2. In the Model View navigation tree, expand the dimension folder and click Mappings.

   The Mapping Window will be displayed in the right pane.

3. Enlarge the mapping window by dragging the divider to the left.

4. In the toolbar, identify the source schema as Star Schema, Snowflake Schema, or Other.

5. In the schema navigation tree, locate the tables with the dimension members and attributes for all levels. Drag-and-drop them onto the mapping canvas.

6. Draw lines from the source columns to the target objects. To draw a line, click the output connector of the source column and drag it to the input connector of the target object. Be careful to map every logical object to a source column.

   Tip: For a star schema with logical names that match the column names, click Auto Map Star Schema in the toolbar. Verify that all logical objects are mapped correctly.

7. To uncross the lines, click the Auto Arrange Mappings tool.

8. Click Apply.

9. When you have mapped all objects for the dimension, drag the divider to the right to restore access to the navigation tree.

Figure 5-6 shows the mapping canvas with the Channel dimension and its attributes mapped to columns in the CHANNEL_DIM table. The mapping toolbar is at the top, and the schema navigation tree is on the left.

Figure 5-6 GLOBAL CHANNEL Dimension Mapped in Graphical View

Description of "Figure 5-6 GLOBAL CHANNEL Dimension Mapped in Graphical View"

Mapping Cubes

To map a cube in the graphical view, take these steps:

1. Define the cube and its measures.

   You can define calculated measures at any time, because they are calculated, not loaded.

2. In the Model View navigation tree, expand the Cubes folder and click Mappings.

   The Mapping Window will be displayed in the right pane. You will see a schema navigation tree and a table with rows for the measures, dimensions, and levels.

3. Enlarge the mapping window by dragging the divider to the left.

4. In the schema navigation tree, locate the tables with the measures. Drag-and-drop them onto the mapping canvas.

5. Draw lines from the source columns to the target objects.

   To draw a line, click the output connector of the source column and drag it to the input connector of the target object. You must map both the measures and the related dimension keys.

6. To uncross the lines, click the Auto Arrange Mappings tool.

7. When you have mapped all objects for the dimension, drag the divider to the right to restore access to the navigation tree.

Figure 5-7 shows the mapping canvas with the Price and Cost cube mapped to columns in the PRICE_AND_COST_HIST_FACT table. The mapping toolbar is at the top, and the schema navigation tree is on the left.

Figure 5-7 GLOBAL PRICE_AND_COST_CUBE Cube Mapped in Graphical View

Description of "Figure 5-7 GLOBAL PRICE_AND_COST_CUBE Cube Mapped in Graphical View"

What is Sparsity?

Sparsity refers to the extent to which cells contain null (NA) values instead of data. For example, if a cube is 25 percent sparse, then 25 percent of that cube's cells contain NA values and 75 percent contain data. You can also describe this cube as 75% dense.

A cube can be dense, sparse, or extremely sparse.

• Dense cubes have up to 20% empty cells. Dense data should be stored directly in the dimensional format.

  Dense cubes are extremely rare. If you know that a cube is dense, then do not use the Sparsity Advisor, because it is optimized for use on sparse data.

• Sparse cubes have more than 20% empty cells. Sparse data should be stored in a special format called a composite.

• Extremely sparse cubes often display these characteristics:

  • The cube has a large number of dimensions (seven or more).

  • One dimension has more than 300,000 members.

  • Two dimensions have more than 100,000 members each.

  • Dimension hierarchies have numerous levels, with little change to the number of dimension members from one level to the next, so that many parents have only one descendant for several contiguous levels.

  Extremely sparse data should be stored in another special format called a compressed composite. Compressed storage for this type of sparsity uses less space and results in faster aggregation than normal sparse storage.

Sparsity is calculated as the relationship between the number of actual data values in the measures and the number of cells defined by the dimensions of the cube. You can easily project the sparsity of a dimensional cube from the relational source tables. It is the number of rows in the fact table with data values divided by the product of the unique keys in the dimension tables.

Ordering the Dimensions in a Cube

The order in which the dimensions are listed for a cube affects performance because it determines the way the data is stored on disk. Performance is optimized when values that are accessed together are stored together, because fewer pages must be swapped in and out of memory.

For regular composites and dimensional storage, order the dimensions from largest to smallest.

For compressed composites, order the dimensions from smallest to largest.

Choosing a Data Type

The most commonly used data types are NUMBER and DECIMAL.

The NUMBER data type provides the same results on all platforms. All calculations are performed as integer arithmetic. Because of this, results based on a NUMBER will match those stored in relational tables. Choose NUMBER when you need a high level of precision, or when you need to match values. An unscaled NUMBER value is 22 bytes.

The DECIMAL data type is smaller at 8 bytes for each value and thus takes up less disk space than NUMBER data types. All calculations are done in the CPU Floating Point Unit, which is many times faster than integer arithmetic. However, floating point calculations produce slightly differently results on different platforms, typically at the seventh decimal place. DECIMAL is the best choice when the analytic workspace will be used heavily for computations.

Choosing Composite Types

A composite is an index into one or more sparse measures, and is used to store sparse data in a compact form. There are two types of composites: regular and compressed. A regular composite is used to store measures with moderate sparsity, and compressed composites are used to store measures with extreme sparsity.

For a cube using regular composites and partitions, you can choose between creating a single, global composite for use by all partitions and creating a composite for each partition. When in doubt, do not use global composites. The cube will have one composite for each partition.

Partitioning Large Measures

Partitioning is a method of physically storing the measures in a cube. It improves the performance of large measures in the following ways:

• Improves scalability by keeping data structures small. Each partition functions like a smaller measure.

• Keeps the working set of data smaller both for queries and maintenance, since the relevant data is stored together.

• Enables parallel aggregation during data maintenance. Each partition can be aggregated by a separate process.

• Allows different client sessions to have write access to different partitions of the same object at the same time.

• Simplifies removal of old data from storage. Old partitions can be dropped as a unit, and new partitions can be added.

• Stores each partition of a compressed cube in a separate analytic workspace object. If a compressed cube is not partitioned, then all measures of the cube are stored in one object.

Example of a Partitioned Dimension

For example, you might choose the Quarter level of the Time dimension. Each Quarter and its descendants are stored in a separate partition. If there are three years of data in the analytic workspace, then partitioning on Quarter produces 12 partitions, in addition to the default partition. The default partition contains all remaining levels, that is, those above Quarter (such as Year) and those in other hierarchies (such as Fiscal Year or Year-to-Date). The aggregate levels in the new partitions are calculated and stored in the analytic workspace as a data maintenance step, while the levels in the default partition are calculated on the fly.

Figure 5-8 illustrates a Time dimension partitioned by Quarter.

Figure 5-8 Partitioning Time by Quarter

Description of "Figure 5-8 Partitioning Time by Quarter"

Submitting Maintenance Tasks to the Oracle Job Queue

If you submit a maintenance task to the Oracle job queue, you can specify the maximum number of simultaneous processes the job can use. This number is limited by two factors:

• The number of objects in the analytic workspace that can be summarized in parallel. Each cube and each partition (including the default partition) can use a separate process.

• The number of simultaneous database processes the user is authorized to run.

  This number is controlled by the JOB_QUEUE_PROCESSES parameter. The setting for this parameter is based on the number of processors, as described in "Initialization Parameters for Oracle OLAP". You can obtain the current parameter setting with the following SQL command:

  SHOW PARAMETER JOB_QUEUE_PROCESSES
  
  

Specify the smaller of these two numbers when submitting a job.

Oracle Database allocates the specified number of processes (if you have sufficient authorization) regardless of whether all of them can be used simultaneously at any point in the job. For example, if your job can use up to three processes, but you specify five, then two of the processes allocated to your job cannot be used by it or any other job.

Managing Maintenance Jobs

When submitting a maintenance task to the job queue, be sure to note the job number so that you can verify that the job completed successfully. Runtime messages are stored in a table named OLAPSYS.XML_LOAD_LOG. Messages in this file are identified just by the digits in the job number. The following SQL statement returns the messages for job AWXML$_54:

SELECT XML_MESSAGE FROM OLAPSYS.XML_LOAD_LOG WHERE XML_LOADID='54';

You can manage these jobs using tools such as Oracle Enterprise Manager Scheduler or the DBMS_SCHEDULER PL/SQL package.

Defining the GLOBAL_AW User

This example creates the GLOBAL analytic workspace in a different schema from the source tables. Example 5-1 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.

The installation scripts for Global create the GLOBAL_AW user, so you do not need to run the script shown here. However, you may wish to use it as a model for creating other users.

CREATE USER "GLOBAL_AW" PROFILE "DEFAULT" 
    IDENTIFIED BY "global_aw" DEFAULT TABLESPACE "GLOBAL"
    TEMPORARY TABLESPACE "GLOBAL_TEMP" 
    QUOTA UNLIMITED ON "GLOBAL"
    ACCOUNT UNLOCK;

GRANT OLAP_USER TO GLOBAL_AW;

GRANT SELECT ON global.channel_dim TO global_aw;
GRANT SELECT ON global.product_child_parent TO global_aw;
GRANT SELECT ON global.customer_dim TO global_aw;
GRANT SELECT ON global.time_month_dim TO global_aw;
GRANT SELECT ON global.time_quarter_dim TO global_aw;
GRANT SELECT ON global.time_year_dim TO global_aw;
GRANT SELECT ON global.units_history_fact TO global_aw;
GRANT SELECT ON global.price_and_cost_history_fact TO global_aw;

Creating the GLOBAL Analytic Workspace

Take these steps to create the GLOBAL analytic workspace:

1. Open Analytic Workspace Manager and connect to Oracle Database as the GLOBAL_AW user, using GLOBAL_AW as the password.

2. In the Model View navigation tree, expand the GLOBAL_AW folder, and right-click Analytic Workspaces.

3. Choose Create Analytic Workspace from the pop-up menu.

4. Complete the Create Analytic Workspace dialog box, then choose Create.

This step creates the analytic workspace container and populates it with standard form catalogs and similar objects. You must now define the logical model.

Creating GLOBAL Dimensions and Attributes

GLOBAL has four dimensions: TIME, PRODUCT, CUSTOMER, and CHANNEL. Implement the logical model described in Chapter 3 by following the basic instructions in "Creating Logical Dimensions".

Note these choices:

• Time Dimension: On the General tab, select Time Dimension as the dimension type. You can map Time to a star schema (TIME_DIM table) or to a snowflake schema (TIME_MONTH_DIM, TIME_QUARTER_DIM, and TIME_YEAR_DIM tables) as described in this example.

• Product Dimension: You can map Product to a star, level-based table (PRODUCT_DIM) or to a parent-child table (PRODUCT_CHILD_PARENT) as described in this example.

• All Dimensions: On the Implementation Details tab, select Use Natural Keys From Data Source.

  The source tables have numeric surrogate keys that assure unique dimension members across all levels.

• All Attributes: On the General tab, verify that the attributes apply to all levels.

• Languages: Add French and Dutch.

Creating GLOBAL Cubes and Measures

GLOBAL is a very small and dense sample data set, so that some decisions that are crucial when handling huge data sets are simply non-consequential in this case. Nonetheless, this example shows the best practices for handling dense data. "Case Study: Creating the Sales History Analytic Workspace" shows the best practices for handling sparse data; sparsity is typical of real data sets.

GLOBAL has two cubes: UNITS_CUBE and PRICE_AND_COST_CUBE.

• UNITS_CUBE is dimensioned by TIME, PRODUCT, CUSTOMER, and CHANNEL. It contains two measures, UNITS and SALES.

• PRICE_AND_COST_CUBE is dimensioned by TIME and PRODUCT. It contains two measures, UNIT_PRICE and UNIT_COST.

Implement the logical model described in Chapter 3 by following the basic instructions in "Creating Logical Cubes".

UNITS_CUBE

On the Implementation Details page, list the dimensions in this order:

1. TIME

2. CUSTOMER

3. PRODUCT

4. CHANNEL

Time is first to facilitate data maintenance. The other dimensions are listed in order from largest to smallest.

Deselect the sparsity check boxes for all dimensions. They are dense.

PRICE_AND_COST_CUBE

On the Implementation Details page, list the dimensions in this order:

1. TIME

2. PRODUCT

Measures in the Price Cube and the Units Cube will be used together frequently in calculated measures. For performance, the dimensions that the cubes share must be listed in the same order.

Deselect the sparsity check boxes for all dimensions. They are dense.

On the Aggregation page, select Last Non-NA Data Value for Time and Average for Product.

Mapping the GLOBAL Logical Model to Data Sources

The data for the GLOBAL analytic workspace is stored in the GLOBAL schema.

To map the PRODUCT dimension, take these steps:

1. Expand the Dimensions folder, then click the Mappings node for PRODUCT.

2. Drag the divider to the left to expand the size of the mapping canvas.

3. In the schema navigation tree, expand the GLOBAL folder, then drag-and-drop the PRODUCT_CHILD_PARENT table onto the canvas.

4. Drag a line from the output connectors in the PRODUCT_CHILD_PARENT table to the appropriate input connector in the PRODUCT table.

5. Click Apply.

Repeat these steps to map CUSTOMER to the CUSTOMER_DIM table and CHANNEL to the CHANNEL_DIM table. For TIME, select Snowflake Schema and map to TIME_MONTH_DIM, TIME_QUARTER_DIM, and TIME_YEAR_DIM.

To map UNITS_CUBE, take these steps:

1. Expand the Cubes folder, then click the Mappings node for UNITS_CUBE.

2. Drag the divider to the left to expand the size of the mapping canvas.

3. In the schema navigation tree, expand the GLOBAL folder, then drag-and-drop the UNITS_DETAIL_FACT table onto the canvas.

4. Drag lines from the output connectors in the UNITS_DETAIL_FACT table to the appropriate input connectors in the UNITS_CUBE table.

5. Click Apply.

Repeat these steps to map PRICE_AND_COST_CUBE to the PRICE_AND_COST_HIST_FACT table.

Loading and Aggregating the Data

To load all of the data for GLOBAL, run the Maintenance Wizard as described in "Maintaining the Data". Note these choices:

• Run the Maintenance Wizard from the GLOBAL folder in the Model navigation tree.

• Select Objects page: Select the Add the Dimensions of the Cubes box, then move Cubes to the Selected Source Objects column. Click Finish to run the job immediately.

Figure 5-9 shows the results of a query in OracleBI Discoverer Plus OLAP.

Figure 5-9 Discoverer Plus OLAP Displays Data from PRICE_AND_COST_CUBE

Description of "Figure 5-9 Discoverer Plus OLAP Displays Data from PRICE_AND_COST_CUBE"

Creating Calculated Measures

"Identifying Required Business Facts" identifies the business measures required by the Global Corporation. Only three measures were acquired from the source fact tables: Units, Unit Price, and Unit Cost. The remaining business measures can be calculated from those three. Table 5-1 shows the calculated measures for the Units Cube.

Creating a Measure Folder

Define a measure folder with a name such as Global Enterprises, and add all measures and calculated measures to the folder.

Creating the SH Analytic Workspace

Take these steps to create the SH analytic workspace:

1. Define database parameters for OLAP.

2. Create permanent and temporary tablespaces specifically for use by the SH analytic workspace.

3. Define the SH_AW user.

4. Open Analytic Workspace Manager and connect to Oracle Database as the SH_AW user.

5. Create the SH analytic workspace, and define the logical dimensions.

6. Define the logical cube and map it to the relational tables.

7. Run the Sparsity Advisor.

8. Load and summarize the data.

9. Query the analytic workspace.

Defining Database Parameters

When building a large analytic workspace, the parameters for Oracle Database may affect how quickly the build proceeds. Before changing any database parameters, you should monitor performance using the default settings.

Example 5-2 shows a few of the settings in the init.ora file for a computer with 32G of physical memory and four processors. Note that you must define an undo tablespace before you can specify it in a startup parameter. For more information about these settings, refer to Chapter 6.

UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=OLAPUNDO
SGA_TARGET=16G
PGA_AGGREGATE_TARGET=8G
JOB_QUEUE_PROCESSES=5

Defining Tablespaces for Sales History

While the GLOBAL analytic workspace has about a half million cells for base-level data in its largest cube, the Sales History SALES cube has over 18 trillion. This makes the Sales History analytic workspace small to average for a real application, although quite large for a sample data set. It is sufficiently large for a build to fail on a small desktop computer unless resources have been allocated for its use.

You should define temporary and permanent tablespaces for use by Sales History.

• Define a tablespace that is large enough to hold the base-level data, stored aggregates, forecast data, and so forth. If multiple physical disks are available, define an extension file for each one.

• Define a temporary tablespace that is large enough to hold the data for the SALES cube. Stripe this tablespace across multiple disks the same as for the permanent tablespace. Use a small EXTENT MANAGEMENT SIZE value, such as 256K.

Example 5-3 shows how the tablespaces might be defined for Sales History when four disk drives are available.

/* Create permanent tablespaces on four disks */
CREATE TABLESPACE sh_aw DATAFILE '/disk1/oradata/sh_aw1.dbf' SIZE 64M 
AUTOEXTEND ON NEXT 64M MAXSIZE 1024M 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

ALTER TABLESPACE sh_aw ADD DATAFILE 
'/disk2/oradata/sh_aw2.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 64M MAXSIZE 1024M,
'/disk3/oradata/sh_aw3.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 64M MAXSIZE 1024M,
'/disk4/oradata/sh_aw4.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED;

/* Create temporary tablespaces on four disks */
CREATE TEMPORARY TABLESPACE sh_temp TEMPFILE '/disk1/oradata/sh_aw1.tmp' SIZE 64M REUSE 
AUTOEXTEND ON NEXT 64M MAXSIZE 1024M 
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

ALTER TABLESPACE sh_temp ADD TEMPFILE 
'/disk2/oradata/sh_aw2.tmp' SIZE 64M REUSE AUTOEXTEND ON NEXT 64M MAXSIZE 1024M,
'/disk3/oradata/sh_aw3.tmp' SIZE 64M REUSE AUTOEXTEND ON NEXT 64M MAXSIZE 1024M,
'/disk4/oradata/sh_aw4.tmp' SIZE 64M REUSE AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED;

Defining the SH_AW User

Example 5-4 shows a script that is similar to the one used to create the GLOBAL_AW user in Example 5-1. It defines a user named SH_AW and authorizes it to access the SH star schema. The script sets the new permanent and temporary tablespaces as the defaults for the SH_AW user.

/* Create the user and grant privileges */
CREATE USER sh_aw PROFILE "DEFAULT"
    IDENTIFIED BY "sh_aw" 
    DEFAULT TABLESPACE sh_perm
    TEMPORARY TABLESPACE sh_temp
    QUOTA UNLIMITED ON sh_perm
    ACCOUNT UNLOCK;
GRANT OLAP_USER TO sh_aw;

/* Grant access to SH star schema */
GRANT SELECT ON SH.CHANNELS to SH_AW;
GRANT SELECT ON SH.PRODUCTS to SH_AW;
GRANT SELECT ON SH.TIMES to SH_AW;
GRANT SELECT ON SH.CUSTOMERS to SH_AW;
GRANT SELECT ON SH.COUNTRIES to SH_AW;
GRANT SELECT ON SH.PROMOTIONS to SH_AW;
GRANT SELECT ON SH.SALES to SH_AW;

Defining the Logical Dimensions for Sales History

Because Sales History is a star schema, the logical model for the analytic workspace is primarily indicated by the schema design, as shown in Figure 5-10.

The two fact tables, SALES and COSTS, are the data sources for two logical cubes. This case study only uses SALES.

The SALES table has a primary key composed of foreign keys from five dimension tables, which are named TIMES, PRODUCTS, CHANNELS, PROMOTIONS, and CUSTOMERS. CUSTOMERS is related to a sixth dimension table, COUNTRIES, by a foreign key. In addition, SALES has two columns that contain business measures named QUANTITY_SOLD and AMOUNT_SOLD. Thus, the star schema defines a logical SALES cube with five dimensions and two measures for the analytic workspace.

ALTER TABLE times ADD 
   ( days_in_day NUMBER(1) DEFAULT 1,
     days_in_week NUMBER(1) DEFAULT 7 );

Defining the Logical Sales Cube for Sales History

The definition of a cube involves decisions that affect performance. Unlike Global, the Sales History data set is fairly large and sparse like most real data sets. It is a good candidate for using the Sparsity Advisor. The Sparsity Advisor analyzes the sparsity characteristics of the data as it is stored in the relational source tables.

Run the Sparsity Advisor. Be sure that the data type is set to DECIMAL, then re-create the cube using the recommended settings.

Maintaining Sales History

When building the cube, submit the maintenance task to the job queue, either to run immediately or at a later time. If you are running Oracle Database on a single-processor computer, keep the number of processes at 1. Otherwise, check the value of JOB_QUEUE_PROCESSES to see how many jobs you can run simultaneously.