Monday, February 9, 2015

FDMEE 11.1.2.4...the show must go on!

Hey folks,
just had a quick review of new FDMEE and which features we will be able to enjoy. As always there were some more changes than the one described in the new features document.

Workflow and Setup menus
First think I noticed slightly different was the Workflow menu:
As you can see in the image above, there are three changes:
  • Logic Group, Check Rule Group, and Check Entity Group have been moved to Setup tab > Data Load Setup. This actually makes more sense and it's more aligned with FDM Classic
  • Write Back is gone? Noooooooo...it has been embedded into Import Format definitions. As we now have to set source and target types in the import format, when we select Source = EPM and Target = ERP, that implies a write back.
  • Excel Interface has been added to Integration Setup menu. This new feature allows us downloading/uploading (exporting/importing) FDMEE tables into Excel.This is something that everybody was expecting. If you come FDM Classic world, I'm sure you missed exporting any item with "Export to Excel" button and then being able to import new ones with "Import XLS" functionality (Tools menu). Now this feature has been improved as it allows you exporting/importing any table...any? we will see.

New source and target systems
In addition to PeopleSoft Commitment Control (which was integrated with PSPB - Public Sector Planning And Budgeting), FDMEE now supports Fusion Budgetary Control. More support for Fusion in FDMEE:
HPCM is now supported as a target application... and can be used in EPM data synchronization rules:
Application Settings: Display Data Export Option "Override All Data"
I was expecting this feature to be only available for Planning/HPCM/Essbase applications but it is also displayed for HFM applications (bug?). It is good to have this option as it will avoid accidental data clearing when loading data into HP/Essbase:
Target Application Options: Global User for HFM
This option was already available in 11.1.2.3.520 for HP and Essbase. It seems that is also available now for HFM although not documented in the admin guide.

GUI to build our Import Format
Another feature to complete parity with FDM Classic. We can now build our import format using a GUI that helps us to easy identify which fields and where they are placed in our source file:
We just have to select a field, then assign a source and target dimension to it. The field number will be automatically filled by FDMEE.
The new column definition will be added to the import format mappings:
Lock POV
In 11.1.2.3.520, we could only lock the POV at target application level. That was affecting all locations:
In 11.1.2.4, we have two lock options (as FDM Classic)
  • Lock the POV: specific location, period, and category. 
Is Data Load Rule included in the POV locked? or in other words, is POV locked at DLR level so I can lock specific DLRs for specific location, period, and category?
Not, when we lock the POV for location, period, and category, we actually lock it for all DLRs (another nice enhancement request)
And we can now see the lock icon in the POV bar :-)

Also we can now see which POVs are locked. When we select specific POV, it will show Unlock POV option if it is already locked:

  • Lock the POV for all locations: we actually had this feature in 11.1.2.3, just changed button caption
We still cannot see which POVs are locked to all locations from the Unlock All Locations button so you need to search for them.
Now we have parity with FDM Classic although it could be enhanced :-)

As an administrator I would like to have a menu option called POV Lock Manager where I could select combinations of location, period, category, and data load rule to be locked. We could also select "all locations", "all periods"... or even more advanced, "all locations starting with SAP_"... if somebody from Oracle read this...maybe in 11.1.2.5? :-)

HFM Adapter actions now in Jython
This was more than expected, new rewritten HFM, bye to COM? welcome Java...HFM adapter actions in VBS replaced by Jython ones:
If you are learning Jython, this is good place where review coding:

Import Format for file-based loads
When we create an import format we select both Source and Target Types.

  • Source: ERP or EPM
  • Target: ERP or EPM
Different combinations of these values define the data load rule type:
  • Source = ERP and Target = EPM > Data Load Rule
  • Target = EPM and Source = ERP > Write-back Rule
  • Source = EPM and Target = EPM > Data Synchronization Rule
So if we want to load data from a flat file we have to select ERP and EPM as source and target types respectively. Then we can select File from Source drop-down list (notice that File is the default source system for file-based loads and it's already created with FDMEE installation)


File type: delimited, fixed, and multi-period (same as 11.1.2.3.520)
File delimiter: same as 11.1.2.3.520
Data Load Rule for file-based loads
When creating a DLR for source files can now select the file type including different ones for Multi-Period files:
Data Load Rules: source, target, and custom options
Data Load Rule definition has been restructured so we have 3 types of DLR options:

  • Source Options: these are the old source filters in 11.1.2.3.520
  • Target Options: new feature! we can now set target application options at data load rule level. When working with FDM Classic, there were situations where we needed a copy of the target adapter to have different options for different locations. Now we can easily set these options in FDMEE: 

Be aware that if you change an option value in the target application options, the new value will be applied to all Data Load Rules. Then you can override the value for each data load rule. I don't like it.
  • Custom Options: the four integration options we had. It would be great if Oracle enhances these options and allow defining more than 4 and even define option type (check box, drop-down...)
Using a Rule file to load data (Planning and Essbase)
Does it mean we can use our own rule files when loading data to HP and Essbase? In 11.1.2.3.x, FDMEE created rule files on the fly when loading data.We could not do anything about this. This feature is not documented so we will have to test


More about import formats
We can select the concatenation character for source segments/chart-fields when our source system is an ERP (very useful for ARM integration):

Fixing mappings Errors on the fly
Validation Errors during mapping process? were you missing this feature in 11.1.2.3.x? we now have it! new mappings can be added from Validate step when errors are captured:

AIF Logs
I don't remember having this log in previous release...aif-WebApp.log
Maybe my infra colleagues can correct me but it seems that Oracle split the server logs and created a new one for web applications (I saw it also for Planning)

Target Application: refresh members
New option to refresh members have been added to the existing one for refreshing dimensions. So if you have new members in your dimension and want to see them in the list of target members in mappings, you can use this feature:
Adapter for Fusion Cloud
Cloud, cloud, this is something you will be familiar, it seems that Oracle is already testing integration with Fusion Cloud

ODI Studio 11.1.1.7
I suggest you download ODI 11.1.1.7 from OTN and not the one included in the EPM System download. As last release, there are missing files:
Also, don't worry if you try to install ODI 11g in Windows 2012 Server which is not certified? just click Continue and it will be installed:
Data Synchronization
Say goodbye to customization made to move data between EPM applications. We can now do it out of the box. As stated above, once we have registered our EPM applications in FDMEE, we can create an import format where source and target are EPM:
Then we link source and target dimensions, add new dimension rows, expressions, etc.
As shown above, we can use expressions Fill, FillL, and import scripts :-)

I dont' have a server just a humble VM... so I had to follow what my colleague Henri suggested and decreasMinDataCacheSizeInMB to survive :-)

I stop here for today, next...new Excel Interface, Data Synchronization, changes in JAVA API...the show must go on!

Enjoy!

Thursday, January 29, 2015

FDMEE 11.1.2.4 has been released

Hi all again,
I'm pretty sure you are already aware of it already had a look to all published by bloggers all around the world. If not, you should know EPM 11.1.2.4 was released at the beginning of the week.

I have been offline for some time...I had to move forward in many different matters before my 1st baby comes :-)

Although I have to publish some Part 2, 3, etc. I would like to write a brief introduction about what new features of FDMEE 11.1.2.4. Once I have my EPM installation up & running I will update this post with some screenshots and feedback and will continue the series with other new enhancements you cannot easily see but they are actually there...either in the documentation or code line...
In this first post I will point to the ones documented in the New Features Document which you can find here (I know, erpi is still in the doc name!)

Target Application Enhancements
Native support has been added for HPCM and TAX provision (HFM application)
They will be available as target application type in the registration page:

  • Financial Management and Tax
  • Profitability
What about Hyperion Strategic Finance? We will have to wait as it is on the road map.

Target Load Options
This is a good one. Do you remember in FDM Classic the capability of having different adapter for the same target application due to requirement of different locations having different requirements when loading. For example, use data protection or not.
In FDMEE, we can only register a target application once so that was not possible (only with scripting). Now it is.
Data Load Rules have 3 types of options:
  • Source Options (as they already had)
  • Target Options (we can define target application option at location/data load rules level)
  • Custom Options (integration options 1-4)
Data Synchronization
This is the key seller of FDMEE 11.1.2.4. We can now move data between EPM apps out of the box. No more import scripts, no more Open Interface Adapter to extract data from EPM apps, no more calls to Extended Analytics, etc. 
In this release, we will be able to configure data sync in a very easy way. We can even define filters using application native syntax (Es: @RELATIVE...) when extracting data.

Write Back
So now that we can extract data from HFM applications, why not being able to write-back to ERP systems?
Before 11.1.2.4 we could only do it from Planning and Essbase.

POV Locking
As you can see in my old post I was not so fan of POV lock and the way it was designed. It seems they have added FDM Classic parity so you can now lock POV for all locations or individual locations. I still need to double check if the enhanced the locking method.

Import Format Builder
Honestly I don't know many people using this feature in FDM classic but I understand it can be very useful specially for business users.
For those who don't know how it was working, basically you could build your import format in a more graphical way so FDM was telling you how source fields and dimensions were mapped int he import format.

Concatenating ERP segments
This is also a good one as it solves a limitation. We can now concatenate unlimited number of ERP segments/chartfields (before only 5)

Fix mappings on the fly
Anyone could provide an answer  about why this feature was not available since FDMEE born.
We can now fix missing mappings from the validation screen as we did in FDM Classic.

Report Translation
There are now report templates in different languages. I will check if Spanish is one of them :-)

FCM integration with FDMEE
In previous releases FCM users could only trigger data loads for ARM.
Now we go one upper level. FCM users will be able to trigger data loads and navigate to Data Load Workbench from FCM when closing periods. I will have to check if users can also lock periods from FCM?

SAP Adapter
Although it was already available in PSU52, there is a new version of the FDMEE-SAP Adapter (4.0)
You can download it from Bristlecone site as usually. The new version has two main enhancements:
  • Performance improvement for Cost Center adapter including deletion of temporary tables
  • Some ODI KM bugs regarding KM parameters have been now fixed
And also documentation which is now much clearer and complete.

I think this is a good starting point...the show must go on! What do you think about new features?

BTW, I'm very happy to announce you good news for this 2015:
 



I expect a very busy year especially with the most difficult project...being father :-)

Wednesday, December 24, 2014

Merry Xmas and Happy New Year!

Folks,

I would like to wish you a Merry Xmas and a Happy New Year!

I hope this new year brings all the best...including all features you expect from FDMEE :-)

I wish I had more time for posting but helping others in this time is also great!


Francisco

Tuesday, November 25, 2014

Importing files with different File Charset Encoding - Part 1

There are multiple file character set around there...
I'm sure you all are familiar with concepts like UTF-8.
Have you ever seen weird characters when importing data into FDMEE or even your file did not get imported event if there were no errors apparently?

Before starting I would like to share some points that made me better understand all of this. So I hope it also helps you.

Types of strings in Python/Jython
There are 2 types of strings: 
  • byte strings
    • string elements are called bytes
    • there are only 256 possible bytes
  • unicode strings
    • string elements are called characters
    • there are over a 1.000.000 characters defined in Unicode strings
    • very useful because we can store almost any character and can be easily manipulated
How can we convert a unicode string to a byte string?
The word we are looking for is "encode":
"An encoding is a representation of a unicode string"

Note that not all encoding support every unicode character but some subset of unicode. For this reason UTF-8 is a good one (Universal Character Set Transformation Format - bit). It supports everything or, in other words, it defines a sequence of bytes for every unicode character. In fact, it's the default encoding for FDMEE.
So after understanding above we could say that an encoding is essentially a mapping table which translates every unicode character into one byte or a sequence of bytes. And the mapping table for UTF-8 is the most complete one :-)

If we read a byte string (typically from external sources), we need to decode it in order to manipulate it as a unicode string.

Aligning concepts
Now that we have a clearer idea about these concepts we could clarify something. We typically say that either text is ASCII or UTF-8 or UTF-16 (I used to say it), and therefore bytes are text. However text is only text. When we store text we actually should talk about encoding that text into a sequence of bytes. If we talk about images, there are many different ways to encode images into bytes. I'm sure you are familiar with JPG,BMP, etc. In the same way, there are many different ways to encode text into bytes...UTF-8, UTF-16, ASCII, etc.
Once we encode, bytes are just bytes. If we want the original text, we will have to decode.

To summarize
  • Encode: Unicode > bytes 
  • Decode: bytes > Unicode
Please take into consideration that we are discussing this topic in the Jython 2.5.1 context. I say that because you may find differences if you have a look to Python 3.x where byte and string are separate types (I would recommend this article if you are interested in seeing the differences)
I don't want to confuse you (even myself) so we will skip this as FDMEE 11.1.2.3 uses Jython 2.5.1.

File Character Set Option in FDMEE
FDMEE knows source files can use different encoding charsets depending on many different factors like source system generating the file, regions, people, etc. For this reason it provides the option File Character Set (FCS) at three different levels:
  • System Level (Profile File)
  • Application Level
  • User Level
Lower levels override higher levels. For example, we could have one generic FCS, another one for some target applications, and a different ones for different users loading data to same applications:
I woull say that this option includes values for all encodings:
Today I'm going to discuss about how we could manage a different scenario in FDMEE: having same user loading different files with different FCS to same application.

Our Scenario
Our FDMEE application is designed so we have one location used to import files generated from their Legacy System PIOLIN. These files use UTF-8 encoding.
In addition to this, the user responsible of loading PIOLIN data is also responsible of loading HFM eliminations into their HP application. The HFM data is extracted using Extended Analytics (EA). The file generated uses encoding UTF16-LE and it is compressed using GZIP compressor.
Therefore we have:
  • PIOLIN files using UTF-8
  • HFM files using UTF16-LE
  • HFM files compressed with GZP (filname.csv.gz)
Our First Solution: use different users with different encoding
Let's say that we configure FCS as UTF-8 at application level. 
It would be great if we could setup different encoding for different source files but unfortunately we cannot with the current release (11.1.2.3.520)
This is because FCS option cannot be currently defined at Import Format Level. If we could do that, then our solution would be straight forward.
Let's think then. It seems that the only solution would be to create additional FDMEE users because if we set the FCS value at user level we would be overriding both System and Application options. So for each user I could define a different value for FCS option and import would be working as expecting.
I'm not going to ask if you like this solution because I don't :-). Just imagine a FDMEE user having to log off and log on to import different files. Not good.

A Better Solution: convert source file encoding to FDMEE's one
If we have our generic FCS set as UTF-8 in FDMEE, why not converting our source file encoding to UTF-8? This is a very common approach when integrating data from heterogeneous systems. It's quite normal that they use different FCS so conversion and standardization is commonly needed. 
Is that possible? Is it easy?
Nothing is straightforward but with a bit or creativeness everything is possible. 
We have already discussed about how power is Jython for FDMEE scripting.
Using import scripts would be an option but I don't like the idea of having one import script for each import field. Performance of applying multiple import scripts may not be good.
However, we know that we have an Event Script called BefImport that is executed just before our source file is imported. So what about using this event script to convert our source file encoding before it is imported?

My Source File
I will forget about GZIP compression for the moment and I will just focus on my source file:
If we have editor like Notepad++ (my best friend) then we can easily see which is the encoding used:
My file uses UCS-2 Little Endian which is the older version of UTF16-LE (you can check it here)

My first import
Before building any solution we will see what would happen with my import when FCS option is set to UTF-8 and my source file uses a different one.
So after configuring our FDMEE artifacts (Location, Import Format, Data Load Rule, etc), we import our source file and we get a grey fish:
End user would go to the Process Details page and see something that he might not understand:
It says data was imported successfully and there is a warning for the mapping step.
So if data was imported, why don't I see it? it's time to look through the FDMEE process log:
When we see all error messages above and weird characters, we can suspect that it will be probably related to file encoding. It seems that FDMEE was not able to read te file correctly. 

A quick note on this: the Process Details page will not show that mapping step failed because there was no data imported and therefore no data to be mapped. With the time you will learn how to interpret different scenarios like this. I know business users would appreciate more descriptive and intuitive messages in the Process Details page without having to open the log. I'm not inventing this, they just told me :-)

You may want to listen some advice about this: don't try to build any solution at first step if you have potential format issues, just use Notepad++ to convert to UTF-8, or change the FCS option accordingly. Once you confirm data is imported, you can start thinking in the solution.
For example, if we import the same file after updating FCS option value to UTF16-LE:
We can see our data is successfully imported:

We now know our issue is related to charset encoding so let's think about the solution...

Friday, November 7, 2014

WoW you can finally set the Problem Type for FDMEE Service Requests in Oracle Support

Finally, it's here.

It's not a very interesting topic for Blog but it deserves it.

So far we could only create SRs and assign generic term "Technical Issue" to it.

I was creating a SR today for a customer and Oh my God!
Would this help to speed up problem resolution? we will see :-)

Friday, October 31, 2014

Cloud IDE for FDMEE Scripting. Make it your friend!

It has been a hard month...too busy with customers, surgery to pull put my wisdom teeth, recovering from ultra trail marathon... at least we still enjoy summer time in Malaga!

Today I would like to introduce some tools that can be useful for you to learn the scripting languages of FDMEE:

  • Jython
  • SQL
We all have heard about Cloud and Cloud...did you know there are IDEs (Integrated Development Environment) in the Cloud?
What is that? I will try to make it simple. It's a web where you can partially test your FDMEE scripts.
Why I say partially? Because don't think you are going to copy&paste your script, click Run, and voilĂ . 

But what about situations where you need to build an import script to parse data lines to get the account, or convert string to dates, or create a #SQL mapping which strips source Entity? These tools can help you to reduce time cost of implementation. Sometimes you need to run the import step 10 times to get your import script working and that makes me crazy.

I still need to say that I prefer Notepad++ to run my unit tests but Cloud IDEs are also a good tool.

Today I will show you how you can use Python Fiddle to easily test string operations for your FDMEE scripts.

Navigate to http://pythonfiddle.com/
Navigate to the site and enjoy. See what it can offer like some code snippets that may be useful for you but what I would suggest is to initially think of this site as a learning place.
Create your login
You can use your Gmail account or social network accounts:
Your first example
So I need to parse the entity code from my source file and not sure about how to do it. You could start building your script, running the import, fixing the script, running the import, see results..
Let's use the Cloud IDE for the following case:

  • My source Entity looks like XXXX-YYY-PLANTN
  • I want to import into FDMEE the value XXXX_PLANTN

As you can see I have divided the script in two parts:
  • The function for my import script
    • Split source Field and get first and third item (indexed starts at 0)
    • sField.split("-") will return a list of three elements ["XXXX","YYY","PLANTN"]
    • If use [n] to get the nth element
    • We use "+" operator to concatenate
  • Test code for sample source Entity field and source Record. I have used print to show the result in the bottom panel.

What is the objective?
  • Run the script
  • Fix syntax errors
  • If we have unexpected result, fix the script and try again
  • Everything works? Copy & Paste the function definition into your import script and run a final test with the entire source file.

In the example below we get a syntax error when we click Run: we forgot to close the string with "
Once I have fixed my script I get what I wanted: 0000_PLANT1
You can also save your script and share with other users if you wish:
This can be useful if you want to save all your code snippets in case you need to reuse them. Once it is saved you can always access to your Dashboard and open your script:

Some additional notes
As always, don't forget FDMEE uses Jython so it may be that some functions or coding solutions may not be available for Jython and they are for Python. In theory you can use Python 2.5.1 based code in your Event and Custom Scripts. However as explained in previous posts, Import Scripts uses Jython 2.2.1.

Regarding SQL, let's say that you don't have access to any SQL tool and you cannot test the SQL query you are going to use either in your BefImport for Open Interface Adapter or in a mapping script. There are also Cloud IDEs for SQL like http://sqlfiddle.com. You can define your own database/tables online and import some sample data there.

Once you get into advance mode you may prefer to use Eclipse or Notepad++ but at least you know there are other options to speed up your learning process and implementation time.

Other Cloud IDEs? Sure. And it's now time for you to select your preferred one. In this site you have a good review of different ones.

Enjoy!