Michael Armstrong-Smith

Subscribe to Michael Armstrong-Smith feed
Updated: 6 days 3 hours ago

Support for Discoverer

Fri, 2010-06-18 11:59
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.

Corporate Dashboards

Fri, 2010-05-28 12:43
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!

Traditional vs OLAP

Fri, 2010-05-21 23:04
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?

New Discoverer Books

Tue, 2010-05-18 20:08
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

Flooding in Tennessee

Sun, 2010-05-02 22:54
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.


Thu, 2010-04-29 15:02
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:
If you try creating a join using


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


Group Sort will not sort

Thu, 2010-03-25 01:20
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.

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.

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