FDMEE Hybrid and the REST Part 3

On to the third part in this series looking at the new FDMEE hybrid functionality which was introduced in 11.1.2.4.200

In the first part I went through the necessary configuration to get up and running with the hybrid functionality.

In the last part I covered loading data from on-premise FDMEE to a PBCS application and what happens behind the scenes with the REST API.

In this post I am going to reverse the process and export data from a PBCS application and load it into an on-premise EPM application, once again I will focus on what is actually happening with the REST API.

The assumption is going to be that you have read through the previous posts and understand what hybrid is all about.

The first step would be to add a PBCS application as a target, I am not going to cover what happens with REST as I provided the details in the last post.

Then enter the PBCS credentials

Select the Planning application that should have already been registered in PBCS data management.

If any ASO Plan Types have been registered in PBCS data management then you should see them in the dropdown if you select the type as Essbase

In my example I am going extract data from a PBCS BSO plan type and then load into an on-premise Essbase BSO database though it could be any supported application, the process to achieve this is no different than working totally on-premise so I am will only briefly cover it.

When creating an import format the Source Type will be EPM and the Source will be the PBCS application, Target type is EPM and Target is a registered on-premise EPM application.

A new location is added and the import format that has just been created is selected.

When creating a new data load rule the category and the source plan type to extract data from is selected.

In the source options of the rule I defined the members where I want to extract the data from.

When using the member selector there is currently not an option like with on-premise to refresh members.

This is done through in the “Target Application” area.

I covered in detail in the last post how the members are refreshed from PBCS using the REST API.

Once the members have been refresh the member selector should be populated for each dimension.

Setting up the data load mappings is exactly the same as loading from on-premise to PBCS except this time it is reverse so the source is PBCS members and in my case the target is members in an Essbase database.

Let us move on to the exporting of the data from PBCS and moving that into on-premise.

In the on-premise FDMEE workbench it is exactly as you expect the data is imported into FDMEE, validated and then exported into the Essbase database.

So what is happening behind the scenes as the data does not magically appear on-premise to be loaded into the FDMEE database.

If you look in the process details for PBCS you will just see  a step for the data being extracted from the planning application.

The on-premise FDMEE process details breaks down the steps a little further but does not give away too much unless you start looking into the logs.

The first step “Extract data from Planning application Vision” is where all the new functionality is occurring as once the data arrives to on-premise in the form of a file the standard functionality that we are used to in FDMEE takes over.

To extract the data a POST request against a REST resource in PBCS data management is executed.

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

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

It is the JSON input payload of the request that is the main driver to extract the data.

The jobName value is the name of the PBCS application.
The jobType value for a data export is always “DIRECTEXPORT”
The planName is the plan type name.

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

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 "Period";
DATAEXPORTOVERWRITEFILE ON;
DataExportDynamicCalc OFF;
};

FIX ("1150","1520","2210","1410","1110","110","BaseData","Apr","P_000","Actual","No Version","FY16")
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 name of the text file will be generated on the PBCS side and is a combination of the application name, plan type and process id

So in my example the filename is: Vision_Plan1_92.txt

If extracting from an ASO Essbase database then the export script would contain MDX and here is an example using the REST API to extract ASO data

Anyway back to my original process, once the export has completed the file is automatically renamed and moved into a directory ready to be downloaded, in my example the file is renamed to Vision_92.dat

The file can be viewed from the inbox/output explorer in the planning simplified interface.

The next step is the dat file is downloaded from PBCS to the on-premise FDMEE data directory, the downloading of the file is achieved with the application management REST API

I covered downloading files in a previous post on the PBCS REST API and here is an example of how to do this using a PowerShell script.

The file is downloaded from PBCS and renamed to include pbcs and the originating on-premise process ID, the target location depends on the application root folder configuration in FDMEE.

If the option to purge the data fail is set to Yes then a REST resource is called again using a DELETE request.

The above request deletes the file “Vision_92.dat” from PBCS.

Now the exported file is accessible from on-premise FDMEE the remaining steps in the process are no different than what usually happens when loading from a source file, the file is loaded into the FDMEE database, validated and then loaded to the target Essbase database.

Well that pretty much completes what I intend to cover on FDMEE hybrid functionality for now, if additional functionality is added in the future then no doubt there will be more posts on this subject.

FDMEE Hybrid and the REST Part 2

In the last post I went through the necessary configuration to get up and running with FDMEE hybrid functionality, this was based on 11.1.2.4.200 which is the first patch release of FDMEE to support hybrid.

Now I could have left it as one post because to be honest once the cloud target application details have been added in FDMEE the process for integration and functionality is pretty much the same as with an on-premise application.

As I have been spent a far bit of time recently covering web services I thought it would only be right to go into more detail of what is happening behind scenes with the integration between FDMEE on-premise and cloud.

All the interaction between on-premise and cloud is using a REST API, some of the REST resources have been around for a while which I have covered in previous posts and some of them are new.

If you have not read any of my REST posts then it might be worth having a look at them because I am not going to repeat already covered ground.

In this post I am going to go through loading data from on-premise to a PBCS application and highlight the steps in the process which are using the REST API.

So let us start with adding a target PBCS application which is achieved by selecting the Cloud option.

Next PBCS credentials are entered.

If the required configuration steps that I carried out in the last post have been completed and the FDMEE web application server has https access to the PBCS instance you should be presented with the option to select a planning application.

At present only one application will be returned.

So how was the application name returned, was it just magic like most are happy to accept, no it is at this point REST has joined the party.

A GET request is executed against the following REST resource:

https://<pbcs_instance>/aif/rest/<api_version>/applications

I can demonstrate this by using a REST client browser add-on and the following response is returned from the request:

The JSON formatted response contains the application which is used to populate the drop-down menu.

Once the application is select the application details are then populated in FDMEE.

Once again a REST resource is used to return the dimension information:

https://<pbcs_instance>/aif/rest/<api_version>/applications/<app_name>

The only difference this time is the resource URL requires the application name which we know has already been retrieved and stored.

Executing a GET request returns a whole load of information about the application.

Just like with an on-premise application there are application options that can be set and this is where the cloud credentials are stored if they ever need to be updated.

Now that the PBCS application has been added the steps to load data from a source are exactly the same as if working with an on-premise target.

When creating an import format you would select the target in the same way.

In my example I am loading from a simple flat file but it could be any of the available sources.

It is also possible to have an EPM application as the source.

In the next post I will be covering loading data from a PBCS application to an on-premise EPM application.

Anyway when you get to the stage of data load mapping there is a slight difference between an on-premise and PBCS target application.

With on-premise you have a button to refresh members.

If it is a PBCS application there is currently no button.

I am not sure if this is the way it is meant to be or a bug.

This does not stop you being able to refresh the members from a PBCS application as if you go back into the target application setup there is a button to do this.

The “Refresh Metadata” will refresh the dimension information using the REST resources I have already covered.

The “Refresh Members” will fire off a process and if you look at the process details in the on-premise FDMEE you will see the following process name.

The process steps cover in detail what is happening.

First of all the members are refreshed from the PBCS planning application to PBCS data management, once this is complete the members are retrieved for each dimension from PBCS data management and then imported and inserted into the on-premise FDMEE relational database.

Now if you go back into the on-premise “Data Load Mapping” member selector the members will be available.

So what is happening behind the scenes, well the first stage of refreshing the members from the PBCS planning application to data management is carried out using the following REST resource.

https://<pbcs_instance>/aif/rest/<api_version>/jobs

This time it is a POST method and requires a JSON input payload with a job name and job type.

The job name value is the name of the application and the type is “REFRESH_APP_DIMENSIONS”.

The response will contain information about the process that has been executed in data management.

Process details should have a matching process to the one that is returned in the REST response.

Now the members have been refreshed from PBCS planning to data management the following REST resource is executed for each dimension in the application.

https://<pbcs_instance>/aif/rest/<api_version>/applications/<app_name>/dimension/<dim_name>

A GET request is made like the following example:

The response contains information for each of the members in the requested dimension.

This information is processed and loaded into the on-premise FDMEE relational database so when the member selector is used it will contain the target PBCS applications members.

Moving on to exporting the data from on-premise FDMEE to the PBCS application, in my example the data has already been successfully imported and validated.

Once an export has been initiated and completed the on-promise process details will contain the following information:

And in the PBCS process details

In my target application options I had drill region enabled so this information is created in the target PBCS application.

As an example I have created a form in the PBCS application which contains data loaded from on-premise FDMEE.

Selecting the drill through to source option will open a new tab in workspace and then open a new tab in the browser.

The new tab will connect to on-premise FDMEE and display the drill-through summary.

Authentication is required before the drill through summary is displayed as there is no single sign on in operation.

Nice, data has been loaded from an on-premise source, validated, exported and loaded to a PBCS application with the ability to drill through if required.

Let me walk through what is happening behind the web interface.

First of all data is exported from the FDMEE database and a data file is created in the application outbox directory.

This file is then uploaded to the PBCS inbox directory using the following REST resource

https://server/interop/rest/{api_version}/applicationsnapshot/{applicationSnapshotName}/contents?q={“isLast”:”boolean”,”extDirPath”:”upload_dir”chunkSize”:”sizeinbytes”,”isFirst”:”boolean”}

I covered uploading files using the REST API here and here

Here is an example of using the upload resource with a simple PowerShell script.

Once the file has been uploaded you should be able to view it in the data management.

If the “Purge Data File” has been set to “Yes” in the target application options the on-premise file will now be deleted

As the data file exists in PBCS a REST resource is accessed to load the data from the file directly into the plan type (essbase) database.

This time it will be a POST method and the REST resource URL is the same as was previously used to refresh members:

https://<pbcs_instance>/aif/rest/<api_version>/jobs

A JSON formatted input is required which provides information about the job.

The job name will be the name of the planning application, the job type is “DIRECTIMPORT” and the load method will always be “ESSFILE”

The remaining property values depend on the application options and obviously the file name of the file to load.

In the above example I have set no drill region just because it is large and looks quite messy on-screen which I don’t think your eyes could have coped with :)

The response includes information about the process that has been executed and it is possible to keep checking the job status by adding the id to the end of the same URL

The value of the property “purgeDataFile” determines whether the data file is deleted once the load has completed.

Checking the process details confirms the process is the same as if run from on-premise FDMEE like earlier.

In the next part I will cover what happens when extracting data from a PBCS application and loading to an on-premise EPM application.