Wednesday, June 28, 2017

Older Hyperion Systems and SQL Server 2008

Most Hyperion / Oracle EPM shops tend to drag their feet when the topic of upgrading comes around. It's OK, you're in good company! Upgrading can be a difficult process, depending upon which Hyperion modules you have and how far behind in versions you are.
(Disclaimer: If all you have is just Essbase, upgrading is super easy. Otherwise, upgrading can be a "project")
If your EPM environment is version or older, chances are high you're hosted on Windows Server 2008 R2. While I'm not afraid of UNIX, most Hyperion implementers seem to be, and thus the vast majority of EPM environments I've seen are Windows-based. Furthermore, just over half tend to use SQL Server rather than Oracle for their back-end relational databases.

So, this blog post is directed to the community who is using SQL Server 2008 or SQL Server 2008 R2 for their Hyperion environments.

Surrender, Dorothy, and upgrade!
As you prepare your capital budget for fiscal 2018, make sure you include a Hyperion system upgrade. Here's why:
Microsoft Extended Support ends for SQL Server 2008 R2 on July 9, 2019
If you wait until the last minute to plan your upgrade -- that is to say, sometime in 2019 -- you will likely have to wait in line. Us EPM on-premises infrastructure consultants will be busier than a roofing contractor after a thunderstorm rolls through town.

Now is the perfect time to plan your on-premises Hyperion upgrade. Oracle Sales is heavily pushing their Cloud offerings, so the pace of new on-premises implementations is decreasing. This means you could get a good deal on an upgrade project between now and 3rd Quarter 2018.

Monday, June 26, 2017

Quick & Dirty FDMEE File Trigger Alarm

From time to time, our FDMEE data integration jobs depend upon an upstream system delivering a file to us.  Things can get hectic when the file isn't delivered on time, due to a delay or hiccup upstream.

This post offers a set of scripts you may automate, so that you can stay informed whenever a file is late to arrive.

Script #1: The DOS wrapper script

echo off
REM FileAge.bat
REM This script sends an email if a specified file has a timestamp
REM that is older than a specified number of minutes.
REM Syntax:  FileAge.bat \full\path\to\file Minutes
REM Example: FileAge.bat D:\Oracle\FDMEE\Inbox\FileTrigger.txt 120
REM The above example would test if the file is 2 hours old or not.
REM  Written on 10/01/2014 by Dave Shay (Datavail)
REM Modified on 12/15/2014 by Dave Shay - Added check for prior alert to prevent duplicate emails.
REM Modified on MM/DD/YYYY by Your Name - Briefly list changes

REM Variable declaration section
REM ----------------------------------------------

REM Multiple emails may be specified in the EMAILDISTRO variable.
REM Use a comma-delimited list of emails with no spaces


REM Internal company email server settings.
REM Consult your IT department regarding the SMTP server name.
REM The SMTP server should allow anonymous relaying inside the company firewall.

set ALARMFLAG=%1.alarm

powershell.exe D:\Scripts\FileAge.ps1 %1 %2
if %errorlevel% == 0 (
 if exist %ALARMFLAG% (   
  echo All Clear email is sent out.
  powershell.exe Send-MailMessage -from %EMAILFROM% -To %EMAILDISTRO% -Subject 'All Clear %1 File Watcher' -smtpServer %SMTPSERVER% -Body 'The file %1 has been updated on %COMPUTERNAME%. Please do not reply to this automated message.'

  exit 0
 exit 0

if %errorlevel% == 1 (
 if exist %ALARMFLAG% (
  echo File is older than the specified number of minutes. Sending email.
  exit 0
 echo File is older than the specified number of minutes. Sending email.
 powershell.exe Send-MailMessage -from %EMAILFROM% -To %EMAILDISTRO% -Subject '%1 File Watcher' -smtpServer %SMTPSERVER% -Body 'File %1 on %COMPUTERNAME% is older than %2 minutes.  Please check the servers for hung processes.  Please do not reply to this automated message.'
 echo "Alarm conditions exits" > %ALARMFLAG%
 exit 0

Script #2: The PowerShell script invoked behind the scenes

# FileAge.ps1
# This Windows PowerShell script compares a specified file against
# a specified number of minutes.  If the file's timestamp indicates
# an age that is equal to or newer than the specified number of minutes,
# then a 0 is returned.  Otherwise a 1 is returned.
# Syntax: powershell FileAge.ps1 \full\path\to\file minutes
# Example: powershell FileAge.ps1 D:\oracle\FDMapp\Inbox\FileTrigger.txt 120
# The above example would test if the file is 2 hours old or not.
# If you receive a security policy error about “unsigned” Powershell scripts when 
# running this process, open a command prompt and type:
# powershell.exe Set-ExecutionPolicy Unrestricted
#  Written on 10/01/2014 by Dave Shay (Datavail)
# Modified on MM/DD/YYYY by Your Name - Briefly list changes

param($FileToCheck, $Minutes)

$lastWrite = (get-item $FileToCheck).LastWriteTime
$timespan = new-timespan -minutes $Minutes

if (((get-date) - $lastWrite) -gt $timespan) 
 # File is older than specified number of minutes
    exit 1
} else 
 # File is equal to or newer than specified number of minutes
    exit 0

Putting it all together!

One time only, open a DOS command prompt and paste this command, so that your Powershell script will work:

powershell.exe Set-ExecutionPolicy Unrestricted

Now you're ready to go into the Windows Task Scheduler, or your favorite scheduling tool, and create a job that fires off:

D:\Scripts\FileAge.bat \path\to\file number_of_minutes

For example:

D:\Scripts\FileAge.bat D:\Oracle\FDMEE\inbox\hourlyload.txt 59

In this example, you could setup the job to run hourly, and it would send an email alert if you hourlyload.txt file is 59 minutes old or older.