3 Creating Dimensions and Cubes

Model View

The Model View enables you to define a multidimensional 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 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 multidimensional model.

Figure 3-1 shows the dimensions, cubes, and measures 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 from Express databases or modify custom applications. You should not use this view to manually change a standard form analytic workspace, because you may create inconsistencies in the metadata.

Basic Steps for Creating an Analytic Workspace

To create an analytic workspace:

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

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

3. Define the dimensions for the data.

   See "Creating Dimensions".

4. Define the cubes for the data.

   See "Creating Cubes".

When you have finished, you 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 Dimensional Objects

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

• Define measure folders to simplify access for end users.

  See "Defining Measure Folders".

• Support multiple languages by adding translations of metadata and dimension attributes.

  See "Supporting Multiple Languages".

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.

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.

2. Right-click Levels, then choose Create Level.

   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.

Figure 3-3 shows the creation of the Quarter level for the Time dimension.

Figure 3-3 Creation of the Quarter Level

Description of "Figure 3-3 Creation of the Quarter Level"

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 multiple levels 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.

2. Right-click Hierarchies, then choose Create Hierarchy.

   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.

Figure 3-4 shows creation of the Calendar hierarchy for the Time dimension. Time has only one hierarchy, so Calendar is the default hierarchy.

Figure 3-4 Creation of the Calendar Hierarchy

Description of "Figure 3-4 Creation of the Calendar Hierarchy"

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.

User-Defined Attributes

You can create additional "User" attributes that provide supplementary information about the dimension members.

To create an attribute:

1. Expand the folder for the dimension.

2. Right-click Attributes, then choose Create Attribute.

   The Create Attribute dialog box is displayed.

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

   Click Help for specific information about these choices.

4. Click Create.

   The new attribute appears as an item in the Attributes folder.

Figure 3-5 shows the creation of an attribute that pertains only to dimension members at the Quarter level of the Time dimension.

Figure 3-5 Creation of the Time Quarter of Year Attribute

Description of "Figure 3-5 Creation of the Time Quarter of Year Attribute"

Mapping Dimensions

Mapping identifies the relational data source for each dimensional object. After mapping a dimension to a column of a relational table or view, you can load the data. You can create, map, and load each dimension individually, or perform each step for all dimensions before proceeding to the next step.

Mapping Window

The mapping window has a tabular view and a graphical view.

• Tabular view. Drag-and-drop the names of individual columns from the schema navigation tree to the rows for the logical objects.

• Graphical view. Drag-and-drop icons, which represent tables and views, from the schema navigation tree onto the mapping canvas. Then you draw lines from the columns to the logical objects.

Click Help on the Mapping page for more information. When you are done mapping the dimension, click Apply.

Figure 3-6 shows the TIME dimension mapped in the tabular view. The toolbar appears across the top and the schema navigation tree is on the left.

Figure 3-6 Time Dimension Mapped in a Tabular View

Description of "Figure 3-6 Time Dimension Mapped in a Tabular View"

Source Data Query

You can view the contents of a particular source column without leaving the mapping window. The information is readily available, eliminating the guesswork when the names are not adequately descriptive.

To see the values in a particular source table or view:

1. Right-click the source object in either the schema tree or the graphical view of the mapping canvas.

2. Choose View Data from the shortcut menu.

Figure 3-7 shows the data stored in the TIME_QUARTER_DIM table.

Figure 3-7 Data in a Source Dimension Table

Description of "Figure 3-7 Data in a Source Dimension Table"

Loading Data Into Dimensions

Analytic Workspace Manager provides several ways to load data into dimensional objects. The quickest way when developing a data model is using the default choices of the Maintenance Wizard. Other methods may be more appropriate in a production environment than the one used here.

To load data into the dimensions:

1. In the navigation tree, right-click the Dimensions folder or the folder for a particular dimension, then choose Maintain Dimension.

   The Maintenance Wizard opens on the Select Objects page.

2. Select one or more dimensions from Available Target Objects and use the shuttle buttons to move them to Selected Target Objects.

3. Click Finish to load the dimension values immediately.

   The additional pages of the wizard enable you to create a SQL script or submit the load to the Oracle job queue. To use these options, click Next instead.

4. Review the build log, which appears when the build is complete. If the log shows that errors occurred, then fix them and run the Maintenance Wizard again.

   Errors are typically caused by problems in the mapping. Check for incomplete mappings or changes to the source objects.

Figure 3-8 shows the first page of the Maintenance Wizard. Only the Time dimension has been selected for maintenance. All the Time dimension members and attributes are fetched from the mapped relational sources.

Figure 3-8 Loading Dimension Values into the Time Dimension

Description of "Figure 3-8 Loading Dimension Values into the Time Dimension"

Displaying the Dimension Members

After loading a dimension, you can see its members by using the dimension viewer.

To display dimension members:

1. In the navigation tree, right-click the name of a dimension.

2. Choose View Data.

Figure 3-9 shows the Time dimension in the dimension viewer.

Figure 3-9 Displaying the Time Dimension

Description of "Figure 3-9 Displaying the Time Dimension"

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. The default attributes of a measure are inherited from the cube.

To create a measure:

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

2. Right-click Measures, then choose Create Measure.

   The Create Measure dialog box is displayed.

3. Complete the General tab of the Create Measure dialog box. Complete the other tabs to override the cube settings.

4. Click Create.

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

Figure 3-11 shows the General tab of the Create Measure dialog box.

Figure 3-11 Creating the Sales Measure

Description of "Figure 3-11 Creating the Sales Measure"

Mapping Cubes

You use the same interface to map cubes as you did to map dimensions, as described in "Mapping Dimensions".

To map a cube in the graphical view:

1. Define the cube and its measures.

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

   The Mapping Window is displayed in the right pane. You 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-12 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-12 Units Cube Mapped in Graphical View

Description of "Figure 3-12 Units Cube Mapped in Graphical View"

Choosing a Data Storage Strategy

The creation of a cube requires several decisions about data storage that affect the performance of the analytic workspace. These choices are on the Implementation Details tab for the cube. The Sparsity Advisor in Analytic Workspace Manager evaluates the data in the relational tables and recommends the appropriate settings. You can accept the recommendations or modify them before implementing them in the cube.

Choosing a Dimension for Partitioning

If your partitioning strategy is driven primarily by life-cycle management considerations, then you should partition the cube on the Time dimension. Old time periods can then be dropped as a unit, and new time periods added as a new partition. In Figure 3-14, for instance, the Quarter level of the Time dimension is used as the partitioning key.

If life-cycle management is not a primary consideration, then partition on whatever dimension the Sparsity Advisor recommends. If the source fact table is partitioned, then the cube is partitioned on the same dimension. Otherwise, the Sparsity Advisor develops a strategy designed to achieve optimal build and query performance.

To run the Sparsity Advisor:

1. Create a cube and map it to a relational data source.

2. In the navigation tree, right-click the cube and choose Sparsity Advisor.

   Wait while the Sparsity Advisor analyzes the cube. When it is done, the Sparsity Advisor for Cube dialog box displays the recommendations.

3. For compressed cubes, be sure to select a data type for the cube.

4. Look over the recommendations and make any changes.

5. Click Recreate Cube to implement the recommendations.

Figure 3-13 shows the Sparsity Advisor dialog box with settings that are typical for a real-world cube.

Figure 3-13 Storage Strategy for a Cube

Description of "Figure 3-13 Storage Strategy for a Cube"

Example of a Partitioned Dimension

The Partitioning Advisor might recommend partitioning at the Quarter level of the Calendar hierarchy 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 bottom partitions, in addition to the default top partition. The top 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).

Figure 3-14 illustrates a Time dimension partitioned by Quarter.

Figure 3-14 Partitioning Time by Quarter

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

Loading Data Into a Cube

You load data into cubes using the same methods as dimensions. However, loading and aggregating the data for your business measures typically takes more time to complete. Unless you are developing a dimensional model using a small sample of data, you may prefer to run the build in one or more background processes.

1. In the navigation tree, right-click the Cubes folder or the name of a particular cube.

2. Choose Maintain Cube.

   The Maintenance Wizard opens on the Select Objects page.

3. Select one or more cubes from Available Target Objects and use the shuttle buttons to move them to Selected Target Objects. If the dimensions are already loaded, you can omit them from Selected Target Objects.

4. On the Dimension Data Processing Options page, you can choose to do a complete or a partial refresh of the cube.

5. On the Task Processing Options page, you can submit the build to the Oracle job queue or create a SQL script that you can run outside of Analytic Workspace Manager.

   You can also select the number of processes to dedicate to this build. The number of parallel processes is limited by the smallest of these numbers: The number of partitions in the cube, the number of processes dedicated to the build, and the setting of the JOB_QUEUE_PROCESSES initialization parameter.

   Click Help for information about these choices.

6. Click Finish.

Figure 3-15 shows the build submitted immediately to the Oracle job queue.

Figure 3-15 Selecting the Task Processing Options

Description of "Figure 3-15 Selecting the Task Processing Options"

Displaying the Data in a Cube

After loading a cube, you can display the data for your business measures in Analytic Workspace Manager.

To display the data in a cube:

1. In the navigation tree, right-click the cube.

2. Choose View Data from the shortcut menu.

The Measure Data Viewer displays the selected measure in a crosstab at the top of the page and a graph at the bottom of the page. On the crosstab, you can expand and collapse the dimension hierarchies that label the rows and columns. You can also change the location of a dimension by pivoting or swapping it. If you wish, you can use multiple dimensions to label the columns and rows, by nesting one dimension under another.

• To pivot, drag a dimension from one location and drop it at another location, usually above or below another dimension.

• To swap dimensions, drag and drop one dimension directly over another dimension, so they exchange locations.

To make extensive changes to the selection of data, choose Query Builder from the File menu.

Figure 3-16 shows the Units cube in the Measure Viewer.

Figure 3-16 Displaying the Units Cube

Description of "Figure 3-16 Displaying the Units Cube"