Webinar Questions Answered

Purchase Webinars

Did you miss a seminar or want to a copy of one that you attended?

Michael's seminars are now available for purchase. Click here for more details.

January 2009

Question 1: 'Business Intelligence' was a new service on the leading edge for developing a company to provide these new services to other businesses during the 1993-1995 period. Since then not much happened until the last 2-3 years and the definitions seem to have changed or evolved to the point where the term is now a buzz-word for marketing (in some cases). I am curious to know, to hear you describe what business intelligence is?

Question 2:When our campus began using Oracle Discoverer, various consultants established an EUL for each functional area. This means we have challenges reaching across silos for information. Do you think having multiple EULs is advisable, or do you recommend a single EUL for the whole site?

Question 3: I am unable to set query governor restrictions higher than 2 minutes run time. I have Admin rights and have reset restrictions in Discoverer admin but they do not propagate into Discoverer.

Question 4: We have a problem that when users run reports that pop up a Running Time Estimating and this is much higher than it actually takes. We want to disable this message, how can we do this?

Question 5: I am sometimes advised by Oracle to stop and restart the Oracle server. I am new to Discoverer administration and don’t know how to do this. Can you explain how?

Question 6: Why do I get sort errors on Viewer when I sort a field where I have given the field an alias name? For instance the field name is Total Units Count and I rename it to Units. When I try to sort on it in Viewer, I get an error that says the field cannot be found. Is this an issue that is fixed via a patch?

Question 7: I am working on a report and need to do a couple of things:
1) I just need to add columns with hard coded information. For instance, one column will be our state agency code and then a specific client code. This agency code or client code does not come from a view.
2) I need to reformat the zip code. The zip code in the view is 73034-5207. I need to replace the dash with nothing leaving 730345207. In SQL, I just used the replace function replace(zip_code,'-','') Can I format the zip code this way in the output?

Question 8: How can we get a count of number of rows returned by a Discoverer workbook? One way is to enable Row Numbers and scroll until you reach the last record. However, is there an easy way to see the count?

February 2009

Question 1: Several times over the last year I have heard talk about Oracle dropping Discoverer. I would like to hear your take on this

Question 2: Would you please show me the way of creating a LOV with hard coded values? The LOV I need should display the same values all the time. So instead of creating a LOV that reads from a table (Query), I need a LOV in which values are already stored (Hard coded).

Question 3: The oracle BI solution (EBS)comes with pre-defined Business Views- database views and Business Areas and folders. If we want to customize those database views or BAs and folders what will be the best practice in order to avoid losing it during any upgrades?
The out-of box Order Management BA that we are using heavily needs some additional fields to be added to the Order Header and Order Lines folders and we also want to add some new folders to this BA.
If we do the changes to the database views behind this BA would they be lost during the upgrade or do we have to copy(duplicate) those views, updated them and create a custom BA and folders against those views?

Question 4: My users would like to have the SALUTATION table closer at hand to join to, as opposed to joining across business areas.  They will be using this join quite often. Currently the SALUTATION view is under the PERSON DEMOGRAPHIC business area.
I suggested we copy it under the constituent business area. Is this a common request?

March 2009

In March the questions were primarily related to joins and join management.

Question 1: The folders have the following properties: Name, Object and Identifier. My assumption is the Object keeps the pointer to the database. If so, what is the purpose of the name and identifier?

Question 2: I have heard it said that within the End User Layer the folders are primary objects. What is a primary object in Discoverer’s world and are there any others?

Question 3: Can you explain in simple terms what a fan trap is? Our users are getting error messages and we don’t know what to do.

Question 4: Michael, I have inherited a system where several joins have been defined the wrong way round, such that the Master and Detail items are flipped. Do I have to delete these joins and recreate them or is there a way to change them in situ?

Question 4: What is the right way to create joins? By this I mean, could you explain the difference between Master and Detail items and how to go about picking which is which?

Question 5: I have been having trouble with a Crosstab worksheet. It is displaying the data points across the top as columns but what I would really like is to have them laid out in rows with the Cost first, followed by the Revenue and then the Profit. Do you know how to do this?

April 2009

In April, the questions primarily related to dates and date management.

Question 1: I am new to Discoverer administration and have inherited a system that is full of date hierarchies. Can you give me an overview of these and how they are used inside Discoverer?

Question 2: Do you have a recommended design strategy for working with date folders in an EUL?

Question 3: I attended one of your administrator classes where you said that we should not use Discoverer’s date hierarchies. Can you explain how I can have a hierarchy on a date without using a date hierarchy?

Question 4: I would like to know if there is a way to use an analytic function to compare this year’s sales with last year’s sales?

Question 5: Our system is heavily dependent on dates and our users need to be able to manipulate them. Can you talk a little about some of the common functions that can be applied to dates?

May 2009

In May, the questions primarily related to calculations and algorithms.

Question 1: I've heard that you can use the LAG and LEAD functions to get data from the previous or next year. Can you tell me how to use these functions?

Question 2: I would like to be able to take the average of my periods for this year and compare these with the average for the periods last year. I want this to work in a Crossta and I want to be able to see the values for the periods as well as the average.

Question 3: I am trying to create a report that will list the ID(s) of the Student(s) who have been associated with more than one colleage at differing times during their registration. Part of my problem is that there is an outer join in use. Can you help?

Question 4: I am having difficulty working with optional parameters. I have 3 of them, one for a single year, one for a start year and one for an end year. If the user fills in the parameter for a single year I want just that year, no matter what the user puts in the other parameters. By the way, all 3 parameters are based on the same field and are all in the same condition. The problem is that when even one of the parameters is left blank, the rest are ignored. Do you have any ideas how I can override this one?

Question 5: I have a hyper drill between two worksheets and have been trying to pass a parameter between the two sheets. Discoverer fails to correctly pass the parameter. This has been acknowledged by Oracle as a bug. Do you know of a workaround that might help me with our users?

Question 6: Is there a way to BOLD the first row of data for each group sort?

Question 7: How do you rename the actual workbook in OBI Discoverer Desktop version 10.1.2.1?

December 2009

Question 1a: Is there a way to build a Crosstab from a report that is already built?

Question 1b: I have been having trouble with a Crosstab worksheet. It is displaying the data points across the top as columns but what I would really like is to have them laid out in rows with the Cost first, followed by the Revenue and then the Profit. Do you know how to do this?

Question 2: When I do a Discoverer calculation such as:
CASE WHEN Amount SUM > 20 THEN 1 ELSE 0 END
And then do a SUM on that column, my result is 1 regardless of the number of rows in the report where the calculated value is 1. I can overcome this by doing a CELL SUM. It seems to me that the calculation is being treated as either non-numeric or a detail value. I tried wrapping the calculation in a TO_NUMBER function, but it doesn't change the result. Could you explain what is happening here?

Question 3: In my EUL I have 2 folders, one for Invoice Headers and one for Invoice Details. When I create a worksheet with data from the Invoice Header and Invoice Detail folders when the amounts are all summed I can calculate the sub-total and grand total for all amounts. However, if I now add a non-metric column from the Detail folder, the grand and sub-totals for the Header are no longer calculated. Can you explain this behavior and is there a workaround?

Question 4: With the new version of Oracle Support coming on line a couple of weeks ago, I am having a hard time finding the latest updates and patches for Discoverer. Can you point me in the right direction?

Question 5: I need to make Discoverer add up the total of my sales YTD for this year and then compare these with the total of my sales up to the same point in time last year. Expanding this I really would like to be able to key in as AS OF date and get Discoverer to compute. Can you point me in the right direction?