Thursday, June 9, 2011

Access Hyperion Applications on Amazon EC2 from your local computer

In this post you will learn how to access Hyperion Workspace and other Hyperion applications located on an Amazon Cloud server from your local Internet Browser.

Important note: following the below steps at your own risk only. This setup was just a test to play around with the functionality and can cause serious consequences because it exposes your system to potential intruders and other undesired situations. Please consult additional resources to find out about the associated risks and how to mitigate them correctly.

Amazon Web Services (AWS) and its Elastic Compute Cloud (EC2) have found a lot of followers in the Hyperion space. A few of the many benefits are flexibility to start up multiple instances within mere seconds, usage as a service reduces costs, hardware maintenance through Amazon, save snapshots of images to test changes and revert back if needed, and many more.

Development of Hyperion applications on the cloud has gained a lot of trust, especially as it makes it possible to jump start an implementation while the IT department might still be in the purchasing and setup cycle. We've been using this in various situations at MindStream Analytics. A common approach to access servers on the cloud is via Remote Desktop connection, however, by default there is a limitation to only 2 connections at a time. With more than 2 developers or testers, this will obviously cause difficulties. A great way to get around this is to open up the ports for the web components of Hyperion to access applications via the Internet browser on the local computer and thus no need to use a Remote Desktop Connection.

Four configuration steps are required:
1) Create Security Group
2) Open ports for applications to be published
3) Launch instance and assign security group
4) Configure Windows Firewall to open ports

Create Security Group

Security Groups can be maintained on the EC2 tab. They appear underneath the Network & Security section.

Create a Security Group which you will be using for the Hyperion Server.

Open ports for applications to be published

In order to access applications on the Amazon Cloud from a local web browser or other client tool, the relevant ports need to be opened. This can be done as part of a Security Group. Switch to the Inbound tab where you will see the following screen.

As a first step we will grant access to the server for a Remote Desktop connection (RDP). Select "RDP" from the drop-down list, then click on "Add Rule" and "Apply Rule Changes". This will assign the rule to the Security Group and thus opens the RDP port for access from the outside world.

Now add two more rules: HTTP (port 80) and a Custom TCP rule for port 19000 (which is commonly used for Workspace). Our Security Group now looks like this:

Launch instance and assign security group

Now launch your Hyperion image on a new instance and assign the created Security Group during the configuration process of the Request Instance Wizard.

Configure Windows Firewall to open ports

Log on to the server and configure the Windows Firewall to accept connections at the required ports. This can be done via Control Panel > Windows Firewall. Click on Change Settings.

The Windows Firewall Settings window comes up. Switch to the Exceptions tab and click on "Add Port…"

Type in a meaningful name and the port number (protocol is usually TCP):

Verify that the Exception has been added.

This is all that needs to be done from a setup perspective. Go to the Instances screen and click on your running instance. The lower section of your screen lists information to determine the IP address through which the server is available.

In the example you can see above, the server can be reached via IP address If Hyperion Workspace is listening at port 19000, you can access it from any browser at .

Note: the instance I started up for this blog post has been shutdown again. Therefore, the above URL will not work (unless someone else is using the exact same server instance with the same address, has Hyperion running and opened the ports). Be aware that a setup like this has advantages and disadvantages. Especially if you are dealing with sensitive information on the cloud server, you should find out about the risks of using publicly available servers.

Feel free to contact us at MindStream Analytics to help you answer specific questions about your needs around Hyperion on the Cloud

Wednesday, April 27, 2011

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.

Thursday, April 7, 2011

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 processThe 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.

Wednesday, February 23, 2011

There is a need for a new solution: Drill-into-Anything

If you are interested in Drill back from Hyperion Financial Data Quality Management into any of your source systems, then you are at the right place to find out more about a ground-breaking solution called "Drill-into-Anything. MindStream Analytics has developed this solution to help analysts expedite tedious analysis and reconciliation processes.

Last year I was working as a sub-contractor at a very successful high-tech company in San Francisco and learned about a problem which will apply to many other companies as well. This was not a project of MindStream Analytics, but the consulting firm on-site asked me to take over as the data integration specialist for the last two months of an upgrade project from pre-system 9 to 11. While I was facing a lot of work on my side, compared to the short time frame to complete (converting HAL routines to ODI processes and building additional ODI and EPMA automation processes), the project's major risk was in a different area.

The original architecture included Hyperion Planning and Financial Management (HFM) as the target EPM applications which would be the source for various management reports. Financial Data Quality Management (FDM) was going to be the application which would take care of loading and mapping the data from E-Business Suite into Planning and HFM while also enabling drill-back from into the General Ledger system - utilizing the new ERP Integrator (ERPI) adapter for FDM.

I was very familiar with ERPI after leading an implementation with this brand new EPM component in a previous project. The solution sounded state-of-the-art and very promising to help the company improve their performance management processes. However, something was not right here: nobody was talking about FDM and ERPI anymore but drill-through was mentioned many times as a problem area.

Drill-Through not supported with ERPI and E-Business Suite version prior to 11.5.10 cu2

As I found out, the project had required a significant change in scope as the original design was not feasible due to one incompatibility. The version of existing E-Business Suite (EBS) implementation was one sub-release below the required version which is supported by ERP Integrator (11.5.9 instead of 11.5.10). Because of this, FDM - as the enabling solution for the highly desired drill-back - was replaced with Essbase Studio. ODI was supposed to populate the tables to feed Studio's drill-through and in parallel integration processes loaded data to HFM and Planning. As drill-back from Planning and HFM was not possible without FDM, a separate Essbase application was created - the Studio application. This way drill-through to EBS data was possible, although the data was maintained in a separate database.

While it becomes obvious that this solution was not as smooth as one expected (multiple load routines, drill-back is not directly possible from HFM and Planning), it seemed possible. However, the data validation effort to make sure that the Reporting cube would exactly match the consolidated data in HFM did not succeed within time and budget.

Projects aren't always successful, but if this case have happened today, I am sure the outcome could have been changed. The reason I am so confident about this is because of a solution named "Drill-into-Anything" which was created by MindStream Analytics. Drill-into-Anything (DIA) is a source adapter which can be used as an enhancement to FDM. It is quite similar to ERP Integrator, however, it has one major benefit: it is not limited to certain versions of GL systems as the data source.

DIA offers drill-through into any source system, any version.

With Drill-into-Anything, it would have been possible to leave the original architecture at this client as it was and only ERPI would have to be replaced by DIA. The need for an additional Reporting cube - and the associated effort of synchronizing the data between this Essbase application and Planning and HFM - would have been eliminated.

If your company is facing a similar situation, please feel free to contact us.