How to Use FDMEE Cloud to Load Multi-Column Data Files to FCCS

Over the years one of the more challenging things about FDM Classic and FDMEE is that both require a source data file to have one amount value per row. To address this limitation we had to develop some custom solution to pivot the data. See the example below.

Multi Column Source File Transformation


Finally, we can do away with this custom stuff. In a recent release of FDMEE Cloud – the version of FDMEE that comes with EPM cloud apps such as Financial Consolidation and Close Cloud (FCCS) and Planning and Budgeting Cloud Service (PBCS) – FDMEE can now handle multi-column data loads out of the box.

FDMEE Cloud has several new file type formats, including 2 multi-column format options (see the image below).

FDMEE File Type Format Options


In this blog post we’re going to use the multi-column numeric data format option. Note: The following integration exercise was done using FCCS.

First, here’s our multi-column data file with Accounts in the rows and Programs in the columns.  We only want to load values for 4 of the eight data columns, those highlighted in red.

Sample Multi-Column Source File


To start, we want to define our import format, selecting file type: Multi Column – Numeric Data.

FDMEE Multi-Column Import Format Header


Now, we go to the import mapping details and map the source columns to target dimensions. On the Amount field, we want to select the Expression Type of Driver.  The Add Import Format Mapping Expression dialog box appears.  In the dialog box we do the following:

  • Specify the driver dimension: Program.
  • Select the Program dimension members that correspond with each of the data columns we’re pulling data for.
  • Use the Column(s) text box to identify the column numbers for our driver dimension values.

FDMEE Import Format Expression Dialog Box

Once saved, you can go to the import format detail grid. Notice the contents of the Amount Expression field based on our setup.

FDMEE Import Format Detail Amount Expression

To complete the integration build, we build the data load mappings and a data load rule.  That’s it!  We’re now ready to import our multi-column data file.

Below is a screenshot of data load workbench after the multi-column data was imported using our new multi-column import format. You can see a row for each Account/Program combination in the file.

FDMEE Data Load Workbench Multi-Column Load

Enhanced File Type Formats and On-premises FDMEE – As of October 2016, the new file type formats only exist for FDMEE Cloud. We can expect this feature to debut in the next patch release of on-premises FDMEE, which we anticipate to arrive by year’s end. (Safe Harbor applies, of course.)

Submit a Comment

Your email address will not be published. Required fields are marked *