Wednesday, October 21, 2015

Translating entities to multiple currencies during consolidation

HFM typically does currency translation when the parent entity and the child entity have different currencies. A hierarchy for a division may go up through several levels before translation occurs. The problem is users want to see the lower level entities in the translated currency even though the hierarchy says no translation should occur.

HFM offers users the manual ability to set the value dimension to the desired currency and then use an action aptly named "Translate" to run the translation. But users must remember to do this every time the data changes. Doesn't really work well if users forget.

A traditional approach, going back to HFM's predecessor products, is to create an alternate hierarchy, full or partial, with new parent members that are all the desired currency. Like this:

This worked well, as users would just consolidate both hierarchies after data changes (usually have a single "dummy" parent at the top that consolidated all hierarchies underneath). But there's a big problem in that users have to change the entity, like going from RegA1 to RegA1_KYD.

So, what to do? A better approach is to create a parent entity with the desired currency and then add all needed entities directly to the parent - no hierarchy, no new entities. Like this:

Can still use the single "dummy" parent to make consolidations easy. With this approach, the consolidation of KYDTranslations forces the translation of each child entity to the desired currency. To avoid any confusion, change the consolidation rule to not run if the parent entity is KYDTranslations, as any number there is meaningless and not needed.

Best of all, the users don't have to change entity labels - they can view RegA1 in either EUR or KYD just by changing the value dimension.

Sunday, August 23, 2015

Migrating HFM Metadata to DRM

In Classic mode HFM really doesn't care what is used to manage the metadata; Notepad works fine, for example. But DRM - Oracle Data Relationship Manager - is a great choice that many companies have or are moving to. There's a workflow process for requests, audit trails, formulas (i.e., if account starts with a 1 then tag it as an asset), etc.

People ask: when setting up DRM to create the HFM metadata load file, what's the best way to check if it's right. If the HFM version is or higher, HFM can easily provide that info. Here's what to do.

1) Set up a new HFM app and load the current metadata. The app does not need rules, data, lists, etc. but it will need security classes.

1a) As this will be a reiterative process, plan on reloading the real metadata before each DRM load to re-establish the baseline for comparisons.

2) Load the DRM file into the HFM app as a replace.

3) First, if the syntax is bad, it won't go in - use the log to find out the problem(s)

4) Once the file loads, check and see what changed. Ideally, nothing changes. To check, go to Task Audit and click on the link for the metadata difference file. If something changed, it should show up here.

5) Make DRM changes and repeat the steps starting with 1a) until there is a clean load with no changes.

Moving to DRM is a really good thing to do. And it not only works with HFM but Planning, FDMEE (great tool for managing mappings), ERPs, data warehouses, etc.

Friday, July 17, 2015

HFM patch released

HFM patch 100 was released this week. Here's a quick and partial list of new features:

  • Application Creation wizard
  • Dashboard for monitoring system status - Exalytics platform only
  • Ability to run on Solaris for Exalytics platform
  • Print entity details screen [finally!]
  • Application year update
  • Right click options on tabs for close others, close all a la Workspace tabs
  • Open and work with journals in Smart View (requires Smart View

There is also a healthy list of defects fixed.

Happy patching!

Thursday, July 9, 2015

Oracle EPM PSU

The 700 patch for was released on July 1. The big thing that comes with this patch is IE 11 support. For HFM, there were a couple of bug fixes, including one that addresses Smart View performance when comparing to

Sunday, June 21, 2015

HFM's Equity Pickup Module

One of the somewhat newer (well, a few years ago) and lesser used features is the equity pickup module. I've set it up twice and in the middle of a third time, so let's talk about what it does and how.

From an accounting standpoint, when one entity owns another, you want to pull the net income from the owned entity into the owner. During consolidation, when the two entities meet (first common parent) the entries need to eliminate. That part is easy - standard intercompany elimination. But how to do the equity entry?

Before EPU, we'd do a few different things that all had problems. One, you could build the hierarchy where the owned entity is a child of a parent that is also the parent of the owner and use a consolidation rule to just consolidate the net income into the investment. But it was a really weird looking hierarchy. Two, the other approach is using GetCell formulas to pull the numbers around. The problem was order of execution - how to get each entity to calc in the correct order.

What you really want to do is push data to another entity. But HFM is great at pulling but almost never pushes - the exception is during a consolidation rule where you push data from [Parent Total] to [Proportion] and [Elimination].

So, the equity pickup module comes in to fit the bill. It handles both key pieces that are normally missing: order of execution and push. Technically, HFM is still pulling, but it looks like a push.

So, what happens?

For the setup, we'll need some metadata, data, and rules. The metadata is nothing special, other than having the owned and owner entities flagged as intercompany. For data, load in, using the system accounts, for shares outstanding and shares owned by owner. Note: this data comes in by the OWNED entity and says who OWNS them. TIP - assume shares outstanding is 100, so that everything can be loaded on a percentage basis.

For rules, there is a special subroutine (EquityPickup) that gets executed during the process. The rules basically pull data from the owned entity, apply the percentage to it, and put it in the owner entity. This last part uses standard HS.Exp.

After that, go to the EPU screen. The software figures out who owns who and, more importantly, the order in which everything should execute. After that, it's push a button to run the EPU calcs. When it runs, the module will look at the owned entity and first calculate/consolidate/translate as needed, so standard calculate subroutine runs, of course. Then it goes to the owner entity and runs the EPU subroutine to pull the appropriate data over.

There are some quirks to this, like a data load will change the EPU status but a force calc doesn't, but generally it does work and is in production.

Tuesday, June 16, 2015

KScope 2015 next week!

Putting the final touches on my KScope presentations. Both of these are HANDS-ON - attendees show up, get assigned a server, and log in and work in the software - NO POWERPOINT HERE (well, maybe an intro slide). I'm doing two sessions.

Monday morning - 8:30-10:45am - HFM Consolidation Rules - We're going to set up the basic consolidation rules and then introduce some enhancements, like intercompany journals, discontinued operations, and non controlling interest. Will definitely get a good grounding on HS.OpenDataUnit and HS.Con!

Tuesday morning - 11:15am-12:15pm - HFM Tour - Walk through the new features of this year's release.

Both of these are first come, first serve. In previous years you had to sign up for the hands-on sessions and usually a third to a half of the signups didn't show. This year, they're open just like normal sessions and are bound to fill up. The seats are limited to 75 so get there early!

Wednesday, April 29, 2015

Hands On Sessions at KScope15

Going to KScope in June? I've been working on my hands on sessions, the BEST deal at KScope. These are NOT Powerpoint tan-fests. Bring your laptop, connect into a server, and go hands-on with the software.

In the past, you had to preregister to do the hands-on session. Each session would quickly fill up and then only about 60% of the people would show. This year, they're first come first serve, but still limited to 75 attendees. Come early - don't assume that only 45 people or so will show.

I'm doing two sessions this year. The Monday session is on HFM consolidation rules. First we'll go through setting up the standard rules that replicate what HFM normally knows how to do. Then we'll get into how to do various tweaks for a lot of functionality.

The Tuesday session is a walkthrough of HFM We'll take a look at what's new and probably talk about WHY this is the fastest release of HFM.

Here's the link for more info.

When you register, use promo code 123OLAP to save $100.

I'll also be on the floor, in the sessions, etc. I'll be spending some time around the 123OLAP booth so that's the best place to find me (other than the two sessions above!).

See you there!

Wednesday, March 25, 2015

HFM and Copy App Utility - NOT!

I've been working with HFM for over a month now and have been impressed with the improvements in the product. BUT, there is one "improvement" that is not an improvement but a step or five backwards. I'm not saying to not implement - it's a good thing, but this one change isn't a good thing.

For years we've used the HFM Copy Application utility to copy an application from one environment to another. It opens connections to each of the databases and copies the records table by table. Worked great.

With, the main focus was to remove the dependencies on Microsoft Windows. That means the Windows utilities went away, including Copy App. What it was replaced with (and that part is good as there were other utilities not replaced, at least not yet) is a new feature in Lifecycle Management that copies an application. It does make sense, as LCM's purpose is to migrate artifacts (things like rules, forms, etc.) from one environment to another. But in practice, it's a step back. LCM writes out to a zipped file on the server that then must be copied over to the other environment and then imported in. This introduces disk I/O three times to the process, compared to zero with the open databases and copy tables/records approach. If an app is really large, you could be waiting and waiting for the huge output file to move around.

Hopefully with time there will be an improvement here later on.

Friday, February 20, 2015

Oracle Visual Analyzer and Other New Stuff

Taking a break on working through EPM and rewriting my 123OLAP manuals. Hope to have the revised HFM Admin class ready in a few weeks or so and a couple new classes right after that.

Anyway, the other day I was at an Oracle event in Charlotte NC and got a look at an upcoming product, called Oracle Visual Analyzer. No date was given for the release but it sounded like 2015 - we'll see. OBI as a whole isn't my area of expertise, but this looked really good and I hope it can be used with EPM eventually. Here are the notes I got from it - note that I may have heard something wrong, got told something wrong, can't read my notes correctly, etc.

  • Uses just a browser, whether computer or mobile. No addin needed as it's HTML5
  • Connect to a data source, drag and drop the fields that you want to analyze, and the screen starts showing graphics
  • The style of graphics can be changed and go way beyond bar and pie charts
  • If a part of the graph has data that doesn't mean anything, you can highlight it and do a remove only and zoom in on the data that means something
  • Can upload a spreadsheet, do some field mapping, and integrate the data with another data source - thought I heard this will expand to allow merging/mashing multiple online data sources together
  • Can also switch to a data view, but the power here is in the visualizations
  • Not meant for production reports like Publisher but more ad hoc analysis
  • On premise version will be 12c based not 11g based

You get the idea that this is Smart View ad hoc analysis, except through a web page and graphically instead of numerically. Quite cool. Apparently it isn't all that new, though, as here's a YouTube video on it from Sep 14:

The other thing they talked about was a new product, just released, called Oracle Big Data Discovery, a visual interface for a Hadoop cluster. Check it out at Something else mentioned that was in development was a voice interface for mobile, currently called BI Ask. Think Siri for Oracle - hmm, maybe call it Larry?

Back to EPM

Thursday, January 29, 2015

HFM - Consolidation Administration First Look

Getting my first look at HFM today. Working through building an application and seeing what's new and different there, but in the meantime thought I'd post a teaser.

In Consolidation Administration there are a couple of new items (well, they may have snuck into a release and I may have missed them). First in the System area there is a section called Settings that shows the entries that we typically tweak for performance optimization. With this, it looks like we'll no longer need to go into the registry to make these changes.

Seecond, there is a Profile Editor that allows building the PER file for creating a new classic application. Now there's no need to have the Windows client to do this, but the Windows client is available for for creating the profile and managing metadata (Classic apps). Not seeing a web metadata manager yet so maybe there's no need for the Windows client if you're sticking with a Classic app.

The third thing I see that I think is new - again, I may have missed it - is the Configure DSN option. With this you can set up links for database connections for the export of HFM data to the database/table instead of going to a flat file. This used to be done in the software configurator.

Just a teaser for now. Looking forward to new discoveries on this new version. now available - sort of

The long awaited release of is now available, but only from OTN, not edelivery. The links can be found here.

The edelivery release should be coming soon along with documentation. Right now there is no documentation, but there are apparent changes about certain platforms being dropped (Windows XP, Vista, Windows 2003 Server, IE7, IE8, etc.). Once I get an environment going I'll be looking at specific changes to HFM and associated modules.

Wednesday, January 14, 2015

HFM Security and the [Default] Security Class

The other day there was a post on the Oracle HFM forum where someone had two screenshots of from security and asking about the difference in them. One screenshot had the [Default] class set to ALL and the other screenshot had the [Default] class set to NONE. What's the difference and why is this important? For those who have not taken the 123OLAP HFM Administrator Bootcamp training class with my manual (see Chapter 6 - Shared Services), here are the details.

In HFM security classes are used to link metadata elements and various artifacts (data grids, data forms, journals, etc.) to users and/or groups with some level of access (NONE, READ, ALL, etc.). When a class isn't assigned to something, then the built-in [Default] class is used by the software. From a design standpoint, there are two primary ways of working with this behavior.

1) Give users ALL access to [Default]. If something isn't specifically secured then it's open for the users to modify, assuming everything else allows that.

2) Give users NONE access to [Default]. If something isn't specifically secured with access granted then it is not available to the users.

3) You could do something weird like give users one of the other levels of access to [Default] (Metadata, Read, or Promote) but really they're just variations on (2).

By far, number (1) is the preferred security design. The administrator secures what should be secured and everything else is left open. This design reduces the work in the initial setup and in ongoing maintenance. Also, and this is big, end users don't have to worry about security. If they create a HFM journal, ideally they assign a security class to it that relates to the entity being adjusted (ie, show the Canada journal to only the Canadians and not everyone else). BUT, if they don't assign a class, then with (1) they can still see the journal, edit it, etc. Under (2), if they don't assign a class to the journal then when they save the journal will appear to disappear: it's been correctly saved, but security is not letting them see it.

For some new administrators this can be a tough concept. If you're setting up security on a network firewall, you close all the network ports and open only those that are needed, right? But for HFM, it works best (and its widely done this way) to leave everything open and secure only the necessary metadata/artifacts.

Tuesday, January 13, 2015

EPM - still waiting

The big wait going on right now, other than waiting for KScope and Collaborate, is for release This huge release was first scheduled by the end of 2014, but apparently there's more work to be done. With HFM, there are major changes, like the use of Linux instead of Microsoft Windows for the application server, so I'd rather have a delay now versus delays when trying to use the software and it doesn't work.

In the meantime, take a look at the two conferences mentioned above. They both have worthwhile content and are great for catching up with news and techniques for the Oracle EPM suite.

Collaborate - - April - Las Vegas, NV

KScope - - June - Hollywood, FL