Intro

Just a blog listing some weird issues and their solutions with regards to the Oracle Hyperion applications I manage.
Hopefully it will help someone else resolving these -often cryptic- incidents.

Applications involved are:

Hyperion Financial Planning
Hyperion Business Rules
Hyperion Calculation Manager
Hyperion Financial Management
Hyperion Analytic Services (Essbase)
Hyperion Reporting and Analysis
Hyperion Financial Data Quality Management (FDM)
Hyperion Workspace

All running version 11.1.2.1.0

Wednesday, February 5, 2014

Arranging the Hyperion services

If one installs EPM 11.1.2.1 in a distributed environment, it's components setup as clusters it generates an impressive amount of services. These services aren't named consistently and their startup order and dependencies are not very clear. You can't rename them, so I decided to create some order from the chaos by modifying their descriptions instead. This has several benefits:

  • Hyperion services are grouped together if sorted on description
  • Hyperion services gets sorted in the order in which they should startup
  • You can add a more meaningful description

Since several servers needed to be modified a script was setup to perform this task. It can be run locally on the server or remotely by specifying a servername as parameter. It has been written in PowerShell, so it will run (and act) only on Windows servers.
Also, in our environment, HFM, FDM, Reporting & Analysis and Financial Reporting are running separately from Planning and Essbase so the descriptions reflect this. You may want to change descriptions to suit your own situation.

# Name           : Set_Services.ps1
# Purpose        : Change description of Hyperion services and set them to manual. Sorting the services by description
#                  will group the Hyperion services and sort them according to their order of startup.
# Parameter     : Servername - optional, default: localhost
# Dependencies    : None
# Author         : P. da Graça
# Remark         : Edited with Notepad++, layout maybe off in other editors
#
# When            Who                What
# 05-02-2014    PdaGraca        Initial setup in PowerShell

# optional servername as a parameter to act on remote server. Default is local server.
Param([Parameter(Mandatory=$false)][string]$servername="localhost")

# first set up a hash table with service names as keys and descriptions as values. N.B. these are SERVICE names,
# not DISPLAY names! Change descriptions as needed. Use wildcards if servicenames contain EPM instance or other
#
variable values (see 02, 07 & 99), but make sure only one unique service gets selected. Script does not handle
# multiple services simultaneously!
$hyperion_services = @{
                        "HyS9AdminServer"                                = "Hyperion 01 - Weblogic Administration Server";
                        "OracleProcessManager_ohsInstance*"                = "Hyperion 02 - Oracle HTTP Services";
                        "HyS9FoundationServices"                        = "Hyperion 03 - Foundation Services (WorkSpace, Shared Services)";
                        "HyS9CALC"                                        = "Hyperion 04 - Calculation Manager";
                        "HyS9aps"                                        = "Hyperion 05 - Analytic Provider Services";
                        "Hyperion Studio Service BPMS bpms1"            = "Hyperion 06 - Essbase Studio Server";
                        "opmn_EPM_epmsystem*"                            = "Hyperion 07 - Essbase Server";
                        "HyS9eas"                                        = "Hyperion 08 - Essbase Administration Services";
                        "HyS9Planning"                                    = "Hyperion 09 - Financial Planning Web Application";
                        "Hyperion RMI Registry"                            = "Hyperion 10 - Financial Planning RMI Registry";
                        "Hyperion S9 Financial Management DME Listener"    = "Hyperion 04 - Financial Management DME Listener";
                        "Hyperion S9 Financial Management Service"        = "Hyperion 05 - Financial Management Application Service";
                        "HFMWebServiceManager"                            = "Hyperion 06 - Financial Management Web Service";
                        "HyS9RaFrameworkAgent"                            = "Hyperion 07 - Reporting and Analysis Framework Agent";
                        "HyS9RaFramework"                                = "Hyperion 08 - Reporting and Analysis Framework Web Application";
                        "HyS9FRReports"                                    = "Hyperion 09 - Financial Reporting Web Application";
                        "HyS9FRPrint"                                    = "Hyperion 10 - Financial Reporting Print Service (Generates PDF, batch, and scheduled job output)";
                        "HyS9FDMTaskManagerSrv"                            = "Hyperion 11 - Financial Data Quality Management Task Manager (Provides the ability to schedule FDM tasks.)";
                        "Oracled_oracle_middle*"                        = "Hyperion 99 - Configuration Manager";
                        }

ForEach ($entry in $hyperion_services.GetEnumerator())                                # loop through all keys (service names) in the hash table
    {
     $servicename=$entry.name                                                        # assign key to service name
     $description=$entry.value                                                        # assign value to service description
     $service=(Get-Service -computerName $servername -Name $servicename -ErrorAction SilentlyContinue) # check if service exists
     If ($service)                                                                    # if yes
        {
         $servicename=$service.name                                                    # assign definite servicename in case of wildcards
         $service_properties=(Get-WmiObject -ComputerName $servername -Class Win32_Service -Property StartMode -Filter "Name='$servicename'") # get service properties
         If ($service_properties.Startmode -eq "Automatic"){$service_properties.StartMode = "Manual"} # set to manual if it's automatic
         Set-Service -ComputerName $servername -Name $servicename -Description $description # set description
        }
    }

Result on the server:


All Hyperion services grouped together and sorted by startup order.

Thursday, January 16, 2014

PowerShell script to rotate HFM logfiles

Hyperion Financial Management 11.1.2.1 writes it's messages to two logfiles:

HsvEventLog.log
Hfm.odl.log.

These files are located on the HFM application server in the directory:

<EPM_ORACLE_INSTANCE>\diagnostics\logs\hfm

Over time, these files grow and viewing the system messages from within HFM may become slow. They also can become too large for external editors. That's why they need to be rotated occasionally.
Since HFM opens and writes these files continuously and downtime is sparse, any action to rotate these logfiles (renaming, clearing) is blocked. Multiple HFM processes will lock the files:

one or more HsvDatasource.exe
HsxServer.exe
CASSecurity.exe

This is This is why I wrote a PowerShell script (so Windows only), that will close the open file handles on the logfiles and rename them with a yyyymmdd extension. The script needs the SysInternal's handle.exe utility, which can be found here. The PowerShell functions to find the handles and close them were borrowed from Sean Kearney and can be found here.


# Name   : Rotate_HFM_Logfiles.ps1
# Purpose: Renames HFM logfiles HsvEventlog.log and Hfm.odl.log with a date extension
# Author : P. da Graça
# Remark : Edited with Notepad++, layout maybe off in other editors
#
# When            Who                What
# 13-01-2014    PdaGraca        New setup in PowerShell

# script variables

# first determine the EPM instance
$epmroot='D:\Oracle\Middleware'                                                # installation directory of EPM, change if installed elsewhere!
$epm=(Get-Item $epmroot\User_Projects\* -Include Epmsystem* -ErrorAction SilentlyContinue) # check if an EPM instance exists on the server
If ( $epm.Exists )                                                            # if yes
 {
  If ( $epm.Attributes.toString() -eq 'Directory' )                            # check if it's a directory
   {
    $epm_oracle_instance=$epm.FullName                                        # if yes, assign the full pathname to variable
   }
  Else                                                                        # if not
   {
    Write-Host '$epm is not a directory!'                                    # report back
    Exit                                                                    # and exit script
   }
 }
 Else                                                                        # if no instance is found
 {
  Write-Host 'No EPM instance found on server!'                                # report back
  Exit                                                                        # and exit script
 }                    

$hfm_log_dir="$epm_oracle_instance\diagnostics\logs\hfm"                    # location of HFM logfiles
$hfm_event_log="HsvEventLog.log"                                            # name of logfile 1
$hfm_odl_log="Hfm.odl.log"                                                    # name of logfile 2
$curdate=Get-Date -Format "yyyyMMdd"                                        # get current date in double digit format string
$curyear=$curdate.SubString(0,4)                                            # extract current year
$curmonth=$curdate.SubString(4,2)                                            # extract current month
$curday=$curdate.SubString(6,2)                                                # extract current day
$curmmdd="$curyear$curmonth$curday"                                            # generate a yyyymmdd extension

# These functions are borrowed from Sean Kearney (http://gallery.technet.microsoft.com/scriptcenter/79e3a8d3-fe68-4e6a-b41e-1fd22539e264),
# and used to make sure the logfiles are closed

Function global:GET-OpenFilePID()
    {
      param (
             [parameter(ValueFromPipeline=$true, Mandatory=$true)]
             [String[]]$HandleData
            )

      Process
            {
              $OpenFile=New-Object PSObject -Property @{FILENAME='';ProcessPID='';FILEID=''}

              $StartPid=($HandleData[0] | SELECT-STRING 'pid:').matches[0].Index
              $OpenFile.Processpid=$HandleData[0].substring($StartPid+5,7).trim()

              $StartFileID=($HandleData[0] | SELECT-STRING 'type: File').matches[0].Index
              $OpenFile.fileid=$HandleData[0].substring($StartFileID+10,14).trim()

              $OpenFile.Filename=$HandleData[0].substring($StartFileID+26).trim()
              Return $OpenFile
            }
    }
                
Function global:GET-Openfile()
    {
      [Cmdletbinding()]
      param ( 
             [parameter(Mandatory=$True, ValueFromPipeline=$True)]
             [String[]]$Filename
            )

      Process
            {
              If ( ! (TEST-LocalAdmin) ) { Write-Host 'Need to RUN AS ADMINISTRATOR first'; Return 1 }
              If ( ! ($Filename) ) { Write-Host 'No Filename or Search Parameter supplied.' }
              $HANDLEAPP="& 'D:\VionICT\Tools\handle.exe'"
              $Expression=$HANDLEAPP+' '+$Filename

              $OPENFILES=(INVOKE-EXPRESSION $Expression) -like '*pid:*'

              $Results=($OPENFILES | GET-openfilepid)

              Return $results
            }
    }

Function global:Close-Openfile()
    {
      [CmdletBinding(SupportsShouldProcess=$true)]
      param (
             [parameter(Mandatory=$True, ValueFromPipelineByPropertyName=$True)]
             [string[]]$ProcessPID,
             [parameter(Mandatory=$True, ValueFromPipelinebyPropertyName=$True)]
             [string[]]$FileID,
             [parameter(Mandatory=$false, ValueFromPipelinebyPropertyName=$True)]
             [String[]]$Filename
            )

      Process
            {
             $HANDLEAPP="& 'D:\VionICT\Tools\handle.exe'"                
             $Expression=$HANDLEAPP+' -p '+$ProcessPID[0]+' -c '+$FileID[0]+' -y'
             If ( $PSCmdlet.ShouldProcess($Filename) ) 
                    {
                      INVOKE-EXPRESSION $Expression | OUT-NULL
                      If ( ! $LastexitCode ) { Write-host 'Successfully closed'}
                    }
            }
    }

Function global:TEST-LocalAdmin()
    {
      Return ([security.principal.windowsprincipal] [security.principal.windowsidentity]::GetCurrent()).isinrole([Security.Principal.WindowsBuiltInRole] "Administrator")
    }

# End of borrowed functions   
   
Function Close-HFMLogFile()
    {
      param (
              [parameter(Mandatory=$True)]$FileName
             )

      Process
             {
               If (Get-Item .\$FileName -ErrorAction SilentlyContinue)            # if the logfile exists
                {
                  Get-OpenFile $FileName | Close-OpenFile -ErrorAction SilentlyContinue    # close any open file handles
                  $newname=$FileName.Replace(".log",".$curmmdd.log")            # generate a new filename with date extension
                  If (-not (Get-Item .\$newname -ErrorAction SilentlyContinue)) {Rename-Item .\$FileName -Newname .\$newname} # if the new name does not exist yet, rename the file
                }
             }
    }
   
Set-Location $hfm_log_dir

Close-HFMLogFile $hfm_event_log
Close-HFMLogFile $hfm_odl_log

Monday, November 18, 2013

Script to distribute files

EPM is often installed on several servers: web-, application- or database servers. and when a full DTAP cycle is used, the number of servers increases even more. To automate the distribution of files to multiple servers, I wrote the following script.

It has to run on the command line and will accept two parameters:

Path\Filename of file to copy
Path\Filename of file containing servernames to copy the file to


:: Name     : Distribute.cmd
:: Purpose  : Distributes files to other servers
:: Parameter: File to distribute
::              File with list of servernames to distribute to
:: Author   : P. da Graça
:: Remark   : Edited with Notepad++, layout maybe off in other editors
::              Run as (domain) user with sufficient access to the servers.
::
:: When            Who            What
:: 18-05-2011    PdaGraça    First Setup
:: 19-05-2011    PdaGraça    Skip copying to localhost
:: 23-05-2011    PdaGraça    If 'file to distribute' begins with @, the filenames in that file are copied
:: 24-05-2011    PdaGraça    If @distribute.txt is specified, all scriptfiles are copied to all servers
::

@Echo off

Setlocal

:: Set dynamic variables
For /f %%H in ('hostname') Do Set Hostname=%%H
For /f %%H in ('cd') Do Set ScrDir=%%H

:: Set general variables
Set Success=0
Set Error=0
Set ExitCode=0
Set Script=%~0
Set DistributionFile=Distribution.txt
Set AllServerFile=%ScrDir%\Servers-All.txt
Set ServerFile=%ScrDir%\Servers-DEV.txt

:: Main Routine
:: If no parameter specified, abort
If "%1" EQU "" Goto NoPar
:: If parameter starts with @, open the file and process its contents. Each line should list a filename to copy
:: Otherwise, copy the file itself. The ForPar variable is used to process the contents.
Set File=%1
If "%File:~0,1%" NEQ "@" Goto Verder
Set ForPar=/F
Set File=%File:~1,99%
:Verder
:: Check if file to copy exists
If Not Exist %File% Goto NoFile
:: Check if file with servernames has been specified, otherwise the default filename is used (see above).
If "%2" NEQ "" Set Serverfile=%~f2
:: If one specific @filename is given, use a specific corresponding file with the names of all servers as well. The @filename should
:: contain the names of this script and all supportfiles. This is used to distribute this tool across all servers.
If /I "%File%" EQU "%DistributionFile%" Set Serverfile=%AllServersFile%
:: Check if file with servernames exists
If Not Exist %ServerFile% Goto NoServerFile
:: Process the file itself, or the lines it contains.
For %ForPar% %%X In (%File%) Do Call :DoFile %%X

Set /A Total=%Success%+%Error%
Echo.
Echo Total filecopies    : %Total%
Echo Succeeded filecopies: %Success%
Echo Failed filecopies   : %Error%
:End
Goto :Eof
:: End of main routine

:DoFile
:: Add full drive and path to filename to copy
Set DoFile=%~f1
If Not Exist %DoFile% Goto NoFile
:: Process the file with servernames
For /f %%Y In (%ServerFile%) Do Call :CopyFile %%Y
Goto :Eof

:CopyFile
:: Check if the servername in the file is that of the current server. Skip the copy if it is.
for /f "tokens=1 delims=." %%I in ("%1") do Set ServerName=%%I
If "%ServerName%" EQU "%HostName%" Goto :Eof
:: The target filename is the source filename where Drive: is replaced by Drive$
Set ToFile=%DoFile::=$%
For /f %%I in ("%DoFile%") Do Set DoDir=%~dpI%
:: Check if target directory exists
For /f %%I in ("%ToFile%") Do Set ToDir=%~dpI%
:: Create target directory if it doesn't
If Not Exist %ToDir%\ Md %ToDir%
:: If target file exists, check if it's readonly
If Not Exist \\%1\\%ToFile% Goto Verder
Attrib \\%1\\%ToFile% | find " R " > Nul
If %Errorlevel% EQU 0 Set RO=ReadOnly
:Verder
:: Actual copying and errorhandling
If "%RO%" EQU "ReadOnly" Attrib -r \\%1\\%ToFile%
Copy %DoFile% \\%1\%ToFile% /V /Y >NUL 2>&1
Set El=%Errorlevel%
If %El% EQU 0 (Set ExitCode=0) & (Echo %DoFile% ^=^> \\%1\%ToFile% %RO%) & (Set /A Success=%Success%+1)
If %El% NEQ 0 (Set ExitCode=1) & (Echo %DoFile% ^!^> \\%1\%ToFile% %RO%) & (Set /A Error=%Error%+1)
If "%RO%" EQU "ReadOnly" Attrib +r \\%1\\%ToFile%
Set ReadOnly=
Goto :Eof

:NoPar
Echo.
Echo No file specified!
Call :Syntax
Set ExitCode=1
Goto End

:NoFile
Echo.
Echo File %File% does not exist!
Call :Syntax
Set ExitCode=1
Goto End

:NoDoFile
Echo.
Echo File %DoFile% does not exist!
Call :Syntax
Set ExitCode=1
Goto End

:NoServerFile
Echo.
Echo No serverfile specified!
Call :Syntax
Set ExitCode=1
Goto End

:Syntax
Echo.
Echo Syntax: %Script% filename(s) file_with_servernames
Echo or
Echo Syntax: %Script% @file_with_filenames file_with_servernames
Echo.
Echo Default file_with_servernames is %ServerFile%
Goto :Eof

Script to toggle read-only attribute

Over time, scripts, tools and other relevant files for application management accumulate on the servers. To prevent accidental removal of such files, I'd normally make these files read-only. Not foolproof ofcourse but enough to prevent most accidents.
To simplify editing scripts I've written a script that will toggle the read-only attribute of any file(s) that is/are dropped on it. The script is Windows only ofcourse.
:: Name : Switch_ReadOnly.cmd
:: Purpose: Switches readonly attibute on or off for file(s)
:: Author : P. da Graça
:: Usage : Drop file(s) on this script, it will switch the readonly
:: attribute. Run the script from the commandline to switch all files
:: in the current directory.
::
:: When Who What
:: 08-10-2013 PdaGraca First setup
::
@Echo off

Setlocal

Set File=%*
If Not Defined File Set File=*.*

For %%I IN (%File%) Do Call :ProcessFile %%I

:: Set script itself always to read-only, because you can't drop the script onto itself!
Attrib %0 | find " R " > Nul
If %Errorlevel% NEQ 0 Attrib +r %0
Goto :Eof

:ProcessFile
:: Skip directories
If exist %1\ Goto :Eof
:: Determine if file has readonly attribute
Attrib %1 | find " R " > Nul
Set El=%errorlevel%
If %El% EQU 0 Attrib -r %1
If %El% NEQ 0 Attrib +r %1
Goto :Eof

Thursday, November 14, 2013

Error(1051293)

Error(1051293)


Consultant reported an errormessage while accessing an Essbase database via Smartview: Error(1051293) Login fails due to invalid login credentials



His native user account was sufficiently provisioned for the Essbase application and its databases, but still got no access via Smartview.

The solution was to login to the Essbase admin console (EAS) and navigate to this particular database.



Set the user name and password to the native useraccount that was also used in the datasource of the Planning application:





Enter credentials, use Test to verify and Set to save this setting. I've done this for all databases of this application and everything was peachy again.



Workspace icons greyed out

End user reported greyed out icons in Calculation Manager, but only in one particular environment (development). Clearing the browser cached fixed the issue momentarily but after the next logon it re-appeared.
Also, different icons became greyed out when other applications were started, so it appeared to be Workspace related.





Searching the Oracle support site, I found document 1437036.1 and it appeared someone (probably the enduser himself) switched on Screen Reader support in the workspace settings:



Disabling this setting resolved the issue