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!

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.

Resources
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?

Enjoy!