PowerShell and Hyperion: The Outline Load Utility

I was reminded recently that I promised to continue with PowerShell in my last PowerShell blog post, but that I hadn’t done it yet.  I may have gotten a little caught up in my home lab series.  So, if you were waiting and waiting for this, my apologies.

Now that we have a little bit of PowerShell working knowledge and we have it working with MaxL, let’s get serious about some of our other batch commands.  Today we’ll focus on the outline load utility for Planning.  If you’ve used this utility in your batch commands before, you can probably skip the first part of this blog post.  If not, let’s first talk about what the outline load utility is.  It may be the worst named utility that I’ve ever used.  The outline load utility doesn’t actually load an outline at all.  And to make matters worse…it’s not even just a meta-data tool.

The outline load utility allows us to load meta-data, numeric data, text-based data, build smart lists, and generally manage the back-end of Hyperion Planning from a batch script (or PowerShell).  Today we’ll focus on getting the utility working in PowerShell.  So where is this mythical utility?  You should be able to find it here:

<epm install drive>:\Oracle\Middleware\user_projects\epmsystem1\Planning\planning1

You will actually find quite a few utilities in this directory.  There are utilities for imports, exports, running business rules.  This directory is why I will never wake up one day and say “I wish I had something to blog about.”  I’ll literally never run out of content with all of these hidden gems out there.  Here’s a sample:

OutlineLoad1

Next we need to decide what we want to do with the utility and give it a shot.  There are so many things that we can do with it, I’m going to stick with something really simple and use the 11.1.2.3.500 Sample Vision app for the demonstration.  This way you should all be able to reproduce what we are doing.

So let’s first log into our Vision sample app and take an export of the Product dimension.  Once inside our app, click on Administration then Import and Export and then Export Metadata to File.OutlineLoad2

Select our Product dimension from the list and click Run and we should have a great file to start playing with.  When prompted, just save the file away and we’ll come back for it later.

Now let’s get back to our Planning server and start up PowerShell.  Once we have our window open, let’s just change our directory and run the CMD file and see what happens:

OutlineLoad3

You should get something totally useful like this:

OutlineLoad4

Before we go any further, we need to go ahead and generate a password file for the load utility.  This will allow us to execute our PowerShell script without needing to enter a password each time.  To do this we just type in PasswordEncryption.cmd PasswordFile.pass.  It should prompt you for a password.  This will be the password to the username that we will eventually tell the load utility to use.  Enter your password, press enter, and the file we need should be produced.

OutlineLoad5

Now we are ready to load the file that we exported earlier from Workspace.  I placed my exported file directly into the planning1 folder with the utility for my first execution just to make things simple.  So first let’s look at the command, then we’ll pick it apart:

.\OutlineLoad.cmd -f:PasswordFile.pass /A:Vision /U:admin /I:admin_ExportedMetadata_Product.csv /D:Product /L:Product.log /X:Product.exc

ParameterDescriptionValue
-F:Specifies the password file that we just generated so that we don't need to enter a password.PasswordFile.pass
/ASpecifies the application that we are connecting to.Vision
/USpecifies the username that we are connecting with.admin
/ISpecifies the file that we will be importing.admin_ExportedMetadata_Product.csv
/DSpecifies the dimension that we will be loading.Product
/LSpecifies the file to log our results to.Product.log
/XSpecifies the file to write exceptions to.Product.exc

So what happens?

OutlineLoad6

Success!  And how about our log and exception files?

OutlineLoad7

Great…they are there.  Now let’s make this something worthy of PowerShell.  To do that, we’ll make everything into a variable.  I’ve created a folder on my Planning server to store my files and referenced that file in the following PowerShell script:

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

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

$PlanningPath = "C:\Oracle\Middleware\user_projects\epmsystem1\Planning\planning1"
$PlanningUsername = "admin"
$PlanningPasswordFile = "PasswordFile.pass"
$PlanningApp = "Vision"
$PlanningDim = "Product"
$PlanningDimFile = "admin_ExportedMetadata_Product.csv"

$PlanningWorkingFolder = "C:\Data\HyperionEPM\PowerShell"

###############################################################################
#Outline Load Utility Execution
###############################################################################

& $PlanningPath\OutlineLoad.cmd -f:$PlanningPath\$PlanningPasswordFile /A:$PlanningApp /U:$PlanningUsername /I:$PlanningWorkingFolder\$PlanningDimFile /D:$PlanningDim /L:$PlanningWorkingFolder\$PlanningDim.log /X:$PlanningWorkingFolder\$PlanningDim.exc

Along with the file above, I’ve placed the Product file that we exported from Workspace.  I’ve set the PlanningWorkingFolder variable to reflect the name of my folder and I should be ready to execute my new Product PowerShell script.  But wait…I don’t want to schedule this process to run on the Planning server and another process to run on the Essbase server.  I need them to run from the same place.  We’ll cover that exact need in our next post…stay tuned!

The EPM Week In Review: Week Ending February 27, 2016
PBCS March 2016 Update: FR Web Studio Is Almost Here

Comments

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.