Multi-Period Data Loads via Data Management in the Cloud (PBCS FDMEE)

If data needs to be loaded for an entire fiscal year; you may want to consider loading a “multi-period” file to via Data Management to PBCS. This will be utilizing the “Data Management” functionality on the cloud.

Note, if you have On-Premise FDMEE and PBCS applications, these are separate instances of FDMEE.

When logging into PBCS, to get to Data Management either click the Tools > Data Integrations, or go to Navigate > Administer > Data Management.

A multi-period data file in this example has 10 columns representing the dimension members in my PBCS application, and 12 columns of data, Jan-Dec (screenshot only shows through Aug).

To start the build of the integration, in Data Management go to Setup > Register > Source System and create a Source System file. I’ve named mine “BarbieFile”.

Assuming the PBCS application is already set up as a Target Application, next is time to create the Import Format.

Go to Setup > Integration Setup > Import Format. Create a new import format using the green + button. Name the Import Format (mine is BarbieImportFormat), and select the Source Type as ERP, and from the Source* dropdown, select the Source File setup in the previous step.

File Type should be set to Multi-Period. This is imperative if loading a multi-period file. Set the Target to the PBCS app, and the delimiter appropriately. If it is a .csv file, set to comma as I have done here.

Save the import format, and scroll down in the FDMEE window to complete the column mappings. Column mappings line up the column from the source file to the dimension in the PBCS application.

In the Amount field, a script is required to capture the multi-period data columns. In my example, the data columns begin on the 11^th column, and continue for 12 months – or until column 23.

Script: Column=11,23

Complete the rest of the column mappings from the Source column to the PBCS application (Target) column.

Navigate to Setup > Integration Setup > Location and create a location for the multi-period load. I’ve named it BarbieLocation, and it uses the BarbieImportFormat Import Format. Save the Location.

Finally, navigate to Workflow > Data Load > Data Load Rule. Ensure the POV is set to the location (BarbieLocation) and a POV date member within the load year.

Select the category, File Type should default based upon your Import Format settings, and set the Target Plan Type (if you have a mutli-Plan Type application).

Under Source Options, navigate to the file in your FDMEE inbox.

Note: in order for Data Managmeent in the cloud to utilize a data file; it must be copied from an On-Premise location to the cloud Data Management inbox by using the File Transfer Utility (EPMCopy.exe).

Press Save in the upper right-hand corner.

Execute the Data Load Rule. When the rule options appear, select the following:

Note: It is important to set the Start and End Periods for multi-period files. If you do not select the correct End Period, the file will only load until the End Period specified (i.e. if End Period was set to “Mar-15”, only Jan, Feb, and Mar would be updated).

Conversely, if you do not want to update the first 3 months of the year and only everything after March, change the Start Period to “Apr-15” and the End Period to whichever period you wish. Only the columns of data included in the range will be loaded.

Any columns specified in the load that do not have data (say it is June so there is no data available for July) the column data does not get loaded, and does not load blank/zero.

After executing the load rule, remember it will kick off the Import/Validate/Export processes. The Validate step will apply any mapping logic (or lackthereof) and the Export step will export to PBCS. If the mapping/data files contain members that are not present in the PBCS application, an Essbase Error: 3303 error will be thrown; and then mapping will need to be adjusted, or the member added to the PBCS application before data can be loaded.