Sunday, 5 February 2017

FDMEE Hybrid update - Part 2

In the last part I took at look at new hybrid functionality added to FDMEE in 11.1.2.4.210 to allow integration between on-premise FDMEE and Oracle FCCS.

The post covered in detail exporting data from an on-premise planning application and loading into a target FCCS application, the aim today is to reverse that process and extract data from a FCCS application and then load into an on-premise planning application.

I am going to assume you have read through the previous post and the FCCS application has been added as a target application.


Technically in this scenario it is the going to be the source but it does need to exist as a target application.

Once again I am going to keep things as simple as possible and the goal is to export data from the intersection shown in the following form:


To start with an import format is created with the source being the FCCS application and the target set as a planning application.


Where applicable the FCCS dimensions are then mapped to the planning application dimensions.


No surprises with the location and the import format is selected.


On to the load rule and there is a slight difference between when FCCS is the source and when it is the target.

If you take a look at when loading from on-premise planning to FCCS the target Essbase database is called “Cube”.


When extracting from a FCCS application the source Essbase database is called “Plan Type”, like I said in my previous post I hope that a decision is made and implemented where only one naming convention is used to remove the confusion.


Within the load rule source filters are added to define intersection to extract data from the FCCS application.


At this point it is a good opportunity to go back to the target application and click “Refresh Members”


I went into detail in the last post on how this works but basically it will extract the current list of members for each dimension from the target FCCS application and push them into FDMEE, this will allow target members to be populated in the member selector.


I did notice that after refreshing members the currency dimension member list was still blank so they had to be entered manually into the source filter definition.


It is worth mentioning that the method that FDMEE uses to extract data from a BSO database is the Essbase calculation command DATAEXPORT, currently there is no way in FDMEE to change any of the options being used with the DATAEXPORT command so there are a number of limitations, an example being if you need to extract data from any sparse dynamic calc members then it is not possible to do so at the moment.

That leads me on to the target options in the load rule, there is the option to set different types of load methods.


The values define whether to load numeric data using an Essbase load rule with a file or directly with SQL, the “All Data Types” option is for loading text data with the Outline Load Utility.


Please note when the source is a EPM cloud application the setting makes no difference to the way it extracts data, it only controls the way FDMEE will load data from the FDMEE repository to the target on-premise application.

Currently only numeric data can be extracted from a source EPM application so there is not much point in changing the load method from the default, maybe extracting all data types will be available in a future release.

Next the data load mappings are defined which I don’t need to cover as I simply created one to one explicit mapping against the members that data was being extracted.


On to running the rule and to begin with I am only selecting to import from the source FCCS application.


The on-premise FDMEE process details show that the data was extracted from the FCCS application, the exported file is then loaded to the FDMEE repository and mapped.



There is an equivalent process within FCCS data management that shows the step of extracting data from the application.



At this point in the on-premise FDMEE workbench the extracted data has been imported and mappings applied.


So what is going on behind the scenes to extract the data from the FCCS application, well this is where a REST comes into play.

A POST request against a data management REST resource.

The resource URL is the same one that it used in most of the interactions with data management in the cloud.

https://<cloud_instance>/aif/rest/jobs

It is the JSON input payload of the request that defines what data to extract.


The jobName value is the name of the FCCS application.
The jobType value for a data export is always “DIRECTEXPORT
The planName is the plan type (cube) name.

The exportDataScript value contains an Essbase calculation script using the DATAEXPORT command, the FIX in the calc script will be generated from the source filters in the data load rule.

If the source was an ASO database, then MDX would be generated for the exportDataScript property value.

In my example the formatted calc script would look like:

SET DataExportOptions
{
DATAEXPORTCOLFORMAT ON;
DATAEXPORTDIMHEADER ON;
DATAEXPORTDRYRUN OFF;
DataExportRelationalFile OFF;
DataExportNonExistingBlocks OFF;
DataExportLevel ALL;
DATAEXPORTCOLHEADER "Account";
DATAEXPORTOVERWRITEFILE ON;
DataExportDynamicCalc ON;
};

FIX ("Cash","Accounts Receivable Trade","Other Receivables","FCCS_Entity Input","Entity Currency","FCCS_Data Input","E01_101_1110","FCCS_No Intercompany","FCCS_OpeningBalance","FCCS_No Multi-GAAP","Jan","No Product","Actual","FCCS_Periodic","FY17")
DATAEXPORT "File" "," PLACE_HOLDER_DATA_FILE_NAME.txt "  "#";
ENDFIX

The JSON response includes details on the job that is being executed including the status and ID.


The process is then repeatedly checked with a REST call using the job ID in the format:

https://<cloud_instance>/aif/rest/jobs/<jobID>



In the original REST request the DATAEXPORT filename was set as “PLACE_HOLDER_DATA_FILE_NAME.txt”, when the script is run in the cloud instance the filename is updated to the format:

<target_app_name>_<cloud_dm_process_id>.dat

So in my example because the process id in the cloud DM was 45 and the target application name is called FCCS the filename generated is FCCS_45.dat

One the export has completed the exported file is moved into the lcm folder ready to be downloaded.


Now one of the migration REST resources is called to download the file, the URL format for the resource is

https://<cloud_instance>/interop/rest/11.1.2.3.600/applicationsnapshots/<filename>/contents



The file is downloaded to the on-premise FDMEE data folder and the file is renamed using the format

<target_app_name>_<fdmee_process_id>.pbcs.dat



Even though this is FCCS the same process as if it was PBCS is being followed hence the filename containing pbcs, this is also noticeably in the process logs with many references to PBCS, maybe the future was never considered when first developed.

Next the file is deleted from the cloud instance using the same URL but with a DELETE method.


From this point standard FDMEE functionality is resumed to load the file into the FDMEE repository and then map.

Data can then be exported from FDMEE and loaded into the target application.


For good measure I created a form to show the data had been successfully loaded to the on-premise planning application.


So that concludes this hybrid journey from FCCS to on-premise.

Monday, 23 January 2017

FDMEE Hybrid update - Part 1

The 11.1.2.4.210 patch set update for FDMEE adds new Hybrid functionality to allow integration with either Oracle FCCS or Fusion Cloud, the readme for the PSU has the following information:

“FDMEE can be used as a primary gateway to integrate data between on-premise and the cloud based Financial Consolidation and Close Cloud (FCCS) and the Fusion GL Cloud Balances applications. This feature allows EPM customers to adapt FCCS and Fusion GL Cloud Balances deployments into their existing EPM portfolio. You might use the integration to import data from existing on-premise ERP applications, or synchronize data between FCCS applications or Fusion GL Cloud Balances and on-premise applications.”

FDMEE 11.1.2.4.210 now supports these type of hybrid integrations:
  • On-premise ERP applications from supported source with Oracle Cloud
  • On-premise EPM applications with Oracle Cloud
  • Oracle Cloud with on-premise EPM applications
  • Oracle Cloud with EBS/Peoplesoft GL
  • Oracle Cloud with on-premise external sources using custom application
  • Oracle Fusion Financials Cloud (GL) with on-premise EPM application
  • Oracle Financial Consolidation and Close Cloud applications with on-premise applications
In this post I am going to concentrate on the area of integration between on-premise and FCCS, in the past I wrote a three-part series looking at FDMEE hybrid functionality which was focused on PBCS.

If you have not already read my hybrid posts then I recommend taking a look, the first part is a must in order to get going with hybrid integrations.

Part 1 - Necessary configuration to get up and running with the hybrid functionality.

Part 2 - Loading data from an on-premise EPM application to a PBCS application and looking at what happens behind the scenes with the REST API.

Part 3 - Exporting data from a PBCS application and loading it into an on-premise EPM application with focus on what is happening with the REST API.

The aim for today is to export data from an on-premise planning application and load that into a FCCS application.

As usual I am going to try and simple as simple as possible and I have created a planning form to show the data which will be exported.


To be able to get anywhere the FCSS application has to be added as a target.


After selecting the cloud option credentials are entered.


From 11.1.2.4.210 there is a new option of “Consolidation” when selecting the target application.


As long as the FCCS application is available as a target in the cloud Data Management then it should be displayed.


The application name is returned by making a call to a REST call, a GET request is made to the following URL:

https://<cloud_instance>/aif/rest/V1/applications

The response contains the application name.


Once the application has been selected then REST comes into play again to return the dimension information

A GET request is made to:

https://<cloud_instance>/aif/rest/V1/applications/<app_name>

The response contains information about the dimensions in the application.


Now that the dimensions are known the dimension details can be populated in FDMEE.


You will notice that in the target application summary there is an option to “Refresh Metadata” and “Refresh Members”.


I will get to refreshing members later but say you add a new dimension in the target FCCS application then you would want this to be synchronized back to on-premise FDMEE, this option refreshes data management in the cloud and then pushes this down to FDMEE.

Behind the scenes it is all done again with REST, if you click the refresh metadata button then an FDMEE process will be executed.

In the on-premise process details you can the steps that have been carried out.



In data management in the cloud you will see a process has been run to extract the FCCS metadata and update the target application.


To update the metadata in data management a post request is made to a REST resource.


Once this process has been successfully completed then the same REST resource when adding the target application is called again.

https://<cloud_instance>/aif/rest/V1/applications/<app_name>

This will extract the dimension information from DM in the cloud and update the target on-premise FDMEE application.

In the target FCCS application options they contain the cloud credentials and default FCCS system members which are used when loading to the FCCS application.


The “Load Type” property has two possible values.


For today I am only going to be covering the standard “Data” load type, possibly in the future I will go over loading journals.

These load type values can be overridden at data load rule level.

From this point it is the same process you would follow in FDMEE to load to a target on-premise EPM application.

An import format is created setting the source as the on-premise planning application and the target the FCCS application.


The source planning dimensions are then mapped to the target FCCS dimensions.


A location is created and the newly created import format is selected.


By default, the FCCS currency member “Entity Currency” is automatically added for the functional currency.


The same goes for the Consolidation system member “FCCS_Entity Input” which is generated for “Data Value


On to the data load rule, the FCCS scenario to load to is selected, the scenarios already match to what I had configured in the category mapping.

The source and target essbase cubes were selected, I hope that soon they are just going to be called cubes instead of causing confusion with cube and plan type.


Next filters were added to define the source intersection to extract data from, this is defined to match the data in the form that I showed earlier.


In the target options of the rule there is the option to override the application level load type.


Just like at the target application level the value can be set to either “Data” or “Journal”.

Moving on to the mappings, for the source planning application members there is an option to refresh members.


For the target FCCS application members there is no button available to refresh members and initially the member list view will be empty.


The “Refresh Values” is not selectable in the mapping UI.


This is because currently it is not possible to refresh members at a dimension level for cloud application and it has to be done at target application level.


Selecting “Refresh Members” will launch a new process.

In process details there will be a “Refresh Members From Cloud” process.



The steps first involve refreshing the members from FCCS to data management in the cloud

There is an equivalent process in data management.



Once again the refreshing member in the FCCS data management cloud instance is achieved using a REST resource.


Once the process has completed successfully the member information is pushed down from data management to on-premise FDMEE using REST.

A GET request is made for each dimension in the target application using the following URL format.

https://<cloud_instance>/aif/rest/V1/applications/<app_name>/dimension/<dimension>


After all dimensions have been processed the members will be available in the FDMEE mappings member selector.


There will be automatically created mappings for the FCCS system members in the “Data Source”, “Movement” and “Multi-GAAP” ” (if enabled) dimensions, these member definitions originate from the target application options which were shown earlier.


Now the rule can be run and I will start off by only running the import in the workbench.


The steps taken are exactly the same as running an on-premise EPM to EPM integration.

The data will be extracted from the source Essbase application based on the filters in the load rule to a text file, an Essbase DATAEXPORT calculation script is generated to export the data which means currently it is only possible to extract numerical data from a planning application, maybe a future release will provide the ability to extract all data types.

The export file is then loaded into the FDMEE relational database and source members mapped to target members.

So now on to the export.


Depending on the amount of data it is probably best to run in offline mode.


The export completed successfully and process details give a very high level summary to the steps undertaken.



So what is happening behind the scenes, first of all data is extracted from the FDMEE relational database to a text file, this can be seen in the process log.

INFO  [AIF]: Creating data file: \\fileshare\EPMSHARE\FDMEE\outbox\FCCS_1869.dat
INFO  [AIF]: Data file creation complete


What is interesting in the log is details on the method that will be used to load data to FCCS.

DEBUG [AIF]: Overrode info.loadMethod for the available mode: OLU

So by default the way data is loaded with FCCS is by using the Outline Load Utility, this differs from loading to PBCS where it is possible to select “Numeric Data only – File” in the FDMEE data load rule which means data will be loaded directly to the Essbase database using a load rule

Alternatively, for PBCS the “All Data Types” load method can be selected which means the Outline Load Utility will be used to load data through the planning layer

If you open the exported file before it is loaded to FCCS you can see that it is the format required for the OLU.


The next steps in the log show the exported file is then uploaded.

INFO  [AIF]: Uploading data file to PBCS: \\fileshare\EPMSHARE\FDMEE\outbox\FCCS_1869.dat
INFO  [AIF]: Completed data file upload to PBCS.


It looks like the same code is used for PBCS as even though it is being uploaded to FCCS it is logged as PBCS.

The file is uploaded using a REST resource which I covered in a previous post.

Another REST resource is called upon to load data from the uploaded file, an example being:


The file is then loaded to the FCCS application using the outline load utility.

Outline data store load process finished. 3 data records were read, 4 data records were processed, 4 were accepted for loading (verify actual load with Essbase log files), 0 were rejected.
INFO  [AIF]: Number of rows loaded: 3, Number of rows rejected: 0


It is also worth understanding what happens if invalid rows of data are loaded, as a test I added another account to the source filter in the data load rule and mapped it to mapped it to a member that does not exist in the FCCS application.


The account member “Securities” does not exist in the target application and running the export causing the FDMEE process to fail.



Looking at the process log shows that the correctly mapped data did actually load and the new incorrectly mapped record of data was rejected.

Fetch of Driver Member "Securities" failed.
Outline data store load process finished. 4 data records were read, 5 data records were processed, 4 were accepted for loading (verify actual load with Essbase log files), 1 was rejected.
INFO  [AIF]: Number of rows loaded: 3, Number of rows rejected: 1


The full details of the record that was rejected is not displayed in the log but it does contain the invalid member.

Anyway I created a form in FCCS to confirm the data had loaded successfully and test out drill through.


If create drill regions have been enabled in the data load rule then you should be able to select “Drill Through to source”.


This opens a new browser window which redirects to the on-premise instance of workspace passing in the drill through details.

Once authenticated the FDMEE Drill Through Summary page will be displayed.


I am going to leave it there for today and in the next part I will reverse the process and extract data from an FCCS application and load into an on-premise EPM application.