Finally getting back into the swing of things after an awesome #Kscope12 conference. It was a great learning experience and networking wasn’t too bad either. Met personalities such as oraclenerd, Kent Graziano, ODTUG Presidents Mike Riley (outgoing) and Monty Latiolais (incoming). All great people with a wealth of knowledge.
In case I didn’t mention it, I am also the Discover Administrator. It’s an older tool, but it still does its thing quite well, and as long as we are on EBS 11.5.10.2, we will be using it. With that said, when I came on board, Discoverer was being used as a custom reports distribution tool. The developers would write custom SQL register it in Discoverer, create the workbook(s), and then, in some cases, share those workbooks with the end users. Problem is, these reports could take 3 maybe even as much as 6 hours to run. Not a good look I must say. Some of these use multiple UNIONS, and I am researching ways to minimize runtime on those in general. The more standard custom SQL code with a couple of joins, some conditions, and maybe a calculation or two can be handled quite easily.
Using Discoverer objects within the End User Layer (EUL) can reduce your runtime significantly.
1. The simplest approach is to identify the TABLES and/or VIEWS in the custom SQL query and import them into the appropriate Business Area (even it that means creating a new one).
2. Create the joins as they are in the SQL statement
3. Finally, using Discoverer Plus, create your Conditions and Calculations directly in the workbook.
While you may not notice any difference when running a simple query like the one above, you will with the more complex multiple joins and conditions. The other benefit to doing it this way is that you expose the entire table to the users that are developing these workbooks. This way, they are not limited to just the set of columns defined in the custom SQL statements.
As time goes on, I am sure that you will see more posts on this topic. With the impending OBIEE implementation, I look forward to making the transition to Subject Areas from Business Areas 🙂
