Loading Cell Texts into HFM from Flat Files

Definitely, one of the topics that I had in my TO-DO list...

After trying to organize my daily life, I have finally found a good opportunity to work on this. 

Before I start I would like to say thanks to my colleague Michael van der Merwe. He had a challenge and I got the opportunity to collaborate with someone that helped me a lot when I started fishing in the early Classic FDM times. If you come from old Upstream/FDM, I'm sure you know this guy.

Before we dive into this fuzzy concept I think I should briefly describe what I will be showing today so we are all in the same page. 

As you may know, HFM allows adding a text description to any valid cell. 

This description is known as Cell Text and, luckily, FDMEE can load it by attaching to data lines something called Memo Item.
However, out of the box, FDMEE only allows adding memo items manually once data has been imported. Today we will focus on how to import memo items from the source (file or any other) and loading them as cell texts into HFM. 

That's the goal :-)

Memo Items: attaching text descriptions to FDMEE data

I'm sure you have asked yourself (at least once) what is that post-it icon in the very first column on your Data Load Workbench grid. Have you ever clicked in the "--" link of any data line? If you do so, you will see a new window popping up. Welcome to memo items :-)

As we have already shown in this blog, all is stored in the database. So the best we know how it is stored, the best positioned we are to control it. It sounds interesting, doesn't it?

Lets' then have a look to what happened in my FDMEE database when I just clicked Add (all db screenshots are for MSSQL):

Picture above is self explanatory but I would like to highlight some key points:

• Memo items are stored in table TDATAMEMOITEMS
• Memo items and data lines are linked through the column STATICDATAKEY
• I don't know why but column MEMOITEMKEY reuses sequence used by Data Load Rules (I can see potential issues when adding memo items and data load rules at the same time. Maybe not a common case but something that can really happen)

Once that we have added our memo item, we need to type a name and a description. Optionally we can attach up to 10 documents. At this point you must know that current functionality does not support loading of attached documents to the HFM cell text. In any case, you can use this feature in FDMEE in case you want to attach any file to data lines (ex: invoices)

What happens when we click Update? the existing memo item is updated with details entered:

Are you curious? Of course you are. So if you have a look to the columns where attached documents are stored, you won't see any path, just an identifier. To what? As many other files in FDMEE, the information for attached documents is stored in table TDATAARCHIVE:

Loading Memo Items as Cell Texts

To show this functionality I don't need to do any magic, just to enable one target option and click Export.

In this case, I will enable Cell Text loading at data load rule level. Remember that we can override global target application options at data load rule level if needed. When you see the option value is yellow-cream highlighted, then the global option is being overridden:

Now click Export, get all the gold fishes and have a look to your HFM data. There it is, a non really well formatted cell text has been loaded from FDMEE.

Note: HFM allows having multiple cell texts assigned to the same data intersection (Enhanced Cell Text). This can be done by using different Cell Text Labels. Unfortunately FDMEE is limited in this way as it only loads cell texts to [Default] label.

If you try to have multiple memo items to the same data intersection and you export them as cell texts, you will something like this:

Therefore if you have multiple labels and load through FDMEE, then you need to follow another approach. I will not cover this today but as a tip you should know that you can load cell texts by having a section in your DAT file labeled as !Description. In this picture taken from HFM documentation you can see syntax for enhanced cell texts: 

If a load using an append mode is run and new cell text is added to an intersection that already has cell text, the old cell text is replaced by the new cell text and not appended.

How are Cell Texts actually loaded?

Cell texts are loaded through the HFM adapter API. From 11.1.2.4 the adapter scripts are available in Jython so if you open script HFM_LOAD.py you will see a function to load cell texts:

Adapter scripts can be found in %EPM_MIDDLEWARE%\EPMSystem11R1\products\FinancialDataQuality\bin folder.

Have a look to the entire script and you will better understand how it works. If you are not happy with how the cell text has been formatted (author...) then you can change that code in the same script (not supported...)

If appropriate log level is configured, the FDMEE process log will show all actions related to cell texts:

Loading Cell Texts from Flat Files
Now that we better understand how cell text loading from memo items work, it's time to address common requirement to import memo items from source.

As an example we will import the following delimited file:

As you can see I have added an additional column with the value for the memo item (Memo Title:Memo Text). To keep it simple, I have entered NoMemo for data lines not having cell texts.

If we think about the high level solution, our plan would look as follows:

1. Import memo items into FDMEE column (typically attribute or description columns)
2. Add memo items to the database
3. Use the standard cell text loading option

That sounds like a good plan then, doesn't it?

Importing Memo Items into Description column
We are all familiar with Import Formats, right? Descriptions and Attribute columns are not available when you create the import format but they can be added later by clicking Add button:

The image above shows how column 14th has been assigned to Description 1 field (column DESC1 in TDATASEG)
After importing data we can show description column to see text imported (View > Columns menu):

First step was easy. As you can imagine, the art & science is entirely in second step.

Creating the Memo Items Behind the Scenes
I always like to speak about customization from the WHAT-WHERE-HOW standpoint. We already covered the WHAT in step 1. Let's go through the other two.

This is a common customization where you would probably start with the HOW without thinking about the WHERE until last minute, just when you start getting unexpected results :-)

And this is how I'm going to approach it in this blog entry.

The HOW...
We already showed what was happening in the database when new memo item was created. Now it's time to emulate via scripting what FDMEE does when actions are performed in the UI:

1. Insert into table TDATAMEMOITEMS

We just have to execute an INSERT statement into table TDATAMEMOITEMS. The easiest way is to execute INSERT INTO table VALUES (columns) SELECT columns FROM TABLE

2. Update columns HASMEMOITEM and STATICDATAKEY in data table

We need to update DATAKEYs that have memo items assigned:

3. Update sequence value in table AIF_SEQUENCES

The last step is to update the sequence used to generate the MEMOITEMKEY. We can use the number of memo items inserted to update it:

The WHERE ...

One of the first thoughts would be probably to insert new memo items after data has been imported into the FDMEE staging table (TDATASEG_T). This happens in the AftImport event script.

That solution is actually working with no errors but then you get something unexpected when trying to view the memo items in the data load workbench:

Memo item has been added but no details are shown. What do you do next? Go to oracle community and ask? Come on, don't be shy and use your SQL skills:

Yup, memo item is not in TDATAMEMOITEMS but it was inserted indeed. Ok, my SQL skills were a good starting point but it's time to use my log auditing skills: 

After searching text "TDATAMEMOITEMS" we notice that there is a DELETE statement and...voilà! it is executed after AftImport event script. 

Does it mean that the memo item was actually inserted but then deleted because DATAKEY assigned did not exist in TDATASEG yet? Elementary, my dear Watson. Data is copied from TDATASEG_T to TDATASEG table when mappings happen. 

Mmm... then going back to the WHERE, we need to perform the SQL operations when data is already in TDATASEG... and that is? AftProcMap event script, simply after mapping happen.

Let's give a try and do some reverse engineering from HFM to see the detailed process:

Our "custom" FDMEE process log shows results of the 3 SQL operations:

What happened now in the database?

 And what about FDMEE? 
We can now see memo items details as well :-)

A known Bug

Something you need to know: Cell Texts and Data Protection are not the best friends. If you are using Data Protection feature, your cell texts will not be protected when loading data in Replace mode. The workaround would be to extract the cell texts before loading data and the loading them back after data has been loaded into HFM.

Update 11/04/2017: Oracle has released a PSE to fix this issue (Patch 25720138)

Loading through Excel TB Templates

I don't want to leave this post without performing one more test. In old Classic FDM times, we could import memo items from Excel TB files. There were two additional metadata tags M1 and M2 to import both memo item text and description:

So I had to test this in FDMEE...unfortunately this is not supported in FDMEE

Conclusions

Cell texts are very useful to keep additional information to your HFM data intersections. This new information may be available in your source data so being able to load it through FDMEE it's definitely a +1 

Unlucky we don't have an OOTB functionality to import them but we do have a nice way of extending FDMEE via scripting, and this is what we did!

Enjoy!

I can't believe my eyes! that script has a hard-coded password!

Hi folks!

Raise your hands! How many of you have seen or used hard-coded passwords in FDM Classic or FDMEE scripts? When I say passwords, I mean all type of passwords but especially database passwords.

If you are still reading this post, most probably... you have!

Hopefully what you are going to read below can help you to avoid showing the world how unsecured you scripts are :-)

Why do we need to use passwords in scripts?
There are many situations where you would need to use passwords in a script.

For example, you may want to open an Essbase connection to check outline members.

But among all of scenarios, probably the one you are more familiar with is opening database connections.
Do we really need to open a database connection from a script? There is enough material to write a new post about this topic so I will focus in situations where you really need it.

Why don't we want them to be hard-coded?
Code is not pretty if you can read a password...
Administrators can get furious...
Password can be context/environment dependent...
Change the password, change the script(s)...
See the script, see the password...
I don't think it complies with any security policy...
bla bla bla

Our starting point
Maybe you don't know what a hard-coded password is but I'm sure you have used them.

Let's see an example:

As you can see when I need to open a JDBC connection with a database, I must know:

• JDBC connection string
• DB user
• DB password

I'm going to use the FDMEE database as an example. However it's quite important to remark that most of the times you don't need to open a connection with the FDMEE database as it's already opened when the script starts. 

In my case, I need it. Why? it's a long story but let's summarize it...the database has some custom stored procedures returning cursors which can be executed only through callable statements as they are not supported using standard fdmAPI functions like executeDML. Don't ask me why using these stored procedure. Somebody else built it due to specific requirements and we were forced to re-use them. Simply that.

Multiple approaches to avoid hard-coding
This is not new in FDMEE. We already had to open database connections in FDM Classic.
There are many approaches to avoid hard-coding passwords:

• Use a System Environmental Variable to store the password and read it from the script
• Use encrypted configuration files to store passwords
• Encrypt password and decrypt in the script
• Use db links/linked servers from FDMEE database to source databases
• ...

Any of the solutions could easily suffer code injection. What's that? For example, if I have access to the script I can insert some code to write the decrypted password into a file. Even the solution I will show below can be "injected". 

In any case, if your require one security extra level, you can always implement a Java class to perform secured database connection and use it in your FDMEE scripts.

Today I will focus only in a clean solution to avoid hard-coding passwords in a very flexible way.

1st Step - Storing passwords for database connections in ODI

Let's be clear:
FDMEE uses ODI > ODI stores database connections in Topology > Let's use ODI!

As you can see we can store any database connection in Topology, either through ODI Studio or ODI Console (web). You may want to connect to a data warehouse to get data or export your Planning/Essbase data into a reporting system.

Luckily, if we are going to open a connection against FDMEE database, we won't need to create it as it is created with default installation. However any other connection can be created as needed.

ODI uses its own encoding/decoding so why not taking advantage of this? By inserting the password in ODI topology, we are getting them already encrypted and stored somewhere. 

But where is the password actually stored? 

FDMEE database has a set of tables prefixed by SNP for the ODI repository. Among all these tables there is one which stores the physical connections configured in Topology. This table is SNP_CONNECT.

If we run this simple query then we will see the encrypted password:

What do we now with that password? Can we pass it directly to the database connection? No, that's the answer.

2nd Step - Decrypting the Database Password

From my ODI times (I still do a lot with ODI but TBH a lot more with FDMEE) I used to play with the ODI SDK (Software Development Kit). After some investigation I found that the SDK API for ODI 11g (the one FDMEE uses so far although ODI 12c has similar ones) has a class called com.sunopsis.DwgObject which has a static method (can be easily called without creating any instance of the class) called snpDecypher. This method accepts a parameters as a string (encrypted password) and returns the decrypted password.

You can easily see it by opening odi-core.jar file with Eclipse:

Let's give a try!

Testing the solution with a FDMEE Custom Script
I want to keep things simple so I'm just going to show s Jython script which decrypts the password stored in ODI repository. Besides, it gets the FDMEE JDBC connection string and database user from System settings in FDMEE. Another option would be to get JDBC and USER also from the ODI repository :-)

As you already know, one of the strength points of Jython is that we can easily use Java classes to extend FDMEE functionality and decypher our database password:

       decryptedPwd = DwgObject.snpsDecypher(encryptedPwd)

If we run the custom script through Script Execution in FDMEE:

Voilà!

I hope you found this post helpful. It doesn't matter the method you use but remember...

No hard-coded passwords any more!!!

Universal Data Adapter (UDA), direct DB integration - Part 3

In the last two posts we have analyzed the new Universal Data Adapter (UDA) thoroughly. Now it's time for the final part of the series: importing data from our source database table and view!

If you haven't done already you can visit the other two parts:

• Part 1: Introduction to UDA, initial ODI configuration
• Part 2: Configuring source system and source adapter in FDMEE

As any other source, the UDA needs the following artifacts to be configured in FDMEE:

1. Import Format: maps UDA columns to dimensions
2. Location: data entry point 
3. Data Load Mappings: conversion rules for source data
4. Data Load Rule: data extract definition
5. Source Period Mappings: period filters for data extract

1. Import Format
The import format is where we will link our source columns (imported from the source adapter page) with our HFM dimensions (including any potential lookup dimension defined in FDMEE)
As usual, we have to select the source system and the source adapter:

Before going through the details of the import format I would like to highlight that, in our case, source tables were designed to match our FDM/HFM dimensions. Maybe in your case you have to combine source columns in order to get the source value into FDMEE.

What can we select in the source columns?
We can select any column available in the UDA. In order to make column names understandable for anyone, the import format shows the "Display Name" property setup in the source adapter:

What can we select in the target dimensions?

By default you will see the list of dimensions of your target application including amount and lookup dimensions.
In the case you need to concatenate multiple source columns into one target dimension you can add the target dimension again. Each source column assigned to the same target dimension will be concatenated using the concatenation character set in the import format definition:

I'm missing some things here:

• We cannot select Currency dimension (Oracle confirmed that will be available in PSU200)
• We cannot select description columns (DESC1 and DESC2)
• We cannot select Attribute columns

For description and attribute columns the only workaround is to adjust the ODI interface generated by FDMEE. However as I already explained in previous post, this change would have to be replicated in every environment as we won't be able to export and import the ODI objects.

Back to the import format configuration, we map source columns to target dimensions for each adapter:

Regenerating the ODI Scenario

When we create new import formats or we change the columns map, we need to re-generate the ODI Scenario (FDMEE will execute this scenario when you click execute the DLR)

If everything runs fine we will get a success message. However this wouldn't be exciting so we got an error:

We troubleshoot the issue by starting with aif-WebApp.log:

I don't see any error except an issue with the interface but that could be normal because FDMEE just generates the ODI Scenario (it does not update the interface). And if you review the interface generated in the package, it doesn't have any reference to the source table (just variables and hard-coded values)

[2016-02-18T14:23:29.948-08:00] [ErpIntegrator0] [NOTIFICATION] [EPMFDM-140274] [oracle.apps.erpi.model] [tid: 22] [userId: <anonymous>] [ecid: 00iYe^P7VpJFw0zsRG007z2FtVU1zYIxL0000NS0000g1,0:1] [APP: AIF#11.1.2.0] [SRC_CLASS: com.hyperion.aif.odi.common.util.OdiConstants] [SRC_METHOD: logDebugInfo] Message - Issues: LKM is set for SourceSet or Staging Area but TargetDatastore column mappings do not reference any columns from a SourceDatastore of Dataset Default. Either delete SourceDatastore(s) or add source column references in one or more TargetDatastore column mappings for Dataset Default.

In fact, if I see the status icon I can see a green tick which means that the ODI scenario was successfully generated. We can even go to ODI and see it:

Weird. I saw this issue randomly happening in some projects so I decided to run the re-generate again. Now it worked. Don't ask me why, just be happy like me.

After re-generating the two ODI scenarios we are ready to create the other artifacts:

2. Locations

Nothing special to remark here, we just assign the import format as we don't have any other artifact (logic group, check group...)

3. Data Load Mappings
I won't cover mappings in this topic. I just want to execute the DLR and see source data in FDMEE :-)

4. Source Period Mappings

The source period mappings define the values for columns Period, Period Number, and Year. These values will be passed as parameters when we set our DLR with explicit period mapping type.

We can define multiple calendars and configure adjustment periods if needed.

Source period mapping are defined at source system level. Therefore if we have more than one table for the same source system and they use different calendar, we will have to configure multiple calendars. Calendars are then assigned to the data load rules as shown below.

Update (26/Feb/2016): If you have only one period column with alphanumeric values like JAN-16 you will need to enter those values in GL Period Column and classify that column as Period Class in the source adapter page. Then this column will be used as a filter when running the data load rule with explicit period type. Tested and working in a customer :-)

5. Data Load Rules

If you are familiar only with file-based loads you will see some differences in DLRs for the UDA:

We select the calendar defined for table SAMPLE_DATA and set period mapping type to Explicit.

We defined a parameter for the source adapter. This is used to filter data by column ERP_SOURCE. In order to show that works we have added a new line to the table with a different value than the value we want to extract data for:

Time to execute the DLR...3, 2, 1, go!

It's important to select a period or range of periods (UDA supports multi-period loads) with data in the table, or in other words, we have Period, Period_Number, and Year columns both correctly populated in the table and configured in the source period mappings.

Once is finished we navigate to Data Load Workbench and:

Voilà! our source data has been successfully imported and filtered.

Time to test the Oracle View. Will this also work like a charm?

For Oracle adapter, we defined a LIKE condition on column ERP_COMPANY:

Again we have added a new line to be filtered:

Running it...and no data imported!

What did I do wrong? Let's have a look to the FDMEE process log. I can see the SQL query executed. Actually I can't :-)

To troubleshoot source adapters you will have to visit ODI operator (either in ODI Studio or ODI console)

It seems that we forgot to add the % sign in the parameter value so new one value is BL% which will extract all data for companies starting with BL:

Let's execute it again:

Now it's time for you to explore other options.

Customization of UDA
There are many scenarios where our source adapter needs to be customized. As opposed to the Open Interface Adapter, the ODI package and interface are generated from FDMEE. This has some drawbacks:

• Any customization performed in the ODI objects will be deleted when we click on "Generate Template Package"
• Also, it will have to be replicated across environments

For reason I would prefer to customize on the source side. For example, if we need to join two tables, we could create a SQL view instead of using tables as a source.

At the moment, I can only think about customizing the interface in order to import data for descriptions and attribute columns but this is something that Oracle will probably fix so I wouldn't worry.

Migration to other environments
When we talk about migration, LCM has been enhanced a lot so most of the FDMEE artifacts can be migrated right now. But what about ODI objects?

• Topology: the topology needs to be re-created manually in the target environment
• Designer Objects: the packages/interfaces/scenarios need to be re-created from FDMEE in the target environment once LCM migration is done.

Why? basically, as already said, due to ODI master and work repositories having the same internal id across environments.

Hopefully in the future, Oracle adds a button "Generate all ODI objects" :-)

UDA or OIA?
And the key question...now that I have the UDA, should I use OIA for my database integration?
My answer is that it depends on your requirements. In a straight forward integration you wouldn't need OIA. However, if you have a complex logic for data extracts, you may prefer OIA instead of UDA.

What could be enhanced?
Of course, as a new functionality, the Universal Data Adapter could be enhanced. From what I saw, it would be great if we had the following functionality besides the one I said above:

• Filtering capabilities. For example, we can't add a filter to extract data having a column value different than the parameter we passed. Also we cannot add complex nested filter conditions.
• We cannot select import mode. It is always imported in replace mode.
• We cannot use import scripts. If we need any import operation on source fields that would need to be done in a SQL view.
• Being able to apply SQL functions on source fields. These functions would be available in the import format as expressions.

Can you think in any others? I'm sure so feel free to add your comments!

With this post I'm done with discovering the new Universal Data Adapter. At least until I find something interesting to show ;-)

Update 22/4/2016: it seems that table/view names must be in upper case. Otherwise the table import definition fails.

Enjoy!