Over the past six months there have been a number of changes in the way and the type of data you can load through Data Management in EPM Cloud, I am only referring to the cloud as these enhancements have not yet made it down to on-premise FDMEE.
Update 23rd December 2016 - All the functionality in this post is now available on-premise in FDMEE PSU 11.1.2.4.210
In the PBCS 16.07 update document there was the following new feature:
“Data Management added a new All Data Types Data Load method which enables you to load numbers, text, Smart lists, and date.”
In the 16.09 release there was:
“The text-based data load feature enables users to load text data, dates, and smart lists to applications that support these data types.”
You tell me what the difference is between 16.07 and 16.09 because they look the same to me :)
In the 16.10 release there was:
“Data Management now supports a multi-column load of numeric data for any dimension. Prior to this update, Data Management supported only the loading of multi-period column data. In addition, a multi-period load using the header record is now available.”
Basically I have covered the above functionality in a previous blog which you can read all about here
The previous post was aimed at loading text data but the same logic can be applied for numeric data, the only difference would be the file type selection in the import format.
If you look at the import format in the 16.07 release you will see that the “All Data Type” was introduced for fixed, delimited and multi column source files.
At that point there was “Multi Period” available for numeric data where you could only specify period columns in the import format mapping, fast forward to the 16.10 release and you will see that it was replaced with “Multi Column – Numeric Data”.
Currently in the on-premise 11.1.2.4.200 FDMEE version you only have the following file type options:
I am sure this will be change in the future, maybe when .210 patch finally lands.
Update 23rd December 2016: Functionality now available on-premise with FDMEE 11.1.2.4.210
Import format file type options from FDMEE 11.1.2.4.210:
Moving on, there is now another new way of loading data which arrived in the 16.12 cloud release and FDMEE 11.1.2.4.210:
EPM Cloud update document:
“Data Management now supports Oracle Financial Consolidation and Close Cloud “Period” dimensions as columns in a data file. If you have data for multiple periods in a single file, then you can include the year and period on each row of the data file that gets loaded to the target application.”
FDMEE 11.1.2.4.210 readme:
"Data files can include data for multiple periods. To support this, columns for period, year and period number are available to add in the import format."
Notice that the functionality in the EPM Cloud update document is only specified for FCCS, how about PBCS? Well I will get on to that later.
So now it should be possible to specify the period and year in the rows of a source data file, to test out this new piece of functionality I will go through a simple data loading example.
I am going to start out with the following source file which has a column for period/year and multiple periods in the rows.
The objective is to load this file to Data Management, map the data to valid members and then load the data to a target FCCS application.
I have created a form to verify whether the data has been correctly loaded.
In order to map the period/year in the source file to the target application I have created source period mappings and assigned them to a calendar which will be selected in the data load rule.
On to the import format and before the 12.16 release there were the following options available when selecting the Add button.
Now there is a new drop-down option of “Source Period Row” where Year, Period or Period Number can be added.
These will then be added to the import format grid and the field number can be mapped to the source file.
In the data load rule the import format was select, the source file was uploaded and selected, the period mapping type was set to explicit and the calendar that was created earlier selected.
I am not going into the data load mappings as they were straight forward explicit mappings for account and entity members contained in the source file.
To load the data, you execute the rule from the data load rule screen as the workbench is currently fixed by the period/year that is set in the POV.
Even though my source file has data from January to December 2017 I am only going to select to load from January to June 2017.
Once the rule has been executed process details confirms the load from source to target was successful.
In the process steps I have no idea why the months are all out of order because if you take a look in the process log you will see they are processed in the correct order.
In the log you can also see that the rows that are not in between the start and end period are rejected in the data load.
In the target options of the data load rule I had selected not to purge the data file that is created by Data Management and then loaded into the target FCCS application.
It is possible to download the file from the outbox to view the format of the file that is loaded to the target application.
If you have ever been involved with on-premise planning, then you will see that the file is in the outline load utility format and viewing the process log that it is using the utility to load the data.
The form I created earlier is now correctly displaying the data that was loaded.
Using this new method certainly makes life much easier to load data across multiple periods and years.
In the 16.12 cloud update document, it was stated that this new functionality was only available for FCCS but I was interested to know if it is available in PBCS.
I created an import format against a PBCS application and the “Source Period Row” option was available.
As the option is available then this would suggest it is possible to load from a source file containing periods and years in the rows.
I had to test out whether the process would be successfully against a PBCS application.
Once again I started out with a simple source file spanning multiple periods and years.
The period/year were added and mapped in the import format.
I am not going to show them but I created source period mappings, a location and data load mappings.
The data load rule was set up in the same as the previous example.
The rule was executed against with a start and end period to match the source file contents.
The full process of importing, mapping and exporting to the target was successful.
For demonstration purposes I created a form to show that the data was correctly loaded to the target PBCS application.
So even though PBCS was not mentioned in the update documentation the functionality is there and it is possible to load from a source file where the periods and years are defined in the rows.
Update: This functionality can be used against EPM cloud and on-premise.
Update 23rd December 2016 - All the functionality in this post is now available on-premise in FDMEE PSU 11.1.2.4.210
In the PBCS 16.07 update document there was the following new feature:
“Data Management added a new All Data Types Data Load method which enables you to load numbers, text, Smart lists, and date.”
In the 16.09 release there was:
“The text-based data load feature enables users to load text data, dates, and smart lists to applications that support these data types.”
You tell me what the difference is between 16.07 and 16.09 because they look the same to me :)
In the 16.10 release there was:
“Data Management now supports a multi-column load of numeric data for any dimension. Prior to this update, Data Management supported only the loading of multi-period column data. In addition, a multi-period load using the header record is now available.”
Basically I have covered the above functionality in a previous blog which you can read all about here
The previous post was aimed at loading text data but the same logic can be applied for numeric data, the only difference would be the file type selection in the import format.
If you look at the import format in the 16.07 release you will see that the “All Data Type” was introduced for fixed, delimited and multi column source files.
At that point there was “Multi Period” available for numeric data where you could only specify period columns in the import format mapping, fast forward to the 16.10 release and you will see that it was replaced with “Multi Column – Numeric Data”.
Currently in the on-premise 11.1.2.4.200 FDMEE version you only have the following file type options:
I am sure this will be change in the future, maybe when .210 patch finally lands.
Update 23rd December 2016: Functionality now available on-premise with FDMEE 11.1.2.4.210
Import format file type options from FDMEE 11.1.2.4.210:
Moving on, there is now another new way of loading data which arrived in the 16.12 cloud release and FDMEE 11.1.2.4.210:
EPM Cloud update document:
“Data Management now supports Oracle Financial Consolidation and Close Cloud “Period” dimensions as columns in a data file. If you have data for multiple periods in a single file, then you can include the year and period on each row of the data file that gets loaded to the target application.”
FDMEE 11.1.2.4.210 readme:
"Data files can include data for multiple periods. To support this, columns for period, year and period number are available to add in the import format."
Notice that the functionality in the EPM Cloud update document is only specified for FCCS, how about PBCS? Well I will get on to that later.
So now it should be possible to specify the period and year in the rows of a source data file, to test out this new piece of functionality I will go through a simple data loading example.
I am going to start out with the following source file which has a column for period/year and multiple periods in the rows.
The objective is to load this file to Data Management, map the data to valid members and then load the data to a target FCCS application.
I have created a form to verify whether the data has been correctly loaded.
In order to map the period/year in the source file to the target application I have created source period mappings and assigned them to a calendar which will be selected in the data load rule.
On to the import format and before the 12.16 release there were the following options available when selecting the Add button.
Now there is a new drop-down option of “Source Period Row” where Year, Period or Period Number can be added.
These will then be added to the import format grid and the field number can be mapped to the source file.
In the data load rule the import format was select, the source file was uploaded and selected, the period mapping type was set to explicit and the calendar that was created earlier selected.
To load the data, you execute the rule from the data load rule screen as the workbench is currently fixed by the period/year that is set in the POV.
Even though my source file has data from January to December 2017 I am only going to select to load from January to June 2017.
Once the rule has been executed process details confirms the load from source to target was successful.
In the process steps I have no idea why the months are all out of order because if you take a look in the process log you will see they are processed in the correct order.
In the log you can also see that the rows that are not in between the start and end period are rejected in the data load.
In the target options of the data load rule I had selected not to purge the data file that is created by Data Management and then loaded into the target FCCS application.
It is possible to download the file from the outbox to view the format of the file that is loaded to the target application.
If you have ever been involved with on-premise planning, then you will see that the file is in the outline load utility format and viewing the process log that it is using the utility to load the data.
The form I created earlier is now correctly displaying the data that was loaded.
Using this new method certainly makes life much easier to load data across multiple periods and years.
In the 16.12 cloud update document, it was stated that this new functionality was only available for FCCS but I was interested to know if it is available in PBCS.
I created an import format against a PBCS application and the “Source Period Row” option was available.
As the option is available then this would suggest it is possible to load from a source file containing periods and years in the rows.
I had to test out whether the process would be successfully against a PBCS application.
Once again I started out with a simple source file spanning multiple periods and years.
The period/year were added and mapped in the import format.
I am not going to show them but I created source period mappings, a location and data load mappings.
The data load rule was set up in the same as the previous example.
The rule was executed against with a start and end period to match the source file contents.
The full process of importing, mapping and exporting to the target was successful.
For demonstration purposes I created a form to show that the data was correctly loaded to the target PBCS application.
So even though PBCS was not mentioned in the update documentation the functionality is there and it is possible to load from a source file where the periods and years are defined in the rows.
Update: This functionality can be used against EPM cloud and on-premise.
John,
ReplyDeleteThanks for the update great job! I was testing the new functionality which allows you to load data that has the period and Year in rows. I noticed the GL Year field in source mapping doesn't allow FY16, FY17 etc. I was testing with an export from ESSBASE and it has the FY for the years. I can get around it by using a import script on the Year dimension, but is there a better approach?
Thanks,
Hi John,
ReplyDeleteThanks for the post. Your blogs are always interesting. We recently moved to PBCS and trying to load the forecast data. In you post and in the DM guide it says to map the GL Year , does that mean we have to create these maps every year ?
Yes
ReplyDeleteHi John, Thanks this helped a lot!
ReplyDeleteIn the blog, GL Year Mapping 2016,2017 etc ..It is always numeric. What if we get fiscal year in column "FY16, FY17 etc?
..