- July 16, 2015
- Posted by: platformspecialists
- Category: Budgeting & Planning, FDMEE, Financial Consolidation & Close, Hyperion EPM
Let’s picture a situation in which you work for Hipster Shirts and Pants (HS&P), an apparel company with operations all across the globe. One crisp Spring morning you receive news HS&P has just acquired a small subsidiary company in Southeast Asia that specializes in fedoras – Fedoras Unlimited. Fedoras Unlimited has created hats for some of the trendiest celebrity names in the world.
[pictured: Fedora Unlimited “Classic” fedora]
[pictured: Fedora Unlimited “Supreme” fedora]
While great news for the company (given the fedora market is really taking off) you are now tasked with figuring out how to integrate Fedora Unlimited’s financial data into your company’s financial reporting without simply mashing the two together in an Excel spreadsheet. After all, you have a great EPM system like Hyperion already being used in the company. You decide to put your mean vlookup skills to the side and leverage that instead.
Your financial group uses Hyperion Planning as its forecasting tool and Hyperion Financial Management (HFM) to derive its consolidated reporting. While both good tools in their own right, both of these applications would be useless without data. To load the ever-important financial data to these applications, you use Hyperion’s FDMEE tool, which allows for the import, transformation, and loading of data to the various Hyperion applications.
For the purposes of this post, we will focus on the data integration aspect of this quandary – we can save the ownership rates, currency conversions, forecast processes, etc for discussions some other time.
In regards to integrating Fedoras Unlimited’s data into your system, the best general approach is to:
• Get familiar with your source data
• Determine how the data will integrate with your current chart of accounts and company structure
• Build the necessary mappings and data transformations within FDMEE
Know your data!
The first step in the process is to get familiar with the data that you will be feeding to the applications. Is Fedoras Unlimited on the same GL system as the rest of the company? What is the format of the data? Do you have the necessary fields to populate the dimensionality and structure of your applications?
In this example, Fedoras Unlimited is not on the same GL system as the rest of the company, has a different chart of accounts, and will be loaded using a flat file – a text file extracted from their GL system containing all of the pertinent data.
On the bright side, the company segments its data by legal entity and Cost Center, just like your parent company HS&P. Consider the sample of the data from Fedoras Unlimited below:
Once we have a good grasp on our source, we must tell FDMEE how it can expect to receive the data from the flat file – what data is in what field. This is accomplished using an Import Format. Luckily, our case is fairly straightforward, but users also have the ability to write complex import scripts to transform the data as its entering the application.
Let’s take a look at each of the fields in the source file:
1. Entity – Fedora Unlimited’s legal entities
2. Cost Center – segmentation of the data by departments, similar to HS&P. Notice that there is no Cost Center for the accounts starting with a 3 – these are revenue accounts and do not have a Cost Center.
3. Natural Account – GL system account
4. Sub Account – GL system sub account grouping
5. Customer Name – this is good information to have, but will not be loaded to HFM
6. Customer ID – again good data, but will not be loaded
7. Amount – Actual data to be loaded to HFM
After a review, we know our data inside and out at this point – we know that Fedoras Unlimited has fields in their GL extract file that links actual data to customers, holds customer ID numbers, and breaks accounts down further into sub accounts. First, HS&P does not report or plan at this customer level, so the 5th and 6th fields have been left off of the import format (not sourced into Hyperion). Second, to align the account mapping to our current COA, we will use an import script to concatenate the account and subaccount fields, as highlighted in the Import Format below. An import script is a tool available in FDMEE to help transform the data into a workable format as it enters FDMEE, before any further data mapping occurs. (another post to come diving in deeper on import scripts)
Follow the Map
So now we have selected the fields we need from the file and, for the purposes of this blog, have loaded those fields in to review..
After importing the data into FDMEE, we can see it in the Data Load Workbench reflecting the field mappings and scripts from the Import Format. Notice how the account and subaccount fields are now combined.
Once the data is imported to FDMEE, we need to determine how to map that data to our Hyperion applications. In our case, Fedoras Unlimited is not on the same chart of accounts as the main company. Instead of working their existing accounts into the current chart of accounts, we will map them into existing parent company accounts. Seen below, we do this using an Explicit map in FDMEE – a direct mapping of a source account to a destination account in HFM.
From an entity perspective, Fedoras Unlimited operates in 5 legal entities. However, given its size, we will map all of its data to one newly-created member within the parent company’s existing Southeast Asian division. This can be done via a Like map using the * wildcard, as seen below.
For the Cost Center dimension, we will map the source data through to HFM as is, since Fedoras Unlimited happens to use the same cost centers as HS&P.
Lock and Load!
Now that we have our mappings established, the Validate stage can run, where the mappings are executed. Now, looking back to the Data Load Workbench, we can see the data arranged in columns showing both the source, and the target. This is an important and useful feature which allows a user to unwind how a given number got into the application.
Finally, the fun starts! The data is exported and loaded into our target Hyperion applications and we have 3 gold fish indicating our success. You would have to play a lot of games at the carnival to win that many goldfish!
Dude, where is my data (coming from)?
Now that the data has been exported and loaded, we can see it in an ad hoc grid in SmartView (stay tuned for another post if you’re not familiar with SmartView).
After careful examination of the data you have loaded and, being the astute analyst that you are, you notice an interesting trend. The sales figures in Fedoras Unlimited’s “Sales Account A” have declined quite dramatically over the last 4 months.
Doing some investigating, you would like to see what comprises the $808,899.56 sitting in account 300001 so you can talk to Fedora’s controller. Using the FDMEE drillback from excel feature, we can quickly go from an ad hoc grid or query back to FDMEE to see the account and legal entity that went into that value.
Using this drillback feature, you are able to see that source account 30001002, which is attributed to online sales, is unusually low this month at just $33,345.12.
Now you have all the pertinent information you need to go back to Mr. John Controller. At this point you have done as you were tasked and loaded and consolidated Fedora’s data. Unfortunately, the shine has come off your new acquisition as their financials are not as good as you thought. It seems as though fedora market must have crashed hard…. but hey, at least you saw it pretty quickly.
Your company HS&P has learned a valuable lesson with FDMEE – Fedoras don’t Make Everybody Equity.
Also check our new EPM news site! – http://news.epmmarketplace.com/