Title: Enterprise%20Planning%20and%20Budgeting%20
1Enterprise Planning and Budgeting Three Case
Studies
David Fuston Director, Business
Intelligence Linium, LLC Cell
816.729.1033 Email david.fuston_at_linium.com
2About David Fuston
- Technical and functional consultant since 1989
with experience in applications development, IT
management, and financial controller positions
for Fortune 500 and World 200 companies. - Oracle Beta Tester and Early Adopter since 1997
for numerous programs, such as Applications 11i
(OFA, DBI, EPF EPB), 9iAS Discoverer and Drake
10g, Warehouse Builder 3i/9i, and 9i/10g OLAP.
Beta tester and early adopter since 1999 for
Microsoft Plato, OLAP Services, and SQL Server
Analysis Services. - Officer, speaker, or sponsor in OAUG, BI/DW SIG,
OracleWorld, AppsWorld, OpenWorld, IOUG, ODTUG,
Collaborate, HEUG Alliance, NCOAUG, RMOUG, SCOUG,
KCOUG, OOUG, PASS, NorCalOAUG, and Microsoft For
Developers Only conferences and seminars. - Teaching and training role started in 1984 as a
manufacturing plant zero defects trainer
utilizing the Phillip Crosby Quality College
methods. - Board member and Treasurer for Oracle Development
and Tools User Group (ODTUG). Member of Oracle
Business Intelligence Customer Advisory Board (BI
CAB), and International Oracle User Council
(IOUC) Product Development Committee and Fusion
channel.
3About Linium
- Linium is a certified consulting firm, with
practices in Business Intelligence and Oracle
Applications, and Service Asset Management. - Linium combines Best-of-Breed products and
industry best-practices with our talented and
certified IT consultants. Every Linium engagement
is managed by our PMI, ITIL, or PRINCE2 certified
project managers. - With offices in New York, Connecticut, and
Virginia, we are celebrating 8 years, still
privately held, with steady growth year over
year, currently 200 employees nationwide. - Partner Status with
4Agenda
- What is Enterprise Planning and Budgeting
- EPB evolution from early adopter to version 2
- Three mini-case studies
- US Air Force FIRST
- Guthy-Renker
- BAE
- Future Product Direction
- Questions
- Summary and Questions
5Enterprise Planning and Budgeting (EPB)
- Type 4 Application--Delivered as module of Oracle
Applications, but can be run standalone - Delivered as a patch to Oracle Applications
11.5.9 -- Included as part of the rapid install
for 11.5.10 - Backports of EPB to 11.5.8 or earlier are not
technically feasible - Requires Oracle9i Database 9.2.0.7 plus OLAP
Option or above - If a customer has 10.7 or 11.0.x or no Oracle
Applications, then the product can be a
standalone solution - More Planned Sources
- Projects
- Fixed Assets
- Human Resources
- Grants
- Supports consolidated views and scenarios of
Oracle and non-Oracle data via API(s) in FEM
Schema, same as Enterprise Performance Foundation
used by DBI modules, BSC, and PM
6Oracle EPB vs Oracle OFA
Feature OFA (Tool) EPB (App)
Installation/maintenance Express Server Oracle9i/10g DatabaseOLAP option and E-Business Suite 11i
Client VB, Java HTML
Graphics Good Outstanding
Text commentary Text FDI Cell-level annotations
Workflow None Built in feature
Process management None Built in feature
Exception alerts None Built in feature
Data processing set up Manual Automated
End user calculations Express expressions Business calculation templates
7EPB definition
- Oracle Module starting with Oracle Apps 11.5.9,
targeted as replacement for OFA. - Does not use OFA data loader, but rather is built
on top of Enterprise Performance Foundation (EPF,
a.k.a. FEM relational schema). Load batch data
via Oracle GL Link or via EPF interface tables. - Unlike Oracle Financial Analyzer (OFA) task
processor, EPB uses standard Oracle Apps workflow
and concurrent manager controls. - Unlike Discoverer 4i, EPB utilizes JDeveloper
BIBeans for calculations and presentation layer,
same technology stack as Drake (Discoverer
10gR2), Daily Business Intelligence (DBI), and
Profitability Manager (PM, formerly known as
Performance Analyzer and Activity Based
Management)
8EPB definition
- Unlike OFA which uses a Visual Basic client for
Windows and a Java client for the web, EPB uses
only an HTML client - Unlike OFA tiered structure with Super
Administrator, EPB uses 3 different un-scoped
Oracle Applications responsibilities - Unlike OFA, EPB has audit trail for each and
every change made to the data, from the original
worksheet entry thru each change until final
approval via views - Like OFA, EPB can be installed in standalone mode
or, unlike OFA, as part of the rapid install of
11.5.10.2
9EPB definition
- EPB stores all data in the Analytic Workspace
- Shared AW
- Personal AWs
- EPB, unlike OFA, has a built-in allocation
engine. Uses undocumented FROM clause to
AGGREGATE - Like OFA, EPB has solves and models
- In EPB, data in AW exposed through relational
views - In EPB, during process run, calculate as much as
possible dynamically. After process run, persist
certain levels in RAM to optimize access
performance
10FEM Schema
HierarchyTables
InterfaceTables
GL Link
AnalyticWorkspacefor EPB
AttributeTables
LevelTables
Simple DimensionTables
20 DataTables
BalancesTable
FixedDimensionTables
10 UserDimensionTables
11FEM Schema Data Tables
- Balances Table
- Dimensioned by all hierarchical dimensions
- Target for GL integration
- Target for balance data
- Used by other applications directly
- Numeric, date, text measures
- 20 Data Tables
- Administrator defines the primary key
- Effectively 20 tables for storing uniquely
dimensioned data - Numeric, date, text measures
- Specifically for EPB but can also be used for
other CPM applications
12FEM Schema Data Tables
Data Management SOURCE SYSTEM LEDGER DATASET CURRE
NCY Financial Characteristics FINANCIAL ELEMENT
NATURAL ACCOUNT LINE ITEM Financial
Consolidation INTERCOMPANY ENTITY User-Defined
Dimensions USER DIM 1-10
- Analytic Dimensions
- ORGANIZATION (COMPANY COST CENTER)
- PRODUCT
- CHANNEL
- PROJECT
- CUSTOMER
- GEOGRAPHY
- TASK
- ACTIVITY
- COST OBJECT
- TIME (CALENDAR PERIOD)
13Analytic Workspace
- OLAP in the Oracle9i or 10g Database
- Container for collections of multidimensional
data types - Contains one or many cubes, data, formulas,
stored procedures - Stored in Oracle data files
- AW tables have 2 columns
- Index
- BLOB with data stored using Express algorithm
(Each record is like an Express extension file) - Fast and sophisticated multidimensional modeling
and querying
14Middle Tier
- Applications Framework 5.10
- Set of common standards and components for UI and
middle tier (self-service UI) - HTML user interface
- Concurrent Manager Workflow (equivalent to Task
Processor in OFA) - Used to automate business processes handle
multi-thread processes - Process run creates Workflow tasks, which are
queued in concurrent mgr - Executed through concurrent manager
- Integration with Collaboration Suite through
email - Supports simultaneous execution of programs in
background. CM runs the tasks in the correct
sequence for that business process (e.g. Load
before Solve) - BI Beans 3.1
- Multidimensional query API against CwM metadata
- Java OLAP API Writes SQL queries
- Used against relational views on top of AWs
(Discoverer, BO, Cognos Query)
15BIBeans 3.1
- Java components that developers use to build
applications - Can be deployed as Java applets or as HTML
- Crosstab and graph bean within EPB documents
- Query builder bean within EPB documents,
security, and some task set up - Calc builder bean UI only for insertion of
calculations and solves - Persistence bean (BI catalog) for storing EPB
document, saved selection, calculation objects
16Client Tier
- HTML browser interface
- Portal
- Export to HTML, PDF, Excel, Oracle Reports. XML
Publisher - Briefing books
17Business Processes Batch Data Load
Business Intelligence Beans
Java OLAP API
Relational Views with SQL Table Functions
OLAP Catalog
ApplicationCatalog
Shared AW
Personal AW
Personal AW
Controller Load data task in business process
pulls the data into the shared AW
FEM Tables
Schema administrator triggers data move to FEM
tables
FEM Interface Tables
Load data into FEM interface tables
18Manual Data Collection
- Metadata for worksheet copied from shared AW to
personal AW - BUT only the data for the data collection
business process is copied - Formula used to reference shared AW data
- When user makes changes to data, it is written
to personal AW and can be saved - When user submits, a formula is created in
receivers personal AW - When they are approved, then and only then, is
data written to the shared AW
19Data Entry Manual Data Load
Business Intelligence Beans
Data Flow When Budget is Distributed
Java OLAP API
Relational Views with SQL Table Functions
OLAP Catalog
ApplicationCatalog
Shared AW
ApproversPersonal AW
SubmittersPersonal AW
Data for the Budget view is copied to the
submitters personal AW. This is the data that
he/she will change when formulating the budget.
FEM Tables
FEM Interface Tables
KEY Data is moved or copied Data is referenced by
formula
19
20Manual Data Entry
Business Intelligence Beans
The worksheet uses a formula to pull all other
data in the worksheet from the shared AW
Java OLAP API
Relational Views with SQL Table Functions
OLAP Catalog
ApplicationCatalog
Shared AW
ApproversPersonal AW
SubmittersPersonal AW
Data for the Budget view is copied to the
submitters personal AW. This is the data that
he/she will change when formulating the budget.
FEM Tables
FEM Interface Tables
KEY Data is moved or copied Data is referenced by
formula
20
21Manual Data Entry
Business Intelligence Beans
The approvers worksheet uses a formula to pull
all other data in the worksheet from the shared AW
Java OLAP API
Relational Views with SQL Table Functions
OLAP Catalog
ApplicationCatalog
Shared AW
ApproversPersonal AW
SubmittersPersonal AW
The approvers worksheet uses a formula to pull
the data for the business process from the
submitters personal AW
FEM Tables
FEM Interface Tables
KEY Data is moved or copied Data is referenced by
formula
21
22Manual Data Entry
Business Intelligence Beans
Java OLAP API
Relational Views with SQL Table Functions
OLAP Catalog
ApplicationCatalog
Shared AW
ApproversPersonal AW
SubmittersPersonal AW
When the approver approves the budget, the data
from the submitters personal AW is moved into
the shared AW
FEM Tables
FEM Interface Tables
KEY Data is moved or copied Data is referenced by
formula
22
23Agenda
- What is Enterprise Planning and Budgeting
- EPB evolution from early adopter to version 2
- Three mini-case studies
- US Air Force FIRST
- Guthy-Renker
- BAE
- Future Product Direction
- Questions
- Summary and Questions
24Enterprise Planning and Budgeting v1
- Version 1 (ZPB.A) September 2004
- See TechNote258249.1 About Oracle Enterprise
Planning and Budgeting Mini-pack 11i.ZPB.A for
all pre-requisites - In version 1 of EPB, reuse the OFA General Ledger
link, but it will only bring data from GL into
the FEM schema it will not allow you to write
data back to GL this will be a future
enhancement. - In the FEM schema, it is not a star schema it is
optimized for other processing. There are open
interface tables to all of these metadata and
data tables, and some hidden tables. - New Migration technote published as EPB OFA
Migration Tech Note.pdf dated March, 2005 - Single chart of accounts and single USD currency
supported - Delivered on top of Oracle Applications 11.5.9,
but Included as part of the rapid install for
11.5.10 applications, but can be run standalone
If a customer has 10.7 or 11.0.x or no Oracle
Applications - Backports of EPB to 11.5.8 or earlier are not
technically feasible
25Enterprise Planning and Budgeting v2
- Version 2 (ZPB.B) September 2005
- Requires either 9.2.0.7 or 10.1.0.4 database
plus patches, Apps CU2 (11.5.10.2), EPF FEM.D.1,
and EPB ZPB.B patchset G - License
- OFA/OSA licenses can migrate to either
- Full user EPB licenses or Oracle BI licenses
(Discoverer) - Express licenses migrate to Database Enterprise
Edition plus OLAP Option run time only licenses
26EPBv2 Features
- Enhancements for Enterprise-wide rollout
- Budgeting in multiple currencies
- Additional calculation templates (If-Then-Else)
- Improved security management (via Security API)
- Business areas (Multiple SoB/charts of accounts)
- Improved business process management (Solve
Options for multiple hierarchies whether
value-based or level-based) - SQL access to shared data (SQL access layer on
top of EPB metadata) - Support for XML Publisher
- Import from MS Excel
27Agenda
- What is Enterprise Planning and Budgeting
- EPB evolution from early adopter to version 2
- Three mini-case studies
- US Air Force FIRST
- Guthy-Renker
- BAE
- Future Product Direction
- Questions
- Summary and Questions
28USAF FIRST BF Program
- Goal -- Develop and install the first fully
integrated, real-time financial information
system for the USAF. The resulting Financial
Information Resource System (FIRST) will collect
all accounting data into a single integrated
system and provide an enterprise-wide view of
financial activity. - Goal -- Replace existing three disparate
financial systems which includes isolated
historical budget data. FIRST will integrate,
consolidate, and aggregate budgeting data across
all levels of organization to streamline the
budgeting process, increase efficiency and better
support decision-making.
29USAF FIRST BF Program
- Budget Formulation includes the entire Oracle
Technology stack - Oracle Designer metadata repository for Legacy
Data Warehouse - Oracle Warehouse Builder -- transform all legacy
systems data into a single unified architecture - Single Sign-On using Oracle Application Server
authentication - Oracle Discoverer (aka Drake) ad hoc and
standardized reporting from the Data Warehouse - Oracle Applications 11.5.10 Enterprise Planning
and Budgeting (EPB) in controlled release
standard and custom business process workflows - Original design utilized OFA, but then switched
to an EPB focused design as an early adopter.
There were ninety-seven separate
features/functions as determining factors in
comparison to switch.
30USAF FIRST BF Program Metrics
- EPB Configuration Specifications
- Source custom DW 6 TB (est) containing up to
40 years of data from 10 heterogeneous sources - Stand-alone EPB instance technology stack
- BIBeans 3.1
- Drake (aka Discoverer 10gR2)
- Database 10.1.0.4 plus OLAP option
- Oracle Workflow 2.6
- EPB 11.5.10 CU2 with EPB patchset F (controlled
Release) - FEM Schema patchset D.1
- 500 users via USAF portal in phase 1, potential
3000 in phase 4 - 11 hierarchical dimensions, no simple dimensions
- 90 custom attributes
31USAF EPB v2 Problems, Issues, Concerns
- Team has logged over 61 TAR(s) from March 15,2005
to February 17, 2006 - Team has identified 8 workarounds, 3 bugs, and 2
enhancement requests - Sub-BPA, sub-controllers, sub-schema
administrators, like had in OFA, not yet
available - Backup and archive strategy designed but not yet
implemented - Documentation -- user versus admin
- Data Scrubbing and Staging still 60-70 of
effort - Agreement among diverse operations for a
consistent business process flow
32Company Background
- Established in 1988, Guthy-Renker is today one of
the world's direct response television companies
with sales of more than 1.3 Billion per year and
an average annual growth rate of 25 over the
last 10 years. - The company has become vertically-integrated, now
covering every area of electronic retailing.
Channels have expanded from the original
television broadcast into cable and satellite, as
well as telemarketing, direct mail, and retail
channels. - For those unfamiliar with the company, you will
definitely know them by their spokespersons and
brands - Proactiv Solution ? Vanessa Williams
- Principal Secret ? Victoria Principal
- Winsor Pilates ? Daisy Fuentes
- Meaningful Beauty ? Cindy Crawford
- Get the Edge ? Anthony Robbins
- Natural Advantage ? Jane Seymour
33Environment
- With the tremendous growth in vertical
integration and the expansion of product lines,
the need to organize the planning, forecasting,
and budgeting processes became more acute in the
back office. - Each product manager historically operated
autonomously from the other brand managers,
including marketing staff, channel management,
customer service, IT, and accounting
policies,etc. - New CFO is breaking down internal barriers
- common measurements for determining go or no-go
decision on new product launches - upgrading the Oracle Applications to single
11.5.10 2 instance
34Environment
- Current annual budgeting process is current year
detail plus five year summary forecast. - Current process uses an incredible 190
interlocking MS Excel spreadsheets, which are
maintained by two people. Some MS Excel formulas
were traced back to be 14 levels deep. Other
spreadsheet data is being re-keyed from scratch
up to five times. - Recognizing the need for a more stable platform
that could be accessed by more stakeholders, the
search began for software to meet their needs.
35Alternatives and Actions
- As part of the search a team was sent to Oracle
User Group Collaborate06 conference to research
possible solutions, which identified Oracle
Enterprise Planning and Budgeting. - Granular external sales and cost data is held by
outsourced manufacturing and distribution
operations at EDS and other fulfillment houses.
This data is sent weekly to GRC, pre-sorted by
FoxPro at GRC, and loaded via flat files into a
sales data mart, which then feeds the GL only
using top level consolidated numbers. - An internal study was launched to compare what
requirements from original failed custom software
project could or could not be met by the EPB
module. Once Finance team deemed EPB possible,
then analysis began in earnest. Redundant,
overlapping, missing, misstated, and inconsistent
business requirements were identified. Gaps,
workarounds, standard functionality, possible
extensions, and future requirements were
compiled. A Kepnor-Tregoe methodology to the
requirements analysis to enable an overall team
decision.
36Alternatives and Actions
- Major factors in decision to move forward with
EPB were - Minimum manual intervention and data re-keying
required versus current MS Excel method - Ability to run various planning models multiple
times in any one day - Facility to centralize data collection for both
annual budget and monthly forecasting - Ability for multiple users to access/update
forecasts for specific items based on established
security levels. - Ability for individual users to create their own
reports and analysis ad hoc from one central data
repository - Ability for individual users to create their own
what-if scenarios in their own workspace,
without affecting master repository - Capability to download and upload data into and
out of MS Excel - Capability to control pixel level formatting for
certain key reports via .pdf or .xml formats
(i.e., BI Publisher) - Single central store of data with an audit trail
for analysis as the final version of the truth
37EPB Results
- Direct integration via GL Link for data feeds.
Not enough detailed data, so pursuing granular
data feeds from fulfillment houses. - Integration of budget/forecast/actuals into one
report without manual re-keying of data - Accountability for results on existing products,
new product rollouts, and entire product lines is
at an all-time high - Oracle OLAP database option is running both
Oracle Demand Planning module and Enterprise
Planning and Budgeting module in same instance on
a large Sun box. - Parallel system run for a complete overlap from
old MS Excel and new EPB systems is currently in
process, matching on multiple key financial
reports and multiple ad hoc reports
38EPB Results Continued
- FY2007 Budget process is utilizing MS Excel
spreadsheets download from EPB worksheets for
individual manual input and documentation
purposes - Complete System Testing included
- EPB module
- 11i Integration
- Data integrity
- Administrative hands-on training with consultants
in addition to administrative documentation
available from Oracle - Scope increased from original estimate to include
building reports and graphs using XML Publisher
and multiple reporting currencies inside EPB
budget based on new international entities
39Lessons Learned
- EPB will not produce certain reports if the data
is outside of GRC today - EPB and ODP use the same OLAP technology stack,
but are designed for different purposes (GL
versus OM/PO/INV data sources) - EPB has capability to reduce the monthly closing
timeline, but is not an instantaneous reporting
tool. - Detail level Operational KPIs are not included in
EPB, but are included in the BI data mart
(separate project).
40- Aerospace Defense company based in U.K. with US
subsidiaries - Platform Solutions line of business headquartered
in Johnson City NY - Military and Commercial customers
- Using Oracle since 2002
- e-Business suite Financials, Manufacturing,
Service. - Apps version - 11.5.10 Cumulative Update 2
41Oracle Financial Analyzer environment
- OFA Live in 2002
- GL-Link for Overhead Accounting group
- One set of books/one currency
- Programmatic reporting and budgeting capability
added in 2003 - Captures actual program costs from various Oracle
modules - Consolidated reporting and budgeting tool for our
line of business - Approx. 100 users across six US sites.
- Weekly refresh of actuals from Oracle apps.
- Quarterly planning cycle.
42Business Pains Needs
- Why replace OFA?
- Increasing Cycle times for data refreshes
- Business demands
- 4 hour update for solves actuals and planning
- End of life for OFA no future enhancements
- Administrative OH is burdensome manual
processes no workflow email notifications.
43Why Enterprise Planning Budgeting?
- Oracle suite is strategic choice for our Line Of
Business and our parent Operating Group (US BAE) - Analysis showed that EPB is capable of meeting
the requirements of our business. - No additional software cost - We had already
purchased the software as part of our Oracle
suite.
44EPB Implementation Details
- Opportunity to improve our design
- Is there a better way to do it? i.e. Build a
better mousetrap? - Addition of customer enhancements (thankfully,
not many!) - Simplification elimination of many attributes,
hierarchies, dimensionsReduce our 200 FDI(s)
currently in place in OFA - Utilize process driven aspects of EPB and Oracle
Workflow - Reduced administrative OH burden
- Access tied to Oracle login, not unique UNIX/OFA
login - Less babysitting required (OFA) - ability to
schedule repeatable jobs via Oracle Conc. Mgr. - No more Express/SPL requirements rely on generic
Oracle DBA expertise. - 100 web
- One login for Oracle
45EPB Implementation Details (continued)
- Development complete now in UAT instance
- Reduced OFA structures which need to go to EPF
tables - Dimensions, Attributes, Hierarchies
- Tighter integration with Oracle GL
- Entire Trial Balance in EPB Only expenses from
chart of Accounts in OFA - Consolidation of OFA FDIs
- Shared dimensionality same Analytic Workspace.
- Developer modifying the data extracts. Over 80
of our data loaded in OFA is custom extracts
outside of GL Link, and most of this 80 is
Oracle Project Accounting data - Opportunity to clean house for increased
efficiencies, such as pulling hierarchies
directly from GL Link in lieu of separately
maintaining hierarchies in OFA.
46Lessons Learned
- Need to work very closely with Oracle DBA and
Developer - Run STATS often on FEM, OLAPSYS, ZPB, SYS, SYSTEM
for performance - EPB enforces a higher level of data consistency
than OFA - Dimensions with required attributes.
- Level-based Hierarchy must have members be
consistent not a requirement in OFA. - Important to get setup of EPF right.
- Still need to run OFA through parallel transition
- US GAAP and UK GAAP make for some interesting
discussions, dimension member parents,
hierarchies, attributes, and report formats to
satisfy both groups - Pioneering the projects accounting data in same
business area with OGL data will require design
assistance from Oracle
47Success Metrics Results
- Anticipate reduced cycle times through
- Reduction of number of dimension values
- Improvements in underlying OLAP engine
- Potential Issues
- EPB is shared with other Oracle Apps. Cant
optimize EPF/EPB at the expense of other apps. - Historical data how much?
- Analysis or Audit requirement
48Looking Forward
- Oracle Data Warehouse
- Addition of one of our UK business areas that is
part of our line of business. - Use EPB built-in multi-currency option.
49Agenda
- What is Enterprise Planning and Budgeting
- EPB evolution from early adopter to version 2
- Three mini-case studies
- US Air Force FIRST
- Guthy-Renker
- BAE
- Future Product Direction
- Summary and Questions
50Oracle CPM Roadmap to Fusion
- Enterprise Performance Foundation
- Unified analytic and CPM semantic model,
including integrations to support CPM functions
for all customers (Oracle, PSFT, JDE, Siebel,
other) - Integration with PSFT GL
- Integration with Oracle HR and PSFT HCM
- -- Integration with OBIEE (formerly Siebel)
- Enterprise Planning and Budgeting
- Write-back to Oracle GL,
- Integration with PSFT GL for expense budgeting
encumbrance accounting - Integration with Oracle HR and PSFT HCM for
salary planning position budgeting - Simplification of setup processes business
process, metadata management, security
51Customizations/ Extensions
- Oracle Enterprise Planning and Budgeting is fully
configurable by the customer - There are no customization hooks within the
application. All non-OGL data must conform to
EPF requirements - Customizations to Oracle Workflow can be done
using the standard Oracle Workflow APIs
51
52More Questions?
53Enterprise Planning and Budgeting Three Case
Studies
David Fuston Director, Business
Intelligence Linium, LLC Cell
816.729.1033 Email david.fuston_at_linium.com