Checking Out The New Discoverer 10.1.2.2.0 Features

Following on from Abhinav’s announcement last week, I downloaded the 10.1.2.2.0 patch for Application Server 10g over the weekend, and started having a look at the new Discoverer and Spreadsheet Add-in features that it makes available.

If you’ve just got BI Suite Standard Edition installed on it’s own (i.e. not part of a Portal and Infrastructure setup) the patch install is fairly easy; you just unzip the patch file, run the installer and point it to your BI Suite EE Oracle Home. The installer detects the existing setup and, once it’s got your ias_admin password, it shuts the existing instance down and applies the patch. If you want to upgrade the Spreadsheet Add-in, you need to run the patch again afterwards and point it at your BI Tools Oracle Home, whereapon a different patch routine runs and your tools software is then updated.

Reading through the post-installation steps, there’s some scripts to run to upgrade your Discoverer Catalog, and it mentions another database patch (51467) that you need to apply on top of 10.2.0.2.0 to enable the custom members feature. Looking though metalink though, the patch doesn’t exist for Windows 32-bit and after raising an SR yesterday evening, I’m not sure that the patch is actually needed. Certainly, it seems to work without the patch being applied (although I did in the end need to drop and then recreate the Discoverer Catalog, and I have had the database crash-out when accessing OLAP data…), but I’ll keep the SR going and see what comes out of Oracle Support - the’ve just made available to me a later patch (OLAP Patch C, rather than the B-version that the documentation requests) and I’m checking out now whether this is the official version required.

Anyway, after logging in to Discoverer Plus and bringing up the Global Sample Schema, looking at the available items pane I see the new ‘Calculated Member’ button next to the New Calculation and Edit Calculation buttons.

Pressing this starts the New Calculated Member wizard for whatever dimension is currently selected in the Available Items pane. For anyone who doesn’t know about Calculated Members (called Custom Aggregates or Custom Members elsewhere), it allows you to create an arbitrary grouping of dimension members, say the UK, Ireland and Norway, and create a new “virtual” dimension member that contains the sum of those other dimension’s values. In this first release, you can use addition and subtraction calculations, so that for example you could add together the UK and Ireland’s totals, and then take off Norways, and in the next release, you’ll be able to do all sorts of calculations with the member’s values.

In this instance, I’ll create a new calculated member called “UK, USA and Hong Kong” and add it to the Discoverer Catalog. The wizard starts off by letting you select the members…

… and then lets you specify whether the member values are added together, subtracted or some combination of the two.

In addition, if the member selection is based off of a (dynamic) condition, you can have Discoverer automatically rerun the member selection at startup. Then, after naming the custom member, you can see it in the resulting crosstab.

This is a feature that used to be in Oracle Sales Analyzer, and is particularly useful in sales and marketing analysis, where you might want to put custom cohorts of customers together to help with market analysis. It’s a feature that’s unfortunately only available with Discoverer Plus OLAP, as it leverages features in the OLAP Option to create the custom members.

As well as Custom Members, the patch also provides a number of new graph styles that are available in both the relational and OLAP versions of Discoverer Plus.

Finally, the patch updates the Spreadsheet add-in to re-vamp the user interface and provide access to write-back. Writing back to the OLAP cube is quite an interesting area, when you consider the effect of multiple concurrent users, read-consistency, how to handle updates to non-leaf nodes and what to do when the cube needs re-aggregating, and so I’ll take a proper look at this feature at a later date, when I can do it proper justice. Anyway, the patch to make all this possible is available on metalink as #4960210 and is available for download now.