FDQM/EE Loading Metadata to on-premise and cloud EPM Applications – Part-1


On-premise FDMEE will be used as the tool that facilitates loading metadata to the target EPM application. The high level process to use on-premise FDMEE to load metadata is
as follows:

1) Create a target custom application in FDMEE that includes a column for each of the attributes that the user wishes to load. Some of these attributes will be in the source file, and some may be defaulted during the import process. For example, the source file may contain the member name, description, parent, IsICP flag, SecurityClass, ValidForPlanType, etc.

2) Define an import format in FDMEE to map the columns in the source file to the dimensions in the target custom application. For attributes not in the source file, define an optional default in the import format.

3) Enter any necessary mapping rules for any required transformations.

4) When running the integration with FDMEE, the system will create a .dat file in the outbox directory with all the columns specified in the custom Application definition when the Export button is clicked. This file cannot be directly loaded into the target EPM application and requires further processing to match the required format of the target EPM application.

5) Create an AftExportToDat event script that will be used to format the data loaded to FDMEE into the file format required to load data to the target application. An additional script (BefLoad) will also be used to call the necessary API to load the file to the desired target application. In some cases file headers, section headers, etc. may be required as part of the metadata load file.

Please note that there is more than one way to generate a metadata file from FDMEE that can be loaded to a target application. Some users prefer to load data to TDATASEG, and then query TDATASEG to select the necessary data. Other users will scan the system generated file from the export process to generate the file to load rather than selecting data from the TDATASEG table.

Both methods are valid, and it is really the user’s preference to select the method they desire. Once these artifacts are defined in FDMEE, users can begin to load metadata from a file using FDMEE. It is assumed that the reader of this document is familiar with FDMEE, and Jython scripting.

Custom Application Setup
The first step is to create a custom application in FDMEE that can be used as a dummy target for setting up the FDMEE artifacts. The custom application will contain a column for each attribute that the user intends to load as part of the metadata import process. In addition, the customer can also add description and attribute columns to the import format to extend the number of attributes beyond what is defined in the custom application. Please note that the customer is constrained by the TDATASEG table definition in regards to the number of columns that can be used to support the desired set of attributes. The set of columns that can be used includes Account, Entity, ICP, UD1-20, ATTR1-14, DESC1-2 which is a total of 39 columns that can be used to define the metadata that is desired to be loaded. (DATAX is not used because custom applications only support numeric data loads.)

Import Format
The import format is used to map the data from the input file to the dimensions in the target application, and is also a way to set defaults for attributes that are not included in the source file. As of the date of publication, custom applications only allow loading of numeric data, and the user will need to define the source file type as “Delimited – Numeric Data”, and provide a number as a default in the expression field for the amount. This does not impact the metadata load process, but is required to successfully load the file to FDMEE as non-numeric amounts are rejected by FDMEE while performing a numeric data load.

Additional FDMEE Setup
Users will perform the rest of the required setup like a regular data load process and define the required periods, categories, mapping rules and data load rules. Artifacts like periods and categories are not required to load metadata, but are required in FDMEE to successfully process a data load file and will need to be setup. A generic category/period mapping like Metadata / Metadata_Period for example can be created for this purpose.

Event Script
An event script is used to either extract data from the TDATASEG table and reformat into a text file, or process the data file that is generated from the custom application export process. In addition to reformatting the data into the correct format for the target application, the event script must also call the appropriate API or command for the target application. For HFM, the script needs to call the HFM Java API. For on-premise planning, the script must call the outline load utility or the REST API (11.1.2.4 version). For the cloud, the script must call the REST API
or EPMAutomate.

Load to on-premise Financial Management (HFM)
For this blog , we are assuming that the user is loading metadata to HFM version 11.1.2.4.x, and will be using the Java API to load the metadata file. The unique aspect about loading metadata to HFM is that the metadata load file includes multiple sections, and the event script needed to output the metadata and load the file will need to take these different sections into consideration. This
example will show how to load a data file in a specific format to an HFM application. It is possible that users may need to load a metadata file with additional sections not included in this example, and the user should reference the HFM administration guide to understand the format required for any additional information.

Please note that the process to generate the metadata file to load to HFM is as follows:

1. Import file into FDMEE.
2. Perform mapping.
3. Process *.dat file from the export step to generate member data and hierarchy data. The output file will be read one time to generate the individual member data, and one time to generate the hierarchy section of the file.
4. Any logic to determine the “Default Parent” needs to defined accordingly.

Source File
For this example, the following file was used:
EntityName,Description,IsICP,SecurityClass,HFMLevel1,HFMLevel2,HFMLevel3

World,Earth,N,OPWP_90,World,,
Asia,Asian Countries,N,OPWP_90,World,Asia,
Europe,European Countries,N,OPWP_90,World,Europe,
North America,Northern America,N,OPWP_90,World,North America,
India,India,Y,OPWP_90,World,Asia,India,Asia
China,China,Y,OPWP_90,World,Asia,China,Asia
United Kingdom,England Wales & Scotland,Y,OPWP_90,World,Europe,United Kingdom
Canada,Canada,Y,OPWP_90,World,North America,Canada

The input file includes a header row which documents the layout of the file.Loading metadata to HFM also requires a hierarchy, and this data is included in the metadata file in the last 3 positions labeled as HFMLevel1, HFMLevel2 and HFMLevel3.
The intended member output for the load file is defined as the following:

‘Label;DefCurrency;AllowAdjs;IsICP;AllowAdjFromChildren;SecurityClass;UserDefin
ed1;UserDefined2;UserDefined3;HoldingCompany;SecurityAsPartner;Default
Parent;Description(s)

Some of the output is not included in the input file, and these items will be added as defaults from the import format, or as part of the event script that generates the output file.

Custom Application Definition
The custom application used in this example uses the following dimensions:

Dimension Name Dimension Class Data Table Column
AllowAdjFromChildren Generic UD5
AllowAdjs Generic UD3
DefaultParent Generic UD12
DefCurrency Generic UD2
Description Generic UD13
HoldingCompany Generic UD10
IsICP Generic UD4
Level1 Generic UD14
Level2 Generic UD15
Level3 Generic UD16
MemberName Generic ACCOUNT
SecurityAsPartner Generic UD11
SecurityClass Generic UD6
UserDefined1 Generic UD7
UserDefined2 Generic UD8
UserDefined3 Generic UD9

Please note that this is just an example, and the custom application used for this process can use other dimensions as required – you are not locked into this definition. Below is a partial screen shot of the custom target application definition in FDMEE. (Please note that ACCOUNT is a required dimension in a custom application.)

Continuation-

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s