Showing posts with label HFM. Show all posts
Showing posts with label HFM. Show all posts

Wednesday, December 18, 2019

HFM 11.2 - New Features!

The on-premise Oracle EPM product suite was upgraded today to release 11.2. This long awaited release is primarily a technical upgrade for a lot of back end technology. That all said, there are two new HFM features.

First, in Task Audit, there are additional tasks that are now logged.

  • Journal Period Opened
  • Journal Period Closed
  • Data Locked
  • Data Unlocked

The data lock/unlock tasks also include the point of view being acted upon. It's not clear but I'm hoping the journal scenario/year/period is also captured.

Second, there are two new configuration settings related to calculations.

  • MaxNumConcurrentCalculations - sets the number of concurrent calcs allowed per server per application. The default is 8.
  • ConcurrentCalculationWaitTimeInSecond - If the max above is reached and someone then starts another calc, this setting controls how long they wait before the calc times out. If a calc times out, then a message is entered in the system log with the point of view details. The default is 30 seconds. By setting the value to -1, the system will wait indefinitely until another calc finishes.


I've lived without these two features but I can understand and appreciate their usefulness. Looking forward to seeing what else is included with version 11.2.





Wednesday, November 27, 2019

FCCS - calculating rolling x number of periods

A common type of financial calculation involves a rolling x number of month calculation, like a rolling 12 month net income. In HFM, there were functions and keywords we could use. There are different ways of writing the calculation, but most likely one of these:

HS.Exp "A#R12_NetIncome = A#Net Income.W#Periodic.P#CUR + A#Net Income.W#Periodic.P#CUR-1 + A#Net Income.W#Periodic.P#CUR-2 + A#Net Income.W#Periodic.P#CUR-3 + A#Net Income.W#Periodic.P#CUR-4 + A#Net Income.W#Periodic.P#CUR-5 + A#Net Income.W#Periodic.P#CUR-6 + A#Net Income.W#Periodic.P#CUR-7 + A#Net Income.W#Periodic.P#CUR-8 + A#Net Income.W#Periodic.P#CUR-9 + A#Net Income.W#Periodic.P#CUR-10 + A#Net Income.W#Periodic.P#CUR-11"

OR (assuming a December year end):

HS.Exp "A#R12_NetIncome = A#Net Income.W#YTD + A#Net Income.W#YTD.Y#PRIOR.P#Dec - A#Net Income.W#YTD.Y#PRIOR"


If only a three month rolling amount was needed, use the first approach and just stop after P#CUR-2.

In FCCS, a member formula is a good way to write the calculation (as compared to an insertion rule), but for us HFM converts, how? Essbase has a long list of functions, many of which work just fine with FCCS. The functions we want to look at now are @PRIOR, @RELATIVE, and @CURRMBR. Here are the definitions from Oracle's help website.

@PRIOR - "Returns the nth previous cell member from mbrName, in the sequence XrangeList. All other dimensions assume the same members as the current member. @PRIOR works only within the designated range, and with level 0 members."

@RELATIVE - "Returns all members at the specified generation or level that are above or below the specified member in the database outline."

@CURRMBR - "Returns the member that is currently being calculated in the specified dimension (dimName). This function can be used as a parameter of another function, where that parameter is a single member or a list of members."

So, how do we use these? I don't claim to be the best calc script writer (far from it), but these approaches work for me. For the rolling three month calc, I use this (again, assuming December year end):

IF(@ISMBR("Jan"))

"FCCS_Net Income"->"FCCS_Periodic" + @PRIOR("FCCS_Net Income"->"FCCS_Periodic"->"Dec",1,@RELATIVE("Years",0)) + @PRIOR("FCCS_Net Income"->"FCCS_Periodic"->"Nov",1,@RELATIVE("Years",0));

ELSEIF(@ISMBR("Feb"))

"FCCS_Net Income"->"FCCS_Periodic" + @PRIOR("FCCS_Net Income"->"FCCS_Periodic", 1) + @PRIOR("FCCS_Net Income"->"FCCS_Periodic"->"Dec",1,@RELATIVE("Years",0));

ELSE

"FCCS_Net Income"->"FCCS_Periodic" + @PRIOR("FCCS_Net Income"->"FCCS_Periodic", 1) + @PRIOR("FCCS_Net Income"->"FCCS_Periodic", 2);

ENDIF


Starting with the ELSE part first, the formula gets the current periodic amount and then uses the @PRIOR function to get the first prior and then the second prior month data. This will only work for March to December, though, as the @PRIOR function doesn't wrap around the year like HFM does. So, use the IF or ELSEIF to check for Jan and Feb and for the prior year data points, use the @RELATIVE function within the @PRIOR function to pull from the prior year.


For the rolling 12 month calculation, the above technique could be extended to cover 12 months, but as with the HFM formulas above there is a more elegant way.

"FCCS_Net Income"->"FCCS_YTD" + @PRIOR("FCCS_Net Income"->"FCCS_YTD"->"Dec",1,@RELATIVE("Years",0)) - @PRIOR("FCCS_Net Income"->"FCCS_YTD"->@CURRMBR(Period),1,@RELATIVE("Years",0));


This formula pulls the current month YTD value, adds the prior year December YTD value, and then subtracts the current month, prior year YTD value. The @CURRMBR function is inserting the current month being calculated into the dimension reference for the @PRIOR function. As I type this I'm not sure if this is needed, but as seems to be working I'm leaving it alone.



In closing, Essbase has been around since the early 1990s, so there has been plenty of time to figure out the functions that are needed - us HFM converts just need to learn!








Tuesday, September 4, 2018

HFM - Planning for the upgrade

Everyone should have heard by now, but just in case you haven't. Oracle is planning for the release of HFM (actually, all of the on premise EPM products) version 11.2 in the spring.

As Oracle has stated this will be the last release of HFM that will require a full software installation (to their knowledge, safe harbor statement applies). Other updates after this will be via patch set updates. To do this installation, you cannot (or should not) just update the software on the existing servers. The software will need installing on a clean server, the database copied over and upgraded, interfaces tested, and then consolidation and data verified. A parallel close would be a prudent step.

To plan for this, think about budgeting for the new servers now. Most companies are in the middle of putting together their annual budget for next year, so now is the perfect time to plan this upgrade. Unless some design changes are incorporated at the same time, no consulting help should be needed unless it is to help with the infrastructure setup. If you are on an older version or you're having to do work-arounds to make things work with your current design/setup, then you should think about taking this opportunity to do an "application remodel."

The other option would be to plan a migration to FCCS, Oracle's cloud based consolidation and close solution. In this case you would be budgeting for an operating expense, not a capital expense, and not having to budget for the annual maintenance on the on-premise software. Migrating to FCCS will go much easier with some consulting help, but with the recently released HFM to FCCS migration tool, it goes more smoothly than before.



Monday, July 30, 2018

HFM - Metadata Diagnostics

Metadata is the backbone of HFM. Without it, no data gets loaded and nothing gets reported. And the properties of the metadata have to be perfect. If they're not, then either data doesn't load, or (worse), it loads but the reporting is wrong. In this book available on Amazon, I show how to write the code to HFM diagnose the properties and then email or output a file with the results. I've used the results myself over the years and this technique has saved me from huge mistakes.

Here's the link - it's also available on the right hand panel with my other books.

https://amzn.to/2mSAIWo

Note you do not have to own a Kindle to read this. Amazon has free apps for any device for reading Kindle books.



Monday, May 15, 2017

HFM Command Line Automation

Happy May to everyone. I got the idea for this blog post from a client who wanted to maintain and manage HFM from CA-7 jobs; basically where you do things from a command line on a scheduled basis. HFM isn't really geared for that; but, there is a legendary, mythical way to do pretty much everything!

For versions prior to 11.1.2.4 there is a third party utility called HFM Batch that can be used to do this, but it doesn't work with 11.1.2.4. Fortunately there is a solution - from Oracle and included with HFM - and it goes all the way back to the early, early days of HFM. Some documentation I have on the technique is dated circa 2003.

So, what you do, is .....










ATTEND KSCOPE17!    www.kscope17.com


Seriously, at KScope17 there is a presentation that covers this specific topic (Monday, 10:30am). I don't want to steal Beatrice's thunder, especially since she recently helped me with the topic, but she's going to cover it all then.

And to save $100 when registering, use code 123OLAP - you'll see a field where you can enter this.

Hope to see you in San Antonio!

P.S. Here's a sneak peek:












Wednesday, April 5, 2017

HFM - Knowing Which Environment You're In

This blog post comes about because of LifeCycle Management ("LCM"). It's fine as a tool to migrate things (called artifacts) from one environment to another but I don't use it a lot because HFM has native import and export capabilities for most things already. LCM works better when the application has the same name in all environments, which I don't like: I like knowing where I am. 11.1.2.4 addresses this somewhat - if you're migrating from one HFM app to another and they're called different names and the LCM import doesn't find the same application name, it will prompt you to select one. But try this with a task flow and you won't get prompted and you'll end up with a task flow that doesn't work since the embedded application name didn't get changed (believe me, I tried).

The obvious answer to this is to call the application name the same in all environments, and most people do that. But here's the problem. Within rules, we typically want rules to do different things in dev vs. QA vs. production. If you're doing write to file, for instance, you'll want different file paths for each environment. If you're sending emails for your metadata diagnostics (you are sending emails for your metadata diagnostics, right?) we want to have different addresses, different email subject line, etc. If the application name is different, then you can use HS.ApplicationName to find out where you are. But if you're being LCM-friendly, that's not going to work.

So, what to do?

Fortunately, VB script has a way to find out where you are. You can use two simple lines to capture the computer name (server) that is running HFM. So, in your rules, do something like this:

set wshShell = CreateObject("WScript.Network")
strComputerName = wshShell.ComputerName


Then, after that, use if/then or select case to look at the computer name and set the path or whatever you're doing. If your environment has multiple HFM servers, then include all the server names.

Select Case strComputerName

   Case "DEV01"
      location = "\\dev\fileshare\file.txt"
   Case "PROD01", "PROD02"
      location = "\\prod\fileshare\file.txt"

End Select


This allows you to know where you are and still be LCM-friendly. If you're running on Exalytics, you may need to tinker this a bit. I've had multiple Exalytics clients but for some reason they all still put HFM out on separate Windows servers - weird.


Anyway, have fun knowing where you are.








Monday, February 20, 2017

Viewing Metadata Properties within HFM

For HFM admins, making life easier is about automating something, a new calculation, faster consolidation, etc. But for end users, making life easier tends to be about simple stuff. Show me something quick that can make my life easier.

One of the questions end users have is what are the attributes of this account, entity, etc. - the metadata properties. For years administrators have put this information out to Excel files (the HFM Toolbox utility for versions before 11.1.2.4 was great at this).

But within HFM itself, there are some things that can be done.

In the mid 2000s a field was added to the accounts called Calculation Attribute. It provides 100 characters or so for the admin to type in how an account is calculated. The history for this goes back to Hyperion Enterprise that would show the calculation for a calculated account in the Data View window. In HFM right-click a cell (data grid or form), select Cell Information and you'll see the Account Calculation Attribute if it has been provided.





That's all well and good, but what about the metadata attributes? Asset or liability? What custom dimension top members? For this information, there is a simple solution. Right-click the metadata item (I'm using account ROA in the rows here) and select Member Properties.





Once selected, a popup appears with all of the information an end user would want to know. The screenshot does not show all of the attributes, but they are all there.



So simple and available from grids and forms (but unfortunately not Smart View).







Tuesday, February 14, 2017

HFM 11.1.2.4.204 PSU released

The 204 PSU was released today for HFM 11.1.2.4. Below is the list of defects fixed in the patch. Personally, I'm glad to see the issue with extracting data to an Oracle database requiring TNSNAMES getting fixed. This has been a thorn in my side at my current project.

Have fun patching!


Defects Fixed in this patch

Defect Number
Issue
Lcm epmhfm-66115: an error occurred reading metadata file content for data form
25346829

Hfm journal report:turkish characters are not exported correctly

Email alert doesn't work on entity security class
New option to limit number of concurrent consolidations per user action
Hfm application crashing when using fdmee data extracts
Cannot run consolidation after applying hfm 11.1.2.4.203 patch from taskflow
Issue with grid-invalid intersection in the pov with psu 203
Getting an error "an invalid security key was specified" in system messages
Consolidations running server out of memory
Submitting cell text in data forms does not work in smartview
Ic reports aborts running reports concurrently
24701675 

Overlap consolidation is running for wrong parent in process control grid

Error retrieving datagrid in api demo.java sample code

Non admin users are not able to see their "running tasks"
Process level can be promoted even though validation fails
Unknown error happens in metadata load and application crashes
Cannot delete security class from shared services for hfm epmaapp
User getting adf_faces-60097 error opening hfm application from ie11
Hfm application opens to white screen after doing opening journal form filter.
Metadataom.findmembers() returns the member instead of null
Hfm process crashing on startup on exalytics
Hang up in active not turn into failure when stage has an issue like no data file
Hfm dsn export not exporting all member descriptions
Not possible to override system settings - name field in override window is empty
No access/metadata issues when accessing smartview(recurring issue)
21840787
Filtering does not work in data audit
Intercompany report uses the wrong column width settings
Unable to promote the entity to next level if using a system defined memberlist
Unable to transform classic hfm linux app to epma
Extract data to oracle db requires tnsnames
Cannot select books when creating related contents in hfm

Friday, January 27, 2017

To Lock or Not to Lock - That is the Question

When I talk with clients and other consultants, one of the most controversial topics is whether to lock entities or not. When you lock an entity, no one, including the administrator, can change data in the entity. If the entity is a parent entity, then changes to the entity hierarchy (like removing a child) do not impact the locked entity.

There are two different concerns that companies address with locking: preventing data changes from input and preventing changes from hierarchy movements. The big concern that people have with locking, however, is application rebuilds: if parent entities are locked and changes have been made, it will be (and is) a nightmare to make everything tie out again.

So, lock or don't lock?

I'm not a fan of locking. But I realize the concerns above are valid, so what do you do if you don't lock?

For preventing user changes to data, I prefer to promote the data with process control to where the users cannot make changes and trust the admin not to do something crazy (process control has no effect on administrators).

If you do want to lock, consider locking just the base level entities. That will prevent anyone from making data changes but not cause a problem with a rebuild. 

So if you want to lock parents so that hierarchy changes don't cause impacts, then think about if you have a lot of changes. If not, use alternate hierarchies and/or new entities. But if you do have a lot changes, then consider activating organization by period which is designed for this. 

To me, locking the parent entities is the last thing I want to do. But if you do, keep a version of each month's hierarchy offline (metadata extract) so you can compare back and reconcile when rebuilding. 

Note: locking only protects entities. Parent accounts and parent customs are dynamic calc, so any hierarchy changes in these dimensions will apply to history. Nothing you can do except maintain alternate hierarchies. 

With upgrades many times you can migrate and update the database directly without a rebuild so locking wouldn't be a problem. I've also seen clients keep an archive version of the app each year but that's rare.

But I still wouldn't lock parent entities. 



Tuesday, December 6, 2016

Creating Intercompany Matching Reports via Script

One of the features that has been in HFM since almost day 1 is intercompany matching reports. HFM's predecessor, Hyperion Enterprise (which was originally called Hyperion) had intercompany matching reports. Enterprise's predecessor, IMRS MicroControl, a DOS product developed in the 1980s, had intercompany matching reports.

In the beginning of HFM, intercompany matching reports were developed in script (using KEdit or Notepad or other text editor), uploaded, and then you ran it to see if it worked, anything need changing, etc. Later on the graphical interface came along and nowadays no one really sees the script anymore.

But, it is still there.

Where working with script comes in handy is the initial build of the matching reports. Just built 45 matching reports today using script. Here are the steps.

Build one report through the graphical interface like normal and then extract it from the Documents screen.



Open the file in Notepad and copy the script into Excel.

In this screenshot, the script is in column L. Cells L2 and L3 and the last cell (AccountPlug) have been edited to use cell level formulas to point to I1 and I2. Column I is fed from a list in column E and F with all of the plug accounts that need a report.



Paste the script into Excel and save the file. Then move on to the next item in columns E and F. If you have a huge amount, you could write a macro that cycles through the list and writes out the text files.

Last, go into HFM to the Documents screen and load in the script files. Make sure to set the options at the top to the desired folder, document type, and security class before selecting the files.



Having to create the reports via script has gone the way of the green home page, but the utility of working with the script is still there.

Enjoy!






Wednesday, October 5, 2016

Charts in HFM Data Grids

Working with HFM 11.1.2.4.202 and wanted to do a blog post on charts within data grids. This feature has been out a couple of months and I wanted to show how it works.

First, start with a normal data grid. Over on the display options panel on the right, there is now a chart radio button.




Once the chart is turned on, the chart displays in the window, graphing any data showing in the grid.



The type of chart and the properties can be controlled on the display options panel. Note there are a lot of chart types: some trial and error will be needed to select the appropriate type.



Clicking a bar, line, etc. on the chart will focus on that specific row/column, but drill down is not available by double-clicking the chart like other programs do. For drill downs, click on the legend to the right.


Clicking the legend to do the drill down not only changes what is displayed on the chart but also changes the table display (if showing both the chart and the table).

Overall it looks good and works well. But there is still is no printing - still need to do screen shots!