Loading non-numeric data to planning using FDMEE

Before I start it is worth mentioning the functionality is currently only available in Oracle EPM Cloud though it is expected on-premise in the next FDMEE patch, the functionality should be similar between cloud and on-premise so this post should be valid for both.

Update December 2016:
Functionality is now available in on-premise FDMEE from 11.1.2.4.210

One of the many new features included in the 16.07 PBCS release is a new “All Data Types Data Load” method which allows the loading of numeric, text, Smart List and date data through data management (FDMEE).

Before this release text data had to be loaded through the Planning user interface or with on-premise the options are the UI, outline load utility or ODI.

In this post I am going to go through some extremely simple examples of how to load all data types data using data management.

In the planning application I have created a few new account members to load text, Smart List and date data to:

I have also created a form to confirm whether the data for the different data types has been correctly loaded.

So let’s get on with it, if you go into application options for the target application you will see a new property called
“Load method”.

Prior to this release in PBCS there were no load method options available as data was loaded directly to Essbase, now there are the options of “File” which is the same method as before where data is loaded directly to Essbase and “HPL” which loads data through the planning layer so allows all data types.

Behind the scenes to achieve the loading of all data types data the outline load utility is being utilised which is clearly visible in the process logs.

Anyway I selected the load method as “HPL”.

What is a little concerning is that the load method is set at target application level and I couldn’t see if this could be overridden anywhere else, it would make more sense to me if it could be set a data load level as for performance reasons you would want to load the large bulk of numeric data directly to Essbase and the remaining data types date through the planning layer, currently it looks like you are forced down one route or maybe I have missed something, I will update the post if I have.
(Can be overridden in load rule options for on-premise FDMEE from 11.1.2.4.210)

Update October 2016

Load method has now been updated to following options:

To load text data, select "All data types with security"

You will also notice there is another new property called “Date Format” which sets the default format when loading date type data.

Update December 2016

The options available for "Load Method" in on-premise FDMEE 11.1.2.4.210+ are

These options are also available for on-premise in the load rule options.

On to the import format and there are three new file type options available.

"Delimited – All Data Type" - Single column numeric/non-numeric delimited data file
"Fixed - All Data Type" - Single column numeric/non-numeric fixed length data file
"Multi Column – All Data Type" - Multiple column numeric/non-numeric delimited data file

In my first example I will be using the delimited single data column option.

As I am keeping things as simple as possible my source file has two columns with the account all data type members in the first column and the data in the second column.

In the import format mappings section you will notice there is now a target of “Data” as opposed to “Amount” which is available when loading numeric data.

The account and data columns were mapped with the remaining dimensions being hard coded to a single member.

There are no differences when creating a location.

There is no need to go into data load mappings as I have just created them with like for like mappings.

There are no surprises when creating the data load rule.

In the “Target Options” tab there is the option to override the date format to the one set in the target application options but not an option to override the load method.

The target options for on-premise FDMEE from 11.1.2.4.210 allow selection of the "Load Method"

Time to import the data file and load to the planning application.

The workbench now displays the Source/Target data columns which hold the all data types data.

As there were no errors with the process the Planning web form can be viewed to confirm the data has been correctly loaded.

All good, so on to the next example where the data is across columns and contains a header row.

This time the file type is set to “Multi Column – All Data Type” in the import format.

The import format mappings require an additional step when using multi column source data, selecting “Add Expression” has a new expression type called “Driver”.

After selecting “Driver” a new window is opened where additional information can be added to define the format of the source data.

In my source the columns containing the all data type data are account members so “Account” was selected as the driver dimension.

As my source file has a header in the first row I set the header row to 1 and there is no need to select the members as they are contained in the header.

The data in the source is between columns 1 and 4 so the format 1,4 defines this range, the examples in the expression window provide other formats that can be used.

The expression mapping window does not have to be used and if preferred the information can be directly entered into the expression field.

The rest of the setup is the same as my first example so no need to go through it again and can move on to loading the data.

No problems and the data is loaded in the workbench in exactly the same format as my original example.

The web form confirms all data type data has been loaded successfully.

On to my final example and the only difference this time is the source data file does not contain a header row.

The import format mapping expression only requires a slight modification.

This time the header row is not set and the members in the source are selected.

Straight on to the data load and all looks good again.

Final check of the web form just to be sure.

I did have a look at whether the non-numeric data could be exported by creating a custom application but once you get to the import format there is no “Data” column available and it reverts back to “Amount”

Just to be sure I checked in the workbench and only the numeric values that are stored in the Essbase database were exported.

Maybe exporting non-numeric type data will be available in a future release.


Update October 2016

It is now possible to load multi column numeric data  using the same methods as explained in this post, while creating an import format the file type selected would be "Multi Column - Numeric Data"

FDMEE and DRM integration - Part 4

Continuing on with the FDMEE and DRM integration series, just to recap at the end of the last part metadata had been extracted from EBS, loaded to FDMEE and then pushed into DRM.

I am going to return again to loading metadata from FDMEE to DRM because I want to cover the use of the blender option in the DRM import profile as it will help with the process to push mappings into FDMEE.

So once again going back to my simplistic diagram, the flow shown with green arrows will be the area I will be focusing on in this post.

If you open up an import profile in the target tab there is the option to set a blender profile.

Using a blender will basically allow the imported dimensions from FDMEE to be merge into an existing DRM version.

As part of the FDMEE template there is a blender profile named “FDMEE Blend” which has already been preconfigured.

By selecting this option once the import form FDMEE has completed the blender profile will be called.

The import profiles that are part of the FDMEE template also have added parameters.

The "LOADID" parameter is used when manually running the import profile, when running from FDMEE the load id will be passed in.

The parameter defines which load id to use when extracting information from the FDMEE tables, I am going to be running the import profile manually at first so I have set it to value which was part of a previous load to DRM just so I know it has metadata against it.

The “BLEND_TARGET” parameter will be passed to the blender if the import profile is run manually.

The parameter has value of “FDMEE_MAP_VERSION”, this relates to a DRM version variable which is used when mappings are extracted from DRM and loaded to FDMEE.

This variable needs to be assigned to the version you wish to extract mappings from, I set the variable to existing version which contains a planning application entity dimension.

If you take a look in the blender “FDMEE Blend” you can see that the target version is picked up from the “BLEND_TARGET” parameter so it will be using “FDMEE_MAP_VERSION” as the target

The Source version has been left blank as it will pick up that information from the FDMEE “FDMEE AIF_HS_DRM_LOADS” database table.

So let us run the import profile manually from within the DRM web client.

The import was successful and the blender was run, a new version has been created with the naming convention using the format of :
FDMEE_<FDMEE_LOCATION_NAME>

The version contains the hierarchy for member "2999" which I defined in the last part as the starting parent for the metadata rule.

As I mentioned I have already a planning entity dimension which I set the “FDMEE_MAP_VERSION” variable against, before the import profile was executed the looked like the following:

As you can see there is a base member “2999” which is also the top member of the hierarchy imported from FDMEE so let us take a look at the hierarchy after the blender was run.

Now the imported hierarchy has been blended into the existing hierarchy and includes the location name which will be used when loading the mappings back into FDMEE, I will get on to the mapping process shortly but the “SRCKey_Name” property will form the source mapping and the “HP Member” will be the target mapping.

You don’t have to use a blender for use with FDMEE mappings as it can be accomplished using different methods such as action scripts, queries or the compare functionality and manually creating the mapping relationship.

If you going to use the import profile associated with a blender from FDMEE then you will need to update the blender target version from using the “BLEND_TARGET” parameter as even though it will look to run successfully from the FDMEE process and logs it have failed, delving into the details in DRM you will see the following error generated:

DRM-12487: The parameter 'BLEND_TARGET' is not defined.

After FDMEE executes the import process it then calls the DRM Web Service operation “getJobStatus” and looks for a status of done which it interprets as successful.

I feel FDMEE should also call the “getImportResults” operation as this returns more detailed information and highlights that the process completed with a fatal error.

When running the process from DRM it picks up the parameters set in the import profile but unfortunately when this is called from FDMEE using the DRM Web Service operation “startImportByName” these parameters are ignored as it expecting them to be passed in at runtime.

To get around this the target version of the blender can be assigned to an existing version or you can use the “FDMEE_MAP_VERSION” variable to make it more dynamic.

Ok, so now we have the blended hierarchy we are ready to start looking at exporting the mapping from DRM and loading them into FDMEE.

Going back to part 2 in this series in FDMEE I set up the “Export from DRM Profile (Mapping)” property in the DRM options of the target application

This means that FDMEE is going to execute the “FDMEE Planning Map Export” export profile

The version in the profile should be set as the variable “FDMEE_MAP_VERSION” as this is the version that will be passed in from FDMEE.

There is a filter that will export only leaf members and the property “EPM Location Names” is set to the parameter value that is passed in from FDMEE when the export is run.

In the Target tab of the profile you can see which columns are being extracted from DRM and their corresponding column in the FDMEE "TDATAMAP_STG" mapping table.

As I mentioned earlier the "SRCKey_Name" in DRM will be source member mapping and “HP Member” will be the target member mapping.

If the profile provided as part of the FDMEE template does not suit your requirements for mapping functionality it can be updated or a new profile created.

To import the mappings, the “Import from DRM” option can be selected in the Data Load Mapping section in FDMEE.

Just like with other mapping imports there is the option to set the import mode to merge or replace and to validate the target values against the members in the target application.

The process details will provide information on whether the import from DRM was successful or not.

The explicit mappings should now have been loaded and visible in the data load mapping area.

Only explicit mappings are currently supported with the DRM to FDMEE integration.

This is all very nice but what is happening behind the scenes to achieve this, first of all the “startExportByName” DRM Web Service operation is called.

FDMEE generates the input parameters and the export name is defined in the DRM options in FDMEE and in my example is “FDMEE Planning Map Export”

The “fromVersionName” and “toVersionName” is hardcoded to “FDMEE_MAP_VERSION” so this is why it is important to set the version to use this variable in the DRM export profile.

The “runtimeParameters” contains the load id, location name and location key.

I can demonstrate the process by replicating the Web Service operation execution by using a SOAP client, I have removed the header information to make it more readable, if you want to understand the requirements for the SOAP header have a look at my previous posts.

The response contains information like the job ID and current status.

FDMEE checks for a status of “Done” by calling the operation “getJobStatus”.

The export is basically extracting records from the DRM applications database tables and inserting them into the FDMEE mapping staging table “TDATAMAP_STG”

Even though the Load ID and Location Key are passed into the DRM Web Service operation call they are not used in the export profile, once the export has completed FDMEE executes SQL to populate the LOADID, PARTITIONKEY and DIMNAME columns.

If you want to see the SQL that is generated turn up the logging level in FDMEE and then view process details log after importing the mappings.

The mapping information is then taken from the staging table and populated in the core FDMEE mapping table.

When viewing the mappings in FDMEE the "TDATAMAP" table is queried and the results are then displayed in the interface.

Now that the mappings are in place the data can be extracted from the source system, mapped and then loaded to the target application.

So that pretty much completes the FDMEE and DRM integration process, I hope you have found it useful and informative.