Tuesday, October 26, 2010

CP8 for 10.1.2.3 released

Just wanted to let you know that on October 5, 2010, Oracle has released CP8 for 10.1.2.3. You will find it on MetaLink as patch number 9694503. When compared to CP7, 10 bugs have been fixed.

So far this cumulative patch has been released for the following platforms:
  • IBM AIX on Power Systems (64-bit)
  • Microsoft Windows 32-bit
  • Linux x86 (works for both 32 bit and 64 bit)
  • Oracle Solaris on SPARC (64-bit)
  • Oracle Solaris on x86 (32-bit)
If you are upgrading to CP8 from any patch level prior to CP4 then JDBC patch patch p4398431_10105_GENERIC.zip for bug 4398431(release 10.1.0.5) needs to be installed before you apply CP5.

This patch needs to be applied to all Oracle Homes, i.e. Infrastructure home as well as all related midtier homes.

Bug 4398431 - HANG WHEN RETRIEVING A CONNECTION FROM THE IMPLICIT CONNECTION CACHE

The following posting has been updated:

Monday, July 26, 2010

CP7 for 10.1.2.3 released

Just wanted to let you know that on June 4, 2010, Oracle has released CP7 for 10.1.2.3. You will find it on MetaLink as patch number 9112482. When compared to CP6, 9 bugs have been fixed.

So far this cumulative patch has been released for the following platforms:
  • HP-UX PA-RISC (64-bit)
  • Microsoft Windows 32-bit
  • Linux x86 (works for both 32 bit and 64 bit)
  • Oracle Solaris on SPARC (64-bit)
If you are upgrading to CP6 from any patch level prior to CP4 then JDBC patch patch p4398431_10105_GENERIC.zip for bug 4398431(release 10.1.0.5) needs to be installed before you apply CP5.

This patch needs to be applied to all Oracle Homes, i.e. Infrastructure home as well as all related midtier homes.

Bug 4398431 - HANG WHEN RETRIEVING A CONNECTION FROM THE IMPLICIT CONNECTION CACHE

The following posting has been updated:

CP6 for 10.1.2.3 released

Just wanted to let you know that on November 18, 2009, Oracle has released CP6 for 10.1.2.3. You will find it on MetaLink as patch number 8746296:. When compared to CP5, 19 enhancements or bugs have been fixed.

So far this cumulative patch has been released for the following platforms:
  • HP-UX Itanium
  • HP-UX PA-RISC (64-bit)
  • IBM AIX on POWER Systems (64-bit)
  • Microsoft Windows 32-bit
  • Linux x86 (works for both 32 bit and 64 bit)
  • Sun Solaris SPARC (32-bit)
If you are upgrading to CP6 from any patch level prior to CP4 then JDBC patch patch p4398431_10105_GENERIC.zip for bug 4398431(release 10.1.0.5) needs to be installed before you apply CP5.

This patch needs to be applied to all Oracle Homes, i.e. Infrastructure home as well as all related midtier homes.

Bug 4398431 - HANG WHEN RETRIEVING A CONNECTION FROM THE IMPLICIT CONNECTION CACHE

The following posting has been updated:

Monday, June 21, 2010

Be inspired today

This truly inspirational clip makes me proud to hail from Liverpool. I hope you enjoy it and if you do please pass it on and make someones's day.

Be inspired and go on and inspire someone else.

Friday, June 18, 2010

Support for Discoverer

As you probably know, if you are using Discoverer 3 or Discoverer 4 Oracle no longer provides support for these. I've seen a lot of folks recently who are still on these older versions and who are now out of support.

I wanted to let you know that I am able to offer remote support using gotomeeting for troubleshooting issues. I can connect to your system and help diagnose problems. In most cases we can get issues resolved within an hour.

Obviously there is a fee for this service but if you are really stuck and need help who you going to turn to?

Sending me an email is probably the simplest method of getting started. Click here to send an email.

Friday, May 28, 2010

Corporate Dashboards

During a recent consulting engagement I was asked about dashboards and where one should begin when the boss comes in and says I want a dashboard. I decided what I needed to do was step back and look at the dashboard concept, then 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 not the numbers either.

What is really important is the meaning or significance (aka the KPI) that is applied to the 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 100 mph reading, is by itself 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 concepts 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 happen to be running out of fuel the dashboard will 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 fuel tank is reached.

The vehicle dashboard needs to provide enough pertinent information so that informed decisions can be made as to how the vehicle is functioning.

Business dashboards need to provide enough pertinent information to the manager or executive so that they can make informed decisions as to how the department or company is functioning. Just like with a vehicle, a corporate dashboard needs to provide all of the critical information that is needed to run the organization's daily operations.

Most corporate dashboards are a snapshot in time, typically midnight, that tell 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%. The common factor here is that a rule is being applied to the data to indicate that something needs to be brought to someone'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 the same dashboard. Since the user roles are different not everyone needs the same level and kind of information. The worker bees need to work, the managers need to manage, and the executives need to improve their golf handicap. Typically, higher executives 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 should have a goal to move routine work from the manager to the employee, thus 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.

Now that reminds me, golf anyone!

Friday, May 21, 2010

Traditional vs OLAP

I have been following a very interesting thread on LinkedIn in the group called Data Warehouse & Business Intelligence Architects. The thread is discussing the pros and cons of OLAP as compared to more traditional methods of modeling. Personally I love these discussions. Here's what I recently said:

For me, probably an oldie in terms of these discussions, I have been working with modeling and data warehouses coming up on 25 years. I find it very, very strange that for some reason the term OLAP gets pushed around as if it is the answer to everything. This is probably being unfair to the technique because it's actually been around in one form or another a lot longer than most people realise.

Long before the term was invented or, more to the point shall we say, the technique was discovered, documented and given a formal name, we have been able to model enormous data warehouses with enormous amounts of data. Databases with terabytes of data are not new.

If I'm following the thread correctly I see two schools of thought, one pushing OLAP as the bees' knees and one pushing relational modeling. As someone who entered this field not too many years after Dr. Edgar Codd was first touting his ideas to IBM I can tell you that if a relational model is done correct with the right partitions, indexes and joins I can design a data warehouse using traditional methods for far less money than most folks would have you believe it should cost.

I'm somewhat of a historian and I actually have in my possession a set of Dr. Codd's early drafts. It makes for fascinating reading. So to anyone who is not sown on the idea yet I would urge you to read one of the many good books on the subject. You can do no worse than start with one of Ralph Kimball's books but you might also want to look at Bill Inmon.

Personally, I don't adhere strictly to any of the father's of data warehousing. I have read them all and I mix and match as the situation arises replete with a little tangential leap from time to time, sometimes of faith but mostly based on experience. Oh yes, and occasionally I mix them all, you know, just for fun because, after all, this is a beautiful world and we are in a beautiful profession and we have beautiful problems to solve.

So, what do you think? Are you a purist, a traditionalist or a modernist, somewhere in between or an amalgum of all three?

Tuesday, May 18, 2010

New Discoverer Books

I thought I would let you know that McGraw-Hill may well be interested in doing 2 extra versions of our best selling Discoverer book. As you know, the current book is on version 10g and incorporates both end user and administration. We are going to separate these out into a brand new Oracle Discoverer 11g Administration Handbook and a smaller one for end users as a sort of tutorial for getting to know the tool. There is still demand for material on Discoverer and now, following the release of 11g, I believe would be a good time to bring our current book up to date.


The end user book will basically take our end user training, extend it and convert it into book format. The bulk of this material already exists so is almost written.

The main book that I will be working on the 11g Administration Handbook and I wanted to get your thoughts.

As a launch point I will be taking the original book and stripping out everything to do with end users leaving just the administration chapters. Then I am going to add brand new material. The topics I definitely want to include are:
  • Managing PL/SQL functions – nothing on this in original book
  • Java command line – again nothing on this in the original book
  • Interfacing Discoverer with BI Publisher
  • Application Server Management using Weblogic – one, maybe two chapters on this
  • Interfacing with Oracle E-Business Suite
I’m also thinking about adding a chapter on what’s next for Discoverer with a discussion about upgrading to OBI EE and perhaps even covering the Discoverer to OBI EE migration mechanism in some detail.

I'd like to get your input. From the administrators point of view, what would you like to see covered in such a book? Do you have any thoughts as to new material that should be covered?

If so, please contact me via email

Sunday, May 02, 2010

Flooding in Tennessee

As many of you will be aware there has been unprecedented and extensive flooding throughout Western and Central Tennessee this weekend. My home town is Cookeville which lies about 75 miles to the east of Nashville which as you know is one of the worst hit areas with well over 14 inches of rain in the last 48 hours. To everyone who has asked after me and my family I just want to say thank you and to let you know that we are safe. Even though there is water all around the area with trees down and rivers over their banks our property, because it is at a higher elevation than most, is safe.

Unfortunately, the same cannot be said for the rest of the state. Not so very far away there are lots of houses under water and I know that my home state is being devastated even as I write. For anyone who has ever been here you will know that this is one of the most beautiful parts of the United States which makes it even harder to take. While Tennessee may not be the richest state in the union the people here are hard working, God loving, gentle folk who didn't need this.

If you have the opportunity to donate anything to a relief effort, should one be organized, please do so. At the very least, please keep the people in this area in your thoughts and prayers as you go to sleep tonight.

Thursday, April 29, 2010

NVARCHAR2 versus VARCHAR2

I've been coming across more and more databases using NVARCHAR2 instead of the more usual VARCHAR2 data type and found some issues.

First, I had issues inserting data from an NVARCHAR2 column into a table where the corresponding column in the other table has a data type of VARCHAR2.

Second, I had an issue joining tables together where the same column was defined with a different data type, one being NVARCHAR2 and one being VARCHAR2.

Here is the way I solved these issues:

Inserting NVARCHAR2 into VARCHAR2
If you try inserting data from an NVARCHAR2 column into a table where the corresponding column in the receiving table is defined as VARCHAR2 you will get a character set mismatch error. You will need to use the TRANSLATE USING command, like this:
  • TRANSLATE(nvarchar2 USING TABLE1.NVARCHAR2_COLUMN) INTO VARCHAR2_COLUMN
Joining NVARCHAR2 to VARCHAR2
If you try creating a join using

WHERE TABLE1.NVARCHAR2_COLUMN = TABLE2.VARCHAR2_COLUMN

you will get an error. You will need to do this:

WHERE TO_CHAR(TABLE1.NVARCHAR2_COLUMN) = TABLE2.VARCHAR_COLUMN

Thursday, March 25, 2010

Group Sort will not sort

Problem Description
Have you ever encountered a tabular worksheet that when you tell it to Group Sort an item that it doesn't display as a Group Sort even though in the Sort list Discoverer says it is a Group Sort? If so, I have the reason and a solution.

Background
Let's say you have a worksheet that has the following 3 fields:
  • Department Name
  • Supervisor Name
  • Employee Name
And let's say we have two optional parameters:
  • Department Name
  • Supervisor Name
This worksheet simply lists who the employees are within a department along with the associated supervisor. The parameters will let you pick departments or supervisors or both at the same time or none due to the optionality of the parameter.

Now, clone this worksheet to another one to add the projects that an employee is working on. You will now get many instances of the employee. The fields in this worksheet are:
  • Department Name
  • Supervisor Name
  • Employee Name
  • Project Name
In this worksheet, the parameters are, again optional,:
  • Department Name
  • Supervisor Name
  • Employee Name
Now, if you return to the first worksheet and create a Drill Link on the Department Name to the second worksheet you will be asked to provide a value for the 3 parameters in the second worksheet. So far so good.

Next, try and add a Group Sort to the Department Name in the first worksheet. It will not display as a Group Sort?

Why do you think this is?
The answer lies in the fact that you will be passing values from below the Group Sort to parameters in a linked drill.

If the Department Name was Group Sorted you would not be able to click in any row other than the first instance of the department. Discoverer understands that this cannot be right and so does not display Group Sorted items as a Group Sort when lower level items are needed in a drill.

Solution
The solution is to either remove the Group Sort or place the drill to link on one of the lower non-Group Sorted items.

Thursday, March 18, 2010

Corporate E-Learning

We at Armstrong-Smith Consulting are pleased to be able to offer corporate E-Learning for Discoverer.
When you purchase our corporate E-Learning package for Discoverer.

Here is what you get:  Click here for more information
  • You will get a site license to use both our Discoverer Administration and Discoverer End User classes for 2 years
  • You will get two installation CDs, one containing the 13 Administration and a second containing the 17 End User interactive video modules
  • You will get a PDF copy of both of the full Armstrong-Smith Consulting manuals
  • You will get a copy of the Armstrong-Smith Consulting database schema along with full instructions for installing onto your database
  • You will get the full Armstrong-Smith EUL complete with test workbooks
  • You will be authorised to install the training modules onto any number of files servers within your corporation
  • You will be licensed to use the material in either course any number of times over the 2 year period
  • Your materials will be sent free of shipping and handling to any location in the world
Are there any Caveats or Restrictions?
The only caveats and restriction that we have are:

  • You must own the relevant Discoverer licenses from Oracle
  • You may not use this material to give training to anyone outside of your company without express permission from ASC
  • You may not use this material in a country to which the United States has prohibited export
Click here for more information

Thursday, January 28, 2010

Discoverer E-Learning Offer Extended Until Further Notice

Due to the great response we had from folks purchasing our Discoverer E-Learning software for Administrators and End Users we are delighted to let you know that we have extended the offer until further notice. Interested in buying? Click here.

How much does it cost?
We will let you have our E-Learning courses for the following amazing prices:
  • Discoverer Administration course only $99.00
  • Discoverer End User course only $199.00
  • Buy both course for only $249.00

This is the offer of a lifetime and these prices represent a saving of 1000s of dollars over our standard price list.

We know that these courses will be of particular interest to people outside of the USA, or for people on a limited budget who cannot get to one of our fantastic instructor-led sessions in beautiful Tennessee, or for someone like you who would like to get training but your company has cut back on your training budget. Whatever the reason, you cannot afford not to take part in this offer to get a personal copy of one or both of our world-class training.


What will you get?

When you buy our E-Learning training through this offer, you get everything you need to complete your training. Here is what you get:
  • You get the 13 Administration or 17 End User interactive video modules
  • You get a PDF copy of the full Armstrong-Smith Consulting manual
  • You get a copy of the Armstrong-Smith Consulting database schema along with full instructions for installing onto your database
  • You get the full Armstrong-Smith EUL complete with test workbooks
How do you buy?
To purchase this training please click here. You will be able to pay either by credit card or via PayPal.

Useful Patch Numbers posting updated

I have today updated my Useful Patch Numbers posting with full details for 10.1.2.3 CP6

Click here for more information

Friday, January 01, 2010

There's Nothing like NULLs for Confusion

Happy New Year everyone. Here is a cautionary note about using NULLs and the 'NULL' string with Discoverer. If you want to select rows containing NULLs you need to use the condition IS NULL. However, this causes a problem if you want to select null values using a parameter because the condition column=:parameter would not return any rows if the parameter was NULL.

Before 10gR2, Discoverer did not handle selecting null values from a parameter so you needed to convert the NULL values into a NULL string and include the the NULL string in the list of values. So for example, if you had a column that contained the values 'YES', 'NO' and NULL you would create a list of values that returned the strings 'YES', 'NO' and 'NULL' then use a condition NVL(column, 'NULL') = :parameter. So when you wanted to select rows where the column contained 'YES' or was NULL, you selected 'YES' and 'NULL' from the list of values and Discoverer generated the condition NVL(column, 'NULL') IN ('YES', 'NULL') returning the correct rows (assuming your column did not actually contain any 'NULL' strings).

So far so good, but this changed with Discoverer Plus 10gR2. In this release Discoverer tried to deal with the NULL value in the list of values. So if you had 'NULL' in the list of values it would create an IS NULL condition. So in the above example you could use the conditioncolumn = :parameter and if you selected 'YES' and 'NULL' from the list of values Discoverer would generate the condition column IN ('YES') OR column IS NULL.

Great, but this release would change the 'NULL' string to NULL anywhere it was entered into a condition. So for example, if you entered the condition column = 'NULL' it would change it to column IS NULL. Also if you had the condition NVL(column, 'NULL') = 'NULL' it would change it to NVL(column,NULL) IS NULL. This would give you unexpected results and to add to the confusion, Discoverer Desktop continues to work as before so the results would be different from the Desktop edition.

However, the confusion is compounded in Discoverer Plus 10gR2 CP6. In this consolidated patchset the functionality changes yet again. Discoverer Plus nolonger converts the condition column='NULL' so Plus now returns the correct results. However it stores the condition internally as column = '''NULL''' and when you open and run the workbook in Discoverer Desktop the condition column = '''NULL''' is used so again you get different results. There are more differences when you create the conditions in Desktop and open the same workbook in Plus.

One would hope that with a product with the longevity of Discoverer the basic functionality of the product would be stable, but NULLs are nothing and nothing changes like 'NULL's.

Followers