Your help is needed

Are you interested in pushing the envelope for inter-connectivity between the Oracle BI tools and Microsoft Office? If so, we want to hear from you.

In co-operation with Armstrong-Smith Consulting, Oracle is looking to talk to and possibly meet with customers in the US that are using any form of Microsoft Office / Excel plug in for their BI data and reporting.

Thus, if you are using one of the following:

• Oracle Excel Spreadsheet Add-In 10.1.2
• BusinessObjects Live Office
• MicroStrategy Office
• MIS Plain - the Excel Add-In for Microsoft SQL Server
• Hyperion Essbase Spreadsheet Services Add-In
• OutlookSoft, and so on...

and are interested in talking or meeting with someone from Oracle, please let me know by dropping an email to me, and I will make the arrangements.

Third party scheduling and emailing - 1

Of the many missing "features" of Discoverer is the ability to schedule and email reports. As many of you are are, scheduling can be done using one of a multitude of third-party scheduling tools. The one that I like is the native scheduler that comes with Windows XP.

Coupled with Discoverer Desktop's command-line interface it has always been possible to schedule the execution of a Discoverer workbook and export the output in a selected format, to a named file in a named folder. The difficulty has been in the emailing of that report. While there are a myriad of ways to email something, one of the simplest involves a product called Febooti. I discovered, pardon the pun, this product a few days ago and purchased a commercial license straight away. Now I can honestly say that all of my emailing problems have gone away and I can finally put this issue to bed.

With febooti Command line e-mail you can:

• Send email from the dos prompt to any number of recipients.
• Send attached files, text or HTML with embedded pictures.
• Send email using CC and / or BCC (Blind Carbon Copy).
• SMTP authentication (PLAIN, LOGIN, NTLM, CRAM-MD5).
• Secure connections by using -SSL and -STARTTLS.
• Set SMTP server address and SMTP server port.

Advanced features:

• Create batch files to send email from any* program.
• Easy-to-use command line switches with alias support.
• Unlimited TO, CC and BCC number of recipients.
• Take parameters from external text file or files.
• e-update - checks for updates automatically.

I will be posting more information on this subject, including example scripts and a white paper during the coming weeks. For more information see my website (link).

Nice feedback

I received some feedback on my MetaLink postings a couple of days ago. Because it feels good to get nice feedback I asked Melinda if I could share her thoughts and she has given me full permission to do so. By the way, I also answer questions on the OTN Discoverer forum. If you want to see my recent OTN postings click here.

Here is Melinda's feedback:

I attended your pre-conference Discoverer session at this year's OAUG conference in Nashville. I just wanted to drop you a quick note and say THANKS for replying to the Discoverer questions in the Metalink Technical Forum. I periodically read through the questions to see if anything is relevant to me, and must say I really appreciate your answers. You have a wealth of knowledge and sharing them with the Discoverer community through this forum is extremely helpful and allows me to learn more through you then spending days bothering our DBAs and researching on my own.

I'm not sure if Oracle contracted with you to post responses (they should !) but I really appreciate your taking the time to help us out.

Thanks again,
Melinda Bedford
State Corporation Commission

The trouble with alternate sorts and custom folders

Discoverer's alternate sort is one of those wow features that once learned has a tendency to be used over and over again. When the alternate sort is based on a small, known set of values created in a custom folder the end result is just what the doctor ordered. However, when the SQL inside the custom folder is dynamic and is drawn from one or more database tables then, because of a little-known feature of the nature of alternate sorts, Discoverer performance can and will deteriorate. The use of the alternate sort is therefore like a double-edged sword. On the one hand, you can create lists of values in exactly the order requested by the users. On the other hand, performance can down the drain - big time!

So why does the performance go down? It goes down because whenever a user includes an item in a workbook that has an alternate sort associated with it, Discoverer embeds the SQL used in the custom folder within the query for the workbook. It does this just in case the user wants to sort by the item. As you probably know, when a user adds a sort on an item that has an alternate sort, Discoverer will sort the workbook data in the order as defined by the alternate sort. The only way it can do this is by including the alternate sort code within the query.

Let me show you using an example from my own database, the one used in my Discoverer 10g Handbook. I have a custom folder called Prod Size Lookup containing the following code:

SELECT 'MINI' PRODSIZE, 1 SEQUENCE FROM DUAL
UNION
SELECT 'SMALL' PRODSIZE, 2 SEQUENCE FROM DUAL
UNION
SELECT 'MEDIUM' PRODSIZE, 3 SEQUENCE FROM DUAL
UNION
SELECT 'LARGE' PRODSIZE, 4 SEQUENCE FROM DUAL;

This creates a folder with two items, one called Prodsize and one called Sequence. I then created a list of values based on the Prodsize using the Sequence as the alternate sort. This allows my users to see the values in a more natural order, the order shown above. Similar code can be used for days of the week and months of the year. So far so good. Next I created a simple workbook that queried just the Prodsize. Here is the code that was generated by Discoverer:

SELECT
o100558.PRODSIZE AS E100726,
as100626_100726_OLD AS as100626_100726_OLD
FROM GSW.GS_PRODUCTS o100558,
( SELECT o100572.PRODSIZE AS as100626_100726_OLD_2,
MAX(o100572.SEQUENCE) AS as100626_100726_OLD
FROM
( SELECT 'MINI' PRODSIZE, 1 SEQUENCE FROM DUAL
UNION
SELECT 'SMALL' PRODSIZE, 2 SEQUENCE FROM DUAL
UNION
SELECT 'MEDIUM' PRODSIZE, 3 SEQUENCE FROM DUAL
UNION
SELECT 'LARGE' PRODSIZE, 4 SEQUENCE FROM DUAL
) o100572 GROUP BY o100572.PRODSIZE)
WHERE ( (0100558.PRODSIZE = as100626_100726_OLD_2(+)));

So what happened here? First of all, I bet you are amazed at the amount of code that has been generated. As you can see, Discoverer has not only embedded my custom SQL within the main query but has also added an outer join, shown as(+), between the two components. Now, because my list of values is a small, known set of items, this additional code has a marginal impact upon my query. My explain plan looks like this:

SELECT STATEMENT
HASH JOIN OUTER
TABLE ACCESS FULL GSW.GS_PRODUCTS
VIEW DRAKE
SORT GROUP BY
VIEW DRAKE
SORT UNIQUE
UNION-ALL
FAST DUAL
FAST DUAL
FAST DUAL
FAST DUAL

So now let's move into the real world, the world of Oracle E-Business Suite, where the custom SQL is executing another query to get the list of values. Using such custom SQL with an alternate sort causes Discoverer to generate code such as this:

SELECT /*+ ALL_ROWS */
o114210.PERIOD_NAME AS E187845,
as120354_187845_OLD AS as120354_187845_OLD
FROM GL.GL_JE_HEADERS o114210,
( SELECT o779427.PERIOD_NAME AS as120354_187845_OLD_2,
MAX(o779427.SORT_KEY) AS as120354_187845_OLD
FROM
( SELECT PERIOD_NAME,
PERIOD_YEAR,
REPLACE(PERIOD_YEAR,TO_CHAR(PERIOD_NUM,'09'), ' ') SORT_KEY
FROM GL.GL_PERIOD_STATUSES
WHERE
APPLICATION_ID = 101 AND
SET_OF_BOOKS_ID = 1 ) o779427
GROUP BY o779427.PERIOD_NAME)
WHERE ( (o114210.PERIOD_NAME = as120354_187845_OLD_2(+)));

You can see that this may have an impact of performance. It may well bring back more rows of data than expected so the user may well have to check the Hide Duplicate Rows box in the Table Layout tab of the Edit Worksheet dialog box.

By the way, the same query that I used at the beginning of this posting looks like this when I have no alternate sort:

SELECT O100558.PRODSIZE
FROM GSW.GS_PRODUCTS O100558;

and my explain plan looks like this:

SELECT STATEMENT
TABLE ACCESS FULL GSW.GS_PRODUCTS;

Therefore, when managing alternate sorts, you need to:

• be careful and only use them when the list of values is either fixed and can be generated by a SQL statement that does not access the database, or the list of values is small and the SQL statement is extremely efficient and does not generate duplicate values.
• be frugal and don't get carried away. Create them when absolutely necessary, and not just because the users asked for them.
• test them out with realistic before and after scenarios. This way you can make sure that performance is not being adversely affected, and if it is - don't use them, simple as that!

For all of you who have asked why Discoverer sometimes adds an outer join, take a close look at your End User Layer. The inappropriate use of alternate sorts could be one of the reasons.

More to toolbar icons graying out

Are you using Discoverer 10.1.2 with the Sun Java 1.5 plug-in? If so, you may have experienced one or more of the following:

• Not being able to use a list of values dialog.
• Not being able to enter a workbook name in the 'save' or 'save as' dialog without switching to another application and coming back.
• Message about the Digital Signature / Digital Certificate being expired.
• Time Fields always shown as zeros (0) in Plus/Viewer.
• Color picker not working correctly.
• Toolbar icon greying out intermittently.

You may well be interested in taking a look on MetaLink for the following note: 367389.1 which describes all of the above symptoms. The note also states that a recommended patch fix 5345528 has been released. This patch is a secure patch and only available from Oracle if you raise a service request (SR) on MetaLink and state that you are having one of the issues addressed by the patch. Oracle will then give you a password that you can use to download the patch.

Here is a full list of the "bugs" fixed by patch :

4206793 - TCH11IX JDK50 UNABLE TO INPUT SHEET NAME/WBK NAME, HAVE TO TRY FEW TIMES
4360769 - JDK 1.5 DROP DOWN LIST DONT SHOW CURRENT SELECTED ITEM WHEN PLUS USES JDK1.5
4392717 - IF THE CALCULATION ITEM VALUE IS 0 THEN IT IS DISPLAYED AS 00 IN DISCOVERER PLUS
4493163 - OPENING IN PLUS WORKBOOK CREATED IN DESKTOP FAILS
4567795 - JEWT 4.2.35 SELECTED COLOR NOT GETTING PICKED UP FROM THE FORMAT DIALOG.
4631865 - DISCO PLUS WITH IE IN WINDOWS 2003 AS CLIENT HAS PAGE REFRESH ISSUES
4707854 - NLS TIME FIELD IS ALWAYS SHOWN AS ZEROS IN PLUS AND VIEWER
4778465 - PATCH 4707854 FIXES BUG 4392717 FOR PLUS BUT NOT FOR VIEWER
4926873 - PLUS PRINT FIT TO PAGE DOES NOT WORK ON HYPERTHREADED CLIENT PCS
4926890 - PLUS PRINT MISSING TITLES, HEADER; FOOTER ON HYPERTHREADED CLIENT PCS
4939445 - DISCOVERER PLUS EXPORTED XML FILE WITH TITLE PARAMETERS CREATE 0 FONT SIZE
5043830 - SECURITY CERTIFICATE HAS EXPIRED OR IS NOT VALID WHEN ACCESSING PLUS
5085248 - PLUS SAVE DIALOG NOT WORKING WITH MLR 5047079 AND SUN PLUGIN 1.5.0_06

A couple of database scripts

Here's a couple of scripts that I thought you might be interested in.

The first returns the current size of the database in gigabytes, rounded to two decimal places. It ignores the TEMP and the UNDO segments.

SELECT ROUND(SUM(BYTES)/1024/1024/1024,2) DB_SIZE
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE NOT IN ('TYPE2 UNDO','TEMPORARY');

The second displays a list of the segments that you have. Using this list you can identify the UNDO segment(s) that you supply to the above script.

SELECT DISTINCT SEGMENT_TYPE FROM DBA_SEGMENTS;

Non-aggregated items and joins

The message "more than 1 of the detail folders users non-aggregated items" means that you must have a numerical item using SUM in one folder and using Detail in the other. It could even be the column that you are joining on. Common examples of this are Sales Order Numbers and Line Numbers. If these are defined as numbers, as opposed to VARCHARs, by default Discoverer will apply a mathematical aggregator to these. This could be SUM or it could be Detail depending upon what you chose as the default when you created the business area.

However, later on you may have added a new folder and inadvertently assigned the other aggregator. So, what can you do? Well, there are two ways of handling this. You could go through and change the aggregator so that they align but to me this is still wrong if the data is something like a Sales Order or Line Number. For these, you need to change the Default Position to one of Top, Page, Side or Top or Side. Basically you want it to be defined as anything other than Data Point.

To check the above, use this workflow:
1. Launch Discoverer Administrator
2. Open the business area in question
3. Open one of the folders
4. Look at your items, particularly those with a little calculator icon alongside, and right-click on the item
5. From the pop up menu, select Properties
6. In the Item Properties dialog box, look at the current setting for the property called Default Position
7. If this setting is wrong, click in the cell and select the appropriate setting - I usually select Top or Side
8. Click Apply
9. Go back to step 4 and repeat steps 4 to 9 until you have checked all of the items
10. Click OK to close the Item Properties dialog box
11. See if you can now create the join

Discoverer will create an outer join if the column you are joining can have null values. Basically if there is no database constraint defined matching up the columns Discoverer will assume that the columns can be null, therefore creating an outer join.

I will be posting some more very interesting Discoverer snippets and tricks in the coming weeks so stay tuned. In the meanwhile, if you have a question or trick that you would like to see published, drop me a line at michael@learndiscoverer.com