Question Time

I've had a few questions through recently, and rather than answer them directly via email, I thought I'd publish them here. Going into the future, I'll only be answering questions that get posted to the forum (http://www.rittman.net/forum), as it makes the answers available to everyone and other people can step in and answer if I'm not available. Anyway, here goes...

“I have had trouble finding information regarding write-back in Oracle OLAP – and then I read your weblog entry from December 1, 2005 - Chris Webb Interview with Nigel Pendse. From it you state “Nothing for Oracle to get too excited about here as of course the Oracle OLAP add-in doesn't even feature write-back yet,” …

This would explain why I can’t find anything about Oracle OLAP write-back. Do you have any thoughts on whether an OLAP write-back tool would be able to go against Oracle OLAP? Or is it just a simple “not at this time” answer?

Any idea whether they are considering supporting write-back functionality in the future?”

It's not something I've done myself, but a quick look through the OTN Forums shows that the BI Beans Crosstab bean has an “editable” property that lets you edit data in a crosstab cell. According to this other posting on the BI Beans Forum, “i have tried making a crosstab (in java client)ediatable by setting its 'setcellediting' property to true” although the post later says that only cells with values, i.e. not NA cells, can be edited in this way – see here also for issues over double-clicking. I seem to remember from speaking with colleagues that you've still got to write your own routine to update the underlying OLAP datasource, be it an AW or relational tables, although this may well be done for you now with the 10g release of BI Beans. Apart from that, the upcoming release of the Spreadsheet Add-in will I believe feature the ability to write back results from Excel to the OLAP datasource, and this presentation by Fred Dean from a recent UKOUG BIRT SIG event (UKOUG membership required) talked about a method of using Excel, together with some macros and the DBMS_AW.EXECUTE package, to write-back to an analytic workspace.

The next one's on Discoverer:

"i was reading through your blog and had a few questions. We are looking at a product that uses MS SQL.

We are an all Apple shop and currently use Discoverer against and Oracle DB to edit reports and templates.

Now that we are being forced to use MS SQL server we want to know if it is still possible to use Discoverer to edit reports against the MS SQL server? and if so how do we go about integrating Discoverer with MS SQL Server?

Any light you shed would be greatly appreciated."

Discoverer can report against data held in a Microsoft SQL Server database, through the use of the Generic Connectivity feature you get with the Oracle database. This uses an ODBC driver (and therefore is only really practical for Oracle-on-Windows installation), is free and allows you to connect to an SQL Server database using a database link. This article on OTN describes the process of setting up the connection and using the data using Discoverer. Note that with Discoverer, you're still going to need an Oracle database somewhere, to hold your EUL and to authenticate users – other databases accessed via ODBC have their data then brought in by this Generic Connectivity feature, which requires an Oracle database at the centre. In addition, if you're going to be using Discoverer 10g, you'll still need an Oracle Application Server 10g installation to serve up the Discoverer application, although you can run this standalone without tying it in to an Infrastructure, i.e. You can still use (Oracle) database authentication, rather than using SSO.

"Sorry for e-mailing you directly. I read your blog a lot.

Kind of in regards to:

"...and then license and implement the Oracle Business Intelligence 10g component of Oracle Application Server (or now, more properly, the component of Oracle Fusion Middleware). "

At; http://www.rittman.net/resources.html

I was wondering if you have any insights (or gut feelings) about the direction Oracle is going with their Fusion product. I started to get worried when they released iAS 3 without an enterprise version. Does this mean they will be stripping OWB, Disco, Portal, etc. and charge for them separate? Or is it just as usually they are a year behind?"

Well, first off it's worth saying that, contrary to what people might think, I've got no special inroads or contacts into what Oracle are doing with their products. I don't work for Oracle and no-one from Oracle lets me in on what's going on with the products, all I know is what I pick up from user group events, what's on the Web and just general intuition. Having said that, I wouldn't read too much into the 10.1.3 release of Oracle Application Server not coming with an Enterprise Edition and new versions of Discoverer, Reports and so on. A couple of years ago, Oracle released a version of Application Server – 9.0.3 I think – that contained, again I think, OC4J as the Java container and a number of other Java upgrades on what was in 9.0.2, but that didn't contain updated versions of Discoverer, Portal and so on. A year or so later, version 9.0.4 was released that contained the full Enterprise Edition update. My guess therefore is that there's another version of Application Server 10g Enterprise Edition coming up, perhaps to be launched at Open World 2006, that will be the 10.1.4 version and will contain upgrades of all the products mentioned. I've heard on the grapevine that there'll be an updated version of Discoverer Plus relational, that has support for subqueries, and Discoverer Plus OLAP, which has support for custom aggregates (referred to as the “Armstrong” release) and I expect that's where you'll get your new version of Enterprise Edition.

Beyond that, who knows (outside of Oracle corp.) what Fusion will bring. My guess is that whatever future BI platform Oracle build will incorporate elements of Oracle's current offerings and those it's acquired through the Siebel merger – these postings by myself and Abhinav Agarwal take a look at Siebel Analytics and what it might offer the Oracle Fusion technology stack. This blog posting by Jeff Nolan is quite interesting as well, with the following comment about Discoverer and Oracle's future BI plans : “Also, a couple of weeks ago I heard another rumor that the Oracle Discover and BI teams were being "decimated" to make the way for Siebel Analytics to become the flagship offering operating as a standalone business unit under Larry Barbetta reporting directly to Chuck Phillips.” Beware though as this could just be “a rumour about a rumour” bit its clear that there'll be some interesting news about Oracle's BI plans in the next 12 months.

Another question about Discoverer:

"We use a host of Oracle Financial Applications up and running, and currently we are on version 11i. We use Discoverer for a lot of our client data, and because our end users are becoming so savvy with the application we have begun thinking we can leverage Discoverer for our Financial Reporting on the GL side.

My basic question is this, is there a tool or application that can work with Discoverer, that will allow us data input into Discoverer pages themselves. I know we can use the application for the reporting side, but we would like to use it for the data input function that comes with budgeting. I have looked at OFA/OSA and Oracles new EPB product. But frankly I think the applications I have seen are far to complicated for our business use. Most of our Financial Reporting is fairly straight forward.

I think you for advice that you can give me....thanks."

No, I don't know of any way you can do data input using a Discoverer table or crosstab, they're read-only views of data. As I mentioned earlier, the BI Beans crosstab appears to have an “edit” mode but that route requires you to build your own application and use the OLAP Option. Most customers of ours do their data input using either an Oracle Financial Analyzer data entry form, or use OFA but enter data using Microsoft Excel, and you can typically implement OFA in about 20 or 30 days. Beyond that, you could include in your Discoverer report a hyperlink to a separate Web application that allows users to enter data, with the hyperlink including an automatically generated reference to the set of data that the user is viewing. Other than that, I guess Discoverer is just not a “data entry” tool.

Finally, a “meta” question about the site itself:

"Mark

You have designed and maintain an interesting and cool Blog site. The look and feel is something like HTMLDB. I was wondering what underlying software drives this system

Appreciate your reply"

Well... yes, the site has an HTML DB (or Oracle Application Express, as it's now known) look and feel, coupled with Discoverer, Grid Control and all the other recent Oracle Web-based applications. But it's not built using any Oracle software, it all actually runs using Movable Type 3.2 and an underlying mySQL database. Here's how I built it.

The first thing I did was put together the tabs and the navigation element of the site, and I did this by putting a tab structure together using HTML DB. I then looked at the HTML it generated, the CSS stylesheet and the images it used, and then extracted these from the rest of the page and built an HTML template up using these elements. I then started up Oracle Database 10g Database Control on my laptop and extracted the HTML that produced the menu sidebar down the left-hand side of each page.

With these two elements, together with the CSS used to provide the decoration for links, the page fonts and so on, I then built up a template using Microsoft Frontpage, and then turned this into a Moveable Type template by adding all the tags for the entry body, title, comments and so on. I then reused the template for all of the “static” pages on the site, the resources pages, links, “about me” and so on, until I'd built up the whole site. I made a start on it after I'd left work for the Christmas break, fully expecting it to take a couple of days – in the end I spent most of the Christmas break putting it together and finished a day or so after the New Year. So – although it uses Oracle design elements, there's no Oracle software there, just Moveable Type, Frontpage and mySQL. Maybe some time in the future I'll port it to Oracle Express Edition and HTML DB, but the blogging framework you get with MT is pretty useful and it'd take a lot of work to reproduce this in HTML DB.