HFM translations against the grain

When HFM is set up, the application settings have a section for specifying where balance accounts should be translated with PVA or VAL and same happens for flow (income statement) accounts. Normally this works fine. Typically we do VAL for balance and for flow we do PVA if the exchange rate is a monthly average and VAL if the exchange rate is a year-to-date average.

What happens, though, if some of the data works with these settings and some doesn't?

My current client has this situation. For actuals, they use year-to-date exchange rates. But for budget and forecast, which are coming over from Hyperion Planning, they do monthly translations. If the rate doesn't change through the year, it doesn't matter. But if the rate changes, then there are problems.

Fortunately, there's an easy solution. In the translate subroutine, the function DefaultTranslate allows you to override the application settings with the translation methods and exchange rates for balance and flow. So, I did an if/then to apply this function with the appropriate settings for budget and forecast scenarios and we're good to go.

The code looks like this. Note you can't point to the exchange rate accounts - you have to supply the rate. So I used GetRate for balance and flow and included them in the DefaultTranslate function with variables.


ElseIf pov_scenario = "Forecast" or pov_scenario = "Budget" Then

   AvgRt = .GetRate("A#AvgRate")
   EOMRt = .GetRate("A#EOMRate")
   .DefaultTranslate EOMRt, AvgRt, "True", "False"

Book on Rounding and Scaling in Financial Reporting

Hi all. Started something today that I've thought about for a while and hope to expand. I put together a book on Apple's iTunes bookstore - should eventually be available worldwide as it goes through Apple's process. The topic for this book is how to round and scale data in a Financial Reporting report and also addresses making everything foot. It's short, a very specific topic, and hopefully it's useful.

The difference between the book and the blog is I can put more detailed content, pictures, etc. in the book than I would feel comfortable putting in the blog. 

I'm also going to look into cross publishing via Amazon for all of the Kindle users out there. Let's see how this works and then I'll move on to the next topic. If there's something else that I should write about, leave a comment.

UPDATE: Here's the link for:

Apple: https://itunes.apple.com/us/book/rounding-scaling-in-oracle/id1098935435?ls=1&mt=11

Amazon/Kindle. http://www.amazon.com/dp/B01E4J61I2

HFM PSU 11.1.2.4.200 is available

PSU 200 is out for HFM 11.1.2.4 - four new features!

Rules Profiling

You can use rules profiling to capture in-depth information about rules execution at the major subroutine level. Rules profiles include statistics about time spent calculating, translating, and consolidating each entity during a consolidation task.

Duplicating Applications

You can duplicate of the contents of an application to create a new application. You can duplicate an application in the same environment and rename it, or register the new application in a different project and cluster.

Importing Applications

You can import an application across databases or across EPM systems. Both EPM systems should use Oracle DB or SQL Server.

Viewing Data Grids, Forms, and Process Control as Charts

This release includes the ability to view data grids, data forms, and Process Control information as charts. You can customize the chart display, and also print charts.

And lots of bug fixes!

Fixed bugs:

Defect Number	Issue
22852005	Xfmdatasouce.exe process crashes after network connection to database reset
22691194	In Grids accessing accounts that the user does not have security access to causes instability.
22650693	Cannot open WDEFs when POV is empty for dimensions in rows or columns.
22526377	Users without admin rights are able to view running tasks executed by other users.
22512862	Informational messages logged from the execution of HS.IsValidDest where an invalid member has been passed to the function do not specify which member in the dimension was invalid.
22497823	Assign access control table displays incorrect rights using users/groups filter.
22469303	Launching related content from a tasklist does not use selected active POV.
22345011	On Solaris applications intermittently fail to open.
22331984	Calculation Manager rules are not upgraded correctly.
22305302	Long running tasks in Financial Reporting or Smartview cause xfmdatasource.exe to become unstable.
22289439	Unable to open application in OEL6.
22287103	Error when creating a new journal detail line with no account selected.
22286684	Consolidation aborting due to invalid record.
22272652	Opening a Japanese named custom document displays underscores in the filename.
22269359	Unable to access task audit and data audit in imported application.
22255935	In Smartview adhoc queries do not return all ICPs.
22225041	Loading data after moving application using LCM causes xfmdatasource.exe to become unstable.
22198791	In grids account member properties that contain '<' and '>' are not diplayed properly.
22176031	In Smartview function builder using HSCurrency with a top parent results in 'Invalid Currency Combination' error.
22112722	Large cube size error message contains meaningless label names.
22097353	Copy application fails when creating a new application.
22090169	It is not possible to run more than 8 concurrent intercompany reports.
22075930	There is no documentation for sessionmanagertimeoutinmssetting.
21983885	xfmdatasource.exe
21974387	In grids multi-cell select does not consolidate correct periods.
21958870	Cannot cut and paste from the Assign Access Control screen into Excel.
21956659	No info when data load has errors like cell for period is locked.
21946994	In WDEFs security class access does not work as expected.
21917540	For OBP applications active/inactive status updates do not synchronize across servers.
21894737	The values for Entity and Custom4 are not correct in journal report.
21888335	Documentation for SCALC function needs to be updated.
21882308	Unable to manually input metadata members in intercompany module and save transaction.
21881275	WDEFs with override on a column cannot be used.
21875189	Intercompany reports show empty lines when suppress details is enabled.
21861089	When errors occur during consolidation xfmdatasource.exe may hang.
21856687	Monitor intercompany cannot be used.
21856670	Unable to deploy Calculation Manager rules.
21852281	Smartview journals does not work with entities prefixed with zero.
21819351	Long running Process Management tasks stop when xfmdatasource shuts down due to inactivity timeout.
21812326	Data not synchronized across servers under certain circumstances.
21804549	In Process Control validation incorrectly passes when validation account is not zero.
21799371	Cannot open application that was created using duplicate application.
21797377	Unable to lock period due to erroneous error.
21791057	In Smartview if any HsSet function in a sheet fails all of the HsSet functions in that sheet will fail.
21789553	Cannot clear ownership using clear data.
21771814	The [Default] security class is not selected in new journal page.
21760205	Added a warning message when the "Clear All" option is checked when loading security.
21749284	Unable to submit cell text in WDEF from smartview.
21696503	Conversion rates do not get updated when loading intercompany transactions.
21695535	Autojournals report aborts.
21627558	It is not possible to duplicate applications across databases.
21618967	In WDEFs it is not possible to save changes to journals accessed from entity details.
21568335	Consolidations cause xfmdatasource.exe to close.
21566981	Poor performance opening some data grids.
21523944	Users cannot open an application while a Smartview retrieve is in progress.
21509139	On Solaris xfmdatasource exits during heavy multi-user load.
21508716	Journals not migrated properly in LCM application snapshot.
21491896	POV of related content has different behavior from earlier versions.
21480488	Application fails to open after deploying rules from calculation manager.
21479785	Relative URLs not working.
21394654	Task flows do not run in a multi-server (distributed) environment.
21373263	Cannot create a journal report with more than 1000 journals.
21359703	On Solaris journal reports fail to run with malformedbytesequenceexception.
21348910	On Solaris application fails to open after auto shutdown.
21342557	On Solaris opening large grid results in stuck threads.
21341902	On Solaris the xfmdatasource process locks up during consolidation and is killed off by the health monitor.
21314907	Need to remove the reference to the HFM Rules.xml file from the admin guide.
21313223	The web services reports a hanging thread when posting/unposting multi-entity-journals.
21311538	Partner amounts are not matching between the Intercompany and 'Match By Account' reports.
21297045	Time is shown in UTC on intercompany matching reports.
21263955	Security reports in shared services are all center aligned.
21255493	Application is created but error occurred while enabling features.
21248973	Line item detail descriptions that have an apostrophe do not extract correctly.
21215927	During application creation ‘epmhfm-65554: the system was unable to retrieve the app session’ error is generated.
21206631	In WDEFs dynamic accounts do not show if suppressnodatarows is true.
21193371	Suppress rows with no data with add member functionality, suppressing more than necessary.
21170645	On Linux extracted metadata app file does not retain extended ascii characters.
21162581	Metadata load does not display an error if security has not been loaded.
21154474	In Smartview numeric members are turning into numbers.
21150325	On Solaris open application fails intermittently.
21148421	EA extract to a database does not include member descriptions.
21128657	Intercompany matching report is erroneously listed as a filter in task audit.
21122794	Cannot save journal in an application copied using LCM.
21104385	Entity details are displayed in the wrong columns in data grids.
21090570	Communication error occurs removing cell text label via replace mode in app load.
21086976	Smart view does not maintain parent.child for entity.
21075645	Reports containing suppression options are not working properly.
21074340	Opening a WDEF that contains nodata rows or columns results in 'Invalid Argument' error.
21024595	The Manage Journals task in a task list should open within smartview.
20980001	Security group with ampersand causes an error when printing security report.
20973690	Unable to save security class changes from assign access control with parentheses in the name or description.
20971813	Certain incorrect syntax for hs.clear in rules is not recognized as an error.
20930526	Error in system messages saving a new security class in assign access control.
20852774	In data load replace by security only works for data, not for cell text or line item details.
20826812	Performance issue during open application when LDAP users are assigned to large numbers of groups.
20787473	Cannot reorder columns in smartview.
20684065	In WDEFs force calculate contribution does not work when Entity is not in Parent.Child format.
20647876	EPMA duplicate as deployed does not provision users properly in the new application.
20613267	Cannot override the MaxNumCubesInRAM at the application or server level.
20585801	In Process Control the Validation tab is only visible if Phase 1 is also selected.
20387924	Running an FR report with no members on the rows or columns may cause instability.
20274875	In smartview an error occurs and you cannot refresh if sheet members are moved.
20086146	Intercompany reports do not perform well when consolidation is running.
20017050	Incorrect document name when opening a custom document.
19569673	When a dot is missing between dimensions in a WDEF script opening the WDEF causes the system to become unstable.
19446759	In Manage Taskflows it is not possible to select POV parameters using IE11.
19375860	System logs show numerous 'Unable to open file' errors.
19327569	In WDEFs users are able to select members that are out of constraint when the hierarchy contains shared members.
18753552	Errors occur when opening specific journals.
18537370	Poor performance in process control when email alerts are enabled.
18228788	Clear invalid records does not produce a log when running for a long time period.
17170275	Process control 'Show Active Only' option affects WDEF entity change.
6569808	In WDEFs it is not possible to have multiple member lists on columns.

Metadata changes - what changes?

Just saw a post today on the Oracle OTN forum regarding metadata changes. I gave a short answer there and I'll give a more detailed one here.

The question is: what happens to data when metadata changes (adds, moves, deletes) are made.

Answer: it depends on the circumstances.

First, adds. Adding new metadata will not affect the data. I say "will not" but keep in mind I can write a rule that says if the account starts with 123 then make it equal to 1 and if I add account 12345 then the rule will run, create a 1, and now my data is changed. I'm ignoring weird stuff like that for this post.

Next, deletes. Deleting metadata will cause data changes (pretty obvious but just including it for completeness). If the data wasn't cleared first, the records will be orphaned and can be cleared by running Delete Invalid Records from the Manage Data screen. 

Next, renaming or changing the label of a metadata item. If the application is Classic, then this is treated as a delete and add, so data will be lost. If this is needed then extract the data first, make the change, and then reload (I'd maybe go a step further and do the add with the new label, reload the data, verify, and then delete the old label). If the application is EPMA, then the member is actually renamed and data is not lost. This is the one feature of EPMA that I like.

Last, moves. Here, there are further conditions...

Account and custom dimensions - parent account and custom members are calculated in memory whenever retrieved, so moving a member will automatically be reflected in the data. This occurs whether the entity is locked, promoted/published, etc. and the behavior cannot be modified.

Entity dimension - if parent entities are locked (I've never recommended doing this but I'm sure someone is out there doing it), then changes to the entity hierarchy will not be reflected. If org by period is in use, then moving an entity must also include updating the [active] system account for when the entity should roll up to the parent. If not using either of these features, then the move will impact the parent entities but only after data is reconsolidated. 

Note: we generally recommend reconsolidating all scenarios/years/periods/entities after making metadata changes. This ensures the data is consistent with the metadata and rules and users don't have the worry about seeing "OK SC" as the consolidation status.

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.

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

HFM 11.1.2.4.100 patch released

HFM 11.1.2.4 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 11.1.2.5.500)

There is also a healthy list of defects fixed.

Happy patching!