3 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 and views in Oracle Database. The source columns can be star, snowflake, or any other schema design that supports the logical model.

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

Figure 3-1 Model View in Analytic Workspace Manager

Description of "Figure 3-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 Express databases, modify custom applications, or customize a new analytic workspace. Be very careful when working with a standard form analytic workspace, so that you do not create inconsistencies in the metadata.

Figure 3-2 shows the Object View. A formula named UNITS_CUBE_SALES is currently selected in the navigation tree, and the right pane shows the Expression tab of the property sheet. This tab shows the OLAP DML expression used to calculate the formula.

Figure 3-2 Object View in Analytic Workspace Manager

Description of "Figure 3-2 Object View in Analytic Workspace Manager"

OLAP Worksheet

OLAP Worksheet provides full use of the OLAP DML for users who need to manage the contents of an object or execute a program. It opens in a separate window from the Analytic Workspace Manager console. This window provides menus, a toolbar, an input pane for OLAP DML commands on the bottom, and an output pane on the top.

Figure 3-3 shows OLAP Worksheet opened from Analytic Workspace Manager. Notice that the GLOBAL workspace is attached with read/write access in both OLAP Worksheet (as shown by the AW LIST command) and Analytic Workspace Manager (as shown by the Model View navigation tree). The two applications share the same session.

The OLAP DML Reference is available through the Help menu.

Figure 3-3 OLAP Worksheet Opened From Analytic Workspace Manager

Description of "Figure 3-3 OLAP Worksheet Opened From Analytic Workspace Manager"

Installing Analytic Workspace Manager

Analytic Workspace Manager is distributed with Oracle Database. Three disks compose the Oracle Database 10g Release 2 installation set: Database (db), Companion, and Client. Analytic Workspace Manager is on the Client disk.

If you are installing on the same system as the database, then choose a Custom installation and install into the same Oracle home directory as the database. Select OLAP Analytic Workspace Manager and Worksheet from the list of components.

If you are installing on a remote system, then choose either an Administrator or a Custom installation.

Opening Analytic Workspace Manager

On Windows, use the Start menu to open Analytic Workspace Manager:

Start > All Programs > Oracle - Oracle_home > Integrated Management Tools > 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

Your goal in using Analytic Workspace Manager is to create a multidimensional data store that supports business analysis. The analytic workspace that you create must contain the logical objects described in "The Logical Dimensional Data Model". 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 3-4 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:

• PRODUCT_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 3-4 Star Schema

Description of "Figure 3-4 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 3-5 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 3-5 Normalized Time Dimension in a Snowflake Schema

Description of "Figure 3-5 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 3-6 shows the Product dimension in schema that contains the appropriate relationships. Contrast these 11 tables with the single table shown in Figure 3-5 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 3-6 Product Dimension in an "Other" Schema Design

Description of "Figure 3-6 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 a Standard Form Workspace

To create an analytic workspace in database standard form:

1. Configure your database instance for OLAP use. Define permanent, temporary, and undo tablespaces, 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.

   While you can create the workspace in the same schema as the relational tables, doing so can cause problems in defining unique names within a single namespace.

3. Examine the sparsity characteristics of your data, so that you can implement a logical model for the best performance.

   See "What is Sparsity?" and "Examining Sparsity Characteristics for GLOBAL".

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

5. 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.

6. Define the logical dimensions for the data.

   See "Creating Logical Dimensions".

7. Define the logical cubes for the data.

   See "Creating Logical Cubes".

8. Map the logical items to their data sources.

   See "Mapping Logical Objects to Data Sources".

9. Load the data.

   See "Mapping Logical Objects to Data Sources".

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, so that you can establish the order of calculations. Calculation plans enable you to include dependent calculations in the model.

  See "Creating Calculation Plans".

Creating Dimensions

Dimensions are lists of unique values that identify and categorize data. They form the edges of a logical cube, and thus of the measures within the cube. Analytic Workspace Manager supports these common dimension styles:

• List or flat dimensions have no levels or hierarchies.

• Level-based dimensions use parent-child relationships to group members into levels. Most dimensions are level-based.

• Value-based dimensions have parent-child relationships among their members, but these relationships do not form meaningful levels.

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 instead of 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 of the Create Cube dialog box.

   Important: Your decisions have a major impact on the performance of the analytic workspace. Click Help for specific information, and refer to "Making Data Storage Decisions".

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.

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.

In general, if a cube's detail-level data is more than 80 percent sparse, then you must manage sparsity by identifying the sparse dimensions in order to promote good performance.

SELECT COUNT(*) FROM units_history_fact WHERE units IS NOT NULL;

SELECT COUNT(ship_to_id) FROM customer_dim;

SELECT COUNT(month_id), COUNT(DISTINCT quarter_id), COUNT(DISTINCT year_id)
     FROM time_dim;

SELECT COUNT(DISTINCT a.month_id), COUNT(DISTINCT a.quarter_id), 
     COUNT(DISTINCT a.year_id) 
     FROM time_dim a, units_history_fact b
     WHERE a.month_id=b.month_id;

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. The first dimension in a cube is the fastest-varying dimension, and the last dimension is the slowest-varying dimension. The data for each measure in a cube is stored as a linear stream, in which the values of the fastest-varying dimension are clustered together and values of the slowest-varying dimension are spread far apart. Performance is optimized when values that are accessed together are stored together, because fewer pages must be swapped in and out of memory.

Data storage may be optimized for querying or loading. To optimize for loading, list the dense dimension (such as Time) before the sparse dimensions. If there is more than one dense dimension, then list the largest one first. To optimize for querying, you need to understand how your users are querying the data.

Partitioning Large Measures

Partitioning is an 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.

For partitioning a cube in Analytic Workspace Manager, you must choose a dimension and one of its levels as the basis for creating the partitions. 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 3-7 illustrates the Global Time dimension partitioned by Quarter.

Figure 3-7 Partitioning Time by Quarter

Description of "Figure 3-7 Partitioning Time by Quarter"

The number of partitions also affects the database resources that can be allocated to loading and aggregating the data in an analytic workspace. Partitions can be aggregated simultaneously when sufficient resources have been allocated, as described in "Maintaining the Data".

As this discussion explains, partitioning affects the extent to which an analytic workspace is optimized for querying or for maintenance. The fewer partitions, the more levels are precalculated (optimized for querying); the more partitions, the more levels are calculated on the fly, and the more resources can be allocated to loading and aggregating the data (optimized for maintenance).

Time is typically a good candidate for partitioning, because this choice supports life-cycle maintenance. Old time periods can be dropped as a unit in a partition, and new time periods can be added in a new partition. Moreover, the partitions will be approximately the same size because of the inherent regularity of the calendar. For example, in a calendar hierarchy, months have 28-31 children, quarters have 3 children, and years have 4 children.

However, some enterprises prefer to redeploy their analytic workspaces with new data instead of maintaining them. This is also the model used by Oracle Warehouse Builder. When life-cycle maintenance is not a factor, you should choose the most dense dimension for partitioning. The most dense dimension is frequently the one with the fewest members.

Defining Rules for Summarizing Data

Analytic Workspace Manager enables you to define summarization rules at three different levels. You can use whatever combination of levels best suits your needs:

• Cube. You can define default summarization rules for all measures in a cube. You define these rules when creating or modifying a cube.

• Measure. You can define unique summarization rules for a particular measure. These rules override the default cube summarization rules. You define these rules when creating or modifying a measure.

• Analytic Workspace. You can define rules for one or more measures and determine the order in which the measures are summarized. In this way, you can support dependencies among the measures. You define these rules when creating or modifying a calculation plan.

Regardless of the level at which you define the summarization rules, the decisions and the user interface are the same.

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 3-9 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 3-9 GLOBAL CHANNEL Dimension Mapped in Graphical View

Description of "Figure 3-9 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 derived 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 3-10 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 3-10 GLOBAL PRICE_AND_COST_CUBE Cube Mapped in Graphical View

Description of "Figure 3-10 GLOBAL PRICE_AND_COST_CUBE Cube Mapped in Graphical View"

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 follows best practices by creating the GLOBAL analytic workspace in a different schema from the source tables. Example 3-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.

CREATE USER "GLOBAL_AW" PROFILE "DEFAULT" 
    IDENTIFIED BY "global_aw" DEFAULT TABLESPACE "GLOBAL"
    TEMPORARY TABLESPACE "OLAPTEMP" 
    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;

Examining Sparsity Characteristics for GLOBAL

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

The Time dimension has 96 members. However, the last 17 months have no data. These time periods and their aggregates will be used initially for forecasting and later to store actual data. These additional time periods are excluded from the following calculations because they might skew the results in such a small data set.

The PRICE_AND_COST_HISTORY_FACT table has 2023 rows out of a possible 2844 dimension value combinations (79 historic months * 36 products). The Price cube is mapped to the PRICE_AND_COST_HISTORY_FACT table and is over 70% dense.

The UNITS_HISTORY_FACT table has 222,589 rows out of a possible 520,452 dimension value combinations (79 historic months * 36 products * 61 customers * 3 channels). The Units cube, which is mapped to UNITS_HISTORY_FACT, is over 40% dense.

Because the Global data set is dense, even a regular composite should not be used.

Identifying Levels for Precalculation

To identify the levels to be precalculated, you must know the number of dimension members at each level. You can easily acquire this information using either SQL statements or OLAP DML commands.

For example, this SQL statement:

SELECT COUNT(DISTINCT year_id) FROM global.time_year_dim;

and this OLAP DML command in the GLOBAL analytic workspace (after loading the dimension):

SHOW NUMLINES(LIMIT(time TO time_levelrel EQ 'YEAR'))

both return the number of TIME dimension members at the Year level.

Global is a very small data set, so few adjacent levels have a 10:1 ratio of dimension members. Table 3-1 identifies the levels to be calculated and stored in the analytic workspace.

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.

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 2 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).

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

• 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 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 2 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

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

On the Aggregation page, select the SUM operator for all dimensions. Use Table 3-1 to select levels for presummarization.

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 Cube box, then move Cubes to the Selected Source Objects column. Click Finish to run the job immediately.

Figure 3-11 shows the results of a query in OracleBI Discoverer Plus OLAP. **Replace this pic***

Figure 3-11 Discoverer Plus OLAP Displays Data from PRICE_AND_COST_CUBE

Description of "Figure 3-11 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 3-2 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. Examine the sparsity characteristics of the data and define the logical cube.

7. Map, load, and summarize the data.

8. 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 3-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. For the best performance, do not use the same tablespace that the star schema uses.

• 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 3-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 3-4 shows a script that is similar to the one used to create the GLOBAL_AW user in Example 3-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;

/* Create a directory object*/
CREATE OR REPLACE DIRECTORY sh_scripts AS '/users/oracle/sh_scripts'; 
GRANT ALL ON DIRECTORY sh_scripts TO PUBLIC;

/* 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 3-12.

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 many 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.

--Connect and set display parameters
CONNECT sh/sh
SET ECHO ON
SET LINESIZE 300
SET PAGESIZE 300
SET LONG     8000
SET SERVEROUT ON FORMAT WRAPPED
 
--Create a table for results
BEGIN
     dbms_aw.sparsity_advice_table('sh_sparsity_advice');
EXCEPTION
     WHEN OTHERS THEN NULL;
END;
/
 
TRUNCATE TABLE sh_sparsity_advice;
 
--Define program variables
DECLARE
     dimsources dbms_aw$_dimension_sources_t;
     dimlist VARCHAR2(500);
     sparsedim VARCHAR2(500);
     counter NUMBER(2) := 1;
     maxpart NUMBER(2);
     defs CLOB;
BEGIN
--Describe the dimension hierarchies
     dbms_aw.add_dimension_source('channel', 'channel_id', dimsources,
         'channels', dbms_aw.hier_levels,
          dbms_aw$_columnlist_t('channel_id', 'channel_class_id',
         'channel_total_id'));
     dbms_aw.add_dimension_source('product', 'prod_id', dimsources, 'products',
          dbms_aw.hier_levels, dbms_aw$_columnlist_t('prod_id',
         'prod_subcategory_id', 'prod_category_id', 'prod_total_id'));
     dbms_aw.add_dimension_source('customer', 'cust_id', dimsources, 
         'customers', dbms_aw.hier_levels,
          dbms_aw$_columnlist_t('cust_id', 'cust_city_id',
         'cust_state_province_id', 'cust_total_id'));
     dbms_aw.add_dimension_source('time', 'time_id', dimsources, 'times',
          dbms_aw.hier_levels, dbms_aw$_columnlist_t('time_id',
         'calendar_month_id','calendar_quarter_id', 'calendar_year_id'));
     dbms_aw.add_dimension_source('promotion', 'promo_id', dimsources,
         'promotions', dbms_aw.hier_levels,
          dbms_aw$_columnlist_t('promo_id', 'promo_subcategory_id',
         'promo_category_id', 'promo_total_id'));
 
--Analyze tables using default settings
   dbms_aw.advise_sparsity('sales', 'sales_cube', dimsources,
         dbms_aw.advice_default,        dbms_aw.partby_default, 'sh_sparsity_advice');
 
commit;
 
-- Get recommendations as OLAP DML commands 
select max(partnum) into maxpart from sh_sparsity_advice;
WHILE counter <= maxpart LOOP
     dimlist := dbms_aw.advise_dimensionality('sales_cube', sparsedim,
          'sales_cube_composite', counter, 'sh_sparsity_advice');
     dbms_output.put_line('Dimension list:  ' || dimlist);
     dbms_output.put_line('Sparse dimension:  ' || sparsedim);
counter := counter+1;
END LOOP;
dbms_aw.advise_dimensionality(defs,'sales_cube', 'sales_cube_composite',
     'DECIMAL', 'sh_sparsity_advice');
dbms_output.put_line('Definitions:  ');
dbms_aw.printlog(defs);
END;
/
-- Get recommendations directly from the output table 
COLUMN cubename          FORMAT a8
COLUMN fact              FORMAT a20
COLUMN dimension         FORMAT a12
COLUMN dimcolumn         FORMAT a12
COLUMN dimsource         FORMAT a12
COLUMN nmem              FORMAT 99999
COLUMN nleaf             FORMAT 99999
COLUMN advice            FORMAT a12
COLUMN pos               FORMAT 99
COLUMN density           FORMAT 9.9999
COLUMN partnum           FORMAT 99
COLUMN partby            FORMAT a20
COLUMN parttops          FORMAT a20
 
--Get basic information about the dimensions
SELECT DISTINCT(dimension), dimcolumn, position pos, membercount nmem, 
      leafcount nleaf
      FROM sh_sparsity_advice
      WHERE cubename='sales_cube'
      ORDER BY position;
 
--Get partitioning advice about the dimensions
SELECT dimcolumn, advice, partnum, parttops
      FROM sh_sparsity_advice
      WHERE cubename='sales_cube'
      ORDER BY partnum;
 
--Identify the partition tops
SELECT DISTINCT(calendar_year_id), calendar_year FROM times
      ORDER BY calendar_year;

Dimension list:  <sales_cube_composite<time channel product promotion customer>>
Sparse dimension:  DEFINE sales_cube_composite COMPOSITE COMPRESSED <time channel product promotion customer>
DEFINE sales_cube_pt PARTITION TEMPLATE <time channel product promotion customer> -
    PARTITION BY LIST (time) -
   (PARTITION p1 VALUES () <sales_cube_composite_p1<>> -
    PARTITION p2 VALUES () <sales_cube_composite_p2<>> -
    PARTITION p3 VALUES () <sales_cube_composite_p3<>> -
    PARTITION p4 VALUES () <sales_cube_composite_p4<>> -
    PARTITION p5 VALUES () <sales_cube_composite_p5<>>)
          .
          .
          .

SELECT distinct(dimension), dimcolumn, position, membercount, leafcount
     FROM sh_sparsity_advice
     WHERE cubename='sales_cube'
     ORDER BY position;
 
DIMENSION    DIMCOLUMN    POSITION MEMBERCOUNT LEAFCOUNT
------------ ------------ -------- ----------- ---------
time         time_id             1        1826      1460
channel      channel_id          2           5         4
product      prod_id             3          72        72
promotion    promo_id            4         503         4
customer     cust_id             5       55500      7059
 
SELECT dimcolumn, advice, partnum, parttops
     FROM sh_sparsity_advice
     WHERE cubename='sales_cube'
     ORDER BY partnum;
 
DIMCOLUMN    ADVICE       PARTNUM PARTTOPS
------------ ------------ ------- --------------------
time_id      COMPRESSED         1 1803
channel_id   COMPRESSED         1
prod_id      COMPRESSED         1
promo_id     COMPRESSED         1
cust_id      COMPRESSED         1
time_id      COMPRESSED         2 1805
channel_id   COMPRESSED         2
prod_id      COMPRESSED         2
promo_id     COMPRESSED         2
cust_id      COMPRESSED         2
time_id      COMPRESSED         3 1804
channel_id   COMPRESSED         3
prod_id      COMPRESSED         3
promo_id     COMPRESSED         3
cust_id      COMPRESSED         3
time_id      COMPRESSED         4 1813
channel_id   COMPRESSED         4
prod_id      COMPRESSED         4
promo_id     COMPRESSED         4
cust_id      COMPRESSED         4
time_id      SPARSE             5 1802
channel_id   SPARSE             5
prod_id      SPARSE             5
promo_id     SPARSE             5
cust_id      SPARSE             5
 
25 rows selected.
 
SELECT DISTINCT(calendar_year_id), calendar_year FROM times
      ORDER BY calendar_year;
 
CALENDAR_YEAR_ID CALENDAR_YEAR
---------------- -------------
            1802          1998
            1803          1999
            1804          2000
            1805          2001
            1813          2002

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. As defined in this example, Sales History can use up to six processes (1 cube + 5 partitions).