Oracle Discoverer Applications

Oracle Discoverer in Applications Mode Michael Blunck Aris Corporation Introduction Oracle Discoverer, long an ad-hoc reporting tool within Oracle based data warehouses and custom applications, is an extremely powerful, dynamic reporting tool in an Oracle Applications environment. An Oracle Applications environment can present some unique challenges and situations for Discoverer developers. Installing Discoverer in Applications-mode can enhance the capabilities at the hands of developers. While most of the tasks involved in defining business areas are the same as those used within data warehouses and custom applications some additional steps and discovery are necessary to create useful business areas for end users. Over the years of its existence, Oracle Discoverer and its predecessor products have provided users with a tool that can be used to generate queries and create reports in custom databases and data warehouses. This is still an important use of the Discoverer product. As the Oracle Applications have matured there have been significant increases in the amount of data that can be captured and stored within the Oracle Application databases. Along with the maturity of the Applications has come an increased use of multiple Applications and the numbers of companies employing the power of the suite of Oracle Application modules. With the diversity of the Application modules and the increased desire to understand the data that is being captured, users continually wish to display the information that the data provides in more complex ways. Capturing data is relatively simple, but the data itself does not provide the information necessary to make proper business decisions. The goal is to turn the data into valuable information that can be analyzed and improve the operations of the organization. Problems arise within organizations as the users of the data compete for both computer resources and report developer resources to create reports that can be used to analyze the data. Depending upon the structure of the company, hardware improvements and report development can become an extremely costly initiative. If consultants are employed to create a custom Developer report, the cost can range between $4,000.00 and $20,000.00 depending on the complexity of the report to be created. After these costs have been incurred, there is no guarantee that the new report will provide the necessary information to all users. Frequently once users learn that the data can be added to reports, they desire more reports or different views of the same information. To remedy this issue, information systems departments are implementing Oracle Discoverer and empowering their users to generate queries against their OLTP databases. Oracle Discoverer is a very powerful reporting tool, which allows users the ability to generate queries and reports without knowing Structured Query Language (SQL). For that matter, Discoverer Administrators can create valuable business areas also without the knowledge of SQL. Administrators will need knowledge of the Oracle Application database tables and the theory behind a relational database. Still valuable information can be generated and the use of Discoverer against the Oracle Applications can be enhanced with an Applications Mode installation. Release 3.1.26 of Oracle Discoverer was the first release to offer installation in Apps Mode against an 11.0 instance of the Applications. Release 3.1.28 provided the same support for release 10.7 of the Applications. Release 3.3.48 can be installed in Apps Mode against 10.7, 11.0, and 11i application instances.

Advantages of Applications Mode The question arises ‘What are the major advantages to running Discoverer in Applications Mode?’ Installing Discoverer on the Oracle Applications database in Apps Mode provides you with the ability to use the current security in place in the Oracle Applications. Users login to the User Edition using their Application Object Library (AOL) usernames appended with an applications responsibility name and password. This eliminates the need for database administrators to create additional usernames for all Discoverer users who access the system, which reduces the workload of the DBA.

OAUG Fall 2001

Copyright © 2001 by Michael Blunck

Page 1

Figure 1 – 3i login form Logging into the User Edition in Apps Mode requires entering your Oracle Applications username:responsibility combination followed by your password and the database name. For example, MBLUNCK:General Ledger Super User.

Figure 2 - Security

OAUG Fall 2001

Copyright © 2001 by Michael Blunck

Page 2

Administrative users will no longer need to know all the usernames that need access to the business areas. They can assign business areas simply to the AOL responsibility. In an Applications Mode End User Layer (EUL) business areas are granted to AOL usernames and responsibilities. Additionally, privileges are granted to AOL usernames. Many organizations operate in a multiple organization structure within their instance of the Oracle Applications. These organizations can be in place to differentiate between inventories, human resources, operating units, and more. Oracle Discoverer in Apps Mode recognizes the organization security that has been put in place as part of the system profile options of the usernames and responsibilities. Typically this is a responsibility level profile setting.

Application Tables and Views Installing the EUL in Applications Mode relieves the administrator from the repetitive tasks of adding access to new users, but it does not make the development of business areas any easier. Oracle Application tables store a vast amount of data, but they are not created in a very Discoverer friendly manner. While the Application tables contain the usual primary/foreign key relationships that make the Oracle Database so effective, the columns within the tables are not labeled (defined) as primary or foreign keys. Due to this feature of the table definitions, it is not possible to automatically create joins of primary and foreign keys during Step 4 of the Load Wizard.

Figure 3 – Load Wizard Step 4 Many companies choose to use Noetix Views or Oracle Business Views to assist in the development of business areas. These products install a series of views within the Oracle Applications database that combine anywhere from one to fourteen tables together. These views provide administrators with predefined objects with which to create business areas from without the necessity to create multiple joins of the application tables. Still the business areas need to be developed and the views only link tables together. They do not automatically make business areas available or user friendly. Actually even with the purchase of the views, companies tend to run across problems. While the views help in the development of the business areas, they are not as simple as install the views and poof you have instant easy to use business areas. The creation of business areas in an Oracle Applications environment requires the administrators to have strong knowledge of the underlying tables of the modules installed at the site. Many custom Oracle developers have a difficult time understanding the complexity of the Oracle Application tables. While custom applications are often comprised of multiple tables linked together to store data, seldom do you see the volume of tables as found in an Oracle Applications environment. Retrieving simple Accounts Payable invoice

OAUG Fall 2001

Copyright © 2001 by Michael Blunck

Page 3

data can involve extracting data from up to eight different tables, potentially more depending upon the detail requested. These tables are not necessarily solely Accounts Payable tables either.

Developing Business Areas The overall purpose of the Oracle Discoverer Administrator is to allow end users to transform the data stored in the Applications tables into useful information. This involves creating joins between folders often across multiple application modules. Conditions can be placed upon business areas to enable better performance and limit what an end user can view. These are particularly useful in very large application databases. Providing users with a list of values on particular items will improve the use of Discoverer. Renaming items and folders is a proven benefit for creating more intuitive queries and reports. Changing other item properties, such as the Default Position and Visible to User will lead to a cleaner more user-friendly business area. Finally, the use of Complex folders can provide your report writing end users with a simple one stop shopping for creating a variety of useful reports. Joins between folders in business areas are critical to the successful use of Oracle Discoverer in an Applications environment. Although joins are not created automatically, due to the lack of primary/foreign key definition, these types of relationships do exist in the Oracle Applications tables. With the aid of the Oracle Technical Reference Manuals (TRM), the creation of joins becomes relatively simple.

Figure 4 – TRM Foreign Keys The TRMs provide thorough information of the table structures for each module. The manual has a list of each table’s foreign keys and the source table’s primary key. This feature of the TRM is extremely useful because many of the primary/foreign key relationships are not obvious. For example, the primary key Code_Combination_Id in the GL_CODE_COMBINATIONS table is linked to the foreign keys throughout the applications. One such join is between Code_Combination_Id and Sales_Account in the MTL_SYSTEM_ITEMS_B table. Another is the AP_INVOICE_DISTRIBUTIONS_ALL.Dist_Code_Combination_Id. The TRMs are instruments that will guide administrators to successful business area development. The TRMs can direct Admin users to tables, which they might never think of adding to a particular business area. For instance, Inventory Organizations are stored in the HR_ALL_ORGANIZATIONS_UNITS tables, even though they are used extensively in the Oracle Inventory and Purchasing modules. All of the organization information, such as names and attributes are stored in the shared Oracle Human Resources tables.

OAUG Fall 2001

Copyright © 2001 by Michael Blunck

Page 4

Using Join Options The Join Options allow administrators to return all the lines available in a query. Sometimes within the database there are null values for some of the join items. Using the join options will allow users to return all values regardless of null values. For instance, a user may be interested in creating a query, which displays all Purchase Orders numbers and any associated Requisition numbers. The problem here would be that without using the join options the results of the query will only display the Purchase Order numbers that are associated with Requisitions. Since a requisition is not required to create a Purchase Order all POs that were not auto-created from Requisitions will be missing from the results. To remedy this situation the administrator can create a join using an Outer Join on Detail.

Figure 5 – Join Options The Join Options can be set to look for null detail or null master items. An Outer Join on Detail returns all rows where the detail value of the join is null. An Outer Join on Master returns all rows where the master value of the join is null. One-to-One joins should be used very carefully, for in most Application databases there is a one-tomany relationship between primary and foreign keys. With the appropriate joins among the application tables, an end user can perform any query they desire against multiple application modules. End users can return the specific information in their queries as opposed to just identification numbers that are typically stored in the foreign key tables.

Conditions The use of Conditions provides Discoverer Administrators with a means to further limit the amount of data returned from a query. Conditions are especially useful when developing business areas against a large applications database. Often in very large databases queries take an exceptional amount of time to process, thus reducing the use of Discoverer. Creating like business areas with mandatory conditions, which limit the data to be retrieved by business unit or some other component, can reduce the processing time necessary to complete the query and provide the valuable information requested. Conditions can be used as increased security to allow end users access to only particular data in the folders. For instance a mandatory condition can be added to a purchasing business area limiting the data returned based upon ship to addresses. This condition will be invisible to the end users and still eliminate the possibility of users viewing information that does not pertain to their role within the company.

OAUG Fall 2001

Copyright © 2001 by Michael Blunck

Page 5

As an administrator is developing business areas for users they should gather as much information as possible regarding the results the users wish to obtain. During the communication of the business area requirements, it may become apparent that only particular data is necessary. Again creating a mandatory condition within the business area will improve the performance of the queries in the User Edition. Additionally, optional conditions can be created for the end users to use, as they deem appropriate. The use of optional conditions provides for a very userfriendly business area.

Item Classes (List of Values) The User Edition offers end users the ability to create conditions themselves within their reports, but an administrator can vastly improve the users conditions by creating list of values on items. Item Classes, as list of values are referred to in Discoverer, can be created automatically during the Load Wizard process. Often, though, it will be necessary to create a list of values for an identifier item. The Load Wizard will create list of values based on the datatype of the item. This is not always sufficient as typically there is no need for a list of values for numeric items unless they are identifier items. List of values can be used in the Workbook Wizard of the User Edition to select distinct values for particular items. This will facilitate a Where condition in the Workbook and allow the query to process in a more efficient manner. The list of values can also be used when defining Conditions, Parameters, and Calculations in the User Edition.

Figure 6 – Item Classes An Item Class can provide users with expanded drill capabilities within their queries. Using the Drill to Detail functionality when defining a list of values will allow individuals, using any item assigned to the item class, with the power to drill to any item in the source item’s folder. For example, if a List of Values is created for Organization Id in the Hr All Organizations Units folder and every other Org Id item in the other folders of the business area is added to the item class, users will have the ability to drill to any item in the Hr All Organizations Units folder. Therefore a user can determine the Organization name when running a query using the Org Id item in Mtl System Items All. This will offer the end user with the opportunity to run a simple query and drill to more detail, as they deem appropriate, without adding multiple items to their original query.

OAUG Fall 2001

Copyright © 2001 by Michael Blunck

Page 6

Item and Folder Properties While list of values can make finding particular values for items easier, a standard, user-friendly naming convention of items and folders can be extremely beneficial to the enterprise use of Oracle Discoverer. Different terminology and naming is a way of life with everything we see or do. It is no different among users of the Oracle Applications. Developers often name items differently than what the values of the items mean to end-users. Communication between end users and Discoverer administrators can determine the most useful manes to be assigned to folders and items in the business areas. For example, some of the most useful information is stored within the Oracle Application key flexfields. These items when brought into the business area are named Segment1, Segment2, etc., which can be very unfamiliar to many end users. If the segment values within the organization’s chart of accounts are Company, Line of Business, Department, Account, and Sub-Account, it makes sense to rename Segment1, Segment2, Segment3, Segment4, and Segment5 in the Gl Code Combinations folder to Company, Line of Business, Department, Account, and Sub-Account. Additionally, item and folder names can be renamed to be more indicative of there meaning to the end user groups. For instance, the Invoice_Num column in the AP_INVOICES_ALL table can be renamed Invoice Number in Discoverer. While the Mtl System Items B folder can be renamed to be the Inventory Items folder.

Figure 7 – Item Properties In addition to changing the names of items and folders, it may be necessary to change other Properties. This becomes increasingly necessary for many of the id items. During the Load Wizard process these numeric column types are automatically assigned as Data Points. The Load Wizard will automatically create default aggregates for numeric datatype columns, which is very useful for many of the numeric columns. Unfortunately, Discoverer views all numeric columns as needing default aggregates, but this is not useful for id items. The id items are identifiers or placeholders within the table and serve no purpose as a summed or minimum value. Changing the folder and item property Visible to User from Yes to No for particular items is another technique to make the business area more user friendly.

OAUG Fall 2001

Copyright © 2001 by Michael Blunck

Page 7

Complex Folders As administrators develop business areas for an Oracle Applications based installation, the use of Complex Folders can greatly improve the quality of the business area. Complex Folders allow administrators to create custom folders that contain only the distinct items that users desire for their queries. Complex folders essentially act as views on the database. They can house items from multiple application tables as long as the sufficient joins between the tables have been created.

Figure 8 – Complex Folders For instance, an administrator can create a complex folder for Oracle Payables users that contains Invoice Number, Vendor Number, Vendor Site, Purchase Order Number, Check Numbers, and Accounting Distributions. By creating the appropriate joins amongst the tables, the administrator can add these items to the Complex Folder. The administrator can then hide the remaining folders from the end users and assign the business area to the appropriate usernames and/or responsibilities. Within the User Edition, the end user will only see the Complex Folder and the necessary items with which to create detailed queries. This will provide easy one stop shopping within the business area. The users will not have to search through multiple folders to find the items they wish to include on their reports. The Complex Folder approach eliminates the need for the administrator to disable from view hundreds of items in the business area that serve little or no purpose to the end user. This will prevent the end user from having to scroll through a very large folder to find a particular item to add to their queries.

OAUG Fall 2001

Copyright © 2001 by Michael Blunck

Page 8

Conclusion In conclusion, Oracle Discoverer can greatly improve the reporting capabilities of an organization. The tool provides non-technical individuals with the power to create very detailed reports and queries. While it takes time to learn the intricacies of the Oracle Applications database tables, people do not need to also understand all of the details of SQL and PL/SQL. Having the knowledge of SQL and PL/SQL only enhances the capabilities of business area developers, but is not a requirement. Using the tips discussed in this document and strong communication among Oracle Discoverer Administrators and End Users, Oracle Discoverer can become an extremely valuable addition to the Oracle Applications environment. Oracle Discoverer is not a simple install and use piece of software, but its benefits to an organization can be immeasurable. With the proper use of joins, conditions, item classes, and complex folders all application users in the organization can be granted the power of Oracle Discoverer to enhance and improve their analysis and reporting requirements. This will in turn provide for better business decisions and management.

OAUG Fall 2001

Copyright © 2001 by Michael Blunck

Page 9