Implementing business intelligence with Oracle's E-Business Suite

This tip is brought to you by the International Oracle Users Group (IOUG), a user-driven organization that empowers Oracle database and development professionals by delivering the highest quality information, education, networking and advocacy. It is excerpted from the paper "Using Business Intelligence with Oracle's E-Business Suite," by David Fuston. Become a member of the IOUG and gain access to thousands more tips and techniques created for Oracle users by Oracle users.

Implementing business intelligence with Oracle's E-Business Suite

Along with the different modules in Oracle's E-Business Suite, some analysis of the data may need to be done with other Oracle BI/DW software or third-party tools. This tip covers the merits of using Oracle's Embedded Data Warehouse, Business Intelligence System and Daily Business Intelligence features.

Embedded Data Warehouse (EDW)

Oracle's Embedded Data Warehouse covers the GL, AP, AR, PO, HR, and part of OM modules. It was first released in GA status in 2001, but is now in "maintenance mode" with approximately 30 customers. "Maintenance mode" means bug fixes only, and no enhancements or extensions as the development teams have all departed. EDW does allow for integration of multiple instances/versions of Oracle Apps (10.7, 11.0.x, 11i) via API(s) -- the same API(s) used to load data into apps from outside data sources. EDW leverages 9i data warehousing features, primarily SQL analytic commands and materialized views. EDW partially conforms to Common Warehouse MetaModel (CWM), specifications adopted by the Object Management Group (OMG -- composed of IBM, Oracle, Microsoft, and Informatica). Beware, though, that if you start loading all your data into the DW, you will find that reporting is only supported via Discoverer.

Oracle EDW has support for non-Oracle Apps data, but it is difficult as you must...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle business intelligence Implementing business intelligence with Oracle's E-Business Suite, part 3 Implementing business intelligence with Oracle's E-Business Suite, part 2 Oracle applications best practices Where can I get more information on the Oracle CRM E-Business Suite? Numbers to words in any language Oracle vs. SAP Oracle apps DBA interview questions The BI application consolidation challenge Upgrading PeopleSoft, part 3: Application-specific conversions and going live Upgrading PeopleSoft, part 2: Installation and the move to production Upgrading PeopleSoft, part 1: The first steps Nine steps for successful CRM implementation: Check IT List Keep downtime short on 11i migration: Reusing a prepared software stack Oracle E-Business Suite Part 3: The final ERP transition: Crossing the finish line successfully Part 2: Maintaining your place in the race -- ERP project management Revving your engines: Tuning up your ERP project plan Ventana's Mark Smith on Hyperion and Oracle BI Oracle delivers database fixes in Critical Patch Update Oracle CRM On Demand data integration raises big issues Oracle applications learning guide SMART weighs Oracle vs. SAP, picks E-Business Suite 12 for IFRS compliance Oracle's E-Business Suite consolidates HBO's financial operations Ellison sees new opportunities for Oracle with Java on mobile devices

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

load data via API(s) into Oracle Apps tables first. If you use OWB and load non-Oracle data directly into EDW tables, you are into a custom non-supported solution. Also, if you need data from WIP, BOM, MRP, or FA, these modules are also outside of support. Be prepared to maintain separate security from the Oracle apps security due to separate db instance required on a separate box. The latest version, EDW 4.1 (applicable to apps 11.5.9), requires 9iAS 9.0.2+, OWB 9.0.3.5+, and Discoverer 4.1.37+. In order to get EDW, though, you will need to get the required Oracle VP approval to implement, and sign up with Oracle Consulting as EDW is in maintenance mode.

Should you decide to pursue Oracle EDW with these restrictions in mind, then you also will most likely face the need to extend the architecture. The current supported strategy to extend EDW is as follows:

• Create custom business views for custom facts and dimensions.
• Create custom facts and dimensions (in OWB).
• Create mappings for populating custom facts and dimensions (using OWB).
• Create EUL for custom facts and dimensions (using OWB).
• Export EUL from OWB.
• Create ad hoc workbooks.

Business Intelligence System (BIS) and Daily Business Intelligence (DBI)

BIS has been through major architectural changes during its lifetime, as identified below:

• BIS 1.x patch on Apps 11.0, Database Views on OLTP
• BIS 2.x released with Apps 11i, Database Views on OLTP
• BIS 3.x/4.x Embedded Data Warehouse, Data Warehouse tables
• BIS 5.0 for 11.5.8, Materialized Views

If you were starting to like the possibility the BIS would fit your needs, please consider that BIS is currently in "maintenance mode". The good news is that if the "maintenance mode" does not scare you, then you will like the fact that it uses Discoverer technology under the covers, so you can customize to your heart's content.

The announced successor to BIS is DBI, which technically first shipped as Early Adopter in February 2003, and just reached GA status in early 2004. The good news from an architectural perspective is that DBI uses same HTML server as Performance Analyzer and Enterprise Planning and Budgeting, which is not the same as Discoverer. DBI covers GL, AP, AR, PO, PR, CRM, Sales, HR, contracts, marketing, iStore and services modules; there is partial coverage for supply chain and discrete mfg. DBI uses the same security model as Oracle Apps if it is installed in same instance. It is designed to be intuitive, and therefore no end user training should be necessary, according to Oracle.

DBI does, however, have some limitations, identified below:

• Since materialized views must be synchronized on a schedule (time or event driven), each html page in viewer must have all data updated at same time.
• Not allowed to extend or customize views or viewer; must wait for next release which includes a designer and toolkit; in meantime, you can create EUL on top of materialized views and use Discoverer for customizations.
• Requires 9iR2, 11.5.8, and 9iAS; prefer RAC for performance reasons; if do not have RAC, then can use a bigger box or create a copy instance for reporting only.
• Oracle consulting must be engaged when participating in early adopter program; expect 3-6 weeks to get up and running on multiple modules.
• If need to incorporate external data sources, you are currently outside of DBI support.

About the author: David Fuston is a principal consultant at the Michael Taylor Group. He is a technical and functional consultant with over 18 years of experience in applications development, IT management, and financial controller positions for Fortune 500 and World 200 companies. He has been an officer, speaker, and sponsor in OAUG, BI/DW SIG, OracleWorld, AppsWorld, IOUG, ODTUG, KCOUG, and ICCA.