Oracle Fusion Applications 101: BI & Reporting

Oracle Fusion Applications provides an extensive range of BI Reporting tools and capabilities all tightly integrated with the FA user experience (UX).

The Business Intelligence and Reporting infrastructure is delivered by Oracle Business Intelligence Enterprise Edition, OBIEE provides analysis tools, interactive dashboards, alerts and an enterprise reporting engine that lies at the heart of the BI and Reporting capability for FA. A number of BI products are integrated with OBIEE to deliver the FA BI and Reporting expierience:

Oracle Business Intelligence Publisher (BIP) - The latest incarnation of the old XML Publisher, a pixel perfect reporting tool, providing a complete enterprise solution for authoring, managing, and delivering reports from multiple data sources (SQL, ADF-BC View Objects, XML) in multiple formats (PDF, Excel, HTML) through multiple channels (E-Mail, Print, File).

Oracle Business Intelligence Applications (OBIA) - A prebuild data warehouse for FA transactional data, provides the warehouse database schema and the logic that extracts data from the Oracle Fusion Applications transactional database and loads it to the warehouse; OBIEE reporting tools like Answers and Dashboards are used to query data.

Oracle Transactional Business Intelligence (OTBI) - Allot of confusion and hype around OTBI, will dedicate my next post to it, follow me on twitter @FusionApps101.

ESSBASE - "Extended Spread Sheet dataBASE", selectively used to enhance the FA reporting capability, used by Fusion Financials in the Fusion Accounting Hub application to delivery real-time analytic reporting on General Ledger Balances.

Like most things with Fusion Applications, the BI Reporting strategy and capabilities are extensive and initially somewhat bewildering. It takes time to wrap your head around Oracle's master plan for OLTP BI and Reporting, but WOW what a brave new world!

Oracle Fusion Applications 101: Enterprise Scheduling Service

I suspect these posts will continually try to relate functionality in Fusion Applications (FA) with my background in Oracle e-Business Suite (EBS), so apologies if your from the JD Edwards, PeopleSoft or Siebel crowd.

SAP? Well no apologies or sympathy, we all have our problems.

The Enterprise Scheduling Service (ESS) provides the EBS Concurrent Manager equivalent service in FA but with an extended list of features and functionality. The Enterprise Scheduler is a J2EE application that provides schedule based callbacks to other applications to run their jobs, it does not execute the jobs itself.

Here is some interesting points to note:

• Completely Metadata driven: the metadata consists of job definitions, including the executable class, parameters and schedules. All Metadata is created using Oracle JDeveloper, limited functionality via FA ESS Job Definition UI to register a Job.
• Job Definition or Job = Concurrent Program in EBS.
• Job Set = Request Set in EBS, sequential or parallel set of Job Steps, where a Job Step can be a single Job or another Job Set.
• Different job types, including JAVA, SQL, PL/SQL, SQL*LOADER, C, HOST and BIPublisher.
• Includes features like Incompatibility, Schedules and Workshifts.
• Monitoring of jobs through ESS Monitor UI and/or Oracle Fusion Applications Control (Enterprise Manager)

Want to know more, see the Oracle® Fusion Applications Developer's Guide for Oracle Enterprise Scheduler for more detail.

#OracleFusionApplicationLesson 2 for Oracle EBS Developers: Your going to be defining concurrent programs through JDeveloper.

Oracle Fusion Applications 101: Bye, Bye PL/SQL

Starting to get hands on with Oracle Fusion Applications (OFA), one of the first clear indications that OFA is a completely different beast compared to Oracle EBS is by looking at the amount of PL/SQL packages used in EBS 12.1.3 compared to OFA 11.1.1.5.1:

-- Oracle EBS 12.1.3 --

SELECT COUNT(*)

FROM DBA_OBJECTS

WHERE OBJECT_TYPE = 'PACKAGE BODY'

and owner not in ('SYS','SYSTEM');

COUNT(*)              

----------------------

47929    

-- Oracle Fusion 11.1.1.5.1 --

SELECT COUNT(*)

FROM DBA_OBJECTS

WHERE OBJECT_TYPE = 'PACKAGE BODY'

AND OWNER NOT IN ('SYS','SYSTEM');

COUNT(*)              

----------------------

3677

In EBS 12.1.3 the majority of Business Logic was developed in PL/SQL, either in server side Packages or contained in Forms and Reports native PL/SQL. Since 11.5.10 and the introduction of OAF, allot of new Business Logic has been developed in JAVA, contained in BC4J Entity, View and Application Module objects. With Fusion Applications, the entire product is developed as a Java Enterprise Application.

As we can see from the amount of PL/SQL packages, only a small amount of business logic runs outside of JAVA, predominantly in PL/SQL for more data-intensive tasks.

#OracleFusionApplicationLesson 1 for Oracle EBS Developers: Get your JAVA on!

Oracle EBS Customization and Extension - OAF vs ADF vs APEX vs Forms

The E-Business Suite Technology Group recently released a whitepaper: Extending E-Business Suite 12.1.3 using Oracle Application Express. In summary, "This new whitepaper outlines how to extend Oracle E-Business Suite 12.1.3 (and higher) functionality using Oracle Application Express. Recommended architecture and security considerations are discussed in detail." For some time now EBS customers have used APEX to extend EBS, with the release of this whitepaper the EBS Tech group has acknowledged it's growing use and have provide recommendations and guidelines for standardised integration.

What's this all about some might ask, is Oracle moving to incorporate APEX as part of the default EBS tech stack? What about OAF? Isn’t Fusion Applications build on ADF, so what's up? Well when it comes to Oracle EBS extension and customization, OAF is still top dog, the E-Business Suite Technology Group continues to recommend OAF for EBS extensions.

Without a doubt, ADF is the future, it’s a very powerful alternative to OAF, it provides an array of new functionality and is used to develop Oracle Fusion Applications. If you are planning a stand alone application, not requiring the tight integration provided with OAF, ADF would be an excellent choice. But where Oracle EBS R11.X and R12.X is concerned, it’s always recommendable to use a tool set that is part of the current tech stack and provides tight integration i.e. security, flexfields, personalization etc.

I have to come clean here, I have never been a supporter of using APEX to extend EBS, but I am glad to see the E-Business Suite Technology Group laying down some standards and guidelines. Whatever the motivation for choosing APEX, and in my experience it’s usually a OAF skills shortage problem, it’s good to know that Oracle is bringing some order to the current free for all approach to APEX extensions.

Forms? Dead, move on.

Although ADF is the future and APEX is now "supported", the Oracle EBS UI is and will continue to be developed using OAF, it’s powerful personalization and extension framework, plus transparent upgrades and seamless integration makes OAF the number one choice in EBS extension and customization.

Finding the Cost of an Index

Building and maintaining an index structure can be expensive, and it can consume resources such as disk space, CPU, and I/O capacity.

According to the Oracle Database Performance Tuning Guide 10g Release 2 (10.2): "...each index maintained by an INSERT, DELETE, or UPDATE of the indexed keys requires about three times as much resource as the actual DML operation on the table. What this means is that if you INSERT into a table with three indexes, then it will be approximately 10 times slower than an INSERT into a table with no indexes".

So let’s attempt to demonstrate this estimate. Let’s start out with one of the largest tables in the Oracle E-Business Suite; ONT.OE_ORDER_LINES_ALL the table stores sales order lines for the Order Management module. I have recreated the table and associated indexes from a 11.5.10.2 installation on a Oracle 10.2 XE database.

Some facts on my testing:

• The ONT.OE_ORDER_LINES_ALL table contains 340 columns.
• Standard indexes consists of 1 unique reverse key index and 17 non unique indexes, together they index 26 columns.
• There are 14 custom non unique indexes covering 35 columns (9 function based).

Test 1: Insert 20000 rows using Insert with select sub query.

With no indexes: Avg 2.5 Seconds.
With standard indexes: Avg 6 Seconds.
With standard and custom indexes: Avg 18 Seconds.

Test 2: Insert 20000 rows using Bulk Collect Cursor and For Loop with Insert:

With standard indexes: Avg 8 Seconds.
With standard and custom indexes: Avg 22 Seconds.

I did not experience the 10 times slower estimate made by the Oracle Performance Tuning guide but the custom indexes significantly degraded insert performance.

So what is custom indexing doing to your Oracle EBS performance?