An Evaluation of Oracle’s BI Publisher

BIP has been evaluated by downloading and installing the demo, and then following the user guides to build reports.

Installation

The installation process for the Business Intelligence Publisher demo is only somewhat smooth. For starters, the set up process is not intuitive. For example, trying to install the optional installation items took our developer two full days to figure out before finally giving up! To top that, the installation manual, although simple in layout, is very difficult to follow and does a poor job of addressing problems the user may encounter during the set up process. When installing, the user has three options: 1) a basic install into a standalone application server, 2) an advanced install into an existing Oracle Application Server, or 3) a manual install into a J2EE server.

After the basic installation, the user has three post-installation tasks, some more
complicated to install than others. For example, installing the Template Builder in Word is simple, you just click on Template Builder in the application and it downloads the installer. The other post-install tasks, including configuring the Scheduler and Demos is rather hard. To do this, the user must configure BIP to connect to the correct databases, which is hardly a simple task for a non-programmer and has even been proven difficult to programmers.

overall process, but unfortunately, fails to explain to the user how to connect to a jdbc data source or to tell the user where to find such information. The databases certified for use with BIP include Oracle Database 9i or 10g, Microsoft SQL Server 2000 or 2005, IBM DB2 for z/OS 8 (NFM mode), IBM DB2 for UDB EE 8.2, Sybase Adaptive Server, and MySQL 4.1 or 5.0.

Using the BIP Interface - Report Viewing & Creation

If you’ve survived installation, you can finally start using the BIP platform. The
interface for BIP is accessed through a Web browser, and the main page is clean and easy to navigate. Here, you will find the Search box and your top-level folders, which include a short list of links to the top reports in each. Viewing a report is intuitive – just click the link. The reports that cannot be viewed online simply do not display a live link. Report creation, on the other hand, is not intuitive without reading the guide, partly due to the unfamiliar development environment. To create a template, the user has the option of either rtf or pdf, or can choose to use predefined government templates, which only require connecting data to the template. The usable data sources include an SQL query, http (XML feed), Web service, Data Template, Oracle BI Answers request, or File (for xml files).

SQL query has a query text area for hand-coded queries and a query builder for building SQL queries without coding. The http data source, which allows for an XML feed, enables report creation from an RSS feed. Web service supports document and literal web service data sources that return XML as a string. The Data Template is used to create more complex SQL queries. The templates are XML files that tell the data engine how to generate an XML structure against a database. Oracle BI Answers requests is an ad hoc query building tool included in BI Enterprise Edition. Finally, File data sources, for XML files, allow the user to set up a file directory as a data source, place XML files in the directory, and access any of the XML files in the directory as a data source.

making a selection from a list – this option supports multiple selections and lists can be fixed or contain data generated from SQL queries on defined data sources; and date parameters pass dates into the report. Parameters can be of type string, integer, Boolean, date, or float. Lists are lists of selectable values; they can be fixed or come from a query.

Layouts/Templates

The Layouts and Templates in BIP are options for designing templates. Available types include pdf, xls, XSL, stylesheet, eText and rtf (the most common).

PDF templates are primarily used to employ predefined forms as templates, such as the downloadable government forms. For pdf outputs, BIP provides a mapping tool to enable mapping fields from data to form fields. XLS templates are created by downloading the report data to an Excel spreadsheet using Analyzer for Excel. The user then creates the layout for the data in Excel and uploads the spreadsheet back to BIP as a template. XSL Stylesheet Templates define the template in XSL formatting language – the user just needs to specify if the template is for FO, html, XML, or txt transformation. eText templates are specialized rtf templates for constructing EDI or EFT transactions. The user must upload the template file and then connect it to the report. With eText templates, multiple layouts are available in a single report. While other template types are limited in the output types available, rtf templates can output to any of the output types (html, pdf, etc.).

RTF Templates are the most common – this option uses Word (or any word processing application) to design reports by inserting code directly into the template. BIP provides a plug-in (Template Builder) for Word that assists in template design and enables a connection to BIP to access data and upload templates directly from Word. The user can insert simplified tags for xsl expressions into a template to associate data to the template or use pure xsl in place of a tag. BIP supports Word format features as well as advanced reporting features such as conditional formatting, dynamic data columns, running totals, and charts. There are two methods for creating RTF templates in BIP. One is the Basic RTF Method, which uses any word processor that supports RTF version 1.6 or later. This method, which has the user design templates using BIP syntax, is straightforward; however, it does require hand coding everything and does not work with pure XSL/XSL:FO code. The Form Field Method requires the use of Word’s Form Field feature to place syntax into hidden form fields and make templates more readable. This method works on Word 2000 or later and in Windows 2000 or later and must be used for writing pure XSL/XSL:FO code.

Useful rtf Features

The Template Builder is a Word add-in optional to BIP users that uses Placeholders and Groups for mapping data to a template. Placeholders are tags within a template that contain the name of a corresponding XML element (e.g. vendor name). These tags just output the data from the XML element; the user will know exactly what will be placed there since the tag looks almost identical to the XML tag. Groups notify BIP to take action for each occurrence of an XML element (e.g. output the year of each CD).

Other useful features included in the rtf template builder include Headers and Footers, Images, Charts, Hyperlinks, Table of Contents, Checkboxes, Dropdown Menus, Column and Row Formatting, Cell Highlighting, Page Level
Calculations, and other Tags and Statements.

The word processor’s Header/Footer tool is supported in BIP. Start Body and End Body tags (required for multiple or complex headers) can be used to distinguish the body section from the header/footer section – the area above the body is treated as the Header and the area below the body is treated as the Footer. In complex headers, forms cannot be put into the header, they must be hand-coded; however, if start/end body tags are used, forms may be used, allowing for an excellent way to make dynamic headers. If the user wants different first page, different odd page, or different even page functionality, Word’s page setup dialog will be used.

Using BIP, Images can be directly inserted, imported from the data source, or a dummy image can be used to import the image from the internet. Images can also be configured to have a hyperlink to a website. Placement commands may be used to cause effects on the shape, and shapes can be combined into composite shapes and then programmed to react to data – this can be done to create custom charts. Word clip art, auto shapes and other drawings, and Watermarks are all supported in BIP, but only in a pdf output format.

Creating Charts in the rtf template builder requires the insertion of a dummy image for defining the size and position of the chart and uses Alternative text for definition. The Charts option uses the Oracle BI Beans application to render images and all charts (e.g. Vertical and Horizontal Bar Charts and Pie Charts) from BI Beans are available. Most chart design requires knowledge of the obscure BI Beans DTD. Chart elements, like the title, can be fixed or obtained from the data source, and the chartappearance can be customized (with more hand-coding). If the user chooses to implement the Template Builder, charting becomes much easier.

The Hyperlinks option, which uses Word features, supports internal or external linking. Links can be fixed or dynamic and can be added to shapes (but not grouped objects). BIP also supports rtf Table of Contents. Headings can be fixed or dynamic and the user can create bookmarks for toc in pdf. Checkboxes can be controlled by data and Dropdown Menus can be populated by data (supposedly – our developer couldn’t actually get these features to work). Column and Row Formatting in rtf templates can be controlled conditionally, based on the data and can be conditionally shown or hidden. Rows can also be conditionally highlighted (the example uses modular division to accomplish special highlighting – this is great because it gives the user a lot of flexibility but also presents a problem through its highly technological nature). Cell Highlighting allows for cells to be conditionally highlighted – this is a complex feature but is also very flexible. Page Level Calculations allow special calculations to be performed for the current page – this is also a very powerful tool and only works in a pdf output.

The final features useful to the rtf designer include special Tags and Statements. The Page Break Tag causes a page break at the end of each for-each iteration. The Initial Page Break Tag sets the initial page number; this can be set from the data or from a parameter, but the user must declare the parameter in the template. The Last Page Tag allows different content to appear on the last page, which sounds very useful, but is a bit tricky to use. Different Even/Odd End Tags allow the user to force the report to end on an even or odd page. Choose-When-Otherwise Statements work like programming language switch statements and allow the user to control the layout based on several conditions, including a default condition. For example, if the exempt field has a one in it, display gray, if it has a zero in it, display black, otherwise display nothing.

BIP Features - Scheduler

The Scheduler feature for BIP is a useful tool for automating work. Basically, it allows for the scheduling of reports to be generated periodically based on the user’s specifications. Users don’t have to be very technical to schedule a report, but they must be familiar with each field’s purpose in the Scheduler. This feature requires extra installations to the software and a database is necessary to run the scheduler schema.

Analyzer

The Analyzer tool allows the user to compare data from a report in a cross-tab area. This feature appears to be useable for only some reports. The Analyzer for Excel, a
desktop tool that requires installation of a new component, enables the user to export the results of a report to an Excel spreadsheet and then log in to BI Publisher Enterprise from Excel to refresh data, apply new parameters, and apply a template to the report data. It also allows for the creation of Excel templates and their upload to the BIP server – the user can then access and run reports from an Excel session.

Cache Results

BIP will cache the results of queries during a user’s session in order to increase performance. However, changes in data that occur during a session will not show.

Query Builder

The Query Builder is a part of the data set definition for SQL queries. It builds queries without coding, can search and filter database objects, select objects, and columns and can create relationships between objects. Using this feature, the user can view formatted query results (supposedly with minimal SQL knowledge). The Query Builder has a relatively clean and simple layout, but seems to be a rather intricate tool, as Oracle explains it is great detail throughout its literature.

Template Builder

The Template Builder for BIP is a Word add-in to be used for rtf templates. It requires an extra, easy installation step – simply click the Template Builder link in a folder, and voile. Upon installation, a BIP menu and BIP toolbar are added to Word. Instructions and tutorials are located in the readme and help files delivered with the tool. Here is a list of what the template builder enables the user to do:

• Connect to a BIP server to retrieve data and build a template
• This feature provides a central location for report designers to locate all of the company's data sources and reports.
• Insert of report elements such as data fields, tables, charts, or cross tabs
• Validate the template
• Template Viewer: preview template in any supported output type with sample data
• Perform calculations on fields within the template
• Build and upload template via a direct connection to BIP server
• Data comes from connection
• Templates can be published (attached) directly to the report from Word
• Build and upload templates in disconnected mode
• Need to download sample data from the BIP server
• Connect to sample data via a Word template
• Load completed template from BIP server

Bursting

Bursting is a feature of BIP that allows the user to split a report based on the split key in the report data or deliver a report based on the delivery key in the report data. This user can apply different templates, output formats, delivery methods, and locals to each split segment of the report.

Data Template

Data Templates let the user build special queries for the report. These templates are special XML files that instruct BIP to generate an XML structure against any database.

User Guide

The User Guide has very nice visual examples. However, there are some downfalls in the BPI User Guide that make it rather unhelpful to its readers. For one, it is heavily geared toward developers, which is good for developers, bad for regular business users. Second, a lot of information is repeated, making it difficult to both find the information you’re looking for and determine what really matters. While the manual does a good job of explaining features through (visual) examples, many of the feature explanations have been found to be either incorrect or incomplete.

Weaknesses

• Installation Manual is difficult to follow.
• Desktop Tools would not install on an XP machine even though it meets all of the requirements.
• Only able to install Desktop Tools by setting up a virtual machine
  running Windows 2003 Server.
• Difficult to connect to a Microsoft SQL Server 2005 database.
• The Basic Version of BIP (basic install does not include Template Builder, Database connectivity, or Scheduler) requires everything to be hand-coded, even if form fields in Word are used.
• BIP has a high learning curve and requires its users to make a serious
  commitment to learning and remembering the software. Learning how to build templates in BIP is like learning a new, obscure programming language.
• Many features require frustrating trial and error to understand. This is because the features are either not explained well in the manual or not explained correctly in the manual.
• In order to test the template without the Template Builder, the report must be uploaded after every change made.
• Configuring the Scheduler is difficult and requires extra components that are not automatically downloaded and installed. It also requires learning how to use these new components.
• Installing datadirect connection for jdbc nearly impossible- the zip file didn't include installation instructions.
• Oracle Containers for J2EE Services Guide (the user guide) is difficult to
  find.
• "Dependency Hell"
• The manual is not easy to follow or understand – it’s too easy for the user to get caught up on pointless, painful tangents. If our programmer thought it was hard to understand, imagine how a less technical user would feel when trying to weed through a bunch of database jargon.
• Desktop Tools won't install if Word is running, but the installer seems to
  invoke word to run - ridiculous!

Strengths

• Oracle supports many advanced Word and drawing features (although some only work in pdf format). These features can act dynamically based on data and can be grouped to create special objects like custom charts.
• Unlike AutoTag, Oracle has background support for pdf outputs, including support for Watermarks
• Unlike AutoTag, Oracle supports the Table of Contents option in Word with either fixed or dynamic headings; however, the headings do not link.
• Unlike AutoTag, Oracle can do conditional column formatting. This feature formats whole columns so that they either do or do not appear based on data.
• Oracle allows highly flexible conditional highlighting over entire rows or
  single cells.