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:

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.

EPM Automate - DOS style automation for Oracle EPM Cloud

Ever heard the phrase "history repeats itself" or "something out of fashion will become popular again"? EPM Automate is a good example.

EPM Automate is the automation tool for the Oracle EPM cloud products: EPBCS, PBCS, FCCS, ARCS, etc. It allows users to log into the cloud, perform various actions, and log off. It can be run interactively or scripted and then scheduled.

The "history repeating itself" piece of this comes from the user interface and the scripting language: MS-DOS command line and MS-DOS batch file (or PC-DOS if you got your copy of DOS direct from IBM instead of Microsoft or a clone manufacturer). Or if you've ever used the command line on a Unix computer - did you know Apple's Mac OS X is a Unix operating system? - then you'll feel right at home. A DEC VAX had something similar but the language was different.

Actually the idea of a cloud environment harkens back to mainframe computing, so "what goes around comes around." Ok, no more phrases.

Anyway, the first step is to log into the cloud product and download the installer - works with Windows or Linux (64 bit versions only). As the cloud environment is updated each month, the downloaded program should be updated each month too.

After the install, launch the program from the menu. You're basically put into a command line environment, complete with drive/folder> and blinking cursor. Type in what you want to do, like login, and then get a response back. If you don't know what you can type then enter "help" and get a list. For help on a specific command, type the command and then help, like "login help".

For automating, write a batch file and then schedule that with Windows Scheduler, CA-7, etc.  I'm tempted to use EDLIN as the text editor, just for the full 1980s DOS effect.

Notice I'm skipping screenshots for this post - Oracle has a YouTube video that shows you how it works - this video uses PBCS for the cloud product.

https://www.youtube.com/embed/1lUthzzoWEY?rel=0&autoplay=1


For full documentation on EPM Automate, go to:
https://docs.oracle.com/cloud/latest/epm-common/CEPMA/CEPMA.pdf


Overall, a simple and easy way to work with the Oracle EPM Cloud products. I wish they'd make this available for HFM - I like it better than task flows. HFM Batch (Google it) was similar but wasn't replaced when 11.1.2.4 (with no Windows API) came out.


P.S. With good ideas from the past being brought to bear in current day solutions, I wonder when keypunch cards are coming back. I need a new holiday wreath (search Pinterest).

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

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
25352846	Lcm epmhfm-66115: an error occurred reading metadata file content for data form
25346829	Hfm journal report:turkish characters are not exported correctly
25111633	Email alert doesn't work on entity security class
25099054	New option to limit number of concurrent consolidations per user action
25085411	Hfm application crashing when using fdmee data extracts
25023506	Cannot run consolidation after applying hfm 11.1.2.4.203 patch from taskflow
24970914	Issue with grid-invalid intersection in the pov with psu 203
24912844	Getting an error "an invalid security key was specified" in system messages
24911708	Consolidations running server out of memory
24850722	Submitting cell text in data forms does not work in smartview
24835551	Ic reports aborts running reports concurrently
24701675	Overlap consolidation is running for wrong parent in process control grid
24615995	Error retrieving datagrid in api demo.java sample code
24569482	Non admin users are not able to see their "running tasks"
24417229	Process level can be promoted even though validation fails
24389966	Unknown error happens in metadata load and application crashes
24297212	Cannot delete security class from shared services for hfm epmaapp
23725599	User getting adf_faces-60097 error opening hfm application from ie11
23704850	Hfm application opens to white screen after doing opening journal form filter.
23562417	Metadataom.findmembers() returns the member instead of null
23299237	Hfm process crashing on startup on exalytics
23194007	Hang up in active not turn into failure when stage has an issue like no data file
23101101	Hfm dsn export not exporting all member descriptions
22342743	Not possible to override system settings - name field in override window is empty
22322353	No access/metadata issues when accessing smartview(recurring issue)
21840787	Filtering does not work in data audit
21480292	Intercompany report uses the wrong column width settings
21112270	Unable to promote the entity to next level if using a system defined memberlist
20546956	Unable to transform classic hfm linux app to epma
19323855	Extract data to oracle db requires tnsnames
18922785	Cannot select books when creating related contents in hfm

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. 

Oracle Data Visualization Cloud Service

For 2017 I thought I would start with something different. I recently spent some time working with the Oracle Data Visualization Cloud Service ("DVCS" from here on out). I had seen a preview of this product before its release and thought it looked great and wanted to try it out.

DVCS is a presentation tool. You provide it the data, it makes sense of it and presents it. The data can come from Excel or various Oracle products. HFM isn't one of them, though.

For this test, I used some data from a recent wine tasting. Note the column headers will be helpful in working with the data later.

Click Data Sources and then click Create New Data Source to add the source (or in the case of Excel upload the file).

Once uploaded, return to Home and create a new VA Project. Once the data source is selected, the different fields can be clicked and dragged to the project - rows, columns, etc. The type of chart can be chosen and table layouts are available too for those who just want to see data.

With this setup below, I'm showing the tasters across the x axis for each wine and filtering the results (located above the bar chart selection) to taste. A score of 5 is good, 1 is bad.

With the above setup, we can see that Honig, Worthy, and Brookman were the leaders.

Changing the setup to a donut chart, removing the tasters, and changing the filter to total score, we can see that the Honig was the winner, with Brookman just beating Worthy for second.

Look at the bottom of the screenshot. Commentary can be added to provide context, etc. For the seven wines we tasted, 14 Hands Hot to Trot was not the best scoring wine, but for the price it provided (in our opinion) the best value.

There are other capabilities of DVCS: blending data (using more than one data source together [think joining tables]), calculations based on the data, multiple visualizations on one page, etc. But hopefully this gives you a good start to it. The Academy is a good starting point (as with all of the cloud offerings). This serves as a one-stop shop to docs and videos to get you started.

If you're able to sign up for a free trial, like I was, the product is worth a look. It also gives you some practice on how Academy and Console work which are applicable across many of the Oracle Cloud offerings. If the trial isn't available, or you just want something quick, then take a look at the videos.

http://docs.oracle.com/cloud/latest/data-visualization-cloud/data-viz-cloud_videos.htm