Adding Enhancements to FDM

Oracle Forums is a great place to share thoughts on Oracle products and to get help with difficult problems. The other day, a question was posted on how Hyperion Financial Data Quality Management (FDM) can be customized to ask a user for input and then further process the data. This particular thread is about adding email addresses for certain users whose information cannot be retrieved from Active Directory and therefore needs to be entered manually. Due to the large user base in this company, it was desired to create an automated solution.

This reminded me of a solution I created previously which could handle this type of request. This post will outline the steps to customize this solution and how it would be possible to tweak FDM to support the input process.

Important note: the following content shows an example of a process, there's no guarantee that this will work in your environment and if you decide to do any of this, it will be at your own risk. Tasks performed to manipulate the content of the FDM database should only be performed from consultants or developers who have a very thorough understanding of FDM and its database. Changing content in the tables can cause severe system failures and recovery will most likely not be supported through Oracle.

FDM by itself is not able to retrieve user input through a form. With a little trick, it is possible to create forms which will take input information, however, the entered information cannot be passed back into FDM without significant effort. This is where MindStream's FDM Enhancement Utility comes into play. It is a web application which can be configured to wait on certain requests and then execute tasks. This will get a little bit technical, you will need to refresh your HTML knowledge a little bit, but here's a blue print of how this can be done.

As a first step, let's see how we can create a form in FDM. We need to create a script which will open a pop-up window and add content to a HTML form which requests the user to enter his/her email address.

The script can look like this:

(it's a little tricky to embed this into the HTML code that FDM produces for the pop-up window, in order to understand the opening and closing of the pre and form tags you will need to take a look at the HTML source code)

When you run this script from the Script Editor (it only works from the WebClient), the following window will appear:

You can launch this form from a FDM Task Flow or by adding it to an event script which will prompt the user e.g. when launching the Import process. You will probably want to put in a test to check if the user already entered an email address.

After entering the email address, the user can now click the "Sign Up" button. Now take a look at the script again. The form action will pass the entered parameters to the FDMEnhancer web application and launches the executeTask.jsp. In the background the email address will be added to the FDM database and the successful entry will be acknowledged.

Wow, that was easy - but how does this work? Obviously there's quite a bit going on in the background. The FDM Enhancement Utility is a web application which can be configured to perform certain tasks. In our case, a task has been created to write the email address back to the FDM database. I will show you what's going on behind the scenes.

First, an administrator would launch the web application and enter logon credentials.

The first screen shows an overview of commands: Administration brings you to the screen where you configure access to the FDM repository, the Tasks contain the different enhancements which have been defined and that can be launched from one of those custom web forms in FDM.

What we want to do now is create a new task that can handle the email address collection process.

Here's what the entries related to (keep in mind that this is a web application which is based on Java. There is obviously a program in the background which takes these input parameters and then executes the process):
- Task name: this description will show up on the overview screen
- Action name: this is the parameter that will be passed to FDMEnhancer in order to trigger the correct process
- Parameters: 'username' and 'email' are the input parameters to the email function, additional parameters can be added
- Command Type: select SQL or Batch script etc.
- Command: this is what will be executed (parameters will be replaced at run-time)


This should give you a little insight into what MindStream's FDM Enhancement Utility can do. Thanks to FDM's extremely valuable API, things like this can be embedded smoothly into a process. Feel free to post a question or a comment to get in touch.

Purpose and Benefits of Drill-into-Anything (DIA)

What’s in it for you to read this?	In this blog you will learn what inspired us at MindStream Analytics to create this brand new source adapter for FDM. I will also show you a couple use case and as we are going along with them you will understand the benefits that Drill-into-Anything can offer to your organization. On March 10, I went to the Central States OAUG conference in St. Louis for a presentation about this topic. You can download the slide deck here (link)
A quite common scenario during the Financial Close process	The following example describes a scenario which is very common during the Financial Close (link to FCM) process. We are assuming that the latest GL and statistical information will be supplied in form of a flat file which IT extracts twice daily from the General Ledger system. The data will then be imported by a financial analyst into Hyperion Financial Data Quality Management (link), mappings are being validated and the data will finally be loaded to Hyperion Financial Management (link) and consolidated. A few Financial Reports (link) are available via Hyperion Workspace (link) so the data can be reviewed by the responsible manager.
… will it ruin your day?	Today is the last day of the Close Process, the quarterly reports are due to be released tomorrow. Everything went well and you are ready to leave and meet with your best friend who invited you to go to this great event you’ve been really looking forward to for a while. You’re just about to put your monitor to sleep when the phone rings. You find out that a regional manager is questioning the correctness of one of the reports. [What you are thinking right now is bleeped out in this blog.]
There’s something wrong – can you find it out?	There is one number in the system that is incorrect, Accounts Receivable are missing $20K. This is “an extremely important transaction” (and the intensity of the manager’s voice underlines this – probably it’s related to a bonus paid if the results were $20K higher, but the transaction doesn’t even exist). Well, it’s kinda YOUR job to make sure the reports are 100% correct, so you better get started.	MindStream’s Drill-into-Anything (DIA) Adapter extends the Drill-Through for Hyperion Systems all the way in the source system(s) – any source system. The systems are completely integrated.
Of course you can…	You know how you can find it out: you ask for the exact intersection where the incorrect number was found and then you drill back with the manager, maybe get another analyst or accountant involved. Then you drill to the lowest level in the Financial Report and back into FDM. In there, you first review the maps of the transaction in question and if they are correct go further down to the source data which was imported.
…but how long will it take?	The details within Hyperion are not sufficient to find the root cause for the discrepancy, so you will need to go back into the ledger system. The Accountant you asked for help has access to the source system, but unfortunately this exact system is the one which requires several transformation to get the source data into the format that is needed to load it to Hyperion (at least based on the process that your consultants implemented in the very last minute). Great, so you will even have to go through some spaghetti-like mappings logic. After collaborating with the whole team and even getting a helpful software developer involved, you are able to decipher the SQL code that leads to the intersections in the source system and you can log on and review the actual transactions which the incorrect number was comprised of.	You are just a few clicks away from discovering the root cause of this discrepancy
Good news: the cause has been identified	Looking at the transactions, the manager notices right away that reversing entries for 2 transactions of $6,400 and $13,600 have been entered. The intern must have misunderstood the directive! The accountant removes the entries. Now the data from the GL system can be reprocessed.	Yes, you found this out with DIA – within a couple minutes
But how do we reprocess the data if we can’t wait until tomorrow	As the data from the source system is being transferred to FDM via a flat file, we need to get IT involved – but they all went home already. The Service Level Agreement gives the on-call support rep 2 hours. Waiting around now, call your friend to cancel, maybe you can join the event for the last 10 minutes or so.	Besides drill through, DIA also enables you to pull real-time data from your source system into FDM. Click 1 button instead of waiting on a new file.