Taking a “Sneak Peek” At XML Publisher 5.6 Enterprise

If you read my postings on XML Publisher 5.5 towards the end of last year (here, here), or sat through the "XML Publisher : What's It All About" (paper, slides

           )

I've given recently, you'll probably be interested to know that I've been given
early access to a release candidate of the next version of the product, known as
XML Publisher 5.6. I featured this new release in the presentations on the
product I've given recently, but up until now what I've spoken about has been
based on conversations with the product team and access to documentation. I've
got my hands on an early version of the product now and I'm starting to give it
a walkthrough. Over the next few days I'll be going through the process of
setting XML Publisher 5.6 up, accessing the new XML Publisher Enterprise
reporting portal and running some reports, using the new Microsoft Excel
integration, and then creating an XML Publisher Enterprise online reporting
application from scratch. I'm doing this as a way of working through the product
prior to writing an article on it for OTN, so if there's an area I cover that
you'd like a bit more information on, drop me a line and I'll see what I can do.
As a quick recap, XML Publisher 5.6 is the second stand-alone release of XML
Publisher and the hot new features include:

• a new "Enterprise" application that provides online access to reports using a folder-based portal, with reports accessed via users and groups
• Microsoft Excel integration, so that you can analyze reports, change parameter values and rerun them using the familiar Excel interface
• Easy installation as part of the OC4J install
• A pivot table-style OLAP analysis feature

XML Publisher 5.6 currently runs as an application hosted in either OC4J 10.1.2 / 10.1.3, or Apache Tomcat 5.x, and the plan is for the production release to include OC4J along with the product in the same installer so that you can get up and running straight away. With the version I had I had to do a manual install (it's just an early preview release) and so it was a case of installing OC4J 10.1.3, start up the OC4J admin console, pick up the .ear file for XML Publisher and then deploy the application - even this manual process was easier than with XMLP 5.5 although that's more down to the choice of OC4J 10.1.3 rather than 10.1.2 on my part.

As well as the server element that gets installed on OC4J or Tomcat, there's also a Desktop element that installs as a plug-in to Microsoft Word. This is a straightforward Windows installer (someone asked me last time about Open Office support, as far as I know it's MS Word only at this point) and it's a similar process to 5.5. The Desktop version of XMLP is used for building and then previewing report templates, but for the time being I'll put this aside and we'll take a look instead at the server element, which is where the main differences are from the previous release.

If you read the postings on XMLP 5.5 a few months ago, you'll be aware that this first release didn't really come with a server application for viewing your reports. What you got with 5.5 was a set of java libraries, and some example applications that showed how the libraries could be used. What's new in 5.6 is that Oracle have taken these libraries and build a complete report viewing, scoping and administering environment using these libraries and delivered these as XML Publisher Enterprise. Taking a look at it, and the XML Publisher Desktop element, is quite suprising actually as they've done a pretty good job - the online Enterprise part is way in advance of what you get with Oracle Reports, which pretty much left you to your own devices in coming up with an environment for your users to select and then run their reports, with jobs like scheduling pretty much being a DBA-only task that required you to submit arcane URLs to the reports server to create jobs and return status information. With XMLP 5.6 all this has a pretty new HTML front-end and you can obviously see that XMLP rather than Reports is where Oracle's reporting effort is going in future.

Anyway, once you start up XML Publisher Enterprise you get presented with a login screen. The default login and password is "admin/admin" which you should obviously change once you're up and running.

Once you're in, you're presented with a view of the folders and reports that you can access, plus a taskbar on the left-hand side for creating new folders, reports and uploading existing reports. The admin tab lets you define datasources and other settings, I'll cover those later on.

Navigating over to the HR Manager folder, I first clicked on the Employee Salary Report; after a couple of seconds the report came up - pretty fast actually, no noticable lag, much faster than Discoverer or Reports. The report looked like this:

Pretty standard stuff. Note the parameter drop-downs at the top of the report, the links for scheduling and viewing report history, the template and output format changer, and the Analyzer and Excel Analyze buttons (more on those later).

The next report I ran was the Sales Dashboard in the Executive folder. Now this looked quite impressive - again very quick to display, very little load on the server as it's rendered.

What this dashboard is showing is a number of separate queries coming both from the datbase, and from XML document datasources. It's interesting to think about this in terms of Oracle Portal - obviously Portal is a pretty good framework and gives you lots of additional features - Single Sign-on, ability to host Oracle and JSR-168 portlets, Discoverer integration and so on - but this feature in XMLP is pretty nifty and is an alternate solution when you need to put a lightweight portal together. I'll be looking at this in more detail at a later date, but for the time being it looks like a pretty impressive BI feature.

The next feature I thought I'd try out is the Microsoft Excel integration. This works via a .NET Framework 2.0-based plug-in to Excel that gets downloaded to your PC when you first use the "Excel Analyzer" feature. To test this out I loaded up the Employee Report again and clicked on the Excel Analyzer button:

Once you've pressed the button, the first thing that happens is that Excel starts up, loads the report data into a worksheet and then formats it to look like the report template. You then get a new XML Publisher menu item at the top of the page that gives you the ability to log in to the XML Publisher server, using the same credentials as you used to log into the online web interface.

Once you've logged in, you can then bring up a toolbar that lets you select from the reports parameters, then rerun the report to bring back a new view of the data. This is where the value comes in - you're connected to the XMLP server, which gives you the ability to rerun the report from within Excel, have XMLP server return the data, and then present it to you again in Excel - there's no need to use another tool and XML Publisher is doing all the hard work in retrieving the data, applying security and scoping, and providing interaction features such as toolbars and parameter lists.

Going back to the online interface, there's a nice scheduler based on the OpenSymphony Quartz Scheduler that allows you to submit a report for running now or in the future, run it at regular intervals and send the results via email, PDF, HTML, XML or RTF.

Finally, and for me this is one of the most interesting features, there's an "Analyzer" button on the top of each report page that lets you analyze your data using a pivot table-style crosstab interface. Going back to the folders and reports page, selecting the Sales Analysis report and then pressing the Analyzer button, you get presented with this interface that lets you pick data items for the pivot table:

You can then drag and drop data items onto the pivot table, add other items in as page items (as with Discoverer), and then drag on the measures that you'd like the pivot table to analyze. You can even nest dimensions by dragging a second row or column item on top of an existing one, and change the orientation of the crosstab by dragging a row field over to the column field area and vice-versa. A very impressive bit of code actually, I wonder if Oracle bought this in as a component or developed it themselves?

From what I understand, XML Publisher does all the indexing and rotating of data at the server mid-tier, i..e. there's no extra load on the database server, a bit like how Discoverer works when using Oracle Application Server. Again from my conversations with the product team, they've in addition to regular SQL queries, got this feature working with Oracle OLAP in that you can analyze data extracted using SQL views using OLAP_TABLE, although as the XMLP server does the indexing and caching obviously you're not going to get the benefit of Oracle OLAP's summary management and indexed data access. Still, it looks a very impressive feature, obviously not quite as featurefull as Discoverer but an excellent way of presenting data to users so that they can analyze it online. Good stuff.

Right, that's it for now. Over the next few days I'm going to look at the process of putting your own reports together, publishing them using XMLP Enterprise, and using the scoping and user management features to add a layer of security. Back in a day or so.