How Do You Debug OLAP DML from BI Beans?

Most BI applications simply provide read-only views on business data. However, some users do actually need to interact with data to perform data modeling and/or forecasting scenarios. Oracle’s OLAP environment provides an extremely rich and secure environment for providing this type of interaction. In a previous article in the Oracle magazine I explained how you to build a JSP page that allowed a user to select different types of forecasts. However, since SQL does not currently provide forecasting I used the multi-dimensional programming language OLAP DML, which is very similar to SQL.

In the process of creating this sample I discovered a number of issues that lead to some new features within BI Beans 10g. Most of the issues of combining server side programming (OLAP DML) with JSP development is actually debugging the whole stack. JDeveloper provides excellent JSP debugging tools and the JDev team have some excellent samples that can be found on their OTN home page.

However, trying to debug OLAP DML was not quite as easy. So as part of BI Beans10g we provided some additional debugging facilities to help OLAP DML developers. A generic AW is used to debug any OLAP DML operation that is called from a JSP page. This AW is shipped as part of the Common Schema samples and is usually installed into the CS_OLAP schema and is called AWDEBUG. It contains a program that controls the whole debug process, called BI_BEANS_UTIL. That program takes the following arguments:

• Context Name passed from Java Environment
• Current directory setting
• Name of calling program
• Mode of operation - START or STOP
• Should prgtrace be enabled
• Should badline be enabled
• Should monitor be enabled
• Should trackprg be enabled

The AWProgram JSP tag controls many of these arguments. The JSP tag has properties that allow the developer to control which of the features are enabled or disabled.

To show how the debugging process works lets review the What-If JSP page that is part of the Executive Insight demo. The page contains two buttons above the bar graph:

• Run Model – executes a new forecast using the OLAP DML program RUN_MODEL
• Reset – clears forecasted data using the OLAP DML program CLEAR_DATA

To enable the debugging process the AWDEBUG workspace needs to be attached to the current session. This is done using an AWCommand tag. The tag is added to the start of JSP page and is set to manually execute. Other options are “onRender” or “onInit” and these are self-explanatory.

The wizard that defines the tag allows the developer to type any OLAP DML command into a window. These are the commands that will be executed when the tag is called.

For this example, we attach the AW UTILS that contains the programs that will create the forecast data and also remove the forecast data for the “Reset” button. The second command attaches the AW that controls debugging.

The next step is to add the two buttons that will execute the OLAP DML and update the graph with the new data. These buttons can be defined using the AWProgram JSP Tag. The tag wizard prompts for the required inputs. The key fields are as follows:

• debugPath – this is the name of a directory object created within the database instance and points to a directory path on the server. All trace files will be written to this directory
• ShowTrace – enable trace of each line of code. This sets the OLAP DML option called PRGTRACE. This generates a file called program_name_PRGTRACE_timestamp.trc
• ShowBadLine – enables verbose error messages
• ShowMonitor – enables recording of execution timings of each line of code. This generates a file called program_name_MONITOR_timestamp.trc
• ShowTrackprg - enables recording of execution timings of each program. This generates a file called program_name_TRACK_timestamp.trc

Each file is written to the directory specified in the debugPath property. All tracing is enabled only for the duration of the execution of the tag. Once the tag has finished firing all tracing is automatically disabled.

The three files that are generated contain different information and are used to debug different situations. Once an OLAP DML program has been defined the next step is to ensure it is executing correctly. The execution path can be traced using the showTrace property. This will show, line by line, the flow of the program. Assuming the program is running correctly but performance is not as expected you can use the showTrackprg and showMonitor properties to evaluate how much time is being spent within each program and on each line within each program. This can highlight lines of code that are taking a long time to execute and that need optimizing. I will provide a detailed review of each of these trace files in the next article.

Standalone Discoverer 10g (10.1.2.0.2) clients now fully SSO compliant for E-Business Suite users

This announcement just came in from the Applications Technology group, that does the certification of Discoverer releases on E-Business Suite versions (among a lot and lot of other things).

I am reproducing it verbatim below:

ANNOUNCEMENT
Standalone Discoverer 10g (10.1.2.0.2) clients are now fully Single Sign-On-compliant for E-Business Suite users.

E-Business Suite end-users who access E-Business Suite workbooks via standalone Discoverer 10g clients no longer need to dual-maintain user credentials in both Oracle Internet Directory and FND_USER.

With this upgrade:

• Users may access E-Business Suite Discoverer workbooks via Discoverer's Connection Manager feature from standalone Discoverer clients, with user credentials managed in Oracle Internet Directory (NEW)
• Users may access E-Business Suite Discoverer workbooks via E-Business Suite menus via E-Business Suite Home Page
• Users may access E-Business Suite Discoverer workbooks via Oracle Portal and the E-Business Suite Applications Navigator and Applications Favorites portlets

Supported Platforms

• Sun Solaris
• Linux
• Microsoft Windows

HP/UX and AIX support for this configuration is not yet available. The documentation will be updated as soon as those ports are released.

For More Information
For full details, see Metalink Note 313418.1 (December 2005 version).

The note is titled "Using Discoverer 10.1.2 with Oracle E-Business Suite 11i", and has a Last Revision Date of 21-DEC-2005
Try this URL to directly access the note (you may be asked to login to Metalink first).
Also note, that the announcement above is meant to be shared with customers, lest you think I am divulging confidential info ;-)
Finally, please refer to the Metalink note for clarifications - I won't be able to provide you with answers in any great level of depth here.

UIX Hack 4 - Removing the worksheet breadcrumb

A Discoverer forum post on OTN last week asked how to remove the had a query on how to remove the worksheet breadcrumb from the Viewer page. You cannot do that by using the Viewer customizations feature available from the App Server Control (see Section 9.2 - Customizing Discoverer Viewer - of the Oracle® Business Intelligence Discoverer Configuration Guide, 10g Release 2 (10.1.2.1) for Microsoft Windows and Solaris Operating System (SPARC), Part No. B13918-03, link to doc on OTN). You can however do a lot of other customizations to the layout, and the look and feel.

To remove the breadcrumb, you have to get your hands dirty with UIX (see links to previous posts on UIX and UIX hacks in Discoverer at the end of this post).

Before I get into what is required (and it is really quite simple), I must warn you on two points:

• Such hacks are not supported and not encouraged. Why? Because Oracle Support will insist you reproduce any problem you have with your Discoverer installation WITHOUT these hacks. You cannot call/contact Oracle Support for any, and I repeat ANY, issues you may run into as a result of using these hacks. These hacks are not fully tested, nor are they guaranteed to work on all combinations of platforms. Any upgrade or patch you may apply to your Discoverer installation will most likely overwrite these customizations. And finally, because we do not guarantee that we will continue to use UIX in future releases. So any effort you expend into gaining UIX expertise for the purposes of Discoverer customizations may likely be useless in the future.
• Ok, I only had one point, albeit a long-ish point. Well, no! I do have a second point!!. The second point is that if you do create an unsightly furor over any problems with UIX hacks, I shall get into trouble, and shall most certainly be proscribed from writing on these hacks.
  

Ok, I have carried out my duty about warning you in letter and spirit. Let's move on with the hack now, shall we?

The offending breadcrumb in question is shown below, circled in red.
To remove this, you need to edit a UIX file. The file is located under the following folder: C:\ias\j2ee\OC4J_BI_Forms\applications\discoverer\discoverer. c:\ias is the folder on my machine where Discoverer is installed. Under this folder is a file named 'Worksheet.uix' (intuitive, isn't it?). First, make a backup of this file. Which can be as simple an operation as doing a copy-and-paste if you are using Microsoft Windows. Once this backup has been made, open the worksheet.uix file using a text editor like Notepad, vi, TextPad, etc...
Browse down the file till you find the line that starts with <breadCrumbs rendered=...
Select this line, and delete all lines starting with this line till the line that starts with </breadcrumbs>, including this last line also. Save the file, re-start your OC4J_BI_Forms instance, and re-start Viewer. You should not see the breadcrumb any longer.

An alternative, and a simpler alternative at that, is to find the line that says:
<breadCrumbs rendered="${uix.eventResult.notPortalMode}">
Change this to:
<breadCrumbs rendered="false">
Save the file, re-start your OC4J_BI_Forms instance, and re-start Viewer. You should not see the breadcrumb any longer.
If things didn't blow up to high heaven, the breadcrumb should no longer be visible.
Related UIX Hacks posts:
http://oraclebi.blogspot.com/2005/08/uix-hack-1-removing-connect-directly.html
http://oraclebi.blogspot.com/2005/09/uix-hack-2-remove-last-refresh-date.html
http://oraclebi.blogspot.com/2005/09/uix-hack-3-removing-type-column-from.html

From detail to summary in two clicks

My heart leaps up when I behold a crosstab... (to mis-quote Wordsworth). Imagine being able to tell your users that you can take about a quarter million cells of data, and summarize them into nine cells! Of course the assumption being that your data is amenable to that kind of extreme summarization. A picture being worth a thousand words I shall demonstrate, and illustrate, with screenshots. (I have never really tried to test that theory out.... is it really worth only a thousand words? Do you need a really large picture? Is there a formula that you can use to convert pixels into words? You get the drift; this can meander into a really exciting debate, one that I am not too inclined to enter, just not yet.)

Take the screenshot below. It is a table. It shows Profit, Sales, and Unit Sales data at the detail level. I.e. it shows data at the transactional level. I have included only the 'Region' level of the geography dimension. There are only three regions in my data set: East, Central, and West. Which is why these members repeat many, many times. But each row represents a transaction. And I have more than 87,000 transactions. This is a lot of data. You can imagine the excitement if this were to be a few million, billion rows. How do you make sense of this data? Or more precisely, how do you start to make sense of this data?
One way to do is to summarize this data. And if you are using Discoverer, you can summarize this data in about three clicks . How? Simple. From your menu click the arrow to the right of the 'Add Worksheet' icon (that would be the sixth icon in the Discoverer toolbar).
Or you could use the menu and select 'Edit' >> 'Duplicate Worksheet' >> 'As Crosstab'.

Up pops a screen where you can adjust the layout of your crosstab.
Click the 'OK' button and there you go! Nine cells of data. Profit, Sales, and Unit Sales have been summarized for each of the three regions.
Simple, yet powerful (or is it powerful, yet simple?). Discoverer has had this capability for many, many releases now. Yet, as I read and scout the web for news and views, I hear that there are vendors that are aiming to bring out similar functionality only next year, or the year after next. How original.

PDF Settings in Viewer

One post on the Discoverer OTN Forum (link to Discoverer OTN Forum) recently asked how to persist PDF settings in Viewer. I realized that today we do not persist these changes, neither across sessions or users, nor within the same session. Which can be a bummer at times. For example, what happens if I have a wide worksheet, and I want to not only change the print orientation from portrait to landscape, but also apply a scaling factor to the text size? Sure, I can do that by going to the Printable Page link and make the required changes here. However, if I want to email the same worksheet as a PDF attachment, or maybe take a PDF export of the worksheet, I lose those settings. Now, of course we shall provide a way to persist these settings across a session or even across users in the next release, but what about now? Well... I went over to our Viewer expert, Matthew, and of course there is a way!

The short one line explanation of the workaround is to make the changes using the 'Printable Page' link, generate a 'Preview' or the entire PDF report, and then use the worksheet breadcrumb link to return to the worksheet. This ensures that the PDF changes you made are available during the sesion to the worksheet. You can always undo these settings by clicking the 'Revert to saved' link.

Let's see with an example, and lots and lots of screenshots!

This is a slightly modified version of the 'New Features Examples' workbook that ships with Discoverer. As you can see, the worksheet is 'wide' - it has three measures, and three members of the geography dimension laid out on the row edge, resulting in nine columns of data.
If I generate a printable PDF sample from this report, you will see, unsurprisingly, that the some columns of the report spill over to the second page.
If I want to get them to fit on one page, I have a couple of options available. I could go to the 'Printable Page' screen, and change the paper orientation from 'Portrait' to 'Landscape', and also set a scaling factor of 50%.

With these settings, the resulting PDF generated (using the Printable Page option) now fits on a single page, width-wise. And as you can see, the text size also looks a lot smaller (should be 50% of the original size, but I have not tested that).

Now, click the worksheet link at the top of the page (Connect > Workbooks > Viewer Print Test - Conditional Formats > ) to return to the sheet. The PDF settings that you applied are now available within this session to the worksheet. Therefore, you can now click the 'Export' link and select PDF as the export format.
The exported PDF file has the same settings applied: paper orientation as well as the scaling factor.
You can also test this out with the email option; select PDF as the format to email the report as, and click the 'View attachment' button at the bottom of the 'Send Email' page.

Voila! PDF settings are available here also!

You could close this worksheet, open another worksheet or even another workbook. So long as you don't exit Viewer and remain in the same session, the PDF settings that you applied earlier using the Printable Page options shall be available to the worksheet.

Multiple Conditional Formats on the same item

During a presentation today, I realized I didn't about this cool feature in Discoverer 10.1.2 till our UI and usability expert pointed it out.
When applying conditional formats in Discoverer (OLAP), you can have more than one conditional format active for a given item. As long as they do not overlap, they keep getting applied to the item, which means each successive format simply applies itself over and above the previous formats.

I always find it better to explain things using images and screenshots. So if you have understood what I am saying, the screenshots below are quite tautological.

Take this worksheet I have created in Discoverer Plus OLAP. Quite straightforward; I have two measures - Costs and Sales, and one calculation - Margin.

I now start applying a series of conditional formats to the 'Margin' calculation. Each format has the same condition, for simplicity: "Margin Less Than or Equal to 0.75" (i.e. 75%). For this first format, I simply state that the item should be formatted bold. The preview pane at the right shows me how this format would look like.
And this is how my worksheet looks like. Note that two cells in the 'Margin' column are now bold highlighted, corresponding to 'Quarter 1, 2001', and 'Quarter 2, 2001'.
The second conditional format I apply is to format such cells (as meeting the "Margin Less Than or Equal to 0.75" condition) with a background of 'orange' (hex color code FF9900).
This background color format does not conflict with the earlier defined format, so all cells matching the condition get a background color of orange.

And finally I add a third conditional formt, with the same condition, but a different format. The font color is now set to be blue whenever the condition evaluates to true.
So now you can see below all three conditional formats existing in harmony, as none of the formats conflicts with the other.
If you now take a look at all conditional formats (Format --> Conditional Formats), you shall see that all my three conditional formats are active.

Let's now define a new conditional format, on the same item, that actually conflicts with an existing format. So, the fourth format I define still has the same condition (), but a format that conflicts with earlier formats: a cell background color of red and a foreground color of yellow (color:#FFCC00;background-color:#990000)
As I can see, this format now takes precendence over earlier defined conflicting formats.

See the Conditional Formats list, and you will see that while all earlier formats for the item are still active, the last conditional format defined takes precedence, and hence conflicting formats are suppressed.