Dashboards - a beginning

I was recently asked about dashboards and where one should begin when the boss comes in and says I want a dashboard. I thought about this for a short while and decided what I needed to do was step back and look at the dashboard concept and explain my understanding in simple terms. I share those thoughts here and invite your comments.

Dashboards are unique to an organization and what works in one place will not be suitable in another. But of course, it all depends on your definition of a dashboard. The one that I like and the one that keeps me out of mischief is this one:

A dashboard or dash board is a panel located under the windscreen containing indicators and dials such as the tachometer / speedometer and odometer.

I bet you never thought it was so easy.

Seriously, look again at this definition and you will see the foundations of business dashboards. It is not the dials such as the tachometer, odometer and fuel gauge that are important. It is atually not the numbers either.

What is really important is the meaning or significance (aka KPI) that we apply to these numbers. Thus, depending upon the situation, a speed of 100 mph might be considered excessive, particularly if being chased by an irate police officer down a busy city street. Do the same thing on a race track and you might be considered a menace for going too slow. But do 100 mph on an autobahn in Germany and no-one will bat an eyelid because it is perfectly acceptable. You can see that the gauge, in this case the speedometer, and the reading from the gauge, 100 mph, are by themself meaningless as a KPI. It is only when you apply the criteria which states that 100 mph must be highlighted in red because it is excessive that a real KPI is born.

The concept of dashboards in automobiles and in business are the same - they give us a snapshot of critical information at a moment in time. If you do happen to be running out of petrol the dashboard will start the indicator to bring this fact to your attention. It does this by turning on a light or sounding a bell when a certain low point in the petrol tank is reached.

Similarly, a business dashboard needs to provide all of the critical information that is needed to run an organization's daily operations. It will be a snapshot in time, probably midnight, that tells an organization if it is spending cash too fast; or whether the percentage of patients who needed a repeat visit is higher than 5%; or whether the number of requests for service this week exceeded the number from last week by more than 10%. You will notice that the common factor here is the rule being applied to the data to indicate that something needs to be brought to the organization's attention.

In a business, you can imagine that every employee has a steering wheel and an accelerator pedal. However, it is not necessary that everyone gets a full-blown dashboard. Since the user roles are different they do not need the same level and kind of information. The worker bees need to work and the managers need to manage. Typically only the higher executives will get the full dashboard priviliges. They want to manage by exception and will only become really interested when something out of the ordinary happens.

If an organization is truly managing by exception then it will have a goal to move routine work from the manager to the employee, this leaving the manager more time to manage. By creating a dashboard that displays the KPIs that the manager is interested in, a quick glance to see that all is green is all that is needed. Good KPIs, and thus good dashboards, reduce micromanagement which is good for everyone involved.

Thus, this is where you ought to begin. You need to find snapshot metrics that when compared to against a set of criteria will have a value above, equal to or below the criteria. Since the manager has access to the dashboard he or she can tell at a glance if the company is running out of petrol, or whether the dashboard can be closed for another day and he or she can get back to running the company.

Now that reminds me, golf anyone!

Do not upgrade to Internet Explorer 7

Hi everyone
Based on recent postings in the OTN forums and from investigations with my own clients I have to warn you not to upgrade to Internet Explorer 7 if you are using Discoverer Viewer. After you upgrade to IE 7 you will not be able to drill to a related item because somehow IE 7 prevents you from seeing the list of items that you could drill to. This is certainly true if you are using Discoverer 10.1.2.0.2. Unfortunately, I have no idea whether the latest 10.1.2.2 patch (link to Abhinav's recent posting) fixes this. Does anyone know and has anyone been brave enough to try?

An interesting observation here is that IE 7 is included in Microsoft's automatic updates for XP. Therefore, if you have automatic updates enabled you may come in tomorrow and if you are not paying attention you could find that you have been upgraded. Microsoft state (link) that when the update is downloaded you will be presented with a window that will inform you that IE 7 is ready to install. You should not click Install when so prompted.

IE 7 is currently not certified for use with Discoverer and is also not certified for use against Oracle E-Business Suite 11i (link to Steven Chan's recent posting) Until such time as Oracle certify IE 7 for use against Discoverer and / or E-Business Suite I recommend turning off Microsoft's automatic updates.

Here is a workflow to alter the upgrade policy on Windows XP:

1. Launch Control Panel
2. Click Security Center
3. Click Automatic Updates
4. Deselect Automatic by selecting one of the other options
5. Click OK

On my own PC I set this to Download updates for me, but let me choose when to install them. I will then be able to select which updates I want. I certainly don't want to miss out on the other updates, and I certainly don't want to install IE 7 by any act of carelessness or confusion on my part.

Are you Microsoft Vista ready?

You've probably heard that Microsoft will be releasing a new version of Windows next year. You've probably heard that the name of the new version is Vista, but is your PC Vista ready?

If you are not sure whether your Windows XP-based PC will be able to run Windows Vista, you can download the Windows Vista Upgrade Advisor RC.

Did you also know that Vista will be coming in six editions?

The planned six editions are:

Starter - will be exclusively* available in emerging markets, and is designed for a beginning PC user with additional tools and tutorials to make it easier to use.
* It is not scheduled to be available in the United States, Canada, the European Union, Australia, New Zealand, or other high income markets as defined by the World Bank.

Home Basic - for basic home needs such as e-mail and internet browsing.

Home Premium - for the best in home computing and entertainment.

Business - for small and mid-sized organizations. For small businesses, Windows Vista Business will help keep PCs running smoothly and more securely so you will be less reliant on dedicated IT support. For larger organizations, Windows Vista Business provides dramatic new infrastructure improvements, enabling your IT staff to spend less time focused on the day-to-day maintenance of PCs and more time adding strategic value to your organization.

Enterprise - for the needs of large, global organizations and those with highly complex IT infrastructures. Windows Vista Enterprise will include built-in tools to improve application compatibility with previous versions of Microsoft operating systems, as well as with UNIX operating systems. In addition to all of the features available in Windows Vista Business, Windows Vista Enterprise is designed to provide higher levels of data protection using hardware-based encryption technology. It also includes tools to improve application compatibility and enables organizations to standardize by using a single worldwide deployment image. Windows Vista Enterprise will only be available to customers who have PCs covered by Microsoft Software Assurance or a Microsoft Enterprise Agreement.

Ultimate - for both work and entertainment. According to Microsoft it will be the most comprehensive edition of Windows Vista. They claim it is the first operating system that combines all of the advanced infrastructure features of a business-focused operating system, all of the management and efficiency features of a mobility-focused operating system, and all of the digital entertainment features of a consumer-focused operating system. For the person who wants one operating system that is great for working from home, working on the road, and for entertainment, Microsoft says that Windows Vista Ultimate is the operating system that lets you have it all.

Click here to see more high-level information about the different editions of Windows Vista and see which one best fits the way you use your computer.

Of course, just because your PC is Vista ready I would not advise you to upgrade when it first comes out. Like me, you will have to wait until Oracle certifies the product that you are using.

However, you can rest assured that Armstrong-Smith Consulting will be getting hold of a copy of the new operating system just as soon as we can and will be testing Discoverer to see how well it works. I'll then be making further postings.

Prod Line example code

Hi everyone
I am frequently asked how to create a custom SQL folder that will generate a known list of values. The following two examples of SQL are identical in their output and you are free to choose the one that you prefer. In terms of performance, the first is more efficient but is a little more difficult to understand.

Both pieces of code produce a folder with two items: an item called PRODLINE and an item called SEQUENCE. You can use the SEQUENCE when creating an alternate sort. Have fun!

SELECT
DECODE(ROWNUM,
1,'MINI-WIDGET',
2,'SUPER-WIDGET',
3,'MEGA-WIDGET',
4,'WONDER-WIDGET') PRODLINE,
ROWNUM SEQUENCE
FROM DUAL
CONNECT BY LEVEL <= 4

or

SELECT 'MINI-WIDGET' PRODLINE, 1 SEQUENCE FROM DUAL
UNION
SELECT 'SUPER-WIDGET' PRODLINE, 2 SEQUENCE FROM DUAL
UNION
SELECT 'MEGA-WIDGET' PRODLINE, 3 SEQUENCE FROM DUAL
UNION
SELECT 'WONDER-WIDGET' PRODLINE, 4 SEQUENCE FROM DUAL

Full time Discoverer position

We have a current client ($19 billion dollar consulting firm) who is seeking a permanent Oracle Discoverer consultant to join their team as a full-time employee. Most of the work will be on the east coast, so the client prefers someone who lives east of Texas. The position does require weekly (Mon-Thurs) travel. My client has an excellent compensation and benefits plan, and offers great career growth. Because some of the client work is with government entities, all candidates must be US Citizens. If you are interested please drop me a line at michael@learndiscoverer.com and send me your current resume.

Back from vacation

Hi everyone
Did you wonder why I have made no postings recently? No, I did not give up blogging and I did not emigrate. I simply took my wife on a long vacation to Mazatlan, Mexico. If you have never been, I thoroughly recommend it, particularly at this time of year as the rainy season is at an end and the snowbirds have not begun to flock.

Anyway, I got back home to Tennessee in the early hours of this morning. I just need to get my feet back under the table and catch up with email then my postings will resume. I actually got less than 12 hours at home as I'm about to leave for Nashville airport to get on another plane. This coming wewk I'm giving Discoverer Administration training and mentoring to a new client in Baton Rouge, Louisiana. At least I get to spend a few more days in the sun!

Changing folder names and identifiers - the correct way

After you have made an important change to your EUL you always get hold of a sample of your users' workbooks and test them, right? Of course you do. I'm not even going to think about the consequences of not doing so. Well maybe a little but then that's really your problem not mine. Ok, well it's my problem too because it's at this stage that I get the all-important "help me" call.

You're probably aware that the very important objects to Discoverer are the folder names and identifiers. Oracle calls these primary objects for a reason. You're also probably aware that Discoverer embeds both the folder identifier and the folder name within the workbook. When that workbook is opened, Discoverer looks for a folder by the embedded identifier. If the identifier is not found then it looks for a folder by the embedded folder name. If the folder is located by either mechanism, phew, the workbook opens and runs. However, if neither are found then you are in deep trouble because the workbook will not open.

So, armed with that knowledge, you can now proceed with editing your EUL. If you have ever attended one of our training courses (link) or read our book (link), you will know that we always, always recommend giving folders meaningful identifiers and business names. Leaving folder identifiers as NEWFOLDER1 is asking for trouble when migrating environments, so please don't do it. Changing a folder name or identifier is a simple process but one that can get you, as the administrator into a lot of trouble.

To change a folder name or identifier, use this workflow:

1. Open Discoverer Administrator and connect as an administrator
2. Open the business area containing the folder you want to work with

3. Expand the business area and locate the folder
4. Right-click on the folder, and from the pop-up select Properties
5. The name of the folder is the first property, while the identifier is the last
6. Change one or the other - not both
7. When prompted that doing this could upset existing workbooks, click OK
8. Get your users to test

But what about if you have already got badly named identifiers and names, what should you do? You have to change them, that's what you do, and here is where a testing strategy comes into play. Whatever you do, you must NOT change both the identifer and name at the same time because the workbooks will never open. You need to decide what you will change first, perhaps the name, and make all those changes. You now need to ask or tell your users that they MUST open and RESAVE their workbooks. Remember, because you only changed the name Discoverer will locate the folder using the identifier. The resave process re-embeds the name. After you are sure that all workbooks have been opened and resaved, and only now, can you change the identifiers. Once again you have to repeat the process and tell your users that they MUST open and once again RESAVE their workbooks. At the end of this step, all of your workbooks should be in sync with all of your folders.

But, there is a gotcha, and that gotcha is the purpose of this posting

Just opening a workbook to prove that it still works is not enough because if the user closes it without resaving and you now proceed to change the identifier, that workbook will no longer open. I have seen this happen when the administrator was unaware that the workbook has to be resaved.

Releases and patch numbers - September 2006

Back in April of this year I published a release guide for Oracle Discoverer. This posting contains the latest information, as of September 18, 2006. I will endeavor to update this every six months or thereabouts with the latest information.

• Discoverer 10g (10.1.2.0.2) - This is the current and most up-to-date release and the one you should be on if you can. Notwithstanding the fact that this is the current version it is also an excellent piece of software. It has a new user interface and is packed with lots of new and exciting features. The current release is 10.1.2.48.18. No ECS or ES dates have been announced for 10.1.2.0.2. The latest open patch for all platforms is 5381031, which was released on July 11, 2006.

• Discoverer 10g (10.1.2.0.0) - The terminal release is 10.1.2.45.46c. You can upgrade to 10.1.2.48.18 by applying the 10.1.2.1 patchset which will bring you to the 10.1.2.0.2 code level. No ECS or ES dates have been announced for 10.1.2.0.0.

• Discoverer 10g (9.0.4) - There is no terminal release as of this time because 9.0.4 is still a supported product, although desupport (ECS) is scheduled for December 31, 2006. The recommended release is 9.0.4.45.07. Extended Support will continue until December 31, 2009.
  
  Note: The upgrade from 9.0.4 to 10.1.2 is a destructive upgrade. This is because, while 9.0.4 and 10.1.2 share a common EUL prefixing system, namely EUL5x, the End User Layers are different. Therefore, if you upgrade from 9.0.4 to 10.1.2 using the same schema be warned that you will not be able to use your 9.0.4 installation after the upgrade. You therefore may want to consider installing 10.1.2 into a separate schema, using Discoverer's export import mechanism to effect the EUL upgrade.

• Discoverer 9i (9.0.2) - desupported from July 1, 2005 with Extended Support being available until July 1, 2008. When installed from CD, the release was 9.0.2.39. The terminal release was 9.0.2.54.10. Patch 3079459, for Administrator and Desktop, which upgraded the software to 9.0.2.54.10, was released on August 1, 2003. Various one-off patches to support Administrator and Desktop bug fixes have been released since, with the last being on December 16, 2004.
  
  Patch 3079487 is the one which upgrades Plus and Viewer to 9.0.2.54.10. It was released on August 5, 2003. Oracle continued to release one-off Plus and Viewer patches until desupport with the last one, 4104258, being released on February 18, 2005.
  
  You are urged to upgrade to 10.1.2.0.2 as soon as you can.
  
  Note 1: The upgrade from any version of 9i to 10.1.2 is a destructive upgrade. This is because, while 9i and 10.1.2 share a common EUL prefixing system, namely EUL5x, the End User Layers are different. Therefore, if you upgrade from 9i to 10.1.2 using the same schema be warned that you will not be able to use your 9i installation after the upgrade. You therefore may want to consider installing 10.1.2 into a separate schema, using Discoverer's export import mechanism to effect the EUL upgrade.
  
  Note 2: So long as you are running at least 9.0.2.53, the upgrade from 9i to 9.0.4 is a non-destructive upgrade. This is because the two systems share a common EUL. Therefore, if you upgrade from 9i to 9.0.4 using the same schema you will still be able to use your 9i installation after the upgrade.
  
  Note 3: Discoverer 9i was never certified for use against E-Business Suite.
  
  
• Discoverer 4.x (with E-Business Suite) - will be desupported from October 31, 2006. According to Oracle "since Discoverer 10g (10.1.2.0.2) is fully certified, we HIGHLY recommend implementing or migrating to this release". Extended Support (ES) will continue until October 31, 2009. The terminal release for use with E-Business Suite was 4.1.48.08. When installed from CD, the release was 4.1.37. It is interesting to note that 4.1.48.08 was issued only as a one-off patch for Administrator and Desktop as 3451630 on February 18, 2004. This release can be applied on top of any 4.1 release from 4.1.37. However, if applied to any release prior to 4.1.46 it will upgrade the EUL. The 4.1.48.06 patch for Administration and Desktop, 3201601, was released on November 25, 2003 and did an EUL upgrade. Since these major releases, Oracle has continued releasing additional one-off patches, mainly for bug fixes, to support E-Business Suite, with the most recent being 5231872 on June 8, 2006.
  
  Patch 3201610, for 4i Plus and Viewer, was released on November 25, 2003 and upgraded the system to 4.1.48.06. The required E-Business Suite release is 4.1.48.08 which was released on February 18, 2004 and is patch number 3451636. Since then, Oracle has continued to release various one-off patches, with the last one being 3759425 which was released on February 15, 2006.
  
  
• Discoverer 4.x (stand-alone, not as part of E-Business Suite) - desupported from June 30, 2004 with Extended Support (ES) available until June 30, 2007. You should migrate to 10.1.2.0.2 as soon as you can. The final release for 4.x not running against E-Business Suite was 4.1.48.06. See above for patch numbers.
  
  Note: The upgrade from 4i to 10g is a non-destructive upgrade. This is because the two systems do not share a common EUL prefixing system. Discoverer 4i uses EUL4x while Discoverer 10g uses EUL5x. Therefore, if you upgrade from 4i to 10g using the same schema you will still be able to use your 4i installation after the upgrade. You may want to consider installing 10g into a separate schema, using Discoverer's export import mechanism to effect the EUL upgrade.
  
  
• Discoverer 3.x - completely desupported. The terminal release was 3.1.46 for Administrator and Desktop, and 3.3.62 for Plus and Viewer. When installed from CD, the release was 3.1.36. Patch 2229365, for Administrator and Desktop was released on January 15, 2002 and upgraded 3.1.36 to 3.1.46. Patch 2240408, for Plus and Viewer, upgraded Discoverer 3i to 3.3.62 and was released on February 25, 2002. A further patch, 2675005, released on 19th November, 2003, upgraded this to the final release of 3.3.62.08.
  
  You should migrate to 10.1.2.0.2 as soon as you can.
  
  Note: There is no direct migration path from 3.1 to 10.1.2. This migration must be done via either 4.1.48.06 or 4.1.48.08. However, because 4.1 will be desupported from October 31, 2006, you need to do this as soon as you can.

Notes: the definitions of ECS and ES can be found on MetaLink in note 187553.1

Have you applied any patches to Discoverer that are different to those listed above or have you applied any of the patches listed? Did they fix the problem or not? If you have any information, let me know and I will gladly post it here to help the community.

Do you want to work for Oracle?

If you know of anyone who is qualified and interested in working as a product manager for the business intelligence product management group, including Discoverer, at Oracle, (link) they would like to hear from you. This post can be filled in either India, UK or USA.

Discoverer 4i will be desupported at end of October

Despite many warnings on the Oracle websites, there has been a lot of feedback from consultants, partners, and customers suggesting that a large percentage of the Oracle user base don't know that Discoverer 4i will be desupported at the end of this coming October. If your organization is one of those still running 4i you need to upgrade to Discoverer 10.1.2 as soon as you can. This means immediately - if not sooner!

For more information please take a look at this posting on Steven Chan's blog and at this posting on my own blog. If you don't know who Steven Chan is he, is the Director, Applications Technology Integration Oracle E-Business Suite Development New E-Business Techstack, so he certainly knows what he is talking about when he suggests doing this upgrade now. Oh, by the way, just in case you were wondering, Discoverer 10.1.2 is fully certified for use against Oracle E-Business Suite so you really have no excuse to stay on 4i.

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

What motivates you?

I like to find out everything about Discoverer, Application Server, SQL and so on. Because of this, I like to have some quiet time to myself to figure things out, to do my what-if analysis, and to try out all those little ideas that popped into my mind since the last time I had some quiet time. This ability to have such leeway in my work really motivates me, but does it make me a geek? Probably. However, what motivates me might not be the same as what motivates you.

A very interesting article entitled Top 10 ways to motivate geeks (link) was posted today on The Retrospector blog. I think the author hits on most of the important ways, but not necessarily in the right order for me. Number 6, though, is an interesting concept because I for one do like to pick up meaningless items at conferences. I can't be the only one either because everyone I saw leaving the recent Collaborate and Kaleidoscope conferences, in Nashville and Washington respectively, had bags crammed with goodies.

So what motivates you? Feel free to take a look at the article and add a posting.

Incidentally, I uncovered another article entitled Top 10 ways to demotivate geeks (link) which also makes for interesting reading.

Technology Isn't Always The Answer

I'm an avid reader of the ITtoolbox Blogs and a posting that came in today (link) made for interesting reading. The main point of the article was to discuss some thought processes that are used to rate new ideas. The conclusions were as follows:

The "greater good" or "the good of the masses" - The process or problem has to be of a certain size and must be substantial. Develop your measurements according to the type and size of the business that you are serving. The criteria to be considered substantial will differ greatly depending on the environment in which you find yourself.

Solve core process issues and needs - Drill down into the surface process or problem. Don't just treat the symptoms - find the disease. How does the process or problem relate to the critical functions of the business?

Ask yourself this - If I could implement my change tomorrow what immediate and measurable impact would it make? If you can't list at lease 3 substantial impacts to critical functions you need to rethink. If you can list 3 you need to return to the "greater good" and critical function analysis - make sure it will serve the greater good and will make a medium to substantial impact on at least one critical function.

Don't be taken in by the latest and greatest - a year ago I was enamored with new BI applications - today I'm not happy with them. Always look forward.

While I don't necessarily agree with all of the comments made by the author, I particularly liked the last comment. Don't you find it true that there is a lot of hype these days about how great the next BI idea is and how it will revolutionize what we do? It is interesting to look back, as the author comments, one year ago and see just how few of the "hit" ideas from then actually made it through to today. Many are still being touted as the way to go. I'm sure you can name some of them.

Lost or grayed out icons in Discoverer Plus

I have been researching the issue over lost or grayed icons and it is definitely an issue with Java memory on the local PC. So far, my research indicates that you need to increase the Java heap memory. I am still researching but here is what I have so far:

You may want to alter the maximum memory heap value for your Java. To do this, use this workflow:

1. From the Start button, use Settings > Control Panel.
2. Double-click Java Plug-in .
3. On the Advanced tab, set the Java Runtime Parameters to something like:
   –Xmx256m
4. Click Apply .

This sets the maximum amount of memory to allocate to Java to be 256 Mb. The installed default is 64 Mb which in my opinion is way too low. You can alter the maximum (-Xmx) to other values if you have plenty of memory but 256m seems to work very well. Generally I would not set the maximum to be more than 25% of your system memory. Initial research indicates that as a by-product you may find Plus loads and executes worksheets a little quicker also.

You may also want to increase the cache allowed for your local Java. The default is 50 MB but because the Plus applet takes almost 75% of this to even load, I recommend increasing this to 500 MB.

To alter the cache to 500 Mb, use this workflow:

1. From the Start button, use Settings > Control Panel.
2. Double-click Java Plug-in.
3. On the Cache tab, set the Maximum to 500 MB
4. Click Apply .

You can also increase the cache this way:

1. From the Start button, use Settings > Control Panel.
2. Double-click Java Plug-in.
3. On the Advanced tab, add another Java Runtime Parameter like this:
   -Dcache.size=100m
4. Click Apply .

If you have been experiencing issues with icons graying out, please let me know how you get on and whether the above helps. If you have made some alternative settings or know of another workaround please let me know what settings you applied. If you also notice any improvement in performance please let me know too.