| 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 presents the rich development environment and the powerful tools that you can use to create OLAP applications. It includes the following topics:
For information about SQL access to analytic workspaces, refer to Chapter 7, " SQL Access to Analytic Workspaces ".
Java is the language of the Internet. Using Java, an application developer can write standalone Java applications (which can be launched from a browser with Java's WebStart technology), or they can create HTML applications through servlets, JavaServer Pages (JSP), and Oracle User Interface XML (UIX), which access live data from an Oracle Database.
Java is the preferred programming language for an ever-increasing number of professional software developers. For those who have been programming in C or C++, the move to Java is easy because it provides a familiar environment while avoiding many of the shortcomings of the C language. Developed by Sun Microsystems, Java is fast superseding C++ and Visual Basic as the language of choice for application developers, for the following reasons:
Object oriented. Java enables application developers to focus on the data and methods of manipulating that data, rather than on abstract procedures; the programmer defines the desired object rather than the steps needed to create that object. Almost everything in Java is defined as an object.
Platform independent. The Java compiler creates byte code that is interpreted at runtime by the Java Virtual Machine (JVM). As the result, the same software can run on all Windows, Unix, and Macintosh platforms where the JVM has been installed. All major browsers have the JVM built in.
Network based. Java was designed to work over a network, which enables Java programs to handle remote resources as easily as local resources.
Secure. Java code is either trusted or untrusted, and access to system resources is determined by this characteristic. Local code is trusted to have full access to system resources, but downloaded remote code (that is, an applet) is not trusted. The Java "sandbox" security model provides a very restricted environment for untrusted code.
To develop an OLAP application, you can use the Java programming language. Java enables you to write applications that are platform-independent and easily deployed over the Internet.
The OLAP API is a Java-based application programming interface that provides access to multidimensional data for analytical business applications. The OLAP API uses OLAP Catalog metadata to access data that is stored either in the relational tables of a star or snowflake schema, or in views of an analytic workspace that has been enabled for its use.
Java classes in the OLAP API provide all of the functions required of an OLAP application: Connection to an OLAP instance; authentication of user credentials; access to data in the RDBMS controlled by the permissions granted to those credentials; and selection and manipulation of that data for business analysis.
The BI Beans simplify application development by providing these functions as JavaBeans. Moreover, the BI Beans include JavaBeans for presenting the data in graphs, crosstabs, and tables.
|
Note: Oracle JDeveloper and the BI Beans are not packaged with the Oracle RDBMS. |
Oracle JDeveloper provides an integrated development environment (IDE) for developing Java applications. Although third-party Java IDEs can also be used effectively, only JDeveloper achieves full integration with the Oracle Database and BI Beans wizards. The following are a few JDeveloper features:
Remote graphical debugger with break points, watches, and an inspector.
Codecoach feature that helps you to optimize your code
Generation of 100% Pure Java applications, applets, servlets, Java beans, and so forth with no proprietary code or markers
Oracle Database browser
|
Note: Oracle JDeveloper is an application and is not packaged with the Oracle RDBMS. |
The BI Beans provide reusable components that are the basic building blocks for OLAP decision support applications. Using the BI Beans, developers can rapidly develop and deploy new applications, because these large functional units have already been developed and tested — not only for their robustness, but also for their ease of use. And because the BI Beans provide a common look and feel to OLAP applications, the learning curve for end users is greatly reduced.
The BI Beans contain the following:
Presentation Beans display the data in a rich variety of formats so that trends and variations can easily be detected. Among the Presentation Beans currently available are Graph, Table, and Crosstabs.
Data Beans acquire and manipulate the data. The Data Beans use the OLAP API to connect to a data source, define a query, manipulate the resultant data set, and return the results to the Presentation Beans for display. Data Beans include a Query Builder and a Calculation Builder.
Persistence Services is a set of packages that support the storage and retrieval of objects in the BI Beans Catalog, not only so that you can save your work, but also so that you can share the work with others who have access to the Catalog.
The BI Beans can be implemented as a Java client or a thin client. Java clients best support users who do immersed analyses, that is, use the system for extensive periods of time with a lot of interaction. For example, users who create reports benefit from a Java client. Thin clients best support remote users who use a low bandwidth connection and have basic analytical needs. Thin clients can be embedded in a portal or other Web site for these users.
The OLAP API and the BI Beans use the OLAP Catalog to provide the information they need about multidimensional objects defined in an Oracle data warehouse, such as measures and dimensions. The BI Beans generates additional metadata to support its additional functionality. This additional metadata is called the BI Beans Catalog.
The Presentation Beans support navigation techniques such as drilling, pivoting, and paging.
Drilling displays lower-level values that contribute to a higher-level aggregate, such as the cities that contribute to a state total.
Pivoting rotates the data cube so that the dimension members that labeled a series now label groups, or the dimension members that labeled columns in a crosstab now label rows instead. For example, if products label the rows and regions label the columns, then you can pivot the data cube so that products label the columns and regions label the rows.
Paging handles additional dimensions by showing each member in a separate graph, crosstab, or table rather than nesting them in the columns or rows. For example, you might want to see each time period in a separate graph rather than all time periods on the same graph.
The Presentation Beans enable you to change the appearance of a particular display. In addition, the values of the data itself can affect the format.
Number formatting. Numerical displays can be modified by changing their scale, number of decimal digits and leading zeros, currency symbol, negative notation, and so forth. Currency symbols and scaling factors can be displayed in the column or row headers rather than in the cells.
Stoplight formatting. The formatting of the cell background color, border, font, and so forth can be data driven so that outstanding or problematic results stand out visually from the other data values.
Ranking. In ranking reports, the numerical rank of each dimension value, based on the value of the measure, is displayed.
The Graph bean presents data in a large selection of two- and three-dimensional business chart types, such as bar, area, line, pie, ring, scatter, bubble, pyramid, and stock market. Many of the 2D graphs can be displayed as clustered, stacked, dual-Y, percentage, horizontal, vertical, or 3D effect.
Bar, line, and area graphs can be combined so that individual rows in the data cube can be specified as one of these graph types. You can also assign marker shape and type, data line type, color, and width, and fill colors on a row-by-row basis.
The graph image can be copied to the system clipboard and exported in GIF and other image formats.
Users can zoom in and out of selected areas of a graph. They can also scroll across the axes.
The Crosstab bean presents data in a two-dimensional grid similar to a spreadsheet. Multiple dimensions can be nested along the rows or columns, and additional dimensions can appear as separate pages. Among the available customizations are: Font style, size, color and underlining; individual cell background colors; border formats; and text alignment.
Users can navigate through the data using either a mouse or the keyboard. They can insert rows and columns to display totals, and edit cells for what-if analysis.
The Table bean presents data in record format like a relational table or view. In contrast to the crosstab, the table display handles measures individually rather than as members of a measure dimension. Thus, each measure can be manipulated individually.
The Data Beans use the OLAP API to provide the basic services needed by an application. They enable clients to identify a database, present credentials for accessing that database, and make a connection. The application can then access the metadata and identify the available data. Users can select the measures they want to see and the specific slice of data that is of interest to them. That data can then be modified and manipulated.
The BI Beans offer wizards that can be used both by application developers in creating an initial environment and by end users in customizing applications to suit their particular needs. The wizards lead you step-by-step so that you provide all of the information needed by an application. The following are some of the tasks that can be done using wizards.
Building a query. Fact tables and materialized views often contain much more data than users are interested in viewing. Fetching vast quantities of data can also degrade performance unnecessarily. In addition to selecting measures, you can limit the amount of data fetched in a query by selecting dimension members from a list or using a set of conditions. A selection can be saved and used again just by picking its name from a list.
The BI Beans take advantage of all of the new OLAP functions in the database, including ranking, lag, lead, and windowing. End users can create powerful queries that ask sophisticated analytical questions, without knowing SQL at all.
Generating custom measures. You can define new "custom" measures whose values are calculated from data stored within the database. For example, a user might create a custom measure that shows the percent of change in sales from a year ago. The data in the custom measure would be calculated using the lag method on data in the Sales measure. Because a DBA cannot anticipate and create all of the calculations required by all users, the BI Beans enable users to create their own.
OLAP applications typically have object-oriented user interfaces where users manipulate objects that represent organized groupings of their data. Thus, there is a natural relationship between an object-oriented user interface and an object-oriented API such as the Oracle OLAP API. The OLAP API exploits this natural relationship by providing objects that match the end-user behavior that an application needs.
Object-oriented languages such as Java manipulate data by applying methods on objects. This approach enables the objects to maintain a current state and support incremental modifications to that state. This approach provides excellent support for common OLAP actions such as drill and rotate.
For example, a central activity for users of OLAP applications is refining queries. A user has a question in mind and devises a query to answer that question. In most cases, the initial results of the query prompt the user to want to dig deeper for a solution, perhaps by drilling to see more detailed data or by rotating the report to highlight correlations in the data. The OLAP API is able to use the result of one query as the input to the next query.
The OLAP API accesses the data through the OLAP Catalog, that is, the relational tables that contain OLAP metadata. The application does not need to be aware of whether the data is located in relational tables or in an analytic workspace, nor does it need to know the mechanism for accessing that data.
Oracle OLAP translates all queries from the OLAP API into SQL; when a query is issued through the OLAP API, the SQL generator in Oracle OLAP issues a SELECT statement against a relational table or view. This has several advantages for application developers:
The difficult task of writing the complex SQL needed to resolve multidimensional queries, and even more difficult task of optimizing that complex SQL, is left for Oracle OLAP to do. Application developers can be more productive writing in the OLAP API, which is designed for OLAP.
Updates to SQL and the OLAP DML will be incorporated into new versions of the OLAP API. Applications can make use of new analytic and performance features without recoding.
As an alternative access method, the OLAP API provides a way for a Java application to directly manipulate workspace data, without the need for any metadata and without the use of the OLAP API data manipulation classes. The Java application uses the SPLExecutor class in the OLAP API to send DML commands directly to Oracle OLAP for execution in the workspace.
Whichever access method is used, the application establishes a connection, opens the workspace, accesses the data (either through MDM metadata or through SPLExecutor), closes the workspace, and closes the connection.
The OLAP API generates SQL commands to select and manipulate data stored in the relational tables or views. When the data is stored in an analytic workspace, the computational power of the OLAP engine can be used to manipulate the data, including:
Modeling
Forecasting
What-if scenarios
When the data is stored in a star or snowflake schema, the SQL commands generated by the OLAP API can include the "N-pass" functions, such as RANK, PERCENTILE, TOPN, BOTTOMN, LAG, LEAD, SUM, AVG, MIN, MAX, COUNT, and STDDEV. Data fetches use many database innovations, including concatenated rollup, scrollable cursors, and query rewrite.
The OLAP API provides expanded calculation capabilities beyond those that can be handled efficiently in other OLAP solutions, such as:
Totals broken out by multiple attributes
Suppression of NA and zero rows, columns, and pages
Union dimensions
Measures as dimensions
Inter-row calculations such as the following book-to-bill ratio:
Balance(Account "BOOKED", Period "PRIOR")/ Balance(Account "BILLED", Period "LAST")
Asymmetric queries
Analytical queries are by nature iterative. An analyst formulates a query, sees the results, and then formulates other queries based on those results. Since the likelihood is very high in business analysis of needing the same data to answer subsequent queries, the OLAP API caches the metadata so that it is available throughout the session without fetching it again. Moreover, the OLAP API defines the result set of a query geometrically. Using multidimensional cursors, the OLAP API can randomly access disparate regions of the result set. This enables an application to retrieve just the data currently of interest instead of all of the data in the result set. For example, you might scroll to the end of a page without having to fetch all of the data on the page.
Applications built using the BI Beans and the OLAP API can have as a data source either an analytic workspace or a relational schema (star or snowflake). This guide is written primarily to describe the creation and management of analytic workspaces. However, the information for creating a relational data warehouse for use by the BI Beans and the OLAP API is also contained here.
Take these steps if you plan to use a star or snowflake schema as the data source for OLAP applications, and you do not plan to create an analytic workspace:
Create CWM1 or CWM2 metadata as described in "Overview of the OLAP Catalog".
Using a SQL command processor such as SQL*Plus, issue this command to make the metadata accessible to the BI Beans.
EXECUTE CWM2_OLAP_METADATA_REFRESH.MR_REFRESH
Create materialized views as described in Chapter 13.
|
 Copyright © 2002, 2003 Oracle Corporation All Rights Reserved. |
|
