Saturday, June 20, 2015

Ignoring data in #SQL Scripts

Greetings from Miami :-)

Sometimes we want some source data to be ignored during the export step but being kept in FDMEE.
There can be many reasons that take us to that decision:

  • We may want not to stop the process when unmapped members exist (automap)
  • Invalid data coming from your ERP that you want to import but not to export
  • We just want to use it as audit information
  • ...
How can we achieve this? there is a special value you can set in your mappings as target: IGNORE.

Today I want to provide you with a bit of insight about how ignoring data works, especially when you want to create a SQL mapping script to set IGNORE as target value.

As usually, let's see an example.

I will be direct, I want to ignore GL data for Building account. In order to make it simple, I can create an explicit mapping as follows:
Having the mapping rule as explicit ensures it will be executed at first place before any other rule.
What happens when data is imported? We will see that the target account has been mapped to IGNORE:
Although we can see the data in FDMEE, that line will not be included in the DAT file for my target application.
We all know that FDMEE is really cool for filtering and searching. Actually there is a new feature to show in the grid ignored data only:
Even if you can use Query by Example to filter values in the grid (just type IGNORE in any target column) with this feature you will see all ignored data regardless the dimension you had the mapping for.

That's easy, right? there is not so much to add, or is there?

Is "ignore" the same as "IGNORE"?
Like Saint Thomas said, if I don't see it, I won't believe it.
Target value for ignore mapping is not case sensitive, at least when using SQL Server with no case sensitive collation :-)
In any case, as best practice, I recommend using upper case always.

What's happening in TDATASEG table?
Besides having the corresponding target column as "IGNORE" (ACCOUNTX, ICPX, etc.), there is a flag in TDATASEG which indicates the status of the row. That column is named VALID_FLAG and can have three different values:
Actually this is the column the Show option uses to filter data in the grid.

Can I set target value to IGNORE in a SQL mapping script?
Sure, let's see an example where data must be ignored if amount is less than €5000:
We run our Data Load Rule after we create our mapping but we get an intersection check report (good I enabled it), why? how can this be working properly with the explicit mapping but not with the SQL one?
The report above is basically saying "account IGNORE is an invalid member in HFM" (you would get an export error when loading into HP/Essbase)
Time for troubleshooting, one of my favourist task. First thing I would do is to select Show > Ignored data:
Mmm, no data is shown... was it actually ignored? It seems it wasn't.
What is FDMEE doing when applying the mapping? I always suggest to set log level to 5 and see what's happening there. Best way to learn.
As you can see above, mapping was correctly applied but we don't see anything about the VALID_FLAG column. Actually I would expect this to be set as "I". Let's check in the database:
Hold on, so target account is set to "IGNORE" but valid flag column is set to "Y". How can this happen? We do expect value "I".
I already raised this a bug and luckily this will be fixed in 11.1.2.4.100 (coming soon).
However, if you want to have SQL mappings to ignore data then you can create your SQL script as follows:
After re-applying the mapping we can see how valid flat column is now set to "I". Good! we have what we wanted!
And that's making it work as expected:
One of the main features of #SQL Scripts is that you can update more than one TDATASEG_T column in the same script (that's the table where mappings happen. So basically SOURCE > TDATASEG_T > Mappings happen > Data moved to TDATASEG). Therefore we have used that capability for our workaround :-)

Quick update: if you want to avoid updating VALID_FLAG in the mapping script, you can have an event script AftProcMap in order to update that column in TDATASEG when any of the target dimensions are "IGNORE".

Enjoy!

Wednesday, May 13, 2015

Enclosing double quotes or apostrophes in your data? Don't worry, be happy!

Hi folks,

Is 11.1.2.4 your first contact with FDMEE? or are you coming from 11.1.2.3.x?
Why I'm asking that? because what we are going to discuss about today is something new in 11.1.2.3.530 and 11.1.2.4.

One year ago I posted about issues when your source data file had either enclosing double quotes or apostrophe.
First scenario typically happens when text/csv files are generated from Excel or other systems which configured to use "" as text delimiter.
Apostrophes may be used within account descriptions or any other description field extracted from your source system.

We can summarize my post and tell you that:
  • Double quotes were not removed when importing data
  • Apostrophes often made your import fail as single quotes (or apostrophes) are also used as string delimited in Jython
Oracle even published some documents for these issues:
  • FDMEE Script Samples (Doc ID 1613836.1)
  • FDMEE: Unable To Import File With Apostrophe In Account Description (Doc ID 1629889.1)
And now what? well from 11.1.2.3.530 these two issues are handled by FDMEE (PSU530 and PS4 have mostly same code) so no need to worry about any scripting to parse source fields anymore.
Let's see how it works in 11.1.2.4.

Importing a file with double quotes as text delimiter
Let's say we have the following file generated from an Excel file:
We have created a simple import format and data load rule having assigned the import format:
In order to make this example easier we have pre-selected the source file in DLR. In that way we don't have to select it when importing data:
As you can see we have assigned the import format to the DLR. This is not needed if you have only one import format for your location (file-base loads).
Time to import data. We can either execute the DLR from Data Load Workbench or from DLR page (even from a batch...). Results will be the same:
Data has been imported with no double quotes as text delimiter...and no need of import scripts...good!

Importing a file with apostrophe in description
What about this one?
We have defined a DLR using the same import format so no import scripts being used:
The source file has been also pre-selected. Let's import it!
VoilĂ ! data being imported without issues...and descriptions as well. 

BTW, If you were wondering, any of the source files have been modified, so FDMEE keeps your original file as it is :-)

This enhancement is a really good one we don't need additional import scripts to parse our data.

Finally, if you are upgrading your FDMEE from 11.1.2.3.x to PSU530 or PS4, don't forget to remove your scripts for that purpose!

Take care!

Sunday, April 12, 2015

Collaborate2015

Hi all,

I will be attending and presenting @Collaborate2015.

In case you wish to meet there just let me know!

Update 13th April 
For DRM customers, we knew FDMEE is was going to be integrated with DRM so we can maintain our mappings in DRM and import them into FDMEE (you may have been doing this with custom solutions)
It seems that Oracle is also delivering the integration the other way back...having DRM as target in FDMEE so we will be able to load metadata from our ERP systems into DRM using FDMEE.
To wrap up:
- DRM as a source for mappings import
- DRM as a target for metadata loading 
Cool!

Also, as FCM is getting more powerful, FDMEE-FCM integration will provide more capabilities to users during the month close.

For SAP integration, we will able to load metadata from SAP BW into our EPM apps. Why no SAP ERP? Basically metadata is too spread for SAP ERP so metadata can come from many different places whereas SAP BW metadata is easier to access. First step for a better future integration.

Internet Explorer 11 is now supported in 11.1.2.4. What about 11.1.2.3? we will have to wait until summer :-)

All this is planned to be released in the next PSUs.

And what about next release? 11.1.2.5? 12.x.x? although not officially confirmed, I would bet all my money on the second option. In that way, we would have EPM aligned with the Oracle application stack. When? 2016

Roadmap

  • Non-numeric data loading into Planning
  • Metadata loading from heterogeneous systems (files...)
  • Mapping review mode
  • Mobile functionality for drill-thru page

Please, not that this information is not officially published yet so do not use for making important decisions :-)

Monday, April 6, 2015

Mayday, Mayday, I cannot see the source columns in drill-through details page!

Often seemingly insignificant things make us waste time more than expected.
From my experience, issue below is one the most common for FDM users getting into FDMEE.
Customers sometimes complain about not being able to see source accounts when they drill-through from EPM applications into FDMEE.
Even if we try to select columns from View option, we don't see them:
Are they gone? maybe under the sofa? don't panic :-)
The reason is that FDMEE takes the column names from your import format so if they are not correctly setup in it you will not be able to select them from the View menu in the drill-through details page:
As a good practice, try to set column names for your import even if they have the same name as the target dimension. You can also use some meaningful name or alias.
Then when you drill-through you will see source columns as expected. Column name will show the name you set in the import format plus the dimension assigned in the import format. Now you have time to fix your next issue and forget about this one.
All this applies also to Excel Trial-Balance templates for both single and multiple periods. Documentation says:
"When you an Excel Trail Balance template, the template can contain one or more periods. When
doing a multiple period load, you need to create a dummy import format that indicates multiple
periods. If just a single data value is in the Excel file, then you don't need an import format"
But what is a dummy import format? empty one? If you leave it empty, then you will experience issue above.
In that situation, create a dummy import format (it won't be used anyway), set all column names to what you want to see in the drill-through details, and assign 1 to field number.

Enjoy!

Where was I? What have been doing?

Hey folks!

I have been very busy with my little baby, not sleeping so much, and becoming an expert on diaper changing :-)
Being said that, I hope you all understand why I was not blogging so often.

So now trying to normalize my life, one of the TO-DO bullets is to keep on sharing FDMEE stuff with all the EPM community.

As I always say, the show must go on!!!

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!