Remove obstacles to OLAP when managing Oracle OLAP

OLAP options in Oracle9 I Database Release 2 introduce the analysis workspace and truly integrate the relational and multidimensional data types in the same Database. So far, the only way for developers and DBAs to use the analytic workspace is to use command line API calls to manually build them. However, with the latest Analytic Workspace Manager (AWM) version, you can better utilize the multidimensional features in Oracle OLAP options for the first time without complex command line programming.

In this article, I will introduce Analytic Workspace Manager and gradually describe how to create a multi-dimensional dataset stored in the analysis Workspace, which can be accessed through Oracle Business Intelligence (BI) beans, OracleAS Discoverer, or SQL. I will also describe how to add new metrics and formulas, and how to port existing Oracle Express databases.

Analytic Workspace Manager Concept

When Oracle OLAP options are enhanced to include the analysis workspace, olap dml, a command-driven API and command language, is also provided to help DBAs and developers create, maintain, and process data and objects in this new data storage tool. Unfortunately, no GUI-based tools were provided at that time to help with the work. Therefore, specialized skills and knowledge are required to effectively use the OLAP data storage tool.

AWM-a graphical tool used to analyze the creation and maintenance of the Workspace-a significant step towards improving this situation. You can download AWM from the Oracle OLAP/global/cn/products/bi/olap/olap.html page on OTN immediately. 4] patch 3085634 must be installed .)

AWM provides the following basic functions:

● Integrated graphical user interface.

● You can use Oracle Enterprise Manager or Oracle Warehouse Builder to create a multidimensional Relational OLAP dataset and transfer it to an analytic workspace.

● You can use menu options or scripts generated by AWN to refresh data, dimensions, and facts.

● Automatically create BI Bean views and related metadata required to access data using BI beans.

● Enable Discoverer to create end-user layer information and OLAP table views necessary for Discoverer to access OLAP data.

● Create an SQL olap table view that allows you to view and analyze workspace data through standard SQL.

To illustrate the advantages of AWM, let's look at an example. Recently, a customer spent several weeks trying to create an analytic workspace, multi-dimensional datasets and metrics, and using DBMS packages to implement BI beans. They have achieved limited success, but cannot view data correctly in BI beans. After AWM and necessary database patches are loaded, they can create a new AW, transfer the relational dataset to the new AW, and enable data for the BI Bean, after several hours of work, you can actually view their data.

Tutorial

AWM allows you to use two views: the Oracle OLAP directory view and the object view. The OLAP directory view allows you to intuitively view the OLAP directory metadata contained in the database. The object view allows you to view and maintain data residing in the analysis workspace. You can switch between the two views through the View menu.

The display includes a navigation panel and a display panel. When you select an object in the navigation panel, the display panel on the right displays information about the selected object. You can also right-click an object in the Navigation Pane and select an object from the menu options with corresponding operations on the object.

A tool called OLAP Worksheet opens an interactive session in the analysis workspace using olap dml. These two tools share the same session, so you can switch between the console and OLAP Worksheet while viewing the same data.

OLAP directory View

When you log on to AWM for the first time, the Oracle OLAP directory view is provided to you. This view lists the main directory objects: measurement folders, multi-dimensional datasets, and dimensions. Metadata cannot be created or modified in this view. To modify metadata, you must use the Oracle Enterprise Manager, Oracle Warehouse Builder, or CWM2 PL/SQL process. When an analytic workspace is created, the OLAP directory view displays multidimensional datasets in the workspace and relational cubes if they are enabled ). In addition, the aggregate plan is displayed and can be created from this view. Figure 1 is an OLAP directory view that shows a relational cube and a cube in an analytic workspace. The cursor is located on the relational multi-dimensional dataset (Audio Product Sales). Details are displayed on the right panel.

Object View

The object view provides a graphical navigator for the objects contained in the analysis workspace. Unlike the OLAP directory view, this view allows you to create and modify objects in the analysis workspace.

The object view is displayed. On the left is the object navigation panel, which is extended to display various analysis workspace and related object types provided, and on the right is the program that loads data into the cube. This view avoids the need to run olap dml commands to maintain objects.

OLAP Worksheet

To run olap dml or directly create or modify an analytic workspace or object by using olap dml, you can call OLAP Worksheet from the Tools menu. Figure 3 shows the OLAP Worksheet opened in edit mode. OLAP Worksheet is used to run olap dml commands and programs, as well as display and process objects in the analysis workspace. OLAP Worksheet is considered as the SQL Plus provided for OLAP.

Create an analytic workspace from a relational multi-dimensional dataset

The Create Analytic Workspace wizard allows you to Create a Workspace from one or more cubes in the OLAP directory. The generated workspace is in the standard format of the database, which is described in the "Next Step" in the olap api section of the Oracle9 I OLAP developer guide.

The Create Analytic Workspace wizard provides the appropriate default values. You can Create an Analytic Workspace without any decision. By accepting the default settings, you can create a physical storage model suitable for multiple data types. If you are new to Oracle OLAP analysis, you may want to create an analysis workspace with the default settings.

However, in the product system, good performance is crucial. To create a workspace with optimal performance, you must study the features of the data and set the advanced storage options as needed. You can use advanced storage to manually define a composite structure to allow control of insufficient space and storage space used by data. This can also improve the database performance. For example, AWM automatically creates a composite structure for each created cube. Composite Structure, for example, assuming that NODE is the fastest changing, and PRODUCT is the slowest changing. If this is not the case, you can build a composite structure to correctly reflect the actual data.

The process for creating a workspace in the standard format of a database is as follows:

Configure a database instance for OLAP. Define permanent, temporary, and undo tablespaces, and set database parameters to values suitable for data loads. Defines a user who will have the analytic workspace. Creating an analysis workspace in a different mode from the link source is a good practice, which prevents object name conflicts and allows better control of security and data access. Although you can create a workspace in the same mode as a relational table, this will cause problems when defining unique names in a single namespace. The user will be granted the OLAP_DBA role and the SELECT permission for the source data table. Open Analytic Workspace Manager and connect the user defined for this purpose to the database instance. If you want to generate a log file, select Configuration from the Tools menu. Click Help to obtain more information. In the Oracle OLAP directory view, make sure that you have defined dimensions, hierarchies, metrics, and cubes for the source data, and that you can access these logical objects from the current session.

From the Tools menu, select Create Analytic Workspace Using Wizard. Complete the wizard step. If you need to define a composite structure, make sure you have selected the advanced storage option. Click the Help button to obtain specific information about each step.

You can enable this workspace for BI Bean now or later. You may want to postpone enabling until the analytic workspace is enhanced with aggregate data and custom measurements. If you encounter problems during the build process and you have selected to enable it, you may encounter problems when deleting all metadata created during the activation process. You can always enable the workspace after the build and load processes are completed successfully.

Select Save from the File menu. This option submits all modifications made to the database during this session. If you select a build option to define an object without loading all the data, run the Refresh Analytic Workspace wizard when you are ready to complete the build. Now the analysis workspace has been created. You can view the objects created using the object view.

1. Effect of disk sorting on Oracle database performance (1)
2. Prospective role in Oracle Database Maintenance
3. Use Resource Manager to optimize Oracle Performance