| Oracle® OLAP Application Developer's Guide, 10g Release 2 (10.2.0.3) Part Number B14349-03 |
|
|
View PDF |
| Oracle® OLAP Application Developer's Guide, 10g Release 2 (10.2.0.3) Part Number B14349-03 |
|
|
View PDF |
This chapter introduces the powerful analytic resources available in Oracle Database 10g installed with the OLAP option. It consists of the following topics:
Multidimensional technology has been available in the Oracle database since Oracle9i. Each release since then has provided enhanced integration, functionality, and performance. Organizations no longer need to choose between a multidimensional OLAP database and a relational database. By integrating multidimensional tables and an analytic engine into the database, Oracle provides the power of multidimensional analysis along with the manageability, scalability, and reliability of Oracle Database.
The integration of multidimensional technology in a relational database is important because maintaining a standalone multidimensional database is costly. It requires additional hardware and DBAs who are skilled at using the specialized administrative tools of the multidimensional database. Moreover, standalone multidimensional databases require applications that use proprietary APIs. This severely limits the number of applications that can be run against them, not only because fewer applications are available in these APIs, but because all the data that they run on must be transferred from the relational database to the multidimensional database. These requirements often force enterprises into supporting two sets of query and reporting tools, one for the relational database and the other for the multidimensional database.
In contrast, the OLAP option is fully integrated into the Oracle Database. DBAs use the same tools to administer this option as they use to administer all other components of the database. The DBA can decide the best location for storing and calculating the data as part of optimizing the operations of the database. A single application can access both relational and multidimensional data.
SQL-based applications can now use pure SQL against information-rich relational views of multidimensional data provided by an OLAP-enabled Oracle Database. OLAP calculations can be queried using SQL, enabling application developers to leverage their investment in SQL while expanding the analytic sophistication of their software to include modeling, forecasting, and what-if analysis. Standard reporting applications can present the results of complex multidimensional calculations, while ad-hoc querying tools such as custom aggregate members and custom measures can expand the analyst's range of calculation functions.
Relational databases provide the online transactional processing (OLTP) that is essential for businesses to keep track of their affairs. Designed for efficient selection, storage, and retrieval of data, relational databases are ideal for housing gigabytes of detailed data.
The success of relational databases is apparent in their use to store information about an increasingly wide scope of activities. As a result, they contain a wealth of data that can yield critical information about a business. This information can provide a significant edge in an increasingly competitive marketplace.
The challenge is in deriving answers to business questions from the available data, so that decision makers at all levels can respond quickly to changes in the business climate.
A standard transactional query might ask, "When did order 84305 ship?" This query reflects the basic mechanics of doing business. It involves simple data selection and retrieval of one record (or, at most, several related records) identified by a unique order number. Any follow-up questions, such as which postal carrier was used and where was the order shipped to, can probably be answered by the same record. This record has a useful life span in the transactional world: it begins when a customer places the order and ends when the order is shipped and paid for. At this point, the record can be rolled off to an archive.
In contrast, a typical series of analytical queries might ask, "How do sales in the Pacific Rim for this quarter compare with sales a year ago? What can we predict for sales next quarter? What factors can we alter to improve the sales forecast? What happens if I change this number?"
These are not questions about doing business transactions, but about analyzing past performance and making decisions that will improve future performance, provide a more competitive edge, and thus enhance profitability. The analytic database provides the information needed by decision makers whose ability to set goals today is dependent on how well they can predict the future. Getting the answers to these questions involves single-row calculations, time series analysis, and access to aggregated historical and current data. This requires OLAP -- online analytical processing.
Here are a few examples of common applications that can use the OLAP option to realize valuable gains in functionality and performance:
Planning applications enable organizations to predict outcomes. They generate new data using predictive analytical tools such as models, forecasts, aggregation, allocation, and scenario management. Some examples of this type of application are corporate budgeting and financial analyses, and demand planning systems.
Budgeting and financial analysis systems enable organizations to analyze past performance, build revenue and spending plans, manage to attain profit goals, and model the effects of change on the financial plan. Management can determine spending and investment levels that are appropriate for the anticipated revenue and profit levels. Financial analysts can prepare alternative budgets and investment plans contingent on factors such as fluctuations in currency values.
Demand planning systems enable organizations to predict market demand based on factors such as sales history, promotional plans, and pricing models. They can model different scenarios that forecast product demand and then determine appropriate manufacturing goals.
As this discussion highlights, the data processing required to answer analytical questions is fundamentally different from the data processing required to answer transactional questions. The users are different, their goals are different, their queries are different, and the type of data that they need is different. A relational data warehouse enhanced with the OLAP option provides the best environment for data analysis.
Analysts can choose between two query and analysis tools for selecting, viewing, and analyzing the data:
OracleBI Discoverer Plus OLAP is a full featured tool for business analysis that provides a variety of presentation options.
Discoverer Plus OLAP provides various wizards to guide power users through the entire process of building and publishing sophisticated reports containing crosstabs and graphs. They can choose from multiple layout options to create a visual representation of their query results. They can create queries, drill, pivot, slice and dice data, add analytic calculations, graph the data, share results with other users, and export their Discoverer reports in various data formats. Discoverer reports can also be published in dashboards where other users can access them from their browsers.
OracleBI Spreadsheet Add-In combines Oracle Database dimensional analytics with the capabilities of Microsoft Excel.
Spreadsheet Add-In enables analysts to work with live dimensional data in the familiar spreadsheet environment of Microsoft Excel. The add-in fetches data using an active connection to an OLAP data store, and displays the data in a spreadsheet. Users can use the add-in to perform OLAP operations such as drilling, rotation, and data selection.
In addition, OracleBI Beans is available for developing custom applications, as described in Chapter 4.
See Also:
Oracle Business Intelligence Concepts Guide, which is available at
http://www.oracle.com/technology/documentation/bi.htmlBoth Discoverer Plus OLAP and Spreadsheet Add-In use a dimensional data model so that analysts can formulate their queries in the language of business. Dimensions provide the context for the data. Consider the following request for information:
For fiscal years 2003 and 2004, show the percent change in sales for the top 10 products for each of the top 10 customers based on sales.
The sales measure is dimensioned by time periods, products, and customers. This request is articulated in business terms, but easily translates into a query in the language of dimensional analysis: dimensions, levels, hierarchies, and attributes.
Figure 1-1 shows a step in the Query Wizard in Discoverer Plus OLAP for selecting the top 10 products. The Query Wizard assists users in selecting by criteria, by value, and by saved selections. All OLAP tools provide a Query Wizard to assist users in formulating these queries.
Figure 1-1 Selecting Dimension Values By Criteria
Multidimensional data types facilitate the creation of custom measures. From the measures stored in your data warehouse, you can use numerous operators and functions to generate a wealth of information. Figure 1-2 shows a step in the Calculation Wizard of Discoverer Plus OLAP for calculating percent change in sales. Spreadsheet Add-In has the same Calculation Wizard. Both tools use the OracleBI Beans CalcBuilder.
Figure 1-2 Choosing a Calculation Method for a Custom Measure
Multidimensional data is stored in analytic workspaces, where it can be manipulated by the OLAP engine in Oracle Database. Individual analytic workspaces are stored in tables in a relational schema, and they can be managed like other relational tables. An analytic workspace is owned by a particular user ID, and other users can be granted access to it. Within a single database, many analytic workspaces can be created and shared among users.
Analytic workspaces have been designed explicitly to handle multidimensionality in their physical data storage and manipulation of data. The multidimensional technology that underlies analytic workspaces is based on an indexed multidimensional array model, which provides direct cell access. This intrinsic multidimensionality affords analytic workspaces much of their speed and power in performing multidimensional analysis.
Creating an analytic workspace involves a physical transformation of the data. The first step in that transformation is defining dimensional objects such as measures, dimensions, levels, hierarchies, and attributes. Afterward, you can map the dimensional objects to the data sources. The analytic workspace instantiates the logical objects as physical objects, and the data loading process transforms the data from a relational format into a dimensional format.
The analytic workspaces that are created by Oracle Warehouse Manager and Analytic Workspace Manager are in database standard form (typically called simply "standard form"). Standard form specifies the types of physical objects that are used to instantiate logical objects (such as dimensions and measures), and the type, form, and storage location of the metadata that describes these logical objects.
This metadata is exposed to SQL in the Active Catalog. The Active Catalog is composed of views of standard form metadata that is stored in analytic workspaces. These views are maintained automatically, so that a change to a standard form analytic workspace is reflected immediately by a change to the Active Catalog. Discoverer Plus OLAP and Spreadsheet Add-In use the Active Catalog to query data in analytic workspaces.
The dimensional data model is highly structured. Structure implies rules that govern the relationships among the data and control how the data can be queried. Analytic workspaces are the physical implementation of the dimensional model, and thus are highly optimized for dimensional queries. The OLAP engine leverages the model in performing highly efficient cross-cube joins (for inter-row calculations), outer joins (for time series analysis), and indexing. Dimensions are pre-joined to the measures.
For data stored in analytic workspaces, the OLAP calculation engine performs analytic operations and supports sophisticated analysis, such as modeling and what-if analysis. If you require these types of analysis, then you need analytic workspaces. The OLAP engine also provides the fastest run-time response to analytic queries, which is important if you anticipate user sessions that are heavily analytical.
A basic characteristic of business analysis is hierarchically structured data; detail data is summarized at various levels, which allows trends and patterns to emerge. An analyst who has detected a pattern can drill down to lower levels to identify the factors that contributed to this pattern.
The creation and maintenance of summary data is a serious issue for DBAs. If no summary data is stored, then all summarizations must be performed in response to individual queries. This can easily result in unacceptably slow response time. At the other extreme, if all summary data is stored, then the database can quickly multiply in size.
Analytic workspaces store aggregate data in the same objects as the base level data. Aggregates can be stored permanently in the analytic workspace, or only for the duration of an individual session, or only for a single query. Aggregation rules identify which aggregates are stored for each measure. When an application queries the analytic workspace, either the aggregate values have already been calculated and can simply be retrieved, or they can be calculated on the fly from a small number of stored aggregates. The data is always presented to the application as fully solved; that is, both detail and summary values are provided, without requiring that calculations be specified in the query. Analytic workspaces are optimized for multidimensional calculations, making run-time summarizations extremely fast.
Analytic workspaces provide an extensive list of aggregation methods, including weighted, hierarchical, and weighted hierarchical methods.
The OLAP option is installed with Oracle Database 10g Enterprise Edition. The following components are installed from the database (db) disk:
These components are installed from the client disk:
These OLAP components are described in the following paragraphs. The relationships among them are described throughout this guide.
The OLAP analytic engine supports the selection and rapid calculation of multidimensional data. The status of an individual session persists to support a series of queries, which is typical of analytical applications; the output from one query is easily used as input to the next query. A comprehensive set of data manipulation tools supports modeling, aggregation, allocation, forecasting, and what-if analysis. The OLAP engine runs within the Oracle kernel.
Analytic workspaces store data in a multidimensional format, as described previously in "About Multidimensional Data Stores". An analytic workspace is stored as a table in a relational schema. Individual workspace objects are stored in one or more rows as LOBs. This storage structure permits the analytic workspace to be partitioned and for multiple users to write to the analytic workspace simultaneously.
Analytic Workspace Manager provides an easy-to-use interface for creating and managing analytic workspaces in database standard form so they can be queried by OLAP tools. It enables you to develop a logical dimensional model of your data quickly and easily, map logical objects to relational data sources, and load and aggregate the data. Using Analytic Workspace Manager, you can manage the life cycle of your analytic workspaces. You can save the logical model as an XML file.
Analytic Workspace Manager also contains tools for upgrading from Oracle9i and Oracle Express Server.
OLAP Worksheet is an interactive environment for working with analytic workspaces, similar to SQL*Plus Worksheet. It provides easy access to the OLAP DML, which is the native language of analytic workspaces. You can switch between two different modes, one for working with analytic workspaces in the OLAP DML, and the other for working with relational tables and views in SQL. It is available through Analytic Workspace Manager or as a separate executable.
OLAP DML is the native language of analytic workspaces. It is a data definition and manipulation language for creating analytic workspaces, defining data containers, and manipulating the data stored in these containers. All other levels of operation (GUIs, Java, and SQL) resolve to the OLAP DML.
If you are upgrading from Oracle Express or you plan to develop all the tools for working with analytic workspaces, then you may work directly in the OLAP DML. If you plan to use Oracle OLAP tools and applications, then do not work directly in the OLAP DML; your manual changes may invalidate the metadata.
The SQL interface to OLAP provides access to analytic workspaces from SQL. The SQL interface is implemented in PL/SQL packages.
For more information, refer to the Oracle OLAP Reference.
The Analytic Workspace Java API supports the creation and maintenance of analytic workspaces in Java. It provides a programmatic method for defining a logical dimensional data model and instantiating that model in an analytic workspace. This API is used in Analytic Workspace Manager to create and modify analytic workspaces.
The OLAP API is a Java-based programming interface for OLAP applications, and it supports OracleBI Beans.
OracleBI Beans contains building blocks for developing analytic applications in Java, and it is available for use with JDeveloper. If you are an applications developer, then you will use OracleBI Beans in your OLAP applications. OracleBI Beans is not included with the OLAP option, but it requires Oracle Database with the OLAP option.
See Also:
Oracle OLAP Java API ReferenceAnalytic workspaces can be created in a variety of ways, depending on the characteristics of the data source and your own personal preference. However, the basic process is the same for all of them.
These are the basic stages:
The first stage of implementing an analytic workspace is defining the analysis requirements of end users. By interviewing them, you can identify the business analysis questions they want to answer with an OLAP application. With this information, you can determine the business measures that must be available, the base level at which the measures must be stored, and the types of data calculations that must be available.
See Also:
Chapter 3 for a sample approach to identifying business goals.To load data into an analytic workspace using OLAP tools, the source data must be in relational tables or views. The tables can be in a star, snowflake, or other schema, as described in Chapter 5. Analytic Workspace Manager supports direct mapping of logical objects to relational columns. If your relational data requires transformation, then you must define views that perform the transformations.
If your source data is not stored in relational tables or requires extensive transformation, then you can choose from one of these options:
Use Oracle Warehouse Builder to create a star schema from disparate data sources, then use Analytic Workspace Manager to create an analytic workspace from the relational data. Your Information Technology (IT) department may do this task for you. Choose this option when you are developing a new analytic workspace.
Use Oracle Warehouse Builder to create an analytic workspace, then use Analytic Workspace Manager to manage it. Choose this option when the design phase is complete and the analytic workspace is in a production environment. The IT department can manage this task along with its other maintenance tasks.
See Also:
Oracle Warehouse Builder User's GuideA logical dimensional model defines the dimensions, levels, hierarchies, attributes, cubes, and measures of your data. The Model View in Analytic Workspace Manager enables you to define the logical model by defining the individual objects and the relationships among them. When you save the definition of a logical object, Analytic Workspace Manager creates the physical objects in an analytic workspace that are needed to instantiate the logical object in database standard form.
See Also:
Chapter 5 for an introduction to the Model View of Analytic Workspace ManagerAnalytic Workspace Manager provides a graphical tool for mapping the logical objects to physical data stores. You can drag-and-drop tables and views from schemas to which you have access onto a mapping canvas. You can then draw lines from the appropriate columns to the logical objects that you have defined in the analytic workspace. Using a wizard, you can load data into the analytic workspace and aggregate the data using the rules that you provided.
As part of setting up an analytic workspace, you can define numerous calculated measures using the Calculation Wizard, which is described in "Creating Custom Measures". In addition, you can create forecasts, allocations, and post-load aggregations.
If you created an analytic workspace in Oracle 10g Release 1, you can upgrade it to Release 2 using the following procedure. Upgrading is optional. However, upgrading enables you to use the new features of Analytic Workspace Manager 10.2, such as additional aggregation operators for compressed composites, support for multiple languages, and performance improvements.
To upgrade an analytic workspace, take these steps:
Open Analytic Workspace Manager in the Model View.
In the navigation tree, select the name of the Oracle Database instance where your analytic workspace is stored.
On the Basic tab of the Database property sheet, verify that the database is running in 10.2 compatibility mode.
Right-click the analytic workspace, and select Upgrade Analytic Workspace to 10.2.
Complete the Analytic Workspace Upgrade to Version 10.2 dialog box.
Click Help for additional information.
If you have analytic workspaces that were created in Oracle9i, then you should upgrade them to take advantage of new features such as partitioning and compressed composites.
Upgrading may break custom OLAP DML programs. For this reason, you can choose to upgrade at a time that is convenient for you. You can continue to manage your older analytic workspaces by using an older version of Analytic Workspace Manager (such as Oracle9i Release 9.2.0.4.1).
Any new analytic workspaces that you create using the new Oracle Database 10g version of Analytic Workspace Manager will automatically be in 10g standard form, as long as Oracle Database is running in 10g compatibility mode.
If Oracle Database is running in 9i compatibility mode, then you will continue to work the same way as before without upgrading the analytic workspaces.
To upgrade an analytic workspace, take these steps:
Set the COMPATIBLE parameter to 10.0.0.0 or later in the database initialization file.
Upgrade the physical storage format.
Upgrade the standard form metadata.
You can upgrade the physical storage format without upgrading the standard form metadata, if you wish. This change will improve performance and support partitioning. However, the analytic workspace will not be enabled dynamically for OracleBI Beans until you upgrade the metadata.
You can perform the upgrade steps either in the Object View of Analytic Workspace Manager or in PL/SQL.
Convert the physical storage format by using either of these methods:
Recreate the analytic workspace by following these steps:
Export the contents to an EIF file.
Delete the old analytic workspace.
Create a new, empty analytic workspace.
Import the contents from the EIF file.
You can export and import in Analytic Workspace Manager. For more information, see these topics in Help: ÒExporting Workspace ObjectsÓ and ÒImporting Workspace ObjectsÓ
Use the PL/SQL conversion program:
EXECUTE DBMS_AW.CONVERT('aw_name');
Tip: Use a program such as SQL*Plus to execute this procedure. For the full syntax, refer to the Oracle OLAP Reference.
To upgrade the standard form metadata, follow these steps:
In Analytic Workspace Manager, open the Object View.
Expand the navigation tree until you see the name of the analytic workspace.
Right-click the analytic workspace and choose Upgrade Analytic Workspace From 9i to 10g Standard Form from the popup menu.
Upgrade to Release 2 by following the instructions in "Upgrading Oracle Database 10g Release 1 Analytic Workspaces".
Alternatively, you can use DBMS_AWM PL/SQL procedures CREATE_DYNAMIC_AW_ACCESS and DELETE_ALL_AW_ACCESS to perform the upgrade. Refer to the Oracle OLAP Reference for the syntax and usage notes.
