FDMEE/Data Management – All data types with auto-increment of line item – Part 1

Recently a new load method was added to Data Management in EPM Cloud, there was no mention of it in the announcements and new features monthly updates document so I thought I would put together a post to look at the functionality.

The new load method is called “All data types in Planning File Format” which may be new for Data Management but the core functionality has been available in the Outline Load Utility since 11.1.2.0 of on-premise planning.

Update: The functionality is also available in on-premise FDMEE from 11.1.2.4.220

The naming convention has changed in the cloud and on-premise since I originally wrote this post, "All data types in Planning File Format" is now known as "All data types with auto-increment of line item"

The documentation provides the following information:

“You can include line item detail using a LINEITEM flag in the data load file to perform incremental data loads for a child of the data load dimension based on unique driver dimension identifiers to a Oracle Hyperion Planning application. This load method specifies that data should be overwritten if a row with the specified unique identifiers already exists on the form. If the row does not exist, data is entered as long as enough child members exist under the data load dimension parent member.”

I must admit that in the past when I first read the same information in the planning documentation it wasn't clear to me how the functionality worked.

It looks like the above statement in the cloud documentation has been copied from on-premise and is a little misleading as in Data Management you don’t have to include the flag in the source file because it can be handled by data load mappings.

Before jumping into the cloud I thought it was worth covering an example with the on-premise Outline Load Utility because behind the scenes Data Management will be using the OLU.

As usual I am going to try and keep it as simple as possible and in my example I am going to load the following set of employee benefits data.

Using the LINEITEM flag method with the OLU it is possible to load the data to child members of a defined parent without having to include each member in the file, so say you need to load data to placeholders this method should make it much simpler.

You can also define unique identifiers for the data so in the above example I am going to set the identifiers as Grade and Benefit Type, this means if there is data in the source file which matches data in the planning application against both the identifiers the data will be overwritten, if not the data will be loaded against the next available child member where no data exists for the given point of view.

It should hopefully become clearer after going through the example.

I have the following placeholder members in the Account dimension where the data will be loaded to, the Account dimension will be set as the data load dimension and the member “Total Benefits” will be set as the parent in the LINEITEM flag.

The data in the source file will be loaded against the following matching members in the Property dimension, these will be defined as the driver members.

The members are a combination of Smart List, Date and numeric data types.

I created a form to display the data after it has been loaded.

Before creating the source file, there are data load settings that need to be defined within Data Load Administration in the planning application.

The Data Load Dimension is set as Account and the parent member where the data will be loaded to is set as “Total Benefits”

The Driver Dimension is set as Property and the members that match the source data are defined as Benefit Type, Grade, Start Date, Active and Value.

The Unique Identifiers in the property dimension are defined as Benefit Type and Grade.

Now on to creating the source file, if you have ever used the OLU to load data you will know that the source file will need to include the data load dimension member which in this case will the line item flag, driver members, cube name and the point of view containing the remaining members to load the data to.

The format for the line item flag is:

<LINEITEM(“Data Load Dimension Parent Member”)>

So based on the data set that was shown earlier the source file would look something like:

You may ask why does the line item flag need to be on every record when it could just be included in the parameters when calling the OLU, this would make sense if loading data to children of only one member but it is possible to load to multiple members so it needs to be included in the source file.

The final step is to load the data file using the OLU and the parameters are the same as loading any type of data file.

The parameter definitions are available in the documentation but in summary:

/A: = Application name
/U: = Planning application administrator username
/D: = Data load dimension
/M: = Generate data load fields from header record in file.
/I: = Source file
/L: = Log file
/X: = Error file

You could also include the -f: parameter to set the location of an encrypted password file to remove the requirement of entering the password manually at runtime.

After running the script the output log should confirm the status of the data load.

Planning Outline data store load process finished. 4 data records were read, 4 data records were processed, 4 were accepted for loading (verify actual load with Essbase log files), 0 were rejected.

In my example four records were successfully loaded which is what I was hoping for.

Opening the form I created earlier confirms the data has been loaded correctly.

As no data previously existed for the POV the data was loaded to the first four children of “Total Benefits” and the unique identifier members would not apply in this case.

Let us load a record of data for the same POV and to matching unique identifiers, the unique identifier has been defined as a combination of members Grade and Benefit Type 

As matching data values already exist for “Grade 1” and “Health Insurance” under “Total Benefits”, this means the data should be updated instead of data being loaded to the next available child member.

The data has been updated where the identifier data values match and in this case the Active member data has changed from Yes to No.

Now let us load a new record of data where data values don’t match for the identifier members.

In the above example there is currently no matching data values of “Grade 3” and “Health Insurance” so the data should be loaded to the next available child member of “Total Benefits” where no data exists for that POV.

The data has been loaded against next available member which is “Benefit 5” as no data previously existed for the given POV.

So what happens when you try to load data and there are no available members left.

All five child members of “Total Benefits” have data against the above POV and as there is no matching data for the unique identifier combination the load fails with the following messages.

There is no uniquely identifying child member available for this member defined in Data Load Dimension Parent. Add more child members if needed.: 
,Plan1,"Jan,No Year,Forecast,Working,110,P_000",Grade 3,Car Allowance,01-05-2017,Yes,18000

Outline data store load process finished. 1 data record was read, 1 data record was processed, 0 were accepted for loading (verify actual load with Essbase log files), 1 was rejected.

At least the log provides exactly what the issue is and how to resolve.

I am going to leave it there for this post and in the next part I will look at how the same functionality has been built into FDMEE/Data Management and go through similar examples.