| Oracle® OLAP Application Developer's Guide 10g Release 1 (10.1) Part Number B10333-02 |
|
|
View PDF |
| Oracle® OLAP Application Developer's Guide 10g Release 1 (10.1) Part Number B10333-02 |
|
|
View PDF |
This chapter introduces the powerful analytic resources available in an Oracle Database installed with the OLAP option. It consists of the following topics:
Multidimensional technology is now available within the Oracle Database. 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 the 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 is a "crystal ball" for decision makers whose ability to make sound decisions 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.
The types of analyses performed by applications that run against your data warehouse will help you decide whether to store the data entirely in analytic workspaces or distributed between analytic workspaces and relational tables.
Analytic workspaces provide an alternative to materialized views for generating and storing aggregate data. They provide complex aggregation methods that are not available in materialized views, such as weighted calculations, non-additive methods, and models. You might also choose analytic workspaces when you have storage issues concerning aggregate data. Analytic workspaces always present fully solved data to the application, regardless of whether the data is entirely pre-aggregated, partially pre-aggregated, or entirely aggregated on demand. The flexibility of the OLAP aggregation system enables you to pre-aggregate within the limitations of your data refresh window without compromising run-time response time. Moreover, analytic workspaces can store pre-aggregated data very efficiently.
You may also prefer to use analytic workspaces for applications that support predictive analysis functions, such as models, forecasts, and what-if scenarios. Moreover, analytic workspaces are highly optimized for performing single-row calculations, which they can compute at run-time to support custom measures.
A distributed solution may be optimal for query and reporting applications that use the advanced calculation capabilities of analytic workspaces less frequently. For these types of applications, you can create and populate analytic workspaces at run-time for more intensive analysis; the results can be sent directly to the analyst or written to relational tables. The implementation of a distributed model can, of course, vary widely since it encompasses solutions that range from storing all data in relational tables to storing all data in analytic workspaces.
The BI Beans can run against analytic workspaces or relational tables. If you do not plan to use analytic workspaces, then refer to the information in "Managing Data Sources for the BI Beans and OLAP API".
There are several levels at which you can work with analytic workspaces:
Graphical user interfaces (GUIs) provide wizards and property sheets for performing the basic tasks for creating and managing analytic workspaces. This topmost level formulates calls to the underlying SQL packages. Your introduction to developing and maintaining analytic workspaces is learning to use these GUIs.
SQL packages perform all the tasks needed to create, maintain, and expose analytic workspaces for use by applications. Some SQL packages work directly with workspaces and execute the underlying OLAP DML. Other SQL packages work with relational tables and views, and execute SQL.
Java packages can build and query analytic workspaces.
OLAP DML is the native language of analytic workspaces and implements all operations initiated at the other levels.
Installation of the OLAP option with the Oracle Database includes the following components:
The following applications can provide important functionality when working in OLAP, and are available online at the Oracle Web site:
All of these components and applications 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 where it can be manipulated by the OLAP engine. An analytic workspace is stored as a LOB table in a relational schema. Within a single database, many analytic workspaces can be created and shared among users. Like a relational schema, an analytic workspace is owned by a particular user ID, and other users can be granted access to it. Because individual users can save a personal copy of their alterations to a workspace, the workspace environment is particularly conducive to planning applications.
Analytic Workspace Manager provides a user interface for creating an analytic workspace in database standard form. This form enables the analytic workspace to be used with various tools that aggregate, refresh, and enable the data so that it is accessible to OLAP applications. These tools are also provided by Analytic Workspace Manager.
For more information about Analytic Workspace Manager, refer to Chapter 6, " Creating an Analytic Workspace ".
OLAP Worksheet is an interactive environment for working with analytic workspaces, similar to SQL*Plus Worksheet. It provides easy access to the OLAP DML, and enables you to perform sophisticated business analysis, such as modeling, forecasting, and allocation. 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.
For more information about OLAP Worksheet, refer to Chapter 9.
The SQL interface to OLAP provides access to analytic workspaces from SQL. The SQL interface is implemented in PL/SQL packages. These are the primary ones:
CWM2 is a large collection of packages for defining OLAP Catalog metadata. These packages support the BI Beans enabler in Analytic Workspace Manager.
DBMS_AW contains procedures for executing OLAP DML commands. This package supports OLAP Worksheet, and the property sheets and dialogs in Analytic Workspace Manager. Using the procedures and functions in the DBMS_AW package, SQL programmers can issue OLAP DML commands directly against analytic workspace data. They can move data from relational tables into an analytic workspace, perform advanced analysis of the data (for example, forecasting), and copy the results of that analysis into relational tables.
DBMS_AWM contains procedures for creating analytic workspaces. It supports the Create Analytic Workspace wizard in Analytic Workspace Manager.
DBMS_AW_UTILITIES contains procedures for creating and managing custom measures in a standard form analytic workspace. Custom measures are defined at run-time, and are calculated from stored measures.
For more information about these PL/SQL packages, refer to Chapter 7, " SQL Access to Analytic Workspaces " and the Oracle OLAP Reference.
OLAP DML is a mature low-level language that is native to analytic workspaces. It is the 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. It offers the maximum power and flexibility in acquiring, manipulating, and analyzing data.
If you are upgrading from Oracle Express, or if your data is stored in formats not supported by the higher level tools, then you may work directly in the OLAP DML at an early stage. Otherwise, you may use the OLAP DML directly only to enhance the functionality of your workspaces.
OLAP Catalog is the metadata repository provided for the OLAP option. It consists of write APIs, which are a set of PL/SQL procedures, and read APIs, which are relational views within the Oracle Database. The metadata describes data, which is presented as a star schema, in multidimensional terms such as cubes, measures, dimensions, and attributes. The OLAP Catalog is used to perform two distinct functions:
To create an analytic workspace from a star or snowflake schema.
To provide a Java application, which uses the BI Beans, with access to data stored in either an analytic workspace or relational tables. The BI Beans requires OLAP Catalog metadata. If data is not defined in the OLAP Catalog, then it is not available to applications that use the BI Beans.
The OLAP Catalog read APIs make the metadata that you have defined available to applications. They are useful to any application that uses SQL SELECT statements to run against views of analytic workspace data.
SQL applications do not require the use of the OLAP Catalog, but may benefit from using it. They can run against the logical objects that are defined in the OLAP catalog, without an awareness of where the underlying data resides.
The Analytic Workspace Java APIs provide a Java interface for the creation and maintenance of analytic workspaces. These APIs are an alternative to using the OLAP Catalog for defining an analytic workspace build.
|
See Also: Oracle OLAP Analytic Workspace Java API Reference |
The OLAP API is the Java-based programming interface for OLAP applications, and supports the BI Beans. The BI Beans are building blocks for developing analytic applications in Java, and are available for use with JDeveloper. If you are a Java developer, then you should consider using the BI Beans for your analytic applications. Note that the BI Beans are not included with the OLAP option, but they require an OLAP-enabled Oracle Database.
Oracle Enterprise Manager is a system management tool that provides you with an integrated solution for managing Oracle products without formulating complex SQL commands. You can use Enterprise Manager to set up user accounts, define tablespaces, monitor performance, and do other administrative tasks associated with your database, including the OLAP option.
The OLAP Management tool is part of the Enterprise Manager support for data warehouses. Using a graphical user interface, you can define logical metadata dimensions, measures, and cubes in the OLAP Catalog for the dimension tables and fact tables of a star or snowflake schema that complies with the database requirements for creating a dimension.
For more information about the OLAP Management tool, refer to Chapter 5, " Defining a Logical Multidimensional Model".
Oracle Warehouse Builder can extract data from many different sources, transform it into a star schema in the relational database, generate OLAP Catalog metadata, and create an analytic workspace. Warehouse Builder provides an alternative to using the OLAP Management tool in Enterprise Manager, and the Create Analytic Workspace wizard in Analytic Workspace Manager. The resulting analytic workspace is in database standard form, so you can then use Analytic Workspace Manager to aggregate, enhance, and enable your data.
If your data requires transformation, then Oracle Warehouse Builder provides the best method for generating an analytic workspace. Once you have created a logical model for your data warehouse, Oracle Warehouse Builder requires only a few extra steps to generate an analytic workspace in addition to a star schema.
|
See Also: Oracle Warehouse Builder User's Guide |
Analytic 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:
Define a logical multidimensional model in the metadata, and map the logical objects to physical data sources. See Chapter 5.
Create and populate an analytic workspace. See Chapter 6.
Generate information-rich data using aggregation, allocation, modeling, forecasting, and other analytic methods. See Chapter 6, Chapter 9, and Chapter 10.
Generate relational views of the analytic workspace. See Chapter 6.
Define metadata specifically for use by particular applications. See Chapter 6.
Periodically refresh the analytic workspace with new data. See Chapter 6.
Calculate custom measures and dimension members. See Chapter 7 and Chapter 9.
Table 1-1 identifies the tools available for performing each stage. Using these tools to perform the various stages of creating and managing analytic workspaces is the topic of this guide.
Table 1-1 Tools for Working With Analytic Workspaces
| Stage | Tools |
|---|---|
| Design a logical model and map it to data sources | Oracle Enterprise Manager CWM2 Write APIsOracle Warehouse Builder Analytic Workspace Java APIs |
| Create and populate an analytic workspace | Analytic Workspace Manager wizards DBMS_AWM PL/SQL packageOracle Warehouse Builder Analytic Workspace Java APIs |
| Generate information | Analytic Workspace Manager wizards DBMS_AWM PL/SQL packageAnalytic Workspace Java APIs OLAP DML |
| Create views | Analytic Workspace Manager enablers OLAP_TABLE function |
| Generate metadata for views | Analytic Workspace Manager enablers CWM2 Write APIs |
| Generate custom measures | DBMS_AW_UTILITIES PL/SQL packageDBMS_AW PL/SQL packageOLAP_TABLE functionAnalytic Workspace Java APIs OLAP DML |
| Refresh the data | Analytic Workspace Manager wizards DBMS_AWM PL/SQL packageAnalytic Workspace Java APIs OLAP DML |
|
 Copyright © 2002, 2003 Oracle Corporation All Rights Reserved. |
|
