Integration Challenges as EPM Environments Evolve

Summary:

As the importance of EPM environments increases, more and more data sources need to be interfaced with. Many organizations solve this problem by building point-to-point integrations. What they often don’t realize early on is that their environment will become very hard to maintain over time. This article describes how EPM integration challenges evolve over time: what is easy to manage on a small scale leads to severe growing pains as integration needs expand.

This is the second post in our series about EPM Integration Strategy. Please also read our first post Avoid Growing Pains with an EPM Integration Strategy.

The Initial Focus of an EPM project is on Creating Business Value

Organizations implement EPM solutions to improve their ability to analyze, plan and report the key drivers for their on-going success. Their goal is to overcome information challenges and be prepared for an uncertain future. Finance teams and the various other stakeholders of an EPM environment begin this process by identifying their most important metrics. Then they analyze their existing processes and evaluate how an EPM solution can help to support their needs in a better way. The requirements that result from this assessment define the objectives of the project and the overall direction going forward.

This is a crucial part to justify the investment in a new solution or enhancing an existing one.

Let’s Start Our EPM Project

At the beginning of a project, the EPM team defines the requirements and comes up with the solution design. The project plan details the phases from inception to go-live. While the application team implements dimensions, forms and business logic, the integration team defines the data sources. As the build activities progress, business users provide their valuable feedback in review sessions.

At this point in the project, the integration challenges are limited to the tactical requirements of the project. This means we are developing a new process in a sandbox-like environment and don’t need to worry about the big picture.

Building the First Integrations is Easy

The primary focus of the integration team at this point is to answer the following questions:

• What data do we need?
• Is there a data source?
• How can we access the data from this source?
• Which mappings and transformations do we need?
• What does the data flow look like?
• Which dependencies exist?
• How do we manage metadata?
• How do we automate the solution?

As for implementing the solution, here are two common ways (from the Oracle EPM tool set) to address these basic integration challenges:

• Essbase Load Rules
• FDMEE/Data Management

Essbase Load Rules

The integration approach using an Essbase Load Rule is quite simple: at first, the Load Rule accesses a data source (like a file or a database), then it transforms the data into the proper format for Essbase and loads the data to the cube.

The Essbase Load Rule might perform simple transformation tasks, like adding a prefix or splitting one field into two. After that, the modified data is loaded to the Essbase cube. Please note that load rules can only access one data source at a time: either a file or a database, but not both at the same time.

FDMEE / Data Management

The integration approach using an FDMEE / Data Management Data Load Rule follows a similar concept as Essbase Load Rules, but requires more setup steps:

1. Access data from files as well as certain database and ERP systems
2. Perform simple modifications during the Import step, like concatenating fields or skipping records with zero balances (if needed)
3. Define mappings for each dimension in order to successfully validate the data (required)
4. An output dataset is created and loaded to the target application (e.g. Essbase, PBCS)

While both of these approaches work well in small environments, they become harder and harder to manage as environments are growing. Therefore, integration challenges start to develop. Let’s picture how these solutions evolve as an environment is growing over time.

How a Successful EPM Environment Evolves

Our first project was a huge success! Now we are thinking about implementing additional use cases which expand into other business functions and departments. There are many new ideas and we start brainstorming the outcomes of the solution. Again, the focus is on the business functionality which of course is the most important aspect for our new initiatives.

This section describes how the integration processes evolve over time. Please pay attention to two trends:

1. The various project initiatives have a negative impact on our overall data flow
2. We are losing simplicity and transparency along the way.

Let’s picture this evolution by following a real world scenario based on the following assumptions:

• Enterprise Resource Planning/General Ledger
  • Data is coming from more than one ERP system
  • We are loading data to multiple EPM Applications
  • We want to write back budget data to at least one of the ERPs
  • For our Sales cube, we also want to load Billings, Bookings and Backlog information
• Customer Relationship Management (CRM)
  • We want to use Sales Pipeline data as a baseline for our forecast
  • BI & Analytics application demand access to Sales data
• Human Resources (HR)
  • We want to load HR Actuals from our payroll system to our Workforce cube
  • We want to load To-Be-Hired information from our Talent Managment system
• EPM Transfers
  • Analytics and Consolidation systems demand access to Plan data for variance analysis
  • We need to seed data for Scenario Modeling
• Downstream systems
  • We need to export data for SEC Reporting
  • Plan data needs to be shared with other BI and Analytics applications

Project 1 – The Pilot (No Integration Challenge yet)

Integration Objective: Load General Ledger data to a Planning application using FDMEE/Data Management:

This process looks fairly simple to manage, but of course there is a lot more going on here behind the scenes:

• One arrow might represent two or more integrations (e.g. for FX Rates, Statistical accounts, possibly some supplemental files)
• Each arrow represents several mappings, some of them might be quite complex
• Process are often managed manually which requires a lot of coordination: timing of when data is available, dependencies of different processes, variables need to be updated
• Processes are automated using scripts with 100s of lines of code (these can be .bat scripts or Event Scripts in FDMEE/Data Management)
• If cloud and non-supported 3rd party systems are in the mix, integration becomes an even bigger problem
• any many more

Integration Challenge: Low (there really isn’t much going on here yet)

Project 2 – Adding More Business Units

Integration Objective: we want to add additional GLs to our Planning application:

• Integrate Data from more than one ERP system
• Write back budget data to at least one of the ERPs

Please note: I removed the icon for FDMEE/Data Management. This way we can keep the diagrams cleaner as it evolves.

Integration Challenge: Medium (things are starting to become more complex, especially when you consider different loads for FX Rates, Statistical accounts, possibly some supplemental files, etc.; you can reduce cost and effort by 20-40% with an integration strategy)

Project 3 – Adding Analytics, Consolidation and Scenario Planning

Integration Objective: we need a reporting cube for more analytics, a solution to consolidate our global results and facilitate Scenario Planning:

• Load data to multiple EPM Applications
• Transfer Plan data to both Analytics and Consolidation systems
• Seed data for Scenario Modeling

Integration Challenge: High (there is a lot of data movement going on; you can reduce cost and effort by 30-50% with an integration strategy)

Project 4 – Adding CRM and HR Use Cases

Integration Objective: we want to build additional models to better forecast Sales and Workforce data

• Leverage Sales Pipeline data as a baseline for our forecast
• Load Billings, Bookings and Backlog information from ERP to our Sales cube
• Seamlessly integrate HR Actuals from our payroll system to our Workforce cube
• Provide To-Be-Hired information from our Talent Managment system

Integration Challenge: Very High (you can reduce cost and effort by 40-60% with an integration strategy)

Project 5 – Outbound Feeds to External Systems

Integration Objective: we want to share data with users of other BI platforms and streamline SEC reporting

• Load Sales data to our BI & Analytics application
• Export data for SEC Reporting
• Share Plan data with other BI and Analytics applications

Integration Challenge: Very High (you can reduce cost and effort by 50-70% with an integration strategy)

Conclusion: Integration Challenges develop over time

Wow, compare the diagram for Project 5 with the one from Project 1: that’s quite the evolution there, isn’t it? Charles Darwin comes to mind, educating us on the “Survival of the Fittest”. Your administrators have probably started looking for new jobs in between projects 4 and 5.

But seriously: this is a common situation that many EPM teams find themselves in after expanding the usage of their EPM environments. Without a solid foundation for growth this is nearly inevitable.

In the next article we are going to discuss how to build a foundation for growth. As a solution to the integration challenges described here, we are going to discuss a concept that originates from BI and Data Warehouse projects. Based on my experience, it is not well known in the EPM community.

Stay tuned for the next article – and please share your thoughts in the comments below.

---

In the meantime, please follow us on LinkedIn and find out more about FinTech’s Integration and Automation Platform ICE Cloud. Let’s make EPM Integration and Automation easy!

Learn more about ICE Cloud