What is Temporal translation and how does HFM handle that?

Hyper-Inflationary translation means you must use what is called ‘Temporal’ as opposed to the common ‘Current’ method (which is out of the box).

Under the temporal rate method, the objective is to measure each subsidiary transaction as though the transaction had been made by the parent. Monetary items (e.g. cash, receivables, inventories carried at market, payables, and long-term debt) are remeasured using the current exchange rate. Other items (e.g. prepaid expenses, inventories carried at cost, fixed assets, and stock) are remeasured using historical exchange rates.

The Temporal Method:

• Monetary assets and liabilities (cash, liquid securities, accounts payable and receivable, debt) are converted at the current rate of exchange. – default rates in the system .
• Nonmonetary assets and liabilities (fixed assets and inventory) are translated at historical rates. Thus no accounting capital gains or losses arise from these items. – In HE, I would do this via USD overrides.
• Income state items are converted at the average exchange rate for the accounting period unless, as in the case of depreciation or cost of inventory sold, they are directly associated with nonmonetary items. In this latter case the historical cost is used for the translation. – Same as above using overrides.
• Dividends and other distributions are converted at the current rate of exchange at the time they were paid.
• Under the Temporal-Rate Method the net gain does go into the consolidated income statement but since no fluctuations in the value of fixed assets occur the effect on net income is moderated. Because the Temporal-Rate Method uses different exchange rates for different account items there is a problem in the consistency of the accounts. This is a rule you would add to the impact the expenses, I have seen this in other expenses, or other operating expenses. It is likely they know where they want to this impact.

I can’t imagine doing this with rates in HE. You would need a rate for each entity potentially.

Contributed by:
Peter Fugere, Practice Director
HFM & HE Hyperion Certified
Ranzal & Associates
pfugere@ranzal.com

What is PVA and VAL? And How should I do translation per GAPP in HFM?

There are 2 standard methods of translating an account, PVA and VAL.

PVA:

This method retrieves the periodic value for an account and multiplies that by the exchange rate for that period to obtain the translated amount for the current period. Then the translated amount for the current period is added to the prior period’s year-to-date (YTD) translated balance in order to calculate the YTD translated amount. The PVA method is usually applied to Income, Expense, and Flow type accounts.

VAL:

This method retrieves the YTD value in an account and multiplies that by the exchange rate. The VAL method is usually applied to Asset, Liability, and Balance type accounts.

An example of PVA and VAL is as follows:

PVA

	JAN	FEB	MAR	(Divide)
AVERAGE RATE.FRANCS	0.5	0.6	0.7
INCOME ACCT (EURO)	100	300	600	(YTD Amounts)
INCOME ACCT (USD)	200	533	962	(Translated Amt After Consol)

The PVA method takes the periodic value in the INCOME ACCT and divides that by the exchange rate for that period. It then adds this result to the translated value from the prior period. It does not change the exchange rate amount or look to the prior period for the exchange rate at all. In the example above the calculations are as follows:

JAN 100 / 0.5 = 200
FEB (300 - 100) = 200 / 0.6 = 333 + 200 (JAN) = 533
MAR (600 - 300) = 300 / 0.7 = 429 + 533 = 962

VAL

	JAN	FEB	MAR	(Divide)
AVERAGE RATE.FRANCS	0.5	0.6	0.7
INCOME ACCT (EURO)	100	300	600	(YTD Amounts)
INCOME ACCT (USD)	200	500	857	(Translated Amt After Consol)

Using the VAL method instead of PVA, the results would be:
JAN 100 / 0.5 = 200
FEB 300 / 0.6 = 500
MAR 600 / 0.7 = 857

Contributed by:
Peter Fugere, Practice Director
HFM & HE Hyperion Certified
Ranzal & Associates
pfugere@ranzal.com

Common Essbase & Planning Problems & Resolutions: The Essbase Add-In

Over the last 11 years of consulting, there are certain issues I have seen in the implementation of Planning and Essbase applications that are so common, that I have decided to list them here and their resolution in the hopes of sparing you all some undue pain. Today we will focus on a set of issues that probably affects the greatest number of users – the Essbase Add-In. Personally, I think the native Excel Essbase-Add in is still one of the most compelling features to Essbase – which is probably why there hasn’t been a mass migration of older Essbase clients to SmartView (yet). In the words of Mr. Heston – “Not until they pry it from my cold dead hands…”. I sort of feel the same way – so with that in mind, here we go!

Problem # 1 – More than 1 Instance of Excel Open
The Essbase Add-In technically only supports 1 instance of Excel open at a given point in time. If you have ever had issues with retrieving data, but not having anything come back, there is a good shot that this is the cause. As a matter of fact, if anyone ever tells me they have ANY problem with the add-in, this is the first thing I tell them to look at.
Since this is kind of tough to tell given how workbooks appear as separate “programs” in the taskbar now of your desktop, the clearest thing to do is to perform a <CTRL>-<ALT>-<DEL>, display Task Manager, click the processes tab, and do a sort in ascending order by process name. If you see more than 1 Excel.exe process listed, while it may appear that the add-in is working, you will get flaky results (for example, retrieving data and nothing returning). The solutions - terminate one of the excel processes.

Problem # 2 – Conflicts with Other Add-Ins
What other add-ins do you need besides Essbase? (Rhetorical question) Well, how about SmartView, or Peoplesoft’s Excel add-in for reporting? Even a Hyperion Enterprise Retrieve…I’ve had success making the various add-ins work together, but some functionality (such as double clicking) will cause conflicts with each other. To resolve, you can certainly load and unload the various add-ins as you need them, or on the Essbase side, there is a great option made available in the 7.x release called “Limit to Connected sheets” – it basically prevents Essbase from taking your mouse buttons until you manually connect a spreadsheet. Note this also has the added benefit of giving Excel users their native Double Click to Edit Cell, and right click pick from list functionality back. An always on setting for me…

Problem # 3 – Where did my add-in go?
This next problem I am bringing up only because it’s personally happened to me so many times. I call this the “I changed my mind” problem. What happens is the following:

• You are performing an ad-hoc analysis in a spreadsheet against Essbase
• You are done, and click X to close out Excel
• You are prompted to Save Changes for any open workbooks, and instead of saying Yes or No, you click Cancel, which stops the closing of Excel
• You are no longer able to run retrieval code or any add-in function

What happened? Clicking Excel unloads the add-in immediately, despite the fact on exiting the workbook, you have changed your mind

I have also seen this in a # templates with VBA code – generally if you starting getting negative numbers as return codes, this is an indication of that problem – the ad-in is loaded.

The solution is to goto your Tools->Add-Ins menu, deselect the add-in, and close, then go back to your Tools->Add-In menu, and reselect the Add-in, which will load it back up.

Problem # 4 – Why are my retrievals so slow?
Recently at ODTUG Kaleidoscope in New Orleans I gave a presentation on optimizing Essbase retrievals – I got a lot of great feedback from people, specifically related to a number of the tips not being your standard Essbase Common knowledge (you know, your classic IF on dense, FIX on sparse, which isn’t always true anyways). In any event, overall retrieval time is a combination of the server performance, the network performance, and the client performance. Assuming you are an end user, there isn’t much you can do about network or server performance, but there is a lot you can do about your own template configuration on the client, specifically including the following:

• TIP - Minimize the Usage of EssCell

  I have seen former Enterprise users and even HFM users who work with the Essbase add-in fall in love with this excel function that retrieves data into an individual cell in an Essbase grid. While OK for 1 or two values, each usage of this function executes a separate call to the Essbase server. You are better off doing a hidden retrieval sheet with one retrieval from Essbase, and then referring to the values from that spreadsheet.

• TIP - Minimize the Usage of Preserve Formulas
  
  When you preserve formulas on an Essbase retrieval, Essbase needs to check the value of each cell in the grid to see if a formula is there before retrieving it. On particularly large retrievals, this takes additional time.

• An alternative technique is to make use of locking/unlocking cells, and protecting the worksheet. In the attached retrieval sample from Sample:Basic below, % of Sales is a calculated column with an Excel formula.

• To “preserve formulas” without using the Essbase feature, the steps you would take would be as follows:

Step 1 – Select all cells in the grid, and unlock the cells

All cells in a spreadsheet by default are tagged as locked.

Step 2 – Highlight the Columns or Rows with Formulas, and Only Lock Those

Step 3 – Protect the Worksheet

Now, all of the cells you want Essbase to retrieve into will be able to overwrite the values since the cells are not locked, and the cells with formulas will be protected.

Note that for a retrieval this size, the above technique is really not necessarily, but particularly if you are using the cascade function, or doing batch workbook retrievals of spreadsheets with hundreds of rows, even a 4-5 second improvement is worthwhile, particularly when you amplify that over the course of multiple retrievals.

• TIP - Minimize the Usage of “Unknown Members” in Retrieval RangesIn the example above, I added a calculated member called % of Sales. This is not an Essbase member, and as a result, Essbase will let me know about it.

I can work around this annoying message, but I turning off the option for “Display Unknown Members” in the Essbase Options dialog box.

Now when I run my retrieval, I no longer get a message displayed on my client workstation. Problem solved! Well, it is for me, but all of those messages are still occurring, they just aren’t being written the client workstation. They still appear in the Essbase server log.

Imagine a spreadsheet with hundreds of labels that don’t belong – that’s 100 additional lines on every retrieval where the Essbase server is writing really useful messages to the log file instead of processing your query. For something this small, it’s not an issue, but on larger retrievals, I have seen this have an affect of a few seconds per retrieval.

An option to address this issue is to make use of labels in the Excel grids.

Now when the retrieval runs, because the value isn’t stored in a cell, problem solved on both client and server. This also has the added benefit of allowing you to keep “Display Unknown Members” on so in the event something happens that you do care about, say a member name getting renamed and blowing up your retrieval, you will be aware of it.

• TIP - Turn off Enable Flashback

  If you are doing cascade retrieves, or VBA batch retrievals in the Essbase add-in, this setting is unnecessary because it will use memory to store the previous operation only, which won’t necessarily allow you go to back more than 1 sheet. In really early versions of the add-in, we saw lots of issues with both this and preserve formulas on.

Closing Thoughts…

In the next installment of this series, I will spend more time focusing on the # 1 thing you can do to improve retrieval performance, specifically changing the orientation of your retrievals themselves, and selective usage of attribute dimensions. That should be a blog by itself!

Mike Killeen - Ranzal and Associates
mkilleen@ranzal.com

For more than eleven years, Mike Killeen has scoped, designed, and implemented Planning & Essbase applications for Ranzal & Associates, an award winning, preferred consulting partner of Oracle Hyperion. Currently serving as practice director, Mike provides guidance to Hyperion product management, Ranzal customers, and internal consulting resources alike on best practices related to the implementation of Hyperion System 9. Mike is a certified Hyperion Professional, with certifications in Essbase, Planning, and HFM. He graduated cum laude from Cornell University with a degree in engineering, and received his MBA from the University of Connecticut.

Welcome to Ranzal & Associates’ Blog!

Ranzal specializes in Business Intelligence and Business Performance Management with a concentration in Oracle/Hyperion’s toolkit. Ranzal works closely with corporate executives, line-of-business management, end users, and information systems departments alike to address the business issues and challenges inherent in data gathering, management, and dissemination. Organizations from various industries have engaged Ranzal with outstanding results.

Topics of discussion:

• Common Essbase & Planning Problems & Resolutions: The Essbase Add-In
• What is PVA and VAL? And How should I do translation per GAPP in HFM?
• What is Temporal translation and how does HFM handle that?