Library Access

Once upon a time several people were working on several different applications in our development environment.  We were adjusting shared dimensions, building and deleting HFM applications, and generally kicking sand around the playground.

http://aminoapps.com/page/cartoon/9847425/forget-the-internet-when-you-have-a-library-card-arthur-meme-contest

Then one day one of the developers couldn't get into the Application Library.  They got an error dialog with the title Requested Service not found and the helpful text:

----
Requested Service not found
----
Code: com.hyperion.awb.web.common.DimensionServiceException

Description: An error occurred processing the result from the server.

Actor: none

We also noticed we couldn't get to the Dimension Library.  And our scheduled taskflows had stopped working.  Since we could still login and open our HFM and Essbase applications we could rule out issues with foundation services, HFM, and Essbase.  Everything pointed to a problem with EPMA.

Analysis

While there were errors in the EPMA logs there wasn't an obvious smoking gun.  We engaged both Oracle support and our integration partner.  The odd thing about Oracle support support is that there is no option for the EPMA product.  All of the disciplines are cross-trained on EPMA but you have to really hunt around for someone who is an EPMA expert.

Between the three of us we noticed this set of errors in the DimensionServer.log:

[2017-08-26T23:57:18.764-04:00] [EPMADIM] [NOTIFICATION:32] [EPMADIM-1] [EPMADIM.Hyperion.DimensionServer.LibraryManager] [tid: EPMA Server Startup] [ecid: disabled,0] Starting 11.1.2.2.00 FixInvalidDynamicPropertyReferences task
[2017-08-26T23:57:19.263-04:00] [EPMADIM] [INTERNAL_ERROR:32] [EPMADIM-1] [EPMADIM.Hyperion.DimensionServer.LibraryManager] [tid: EPMA Server Startup] [ecid: disabled,0] Failed 11.1.2.2.00 FixInvalidDynamicPropertyReferences task    at System.String.InternalSubStringWithChecks(Int32 startIndex, Int32 length, Boolean fAlwaysCopy)
   at Hyperion.DimensionServer.DAO.LibraryManagerDAO.FixInvalidDynamicPropReferencesForDimension(Int32 libraryID, Int32 applicationID, Int32 dimensionID, String dynamicProps, Int32 propertiesDimID, Int32 dynamicPropsPropID)
   at Hyperion.DimensionServer.DAO.LibraryManagerDAO.<>c__DisplayClass64.<FixInvalidDynamicPropReferencesForLib>b__63(DALDatasetCommand command)
   at Hyperion.DataAccessLayerCore.DataAccessLayer.ExecLoadSQL(String SQL, Action`1 onSetParams, Action`1 onRead, DatabaseContext context)
   at Hyperion.DataAccessLayerCore.DataAccessLayer.ExecLoadSQL(String SQL, Action`1 onSetParams, Action`1 onRead)
   at Hyperion.DimensionServer.DAO.LibraryManagerDAO.FixInvalidDynamicPropReferencesForLib(Int32 libraryID)
   at Hyperion.DimensionServer.DAO.LibraryManagerDAO.FixInvalidDynamicPropReferences()
   at Hyperion.DimensionServer.LibraryManager.FixInvalidDynamicPropertyReferences()
[2017-08-26T23:57:19.294-04:00] [EPMADIM] [ERROR:32] [EPMADIM-1] [EPMADIM.Hyperion.DimensionServer.Global] [tid: EPMA Server Startup] [ecid: disabled,0] An error occurred during initialization of the Dimension Server Engine:  startIndex cannot be larger than length of string.
Parameter name: startIndex.    at Hyperion.DimensionServer.LibraryManager.FixInvalidDynamicPropertyReferences()
   at Hyperion.DimensionServer.Global.Initialize(ISessionManager sessionMgr, Guid systemSessionID, String sqlConnectionString)
[2017-08-26T23:57:19.294-04:00] [EPMADIM] [NOTIFICATION:32] [EPMADIM-1] [EPMADIM.Hyperion.DimensionServer.Utility.ChannelUtility] [tid: EPMA Server Startup] [ecid: disabled,0] Listening using IPv4

(I'm copying everything in the block above so it gets found by search engines.  But I highlighted the failed task that clued us into the solution.)  These errors were thrown each time EPMA services restarted.  And the errors started around the time the taskflows started failing.  Earlier in the log we just see the FixInvalidDynamicPropertyReferences task start and finish.  This looked like the culprit.

Solution

If you bingoogle “DimensionServer FixInvalidDynamicPropertyReferences” you find a couple articles that show the error above:

http://hyperion-epm-bi.blogspot.com/2016/01/epm-patch-11122500-breaks-epma.html
http://brovanture.com/news/blog-epm-patch-11-1-2-2-500-breaks-epma

Both articles note the cause as a specific Oracle patch.  It is an older patch that was superseded in our environments.  Since there were no recent patches applied we initially glossed over this.

But the fix is to run a SQL UPDATE statement to replace blank strings with NULL values:

UPDATE DS_Property_Dimension
   SET c_property_value = null
   FROM DS_Property_Dimension pd
      JOIN DS_Library lib
         ON lib.i_library_id = pd.i_library_id
      JOIN DS_Member prop
         ON prop.i_library_id = pd.i_library_id
            AND prop.i_dimension_id = pd.i_prop_def_dimension_id
            AND prop.i_member_id = pd.i_prop_def_member_id
      JOIN DS_Dimension d
         ON d.i_library_id = pd.i_library_id
            AND d.i_dimension_id = pd.i_dimension_id
  WHERE
      prop.c_member_name = 'DynamicProperties' AND
      pd.c_property_value IS NOT NULL AND pd.c_property_value = '';

Since we had exhausted all other avenues we replaced the UPDATE statement with a SELECT statement:

SELECT c_property_value
   FROM DS_Property_Dimension pd
      JOIN DS_Library lib
         ON lib.i_library_id = pd.i_library_id
      JOIN DS_Member prop
         ON prop.i_library_id = pd.i_library_id
            AND prop.i_dimension_id = pd.i_prop_def_dimension_id
            AND prop.i_member_id = pd.i_prop_def_member_id
      JOIN DS_Dimension d
         ON d.i_library_id = pd.i_library_id
            AND d.i_dimension_id = pd.i_dimension_id
  WHERE
      prop.c_member_name = 'DynamicProperties' AND
      pd.c_property_value IS NOT NULL AND pd.c_property_value = '';

The query returned a handful of records where the c_property_value was blank instead of NULL.  We ran the UPDATE query and after restarting the services the libraries, taskflows, and other EPMA features were available.

Conclusion

Prior to this we had issues with dimensions in one of the applications under development.  Part of fixing that required deleting and importing dimensions and adjusting attributes.  Our suspicion is that somewhere along the line blanks got imported instead of NULLs.

The first lesson is that EPMA issues can be tricky to resolve.  It seems odd that Oracle wouldn't have support staff focused on EPMA since that binds all of the other EPM featuers together along with foundation services.

The other lesson is that if you have an issue and find something that looks like it might help don't automatically discard it just because your situation is different.  Many times there is a way to check if the solution might apply to you.

FDQM scripting errors

Financial Data Quality Management (FDQM) is used to move data into the EPM financial applications HFM and Essbase.  It has powerful and flexible facilities for parsing flat files for import.  It can also use scripts to import data, define conditional mappings, or that get triggered at certain events.  The scripts have a .uss extension but use vbScript as the language.  Scripts are stored in the \Data\Scripts folder of the application and can be edited directly or using the FDM Workbench.

Because we have the full power of vbScript there is a lot of sophisticated processing available.  We can also leverage ADO database connections to pull data from any accessible relational data source.

This post focuses on import scripts.  In a recent project I ran into two issues that gave error messages, one of which was not particularly useful and the solution undocumented.

The situation is a working script that pulls data from a SQL datamart for a single entity.  The script generates a SQL command that includes a WHERE clause which uses the FDQM POV to define the location.  The customer wanted another copy of the script to pull data for all entities, then use that new script as the import format for the parent location.

Easy-peasy.  We copy the script, rename it, update our WHERE clause to pull for all entities, create another import format that uses the new script, and assign that format to our parent location.  In this case the initial script was called HFMSQL and our edited copy is called HFMSQL_ALL.  (I'm won't detail the mechanics of managing the import formats and locations since that is well documented.)

Error 1

The first error is reasonably descriptive.

Error: An error occurred importing the file.
Detail: Script filename [HFMSQL_ALL.uss] is different from
the procedure name entered in the script file

The first line of an import script defines a function.  That function has to have the same name as the import script file.

The error was caused by keeping the original the function name HFMSQL for the new HFMSQLscript HFMSQL_ALL.

Making the function name match the script name resolves the error.

Error 2

This is the error that gave me fits.

Error: Import failed. Invalid data or Empty content.

This looks like we aren't getting any data out of our edited query.  A handy troubleshooting tool is to use the vbScript FileSystemObject to dump information to a text file.

Create a FileSystemObject and then use the CreateTextFile method to make the debug file.  The parameter True indicates that we will overwrite an existing file.

Later in the script I write the SQL query string which is generated by the script to the debug file

The WriteLine method writes the SQL string to the debug file.  The next step sends the SQL string to the SQL connection and pulls it into the record set that gets processed later in the script.

I re-ran the import, then opened the debug file and copied the SQL query and ran that directly on the SQL server.  The query returned the records we expected.  So I don't have empty content, but how could the data be invalid?

Just to be sure I added similar debugging statements to the original working script and adjusted the new script to pull from just the location where the working script was attached.  The SQL statements that got generated were identical and running them directly on the SQL server returned the same results set.

Sometimes it helps to bring in a fresh pair of eyes so I worked with an associate who double checked my findings.  We then created another copy of the working script and ran into the issue in Error 1.  While fixing that error when he suggested searching for the original script name.  At the bottom of the script we found this:

This is a standard practice most sample scripts you find.  After the load completes you set the ActionType and ActionValue which gives the success message on completion.  But note the last line which sets the function to return True to signal a successful completion to FDQM.  This variable name has to match the function name which has to match the script name.

So this is another easy fix but it took a while to resolve, partly because we weren't paying attention when we copied the scripts, but mostly because the error message does not indicate the real source of the problem.  Hopefully this post can save someone trouble next time they run into this error.

Permanent temporary files

We update the metadata for our HFM applications at least once per month.  Before updating the shared dimension library and deploying the production application we test all of the changes in our development environment.

During a recent test the deployment in development failed.  Usually a failure is during the deployment phase due to some misconfiguration in the new dimension members.  In this case the deployment failed during the initial export with the error message:

Detail : An error was encountered while executing the Application Export:  An error was encountered during export: The file exists.

Oracle Doc ID 1639083.1 shows the same error for Planning applications.  Because Planning and HFM both use the EPMA dimension and application libraries you can often extrapolate from the Planning solutions to solve HFM problems.

The article says the <SystemDrive>:\Windows\temp folder is full and the solution is to delete files from that directory.  But my C:\ drive wasn't full and there weren't many files in the \windows\temp directory.

Troubleshooting

According to the Oracle doc we were running out of file names.  But where?

The deployment is performed by EPMA and it fails before HFM starts its processing.  (Years ago an Oracle support engineer told me that during a deployment all the processing up to 20% is EPMA and after that it is HFM.  At any rate we don't get past the export so I know definitively that the problem was with EPMA.)

SysInternals has a bunch of powerful utilities for Windows systems.  I've been using these utilities since the WindowsNT days.  The author of the utilities, Mark Russinovich, was eventually hired as a technical fellow at Microsoft and is now the CTO of Azure.  He's one of the smartest guys you can follow and he's an engaging and articulate speaker if you see him give a presentation.

In this case I had to monitor the processes on the EPMA server to see what caused the failure.  From live.sysinternals.com I downloaded ProcMon and ran it on the EPMA server.  ProcMon will monitor everything on the server so you need to apply filters to isolate the information you want.  A handy technique is to let it capture for a few seconds, then stop the monitor, right-click any processes you don't care about such as explorer.exe, svchost.exe, your anti-virus software, etc and select Exclude.  This adds the exclusions to the current filter.  Repeat until you only have the handful of processes of interest, in this case anything related to EPMA.  Then clear the log, start the capture, and redeploy.

Once the deployment failed I stopped the capture and saved the results.  ProcMon captures access to the file system and registry as well as network and process thread events, so I removed everything except file system requests.  I didn't have to scroll down very far to see thousands of events from EPMA_Server.exe with a result of NAME COLLISION.  These are all in the AppData\Local\Temp directory of the user account under which the services run.

The files are named tmpXXXX.tmp where XXXX is appears to be a random set of 4 hex digits.  Some of these files are created during application deployment.  We do a nightly LCM export of EPMA for backup purposes and which also creates .tmp files.  Some of the .tmp files are XML properties of the applications and some are binary files.  Since this is our dev environment and we have many test applications, do regular deployments of HFM applications, and export EPMA nightly we ran out of the 65,535 possible names for the .tmp files.

Solution

As the Oracle doc implies, we just delete the .tmp files.  They are, after all, just temporary files in a temp directory.  Why Oracle doesn't delete them automatically or overwrite existing files instead of throwing an error is a topic for philosophers and software engineers to resolve.

Another location where files accumulate is \Oracle\Middleware\user_projects\epmsystem\tmp.  That folder holds temp files for shared dimension library updates, diagnostic framework, and web logic, among others.  Beneath this \tmp folder is a folder named EPMA that holds temp XML files with all properties of all dimensions in redeployed applications as well as temp ADS files created during LCM exports from EPMA.  Also beneath the \tmp folder is a folder oracle\tmp which has subfolders holding log files generated during HFM data syncs.

For us that issue came to light since we not only do nightly LCM exports of EPMA, we also have reporting applications that are updated nightly.  We use HFM taskflows to update the metadata, redeploy the applications, and run datasyncs to load data.  Because the .xml files generated during the redeploy can be large and we went through many rounds of testing, the data drive eventually filled to capacity which killed all activity on our EPMA server.

Our solution is regular maintenance to delete temp files in both the AppData\Local\Temp and the epmsystem\tmp folders on servers running the Hyperion EPMA Server service.  This task is easy enough to complete manually that I have not created an automated process for it.

But it is curious that Oracle doesn't automatically delete, or provide facilities for automatically deleting, these temporary files.  If you know of anything built into the products to address these temporary files please share that information in the comments below.

EAL and IIS

Oracle Essbase Analytics Link for HFM (EAL) is a pretty cool product.  We use it to move data from HFM to Essbase over EAL bridges.  The EAL bridge defines the source and target applications as well as configuration parameters for the data transfer.  Data can be moved on demand but we can also create a transparent bridge which lets us have near real-time replication of our HFM data into Essbase.

https://www.pinterest.com/pin/100416266663858663/

This comes in handy for reporting purposes.  We use HFM to do the translations and consolidations of our financials and leverage Essbase for reporting.  During the close cycle we don't have to sacrifice resources needed for consolidations with reporting activity.  In addition we can leverage the Essbase calculation engine to add other accounts or dynamic scenarios that we use for reporting but don't need for normal close calculations.  So we let Essbase do the calculations for prior year or budget-to-actual comparisons so that HFM and Financial Reporting don't have to do it.

You can also do some management of the metadata during the transfer.  You can filter dimensions so you only bring over the members you need in Essbase.  You can rename dimensions which can be handy if you have legacy spreadsheets or reports that use different names.  You can also migrate security so that the security on the Essbase application matches the HFM application.

Product description

EAL has a few moving parts.  There is the EAL service (Hyperion Essbase Analytics Link Server - Web Application) that oversees and manages operations.  The Data Synchronization Service (Hyperion Essbase Analytics Link - DSS) is responsible for moving data from HFM to Essbase and does the bulk of the work.  (Usually these two services are installed on a server other than the HFM or Essbase servers so as to not impede performance of other HFM and Essbase activity).  The Financial Management Connector lives on one HFM server to facilitate communication with the HFM applications.  And there is an add-in for Essbase Administration Server (EAS) that is used to configure all of the pieces and create bridges to move data from HFM to Essbase.

There are also some back-end pieces these services need.  A RDBMS database is needed to store the configuration information which can be on either Oracle or MS SQL server.  The Financial Management Connector uses IIS to host a website named livelink that provides access the .dlls required to move the data.  If you are running HFM on Windows 2008 or later then you have to enable IIS 6 compatibility.

Problem and Solution

Our situation was a patch for EAL that was rolled back partway through the install.  When patching EAL the current version has to be completely uninstalled before the new version is installed.  Because the configuration data is held in the database that information is retained so the upgrades are usually painless.

After the rollback we tried to refresh the metadata for an existing bridge.  We use dynamic accounts so I deleted the existing region for the HFM application and created a new region to enable the dynamic accounts.  But when I tried to create the new region I saw this message in the EAS status window:

Data Synchronization Server database cannot be created

This points an an issue with the DSS.  After confirming that DSS was correctly configured and the database was valid we checked the dss.log file and saw:

[23 Nov 2015 16:21:39] [dbmgr] ERROR: HR#07722: Cube 'main_cube' of application 'MyApp' is not registered.
[23 Nov 2015 16:21:51] [dbmgr] ERROR: last message repeated 1 more time
[23 Nov 2015 16:21:51] [dbmgr] WARN : HR#01566: can't open directory "D:\oracle\product\EssbaseAnalyticsLink\oem\hfm\MyApp\Default": The system cannot find the path specified. [3]

But the path did exist.  We tried adjusting permissions but that was no help.  So we checked the eal.log which showed:

[2015-Nov-23 16:24:08] java.lang.OutOfMemoryError Exception [HR#09746]: in jni_GetStringUTFChars (src/jvm/api/jni/jni.c:963).
[2015-Nov-23 16:24:08] Attempting to allocate 256M bytes
[2015-Nov-23 16:24:08] There is insufficient native memory for the Java
[2015-Nov-23 16:24:08] Runtime Environment to continue.
[2015-Nov-23 16:24:08] Possible reasons:
[2015-Nov-23 16:24:08] The system is out of physical RAM or swap space
[2015-Nov-23 16:24:08] In 32 bit mode, the process size limit was hit
[2015-Nov-23 16:24:08] Possible solutions:
[2015-Nov-23 16:24:08] Reduce memory load on the system
[2015-Nov-23 16:24:08] Increase physical memory or swap space
[2015-Nov-23 16:24:08] Check if swap backing store is full
[2015-Nov-23 16:24:08] Use 64 bit Java on a 64 bit OS
[2015-Nov-23 16:24:08] Decrease Java heap size (-Xmx/-Xms)
[2015-Nov-23 16:24:08] Decrease number of Java threads
[2015-Nov-23 16:24:08] Decrease Java thread stack sizes (-Xss)
[2015-Nov-23 16:24:08] Disable compressed references (-XXcompressedRefs=false)

This looked like it was a memory issue so we tried changing some of the options in the EAL configuration tool.  There are also some java heap options in the registry at HKLM\SYSTEM\CurrentControlSet\services\Hyperion Essbase Analytics Link Server - Web Application\Parameters in the string value CmdLine.  None of this helped.

Finally we checked the livelink web site configuration and noticed that it was using the DefaultAppPool.  Ordinarily the default pool enables 32-bit applications.  But EAL requires 64-bit applications so the application pool has to have 32-bit applications disabled.  We already had an application pool specifically for EAL but between the partial upgrade and the roll-back that got reset.  Reconfiguring the LiveLink web site to use the 64-bit application pool resolved the problem.

There are other errors that can occur if you don't disable 32-bit applications for livelink.  Doc ID 1997467.1 reports that you can get this error in the eal.log:

An unknown error has occurred in the HsxAuthentication object.

And who doesn't love an unknown error?

Doc ID 1489138.1 reports that the eal.log will show this error:

Server/Cluster is incorrectly configured. Please reconfigure your Cluster or Server connection.
Server/Cluster is incorrectly configured. Please reconfigure your Cluster or Server connection.

And, yes, the error is duplicated in the log file.

These are different errors but they all have the same resolution which is to create an application pool for EAL, set Enable 32-Bit Applications to false, and assign that app pool to the livelink website.  This is an easy fix and an easy thing to check as you are troubleshooting EAL issues.