Friday, September 26, 2014

I'm new to FDMEE - Part 1. How do I start? What should I know?

Hi all,

I know I should have posted this entry long time ago but it's never too late if that is good. And I promise my colleague Cameron.

So are you new to FDMEE but have experience with FDM Classic? totally new to FDMEE? end user? developer? administrator?... many questions right? To be honest I don't like making any difference between users although I understand they often want to know different functionality of the product. Basically, they want to know only what they need.

When I started with FDM Classic/Upstream some time ago, I didn't have anyone sitting with me showing the product, its functionality, examples, etc. How did I learn? patience, time, errors, troubleshooting, getting some knowledge from gurus...and projects! I liked the product but honestly what I like more was the fact of being a solution architect for integration. And I say that because I would recommend you not to loose sight of this stream. You can learn how to create an import format, mapping, logic accounts, etc. but the essence is to design the best solution for your requirements, and then build it. In other words, don't ask yourselves only the what and the how but also the why.

I have always tried to let customers know that the data integration is so important as their functional applications like HFM or Planning. In fact, data integration is essential. You can have the best Planning application, with the best business rules, and the best forecast process in the world but if you don't have the data you need for that model to work, you have nothing. Why you want a Ferrari if you don't use the best fuel? It's just my humble opinion.

FDMEE has several functionality. When I was a student my mum always suggested to start with the easier questions of the exam. She said this was a way of gaining confidence. I will make the same recommendation to you. For example, don't try to learn how to create a complex mapping logic in FDMEE if you don't know how mappings work. With the basics you will build the complexity you want.

Is the admin guide enough to learn? Not, absolutely not. You can use the admin guide as support to understand the functionality but they guide is not going to show you how you have to design a solution. It does not say how to solve issues neither :-(

In this first post I will encourage you to discover what FDMEE does

  • Read the first chapters of the admin guide without focusing on the technical details like database or folder architecture. You will have time to learn these topics.
  • Go to Oracle site and see the product data sheet
  • Go to any partner's site working with FDM and see their product introduction
  • Visit OUG groups and subscribe in order to get access to presentations (ODTUG, UKOUG, etc.)
  • Visit blogs (fdmguru, thinkfdm, mine...)
At the end of the day you should have gathered enough information to have a first understanding of the product. Let me add some points to your findings.

Some Introduction...
If you take any sales presentation or demo you will learn about which values FDMEE can add to your business.
I will wrap it up in five points:

  • FDMEE is more than a ETL tool
  • It provides audit trail functionality to source financial data
  • It helps to ensure data integrity and mapping consistency that allows easy reconciliation of financial data
  • It help users with data error investigation, identification, and correction
  • It provides flexibility to meet all levels of complexity in your integration
Only financial data? not really. You can also process non financial data. It all depends on the data you need for your target model.

So technically, What does FDMEE do?

So you now know what the product provides to your business :-) It's time to move to how we work with the tool.
Being said all this, my first recommendation would be to start asking yourself: what does FDMEE do?
And here is where my favorite approach comes... High Level > Detailed Level.

As I said, FDMEE can do lot of things, but what is the main purpose?
Level 1: Get data from a source and load it into a target application
Level 2: Get data from a source, map to my target dimensional model, validate, and load into a target application
Level 3: Get data from different sources in different formats...

This is the way I work and the way I like (not necessarily the way you do). High level explanations always reach all stakeholders regardless they are business users, techies, developers, etc. Detailed explanations may vary based on your audience.

In the following picture you will see the normal data load process for FDMEE users:

The objective? users have to move the fish "up stream" in the header of the FDMEE web in order to get the valid data into the application(s). The process consists in 4 steps:
  1. Import data from your source into FDMEE. During this step mappings are executed so your source data is converted into your target model (dimension mapping). For example, you Cash account is 1010 in you source DWH data and Cash in your HFM application. Then you create mapping rule to convert 1010 into Cash member.
  2. Validate that all your source data have been successfully converted. If you have data for source account 1010 but you did not create a mapping rule for it, you will not be able to progress.
  3. Export your converted data to your target application
  4. Check (optional) will apply a set of rules to enforce data integrity. For example, you may want to ensure that data you loaded to HFM is balanced or specific balances are signed as expected.
A must to know :-)
A bit of history

Why fishes? fishes are not new in FDMEE. They were always there since old times of UpStream. In 2006 Hyperion acquired Upstream and re-branded it as Financial Data Quality Management - FDQM (FDM for friends).
In 2007, Oracle acquired Hyperion so re-branded it as Oracle|Hyperion FDQM. And finally, in 2013, we had the first release of FDM-Enterprise Edition, our lovely FDMEE.
Folks, I think that's enough for today. Next time, we will start looking at how FDMEE processes our reference source system...flat files! FDMEE extracts data from different source systems in different ways but the 4 steps we mentioned are common for all of them so as I said before, let's keep things simple, and files are simple, aren't they?


Friday, September 19, 2014

ADF Patch 19586149 for issue "Excel exported from Data Grid has only headers" you get any error message where this word appears?

I had several customers facing the following issue. When they tried to export data to Excel from Data Load Workbench, the output file was empty, just headers.

The problem was that this issue was happening randomly. For small sets of data was working, then for large was not unless we applied a filter in the quick search boxes. Then suddenly it was working for large sets...

The bug was not coming from FDMEE but from ADF and luckily we now have a new patch to solve that issue and few more:

We have already applied the patch in most of them and they confirmed data can now be exported to Excel with no issues :-)

Tuesday, September 9, 2014

FDMEE PSU520 released (

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.
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, 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 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\\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:

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, 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 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:
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:
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:
where strColumnOrder is replaced by all active custom dimensions ordered by column name ascending.

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 :-)