Michael's Discoverer Blog is up and running now

I had posted a couple of weeks back that Michael Armstrong-Smith had started a Discoverer blog (link to post, link to Michael's blog). The blog is now fully functional, and with a nice look and feel (LAF in Oracle-speak), Michael has been busy adding lots of very useful posts. Also included are posts related to his latest book, the Oracle Discoverer 10g Handbook, with clarifications (link) and errata (link).

I know I had said I would be posting excerpts from the book, but between a viral infection, a holiday, wife's trouble with a root canal infection and a wisdom tooth, daughter's indigestion, a convocation, and other stuff at work, I haven't had that much time. Hopefully things shall change for the better next month (which is tomorrow!!).

Did I say convocation? Yes!! I am now an MBA from the Indian Institute of Management, Bangalore (popularly abbreviated as IIMB) (see more on this blog). And a gold medallist at that (never hurts to do some self-publicity!).

Oracle and Office Interoperability Center on OTN

People may have missed this: there is a new sub-site on OTN for "ORACLE FUSION MIDDLEWARE and Microsoft Interoperability" (link to OTN page), which brings together under one sub-site all of Oracle's products and technologies as they relate to Microsoft Office.

There is also a new doc that has been released as part of the Oracle Application Server 10g (10.1.3) release (Part No B25781-01), titled "Oracle Application Server Developer's Guide for Microsoft Office Interoperability" (link to HTML doc, link to PDF doc).

To prepare this document a virtual team comprising of product managers and development contacts from different Oracle product groups was assembled and each product team then contributed their time and resources to bring to completion chapters for their specific product (or technology). One of the takeaways that people should get from reading this document is that even today there are a lot of areas where Oracle and Office integrate and there are lots of technologies that can be leveraged to bring more of Oracle's technologies and products closer to the end user using Office as an interface.

Here is a list of the chapters from the developer's guide:

• Understanding Microsoft Office 2003 Extensibility Technologies
  Which covers such topics as XML Schemas, Smart Technologies (Smart Documents, Smart Tags, Smart Clients, Smart This and Smart That), Task Panes, Research & Reference Services, and more.
• Understanding Oracle Application Server Interoperability with Microsoft Office
• Part II then gets into the specifics of building Microsoft Office Interoperability technologies using Oracle products and technologies.
  
• Creating Smart Documents That Interact with Self-Service Business Processes
• Completing Forms and Entering Data Using Microsoft Office
• Securing Smart Documents and Web Services
• Delivering Business Activity Monitoring Alerts and Reports to Microsoft Outlook
• Delivering Business Intelligence Information to Microsoft Excel
  This is the chapter that covers Discoverer and all the rich and different ways you can get static as well as refreshable and live information into Excel - via plain CSV (Comma Separated Values), or Excel HTML, binary Excel with formatting preserved, or export Discoverer crosstabs to Excel as Pivot Tables, or the Web Query (iqy) format, or using the OracleBI Spreadsheet Add-In that lets you use the familiar BI Query Builder and Calculation Builder to build and edit queries inside Excel.
  
• Managing Tasks and Collaborating in Microsoft Outlook
• Provisioning User Identity Information and Alerting Microsoft Outlook Contacts
• Accessing in-Context Web Information and Invoking an Enterprise Portal
• Saving Microsoft Office Documents to the OracleAS Portal Content Repository
• Delivering Enterprise Reports to Microsoft Office with Oracle Reports

Oracle BI Suite Standard Edition

A few days, or maybe a few weeks ago, some over-enthusiastic, mis-informed BI vendors (actually, just one) jumped on the FUD wagon and started spreading rumours that customers should think of switching from Oracle'e BI products and onto their under-featured suite (the thought of BI vendors being mis-informed is quite a dichotomy if you think about it...) because of perceived 'uncertainty' over Oracle's BI strategy and vision.

Oracle's Business Intelligence Strategy Briefing in New York city on March 22, 2006 certainly put an end to that poppycock (link to meaning: the word is American in origin, and not British!), where Oracle made known its commitment and vision and strategy for the business intelligence market, both in the tools space and in the applications space (also read Mark Rittman's excellent initial analysis of the event and announcements - link to Mark Rittman's post).

So what happened to Discoverer: it is well and alive (of course), and has a rich pipeline of planned functionality. We in the BI group are working on defining and closing down on the features we want to include in the next release of Discoverer: product managers, development, QA, everyone!
And it is available, today (link to product page).
And you can download it from the Oracle Technology Network, today (link to download page).

As you would know, Oracle announced three packages of its BI products (link to Oracle BI page on OTN), and Discoverer is part of the Oracle Business Intelligence Suite, Standard Edition. So, what forms part of this edition?

• Oracle BI Discoverer: Relational and OLAP data access and user-driven customizable dashboards
• Oracle BI Warehouse Builder: Rapid design, deployment, and management of data and metadata
• Oracle BI Spreadsheet Add-in: OLAP data access from within Excel spreadsheets
• Oracle BI Beans: For building powerful custom business intelligence applications
• Oracle Reports Services: High-fidelity enterprise reporting

Request for feedback:
In my new-found spirit of trying to make this blog more interactive, and to solicit opinion from the substantial readership that this blog enjoys, I request people to spare a couple of minutes (or longer if they so choose) and drop in a line or two on what they would like to see in the next release of Discoverer, and why.
You can click the comments link at the bottom of this post and leave your comments there, or if you want you can email me at abhinav.agarwal at gmail dot com and send me your feedback in complete confidence.

Lastly, I think I may possibly have been just the tiniest bit harsh in characterising this BI vendor's product offerings as under-featured.

Additional Links:
Webcasts from Oracle Business Intelligence Strategy Briefing - New York City, March 22, 2006 (link to page)
Business Intelligence & Data Warehousing Technology Center (link)

Oracle's BI Strategy Briefing

I am unable to post in detail right now (lots of other things at work that are nearing or past deadline!), but here is the news in brief:
You may have heard from different quarters that Oracle was going to announce its BI strategy to the public on March 22, 2006 in New York City. The event took place yesterday, and the recordings are available on Oracle.com at this page (link).

The main speakers were Charles Philips, President of Oracle, Henry Morris, Group Vice President and General Manager, Integration, Development and Application Strategies, IDC, Thomas Kurian, Senior Vice President, Oracle, and Steve Miranda, Senior Vice President, Oracle.

I hope to go into some detail on this in later posts, but the following three things should be clear now:
Oracle has made it a visible priority to become a leader in the BI market.
Oracle has a suite of products, integrated and best-of-breed, that are aimed at the SME as well as the enterprise markets, and are available today.
There is a rich pipeline of great functionality and enhancements that is on the way.

What do people think about these developments? I would love to hear what people think about the presentation as well as what would they like to see from Oracle in the coming months.

You can either post your feedback on this blog, or if you want, drop me an email at abhinav.oracle at gmail.com (I am quite hesitant to give out my work email on the web... Even though Oracle's spam filters are awesome and do a great job of filtering out spam, I think it is better to be careful than sorry. Unlike free email accounts, I really can't change my Oracle email id without changing companies!)

The Discoverer 10g Handbook from Michael Armstrong-Smith

Michael Armstrong-Smith's latest book, Oracle Discoverer 10g Handbook, co-authored by his wife Darlene Armstrong-Smith, updated for Discoverer 10g, and based on the 10.1.2.45.46C release, is out due later this month. Michael tells me that the book is with the printers, and that McGraw Hill is expecting the book to come back shortly. Therefore it should start shipping by the end of the month, or sooner.
You can view the book details on Michael's web site (link to site) or on Amazon.com (link to Amazon.com).

The book, apart from being extensively updated for the 10g release of Discoverer, has separate chapters for Discoverer Viewer, Discoverer OLAP, and Discoverer Portlets.
Mark Rittman (link to Mark's blog), Oracle BI & DW expert and Oracle ACE (link to Mark's ACE profile), has written the chapter on Discoverer OLAP.

Over the next few weeks I shall be posting more details about the book, including excerpts from different chapters (I acted as technical editor to the book, so I have some knowledge of the book and its contents :-). The biggest update to Discoverer in a long time now has the best and most comprehensive book to go with it.

How To Customize The Export Formats List in Viewer

In my previous post (link to post) I described how to add a quick export link to the main Viewer worksheet page that would let you export the currently opened Discoverer worksheet to a supported format.

This is kind of a followup post to the earlier one. Here I show how it is possible to construct a Viewer export page with only certain export formats available.
To begin with, it is important to know which export format corresponds to which 'resource key'. Not all formats are supported for both relational and OLAP worksheets, as you would have seen. Some, like the HTML, CSV, PDF, etc... are supported on both relational and OLAP connections, while some others like 'Excel as Pivot Table' are supported on only relational connections, while there are some export formats that work for both relational and OLAP worksheets.
This is the list of supported Export ids and resource Keys For Discoverer 10.1.2.45.46C (this also works for 10.1.2.48.18):

ID Resource Key Extension Supported Connection
0 export.type.csv (*.csv) Relational\OLAP
1 export.type.excel (*.xls) Relational
2 export.type.excel.pivot (*.xls) Relational
3 export.type.html (*.htm) Relational
5 export.type.reports (*.xml) Relational
6 export.type.text (*.txt) Relational\OLAP
7 export.type.pdf (*.pdf) Relational\OLAP
8 export.type.dif (*.dif) Relational
9 export.type.slk (*.slk) Relational
10 export.type.wks (*.wks) Relational
11 export.type.prn (*.prn) Relational
12 export.type.gif (*.gif) Relational\OLAP
13 export.type.png (*.png) Relational\OLAP
14 export.type.iqy (*.iqy) Relational Non SSO
16 export.type.html.excel (*.zip) Relational\OLAP
17 export.type.excel (*.zip) OLAP

Getting down to the nitty-grtty, this is how a regular Viewer worksheet export page looks like. I have expanded the dropdown so you can see the differences between relational and OLAP connections.
This is the list of export formats you get to see for relational worksheets:
This list is missing the "Excel as Pivot Table" because the worksheet is a table and not a crosstab, in case you were wondering.

And this below is the export formats list you get to see for OLAP worksheets:
This then is the Viewre page that you want to modify. Actually, it is only the dropdown that you would be interested in modiying, removing certain export formats that may not be required andpresent a shorter, cleaner list to end users.

To do that (and you should be familiar with this by now), you have to locate where the UIX file for this export page resides. If you are unfamiliar with UIX then see some of my previous UIX Hack posts. Saves me time, saves you unwanted grief. After all, I am not a UIX expert by any stretch of the imagination, nor is it my intent to start a discourse on UIX here. Jonas Jacobi has a very good blog (link to blog) on UIX technologies, aptly called "Jonas Jacobi's JSF, ADF Faces and ADF UIX blog".

So, using Windows as my installation platform, I have Oracle Application Server 10g (10.1.2) installed on c:\ias, and that is where I go to.

i.e. "c:\ias\j2ee\OC4J_BI_Forms\applications\discoverer\discoverer"

Here you shall find a lot of files that have the extension "UIX". Yippee... so far so good. Be careful now... the intent is not to muck around with every .uix file here; rather, it is only one file that we are interested in mucking up (I think I shall have to explain in some other post why I have this fascination for the word 'muck'). Therefore, as is suggested before people embark on mucking expeditions, make a backup of the "ExportTypes.uix" file first and foremost. Ah yes, also read the disclaimers at the end of this post.

Now, here is where the lobotomy starts. Open the "ExportTypes.uix" file using a text editor like Notepad, TextPad, JEdit, et.... and scroll down to the place where you find lines like these:
Find the contents inside the "contents" tag:
<contents childData="${uix.eventResult.exportTypeList}">
<option value="${uix.current.exportTypeValue}" text="${uix.data.nls[uix.current.resourceKey]}" />
</contents>

And replace them with the following lines:
<contents>
<option value="1" text="${uix.data.nls['export.type.excel']}" />
<option value="2" text="${uix.data.nls['export.type.excel.pivot']}" />
<option value="7" text="${uix.data.nls['export.type.pdf']}" />
</contents>

If the lines above do not display properly, you can also use these screenshots for reference.
The highlighted lines below are what you have to search for in the ExportTypes.uix file:

And replace them with the highlighted lines below:

Having done these changes, you know what to do:
- Save and close this file.
- Restart your OC4J_BI_Forms instance. To do this you haveto login to your Oracle Application Server Control.
- Close and restart your browser.
- Connect to Discoverer Viewer, open up a worksheet, and navigate to the 'Export' page.

See the dropdown now. The dropdown now shows you only three export formats. You can now go back and edit the UIX file to add or remove as many export formats as you want. You have all the information you need available in this post now.
Ok... that's about all I have for this post. I do have a couple of things before I end this post.
If you specify a relational only export format while trying to export an OLAP worksheet (and vice-versa) you will get an error. What error? An error like the one shown below:

This is because when you let Discoverer construct the export formats dropdown list it know which formats to include for relational and which ones to include for OLAP. But here you have explicitly specified the export formats to show in the dropdown, and it is the same options that will be displayed, irrespective of the worksheet type, whether relational or OLAP. Therefore when you select a relational worksheet and specify that it be exported in an OLAP available format, then you are aksing for problems. Now, one could argue that the product should handle this instead of spitting out an error. But, firstly, it is handling matters by throwing out an error instead of doing something weird like not responding or churning out some garbage. Secondly, the whole notion of hacking UIX files is not encouraged or supported... you are basically hacking the product to do something that it was not built to do in the first place. That UIX technologies make so many things possible is an advantage, but needs to be used judiciously.

Oh, another thing... there is one more place in Discoverer Viewer where you can select the export type. Guess where? The email export type selection page! Remember? When you specify that a worksheet be emailed, you have to first select a format in which the worksheet should be emailed as. And here also you can customize the export formats dropdown to exclude certain export types. To do that you have to follow the same steps as above, but the UIX file that needs to be modified is "EmailExportTypes.uix": see the screenshot below. The file is in the same folder as where the "ExportTypes.uix" file is located. And the changes are the same, so I am not going to repeat them here.


Related UIX Hacks posts:
UIX Hack 1 - Removing the 'Connect Directly' section
UIX Hack 2 – Remove the Last Refresh Date from Discoverer Portlets
UIX Hack 3 - Removing the 'type' column from a portlet
UIX Hack 4 - Removing the worksheet breadcrumb
UIX Hack 5 - Add A Quick Export Link to the Worksheet Page

The Disclaimer:

• I am NOT suggesting that you go around and muck around with these UIX files.
• Any changes you make to UIX files are unsupported.
• Oracle Support will not support your installation if you run into problems as a result of making changes to these UIX files.
• You will have to reproduce any errors on an instance without these UIX changes for Oracle Support to help you.
• Any upgrades or patches you apply may overwrite custom changes you make to your UIX files.

Add A Quick Export Link to the Worksheet Page

It's been a while since I posted a 'UIX hack' post. In fact, too long. So it was time for one. This one had actually been sitting with me for some time, but had been buried in my email inbox. Sometime back a bright fellow from our Viewer development team had conjured this up in response to a customer query and I thought it would be very useful to publish as a post. There are actually two parts to it - this is only the first part.

As I usualy do, I shall illustrate this with lots of screenshots.

This, on the left, is a screenshot of a Discoverer worksheet in Viewer. On the left you can see the 'Actions' pane with all the regular links. The one that I have circled in red is the one of interest. It says 'Quick PDF'. New, isn't it? What is it? Well... it's a quick link to let you export the current worksheet to the Acrobat PDF format without having to go to the 'Export' worksheet page, select the format of your choice, and then click the 'Export' button to initiate the export creation process. While the link says 'Quick PDF', it could just as well have said 'Get the PDF', 'Gimmee the PDF' or something else. The point of this link is that sometimes users have the urge to export every Discoverer worksheet in sight to some other format, like Excel, PDF, CSV, XML, etc... So a quick link saves time.

So what happens when you do click the 'Quick PDF' link? It's the same as selecting the PDF export format option from the Export formats list and click the 'Export' button. The worksheet is exported to the PDF format, and the generated PDF file is presented as a link for the user to click and open in Acrobat.
This page is the standard Discoverer Viewer page that you would see if you went through the Export page first.

This above is the exported worksheet inside Adobe Acrobat.

So, the next, and logical, question is how to get this in the first place? The 'Quick PDF' link, and how to tie it to the PDF export action. As it turns out, it's really, really simple.

If you are not familiar with UIX or how Discoverer UIX files look like, I would suggest you read some of my earlier UIX hack posts - that will save me time and you some unwanted grief. Links to those posts are present at the end of this post (link).

Now that you have read those posts let's get down to brasstacks.

Browse to the folder where you your BI (or App Server if you don't have the standalone BI install) Oracle Home is. Then browse to the "Oracle_BI_Home\j2ee\OC4J_BI_Forms\applications\discoverer\discoverer\".... folder as shown in the screenshot below. This is the folder where all the UIX files used in rendering Discoverer Viewer (and Discoverer Portlet Provider pages also for that matter) are located.

Find the 'worksheet.uix' file and make a backup of it before you even so much as take a long look at it. (Working with files that you do not have a backup for is like looking at the sun without wearing sunglasses. Sooner or later something bad is going to happen). Having done that - taking a backup - open the file using a text editor like Notepad, TextPad, vi, JEdit, etc... Scroll down the file till you come to lines that look like the screenshot below.
Just above these lines insert the following lines (if the lines appear garbled it's because Blogger is not yet smart enough to escape < and > with with & l t ; and & g t ; )
<rowLayout>
<contents>
<cellFormat wrappingDisabled="true" >
<contents>
<link text="Quick PDF">
<boundAttribute name="destination">
<concat>
javascript:discoSWP('aForm','
<contextProperty select="ui:contextURI" />
<dataObject select="ACTION_SERVLET" source="constants" />
exportdata?event=ExportData&exportType=7');
</concat>
</boundAttribute>
</link>
</contents>
</cellFormat>
</contents>
</rowLayout>

Having done that, save the Worksheet.UIX file, close it, and login to the Application Server control. Restart the OC4J_BI_Forms instance. This may take a few minutes, so be patient: good things come to those who wait for them (better things may come to the impatient ones....).
Once the component has restarted re-login to Viewer and open any worksheet. You should see the 'Quick PDF' link now. If not, close and restart your browser (this seems to be an issue more with Firefox than IE).


So, there are probably two questions, at least, that you are itching to ask.
How could Australia score 434 and still end up on the losing side (link)???

The second question is how do you change the link so that it generates an Excel or HTML or Pivot Table or TXT or any other export? Because, you would have noticed that the URL associated with the PDF export action has an id associated with the exportType URL parameter (...exportdata?event=ExportData&exportType=7). The intrepid ones among you could start cycling through all values from 1 to a crore and see which ones map to which export formats. For the patient ones, I shall be posting another post, later this week, where I list out all the different export format ids, including how to customize the Export formats dropdown list.

Related UIX Hacks posts:
UIX Hack 1 - Removing the 'Connect Directly' section
UIX Hack 2 – Remove the Last Refresh Date from Discoverer Portlets
UIX Hack 3 - Removing the 'type' column from a portlet
UIX Hack 4 - Removing the worksheet breadcrumb

The Disclaimer:

• I am NOT suggesting that you go around and muck around with these UIX files.
• Any changes you make to UIX files are unsupported.
• Oracle Support will not support your installation if you run into problems as a result of making changes to these UIX files.
• You will have to reproduce any errors on an instance without these UIX changes for Oracle Support to help you.
• Any upgrades or patches you apply may overwrite custom changes you make to your UIX files.

Changed the post time and date to Mar 13, 3:03pm

The Discoverer Blog from Michael Armstrong-Smith

Am back from a week long vacation to attend a family wedding. A break was needed, and it felt goo-ood! I shall post photos on my personal blog in the coming days. Also, there are a couple of Discoverer posts that I have been intending to post, but other things came up that I had to attend to first.

I saw that Michael Armstrong-Smith, Discoverer expert par excellence, has started a new blog on Discoverer at http://learndiscoverer.blogspot.com/
There is only one introductory post there, so I am guessing that Michael is going to be ramping up pretty soon.