Thoughts on OBI EE for Discoverer Users

I mentioned the other days that one of the papers I’m delivering for ODTUG Kaleidoscope 2007 is on Oracle Business Intelligence Enterprise Edition (OBI EE) for Discoverer users. The aim of this paper is to introduce OBI EE to those Oracle customers who currently use Discoverer, perhaps have requirements that Discoverer can’t currently handle, has heard about OBI EE and wants to find out whether it’s a product they might want to upgrade to. By putting OBI EE features in the context of Discoverer, it should make some of the more esoteric features of the product a bit easier to understand (the common enterprise information model, Oracle Delivers, Oracle Answers, what the Oracle BI Server actually does and so on) and it should make a change from the usual “Introduction to Oracle BI EE” presentations we’ve all sat through (or delivered) over the past twelve months or so.

So, I guess that if we’re looking at a potential upgrade for Discoverer users, it would be useful to take a moment to think about what’s good, and what’s not so good, about Oracle Discoverer. In the paper I’ll do a quick one slide/one paragraph recap on what Discoverer is, and then go through what I feel are the major plus-points of the tool:

• Easy to use, lots of wizards, familiar look-and-feel, high awareness and exposure within the Oracle user community – many people have exposure to Discoverer through its apps integration, bundling with Oracle Application Server and so on – the “comfort” factor if you like.
• Leveraging of Oracle’s built in calculation, analytic and PL/SQL functions – Discoverer uses the Oracle database as the calculation engine, you get access to all the built-in SQL and PL/SQL features including all the analytic (lag, lead, window, top N etc) functions.
• Integration with Oracle database security and E-Business Suite responsibilities, and pre-built E-Business Suite reports and BI metadata layer.
• Integration with Oracle Warehouse Builder (although this requires the Enterprise ETL Option for Warehouse Builder, at $10k a CPU on the ETL database), and integration with Oracle Portal
• Oracle OLAP access through Discoverer for OLAP
• Lots of functionality around totals, percentages and other report add-ons

If you were honest, the failings of Discoverer could be summed up as the following:

• Oracle database-centric; although Discoverer can connect to non-Oracle databases, this is a fairly complicated DBA task and still requires everything to be routed through the Oracle Database, and the End User Layer’s Oracle database dependency still means you need an Oracle database somewhere, even if all your data is in MS SQL Server, for example
• Although Discoverer integrates with Oracle Portal, in my opinion is not an optimal solution as it’s tricky to get all the report refreshes working properly, the reports in Portal don’t show a real-time view of the data underneath, you can’t drill and analyze in-place, and Portal itself is a bit overkill for just a BI portal
• It’s very hard, if not impossible, to get Discoverer reports to run lightening-fast; typically Discoverer reports take 10, 20 seconds or more to return data, Discoverer itself adds a significant time overhead to queries, it’s just not a fast, snappy environment to report in.
• The report authoring part of Discoverer requires a Java applet to be installed and then run in the client PCs Web browser, which can cause security and installation issues for users not running with admin rights, and requires a higher-spec (memory, CPU) PC to run on.
• Discoverer for OLAP, whilst very similar to regular relational Discoverer in terms of functionality, look and feel, is however still different, has more limited capabilities (no parameters, can’t total by attribute and so on) and has a separate report catalog and security setup to Discoverer relational.
• There’s (currently) no capability to create alerts, distribute reports, add in-context messages to reports giving advice on how to interpret the report.
• There’s also (currently) no way of calling Discoverer reports via an API, or adding workflow to Discoverer reports so that a user clicking on a report area or a link displayed along it can trigger, say, a BPEL or Oracle Workflow process to act on insights provided by the report.
• There is also (currently) no way of displaying Discoverer-generated data in, say, a letter, or printed labels, or in a report that contains more than one dataset.

I say “(currently)” in some of these issues because a few of them are being addressed by planned integration of Discoverer with OBI EE, and I’ll address these future alternatives to a straight upgrade to OBI EE later in the paper. For the time being though, Discoverer’s advantages could be described as its familiarity, leveraging of Oracle database features, support for totaling, percentages and analytic functions and integration with Warehouse Builder and Portal, whilst the drawbacks are this very Oracle database integration, lack of alerting and report distribution features, lack of APIs and interconnectivity with the application development world, limited output options, and performance, which shouldn’t be overlooked as it’s the number one compliant I hear about Discoverer when I visit customer sites.

So, with these points in mind, I’ll do a short introduction to OBI EE and then start to go through its features, placing them in the context of Discoverer and Discoverer’s good and bad points. As I’m conscious that this is only a one hour session, and working to my “golden rule” that people can only take in a maximum of six things during a presentation before it all starts to wash over them, I’ll concentrate on the following key features.

1. The architecture of OBI EE
   
   
2. What Oracle Answers is, and how it compares to Discoverer. At this point I would build a simple report, and afterwards highlight in summary some of the things Answers does better/differently than Discoverer.
   
   
3. How Oracle Interactive Dashboards works, how it differs to Oracle Portal and how it integrates with Answers. Again, I’d quickly show off Dashboards by bringing in the previously created report, and point out in summary what else it does different/better than Oracle Portal.
   
   
4. How the alerting and distribution element works (Oracle BI Delivers)
5. How the metadata layer (Common Enterprise Information Model) works, how it differs from the Discoverer EUL
6. How it integrates in with Web Services, BPEL and SOA
7. How it integrates with Oracle E-Business Suite
8. What Oracle BI Publisher brings to things

(I know that’s 8, rather than 6, things, but hopefully I’ll still have the audience with me at that point).

Now that I’ve gone through the key points of the new products, it’d be worth talking about the migration process and also, what elements of functionality Discoverer has that the new products don’t have, or don’t do as well.

For the migration process, this would have two strands. Firstly, the Oracle Discoverer development team are working on a migration utility that will, to one extent or another, automate the process of creating OBI EE metadata (and possibly reports) from the equivalent Discoverer metadata and workbooks. Now at the time of delivering this paper it’s unlikely the utility will be publicly available, so whilst I’ll make reference to it, I’ll mainly look at migrating Discoverer elements manually. This will consist of a couple of slides on the process of bringing the Videostore dataset in to OBI EE’s metadata layer, creating regular and time dimensions, recreating complex folders in the OBI EE presentation layer and custom folders as SELECT tables the physical layer, applying security and re-implementing workbooks, which of course could be a presentation in itself, so I’ll only have time to cover this superficially in the presentation, although I might have time to cover it in more depth in the accompanying paper.

In terms of what functionality is currently missing in OBI EE, I thought this was an interesting (and important) area to cover as “what’s missing” is not normally something you’d see covered in Oracle sales presentations or product literature, and it’s only something you tend to find out once you try to implement a migration. As I personally haven’t migrated a Discoverer system to OBI EE yet, I’ve based my observations on migrating the Videostore data and workbooks to OBI EE, and so far I’ve come up with the following areas OBI EE still falls short:

• Discoverer is still, in my opinion, more intuitive for first-time or less experienced users to create or open reports. Discoverer provides you with a Workbook wizard when you first start up giving you simple choices around opening a report, creating a report and so on, whilst Answers provides you with a fairly busy, hard-to-fathom web interface with lots of very small buttons, no real guidance on what to do next. Now the Answers development team would no doubt say that, in reality, most users won’t see Answers as you do the vast majority of report querying directly within Dashboard (in Discoverer/Portal, anything more than just viewing a snapshotted Discoverer worksheet portlet requires launching out into Discoverer Viewer) but even so, Answers could be a bit more newbie-friendly.
• The range of analytic functions and other calculations in Answers is limited compared to the Oracle PL/SQL and SQL functions made available from Discoverer. For example, whilst coming with a number of analytic functions such as Rank, TopN and so on, Answers’ time series calculations are limited to prior period (“ago”) and period-to-date (“todate”), whilst Discoverer has access to all the Oracle analytic functions in the relational version, and all the OLAP DML functions (forecasts, allocations, statistical etc) in the OLAP version.
• OLAP in general in Answers is very limited (there’s no ability to select dimension members via their position in a hierarchy, no concept of dimension attributes, no ability to drill up or to related) although Answers does handle the “OLAP as a star-schema performance booster” use case well, as it just presents OLAP data as a relational dataset and handles the issue of fully-solved cubes very well (and it talks MDX and XML/A natively, opening up MS AS, SAP BW and in the future, Essbase, very well).
• There’s currently a lot of duplication at the BI Server level with equivalent Oracle database features – BI Server has it’s own summary management, query rewrite, security layer and so on – which whilst useful in a heterogenous environment (for consistency) leaves you wondering when to use OBI EE features, when to use Oracle RDBMS features.
• Cost, of course. OBI EE on a per-CPU basis is more than ten times the cost of Discoverer, although on a named user basis the cost difference is less marked and the upcoming OBI Standard Edition one is more comparable cost-wise, albeit with limits on the number of server CPUs and end-users

So, in summary, that’s what I’m going to try and cover, Again, considering I’ve only got an hour, I might end up covering less points but in more detail, or just to reduce the pace of the talk, but if you can think of anything else that’s relevant, or questions you’d like answered (I’ll post the presentation and paper on the site when they’re written), just add a comment and let me know.