Executing Custom Scripts with REST

This is a quick one...

As you may know, GJG (aka Great John Goodwin) has introduced FDMEE and REST in his blog:

• FDMEE and REST is up to you - part 1
• FDMEE and REST is up to you - part 2

So I'm going to steal a bit of his hard work :-)

In my review of PSU210 I went through executing a custom script from command line.

The formula is then easy, My post + GJG's post = Executing Custom Script with REST

We already saw that a custom script is actually treated as a report. Therefore syntax to execute the custom script with REST does not differ so much from the one John showed. The only difference is that reportFormatType is SCRIPT rather than REPORT.

BTW, I'm using RESTClient in Firefox. I'm not going to cover basics to add header with authentication and json content, etc. You can visit John's blog for that.

Also be aware this is only possible in FDMEE on-premise as Data Management in the Cloud does not support scripting yet.

Executing Custom Script without Parameters
We have three mandatory keys in the request body:

• jobType = REPORT
• jobName = this is the script name when you registered (not the py file)
• reportFormatType = SCRIPT

After calling the REST resource to run jobs, we get both status and job id. We can then easily get status to see if it's completed or error was raised:

As you can see, processType is now COMM_EXECUTE_CUSTOM_SCRIPT.

If you navigate to FDMEE, process details page will show the script being successfully executed:

Executing Custom Script with Parameters

If your script uses parameters, then you need to add them to the json body:

Status can be checked in the same way as before:

And finally, process details will show execution:

And that's all I wanted to show. With the new PSU you can now execute custom scripts from external systems in a very easy way.

Thanks again to John for introducing FDMEE and REST.

Enjoy!

FDMEE 11.1.2.4.210 (PSU210), diving into the patch! - Part 2

In my last two posts I went through new PSU210:

• First glance
• Diving into the patch - Part 1

In this new post, I will continue with the new features, especially executing custom scripts from command line.

Running Custom Scripts from Command Line (executescript batch utility)

I'm going to keep it simple so I have created two custom scripts having application COMMA4DIM assigned as target application:

• Custom script with no parameters
• Custom script with one static parameter

The two scripts simply write a debug entry in the FDMEE process log:

I have registered the two scripts as follows:

When having parameters, it is important to know that Display Name value will be passed in the command line. I will show more details below.

If you are familiar with custom scripts, you may have noticed that they are actually treated and stored as reports. Indeed you can run a SQL query on TREPORT table to see them:

Script parameters are also stored in TREPORTPARAMETER:

Batch Utility executescript

This new command line utility allows us to execute custom scripts either from Windows or Unix systems. We already copied the bat/sh utility when applying the patch. Now we will see how to use it.

Bear in mind that the new admin guide has not been released yet (maybe it is already when you read this post), so I may have to update some details later.

Update 13/1/2017: admin guide is now released. However, this topic is not well documented, probably a documentation bug.

Syntax is quite similar to the one used for runreport utility:

executescript <user> <password> "<script name>" <parameters> <execution mode>

where:

• user is the FDMEE user running the script
• password can be either the hard-coded password or a file with encoded password (-f:filename)
• script name is the name registered in Script Registration page (not the python script filename)
• parameters are the script parameters in the form "Display Name=Parameter Value"
• execution mode is either SYNC (synchronous) or ASYNC (asynchronous)

If you are going to use an encoded password, you will find the configuration steps in the admin guide.

Executing a custom script with no parameters

If the execution request is successfully submitted, we will see the new process id:

Getting a success message in the command line window doesn't mean that the script has been successfully executed.

To verify it, we can navigate to Process Details page to see more details:

 

Executing a custom script with parameters

The new process id:

If the script has additional parameters, they will be passed as additional enclosed strings. For example:

executescript user password "script name" "display1=value1" "display2=value2" ...

FDMEE Process Log

I found something unexpected when I executed the script from the command line. FDMEE created two process logs, one in the global application folder and another one in the target application application folder.

For example, when executing the script with parameters:

• Log in the target application log folder

• Log in the global log folder

What I'm missing

If you give a try, you will notice that noting prevents you to create two custom scripts for two different target applications but having the same name. In the following example, I have a new custom script assigned to target application GRA and registered as "Script from Command Line (no parameters)". In the syntax we don't have an option for target application. Therefore, I guess FDMEE just queries the TREPORT table and gets the last one created. In my example, the script for GRA was executed instead of the first one created for COMMA4DIM:

I was also expecting a new button in the UI to show the syntax for executing the custom script from batch. We already have one for executing reports from batch utility in the Report Execution page.

Allow Unlock by Location

When enabling this new option, user will be allowed to unlock a specific POV (Period and Category) by location.

Users can unlock the POV when selecting it in the Data Load Workbench:

IMHO, POV lock feature really needs more enhancements. For example, I would like to have an easy way to have all related to this feature in one place: see all locked/unlocked POVs, lock/unlock a set of POVs in one click, etc.

Max Number of Parallel Batch Jobs

We can now specify the maximum number of parallel processed submitted by a batch in one execution. This can be configured when defining the batch:

Just a funny experiment I tried to set it as a character:

Message says value must be a signed decimal,,,but we just need a positive integer.

It doesn't raise any error when you save a negative number which doesn't make sense at all.

Export Delimiter for Mappings

This a new Global/Application/User setting to define the delimiter for mapping export files:

REST API for FDMEE  (Data Management)

As happened with PBCS and Planning, REST API for Data Management (Cloud) is now available in FDMEE. I'm not going to cover it as my colleague John is already blogging and reading his posts about REST API is highly recommended.

However, I already covered how to use the On-prem Planning REST API to load metadata from FDMEE in this post. In the same way, you can think in may integration/automation requirements which can be now easily met with REST API for On-prem Data Management (aka FDMEE).

I'm going to leave it here for today. I hope you now have a better idea about new PSU210 and what new features are provided. In any case, don't forget that this new PSU fixes several bugs in addition to providing new features.

I consider the SAP HANA integration one of the coolest new features so in my next posts I will cover this integration in more detail.

Enjoy!

FDMEE 11.1.2.4.210 (PSU210), diving into the patch! - Part 1

Time to install the new patch!

Installing the Patch
First thing you have to do is read the Readme.html file :-)
This patch has some pre-requisites which I have to go through. After seeing installation steps, it looks simpler than previous PSUs.
I'm not going to cover how to apply the patch, just make sure you get Opatch succeeded message:

I don't really understand why there is an additional step to copy batch template file for new feature to execute scripts from command line. I was expecting to be automatically when applying the patch:

 In my case, my environment is single server so no need to copy any jar files. As per Oracle's readme:

If your deployment is a distributed environment where Hyperion Financial Management and Planning are on different servers than FDMEE, copy the following jar files:
Copy files from the Hyperion Financial Management server on EPM_ORACLE_HOME\common\hfm\11.1.2.0\lib to the same directory on the FDMEE server.
Copy files from the Planning server on the EPM_ORACLE_HOME\common\planning\11.1.2.0\lib to same directory on the FDMEE server.

You can check versions in Workspace after patching:

In my case, I had to patch HFM and Planning as well.

Action Scripts for HFM

Actions scripts are used to integrate with HFM and they are located in the FDMEE Server:

The three above has been modified in PSU210:

Why this is important?

Sometimes you have to adjust these action script files. This is a valid option but you need to take into consideration that changes applied are undone when the patch overwrites the files. Therefore, if you have changed any of the three scripts, you will have to re-apply changes after patching.

HFM_LOAD.py

Just minor changes to fix bug related to loading journal descriptions.

HFM_EXPORT.py

Just minor change to fix but related to order of records exported in the DAT file.

HFM_CONSOLIDATE.py

This change has been done to correctly cast parameter options as boolean. In previous version, Calculate and Consolidate were not executed correctly.

Import Formats

New import types...new import format options:

Please bear in mind that this is currently supported for File type only. Other sources like Universal Data Adapter don't support it yet.
They have also added a search icon for Source and Target so we don't have to select both Source  and Target types. This is a good enhancement but it has one drawback. They did not include a column to see the application type:

You can also see new options for new features such as skip row for delimited, using period/years in row, Tilde as file delimiter:

TDATASEG and TDATASEG_T Tables

We can now load Text data to planning. FDMEE used to have column AMOUNT and AMOUNTX to load numeric data so new columns have been added to support Text, Date, and Smart Lists.

Consequently, some SQL Views have been also adjusted. Take it into consideration if you adjusted any VIEW for customization purposes (Ex: AIF_HS_BALANCES)

SAP HANA

SAP HANA integration is now supported in the Universal Data Adapter:

Important: there is no documentation yet, neither in the patch readme so I will perform actions below on my own risk.

SAP JDBC Driver

I haven't found any SAP HANA JDBC Driver (supposed to be ngdbc.jar). I find this obvious as I don't think Oracle is allowed to deliver SAP drivers. If you are a SAP customer or has access to SAP market, you will be able to download it.

As part of the configuration, some ODI files need to be imported into both Master and Work repositories.

SAP HANA (Master Repository)

We first need to import the new technology for SAP HANA (I have used INSERT_UPDATE Mode but you can use INSERT as well as this a new object):

This also imports the Logical Schema for UDA:

Having a look at the Import report, I can deduce this a copy of an Oracle technology :-)

After the technology has been imported, we need to import the Data Server and Physical Schema for HANA:

You should get a report like this:

If everything went OK, you will see new objects created in Topology:

SAP HANA (Work Repository)

Next steps are to import UDA Models Folder and UDA Project which in their new version have SAP HANA models and projects respectively.

Important: these two imports should be performed in INSERT mode. Otherwise, existing UDA configuration will be deleted. I will assume that there were no changes for other UDA Data Sources in PSU210, so I don't need to update any model or project existing. 

For example, this is my Model structure before applying the patch:

As you can see, I have UDA already configured for Oracle and MSSQL.

If I import new model folder in INSERT_UPDATE mode:

Then existing objects will be deleted:

Import must be done in INSERT mode if you don't want this to happen:

In that way, only new SAP_HANA model/sub-model will be imported:

And both new and existing Models will be available:

Same applies to UDA Project, we only want to import the new project for HANA: 

The new HANA project is now available in the Project list: 

Some bugs fixed

It's not all new features in a PSU. We need to fix some bugs as well :-)

Duplicate Mappings

It seems that now get errors when adding duplicate mappings:

Even if we try to import mappings from files:

 We get an error pop-up window:

Update 9/1/2017: it seems that some people are able to have duplicate mappings after applying the patch. Indeed I could create now duplicate maps. The reason is that validation of duplicate maps does not work if there is an existing mapping already saved in the system. So if you add two new maps without saving the first one before adding the 2nd one, it will work. If you add new map, save it, and then add the duplicate one, the two are now added... 

In other words, it seems that is not validating against existing mappings but just new ones. Probably a bug. Let's wait until Oracle's feedback.

Update 10/1/2017: a bug has been filed for this issue. Hopefully a PSE will be released soon.

Update 13/1/2017: it seems that bug is related to MSSQL only. Oracle database should work fine

I will leave it here for today. I hope you have now a good idea of what you get with new patch.

Merry Xmas!