TECHNOLOGY: Business Intelligence
Building Analytic Workspaces
By Dan Vlamis
Use Oracle AWM 10g to build analytic workspaces.
The Oracle OLAP (online analytical processing) option of Oracle Database 10g Enterprise Edition provides advanced analytic features to help you to summarize, analyze, and calculate data faster than or in ways not possible with standard SQL. Oracle OLAP can access data stored in relational tables but also includes a special storage structure called an analytic workspace that manages multidimensional objects specially optimized for certain types of business intelligence analyses.
Data in the analytic workspace is stored in a large object binary (LOB) and is managed by Oracle OLAP. The data still resides in a tablespace, is fully integrated into the rest of the Oracle database, and is accessible by SQL as well as other tools specifically designed for querying multidimensional data, such as Oracle Discoverer Plus OLAP, Oracle Spreadsheet Add-In, and Oracle Enterprise Planning and Budgeting.
Analytic Workspace Manager (AWM) for Oracle OLAP 10g makes dimensional modeling accessible to just about anybody in the organization. With it, a departmental DBA or a business intelligence specialist can design a multidimensional model, map that model to a series of relational tables (and/or other datasources), and populate an analytic workspace that implements that multidimensional model. To illustrate how easy it is to build an analytic workspace, I'll create one using the fictional Global Computing Company's dimensional model.
This article shows you how to create and maintain an analytic workspace, using AWM version 10.1.0.4. Note that in order to create this analytic workspace, you need to have Oracle Database 10g Enterprise Edition, the Oracle OLAP option, and Oracle AWM installed.
The Multidimensional Model
The multidimensional model is not designed for collecting data; it is designed to help business users retrieve data in a meaningful way. A benefit of this design is that queries against the multidimensional model map easily to actual business queries. For example, a sales manager may ask, "How do the year-to-date sales for our top five products compare across all of the regions from last year to this year?" or "For which of our products with last-year sales greater than 1 million units have sales declined more than 10 percent from last year?"
By using Oracle OLAP, you can formulate queries and business analyses against your data in a natural business context—a multidimensional context. This multidimensional model allows for powerful cross-dimensional analyses that are difficult (or impossible) to specify using standard SQL.
Building a business intelligence system is often an iterative process. You may build a pilot system with a subset of the data, and the pilot system helps you fine-tune your multidimensional model. It is very simple to build successive iterations of your business intelligence system with AWM, changing your dimensional model as you gain more experience with the tool. You may also discover that you do not have all of the data readily available that you would like to include in your business intelligence system; you may need to create new columns or tables to support your design.
In order to focus on the process of building the analytic workspace, all necessary data for this article is included in the series of tables supplied in a dump file in the sample download.
The first step in defining an analytic workspace is to define the multidimensional model for the problem you are trying to solve. The multidimensional model allows you to formulate your queries—and therefore create your model—by mapping business concepts you know to logical objects, including dimensions, levels, hierarchies, attributes, and measures.
The sample data for this article reflects Global Computing Company's distribution of computer hardware and software products. These sales are collected on a monthly basis and are broken down by CHANNEL, CUSTOMER, PRODUCT, and TIME. Each of these becomes a dimension in the multidimensional model. Each dimension can have multiple levels that comprise one or more hierarchies .
Multidimensional data is logically organized into a cube or a series of cubes. (Those familiar with data warehouses know that a cube is generally loaded from a single fact table.) Cubes are logical objects that contain the data (measures) in your analytic workspace. Measures (typically numeric) in a cube are broken down by dimensions and contain the actual data.
This article focuses on Global Computing Company's SALES_CUBE. This cube has measures SALES and UNITS. SALES is the dollar amount sold through each CHANNEL to each CUSTOMER for each PRODUCT for each MONTH. Similarly, UNITS contains the number of items sold, broken down by these same dimensions.
Using Oracle AWM to Build Analytic Workspaces
To follow along with the example in this article, download the file, extract this zip file into a temporary directory, and import the file global_tables.dmp into the GLOBAL schema. (I will use the files with the XML extension later, in the "Creating Dimensions from Template Files" section.) I like to keep the relational data in a schema separate from the multidimensional data, so define a user GLOBAL_AW and grant the SELECT privilege for the tables in GLOBAL schema to the user GLOBAL_AW . GLOBAL_AW user will need the following rights: CONNECT , RESOURCE , and OLAP_USER .
To launch Oracle AWM 10.1.0.4, run the Oracle AWM executable (awm\bin\awm.exe in Microsoft Windows or awm/bin/awm in Linux) in the directory where you installed Oracle AWM. Oracle AWM starts; in the left pane is the navigation tree .
Create a connection to your Oracle instance, by right-clicking on Databases in the navigation tree and choosing Add Database to tree .... Enter a description for your connection and a TNS alias or the hostname:port:sid (for example, DEVSRVR:1521:orcl ), and click on Create . (The next time you start Oracle AWM, you won't need to create this connection.) Expand your newly created connection, enter global_aw as the username and password, and click on OK . Expand Schema->GLOBAL_AW , right-click on Analytic Workspaces , choose Create Analytic Workspace , enter GLOBAL as the Name, and click on Create . Your analytic workspace has been created.
You create your dimensional model directly in Oracle AWM. I will describe in detail how to define the PRODUCT dimension, and then I'll use a shortcut to define other dimensions (CHANNEL, CUSTOMER, and TIME). To create a dimension, expand the GLOBAL_AW schema you just created and right-click on Dimensions . Enter Product as the name of the dimension. Note that as you type in the Name field, everything you type is uppercased. The Description, however, is shown in mixed case. Underscores are automatically replaced with spaces in the Description field.
The PRODUCT dimension has several levels that comprise a roll-up structure. Figure 1 shows the levels of the PRODUCT dimension as they will appear later in the PRODUCT Dimension dialog box when you choose View Data PRODUCT (see the "Viewing the Analytic Workspace" section) after you have defined and populated the PRODUCT dimension.
To add these levels to the dimension, expand the PRODUCT dimension in the navigation tree, right-click on Levels , and choose Create Level .... Enter the levels from top to bottom—in this case, Total_Product , Class , Family , and Item .
You need to add these levels to a hierarchy so you can aggregate up the hierarchy and so that users can drill down in the hierarchy. In the navigation tree under Product, right-click on Hierarchies and choose Create Hierarchy .... Enter Primary as the hierarchy name, click on >> to add all levels (in order) to the hierarchy, and click on Create .
Attributes help you select which dimension values to report or analyze. Bear in mind that the attribute's Long Description and Short Description are automatically added for each level in your dimension. You use these to label dimension values for crosstabs and graphs and when you're selecting which values to include. You can also add more attributes that show up in the Match tool, which the user will see in client applications such as Oracle Discoverer.
The PRODUCT_DIM dimension table includes columns for the Package, Buyer, and Marketing Manager for each Item (as shown in Figure 2). Add extra attributes— Package , Buyer , and Marketing_Manager —so that users can select Items by using these attributes. In the navigation tree, right-click on Attributes , choose Create Attribute ..., and enter Package as the attribute name. These attributes apply only at the lowest level, so uncheck the PRODUCT dimension and drill down until you see the ITEM level. Check the ITEM level, and click on Create . Create the Buyer and Marketing_Manager attributes the same way.
Mapping Dimension Objects
Once you have defined these dimensions and the associated levels, hierarchies, and attributes, you can map these to columns in a relational table or series of tables. Mappings provide the rules for populating the analytic workspace objects from relational tables.
To define the mappings for the PRODUCT dimension, click on the Mappings folder in the navigation tree (under Product). A schema tree appears to the right of the navigation tree. In the schema tree, expand the GLOBAL schema and then expand the Tables folder. Double-click on the PRODUCT_DIM table to add it to the mapping canvas on the right side of the screen. Draw lines from the relational source columns to the dimensional objects, by clicking and dragging from the source column to the appropriate dimensional object, as shown in Figure 2.
Note that in order to have enough screen space in the mapping window to complete the mapping, you may need to collapse the windows containing the navigation and schema trees by clicking on the top (left-pointing) arrow to the right of each window. (To restore either window, click on the bottom [right-pointing] arrow to the left of the window.)
Note in Figure 2 that no relational columns are mapped to the Short Description attributes. Short descriptions will be populated with the keys if they are not mapped to a relational column. Note also that you can click on the Auto Arrange Mappings button to change the order of the columns so lines do not cross. Click on Apply to save your mapping.
Creating Dimensions from Template Files
Next, define the other three dimensions: CHANNEL, CUSTOMER, and TIME. You could step through the same process I used for the PRODUCT dimension to create each of these other dimensions; define the dimensions, levels, hierarchies, and attributes; and then map these dimensional objects to the associated relational tables.
To save some time, however, set up the CHANNEL, CUSTOMER, and TIME dimensions by using template files. Template files are XML files that contain all of the metadata used to create and map a dimension. Template files for these three dimensions are included with the sample download for this article.
In the navigation tree under the GLOBAL schema, right-click on Dimensions and choose Create Dimension From Template .... Navigate to the directory containing the template files provided in the sample download, select Channel Star.XML , and click on Create . Create dimensions from the Customer Star.XML and Time Star.XML templates the same way. You can save the PRODUCT dimension created earlier to your own Product template file by right-clicking on the PRODUCT dimension and selecting Save Dimension to Template ....
Template files can be very useful if you need to move multidimensional metadata between servers or instances. Because the files are simply XML files, you can carefully edit these files for changes in schema names or other name changes.
Cubes are logical objects that contain the data in your analytic workspace. Often there is a one-to-one correspondence between a fact table and a cube. The cube contains a measure for each fact column in your fact table.
To create and define the cube for this example, right-click on Cubes and click on Create Cube ... in the navigation tree. Enter Sales_Cube as the cube name, and click on >> to add all dimensions to the cube.
You can use the Implementation Details tab to change how the data is stored for the cube. In this tab, you can change the order of dimensions, identify dimensions as sparse, and add compression for extremely sparse dimensions. The order of the dimensions and whether the Sparse check box is selected for certain dimensions can greatly affect the performance and storage requirements of your analytic workspace. If there are combinations of dimension values for which there is no data, make those dimensions sparse dimensions. If the data is extremely sparse, use compression to avoid storing the same data values multiple times. Commonly with daily data, some products are not often sold to a given customer. In this case, you should use compression.
The data for Global Computing Company is stored monthly; in general, if a product is sold to a customer, it is sold in most months. As a result, you should leave the implementation details at their default values. Keep these issues in mind when you create cubes for your own data. Click on Create to create the cube.
Measures hold the data in your analytic workspace and are part of a cube. In the navigation tree under Cubes, expand SALES_CUBE and right-click on Create Measure .... Enter Sales for the first measure, and click on Create . Create the Units measure the same way, but click on the Implementation Details tab, change the Data Type to INTEGER , and click on Create .
Mapping Cube Objects
To load data into your cube, you need to map the cube to the relational source for the data—typically a fact table.
In Oracle AWM, you map cubes in a similar manner to dimensions. To define the cube mappings for the SALES_CUBE , click on the Mappings folder under SALES_CUBE in the navigation tree. In the schema tree to the right of the navigation tree, expand the GLOBAL schema, expand the Tables folder, and double-click on the UNITS_HISTORY_FACT table to add it to the mapping canvas (on the right side of the screen). In order to have enough screen space in the mapping canvas to complete the mapping, you may need to collapse the windows containing the navigation and schema trees by clicking on the top (left-pointing) arrow to the right of each window.
Draw lines from the relational source columns to the dimensional objects, by clicking and dragging from the source column to the appropriate measure or dimensional object, as shown in Figure 3 (with the navigation tree window collapsed). Note that you can click on the Auto Arrange Mappings button to change the order of the columns so lines do not cross. Click on Apply to save your mapping.
Creating Calculated Measures
Analytic workspaces can include calculated measures that Oracle OLAP computes at runtime. Calculated measure definitions are stored in the analytic workspace as formulas.
Suppose you want to calculate an average sales price (sales dollars divided by units) that the user can display in addition to the base measures in your fact table. To create the Average Sales Price calculated measure, expand SALES_CUBE , right-click on Create Calculated Measure ..., click on Next at the first screen, and enter Avg_Sales_Price . There are many types of calculated measures. Expand the Basic Arithmetic group, select Division as the type, and click on Next . You can select base measures that are loaded from the fact tables, or other calculated measures. You want to divide Sales by Units , so select those measures from the drop-down lists, and click on Finish .
You can also create a much more complex calculation almost as easily. For example, you can quickly create your own calculated measure to help answer the question from the beginning of this article: "Which of our products with last year sales greater than 1 million units have declined more than 10 percent from last year?" To answer this question, go through the steps described in this section, defining the measure Pct_Chg_Sales_YrAgo by expanding the group Prior/Future Comparison, using the calculation type Percent Difference from Prior Period, and selecting Sales as the base measure.
These are just simple examples, but calculations can include time-based calculations such as year-to-date measures, index measures, share calculations, and many other types of calculations. One of the prime benefits of Oracle OLAP is the ease with which you can create these complex calculations.
Maintaining the Analytic Workspace
You have designed, created, and mapped the analytic workspace to relational tables, but the analytic workspace is not populated yet (much like a table that has no rows). To populate the analytic workspace with the dimension values and the data from your relational tables (using the mappings you defined earlier), you maintain the analytic workspace.
To populate the analytic workspace, right-click on GLOBAL , and choose Maintain Analytic Workspace GLOBAL . Click on SALES_CUBE , click on >> to select the cube, and click on Next twice.
Oracle AWM generates a SQL script that actually performs the maintenance. You can send this script to the Oracle Job Queue and run it with multiple parallel processes for greater performance. The cube for this article example is small enough that it simply can be maintained immediately while you wait (the default). Click on Finish to run the generated SQL script. The analytic workspace should load and aggregate (depending on hardware) in less than 10 minutes. A build log appears in a window when the process is completed. You can view the results as the maintenance process is running by selecting from the table OLAPSYS.XML_LOAD_LOG . The maintenance process has completed successfully when you see the message "Completed Build(Refresh)..." in the build log.
Viewing the Analytic Workspace
Once the analytic workspace is populated, you can view dimensions directly from Oracle AWM. Oracle AWM contains a mini-application built with BI Beans that can view dimensions and data while you're still in Oracle AWM. In the navigation tree, right-click on PRODUCT , and choose View Data PRODUCT ...; the PRODUCT Dimension dialog box—shown in Figure 1—appears. A portion of the BI Beans Query Bean runs under Oracle AWM and allows you to confirm that the descriptions and hierarchies look correct. You also can view cube data directly in Oracle AWM. To see the SALES_CUBE data, right-click on SALES_CUBE , and choose View Data SALES_CUBE .... The BI Beans Crosstab Bean displays the cube in a Measure Data Viewer window, as shown in Figure 4.
The data is live in the Measure Data Viewer. You can drill down, change your selections, and rotate the report by dragging and dropping dimensions. You can change your selections by clicking on the Query Builder icon—displayed in the upper-left corner of the Measure Data Viewer (shown in Figure 4).
In this article, I used Oracle AWM to create an analytic workspace that is immediately accessible by Oracle Business Intelligence products. These products—including Oracle Discoverer Plus OLAP, Oracle Spreadsheet Add-In, and Oracle Enterprise Planning and Budgeting—enable users to answer questions such as the ones at the beginning of this article without having to ask the IT department to add new columns, modify procedures, or write applications.
By substituting your own dimensions, levels, hierarchies, attributes, cubes, and measures, you can build your own analytic workspaces and experience the power of Oracle OLAP on your own data. With the capabilities of Oracle AWM, you can build the foundation of very capable business intelligence systems very easily.
Dan Vlamis (email@example.com) is president of Vlamis Software Solutions. He has been working with Oracle business intelligence products since 1986, gives presentations regularly at major Oracle conferences, and is a recognized Oracle OLAP expert.