Portal

---

Previously known as WebDB, the Oracle Portal layer provides the means by which you can easily and effectively access and maintain information using your Web browser. It allows you to build and provide Internet- or intranet-based applications that allow your users to bring in tougher information from different sources (such as Oracle E-Business Suite applications, OLTP environment, Data Warehouse, and Online Data Stores) and access the disparate content areas' information using no other means than a browser.

Vocabulary

As with many of the other things that you deal with in Oracle E-Business Suite, the first place to start is with the new vocabulary that goes with this next product. Once you get a better handle on the words that are used in association with each individual part, the better handle that you can get on what those things are doing and the better you can ask questions and make connections.

Page

A page is a single location from which to access data that is gathered from the different areas. It is the piece that users access and interact with.

Page Style

The page style refers to the characteristics specifically associated with the particular page. It includes the colors, fonts, graphics, and structure among the other things that you associate with what you see on the page (just like the attributes that you can associate with any normal Web page).

Regions

A region is the rectangular or square portions that a page gets divided into. Many pages are divided in this way. Pages with static content typically only refer to these areas as table cells. When dynamic content is accessed through any of these areas, they are considered to be regions. Each region has its own specifications regarding how the portlets are placed and displayed within it.

Portlets

A portlet is the building block of a portal application. It is a reusable component that allows access of information from many different sources in a specific place. It is in many ways similar to a servlet (a small program that performs a specific job and provides specific information from a specific place). Portlets are used frequently on Web pages where the local weather (local to somewhere) is provided from a source outside of the immediate Web site so that people accessing the page can have an idea of what the current temperature and conditions are in that place. Each portlet belongs to a portlet provider.

Content Area

A content area is made up of a hierarchy of folders that are used to store and organize content items. It is a collection of related information that can be used to store content in a portal and includes many tools that are required to manage the content. In WebDB 2.x (as well as in real-life Internet terms) this would be understood as a site.

Folder

Folders contain files (folders are similar to directories). These files can be pages, graphics files, other folders, or many other components. In Oracle Portal, there are four kinds of folders that are supported:

• Container folders are repositories for many simple related items, such as text files, documents, graphics images, and applications components.

• PL/SQL folders are used to contain PL/SQL code that is used to render HTML code.

• URL folders allow for the access path to another Web page somewhere either related to or completely unrelated to the current content area.

• Search folders provide different views of content areas for different users. Based on portal content area searches, these folders can be created for all items that belong to the same category or that can be accessed by the same kind of user. These folders are dynamic and are updated each time the folder is accessed.

Item

An item is the most basic content unit that gets placed into a content area folder. Every item has to be assigned to a particular category.

Attribute

An attribute in Portal defines an item, which is similar to an attribute of an entity in a database's Entity Relationship Diagram (ERD). An attribute can include many things, including creation date, expiration date, author information, and category. This is similar to the real-life concept of a dog having attributes that include color, size, breed, coat type, age, and weight.

Activity Log

Portals provide a record of end user requests called activity logs. Oracle stores this log in a table. An activity log contains considerable useful information including the time of access, username, and server information.

Batch Job

Portal provides the ability for a user to submit a background process (similar to a concurrent request) that runs while allowing the user to continue to work during the process. Similar to a batch job in COBOL or other programming languages, it is used to submit long running queries that ordinarily would tie up a session while waiting for the large volume of data to be returned.

Component

A component is typically a PL/SQL stored procedure or package, but can include other programming language programs as well, that was created by Oracle Portal to allow for the building of a report, chart, or form.

Database Access Descriptor File

A DAD file contains a particular set of values that defines a particular set of connectivity attributes (from a portal to the listener) that is used to provide the information from an HTTP request. A DAD file needs information such as username, password, and connection string for the particular database instance on a particular server. A DAD file, in Oracle E-Business Suite, typically has the naming convention of <database server name>_<SID or Two Task>.dbc. Table 6.3 shows the contents of a typical DAD file.

 #DB Settings #Thu Nov 21 14:12:33 CST 2002 APPS_JDBC_DRIVER_TYPE=THIN APPL_SERVER_ID=AFFA2645466ED1F6 E099AC15C80DD1F6314765223084752 13851918059258876 TWO_TASK=TEST GUEST_USER_PWD=GUEST/ORACLE DB_HOST=dbserver.my.company.com DB_NAME=TEST FNDNAM=apps GWYUID=APPLSYSPUB/PUB DB_PORT=1521 

Applications

An application, in Portal terms, is a Web-enabled application that is built using robust component-building wizards.

External Sites

External sites are sites not directly contained within your site. In Portal terms, it is the content from any external Web site (e.g., weather, stock quotes, news) that you pull into your site by means of a portal and a portlet.

Custom-Built Sources

There are portlets that come prebuilt and configured. Custom-built sources are the content and applications pieces that have been custom built by your developers or by contracted developers using the Portal development kit to provide custom flexibility to your implementation.

How Portal Works

Oracle's Portal is a part of the technology stack and resides on one of the servers that makes up the middle tier (typically the server on which Apache is configured). The Application Server translates the requests from the browser (through which the end user accesses the application) into a call to the database typically using the same listener that you have configured for OSSWA, which makes use of the security and scalability of the existing setup. There is a PL/SQL toolkit that installs as part of the Portal installation. This toolkit translates the request's response into dynamic HTML and JavaScript and displays it to the browser. The PL/SQL gateway (also a part of the Portal installation) captures the database connection information from the DAD file and the URL is mapped to that connection information to connect to the database and execute the PL/SQL procedure. This gateway depends heavily on the Oracle Web Agent PL/SQL package that comes as a part of the 9iAS installation. Because Portal does not require the skill set of a forms and reports programmer, it is much easier to develop custom reports using simple PL/SQL programming and facilitating those programs through the Portal interface.

Discoverer

Discoverer is an ad hoc query tool that is geared to allow those with limited or nonexistent knowledge of SQL to customize their own reports to provide them just the view of the information that they require. It further provides data analysis and reporting capabilities that allow for simple, custom reports (albeit with limited formatting capabilities) without actually customizing the application. With its ability to provide pivoting, drilling, aggregation, and calculation at the click of a mouse, it extends the ease of use to be a complex analysis tool.

Prior to 11.5.7, Discoverer was an add-on piece to the core Oracle E-Business Suite. It was housed on its own server and in most cases required that if you chose to have more than one Oracle Home, that it reside in the first one that was installed on the box. Part of the installation/setup resides on a server and the other part, the EUL, resides within the Applications database. Be careful if you upgrade Discoverer that you are upgrading to a supported version of Discoverer as some of the installations cause inconsistencies within the database and may cause you problems later on with upgrading or cloning.

As of the latest several maintenance releases of the Oracle E-Business Suite, Discoverer 4i ships as an integral part of the system (and as of third quarter of 2003) is fully supported on a Windows platform when coupled with 9iAS 1.0.2.2.2. This enables you to centralize operations and eliminate servers that you may have been maintaining just because of some incompatibility issues and make better use of those resources.

Vocabulary

There is a whole set of vocabulary that goes along with Discoverer, too. In the case of Discoverer, most of this vocabulary deals with the different versions of the tool.

User Edition

The User Edition of Discoverer is the end user component, the interface through which the users will access the information. Its GUI interface will likely become a daily part of many end users' daily tools used for generating reports and analyzing data. It is a read-only access tool that provides intuitive access to financial information in a logical manner.

Administration Edition

The Administration Edition version is the tool used by the designer to present the inherent hierarchy of financial data that is known as a business area. The end users through the User Edition access these business areas. The administrator builds and maintains the business areas through this interface.

End User Layer

The EUL shields the users from the complexities of the Apps Database and shields the database from the inexperience of many of the Discoverer end users. It resides, conceptually, between the Oracle Data Dictionary and the User Edition. Physically, it resides in a special schema that is built with the specific purpose to segregate the EUL data from the other portions of the Applications database. The view that this combination provides is intuitive and business focused that can be custom tailored to suit any user's or user group's needs.

The various editions (end user, administration, viewer, and plus) communicate with the database via SQL*Net (now Oracle Net in Oracle 9i).

Workbooks

Conceptually, from the user perspective, Discoverer is very similar to an Excel spreadsheet. Think of it this way: you can loosely compare a relational schema to an Excel workbook. Each sheet is a table; each table has rows (the numbered rows) and columns (the lettered columns), the combination of which uniquely identifies one value. Each workbook combined with its associated schema is a set of tables that in some way relate to each other. The same is true for Discoverer. Discoverer comes with several predefined workbooks, each parameterized with what is required for that workbook. Each workbook is built to support a single set of financial management processes and displays how the data is displayed in each case.

Each workbook is made up of one or more worksheets.

Worksheets

A worksheet in Discoverer displays the result sets from one or more queries. Worksheets are accessed via tabbed dividers (similar to Excel again) in the workbook to which it is associated. Each workbook has associated with it a defined set of report worksheets that are able to be run in conjunction with it.

EUL

The illusive End User Layer can potentially be one of the most powerful and relied on tools that your end users have in their arsenal. It is the information about the information that resides in the database. This metadata can help in clearing the complexity of the underlying database (there are over 10,000 tables and twice as many views and synonyms coming from over 150 schemas) and provides a point and click interface for the users to interact with. The EUL holds the groupings of related objects that represent the logical data model of the company's business areas. These business areas are made up of objects (the folders and items) that are the representations of the business areas to the organization in the database. This component of the suite is a necessary part of an implementation, particularly if you are bringing to the organization the BIS to provide your enterprise with Business Intelligence tools.

The creation and maintenance of an EUL by the end user represents a significant investment in both time and resources and Discoverer can become key to the everyday functions of the staff that use it to support decision making. Loss or corruption of this environment could have significant ramifications to the organization, particularly if those reports are needed for the financial parts of the company.

EUL Tables

Familiarize yourself with the EUL tables. I am certainly not advocating going out and learning the 10,000 plus tables in the base install. The EUL tables are limited in number and you only really have to care about a handful of them.

The EUL tables are the repositories into which the definition of your EUL gets placed. These tables hold the information on what is important to the users who use this tool. It holds folders and items in the EUL as well as the database objects to which each of these items refers. As with any of the Apps resident tables, Oracle advises against modifying any of these tables.

In most cases there will not be any need to modify any of the contents of the tables. There will be occasions, however (like when a user changes his username and wants to be able to access the same reports that he or she was able to access before), when you will need to make cosmetic alterations to the data. In the name change example, you would change the username column of the eul_eul_users table, the owner column of the dis_docs_set table, the owner column of the dis_grants_set table, and the qs_username column of the eul_qpp_statistics table to reflect the username of the new user where the columns point to the old username. Alternatively, you could save off all of that user's reports to a file system on one of your accessible servers and reopen and save them back to the database as the new user. You will then need to decide if you want to continue to maintain the old reports under the old username or if you want to purge off that information.

Backing Up the EUL

Hot on the tail of saying that you can safely purge off the unwanted data, you need to make sure that there is a means by which you can get back the information that resides in the EUL. Outside of a normal backup strategy for your apps database, you should seriously consider adding a logical export of the data in the EUL to allow for a simpler restoration of the data in case there is an inadvertent deletion or problem. This will allow you to capture the entire definition of the data that the enterprise finds important and the associated stored queries that go along with it.

Eex files (not to be confused with exe files) are those files that contain the definition of each business area. You can further add to the flexibility of your recovery scenario of the EUL portion of the database by doing an eex export. This export writes the definition of the business area from the definition that resides in the database to a text file. While this export does not bring with it the queries that reside in the database, it does provide, in an easily reimportable format, the core information about the business areas that your company finds important. These are also the exports that you will have to do when you upgrade to a newer version of Discoverer. These eex files contain the list of all joins that are associated with any particular business area. Being able to easily recover these join conditions will allow existing queries to open and function and will eliminate the required rebuild of the joins using the same names should they become corrupted by the deletion of the information or the instability of a portion of the system.

Deleting Objects

Occasionally it will become necessary to remove things (folders or items) from your EUL that are no longer necessary. While it is easy to remove an item from the layer that does not appear to be used by simply accepting the Delete from End User Layer option provided in the system and completely removing not only the definition of the folder but all of the folder contents associated with the given business area as well, it is probably better to hide the folder for a certain period of time (weeks to months) before you eliminate that information entirely. You can create your own holding corral where you can place the objects so that they can no longer be referenced, but in a place from which you can quickly move them in case they become necessary.

---