HFM as a source, when Value is other than <Entity Currency>

Hi folks!

I'm currently working on a migration for a common ERP > EPM > EPM data integration using legacy FDM. Probably you have already found a similar situation in your organization or at some customers you were working with.

Making it simple, functionally actual data is typically loaded from our ERP system(s) into our consolidation system (1). Then after consolidation happens (2), the consolidated data is exported into our planning system (3) where budget and forecast are calculated (4). Finally, data calculated is exported back to the consolidation system (5). The following picture shows the process above:

AS_IS solution in legacy FDM
There were many different ways of implementing flow above using legacy FDM. The one my customer has is basically using integration scripts to load data from SQL tables/views into target systems using FDM. When moving data between HFM and Planning, Extended Analytics (EA) is used to export HFM data into a table so FDM can access it. In a similar way, when moving data between Planning and HFM, Business Rules (BR) are used to execute a DATAEXPORT that exports (a good pun) data from Essbase cubes into SQL table:

If you take a glimpse to the title you may think what I described above has nothing to do with it. Actually it hasn't but I though it would be also nice to go through the overall solution so you can have a better understanding about how new functionality in FDMEE replaces custom solutions implemented in legacy FDM.

TO_BE solution in FDMEE
We can clearly define 3 interfaces here:

1) MS Dynamics (one of the ERPs of Microsoft)  > HFM
FDMEE provides new functionality called Open Interface Adapter which lets you import data from any source which is not natively supported into FDMEE. This sounds as a good candidate for this interface :-)
You can find in ODTUG a webinar I delivered about Open Interface Adapter.

2) HFM to Planning
Before 11.1.2.4, we did not have any built-in functionality to move data between EPM applications. Luckily, EPM data synchronization was one of the best features coming with new release 11.1.2.4 :-)
Being able to extract HFM data at any level adds a great value to the tool and definitely makes our life easier.

3) Planning to HFM
Same as above, in 11.1.2.4, "everything" can be a source or a target. With still some limitations , FDMEE can extract data from Planning (actually Essbase plan types) in the same way it does from HFM.

Being said that, what about solution depicted below? does is it make sense?

The main goal is to replace most of the customization to give more control to the end-user as he will be able to create synchronization loads as needed by defining filters on source dimensions in the same way they would do in application forms for example (Ex: @IDESCENDANTS, .Base, etc.)

The only piece where we need a bit of customization is in the Open Interface Adapter as the open interface table (AIF_OPEN_INTERFACE) will be populated using a event script in Jython which runs a dynamic remote SQL query based on user-defined filters. End to end in one click!

And now the issue...

One of the requirements was to export consolidated data. For that we had to set the filter on Value dimension as [Contribution Total] (value aggregated to the parent entity). Just an aside, I strongly recommend visiting Thano's blog for a good explanation of Value dimension.

When running the data load rule we noticed that data was successfully exported to the staging file but there was an issue when importing it into FDMEE. As always log level set to 5 in Development:

A SQL exception was raised: EPMFDM-140274:Message - [FMWGEN][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated.

FDMEE exports HFM data into a DAT file located in the \data folder of our target application. Then this file is imported into FDMEE in order to be mapped and loaded into the target.

We can check that the DAT file has the HFM data load format. Entity is exported as Parent.Children pair and Value dimension is [Contribution Total] as expected.

The file exported from HFM is imported into FDMEE temporary table TDATASEG_T so the error seems to be related to some issue importing the file into this table. 

After some researching I got confirmation from Oracle. Value dimension is mapped to Currency. Typical exports are done at <Entity Currency> level which actually will export the currency code. Then currency codes are imported into column CURKEY of TDATASEG_T (and then into TDATASEG after all data is mapped)

Currently CURKEY is declared as NVARCHAR(10) in SQL Server and CHAR(10) in Oracle.

When importing currency codes into that column we don't have issues as they fit but in our case [Contribution Total] exceeds that size so we get the truncate error.

The workaround...

Although Oracle confirmed this has been fixed in PSU100 (most probably coming in early 2016) is good to know that you can apply a workaround at database level in order to perform your HFM exports as expected.

Basically we will have to alter column size. For SQL Server:

After applying it, we run our data load rule again, and voilà! our HFM data has been exported and imported into FDMEE:

And what happened in TDATASEG?

Value [Contribution Total] has been successfully imported into CURKEY column.

Enjoy!