Under The Covers With Enterprise Planning & Budgeting

Mark Rittman

• 9 min read

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:

EPB product 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:

epf schema overview

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.

exampe epb report and graph

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.

Sign up for more like this.

Enter your email
Subscribe