Browse Category


Oracle EPM / Windows Prerequisite Tuning [Part 1]

If you are installing Oracle EPM in a Windows environment, there are a plethora of settings you need to tweak per Oracle guidance.  To greatly simplify this process, I made a script that performs all of the steps for you, saving you a pile of time / effort.  There are a few steps that you’ll need to do manually; however, this will allow you to perform these steps in a fraction of the time *and* ensure that your servers are configured in a consistent manner.

NOTE:  Part 2 of this post will actually go into what these settings do.  For now, just enjoy a free script.

@echo off

@REM ----------------------------------------------------------------------------------------------------
@REM - Name: Infra_Win_Prereq.bat
@REM - Author : Charles Beyer
@REM - Creation Date : 11/2/2015
@REM - Last Modified DAte : 7/17/2017
@REM - DESC: The purpose of this script is to configure Oracle required default settings for Oracle
@REM -       Hyperion Enterprise Performance Management products running on Windows 2012, 2008, 2003
@REM -
@REM - Inputs :
@REM            %username% - Windows system variable containing the currently logged on user
@REM            %COMPUTERNAME% - Windows system variable containing the current machine name
@REM - Outputs : Command line success/fail responses
@REM -
@REM - NOTES: There are a few steps that cannot be performed via command line
@REM -         Those items are in the script and will be called out.
@REM -         User should perform the manually
@REM -         The logged on user while running this script should be the EPM Service Accout as some of the
@REM -         config items are a "per user" setting.
@REM -
@REM - Items Configured in this script are:
@REM -       DHCP Check (Should be using Static IPs)
@REM -       Time Sync w/ Domain Controller
@REM -       UAC Disable
@REM -       Local Security Policy Permission Settings
@REM -           (*NOTE* You need ntrights.exe for these steps. Available on Windows Resource CD of online download)
@REM -       Disable DEP
@REM -       Windows Firewall Disable
@REM -       Group Policy Updates (Disable force registry unload)
@REM -       Windows Power - Set to Max performance
@REM -       Adjust Advanced Display Settings to 'Best Performance'
@REM -       TCP Tuning Settings (Part 1)
@REM -       Worker Threads
@REM -       Disable Delete TEMP on logoff / Disable 'per user' temp folders
@REM -       TCP Tuning (Part 2)
@REM -       Remove TMP / TEMP User Environment variables
@REM -       Disable Anti-Virus reminder (I can't disable anti-virus software, just display message to add exceptions, etc.)
@REM -       Disable Internet Explorer Enhanced Security

@REM -----------------------------------------------------------------------------------------------------

@REM -------------------------- DHCP Check --------------------------------------
@REM Check for any Dynamic IP addresses as we should be used STATIC
@ECHO Checking for Dynamic IPs.
@ECHO If DHCP is shown as enabled, please investigate further.
@ECHO Static IPs are expected.
ipconfig /all | find "DHCP Enabled."

@REM Add space between command output

@REM -------------------------- Time Sync w/ Domain ---------------------------------
@REM Sync time to Domain Server
@ECHO Configuring Date/Time to be synchronized with Domain Controller
w32tm /config /syncfromflags:domhier /update
net stop w32time
net start w32time

@REM Add space between command output

@REM -------------------------- UAC Disable ---------------------------------
@REM Disable UAC
@ECHO Disabling UAC
reg add HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System /v EnableLUA /t REG_DWORD /d 0 /f

@REM Add space between command output

@REM -------------------- Local Security Policy Permissions --------------------
@ECHO Please review Location Security Policy Permissions and assign the service/install account
@ECHO 'Adding Logon as service' right
ntrights +r SeServiceLogonRight -u %username% -m \\%COMPUTERNAME%

@ECHO Adding 'Logon as batch job' right
ntrights +r SeBatchLogonRight -u %username% -m \\%COMPUTERNAME%

@ECHO Adding 'Act as part of the OS' right
ntrights +r SeTcbPrivilege -u %username% -m \\%COMPUTERNAME%

@ECHO Adding 'Bypass Traverse Checking'
ntrights +r SeChangeNotifyPrivilege -u %username% -m \\%COMPUTERNAME%

@REM Add space between command output

@REM -------------------- Disable DEP --------------------
@REM Disable Data Execution Prevention (DEP)
@ECHO Disabling Data Execution Prevention (DEP)
bcdedit /set nx AlwaysOff

@REM Add space between command output

@REM -------------------- Disable Firewall --------------------
@REM Disable Firewall
@ECHO Disabling Firewall for EPM
netsh advfirewall set currentprofile state off

@REM Add space between command output

@REM -------------------- Upgrade Group Policy --------------------
@REM Update Group policy to Disable force unloaded of user registry upon logoff
@ECHO Updating Group policy to Disable force unload of user registry upon logoff
reg add "HKLM\SOFTWARE\Policies\Microsoft\Windows\System" /v DisableForceUnload /t REG_DWORD /d 1 /f

@REM Add space between command output

@REM -------------------- Set Power Option to High Perf --------------------
@REM Set Power Option to High Performance
@ECHO Setting Power Option to High Performance
powercfg /S SCHEME_MIN

@REM Add space between command output

@REM -------------- Update Adv Settings for Best Performance --------------
@REM Update Advanced Settings / Adjust for Best Performance (UI)
@ECHO Updating Advanced Settings / Adjust for Best Performance (UI)

reg add "HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\VisualEffects" /v VisualFXSetting /t REG_DWORD /d 2 /f

@REM Add space between command output

@REM ------------- Registry Updates (TCP / Session Mgr / Term Svr) -------------
@REM Add Windows Registry settings for TCP Performance, Additional CPU Thread settings, Disable Delete Temp Directories on Exit
@ECHO Updating Registry settings for TCP, CPU, Temp/User
reg add "HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters" /v TcpTimedWaitDelay /t REG_DWORD /d 30 /f
reg add "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Executive" /v AdditionalCriticalWorkerThreads /t REG_DWORD /d 64 /f
reg add "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Executive" /v AdditionalDelayedWorkerThreads /t REG_DWORD /d 20 /f
reg add "HKLM\SYSTEM\CurrentControlSet\Control\Terminal Server" /v DeleteTempDirsOnExit /t REG_DWORD /d 0 /f
reg add "HKLM\SYSTEM\CurrentControlSet\Control\Terminal Server" /v PerSessionTempDir /t REG_DWORD /d 0 /f

@REM Add space between command output

@REM ------------------- Network Performance Settings -------------------
@REM TCP Performance Settings
@ECHO Apply TCP Performance Settings

netsh int tcp set global autotuninglevel=disabled
netsh int tcp set global chimney=disabled
netsh int ipv4 set dynamicport tcp start=1025 num=64000
netsh int ipv4 set dynamicport udp start=1025 num=64000
netsh int ipv6 set dynamicport tcp start=1025 num=64000
netsh int ipv6 set dynamicport udp start=1025 num=64000

@REM Add space between command output

@REM ------------------- Remove TEMP USER Env Variables -------------------
@REM Delete User TEMP variables
@ECHO Deleting Current User defined TEMP/TMP Environment Variables
@ECHO NOTE: You may see ERROR / Unable to find key messages. These indicate the
@ECHO TMP / TEMP environment variables have not been set for the current user.
reg delete HKCU\Environment /F /V TEMP
reg delete HKCU\Environment /F /V TMP

@REM Add space between command output

@REM ----------------- Display Virus Exception Reminder ----------------
@REM Virus software warning
@ECHO IF virus scanning is active on this machine, be sure to do the following:
@ECHO - Disable Scanning on Hyperion servers during software installation
@ECHO - Enable Scanning after install, but exclude the \Oracle folders.

@ECHO All steps complete, please review for any errors.

@REM ----------------- Disable IE Enhanced Security ----------------------
@REM This disables the IE enhanced security

@REM Disable for users (0 = disabled, 1 = enabled)
reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A7-37EF-4b3f-8CFC-4F3A74704073}" /v IsInstalled /t REG_DWORD /d 0 /f

@REM - Disable for Admin Users (0 = disabled, 1 = enabled)
reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A8-37EF-4b3f-8CFC-4F3A74704073}" /v IsInstalled /t REG_DWORD /d 0 /f

Kscope13 is approaching!

With Kscope13 around the corner, I thought I’d share last year’s presentations:

For Kscope13, I’m presenting a follow-up to my API session which will see us complete a .NET desktop app from scratch to consuming web services via a tablet app.  Should be interesting for those looking to expand their product’s functionality via the APIs.

To see a full break down for Kscope 13, please go here :

See you there!

Enumerating Financial Reports via Workspace database

Disclaimer : This post will talk about direct database access. Do not perform unless you are sure you know what you are doing and I’m not liable! I’m just passing along information I’ve learned in case it helps you out….
Recently, we decided that we wanted to review all of our reports to determine which ones we could drop and which ones we should keep. Over the past 7 years, things have accumulated and we literally have thousands of reports gasp between all of the backups, Work In Process, and flat out duplication. In order to handle the review of this we wanted to assign prople to review specific sections of the reports. I was asked to provide a nice excel ‘report’ that would break down the report / folder hierarchy so that we could assign it.
That is when I realized that I really couldn’t locate a tool / report that would generate such a report listing!
In the old 7.2.5 version of Reports, it was relatively easy to get this information as all of the report information (including actual report content) was stored in a single database table.
With System 9 and Workspace, this is no longer true. With Workspace, the files are stored on the server’s file system in a manner consistent with the hierarchy of your reports/folders. Intially I thought I would be able to use this file system to build my report using simple DOS commands. (i.e. dir /s > reportlist.txt) Unfortunately though, the file names of the reports and folders are not ‘human readable’. (In order to decipher it, you’d have to go into the workspace database and x-ref these names to a table, etc)  Fortunately; however, you can build a report listing via SQL. The script below recursively builds a report with the following output :

(NOTE: Actually the script includes more columns of output; however, for display purposes here, I’m only showing the name and description)

Report Name     Description
AnnualReports NULL
===Actg-YContractual Obligations – Multiple Entities Contractual Obligations
===Annual Validation Summary Annual Validation Summary
===Customers and End User Markets NULL
====== 001 – End Use Markets Book End Use Markets Book
====== 002 – Top Customers Book Top Customers Book
====== 010 – Customers Book Reports – Corp NULL
========= Revenue by Top Customers – All Sectors – Pr Yr Comp Customer Revenues
========= Revenue by Top Customers – All Sectors – Pr Yr Comp -TEST Customer Revenues
====== 020 – Markets Book Reports – Corp NULL
========= Revenue by End User Markets – All Sectors End Use Markets
====== 030 – All Sectors – End Use Markets – Pr Yr Comp NULL
========= End Use Markets – Pr Yr Comp Customer Revenues


SQL 2005+ Query
USE BIPLus_prod;





— Anchor member definition



WHERE d.PARENT_FOLDER_UUID = ‘0000011b42b29e00-0000-0b4d-c0a8e22a’ UNION ALL

— Recursive member definition

SELECT d1.PARENT_FOLDER_UUID, d1.CONTAINER_UUID, d1.NAME, d1.DESCRIPTION, Level + 1, cast(Sort + ‘|’ + d1.Name as nvarchar(1024)), d1.CREATION_DATE, d1.LAST_MODIFIED_DATE




— Statement that executes the CTE


How this works :
The table V8_CONTAINER holds the main part of the report / folder entry data. In this table you will find Names, Creation Dates, Modified Dates, Owner name, UUID, and Parent UUID information. In order to build a hierarchy, you will need to pay careful attention to the UUID fields. CONTAINER_UUID is the internal name of the object in Workspace. (i.e. non-user friendly name) PARENT_FOLDER_UUID is a reference back to the item’s parent.
With this in mind, if you know the UUID of your starting point, you can simply recurse from there to get a listing of all folders and subsequent reports. In the script above I start off looking for an entry that is the starting point of our reports that we wanted to audit. For your purposes you will need to change this to what your starting point is.

NOTE : If you want to get everything in the table, use REPORTMART. REPORTMART is the parent to everything in the table.
I hope my walk through wasn’t terribly boring and helpful. I apologize for only providing a script that works in SQL Server; however, that is what we use at our site and there should be enough explanation here for someone relatively proficient with Oracle to write a similar script if they desire.

Synchronizing FDM (9.3.1) Applications w/ T-SQL

At my current location we have 1 main and 2 supporting FDM (9.3.1) applications. The main app is used to load production G&L data, and the supporting apps are used for loading our Budget and Forecast data and all are separated to ensure that a mistake in a multi-load template is minimized.
The problem we have with this setup is that we have to do 3x the maintenance work when it comes to users and locations.

While the users and locations are the exact same in each application, there are some differences; however: a.) Data Maps in Budget / Forecast are simply * to * as we use multi-load templates that have hte actual HFM account/entity names in them whereas production maps account for each company’s GL b.) Categories are different as we limit each FDM app to only the exact Category (Scenarios) required. c.) Periods are different as we limit each FDM app to only the needed periods required.

The solution for me was to implement a SQL direct copy routine for the information I need to move. This helps as I can : a.) automate this task b.) copy only what I want c.) Perform the task relatively quickly.

While the Workbench offers the ability to import / export components, I ran into trouble with it not correctly importing all of the data and it also restrcted my flexibility. Copying the entire database was not viable either as there were pieces I did not want to update….
The script below illustrates how to perform a copy via SQL. *bold*Please note that if you want to use this (or base a script off of this) that there’s absolutely no warranty and its use at your own risk.bold I’m fairly comfortable that this works fine (as I’ve tested this in our Dev), but you should test it on your own as well.

Also note, that this script does not copy ALL tables as I didn’t need them all for my purpose.

The script performs the following :

– Clears data from Target Database – Data Archives – Data Maps – Data
– Logs – Import Groups/Items – Validation Groups/Entities – Users / Partition Security – Partition Hierarchies / Links – Partitions (locations)
– Copies data from Source Database: – Import Groups / Items – Validation Groups / Items – Partitions (locations) – Partition Hierarchy / Links – Users / Partition Security
– Updates Parent Location setting on all Data Load locations (This is since I want all of my data load locations in the Budget / Forecast apps to use a * to * map. I added a new location in the Prod Database which is defined with the * to * mappings and when I copy everything over, I want the locations to use this map instead of their production G&L map.