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"
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”
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"
Very useful blog entry. I just came across the same issue with trying to a load a payroll file into a PBCS Workforce plan type and was trying to figure out how to handle the text and smart list accounts across multiple columns.
ReplyDeleteThanks for putting this together.
Hi John,
ReplyDeleteCan we use load option as Add Data and still able to drill through?? When I tried it displayed a blank page.