Skip Headers
Oracle® Application Server Best Practices Guide
10g Release 2 (10.1.2)
B28654-01
  Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

8 Oracle Business Intelligence

This chapter describes best practices for business intelligence. It includes the following topics:

8.1 Oracle Application Server Reports Services

This section describes best practices for Oracle Reports. It contains the following topics:

8.1.1 Leverage High Availability to Replace Separate Clustering Solutions for Each Component

A cluster is a virtual grouping of servers into a community for sharing the request-processing load efficiently across members of the cluster.

Because modern IT environments require high availability of the entire IT infrastructure, it is better to have a centralized high availability solution at the level of the application server, instead of a separate clustering solution for each component.

OracleAS High Availability Solutions provides the industry's most reliable, resilient, and fault-tolerant application server platform. Oracle Reports integration with OracleAS High Availability Solutions ensures that your enterprise-reporting environment is extremely reliable and fault-tolerant. With OracleAS High Availability Solutions providing a centralized clustering mechanism and several cutting-edge features, clustering in Oracle Reports is now deprecated. If you are using Reports Server clustering, switch to OracleAS High Availability Solutions in 10g Release 2 (10.1.2).

Implementation Details

To implement this best practice:

  1. Become familiar with the overview of enterprise deployment in the Oracle Application Server Enterprise Deployment Guide.

    An enterprise deployment is one of the Oracle Application Server configurations described in this guide, designed to support large-scale, mission-critical business software applications. The hardware and software in an enterprise deployment configuration delivers several benefits, including high availability.

  2. Become familiar with the overview of high availability and the Oracle Application Server High Availability Solutions framework.

  3. Set up high availability for the middle tier.

  4. Set up high availability for Oracle Reports, and other middle tier components.


    See Also:

    Chapter 5, "High Availability for Middle-tier Components," of the Oracle Application Server High Availability Guide

  5. Follow the steps in the other chapters of Oracle Application Server High Availability Guide to set up a complete high availability environment.

  6. Optionally, configure Oracle Reports Services Server Targets in Application Server Control Console.

    This step is mandatory if you plan to use Secure Socket Layer (SSL) to execute your reports.


    See Also:

    Chapter 8, "Installing and Configuring the myBIFCompany Application Infrastructure," in the Oracle Application Server Enterprise Deployment Guide

    This procedure completes the configuration for OracleAS High Availability Solutions. You are now ready to execute your reports. Table 8-1 lists the differences between running report requests using OracleAS High Availability Solutions, and running report requests using Reports Server clustering.

    Table 8-1 Running Report Requests Using OracleAS High Availability Solutions

    Method to Call Oracle Reports Using Reports Server Clustering (Before 10g Release 2 (10.1.2) Using OracleAS High Availability Solutions (In 10g Release 2 (10.1.2)

    URL

    In the URL, specify the hostname and port number of theOracle HTTP Server where the cluster is running. You can specify the cluster name in the URL using the command line parameter:

    server=clustername

    In the URL, specify the hostname and port number of the load balancer. The load balancer subsequently redirects the request to one of the nodes where Reports Server is running. Therefore, do not specify the Reports Server name in the URL, so that the request is served by the in-process server.

    RUN_REPORT_OBJECT (from OracleAS Forms Services)

    Specify the cluster name as the value for the report object property REPORTS_SERVER.

    Option 1: Start a Reports Server with the same name as the Reports Server cluster name. OracleAS Forms Services code remains same as 10g Release 1 (9.0.4).

    Option 2: Use the property REPORTS_SERVERMAP to map the cluster name to the Reports Server name, as described in Section 3.4.19, "Specifying the Network Configuration File," in the Oracle Application Server Reports Services Publishing Reports to the Web

    OracleAS Forms Services code remains the same as 10g Release 1 (9.0.4).

    WEB.SHOW_DOCUMENT (from OracleAS Forms Services)

    To get the job output with getjobid, specify the Reports Server cluster name in the URL using the command line option:

    server=clustername

    To get the job output with getjobid, specify the Reports Server cluster name in the URL using the command line option:

    server=clustername


    Table 8-2 compares Reports Server clustering with OracleAS High Availability Solutions.

    Table 8-2 Feature Comparison of Reports Server Clustering and OracleAS High Availability Solutions

    Using Reports Server Clustering (Before 10g Release 2 (10.1.2) Using OracleAS High Availability Solutions (In 10g Release 2 (10.1.2)

    Load balancing: The load for report requests is shared between multiple Reports Servers. Reports Server may either be on the same computer or on different computers.

    Load balancing: The load for report requests is shared between multiple nodes of an OracleAS Cluster, each node having one Reports Server.

    Failover: If one Reports Server fails, incoming jobs will be sent to other Reports Servers in the cluster. Thus, the failure of one Reports Server does not bring down the cluster. When any Reports Server fails, its current and scheduled jobs are not transferred to other Reports Servers, and these jobs are lost.

    See Also: Section 1.2.1, "Terminology," of the the Oracle Application Server High Availability Guide

    Failover: If one node containing a Reports Server fails, incoming jobs will be sent to other nodes in the OracleAS Cluster, and the Reports Server running on that node will process the jobs. Thus, the failure of one node does not bring down the cluster. When any Reports Server fails, its current and scheduled jobs are not transferred to other Reports Server, and these jobs are lost.

    Component-based clustering: Reports Server clustering provides benefits for OracleAS Reports Services only. For ensuring the high availability of other components of OracleAS, you need set up and maintain separate mechanisms.

    High availability for OracleAS: One mechanism provides high availability to the entire application server, eliminating the need to set up and maintain a unique high availability mechanism for each application server component.

    Mechanism for detecting duplicate jobs: When the cluster is identifying where an upcoming scheduled or immediate request should be processed, it considers whether any Reports Server in the cluster has information in cache that matches the request. As a result, if the job has been processed in the past by any Reports Server in the cluster, the duplicate job request will be sent to the same Reports Server. This mechanism maximizes the usage of the Reports Server cache.

    Mechanism for detecting duplicate jobs: The load balancer does not consider whether any Reports Server has information in cache that matches the request. If you want to maximize the usage of Reports cache with the load balancer, you will have to do manual configuration on the load balancer that makes sure that requests with the same URL are always sent to the same node.

    See Also: Load balancer documentation for the manual configuration steps

    Consolidated view of the job queue: When you use Reports Server clustering, it is possible to view rwservlet Web commands like showjobs for the entire cluster. When you do this, you get a single window to all the past, current, and scheduled jobs on the cluster. As a result, you can send a killjobid request to the cluster, and it will route the request to the same Reports Server that processed the request.

    Consolidated view of the job queue: You do not get a single window to the job queue by default. Instead, every Reports Server will show only its own job queue. To get a consolidated view of the jobs submitted to all the nodes, you can perform the following steps:

    1. Configure the job status repository for each Reports Server using the jobStatusRepository element in the Reports Server configuration file.

      Make sure that the jobStatusRepository element is configured to use the same repository in all Reports Servers. This element stores the job information for all Reports Servers in a common table in the database.

      See Also: Section 3.2.1.12, "jobStatusRepository," in the Oracle Application Server Reports Services Publishing Reports to the Web

    2. Write a custom application that can query this table and show you a consolidated list of jobs processed by all Reports Servers.

      Note that operations like killjobid will still work, since you will specify the option server=server_name in the URL. As a result, rwservlet will send the request to the specified Reports Server.



    See Also:


8.1.2 Design Your Paper Layout to Display Report Output in Microsoft Excel

Oracle Reports 10g Release 2 (10.1.2) introduces a new output format DESFORMAT=SPREADSHEET, which enables you to generate output from paper layout reports to HTML files, which you can directly open with Microsoft Excel 2000. Using this output format, it is very easy to generate the output of your paper reports to files, which you can open with Microsoft Excel. There are certain inherent differences between SPREADSHEET output and paginated output formats, such as PDF. Due to this reason, you may need special considerations in designing your report especially for output to Excel, or if you want your paginated and your output in Excel to look exactly alike. This section covers these special considerations.

Implementation Details

Suppose you create a simple report, such as a group-left employees report, in Reports Builder using the Report Wizard, and add a bar graph using the Graph Wizard. If you generate the output in PDF format, you will see the company logo, the report title, background colors, and so on. If you generate the output of the same report in spreadsheet output format and view the output in Excel, you will notice a few differences between the Excel and PDF outputs, as seen in Figure 8-1 and Figure 8-2.

Figure 8-1 PDF Output of a Simple Report Created Using the Report Wizard

Description of Figure 8-1  follows
Description of "Figure 8-1 PDF Output of a Simple Report Created Using the Report Wizard"

Figure 8-2 Spreadsheet Output in Excel of a Simple Report Created Using the Report Wizard

Description of Figure 8-2  follows
Description of "Figure 8-2 Spreadsheet Output in Excel of a Simple Report Created Using the Report Wizard"

Table 8-3 describes the differences between the PDF and Excel outputs.

Table 8-3 PDF and Excel Output Differences

Difference Explanation Guideline

The company logo is missing.

The report title is missing.

The Report Wizard creates the company logo and the report title in the page margin area. Since spreadsheet-based applications like Microsoft Excel do not have a page or page margin concept, the objects created in the report margin area are ignored in spreadsheet output.

Do not define any objects in the page margin.

There are no page breaks.

There are no page breaks in Excel. Any formatting that you do in Reports Builder to honor the exact page boundaries will be lost in the output to Excel.

Avoid any formatting that is dependent on honoring of page dimensions or page breaks.

The background colors and font colors do not exactly match those in PDF output.

The color palette available in Microsoft Excel does not match the color palette of Reports Builder. In fact, the current versions of Microsoft Excel (2003 or earlier) are limited to the 40 colors that you see in the color palette, and any color outside of this palette is mapped to one of these 40 colors. As a result, if you use a color that is not exactly represented in the Excel color palette, Excel will do a closest match to replace it with one of the colors available in its color palette. In some cases, this close match may be not as close as you would like it to be, and may bring in an unwanted change in the look-and-feel of the report.

Use colors that are available in Microsoft Excel's color palette. Alternatively, you can manually match the color palettes used in Reports Builder and in Microsoft Excel. To do this, you need to define the colors available in Microsoft Excel's color palette inside Reports Builder, and then stick to only those colors in your report.

See Also: Reports Builder Online Help for more information on the color palette. This help is also available on Oracle Technology Network at http://www.oracle.com/technology/products/reports/index.html


Additional Guidelines

Keep the following additional guidelines in mind for ensuring proper output in Microsoft Excel:

  • Do not leave any space between two adjacent objects.

    Any space, including a few pixels, between two adjacent objects will result in an empty cell or column in Excel output. Figure 8-3 shows this result.

    Figure 8-3 Empty Cells in Excel Output Due to Space Between Adjacent Cells

    Description of Figure 8-3  follows
    Description of "Figure 8-3 Empty Cells in Excel Output Due to Space Between Adjacent Cells"

  • Make sure that the widths of all objects are vertically consistent.

    If the objects are not exactly aligned vertically, that is, have inconsistent widths, it is likely to result in insertion of unwanted cells/columns in Excel. Figure 8-4 shows this result.

    Figure 8-4 Empty Cells in Excel Output Due to Failure to Align Cells Vertically

    Description of Figure 8-4  follows
    Description of "Figure 8-4 Empty Cells in Excel Output Due to Failure to Align Cells Vertically"

  • Make sure that the vertical elasticity of the frames and repeating frames is not fixed unless you are sure you have allocated enough space to accommodate all the records.

    If you set the Vertical Elasticity property of a frame to Fixed, the output in Excel will show only as many records as could appear on the first page of the paper output. Since Excel does not have a page concept, it is not able to overflow the remaining rows to the next page.

  • For reliable formatting of spreadsheet output, enclose the whole layout area in a frame.

    This action prevents the possibility of parallel objects displaying in different vertical positions, one following the other.

Restrictions

Keep the following restrictions when using spreadsheet output:

  • The following paper layout objects are not supported in spreadsheet output: graphic arc, polygon, rectangle, rounded rectangle, stretchable line, underlined text, and OLE external object. (OLE objects are only applicable to reports developed prior to Oracle9i Reports).

    Space for these drawn objects is reserved, but there is no visible representation in the output. This limitation does not apply to horizontal lines.

  • Graphs embedded in spreadsheet output are static image files, and are not interactive.

    Thus, the Graph Hyperlink property is not supported in spreadsheet output.

  • If you rotate a boilerplate object in the paper layout, the object will appear horizontal in the spreadsheet output.

  • Images included in the paper layout of the report will appear in the spreadsheet output on the browser, for example, using DESTYPE=CACHE or getjobid, only if the Reports Server is running in non-secure mode.

    In the case of a secure Reports Server, images will not appear in the spreadsheet output on the browser. Generating images in the output involves multiple calls from the browser to the Reports Server (one call for each image). Once the user is authenticated, Oracle Reports passes the user's identity between the browser and the secure Reports Server using cookies. Excel does not support cookies. As a result, the call to the secure Reports Server seems like a call from a non-authenticated user. Thus, the Reports Server refuses to pass on the images. Images will appear correctly in the spreadsheet output if you generate the output to any other destination, such as file, FTP, OracleAS Portal, or WebDAV.

  • XML-based bursting and distribution is not yet supported for SPREADSHEET output format.


See Also:


8.1.3 Select Paper Layout to Control Pagination and Web Layout to Control HTML Output

In order to be able to serve the requirements of Oracle Reports customers that use J2EE architecture for their enterprise applications, Oracle Reports introduced Web layout in addition to the paper layout. The Web layout is completely code-based as opposed to the paper layout, which is based on graphical frames, repeating frames, boilerplate objects and so on. When Oracle Reports users need to deploy their reports on the Web, they have two options:

  • Design the reports in paper layout and access the report in HTML/HTMLCSS format

  • Design the Web layout and access the JSP

The paper layout offers you minute control over pagination. In case you would like to generate HTML output, it also provides several properties that you can use to affect the HTML code in the output. In spite of all its capabilities, this format does not offer you full control over the look-and-feel of the HTML output. For example, if you want to alter the width and other attributes of the table that shows your data, it can only be done within the constraints of the graphical capabilities of the report layout designer, and you may not be able to make full use of the HTML or CSS capabilities. The paper layout is quite useful in situations when you do not want to hand-code your HTML report, and you would like to present exactly the same report in HTML as in paginated formats like PDF.

Because Web layout is code-based, it offers minute control over the HTML that appears in the output. You can design your reports to look exactly like the rest of the pages in your application. Using Java code inside the JSP report is more direct and easier than in the paper layout because you have to use PL/SQL logic to call the Java business logic. Once you have designed the report, you can package the JSP with the rest of the application and deploy it on a J2EE application server. The Web layout is useful when you plan to have a J2EE-based Web application and have expertise in writing Java and HTML code. You can use Web application wizards, such as Oracle Reports Wizard or Oracle Graph Wizard to generate JSP code for you. You will not experience the full potential of a JSP report unless you have a JSP-based Web application team.

8.1.4 Use Dynamic Environment Switching to Consolidate Reports Servers

Oracle Reports contains a feature called dynamic environment switching. Previously, the Oracle Reports server could only serve reports that were compatible with the operating environment in place when the Oracle Reports server was started. For example, the reports had to be compatible with the value of the NLS_LANG parameter at the time the Oracle Reports server was started. This restriction meant that you needed to have one Oracle Reports server running for each processing language. The new environment switching feature available in Oracle Reports eliminates this restriction by enabling one instance of Oracle Reports server to serve reports with any number of environment settings, including language.

Implementation Details

To use this feature, add as many <environment> tags in the Oracle Reports server configuration file as needed. Each of these tags can have values such as the NLS_LANG setting, a currency symbol, or a calendar. When processing a job, use the EnvID parameter in the command line to specify which <environment> setting you want to use. The Oracle Reports server reads the relevant environment settings from the configuration file, and if an engine is not already running with these settings, a new engine is started. The new engine, started with appropriate environment settings, will be used to process the job.


See Also:

Oracle Application Server Reports Services Publishing Reports to the Web for further information about the <environment> section of the server configuration file

8.2 Oracle Business Intelligence Discoverer Best Practices

This section describes best practices for OracleBI Discoverer. It contains the following sections:

8.2.1 Identify Worksheets That Need Tuning to Improve Performance

The performance of an OracleBI Discoverer system refers to the time OracleBI Discoverer takes to complete a specific operation. The operation that most users are concerned with is the running of a worksheet - the execution of a query and display of the data. OracleBI Discoverer worksheet performance is largely determined by how well the database has been designed and tuned for queries.


See Also:

The following documents in the Oracle Database documentation library:
  • Oracle Database Data Warehousing Guide

  • Oracle Database Performance Tuning Guide

  • Oracle OLAP Applicaton Developer's Guide


You can achieve additional performance benefits when reporting on relational data by designing your Business Areas and Worksheets with performance in mind. You can use the Discoverer EUL Status workbooks to identify worksheets that may need additional tuning.


See Also:


8.2.2 Establish Scalability to Share the Workload

The scalability of an OracleBI Discoverer installation refers to the ability of OracleBI Discoverer to handle increasing numbers of users or queries without compromising performance. To take advantage of the inherently scalable architecture of OracleBI Discoverer, install it on multiple computers and share the workload between the computers as the number of users increase.


See Also: