Figure 3: Analytic Workspace Manager 10g model view

Once the logical model is complete, you can use Analytic Workspace Manager 10g to map datasources to this logical model and then use the tool to populate and maintain your analytic workspace. Analytic Workspace Manager has an intuitive, simple interface that follows the logical workflow in creating a multidimensional OLAP cube, and creates OLAP cubes and dimensions that are immediately usable with OracleBI Discoverer for OLAP.

Let's walk through the process of creating a multidimensional OLAP cube for use with OracleBI Discoverer for OLAP. This example uses the GLOBAL Sample Schema, which you can download today from OTN.

Building the Analytic Workspace

Before you start using Analytic Workspace Manager 10g, first ensure that Oracle Database 10g Enterprise Edition has the OLAP Option installed and has Patch Set 2 (10.1.0.4) applied. Then set the database parameters to values appropriate for analytic workspace data loads:

PARALLEL_MAX_SERVERS 	= number of processors, less one
PGA_AGGREGATE_TARGET 	= 40% of available memory
UTL_FILE_DIR 		= all directories that OLAP will need to write to
UNDO_MANAGEMENT 		= AUTO
UNDO_TABLESPACE 		= as defined earlier
              

Now, create tablespaces and datafiles to hold your data:

CREATE TABLESPACE "GLOBAL"
    DATAFILE 'GLOBAL.DBF' SIZE 90M AUTOEXTEND ON NEXT 5M;

CREATE TEMPORARY TABLESPACE "GLOBAL_TEMP"
    TEMPFILE 'GLOBAL_TEMP.DBF' SIZE 90M AUTOEXTEND ON NEXT 5M
    UNIFORM SIZE 256K;
              

Then create the schema that will hold your source data and analytic workspace.

CREATE USER "GLOBAL"
    IDENTIFIED BY "GLOBAL" DEFAULT TABLESPACE "GLOBAL"
       TEMPORARY TABLESPACE "GLOBAL_TEMP"
    QUOTA UNLIMITED ON "GLOBAL"
    QUOTA UNLIMITED ON "GLOBAL_TEMP";

GRANT "OLAP_USER" TO "GLOBAL";
              

Then, using the Import utility, import the sample source data into the GLOBAL schema. For example,

IMP GLOBAL/GLOBAL
Import file: EXPDAT.DMP > GLOBAL_TABLES.DMP
Enter insert buffer size (minimum is 8192) 30720> 30720
List contents of import file only (yes/no): no > no
Ignore create error due to object existence (yes/no): no > no
Import grants (yes/no): yes > yes
Import table data (yes/no): yes > yes
Import entire export file (yes/no): no > yes
              

Start up Analytic Workspace Manager 10g, register your database, and log on as the GLOBAL user. Right-click on the GLOBAL schema, select Create New Analytic Workspace, and name it GLOBAL.

Figure 4: Creating an analytic workspace

Creating your first dimension. You now create the first dimension, CUSTOMER. Click on the GLOBAL analytic workspace, right-click on the dimension node, and select Create Dimension.

The CUSTOMER dimension has two hierarchies, SHIPMENTS and MARKET SEGMENT. The first step, however, is to define all the levels used by the two hierarchies; then you'll organize them into separate hierarchies.

Expand the CUSTOMER dimension, right-click on levels, and create the following levels:

• TOTAL_CUSTOMER
• TOTAL_MARKET
• REGION
• WAREHOUSE
• SHIP TO
• MARKET_SEGMENT
• ACCOUNT

Right-click on the hierarchies node, and create the SHIPMENTS hierarchy. Include in it (in order, from top to bottom) the levels TOTAL CUSTOMER, REGION, WAREHOUSE, and SHIP TO.

Figure 5: Creating the SHIPMENTS hierarchy

Then right-click on the hierarchies node again; create another hierarchy, MARKET SEGMENT; and include the levels (from top to bottom) TOTAL_MARKET, MARKET_SEGMENT, ACCOUNT, and SHIP TO. Once you've done so, your screen should look like this:

Figure 6: Viewing your two hierarchies

The data that will be used to populate this dimension is in a table called CUSTOMER_DIM in the GLOBAL schema, one of several dimension tables that will provide some of the data required for your analytic workspace. Click on the Mappings node in the Model View, and drill down to the GLOBAL schema. Click on and drag the CUSTOMER_DIM table onto the mapping pane, and select Star Schema from the Type of Dimension Table(s) drop-down list.

Because your source data for this dimension is held in a table in a star schema, you can take advantage of the Auto Map Star Schema button on the mapping toolbar to automatically map your source fields to the logical dimension model. When the data is mapped, your screen should look like this:

Figure 7: Mapping your dimension to source data

Importing templates. Next you create the PRODUCT dimension, but you don't do it manually. Rather, you bring the definition in by using a template file provided with the GLOBAL Sample Schema. Analytic Workspace Manager 10g allows you to save definitions for dimensions and cubes as an XML template, so you can, for example, save them in a version-control system or use them to deploy your logical model in a different location.

To import the definition, right-click on the dimensions node and then select Create dimension from template. Navigate to the directory where you unzipped the GLOBAL Sample Schema, and select the Product Other.XML file.

This file contains the definition for the PRODUCT dimension. Import the definitions, and then expand the dimension entry. Note that it has a single hierarchy with four levels, plus three additional attributes that belong to the ITEM level.

Within the mapping pane, note that source data has already been mapped to the PRODUCT dimension. In this instance, the source data is held in several normalized tables and the Type of Dimension Table(s) drop-down list is set to Other. With Analytic Workspace Manager 10g, your source data no longer has to be in a star schema, and tables arranged in a snowflake schema, or even regular normalized tables, are just as easy to use for datasources.

Figure 8: Mapping data from normalized tables

To complete the set of dimensions for the logical model, import the definitions for the TIME and CHANNEL dimensions, by importing the Time Star.XML and Channel Star.XML template files.

Designing your cubes. The next step is to design your cubes. Cubes are the part of your logical dimensional model that bring together measures and their dimensions; they are containers for measures directly loaded from your source data, along with any additional calculated measures, with each of the measures in the cube sharing the same set of dimensions. A logical model can have many cubes, but all of the dimensions in a cube must come from the same analytic workspace.

In this example, you will create a single cube that will contain measures for UNITS_SOLD and SALES. To create it, click on the Cubes node, right-click, and select Create Cube. Give the cube the name UNITS_CUBE, and select all four dimensions.

Figure 9: Defining the units cube

The Implementation Details tab enables you to specify how Analytic Workspace Manager 10g orders dimensions within the cube and whether it uses features such as compression and partitioning to improve the performance of data loading and aggregation.

The order in which you list the dimensions within a cube is important, due to the way the values for a measure are stored on disk. Measures within analytic workspaces are stored as a linear stream of data in which the values of what is termed the "fastest varying" dimension are clustered close together and the values of the "slowest varying" dimension are spread far apart. The fastest varying dimension should therefore be the dimension with the largest number of dimension members and needs to be listed as the first dimension in the cube, with the remaining dimensions being listed in size order, so that the last dimension, the slowest varying, is the last one.

The Implementation Details tab also lets you designate dimensions as being sparse or dense. Sparsity refers to the extent to which cells contain NA, or null, values, and your UNITS_CUBE is likely to be sparse along the PRODUCT, CHANNEL, and CUSTOMER dimensions, because not every product will have been sold to every customer through every channel. When you specify that two or more dimensions are sparse, Analytic Workspace Manager 10g generates a composite specification that implements these logical dimensions as a single composite and dimensions the measure by this composite instead of the separate dimensions. Storing data in this manner reduces the amount of disk space required for the analytic workspace when it is aggregated and improves the response time for user queries. See Oracle OLAP DML Reference 10g Release 1 (10.1.0.3) for more details on composites.

If you are aware that your cube is extremely sparse, you can check the Compression box and implement your cube by using a new feature, introduced with Oracle Database 10g, known as compressed composites. Compressed composites reduce redundant data within the measure and the composite, using a patented algorithm, and produce the smallest-possible measure, composite, and composite index and can dramatically reduce the amount of time required to aggregate a cube. The first release of this new feature has some limitations on when compressed composites can be used and some restrictions on aggregation, which are due to be relaxed with the release of Oracle Database 10g Release 2. If your cube is extremely sparse, however, you may get significant performance improvements by using this option. For more-detailed information on compressed composites, refer to the Analytic Workspace Manager 10g online help.

In your instance, your cube is only moderately sparse, so you should leave this option unchecked.

Two other tabs in the Create Cube dialog box allow you to specify how your measures are aggregated and to what extent summaries are precalculated and stored within the cube. As mentioned earlier, analytic workspaces, unlike relational OLAP implementations, embed summaries within the measure itself, aggregate navigation is performed automaticall, and you can decide how this aggregation is performed by using these two tabs.

On the Rules tab, you can choose either the default SUM aggregation for each logical dimension or one of a range of alternative aggregation methods possible with an analytic workspace, such as weighted first, weighted average, or nonadditive.

Figure 10: Setting aggregation rules

For this cube, though, only a simple SUM aggregation is required for each dimension, so you should leave the methods as the default settings.

Using the Summarize To tab, you can select the levels at which data in each dimension is presummarized. By default, Analytic Workspace Manager 10g preselects every other level in each dimension, which would normally represent an acceptable balance between the query response time and the time required to prepare a cube for query.

Now that the UNITS cube has been defined, expand the cube within the model view and right-click on the Measures node. Select Create Measure, and name it UNITS. Do the same again, and create another measure called SALES. You can choose to override the aggregation specification you previously defined for our cube, but for this example, you should accept the default value and inherit the specification for the parent cube.

The data that will be used to populate your UNITS and SALES measures is also in a table in the GLOBAL schema. Click on the Mappings node, and drop the GLOBAL.UNITS_HISTORY_FACT table onto the mapping pane next to the UNITS cube. Because the UNITS fact table is part of a star schema, click on the Auto Map Star Schema button to map the fact source columns onto your cube.

Figure 11: Mapping source data to the units cube

Note that instead of automatically mapping the fact table columns onto our UNITS cube, you could manually map each source field to the relevant measure or dimension level, using a technique familiar to users of Oracle Warehouse Builder.

As well as the UNITS and SALES measures that are sourced from tables in our source data, you will now create a calculated measure that will contain the amount by which sales have increased or decreased since the same time last year. To implement this calculated measure, right-click on the Calculated Measures node in the model view for our UNITS cube, name the calculated measure PCT_CHANGE_SINCE_LAST_YEAR, and select the Percent Difference from Prior Period calculation type from the Prior/Future Comparison folder.

Figure 12: Creating a calculated measure

On the next page of the wizard, select SALES as the measure for which you want the percent difference calculated, CALENDAR YEAR as the time hierarchy, and YEAR AGO as the date range.

Figure 13: Defining a time-series calculation

Click on Finish. The ease with which "time series" calculations can be created is one of the key benefits of using an analytic workspace, and Analytic Workspace Manager 10g allows you to create them without any need to know OLAP DML, the language used to program and interrogate analytic workspaces.

Loading the cube. Now that the OLAP dimensions, cubes, and measures are built and the source data is specified, you can load the cube. Right-click on the GLOBAL analytic workspace, and then select Maintain Analytic Workspace GLOBAL. Highlight and bring across all of the objects in the analytic workspace.

Figure 14: Selecting cubes and dimensions for building

On the next page, accept the default options, click on Next, and then ensure that the Run Maintenance Task Immediately In This Session checkbox is selected. Click on Finish, and Analytic Workspace Manager 10g loads source data into your objects.

At the end of the load, Analytic Workspace Manager 10g reports on the progress and results of each stage, and you should check at the end of the report to ensure that there have not been any errors in your load process.

Figure 15: Monitoring the progress of the cube build

You can now use Analytic Workspace Manager 10g to preview the data in your measures and dimensions. Right-click on the CUSTOMER dimension, and select View Data Customer. You can then view the dimension members in this logical dimension and navigate up and down the hierarchies and levels to check your data.

Figure 16: Viewing the customer dimension

Do the same with your UNITS measure. Note that values exist at all levels of the dimension hierarchies, some of which are precalculated as per your specification, with the rest being calculated "on the fly."

Figure 17: Viewing the units cube

Enabling your cube for OracleBI Discoverer for OLAP. Finally, OracleBI Discoverer for OLAP, instead of holding its metadata in an end user layer, stores its workbook definitions and permissions in the Discoverer Catalog. If you have not done so already, you'll now need to enable the GLOBAL user for access to the Discoverer Catalog, using Oracle Application Server Control.

Figure 18: Enabling the schema for the Discoverer catalog

Log on to Application Server Control, navigate to the Discoverer Catalog, and authorize the GLOBAL schema.

Analyzing Your Cube, Using OracleBI Discoverer for OLAP

You can now analyze your OLAP cube, using OracleBI Discoverer for OLAP. In a Web browser, start OracleBI Discoverer Plus, select OracleBI Discoverer for OLAP from the drop-down menu, wait for the page to refresh, and enter your connection details.

Figure 19: Connecting to OracleBI Discoverer for OLAP

Using the Workbook Wizard, create a new cross-tab and graph and select the measures you want in your worksheet. Note how dimensions are automatically added to your selection. Later you can choose which levels and attributes you want to display from each logical dimension.

Figure 20: The OracleBI Discoverer Workbook Wizard

Moving on through the Workbook Wizard, after you have chosen the graph you want to include in your worksheet, you can specify for each dimension which hierarchy and dimension members you want to include in the worksheet.

Once you have manually added dimension members to your selection, you can use the Conditions tab to conditionally add dimension members to or remove them from your selection. For example, first add all the WAREHOUSE-level dimension members to your selection, like this:

Figure 21: Selecting dimension members

Then, using the Conditions tab, keep the five warehouses with the top sales . Note how the wizard allows you to build up queries step by step and how the most-common condition types are already predefined for you—there's no need to learn complicated analytic SQL or a separate OLAP query language.

Figure 22: Adding a condition to the dimension selection

Now that you have refined your dimension member selection, complete the process by using the wizard to define your remaining dimension member selections.

Figure 23: Viewing the dimension member selection

Your OracleBI Discoverer workbook appears.

Figure 24: The completed OracleBI Discoverer for OLAP workbook

Using this combination of a dimensional model, an easy-to-use query builder, and the power of the analytic workspace, you can now build OracleBI Discoverer for OLAP workbooks that mirror the multidimensional way your business users think.

Using the Oracle Spreadsheet Add-in to Analyze Your Cube

Besides being able to display your OLAP data by using OracleBI Discoverer, you can also use the new Oracle Spreadsheet Add-in to import your data into Microsoft Excel. You can either download the Oracle Spreadsheet Add-in directly from OTN or install it as part of the Oracle Business Intelligence Tools bundle.

Once the Oracle Spreadsheet Add-in has been installed and Microsoft Excel has been loaded, navigate to the Oracle menu item and create a new query. Using the Connection Editor, create a connection to the database that holds your OLAP data. Then, in the OLAP Connection dialog box, connect to the global schema.

You will then be presented with the Query Wizard, which enables you to create an OLAP query much as you created your OracleBI Discoverer for OLAP query. Using the Query Wizard, select the SALES measure and apply the same conditions to the dimensions as before.

Figure 25: The Oracle Spreadsheet Add-in Query Wizard

When you have completed your query definition, your OLAP data appears in your spreadsheet, can be refreshed when required, and can be treated just like any other data in your spreadsheet.

Figure 26: Viewing OLAP data in Microsoft Excel

Summary

Adding the OLAP Option to OracleBI Discoverer gives you the ability to query and report against your data, using a logical dimensional model. The advantage of using a logical dimensional model is that it mirrors the way people picture their organizations.

By implementing your logical dimensional model with multidimensional analytic workspaces, you can take advantage of the query and aggregation optimizations in the OLAP engine that is now embedded in Oracle Database 10g. Analytic Workspace Manager 10g, now available as a free download from OTN, helps you quickly build analytic workspaces that enable you to use the OLAP capabilities of OracleBI Discoverer. Coupled with the Oracle Spreadsheet Add-in, OracleBI Discoverer, Oracle Database 10g, and the OLAP Option let you provide sophisticated, easy-to-use business intelligence for a whole range of users.

Next Steps Watch the Analytic Workspace Manager 10g online demo. Read the Oracle white papers Oracle OLAP 10g: Enhance Content and Improve Performance and Analytic Workspace Manager and Oracle OLAP 10g. Visit the Oracle BI & Data Warehousing Technology Center.

Now that you have built your first OLAP cube and analyzed it by using OracleBI Discoverer for OLAP, here are some other strategies you might like to try:

• Adding further calculated measures to your OLAP cube, using Analytic Workspace Manager 10g, including ones using time-series comparison or ranking against other members in the current dimension or other dimensions
• Displaying your worksheets over the Web, using OracleBI Discoverer Viewer
• Building a Web "performance dashboard," using Oracle Portal
• Applying new OracleBI Discoverer for OLAP features, such as stoplighting, to your workbooks
• Building more-complex workbooks and calculations, using the OracleBI Discoverer for OLAP Query and Calculation Wizards