Friday, April 28, 2006

Memories of Collaborate - Part 1

Well Collaborate '06 has come and gone and I thought I would jot down some of the memories of the event as seen through my eyes. For me the event started on Sunday with an all-day Discoverer workshop. As I live in Cookeville, TN, which is only 80 miles from Nashville, there was no need for me to fly and I was able to avoid the crowds of Collaborators at the airport.

Saturday April 22, 2006
Because Sunday was scheduled to start at 8:30am I decided to drive down the night before. So following dinner, Darlene, Byron, our son, and me set off in two vehicles with all our equipment for the one and a half hour drive to Nashville. After an uneventful journey we went straight to Kinkos to collect our workshop manuals. As has become the norm with Kinkos these days, not all of the material had been printed and they asked us to come back in the morning for the rest. How can they consistently get our printing requests wrong beats me. Fortunately our hotel for the night, the Hampton Inn near to Opryland, was only a couple of miles away and we were able to be in our beds by about 10pm.

Sunday April 23, 2006
After what seemed like only half an hour, we received our 5:30am wake up calls. With less than three hours to go to the workshop there was no time for any dilly dallying. A quick breakfast, and it was quick because sausage in gravy is not my idea of food, was followed by the second trip to Kinkos and then the short drive to the Opryland hotel. If you have never been here, this is a must see hotel, one of the very best in the world in my opinion. Unlike some of the other attendees who were taken aback by the sheer size and beauty of the place, because I live in Tennessee this is a place that I now take for granted as we frequent it often. I also knew the nearest parking place to the conference center so at about 7:15am we were able to start unloading our equipment. This is where the day started to go pearshaped.

First of all, there are stairs going both up and down from the conference center entrance. Our allocated room for the day was on the upper floor, but guess where the elevator was? It was on the ground floor, down a flight of some 20 stairs. Was there an elevator down from the entrance to the ground floor? No, of course not. That would have been too much too ask. So having found one of the hotel trolleys we off loaded all of our equipment and wheeled it into the building. After off loading again to climb the stairs we then man-handled the trolley up the stairs before reloading it for the trip to our room. Anyway, we made it and by 8:30am we were all set up.

The morning of the workshop went well. Our attendees were able to connect to one of our two servers and we had a quite enjoyable couple of hours. Then, for some inexplicable reason, from about 11am the hotel decided to undertake maintenance on their really powerful wireless network. I had thought ahead and purchased a brand new Linksys wireless router but this was no match for whatever the hotel were using because every 20 minutes or so they would zap the airwaves and knock my little network into the next century. Of course I did not know this at the time as it was only the next morning that the hotel apologized. They apparantly had not understood that this was a hi-tech conference! Maybe Oracle isn't hi-tech after all. Maybe its only us who work with it who think it is. Makes you think though, doesn't it?

Despite the interruptions to the wireless network, we were able to complete the workshop and get through all of the material by hard wiring my laptop to the server. At about 4:30pm, Mark Rittman joined me for a short question and answer session. After this it was pack up and go home time for Darlene and Byron, while I went to my hotel for the night. No, I was not staying at Opryland. I know how much it costs. Instead I had a rather lovely two bedroomed suite in a timeshare complex about half a mile away. I'm not stupid.

That night, unaware of what the hotel had been doing with the wireless network, I got on the telephone to Linksys and started giving them stick about my new router. They tested it out and told me there was nothing wrong with it. Hmm, I thought, so what was wrong then? Sleep could not come quick enough.

Monday April 24, 2006
The next morning I had a lie in and then casually wandered over to the conference to check in. After collecting my materials I headed off to the speaker room to let them know about the previous day's fiasco. On the way I bumped into Faun de Henry and her husband outside the BI/DW SIG meeting. Faun and I go way back to the early days of the SIG when I was secretary. I had to give that up when the company that I was working for at the time decided that I could no longer go to conferences. I think its time for me to get back into the SIG. Also outside the SIG was a lady called Marcia Livingstone, someone that I had emailed with with last year when her company was a prospective client. Giving my apologies to everyone I was in a rush to get to the speaker room to find out if anyone had complained about the previous day.

When I arrived at the speaker room, before I could say anything I met Samantha Falkiss from the OAUG organizing committee. She told me she had heard what happened as the hotel had been doing maintenance and I wasn't the only person affected. So now the cat was out of the bag and I could call home and let Darlene know that it was nothing to do with anything that we had done. This news made us all feel a lot better I can tell you.

After that good news, and after checking in and picking up my speaker's badge I set off back towards the BI/DW SIG where I sat in for the last session. There it was that I met up with Mark Rittman for the second time (link). After a few pleasantries and the now obligatory "I know why my workshop network did not work" exchanges with some of the folks who had been in my workshop, I headed off to make my introductions to the conference bookstore.

As I was saying hello to Carolyn Thompson from the bookstore (link), who should be standing there but Lisa McClain, our publisher from McGraw-Hill. What a pleasant surprise that was and we had a really pleasant conversation. Then reminded me that I was due to be the guest speaker on the Oracle Press booth that evening during the opening session of the conference exhibitor hall. To be honest, I thought this was on Tuesday evening so it was fortunate that she reminded me. So at just after 6:00pm I arrived in the exhibit hall and made my way to the Oracle Press stand, conveniently situated in the middle of the hall alongside the main Oracle demonstration area. Phew, the next hour flew by as dozens of people stopped by to get hold of the free Oracle Press t-shirts that we were giving away. By 7:30pm I was tired and went back to my hotel.

Tuesday April 25, 2006
Today was my main presentation day as I was giving a live, two-hour demonstration about how to build a Discoverer End User Layer. I knew this would go well because no network was required! Anyway, like a good Boy Scout, I like to always be prepared and so I went to the speaker room to make sure my presentation was right and that all my accounts were set up on my database. While I was there, who should walk in but Don Burleson, his dear wife Janet, accompanied by John Garmany. If you have not seen it, Don and John and the co-authors of the Oracle Press book "Oracle Application Server 10g Administration Handbook".

As lunch was rapidly approaching, Don invited me to join him. What followed was a rather pleasant hour and a half under the vast dome of Opryland reminiscing about old times. You may not be aware of this but Janet runs an organization (link) that uses miniature horses instead of dogs to help visually impaired people. I had the good fortune to visit to their place in Kittrell, North Carolina, last year to give some Discoverer training. While I was there, I fell in love with the "minnies" as they are affectionaltely known and bought three. They now lead a happy life on my ranch in Tennessee. I had hoped to meet up again with Mike Ault, another of Don Burleson's army of experts, but alas it was not to be. Mike was giving a presentation and did not make it for lunch.

The early part of the afternoon was spent in the exhibitor hall, once again meeting up with lots of old faces and acquaintances. Right by the entrance was the big TUSC (The Ultimate Software Consultants) stand so I thought I would drop in and see who was there. Presenting at the time was what looked like a familar face with a new look, Tony Catalano. I say new look because Tony is now sporting a rather interesting looking goatee! Tony was project lead on a project that I did for TUSC in Kansas City a while back. Also there was Joe Trezzo and Matthew Kundrat. Joe is one of the owners of TUSC and he was responsible for setting up my first project. Matthew leads TUSC's training operation and he smilingly took me to one side to let me know that they had just lost a training deal to us. He was very generous and said that he did not mind missing out on a client to us. I told him the same thing applied the other way and that if a prospect of mine said that they were going to TUSC I would be ok.

My wanderings in the exhibit hall continued and I met up with my friends David Fuston, Maggie Tompkins and Kathleen McCasland on the ODTUG stand. I was reminded that I still have to submit my paper for the ODTUG Kaleidoscope (link) conference in June. I will do it, I promise! Actually, this looks like being a great conference. My paper, which is part of the Business Intelligence Summit, will be on making the most of calculations inside Discoverer.

Following this, I went and sat in on Dan Hotka's tips and tricks presentation on Discoverer. I'm so glad that a second Discoverer session made it to the conference and, as luck would have it, Dan's presentation was in the same room as mine and immediately prior. Good planning, good luck or whatever you call it, I got to relax for an hour.

Wednesday, April 19, 2006

Last accessed date for a workbook

I was recently asked if I knew of a way to get the last accessed date for a workbook. The company were looking to find out which workbooks had not been used recently. So I got to thinking and then it hit me. "So long as the company have not turned off Discoverer's statistical gathering mechanism, I can get this information by querying Discoverer's statistics table".

Here then is the SQL code that you will need. To run it you need to be logged into SQL*Plus as the owner of the EUL:

SELECT
QS.QS_DOC_OWNER USERNAME,
QS.QS_DOC_NAME WORKBOOK,
QS.QS_DOC_DETAILS WORKSHEET,
MAX(QS.QS_CREATED_DATE) LAST_USED
FROM
EUL5_QPP_STATS QS
GROUP BY
QS.QS_DOC_OWNER,
QS.QS_DOC_NAME,
QS.QS_DOC_DETAILS

Monday, April 17, 2006

April 2006 Oracle Discoverer Patch Levels

Oracle recently published a document on MetaLink that "explains the minimum and recommended Discoverer patch level Administrators and DBAs should apply for optimum stability, greatest feature availability and most efficient maintenance".

I will summarize the major releases here, along with their patch numbers.
  • Discoverer 3.x - completely desupported. The terminal release was 3.1.36 for Administrator and Desktop and 3.3.62 for Plus and Viewer. You should migrate to 10.1.2.0.2 as soon as you can. Note that there is no direct migration path from 3.1 to 10.1.2.0.2 as you must migrate via 4.1.48.08

  • 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. The terminal release was 4.1.48.08. You should migrate to 10.1.2.0.2 as soon as you can.

  • 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.

  • Discoverer 9i (9.0.2) - desupported from July 1, 2005 with Extended Support being available until July 1, 2008. The terminal release was 9.0.2.54.10. You are urged to upgrade to 10.1.2.0.2 as soon as you can.

  • 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.

  • 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 (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 a brilliant piece of software. The current release is 10.1.2.48.18. No ECS or ES dates have been announced for 10.1.2.0.2.

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

Another BI blog of interest

You may be interested to know that Steven Chan has started a blog (link) dedicated to Oracle's E-Business Suite. In Stephen's own words he describes his site as follows: "An insider's take on technology stack-related topics for the Oracle E-Business Suite, with the occasional product announcement, glimpses into the inner workings of Oracle Development, editorials on business philosophy, and musings on IT industry news."

Of significant importance to the Discoverer world is the fact that Steven, who is the Director for Applications Technology Integration Oracle E-Business Suite Development, will be posting insider snippets from Oracle as they relate to both Discoverer and E-Business Suite.

One notable posting today (link) details the fact that Oracle plans to desupport Discoverer 4i by the end of October this year. This will have a significant impact on all of the Oracle Applications (E-Business Suite by another name) organizations that have been resistant to upgrade Discoverer to 10g. Now is the time to plan your upgrade. There is no reason for staying behind the eight ball any longer. Discoverer 10g R2 (10.1.2) is fully certified for Oracle Applications and, to quote from Steven's posting:

"The good news is that there really isn't any reason why you should hesitate to upgrade. Discoverer 10g is faster, more efficient, and has more analytic features for your end-users.

For example, Discoverer 4i's automatically-generated SQL statements tended to be very long, and would result in extremely heavy database queries, which in turn would return massive datasets, weighing down your application server. You probably already know this from reviewing your server logs, and from the number of helpdesk calls from annoyed users whose workbooks are taking hours to run.

The Discoverer 10g release has an automatic SQL trimming feature that intelligently removes unneeded columns. Depending on the workbook, this can result in significant improvements in the responsiveness of workbook queries, and lighten up your Discoverer and database server load, as well."

So there you have it, straight from the horse's mouth, so to speak. I urge you to not ignore this desupport notice and begin your upgrade plans. One of the key phrases that I like to use in training is "What starts right, stays right". If you wait until the last minute to plan your upgrade you will rush it and possibly get it wrong. Plan now while you still have five months of support.

Wednesday, April 12, 2006

Pref.txt - color combinations

Have you ever looked inside pref.txt at the settings for the Data, Heading and Totals colors? Have you wondered what color combinations you should use? If you are like me and you said yes, this posting is for you.

Here are the defaults that you will see inside pref.txt:

Data Format: foreground="0,0,0" background="255,255,255"
Heading Format: foreground="0,0,0" background="247,247,231"
Totals Format: foreground="0,0,0" background="247,247,231"

But what do these mean? Well, the three sets of numbers are the R-G-B (Red, Green, Blue) combinations. In the following illustrations you will find the most common used background color combinations. Most of these are displayed with a foreground color of black (0,0,0) but a few use white (255,255,255) as the foreground to show the contrast.

By the way, if you have also wondered about the colors that Discoverer uses to highlight areas of the screen, to work out the highlight color you subtract the R-G-B number from 255 and use that. So, for example, let's say your data color is Aquamarine, which is 127,255,212, the highlight color will be 128,0,43.

You will find pref.txt located at $ORACLE_HOME\Discoverer\util









Tuesday, April 11, 2006

Discoverer 10g Handbook - page 886

Here is the SQL script from page 886. It tells you the tablespaces that have less than 10% free space. It is a great troubleshooting script. Try running it once per day and emailing the results to yourself. I think you will find this is good way to keep an eye on your tablespace, especially when you don't have a lot of time free. It runs on Oracle database 8, 8i, 9i and 10g.

SELECT
TSP.TABLESPACE_NAME T_NAME,
TSP.TOTAL_SPACE TOT_SPACE,
FREE.TOTAL_FREE,
ROUND(FREE.TOTAL_FREE /TSP.TOTAL_SPACE*100) PCT_FREE,
ROUND((TSP.TOTAL_SPACE - FREE.TOTAL_FREE),2) TOT_USED,
ROUND((TSP.TOTAL_SPACE - FREE.TOTAL_FREE)/TSP.TOTAL_SPACE*100) PCT_USED,
NEXTEXT.MAX_NEXT_EXTENT
FROM
(SELECT
TABLESPACE_NAME,
SUM(BYTES)/1024/1024 TOTAL_SPACE
FROM
DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) TSP,
(SELECT
TABLESPACE_NAME,
ROUND(SUM(BYTES)/1024/1024,2) TOTAL_FREE,
ROUND(MAX(BYTES)/1024/1024,2) MAX_FREE
FROM
DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) FREE,
(SELECT
TABLESPACE_NAME,
ROUND(MAX(NEXT_EXTENT)/1024/1024,2) MAX_NEXT_EXTENT
FROM DBA_SEGMENTS
GROUP BY TABLESPACE_NAME) NEXTEXT
WHERE
TSP.TABLESPACE_NAME = FREE.TABLESPACE_NAME (+)
AND TSP.TABLESPACE_NAME = NEXTEXT.TABLESPACE_NAME (+)
AND ((ROUND(FREE.TOTAL_FREE/TSP.TOTAL_SPACE*100)) <> FREE.MAX_FREE);

Friday, April 07, 2006

Discoverer 10g book is #8 on Amazon

I have been keeping an eye on the progress of the new Discoverer 10g Handbook and wondering how long it would take for it to break into the top 10 of all Oracle books being sold on Amazon. Well you can imagine my delight when I woke up this morning to find it at number 8.

When you consider that the book has only been out for 2 weeks, for it to already be in the top 10 of all Oracle books being sold on Amazon this means that you have been buying it. If you are one of the people that have purchased a copy, from the bottom of my heart I say thank you. If you have not bought one yet, why not? :-)

You can buy your copy here: link

Thursday, April 06, 2006

Book signing at Collaborate

I have been in negotiations with Training Systems Inc, (link to their website) the company that is providing the bookstore at the OAUG Collaborate '06 (link) conference in Nashville, for a book signing session for the Discoverer 10g Handbook. I am pleased to be able to let you know that we have concluded our discussions and this has been set for Wednesday April 26th between the hours of 1:00 pm and 1:30 pm.

I have also been invited to be a guest author at the McGraw-Hill (a.k.a. Oracle Press) booth between the hours of 6:00 pm and 7:00 pm during the Happy Hour in the Exhibit Hall on Tuesday April 25th. I will be available to sign copies of the book, answer any questions on Discoverer that you may have, or just to chat.

So, if you at are the conference and find yourself anywhere near the McGraw-Hill booth (part of the Oracle booth) between 6pm and 7pm on Tuesday, or near the Bookstore between 1 pm and 1:30 pm on Wednesday come and say hello. Don't leave me sitting there like a lemon! By the way, I like diet Pepsi and diet Coke so if you see me looking parched, be kind.

Wednesday, April 05, 2006

Sample chapter download from the Discoverer 10g Handbook

OTN has very kindly published a sample chapter (Chapter 1) from our latest book, "Oracle Discoverer 10g Handbook". If you haven't bought the book yet - why not? It is the definitive Oracle Press book on Discoverer and is published by McGraw-Hill Osborne Media.

Please follow this link to the sample chapter - it is a PDF file, 19 pages long, 285KB in size.

I am indebted to Abhinav Agarwal (link to Abhinav's blog) for bringing this to my attention.

Here's a sample.

Sunday, April 02, 2006

April Fool

Being English I guess I am more used to the practice of the April's Fool and am always on the lookout for new ones. Well, yesterday, as you know, was April 1 and I was hoping for a spate of hoaxes. Unfortunately, I was somewhat disappointed because there didn't appear to be any that were spectacularly funny. So I thought I would list for you three of the classic jokes that have been played over the last 50 years.

1. The Island of San Serriffe
In 1977 the British newspaper The Guardian published a special seven-page supplement in honor of the tenth anniversary of San Serriffe, a small republic located in the Indian Ocean consisting of several semi-colon-shaped islands. A series of articles affectionately described the geography and culture of this obscure nation. Its two main islands were named Upper Caisse and Lower Caisse. Its capital was Bodoni, and its leader was General Pica. The Guardian's phones rang all day as readers sought more information about the idyllic holiday spot. Few noticed that everything about the island was named after printer's terminology. The success of this hoax is widely credited with launching the enthusiasm for April Foolery that then gripped the British tabloids in the following decades.

2. The Swiss Spaghetti Harvest
In 1957 the respected BBC news show Panorama announced that thanks to a very mild winter and the virtual elimination of the dreaded spaghetti weevil, Swiss farmers were enjoying a bumper spaghetti crop. It accompanied this announcement with footage of Swiss peasants pulling strands of spaghetti down from trees. Huge numbers of viewers were taken in, and many called up wanting to know how they could grow their own spaghetti trees. To this question, the BBC diplomatically replied that they should "place a sprig of spaghetti in a tin of tomato sauce and hope for the best." Check out the actual broadcast archived on the BBC's website (You need the RealVideo player installed to see it, and it usually loads very slowly).

3. Hotheaded Naked Ice Borers
In its April 1995 issue Discover Magazine announced that the highly respected wildlife biologist Dr. Aprile Pazzo had discovered a new species in Antarctica: the hotheaded naked ice borer. These fascinating creatures had bony plates on their heads that, fed by numerous blood vessels, could become burning hot, allowing the animals to bore through ice at high speeds. They used this ability to hunt penguins, melting the ice beneath the penguins and causing them to sink downwards into the resulting slush where the hotheads consumed them. After much research, Dr. Pazzo theorized that the hotheads might have been responsible for the mysterious disappearance of noted Antarctic explorer Philippe Poisson in 1837. "To the ice borers, he would have looked like a penguin," the article quoted her as saying. Discover received more mail in response to this article than they had received for any other article in their history.

Do you remember these or have a favorite of your own? Let me know. By the way, for a full report of these and dozens more please take a look at the Aprils Fools Day Hoaxes of All Time page on the museum of hoaxes web page, from where I got these extracts.

Metalink Username Change

It has come to my notice that in May 2006, all MetaLink users will need to re-register their account and use a valid email address as their "new username", replacing their current MetaLink user id. MetaLink users will be presented with a new registration screen that will display the email address currently registered with MetaLink. Users will be asked if they want to use this email address as their new username or use another email address. Once confirmed, a new password will be mailed to that email address.

If you have multiple MetaLink accounts or multiple email addresses there will a couple of additional screens.

Note: this username change will only affect MetaLink and will not impact any other Oracle site or login that you may have.

In addition, and this is a big change, users who do not have an active CSI number in their profile will find that their MetaLink access is limited to only viewing their profile. Oracle are doing this to ensure that only those people who genuinely have a valid support identifier gain access to all of the benefits of MetaLink. For further information on this subject, please log in to MetaLink and take a look at note 3661177.1, released on 15 March 2006.

To check whether you have a valid profile, use this workflow:

  1. Log in to MetaLink
  2. Click the Profile link ( top-right of screen)
  3. On the Profile menu, click Licenses
  4. Click the Show button alongside a profile
  5. Look at the Expiry Date

If you don't see an Expiry Date in the future for any of your profiles then you don't have a valid CSI number. You have about a month to get this rectified otherwise you will no longer have access to the forums, patch releases and other useful information on MetaLink. I urge you to check your profile and make sure you are valid.

Followers