A funny thing happened the other day

About six weeks ago I purchased a new REV backup kit from Iomega. We had never had a hard drive crash but I thought - hmm - well it's better to be safe than sorry. We installed it and I distinctly remember it taking many hours to accomplish a complete backup of our application server. Great, great, great - and off to bed I went with a smile.

Well, would you believe it? One week went by and that same server suffered a hard drive failure. Wow - how lucky we have a backup my engineer told me. Not so.....

After purchasing a new hard drive and inserting it into the machine the Iomega software was unable to read their own backup device. Well no problem, my hardware engineer told me, let's give their technical support a call. You could have knocked me down with a feather when they questioned why we had used their software. We're not surprised you can't use the backup if you used our software - you should have used someone else's. Thinking my engineer had misheard I asked him to verify and sure enough the same comment was repeated.

So another call to the Iomega technical support followed, suitably escalated to someone who was a bit more responsible and didn't make flippant remarks about their software. This time we were told to send the tape to them and they would see what they could do. We readily agreed and bundled a new hard drive with it thinking that in a few days we would have our data.

Upon receiving our REV drive, here is what they said:

Our evaluation at this point has concluded that the problem appears to be that the REV disks are unstable with potential read errors that may have resulted in file structure damage and/or corruption which may adversely affect some data segments.

Upon stabilizing the set, we will have to manually extract the data to a native file system, rebuild the corrupted / invalid file system components and tables, adjust file pointers and mount the recovered volume(s) and finally determine if some or all of the data you require is intact.

Based upon this initial evaluation, we feel that a recovery may be possible.

Wonderful, we said and asked When might we expect to see the data?

Oh no, sir, this service is not free. If you want us to give you your data it will cost you $1,700 plus tax.

Of course we said - you're joking right? It's your machine, we just bought the thing, we use your software, it's under warranty, it reported no errors, stop kidding and send us the data.

Unfortunately, they were deadly serious.

Please understand that I'm not casting any aspersions on Iomage or their products. They have a great reputation for great products, I even have one of their external drives. I'm only stating what happened to me and how puzzled I am by what has happened. I honestly thought a warranty was a warranty. Well next time I will read the small print and put this one down to experience.

I thought about it later and reflected what my customers would say to me if I sold them a piece of software that didn't work and then said it will cost you more money to have me come in and fix it - oh and by the way the fee will be double what it cost you to buy it two weeks ago. Guess how many customers I would have!

In case you were wondering, we never did continue the discussions with Iomega. We purchased a new server, this time with RAID technology and triple mirrored drives. So this weekend, Christmas weekend, I get to go home and install OBI.

Happy Christmas everone.

When did those reports last run?

Here's an interesting piece of code that I developed recently that you may find to be of use. It is aimed at administrators and uses a combination of the statistics (EUL5_QPP_STATS) and the documents (EUL5_DOCUMENTS) EUL tables to determine whether a workbook that has been saved to the database has been run recently.

SQL Code:

SELECT DISTINCT
DOCS.DOC_CREATED_BY DOC_OWNER,
DOCS.DOC_NAME DOC_NAME,
(SELECT TRUNC(MAX(DM.QS_CREATED_DATE))
FROM EUL5_QPP_STATS DM
WHERE DM.QS_DOC_NAME = STATS.QS_DOC_NAME) LAST_RUN
FROM
EUL5_QPP_STATS STATS,
EUL5_DOCUMENTS DOCS
WHERE
DOCS.DOC_NAME NOT LIKE 'Workbook%' AND
STATS.QS_CREATED_DATE(+) > :CUTOFF AND
DOCS.DOC_CREATED_DATE < :CUTOFF AND
DOCS.DOC_NAME = STATS.QS_DOC_NAME(+)
HAVING :RUN_DATE >=
NVL((SELECT TRUNC(MAX(DM.QS_CREATED_DATE))
FROM EUL5_QPP_STATS DM
WHERE DM.QS_DOC_NAME = STATS.QS_DOC_NAME),'01-JAN-2000')
GROUP BY DOCS.DOC_CREATED_BY, DOCS.DOC_NAME, STATS.QS_DOC_NAME
ORDER BY LAST_RUN DESC, DOCS.DOC_CREATED_BY, DOCS.DOC_NAME;

Note: The DOCS.DOC_NAME NOT LIKE 'Workbook%' code omits workbooks that were saved using the generic name that Discoverer always users. You might want to find these later and discuss removing them with your users!

Code explanation
The above code has a number of facets about it that I think I ought to explain a little more in depth. These are:

• The reason for the DISTINCT is to make sure that if the workbook was run more than once during a day that it is not counted twice.

• The :CUTOFF parameter prompts for a date. Whatever date is entered here is used to find workbooks that have been saved to the database prior to that date and have either run since that date or never run at all

• The :RUN_DATE parameter prompts for a date also. It is used to find workbooks that have not been run after this date.

• The following code is interesting because it finds both workbooks that have been run and those which have never run, returning NULL if the workbook did not run at all in the period between the two parameters

HAVING :RUN_DATE >=
NVL((SELECT TRUNC(MAX(DM.QS_CREATED_DATE))
FROM EUL5_QPP_STATS DM
WHERE DM.QS_DOC_NAME = STATS.QS_DOC_NAME),'01-JAN-2000')

Sample Output
Here's some sample output, from TOAD, that I ran this evening against my own test database using 01-JUN-2006 for :CUTOFF and 01-SEP-2006 for :RUN_DATE.



Note: If you have never used TOAD or have always wanted to try it but never got around to it please let me know by email at michael@learndiscoverer.com. I have partnered with the manufacturer and have a trial license which you can use for evaluation purposes.

Creating indexed items

I saw this recent posting on the OTN forums:

Is it possible to create Discoverer LOV's to display a lookup description e.g. Department Name and return the primary key code namely, Department Id when using an LOV for parameters for example?

The answer is Yes but the solution needs a bit of explaining so I thought I would do so here so that it is available to everyone. In order to make this happen we need to have a table that has an item and a code, and make sure that there is a one to one relationship between the item and the code. It's better to do this against a dimension table that contains unique codes and items. Then, you also need to make sure that there are indexes on both items.

Example table:

Let's start with creating the new table, inserting some values into it, and creating a couple of indexes. Here's the code:

CREATE TABLE TST_TABLE AS (
SELECT 1 PERSON_UID,
Trunc(SYSDATE) BIRTH_DATE,
TO_CHAR(TRUNC(SYSDATE), 'DD-MON-YYYY') CHAR_DATE,
123456 TAX_ID,
'Smith, Michael' FULL_NAME
FROM dual);

INSERT INTO TST_TABLE VALUES (
2, TRUNC(SYSDATE),
TO_CHAR(TRUNC(SYSDATE),
'DD-MON-YYYY'), 234456,
'Jones, Edward');

INSERT INTO TST_TABLE VALUES (
3, TRUNC(SYSDATE),
TO_CHAR(TRUNC(SYSDATE),
'DD-MON-YYYY'),
434456, 'Evans, Bob');

COMMIT;

GRANT SELECT ON TST_TABLE TO PUBLIC;

CREATE INDEX TST_TABLE_UK1 ON TST_TABLE (PERSON_UID);

CREATE INDEX TST_TABLE_UK2 ON TST_TABLE (FULL_NAME);

Discoverer Administrator steps
Next, I went into Discoverer Administrator and imported the table as a new folder from the database. I then went to the Full Name and created a standard list of values on it. With that done, I right-clicked on the item and from the pop-up list selected Properties. Scrolling down to the bottom of the list is a property called Indexed Item. Clicking this brings up a small dialog box in where I picked the indexed item called PERSON_UID.

Note: Indexed Item only becomes accessible when there is a) a list of values on the item, b) that there is another item in the same underlying table that is also indexed, and c) that there is a one to one relationship between the two items.

When I clicked OK, Discoverer displayed the following message:

There is nothing else to do except to click OK and allow Discoverer to create the other list of values. With this done, I am finished with Discoverer Administrator.

Working with Discoverer Plus
I created a new worksheet that includes the original item, FULL NAME, that we worked on above. Next, I created a new condition for the item but opted to use a parameter for the operand. On the right-hand side of the New Parameter dialog box, as shown below, is a checkbox called Enable users to select either indexes or values. I checked this box, completed the parameter and condition.

Note: the Enable users to select either indexes or values will be grayed out if the administrator has not completed the Discoverer Administrator steps outlines earlier.

When I execute this worksheet, it pops up the following parameter. As you can see this parameter is different to those that are normally seen inside Discoverer because this one has an additional drop-down box.

Clicking on the flashlight for the list of values, Discoverer displayed the following list. As you can see, it has both the value and the index, with the index being displayed in brackets.

Note: the above list is displayed no matter whether the user selects Index or Value in the first drop-down. The difference is only seen when the user completes the selection.

The following illustration shows what is returned when I searched by index and selected (2) Jones, Edward.

The following illustration shows what is returned when I searched by value and selected the same person.

The following illustration shows the output of my worksheet when I complete the parameter selection. It makes no difference whether I select by index or by value because the worksheet still displays the value as originally requested.

I hope this helps show how indexed items works.

Note: Do not attempt any of this inside Discoverer Desktop because it will not work. However, if you create a worksheet that has a parameter that uses Index and Value that parameter will be available inside Discoverer Viewer.

VPD date issue between 10.1 and 10.2 databases

I have uncovered an issue today with VPD against date fields. The issue came up at a client site on a 10.2 database. When I got back to the hotel and tried this on my own database, which is 10.1, I got no issue and everything worked ok.

Therefore, I am asking if anyone is able to try out the following for me on different Oracle databases and platforms and let me know by email at michael@learndiscoverer.com whether it worked for you or not. I'd like to compile a list of database versions and platforms on which the issue occurs.

Step 1: create this table as a user

CREATE TABLE TST_DATE AS (
SELECT 1 PERSON_UID,
Trunc(SYSDATE) BIRTH_DATE,
To_Char(Trunc(SYSDATE), 'DD-MON-YYYY') CHAR_DATE,
123456 TAX_ID,
'Smith, Michael B.' FULL_NAME FROM dual);

Step 2: grant select rights on the table

GRANT SELECT ON TST_DATE TO PUBLIC;

Step 3: switch your login to SYS and run this script:

CREATE OR REPLACE FUNCTION F_CHECK_ITEM_TST(p_object_schema in varchar2, p_object_name varchar2)
RETURN VARCHAR2 IS
V_PREDICATE VARCHAR2(2000) := '1 = 2';
BEGIN
RETURN(V_PREDICATE);
END F_CHECK_ITEM_TST;

Step 4: Run this to grant access rights over the function

GRANT EXECUTE ON F_CHECK_ITEM_TST TO PUBLIC;

Step 5: Run this to enable the policy

BEGIN DBMS_RLS.ADD_POLICY(
OBJECT_SCHEMA => 'DRAKE',
OBJECT_NAME => 'TST_DATE',
POLICY_NAME => 'SecByTST',
FUNCTION_SCHEMA => 'SYS',
POLICY_FUNCTION => 'F_CHECK_ITEM_TST',
STATEMENT_TYPES => 'SELECT',
POLICY_TYPE => DBMS_RLS.DYNAMIC,
SEC_RELEVANT_COLS => 'BIRTH_DATE',
SEC_RELEVANT_COLS_OPT => DBMS_RLS.ALL_ROWS);
END;

Note: in the above policy change the OBJECT_SCHEMA name to be the name of the user who owns the table you created in Step 1

Step 6: Go into Discoverer Admin and import the table as a new folder.
Be sure to uncheck the "Date hierarchies" box and set "Default aggregate on data points" to detail. Grant access to any user.

Step 7: Using Desktop or Plus, log in as that user then try to query the folder with the date not included, there should be no problem.

Step 8: Now try to query with the date included
On my 10.2 database I get ORA-24334: no descriptor for this position. This happens when logged into Discoverer as the table owner, EUL owner or any other user. However, when logged into Discoverer as SYS which is exempt from all VPD policies, there is no error when the date is included.

We tried this in Discoverer Desktop, Plus and Viewer with the same results in all three. We can successfully query the table from SQL*Plus, SQL Developer and TOAD.

There also seems to be no issue at all with the 10.1 database so there seems to be an issue between Discoverer and the 10.2 database.

What do you think?

P.S. to cancel the policy use this script when logged in as SYS:

EXEC DBMS_RLS.drop_policy(
OBJECT_SCHEMA => 'DRAKE',
OBJECT_NAME => 'TST_DATE',
POLICY_NAME => 'SecByTST');

A Discussion about Averages

Overview
Many people think that the term “mean” means the same thing as “average.” It doesn't. The term mean is a mathematical term, whereas the term average is often used loosely as a description for a person or a data item. In mathematics, average is a number that typifies a set of numbers of which it is a function.

In other words, average can stand for mean, median, or mode. So what is the difference between these three terms?

• Median - is the middle value in a set of numbers; above and below which lie an equal number of other values.
  
• Mean - is a number that typifies a set of numbers, such as a geometric or arithmetic mean; it is what most people think of as being the average value of a set of numbers.
  
• Mode - is the value or item that occurs most frequently in a set of statistical data.

Look at the following two sets of data:

Example set 1: 15 16 17 17 17 18 19
Analyzing this first set of data, representing the ages of the seven students in a school who enjoy drama, I get:

• Mean Age = 17
• Median Age = 17
• Modal Age = 17

Example set 2: 15 17 17 17 21 29 52
Analyzing this second set of data, representing the ages of the seven night school students who enjoy drama, I get:

• Mean Age = 24
• Median Age = 17
• Modal Age = 17

As you can see, the median age and the modal age for both sets of students are the same, whereas the mean age is completely different. Which average you choose will determine whether you consider the average age for students who enjoy drama to be the same for both normal schools and night schools. If statistics (mathematics is my major by the way) taught me nothing else, they showed me that there is no such thing as an average.

So let's move on to Discoverer and let me show you how to create the calculations that will enable you to report the Median and the Mode. I won't discuss Mean here because this is what Discoverer and everyone else generally refers to as the Average. As you know, Discoverer has a built-in function (AVG) to handle this.

The base worksheet:
As usual, I will start with a base query drawn from my own tutorial database. As you can see below, this worksheet displays a count of the number of lines, the unit price and the total number ordered for each product against each product line. There is a group sort on the Product Line and a low to high sort on the Order Qty.

Creating the Median

The calculation that you will need for the Median is the PERCENTILE_CONT analytic function. The basic syntax of this function is:

PERCENTILE_CONT(n) WITHIN GROUP(ORDER BY expr1)
OVER(PARTITION BY expr2)

Looking at my base query above let's figure out some values for expr1 and expr2, then I will discuss the appropriate values for n.

First of all, the easy one - the PARTITION BY. Looking at the example worksheet above I can see that I have a group sort on the Product Line. Because I know that page items and group sorted items are normally the partitioned items I will use the Product Line for expr2.

Next, let's determine a value for expr1 - the ORDER BY. Once again, look at the example worksheet. I only have two sorts in my worksheet. The first is a group sort on the Product Line and the second a low to high sort on the Order Qty. However, my Product Line has already been handled because it is my partitioned item. Therefore, the most logical item for me to use in my ORDER BY clause is the Order Qty, which is what I did.

Note: the underlying aggregation for my Order Qty is SUM so it is very imported to select the Order Qty SUM and not Order Qty itself.

Finally, let's discuss n - the value provided to PERCENTILE_CONT. This is a value from 0 to 1 with the value indicating how far along a scale from 0% to 100% I want the value to be. Because the definition of Median is the midle value, it therefore makes sense to choose 0.5 or 50%.

Putting all of this together, here is my calculation:

PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY Order Qty SUM)
OVER(PARTITION BY Prod Line)

which when inserted into a Discoverer calculation makes my worksheet look like this:

Note: because there are an even number of values for MINI-WIDGET and MEGA-WIDGET, Discoverer will choose a value exactly half-way between the two middle items in the set. When there is an odd number of values, Discoverer will always choose the actual middle item, as you can see if you look at the Median for SUPER-WIDGET and WONDER-WIDGET.

Using 0 and 1 in PERCENTILE_CONT

If you use 0, for 0%, as the value for n in the PERCENTILE_CONT calculation this means you want Discoverer to display the first value, wheras if you use 1, for 100%, this means you want to see the last value.

Here is an example using 0:

PERCENTILE_CONT(0) WITHIN GROUP(ORDER BY Order Qty SUM)
OVER(PARTITION BY Prod Line)

Here is an example using 1:

PERCENTILE_CONT(1) WITHIN GROUP(ORDER BY Order Qty SUM)
OVER(PARTITION BY Prod Line)

Here is what this last calculation looks like inside Desktop:

Creating the Mode

The calculation that you will need for the Median is the FIRST_VALUE analytic function, couple with the RANK function. The basic syntax of this combined function is:

FIRST_VALUE(expr1) OVER(PARTITION BY expr2
ORDER BY RANK() OVER (ORDER BY COUNT(*) DESC))

Hopefully, you can see what is going on. Starting from the inner function, the RANK, all this does is count the number of rows, which in our database happens to be the same as the number of lines, and produce a ranking in descending order with the highest count being 1, the next highest being 2 and so on. Because this ranking is embedded within the main function there is no possibility of a user accidentally deleting this calculation.

This whole RANK function then becomes the ORDER BY clause of the main function. Thus, I only need to find values for the two expressions, expr1 and expr2.

First of all, the easy one - the PARTITION BY. Looking at the example worksheet above, once again I can see that I have a group sort on the Product Line. As mentioned earlier, because page items and group sorted items are the partitioned items I will use the Product Line for expr2.

Finally, let's discuss expr1, the value which will be used for the basis of FIRST_VALUE. This is the item that I want to base the Mode on. In my case, I have chosen to use the Unit Price.

Putting all of this together, here is my calculation:

FIRST_VALUE(Unit Price) OVER(PARTITION BY Product Line
ORDER BY RANK() OVER (ORDER BY COUNT(*) DESC))

which when inserted into a Discoverer calculation makes my worksheet look like this:

Note: as you can see the mode has been calculated for each group sorted item. Also, even though multiple products have the same unit price, this will still be taken into account. Look at the mode result for SUPER-WIDGET. Discoverer is telling me that the mode is $26.95. This is correct because Discoverer has added the number of lines for both AVR-500 and AVR-550 together, which accounst for more lines than any of the other products in that product line.

If you are interested, here is my calculation inside Desktop:

Extending the Mode

Take a look at the following calculation. You will see that I have altered it to remove the PARTITION BY clause.

FIRST_VALUE(Unit Price) OVER(
ORDER BY RANK() OVER (ORDER BY COUNT(*) DESC))

Becasue the PARTITION BY clause has been removed, the Mode now calculates on the whole set. Thus, as you can see below, I have now determined that $22.18 is the Unit Price that is most often used in my organization. This is obviously a price that my customers are willing to pay. Hey, maybe I ought to raise the prices on the lower priced items!

Differences between Desktop, Plus and Viewer

One of my good friends, Russ Proudman, has put together a list of the differences between Desktop, Plus and Viewer (link).

If you have any additions you would like to see or of you spot any errors please let me know and I will pass these on to Russ.

The low-down about drilling in hierarchies

If you have taken the time to create hierarchies inside the Discoverer Administrator tool, you may be interested in knowing what Discoverer will do when a user drills up and down one of your hierarchies. The answer may well surprise you.

Overview
When a user drills up, Discoverer leaves the original item in situ, adds the drilled-up item to the query to the right of the original item, and leaves the number of rows the same.

When a user drills down, Discoverer removes the original item, replaces it with the drilled-down item, and increases the number of rows to accommodate the new situation.

Because a picture tells a thousand words, allow me to explain the above scenarios using sme images from my own system. First of all, I must have a start point. I will then take this base worksheet and first of all drill up from the quarter to the year, followed by drilling down from the quarter to the month:

The base worksheet:

As you can see above, this worksheet displays my revenue by fiscal quarter for the fiscal years 2004 and 2005.

Note: for this demonstration, the fiscal year runs from October 1 to September 30.

Let's drill UP. When drilling up, the new item is added to the query rather than replacing the original, thus retaining the same number of rows. Interestingly the new (aka summary) drilled-to item is added to the right of the original item rather than to the left which is where it should be. This is all standard Discoverer functionality so please don't shoot the messenger. Personally, I think it would be better if Discoverer were to replace the original item with the drilled-up item and reduce the number of rows to summarize the data. Unfortunately, as you can see below, it does not:

The drilled-up worksheet:

Let's drill DOWN. When drilling down, the new item replaces the original item and Discoverer increases the number of rows to accommodate the new situation. In my opinion, this is also incorrect. I think it would have been better if Discoverer had retained the original item and added the drilled-down item to its right-hand side. This way I could easily add a group sort on the higher-level item with a sub-total on each break.

Here is Discoverer's method of drilling down:

The drilled-down worksheet:

And here is the way that Discoverer could look if the drilled-from item had not been replaced:

The ideal drilled-down worksheet:

Of course, I know you can always edit the worksheet and add back in the item that was removed, which is exactly what I did above.

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.

10g Discoverer Handbook - Page 923 (Do not upgrade Java)

On page 923 of the Oracle Discoverer 10g Handbook I state and show you how to disable the automatic update feature that comes with the Sun JRE. Here is a before and after screenshot showing what can happen if you mistakenly leave the update enabled.

In this before screenshot, with Sun 1.4.2_06 installed, you can see that the drop-down correctly displays the current value.
However, in the following screenshot, taken following the upgrade to JRE 1.5, you can clearly see that the current value is grayed out.

To disable the automatic upgrade of the Sun JRE, use this workflow:

1. From the Windows Start menu, select Settings > Control Panel.
2. Double-click the icon for the Java ™ Control Page. It is usually named either Java or Java Plug-in.
3. When the Java Control Panel opens, click the Update tab.
4. Uncheck the box labeled "Check for updates automatically".
5. Click OK to save and exit.

Note: if your system has already upgraded to Sun JRE 1.5 or above you will need to remove the old Java and re-install the one.

To remove the Sun JRE 1.5 and re-install the correct version, use this workflow:

1. From the Windows Start menu, select Settings > Control Panel.
2. Double-click Add or Remove Programs.
3. Locate and remove the Java runtime environment.
4. Depending upon which version your Application Server is using, download the Sun JRE 1.4.2_04 or 1.4.2_06 software from the Sun website. They can be found here:
   http://java.sun.com/products/archive/j2se/1.4.2_04/index.html
   http://java.sun.com/products/archive/j2se/1.4.2_06/index.html
5. On this page, look for and click on the Download J2RE link.
6. On the next page, you are required to read and accept the license agreement.
7. After checking the box labeled "Accept License Agreement", download the software labeled "Windows Offline Installation, Multi-Language". This file is about 15Mb in size.
   Either you or one of your IT staff should now connect to your machine as a local administrator and install the software. The Application Server will recognize that the software has already been installed and will not try to install it again, thus allowing Plus to run.

The file that is downloaded is an executable, and is called either: jr2e-1_4_2_04-windows-i586-p.exe or 1_4_2_06-windows-i586-p.exe

NULL format display when upgrading to 10.1.2

It seems that when you upgrade from a previous version of Discoverer to 10.1.2 that the existing default setting for null values has changed from blank to the word NULL. This is annoying because it means that the default display for null values in 10.1.2 is not the same as it was in previous releases. What this means is that old workbooks that contain null values will now display the word NULL instead of blank. One of my customers reported this to Oracle as a service request and received the following reply:

You will need to open each and every workbook, make the change and resave. There is no global setting to apply for previously created and saved workbooks unfortunately.Hope the above information was informative. If this answers your question kindly close the TAR else feel free to update the TAR.

My customer has 100s of workbooks and it will take some time to go through this. I pity the person who has 1000s of workbooks because they will have to do this for each and every one! Don't be fooled into thinking that what you set in pref.txt will help because it won't. The upgrade is done using Discoverer Administrator which does not even know of the existence of pref.txt so it will ignore whatever you have set and will use its own default which is NULL.

If anyone knows of a workaround please let me know. Otherwise, if you have encountered this issue yourself please share your experience.

Best Presentation - ODTUG 2005

This week I have been at the ODTUG (link)Kaleidoscope 2006 conference at the Wardman Park hotel in Washington DC, and what a nice hotel it is. The reason I am here was to take part in the business intelligence summit that was organized by Oracle. My paper, The art of using calculations in Discoverer had been accepted by the ODTUG conference as one of the papers that would be included within the BI Summit (list of papers in summit). I presented my paper on Monday afternoon, the first afternoon of the conference, to a very attentive and receptive audience.

You can imagine my surprise when I arrived last Sunday to find that last year's paper, A first look at Discoverer 10g Release 2, had been nominated as one of the top 10 papers for the 2005 conference. Actually, the authors of the top 10 papers read like a who's who of the cream of Oracle presenters: Mike Ault (link), Bradley Brown (2 papers) (link), Don Burleson (2 papers) (link), Tony Catalano, Paul Dorsey (2 papers) and Steve Feuerstein (link). Then there was me.

Tuesday lunchtime came and the excitement had been building because the previous day the committee had said that they would be announcing the winners of the two coveted speaker awards - Best Speaker and Best Presentation, during lunch. I am delighted to be able to tell you that yours truly won the award for the best presentation with my old friend Tony Catalano, from TUSC, winning the award for the best speaker. We were both shocked because seeing the power of the authors that were against us we were convinced that we were there only to make up the numbers. My award, which incidentally is a rather beautiful black plaque with gold lettering, reads as follows:

Oracle Development Tools User Group
Best Presentation Content and Topic
Michael Armstrong-Smith
Armstrong-Smith Consulting
A First View of Discoverer 10g Release 2
ODTUG 2005
New Orleans, Lousiana

On Wednesday I will be delivering an encore of the winning presentation, a copy of which can be found on the download page of our main website (link). Some photographs were taken and just as soon as I am able to get my hands on one or two I will post here. As you can tell, I am thrilled to bits. It still hasn't 100% sunk in.

I have to say though that I would not have won this award without the help of my dear wife Darlene. She not only contributed to the paper but was the co-presenter of the presentation with me in New Orleans. So even though there is room for only one name on the award, it really was a team effort and she deserves as much credit for this award as I do. Thanks sweetheart, without you I would not be where I am today.

Unable to save a workbook to the database

One of my clients recently had an issue with Discoverer 10.1.2 in that every time a user tried to save a workbook to the database they got the following error:

ORA-1483 INVALID LENGTH FOR DATE OR NUMBER

Upon further investigation it was noted that they were using 10.1.0.4 version of the database and that the database was installed onto a HP-UX(PA-RSIC) 64 bit server.

According to Oracle, there is a known bug, number 3668164, within the 10.1.0.4 database that will prevent Discoverer from being able to save to the database. SAVING A WORKBOOK USING DISCOVERER 10G GIVES - ORA-1483. I have to admit that I had not heard of this bug and so my interest was aroused. Upon further investigation it turns out that the operating system is a red herring and that this issue can arise on any operating system.

The solution is to patch the database to 10.1.0.5 or higher. Apparently there is a one-off patch for 3668164 but not if your database is running on a Windows server. In this case you will have to apply the full 10.1.0.5 database upgrade. My recommendation though is to upgrade to 10.1.0.5 anyway as many other bugs were fixed in this release.

Postscript: my client informed me that after they upgraded their database that the issue went away.

Note: bug 3668164 is not available for public viewing. This is most frustrating because Oracle have lots of cross references to it on MetaLink.