| Oracle® OLAP Application Developer's Guide 10g Release 2 (10.2) Part Number B14349-02 |
|
|
View PDF |
| Oracle® OLAP Application Developer's Guide 10g Release 2 (10.2) Part Number B14349-02 |
|
|
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 is now available within 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 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 and the OLAP API are available for developing custom applications, as described in Chapter 5.
Both 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
The dimensional data model is an integral part of On-Line Analytical Processing, or OLAP. Because OLAP is on-line, it must provide answers quickly; analysts pose iterative queries during interactive sessions, not in batch jobs that run overnight. And because OLAP is also analytic, the queries are complex.
The dimensional data model is composed of logical cubes, measures, dimensions, hierarchies, levels, and attributes. The simplicity of the model is inherent because it defines objects that represent real-world business entities. Analysts know which business measures they are interested in examining, which dimensions and attributes make the data meaningful, and how the dimensions of their business are organized into levels and hierarchies.
Figure 1-3 shows the general relationships among logical objects.
Figure 1-3 Diagram of the OLAP Logical Dimensional Model
Logical cubes provide a means of organizing measures that have the same shape, that is, they have the exact same dimensions. Measures in the same cube have the same relationships to other logical objects and can easily be analyzed and displayed together.
Measures populate the cells of a logical cube with the facts collected about business operations. Measures are organized by dimensions, which typically include a Time dimension.
An analytic database contains snapshots of historical data, derived from data in a transactional database, legacy system, syndicated sources, or other data sources. Three years of historical data is generally considered to be appropriate for analytic applications.
Measures are static and consistent while analysts are using them to inform their decisions. They are updated in a batch window at regular intervals: weekly, daily, or periodically throughout the day. Some administrators refresh their data by adding periods to the time dimension of a measure, and may also roll off an equal number of the oldest time periods. Each update provides a fixed historical record of a particular business activity for that interval. Other administrators do a full rebuild of their data rather than performing incremental updates.
A critical decision in defining a measure is the lowest level of detail. Users may never view this base level data, but it determines the types of analysis that can be performed. For example, market analysts (unlike order entry personnel) do not need to know that Beth Miller in Ann Arbor, Michigan, placed an order for a size 10 blue polka-dot dress on July 6, 2002, at 2:34 p.m. But they might want to find out which color of dress was most popular in the summer of 2002 in the Midwestern United States.
The base level determines whether analysts can get an answer to this question. For this particular question, Time could be rolled up into months, Customer could be rolled up into regions, and Product could be rolled up into items (such as dresses) with an attribute of color. However, this level of aggregate data could not answer the question: At what time of day are women most likely to place an order? An important decision is the extent to which the data has been aggregated before being loaded into a data warehouse.
Dimensions contain a set of unique values that identify and categorize data. They form the edges of a logical cube, and thus of the measures within the cube. Because measures are typically multidimensional, a single value in a measure must be qualified by a member of each dimension to be meaningful. For example, the Sales measure has four dimensions: Time, Customer, Product, and Channel. A particular Sales value (43,613.50) only has meaning when it is qualified by a specific time period (Feb-01), a customer (Warren Systems), a product (Portable PCs), and a channel (Catalog).
A hierarchy is a way to organize data at different levels of aggregation. In viewing data, analysts use dimension hierarchies to recognize trends at one level, drill down to lower levels to identify reasons for these trends, and roll up to higher levels to see what affect these trends have on a larger sector of the business.
Each level represents a position in the hierarchy. Each level above the base (or most detailed) level contains aggregate values for the levels below it. The members at different levels have a one-to-many parent-child relation. For example, Q1-02 and Q2-02 are the children of 2002, thus 2002 is the parent of Q1-02 and Q2-02.
Suppose a data warehouse contains snapshots of data taken three times a day, that is, every 8 hours. Analysts might normally prefer to view the data that has been aggregated into days, weeks, quarters, or years. Thus, the Time dimension needs a hierarchy with at least five levels.
Similarly, a sales manager with a particular target for the upcoming year might want to allocate that target amount among the sales representatives in his territory; the allocation requires a dimension hierarchy in which individual sales representatives are the child values of a particular territory.
Although hierarchies are typically composed of levels, they do not have to be. The parent-child relations among dimension members may not define meaningful levels. For example, in an employee dimension, each manager has one or more reports, which forms a parent-child relation. Creating levels based on these relations (such as individual contributors, first-level managers, second-level managers, and so forth) may not be meaningful for analysis.
Hierarchies and levels have a many-to-many relationship. A hierarchy typically contains several levels, and a single level can be included in more than one hierarchy.
An attribute provides additional information about the data. Some attributes are used for display. For example, you might have a product dimension that uses Stock Keeping Units (SKUs) for dimension members. The SKUs are an excellent way of uniquely identifying thousands of products, but are meaningless to most people if they are used to label the data in a report or graph. You would define attributes for the descriptive labels.
You might also have attributes like colors, flavors, or sizes. This type of attribute can be used for data selection and answering questions such as: Which colors were the most popular in women's dresses in the summer of 2002? How does this compare with the previous summer?
Time attributes can provide information about the Time dimension that may be useful in some types of analysis, such as identifying the last day or the number of days in each time period.
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.
Analytic workspaces have several different types of data containers, such as dimensions, variables, and relations. Each type of container can be used in a variety of ways to store different types of information, including business measures and metadata.
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.
An analytic workspace initially contains only base-level data loaded from its data sources. Summary data is calculated in the analytic workspace, and the aggregates are stored with the base data in the same object. Aggregates can be stored permanently in the analytic workspace, or for the duration of an individual session, or only for a single query. Aggregation rules identify which aggregates are stored, and which aggregates are calculated on the fly.
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.
To implement an OLAP solution, you must create a data store using one of these designs:
Dimensional schema. An analytic workspace in database standard form
Relational schema. A star schema with metadata defined in the OLAP Catalog
For most DBAs, relational tables and SQL provide a familiar environment. On the other hand, analytic workspaces require transformation of the data and learning new concepts. So why use analytic workspaces? The answer is simple: The powerful analytics and run-time performance of analytic workspaces often provide the best support for many types of decision-making. Nonetheless, relational schemas are the best choice for some other situations.
The following sections identify data characteristics that are best handled by analytic workspaces, and those that may be handled better by relational schemas. Your situation may not fit perfectly into one category, so you will need to weigh the relative importance of each characteristic as well as the long-range plans for your enterprise. For example, if your data store primarily supports the generation of routine reports, with some exploratory reporting, and this usage appears to be stable, then a relational schema might be the best choice. However, if usage is shifting toward fewer routine reports and more extensive exploratory ad-hoc querying, then you might choose an analytic workspace instead.
Analytic workspaces are the best choice for these requirements:
Exploratory, ad-hoc querying of all areas of the data
Advanced calculations such as models, forecasts, growth ratios, and trends
What-if scenarios
Time series calculations such as lead, lag, moving average, and year-to-date
Complex run-time calculations
High performance for calculating summary data and inter-row functions such as share calculations
Star schemas may be preferable to analytic workspaces for these requirements:
Predictable querying patterns and prepared reports
No advanced calculations (such as forecasts)
Infrequent complex run-time calculations
Measures with a large number of dimensions
Dimensions with few aggregate levels
The following topics explore the technical differences between dimensional and relational data stores that support these guidelines.
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.
Relational schemas can have much less structure, and the relationships among tables and views can be established on a query-by-query basis. This flexibility may be very important for some users, although it may result in reduced performance. OLAP Catalog metadata can be used to superimpose a dimensional data model on relational data stores. Nonetheless, a superimposed model does not provide the same performance as an integrated data model. The data storage design limits the opportunities the relational engine has for optimizing queries.
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.
For data stored in a relational schema, analytical operations are performed by SQL. The SELECT MODEL clause and the analytical functions (such as RANK, LEAD, and LAG) support calculations such as year-to-date totals and moving averages.
A basic characteristic of business analysis is hierarchically structured data; detail data is summarized at various levels, which allows trends and patterns to emerge. After the analyst has detected a pattern, he or she 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 the data much more efficiently than relational tables and return answer sets to ad-hoc queries as quickly as routine reports. Thus, analytic workspaces provide better support as ad-hoc querying and custom measures become more prevalent.
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. Analytic workspaces are optimized for multidimensional calculations, making these run-time summarizations extremely fast.
Relational schemas store aggregate data in materialized views. Queries can be issued directly against the source tables or the materialized views. When the queries are issued against the source tables, Oracle Database obtains stored aggregates for the answer set from the materialized views. Query rewrite is possible when the materialized views have been created using SQL similar to that used for the query. If the materialized views do not exist or the query cannot be rewritten, then the aggregates are calculated on the fly from the source tables.
A relational schema with a relatively small number of materialized views can support the reports that access a known slice of the data. However, extensive use of ad-hoc queries and user-defined custom measures create a random situation in which any part of the data store may be queried and summarized. A relational schema for OLAP may require hundreds of materialized views, which can result in degraded performance and an a significant increase in the size of the database.
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 Oracle9i analytic workspaces and Express databases.
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.
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.
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. 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 analytic workspaces.
The Analytic Workspace Java APIs support the creation and maintenance of analytic workspaces in Java. They provide a programmatic method for defining a logical dimensional data model and instantiating that model in an analytic workspace. These APIs are 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 a Oracle Database with the OLAP option.
OLAP Catalog provides OLAP applications with a query interface to data stored in star and snowflake schemas. It defines fact tables and dimension tables as logical dimensional objects such as measures, dimensions, hierarchies, levels, and attributes. OLAP Catalog consists of write APIs, which are a set of PL/SQL procedures, and read APIs, which are relational views within Oracle Database.
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:
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.
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 network schema, as described in Chapter 3. 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.
A 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.
Analytic 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 derived measures using the Calculation Wizard, which is described in "Creating Custom Measures".
OracleBI Discoverer Plus OLAP, OracleBI Spreadsheet Add-In, and custom OracleBI Beans applications can run directly against relational tables, either instead of or in addition to analytic workspaces.
These are the basic stages:
The first stage is defining the analysis requirements of your end users. By interviewing end users, 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 to analysts.
For OLAP tools to query data in a relational data warehouse, the source data must be in a star or snowflake schema that conforms to specific requirements.
If your source data does not conform to those requirements, then use Oracle Warehouse Builder to create a star schema.
OLAP tools query the OLAP Catalog; they do not issue queries directly against the data source. There are several methods of defining a logical dimensional model of a relational schema:
OLAP Management tool in Oracle Enterprise Manager Database Control
CWM2 PL/SQL procedures
Oracle Warehouse Builder
The storage format of your data determines which of these methods you can use.
|
See Also: Chapter 7 for a discussion of the requirements for using each method of defining OLAP Catalog metadata. |
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 by following these steps:
Rename the old analytic workspace so the upgraded analytic workspace has the original name, using the following syntax:
EXECUTE DBMS_AW.AW_RENAME('orig_name', 'temp_name');
Run the upgrade procedure, using the syntax:
EXECUTE DBMS_AW.CONVERT('temp_name', 'orig_name', 'tablespace');
Tip: Use a program such as SQL*Plus to execute these procedures. For their full syntax, refer to the Oracle OLAP Reference.
Delete the old analytic workspace (temp_name) using the PL/SQL DBMS_AW.AW_DELETE procedure.
EXECUTE DBMS_AW.AW_DELETE('temp_name');
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.
