PowerShell and Hyperion: Introduction

It’s been a little while since my last post.  Things have managed to get busy both at work and at home, but I’m happy to get back to it tonight.  This post will focus on providing an introduction to PowerShell and how we can use it with Hyperion products.

So what is PowerShell and why do I care?  If you use *nix…you don’t.  But for those of us that have our Hyperion installations in a Windows environment, you should!  In short, PowerShell is a powerful shell built into most modern versions of Windows (both desktop and server) meant to provide functionality far beyond your standard batch script.  Imagine a world where you can combine all of the VBScript that you’ve linked together with your batch scripts.  PowerShell is that world.  PowerShell is packed full of scripting abilities that make things like sending e-mails no longer require anything external (except a mail server of course).

Where do I get PowerShell?  Chances are, you already have it.  If you are running Windows 7 or above, it is included by default.  If you are running Window Server 2008 R2 or above, it is included by default.  If for some reason you still don’t have it, go get it here:

https://www.microsoft.com/en-us/download/details.aspx?id=40855&WT.mc_id=rss_alldownloads_all

The building blocks of PowerShell are called cmdlets.  Cmdlets can do things as simple as a directory listing or file copy and as complex as parsing logs and sending e-mails when errors occur.  So let’s start by taking a look at a very simple PowerShell script:

#############################################################
#Created By:	Brian Marshall
#Created Date:	11/8/2015
#Purpose:		Sample PowerShell Script for HyperionEPM.com
#############################################################

dir

So that’s pretty simple.  It looks very similar to a normal batch file.  This just has comments at the beginning to let us know what the script is followed by a single command: dir.  Before we dig a little deeper, let’s just try to execute this script:

Blog1Script1Capture1

Ok..maybe not simple?  If you are running this on Windows Server 2008 R2 (or above), you will get the above.  By default, it does not allow you run execute your own PowerShell scripting files.  So let’s change that setting.  Open a PowerShell window as an Administrator and execute this command:

Set-ExecutionPolicy RemoteSigned

Answer yes (Y) and we should be ready to try our code again:

Blog1Script1Capture2

And the script results:

Blog1Script1Capture3

Alright…now we’re headed in the right direction.  So what just happened?  It looks just like a batch script, right?  Looks can be deceiving.  What actually happened then?  In PowerShell, the dir command is an alias for another command.  Just like members can have aliases in the Hyperion world, cmdlets can have aliases in the PowerShell world.  So what does dir actually do then?  It executes the Get-ChildItem cmdlet.  This cmdlet was intended to replicate the dir command functionality and adds a little more to it.  If you are really interested in the Get-ChildItem cmdlet, check out TechNet for more information here:

https://technet.microsoft.com/en-us/library/ee176841.aspx

Now let’s try something we actually care about…MaxL!  Let’s start with something simple, like starting MaxL from PowerShell:

###############################################################################
#Created By:	Brian Marshall
#Created Date:	11/8/2015
#Purpose:		Sample PowerShell Script for HyperionEPM.com
###############################################################################

###############################################################################
#Variable Assignment
###############################################################################

$MaxLPath = "C:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\bin"

###############################################################################
#MaxL Execution
###############################################################################

& $MaxLPath\StartMaxL.bat

Looking at the script, we see that we first assign a variable.  The syntax for this is simple (and similar to MaxL).  We prefix the variable name with a dollar sign and set it equal to a value.  Once the path to MaxL has been set to our variable, we can then execute MaxL.  Notice that we use an ampersand followed by the variable for the path and then the rest of the path to the executable is just plain text on the line.  So what happens?

Blog1Script1Capture4

As expected…MaxL starts.  Now let’s do something slightly more complex (but still pretty simple) and actually execute a MaxL script:

###############################################################################
#Created By:	Brian Marshall
#Created Date:	11/8/2015
#Purpose:		Sample PowerShell Script for HyperionEPM.com
###############################################################################

###############################################################################
#Variable Assignment
###############################################################################

$MaxLPath = "C:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\bin"
$MaxLUsername = "admin"
$MaxLPassword = "password"
$MaxLServer = "hyperiones"

###############################################################################
#MaxL Execution
###############################################################################

& $MaxLPath\StartMaxL.bat Blog1MaxL1.msh $MaxLUsername $MaxLPassword $MaxLServer

We’ve established a few more variables and chosen a MaxL file to execute.  Let’s look athe MaxL while we’re at it:

login $1 identified by $2 on $3;

logout;

Ok…the world’s simplest MaxL script.  And the results:

Blog1Script1Capture5

And there we have it!  We’ve successfully integrated MaxL and PowerShell.  Up next…we’ll start looking into combining Essbase and Planning…on different servers!  While you wait, you can check out more PowerShell information here:

https://technet.microsoft.com/en-us/library/dd772285.aspx

The Planning Repository: HSP_MEMBER (Part 6 – Putting It All Together)
New Year, New Blog Posts

Comments

  1. Mike Fredericks
    August 10, 2018 - 8:58 am

    Brian,

    Great article. I had a question. Have you tried incorporating a Powershell command into Workspace? What I am working on and having trouble is, in Workspace I execute Maxl statements that load Essbase data for example. I wanted to be able to send an email after the load is completed.

    What I have done is use Task Scheduler to execute the batch files that call Maxl to load data and then when that task is complete execute a Powershell command to send the email that it is complete. I’d prefer to just have this all in one via Workspace, but struggling.

    Any suggestions?

Leave a Reply

Your email address will not be published / Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.