FDMEE PSU520 released (11.1.2.3.520)

From the creators of FDMEE PSU100, PSU200, PSU500, and PSU510, we now present... PSU520!!!

I know lot of people have been waiting for this the whole summer. Honestly, I haven`t. I was just thinking of enjoying summer time :-) But we finally have it here so it's time to see what is going on.

Before installing the patch and discussing about new features let's see defects fixed in this PSU (+40):

I have faced some of these issues but I would like to highlighted one known issue related to an ADF bug. I had several customers getting empty Excel when exporting data from Data Load Workbench, We finally have a workaround:

18665000 – Export to Excel works for small data sets, but not for a large data set.

Workaround: First, in the Data Load Workbench, display the data to export to Excel. Then, click on the last row of data. (In some cases you will have to scroll down through many pages to get to the last row.) Lastly, click the Export to Excel icon.

Installation

Applying this patch is similar to PSU510. Tasks to be performed are well described so I didn't expect any problem installing it. If you are coming from previous PSUs you can skip most of the steps:

1. Apply ODI Pre-Requisite Patch 17178347 (SKIPPED as it was already done in PSU510)

2. Stop the FDMEE service

3. Apply patch 18202833 using opatch: 

Unzip patch in folder %EPM_ORACLE_HOME%\OPatch
Then apply path as follows:

opatch apply 18202833 -oh %EPM_ORACLE_HOME% -jre %MW_HOME%\jdk160_35

Example for Windows:
opatch.bat apply E:\Oracle\Middleware\EPMSystem11R1\OPatch\18202833 -oh E:\Oracle\Middleware\EPMSystem11R1 -jre E:\Oracle\Middleware\jdk160_35
Example for Linux:
opatch apply /u01/Oracle/Middleware/EPMSystem11R1/OPatch/18202833 -oh /u01/Oracle/Middleware/EPMSystem11R1 –jre /u01/Oracle/Middleware/jdk160_35

In my case:

Note: as all database changes and ODI scenarios are updated during the FDMEE Server startup after this path is applied, ignore errors in the FDMEE Server log due to existing objects in the FDMEE database.

4. Add rewriting rule to the OHS configuration file (SKIPPED as it was already done in PSU510)

If not using OHS, have a look to this Support Document.

5. Register the HFM Adapter
As we already applied 11.1.2.3.XXX, we first need to unregister the HFM adapter:

We can then register the adapter again either using the EPM System Configuration tool or the command RegisterHFM.vbs. I prefer second option as I see all messages detailed:

6. If we are running JDE or SAP BW Adapter import the Adapter Projects for them.

Important Note: this step shouldn't be performed if you already did either in PSU200, PSU500, or PSU510. The two ODI adapter projects have not been changed since PSU200. Patch readme can be very confusing and take you to undesired scenarios:

Step 6 is missing following statement as other steps does:

"This step is not required if you have already performed it as part of the 11.1.2.3.XXX patchset update application"

What can happen if you perform this step and you already have configured/customized/used the adapters?

I will show you a situation you would like to avoid. 

In my case, I have already generated the ODI Scenario from JDE import format, and configured the SAP BW adapter from FDMEE (so generated the ODI package template, interface, and scenario)

a.    Log in to the Work Repository FDMEE.

b.    Go to Designer->Project.

c.    Select Import Project, then IMPORT_UPDATE for mode (it is actually INSERT_UPDATE), and then Project File from the directory:    C:\Oracle\Middleware\EPMSystem11R1\products\FinancialDataQuality\odi\11.1.2.3.00\workrep

When we use INSERT_UPDATE mode, we are warned about some objects to be deleted/replaced:

Note: When warnings are prompted for object replacement, you need to be aware that ODI objects created either from ODI or FDMEE (Ex: Package, Interface, Scenario for SAP BW) will be deleted

My previous work has been deleted :-( 

What happened above was due to importing the two ODI projects. This is not supposed to happen as you don't have to import the ODI projects if you already did in previous PSUs (at least in PSU520). 

But in the future it can happen that Oracle updates the ODI projects so you have to import them in order to have the new versions.

In these situations, I think that documentation should state that before importing new ODI projects, you should export Packages, Scenarios, Interfaces, and Variables or any other ODI object which were created during your FDMEE configuration or customization of these 2 adapters (you can use Smart Export from ODI). Then after importing the new project adapters (if needed) you can import your objects again. 

In case new versions implies you need to regenerate ODI objects from FDMEE, then documentation should inform you accordingly.

If you don't backup ODI objects that FDMEE generated or you customized:

• If you have ODI customization (I didn't have in this environment) and you don't back up your ODI customized objects, then you will have to recreate them from FDMEE after adapters are imported
• If you don't have ODI customization and you don't back up your ODI objects
• For SAP BW: Generate Package Template + Regenerate ODI Scenario from import formats. You don't need to import info cube definitions from FDMEE again as ODI models are not deleted (models are not present in the SAP BW Adapter because they are created as needed when info cube are imported from FDMEE Source Adapter page):

• For JDE: Regenerate ODI Scenario from import formats. Models used by the adapter are already delivered by Oracle in the JDE Adapter so you don't need to worry unless they are additional models for tables you reversed from ODI.

Note: if you have customized any of the two adapters by following instructions in Support Doc 1920095.1, then you would have to perform backup + import new adapters in the Development Work Repository you created for Customization.

7. Update the EPM Instance with the new report batch scripts (SKIPPED as it was already done in PSU510)

Windows:     copy: %EPM_ORACLE_HOME%/products/FinancialDataQuality/bin/runreport.bat.template 

to: %EPM_ORACLE_INSTANCE%/FinancialDataQuality/runreport.bat

Linux. Copy the following files:

- runreport.sh.template

- runbatch.sh.template

- encryptpassword.sh.template

8. Start FDMEE Service

DB changes are applied and ODI Scenarios are imported. As described above you don't need to worry about errors raised when creating objects already existing in the DB:

9. Restart the HTTP Server

Once we finish, we can check the FDMEE current version from Workspace:

New Features

Although we know that most of the features we are waiting for are coming in 11.1.2.4, this PSU brings some of the missing ones:

Point of View Lock for All Locations
This feature was in the must list. All FDM Classic users were expecting it. With this new functionality FDMEE administrators will be able to prevent other users from loading data to locked POVs in different business processes like month closing.

What can we lock? lock all locations related to a target application for a given Period and Category

How do we lock? there are two new buttons "Lock POV" and "Unlock POV" in Application Settings

When we click in Lock POV button a window is prompted so we can select Period and Category to be locked for all locations

When we click OK we get an information message:

How Lock POV works? when running a Data Load Rule from Data Load Workbench, Data Load Rule page, and Batches, FDMEE will check if the period and category being processed are locked or not.

Loading data for a locked POV
When we run the DLR we get an error message (even if we are administrators):

Example above was for a Location which we did not process data for January 2006 - Actual. 

In the same way, if data has been already processed and the POV is locked, we will get the same message:

I really miss an icon in the POV bar showing the POV is locked :-(

We had this functionality in FDM Classic:

What happens internally in the DB?

Once we click OK after selecting Period and Category, table TLOGPROCESS is updated so column INTLOCKSTATE is set to 60 for all entries where PERIOD and CATEGORY match the ones we selected for our target application.

INTLOCKSTATE = 60 > Locked

INTLOCKSTATE = 50 > Unlocked

But what happens with new DLRs which are not executed yet?

This may not be the correct question. I would prefer, what happens when we lock a PERIOD-CATEGORY combination we never processed for our target application?

For example, I will lock JANUARY 2007-ACTUAL. This is a POV I never processed:

When we click OK: 

We get the message:

POV cannot be locked because data was never loaded from any Location to this POV

So basically we can only lock PERIOD-CATEGORY combinations that we already processed for any Location loading to our target application. In other words, PERIOD-CATEGORY can be only locked if there is any record for that combination plus target application in table TLOGPROCESS. Therefore I cannot prevent users loading data for new PERIOD-CATEGORY combination using this new functionality. I will have to wait until they load data for the first time and then lock that POV.

How do we unlock a POV for all Locations? 
Before unlocking, I'm going to lock the following PERIOD-CATEGORY combinations:
* January 2006 - Budget
* February 2006 - Budget
So I have already locked the two combinations above plus January 2006-Actual. The following SQL query shows my POVs locked:

When we try to unlock we don't see combinations locked as we select PERIOD and CATEGORY separately:

So how do we know which PERIOD-CATEGORY combinations are locked?
In fact, I will try to unlock February 2006-Actual which is actually not locked:

Surprise, surprise... POV has been unlocked successfully!  

But February 2006-Actual combination was not locked :-)

Let's unlock February 2006-Budget:

After seeing the same above information message  (so period unlocked) I will run the same SQL query:

As expected, INTLOCKSTATE has been updated with value 50 for all records in TLOGPROCESS matching our target application and PERIOD-CATEGORY combination unlocked.

Enhancing Unlock POV
To be honest, I was expecting a table like TPOVLOCK where we have combinations of PERIOD-CATEGORY locked by Target Application. I'm not going to question why Oracle designed this functionality in that way but I know administrators will be saying..."How do I know which POV Period-Category combinations are locked?"
I see Custom Scripts a good approach. You could even build your own Custom Script to unlock POV PERIOD-CATEGORY.
Once I build my Custom Script, I try to execute it...

Hey Hey, where is Script Execution link? I'm administrator so I should see it but I don't :-)

So we probably are facing an important bug. As a workaround, I have just setup an Intermediate-2 user and enable Script Execution for Intermediate-2 Role...but I don't see the menu entry neither:

If you never saw this option, here you have it from 510:

NOTE: Once this is fixed, I will update this part.

At the moment we already got confirmation from Oracle about this bug. They will be releasing a patch ASAP (1-2 days)

Anyway, think that if you know how locked POVs are stored then you can use custom scripts for any administration task related. For example, the following custom script use a parameter to show locked POV PERIOD-CATEGORY combinations:

NOTE (10/SEP/2014): Oracle just confirmed the patch for this bug (19579637) 

Lock POV for Multi-Period

I was wondering what would happen with Multi-Period files. In the following example I have a DLR for Multi-Period file having 3 periods:
* January-2006
* February-2006
* March-2006
When we execute the DLR from DLR Page:

Is this message because having January 2006-Actual in my current POV bar? or because January 2006-Actual is a column in the Multi-Period file?
As always, any answer needs a test so I change current POV Period to be July 2006:

Woops, we don't get any error prompted but we can see status is FAILED.
If we navigate to Process Details page, we can see how it failed but no error messages/information (bug?):

Let's have a look to our friend ODI Operator:

The DLR was executed but it failed because January 2006-Actual was locked.

And finally, what our friendly FDMEE process log has to say?

As we were expecting, the DLR failed when getting the POV list. Basically, it gets the POV list to be processed and checks if PERIOD-CATEGORY combinations are locked.

I like stress testing so what about if we lock February 2006-Actual and unlock January 2006-Actual. Would it process January 2006 or stop before processing periods?

My locked POV will look now as:

If we execute the Multi-Period DLR:

Those who predicted that data was not going to be processed for any periods were right. In fact, we saw before how the error is raised at Pre-Import step, meaning that this check for locked POV is performed before data is processed.

POV Lock in FDM Classic
I wouldn't like to leave this new functionality without comparing to what we had in FDM Classic.
If we have a look to the old FDM admin guide we can see details about POV Lock settings:

If you see listed options, you will notice that we could lock the current POV, being the POV the combination of Location-Period-Category and not only Period-Category for all locations. In other words, we could lock the POV for specific locations. However in FDMEE we can only lock POV (Period-Category) for all locations and not for individual ones.

What was I expecting here? the functionality to lock POVs so we can lock specific Location-Period-Category-Data Load Rule. Maybe in 11.1.2.4? I would really like to see a "POV Management" menu entry. And I know FDMEE administrators would love it as well.

Being said all this, I think that's enough discussion about Lock/Unlock POV...

Global User for Application Access
This is a big step forward, something demanded since years. Are you familiar with this issue?
It was inherited from FDM Classic. 

Basically, only a user with Administrator access can export data to Planning and Essbase
With this new application option, we can specify a global user that will be used to access the Planning/Essbase application instead of the FDMEE sign-on user. This global user should have administration access to the applications so he can lock/unlock Essbase objects like Rules file.

We can specify the global user from Register > Target Application > Application Options:

This new feature will be totally transparent for end-users as they don't know which user is loading data to Planning/Essbase.

PS: what do you think about security if using this approach? 

JavaScript supported Drill-Through
Before this new patch, FDMEE only supported http based drill-through.

Let me describe an example:
We all know we can drill-through to SAP Web Client, but what about drilling-through to SAP GUI (Desktop Client)? It was not possible until now. We just have to write the appropriate JavaScript and specify from our source system.
The same way we do with http drill URL, we can pass contextual parameters to a JavaScript.

If we navigate to Script Editor page we can see new Script type "Drill-Through" and Technology "JavaScript":

Here is dummy example to show Description-1 value when I try to drill through using JavaScript:

1. Ensure that Application Root Folder is correctly configured at System level (System Settings). New JavaScript drill-through is defined for source systems so they will be saved at system level rather than application level.

The javascript will be saved in Application Root Folder (System Settings)\data\custom\yourscript.js

2. Navigate to Script Editor > Drill-Through > New. Enter script name to be created (with no extension)

3, Write or paste your code. The script function name and the filename should be the same. In my example, I'm just showing a message with Description.

FYI I have written my JS file in Notepad++:

And then paste it in the script I created from Script Editor:

As described above, the JS script is stored at system level:

And that means that you will be seeing the script in the Script Editor under Drill-Through scripts regardless your target application. But they will be the same script.

NOTE: there is a known bug that will be fixed along with "Script Execution" one, we need to copy the JavaScript file to scripts\custom at Application level as well. This step is currently needed to make the JavaScript working:

4. Update the Drill URL in source system page to be #javascript

5. In the Source Adapter or the Import Format for file, update Drill URL with script call:

testDrillThruJS('$DESC1$')

Single quotes are only required if you want to pass the parameter as String.

6. Testing new JavaScript Drill-Thru

Import Data and drill-through from any source amount:

And...

I have been able to show a message with column Description-1 (DESC1 in TDATASEG).

The same way we show messages, we can open local programs like SAP GUI :-)

NOTE: I just noticed that description is shown with + instead of white spaces. I will be investigating but in the mean time we can force replace in our JavaScript:

So we get what we expected:

And lastly, just a quick look to the ERPIntegrator0.log to see how this new feature works:

EBS Data Loads supported in Open Batches
We can now use Open Batch for EBS and PSFT data sources.

Support for JDE Tools 9.1.4
This version of JDE Tools is now supported. You have more details about configuring JDE integration in the admin guide.  

Other changes in new version
They are not new features but technical changes in FDMEE so I considered interesting.

HFM Adapter Action Scripts
There are some changes in HFM_EXPORT.vbs to fix some issues with Line Item Details and to add column order when exporting files to HFM:

Column order is now set as:
 "!COLUMN_ORDER=SCENARIO,YEAR,PERIOD,VIEW,ENTITY,VALUE,ACCOUNT,ICP,"&strColumnOrder
where strColumnOrder is replaced by all active custom dimensions ordered by column name ascending.

API
I don't want to anticipate anything but we may have some new functions like being able to insert custom information into Process Details Table. We will see once the admin guide is published.

ODI Scenarios
It seems that a new scenario has been added to refresh source values in FDMEE. This was probably performed by API before:

Report Templates
I don't see any new template.

As you can see, it took some time to fully analyze the new patch and its new features. Now it's time for you to install and stress testing as much as you can!

If you have any findings or any concern about 520 feel free to share with us :-)

Share this post