Rittman Mead - Delivered Intelligence

If you’ve been looking at the available
material and presentations on Enterprise
Planning & Budgeting, you’ve probably got a good idea now of the
capabilities of this new product. What isn’t so immediately obvious though is
how EPB works, and therefore these notes explain the background to EPB’s
architecture, how the data is stored and how EPB differs from Oracle Financial
Analyzer.

For anyone who doesn’t know already, Enterprise
Planning & Budgeting (EPB) is the successor to Oracle Financial Analyzer
(OFA) and Oracle Sales Analyzer (OSA), and is a planning and budgeting tool
based on Oracle 9i technology. Whereas OFA and OSA use Express Server as their
database and analysis engine, EPB uses Oracle 9i and the OLAP Option, and
whereas OFA and OSA are standalone applications, EPB is part of the e-Business
Suite. EPB can be run standalone from e-Business Suite, and can use data from
other ERP applications and data warehouses, but it uses e-Business Suite
technology and is licensed as an e-Business Suite module.

EPB as an application has the following architecture:

Starting with the bottom left part of the diagram, the EPF schema holds a
number of tables that are used as data sources for the analytic workspace used
by EPB. The EPF schema is a regular Oracle schema that has the following layout
of tables:

Within the EPF schema (previously referred to by Oracle as the FEM schema)
there are the following groupings of tables:

• Open interface tables - these are the tables you will load data from
  Oracle Apps, SAP, other ERP systems, or from your own data warehouse
• Simple dimension tables - used to hold lookups, such as a list of colours,
  products, shoe sizes and so on
• Predefined dimension tables - these are tables defined in advance by
  Oracle, and include such ones as Organisation, product, channel, customer,
  geography, currency and line item.
• User defined dimension tables - maximum of ten dimensions defined by the
  customer.
• Attributes table, hierarchy table and level tables - used along with the
  dimension tables to contain metadata for the dimensions
• Balances table - where you load your ledger-type fact data
• Data tables - tables for additional facts and measures, with each table
  containing data that is similarly dimensioned. Maximum of 20 tables.

It’s worth noting that the EPF schema is going to be shared by Oracle’s other
Corporate Performance Management applications (such as Regulatory Capital
Manager, Performance Manager and Activity Based Management) and some of the
prebuilt dimensions in the EPF schema (Task, Activity, Dataset and Financial
Element, for example) are for the use of these tools.

Note also that the EPF schema is not a star schema - it’s designed as a place
for storing data that will then go into EPB’s analytic workspace, which is where
all the planning and budgeting functionality is carried out. Also, you don’t
load data directly into the EPF fact and dimension tables - data is instead
loaded (using Oracle Warehouse Builder, or SQL*Loader, or another data loading
method) into the "open interface tables" within the EPF schema, and
EPB is then used to load data into the facts and dimensions. Also, EPB (and the
EPF schema) is not a substitute for a data warehouse - more likely, you would
load warehouse data into the EPF schema so that you can use EPB for budgeting
and planning, whilst still keeping a regular star schema or analytic workspace,
together with tools such as Discoverer "Drake", Oracle Reports and BI
Beans to carry out your regular warehouse queries. EPB assumes your data is
clean as well, so you’ll still need tools such as Oracle Warehouse Builder at
some point to cleanse and transform your data.

When EPB functionality is then used, the application gets data out of the EPF
relational tables, loads it into the analytic workspace within the EPF schema,
and then interacts directly with the AW using OLAP DML and the Java OLAP API.
For scheduling and workflow (a major new part of EPB functionality that wasn’t
in OFA and OSA) Oracle Workflow, together with Oracle Concurrent Manager, is
used to schedule and run tasks.

Sitting above the EPF schema is the EPB Application Catalog, EPB’s equivalent
the the BI Beans or Discoverer Catalog, which contains XML definitions of
objects such as reports, calculations and selections, together with business
process definitions in relational structures. OLAP metadata is held in the OLAP
Catalog (common to all Oracle 9i OLAP applications) and EPB’s analytic
workspaces - a shared analytic workspace for all users, together with personal
AWs for "work in progress" - are held in BLOBs within regular Oracle
tables.

Like other Oracle OLAP applications, EPB uses a combination of direct OLAP
DML access to its analytic workspaces together with SQL access, via views and
the OLAP_TABLE function, to access it’s data. EPB is a thin-client application,
and presents data to users through a dynamic HTML interface (the same look and
feel as Oracle Portal and e-Business Suite) that uses BI Beans to provide
crosstabs, graphs and reports. 

EPB uses a number of features unique to Oracle 9iR2 OLAP to provide it’s
calculation, modeling and forecasting capabilities. These include:

• support for dynamic aggregation of models and formulas - Express Server
  6.3 allowed you to dynamically aggregate variables "on the fly"
  but this required custom coding and was difficult to put together. Dynamic
  aggregation is built in and supported in Oracle 9iR2 OLAP and is now a
  tunable parameter; in addition, through the undocumented "FROM"
  clause you can now dynamically aggregate formulas as well.
• EPB takes advantage of the allocations feature in 9iR2 OLAP - you could
  always do this in OES and OFA but of course you needed to code the
  allocation routines yourself.

When users come to enter budget data through the budget worksheet, it is
keyed in through a BI Beans crosstab and stored in an analytic workspace. Data
is then recalculated, allocated and so on, and stored back in the AW. At this
point, there is no write-back (as of version 1 of EPB) which means that budgets
cannot yet be written back to the e-Business suite database.

Some general limitations to note with the first release of EPB:

• Solve (rollup) calculations are predefined (though configurable) with no
  opportunity for customisation - i.e.. you can’t "tinker" under the
  hood as you used to be able to do with OFA and OSA. This isn’t likely to
  change for several releases yet, so it’s best to think of EPB as an
  application, not a toolset, and ensure that the types of solves you want to
  do are covered in the current functionality.
• There is no Excel interface yet, so no reporting through Excel or data
  capture through Excel workbooks. Expect this is the next release.
• No multi-currency calculations in this first release
• Unlike OFA (which does this via ADI), there is no drill back to
  (transactional) GL data, and there is unlikely to be before Version 3
• Advanced Modelling (i.e. better solves) is coming in version 2
• Currency calculations are coming in version 3
• Integration with Oracle HR and Oracle Projects is likely in version 3
  (note - all versions and dates are all speculative at present)

So
how would you go about implementing EPB? Well, it’s a different sort of
implementation to OFA and OSA. OFA and OSA were application implementations for
sure, but they also benefitted from Express Server skills, to code workarounds
or added functionality into the product, and to provide much of the
functionality you now get "out of the box" with EPB. OFA however gave
you the ability to do whatever solves and allocations you wanted, whereas EPB
(in at least the initial versions) is much more of a predefined application that
gives you a set of business processes that you’ll either do already, or (like
e-Business Suite) you’ll adapt to. No doubt this stance will soften over time, with hooks introduced
into the product to make it customisable, and a broader range of solves and
functionality to meet a wider spectrum of needs, but in general terms it’s an
application not a toolset, which although it reduces flexibility a bit, makes
implementation a lot quicker and simpler than OFA and OSA.

In
terms of the typical skillsets to implement EPB, you’re probably looking at:

• Knowledge
  of Oracle e-Business Suite technology
• Accounting/Finance
  Functional Knowledge
• OLAP
  Knowledge
• Data
  Integration Skills
• Financials
  / GL Knowledge

All
of which (quick plug) we’ve got in abundance at SolStonePlus.
Drop me a line if you’re
looking at implementing EPB and would like a chat about options.

Lastly,
Lucas Jellema from Amis over in
the Netherlands emailed me with a few questions about EPB that others might find
answers to useful (please note these are not official Oracle or SolStonePlus
answers, just my interpretation)

"Does
EPB work on its own Data Warehouse and if so, is this DWH also available to
other OLAP tools for Analysis? Or should organizations doing Analysis and
Reporting apart from EPB have a separate DWH for those OLAP queries?"

EPB
has it’s own EPF schema, which it shares with Oracle’s other CPM applications,
but it’s not a traditional warehouse schema arranged as a star schema, you’re
not supposed to load data directly into it, and it’s really designed for storing
data that needs to be loaded into AWs for analysis. You’ll therefore still need
to build your own warehouse schema, and then load data (through the interface
tables) into the EPF schema for use with EPB.

"Does
Oracle recommend loading data directly from operational sources into the FEM
Interface Tables or does Oracle think it best for non-EBusiness Suite
customers to set up a Data Warehouse independently of EPB and in a later stage
implement processes to extract data from that DWH into the FEM Interface. Are
the FEM Interfaces tables at the operational (fact) level or do they already
have some sort of consolidation in them?"

It
depends. Data can be loaded directly into the EPF (new name for FEM) schema, but
you’ll probably want to cleanse and transform it first, most probably in a data
warehouse. The data that goes into the EPF schema is at transaction level, and
EPB will perform any aggregations as it loads data into it’s AW. Of course data
in the form of balances - that goes into the EPF balances table - will be to an
extent already summarised (or at least snapshotted) but the twenty user data
tables are just regular fact tables, with the granularity set to whatever you
require.

"Can
Oracle somehow convince us that while EPB does not provide hooks for
customization it will offer reports desired by non-EBusiness Suite customers
with potentially very varied businesses? How much of EPB benefits are you
missing out on if you do not have the E-Business Suite? What is EPB then
giving you that with OWB and BI Beans/Discoverer you can not easily build
yourself?"

To
early to see really, especially as no-one apart from Oracle has done an EPB
implementation yet. With regard to the question about what EPB gives you that
OWB and BI Beans don’t already give you, I think the relevant bit is "you
cannot easily build yourself". EPB is an application, not a toolset, and
it’ll appeal to those people that want to buy a supported planning and budgeting
application from Oracle, that integrates with e-Business Suite and doesn’t
require them to build everything from the ground upwards. With OFA and OSA you
could have course build a similar application yourself, using Visual Basic or
Express Objects and Express Server, but for most people buying it from Oracle
was a more sensible solution.

"The
specific case of my customer: they have clear requirements for Management
Information. Most of these are about analyzing historical data (the classic
slicing and dicing); forecasting and budgeting would be a nice to have in the
longer run. They currently do not have a proper Data Warehouse. They have a
consolidation point for some operational data. No dimensions have been
defined, no data cleansing is performed and some data is not available at all.
I am wondering whether EPB would be the proper tool for them to start with.
Given their requirements as well as their lack of current experience with BI
as well as the relative uncertain situation with regard to the data sources, I
would feel more comfortable with a more down-to-earth approach: start
designing a DWH and the processes to populate it as well as developing the
most urgently needed reports. In that case, EPB would be brought in at a later
stage when the organization as well as the data management is ready for
more advanced stuff."

Yes,
I’d agree with this approach. EPB isn’t the place to start integrating and
consolidating data, and the EPF schema isn’t where you’d want to store your
complete data warehouse. You could of course, using the twenty data tables and
the user defined dimension tables, but you’d pretty much be limited to querying
via EPB (the schema isn’t really suitable for querying directly using tools such
as Discoverer) and the AW that EPB creates wouldn’t be
suitable/supported/documented for use by regular OLAP API tools such as Drake or
the BI Beans. If it was down to me, I’d build the warehouse as normal, using OWB
or any other ETL tool, create my dimensional model as normal, then take selected
data out and put it in the EPF schema, and then carry out my planning &
budgeting using EPB. I’d then still run Discoverer / Discoverer
"Drake", or BI Beans or any other ad-hoc query tool against my
warehouse schema for the warehouse "power users", and use EPB as the
application for my finance and planning department,

Anyway,
hope this has been of use to anyone contemplating implementing EPB. Once again,
if you’re looking at EPB, have a
word with us at SolStonePlus, and
we’re particularly looking for organizations looking to carry out EPB
"proof of concepts" where we could partner up and implement the first
stages together. In the meantime, take a look at our EPB
brochure and Mick
Bull’s recent UKOUG presentation, which gives you a good idea of the
functionality EPB currently provides.

Posted in Uncategorized |