Thursday, January 19, 2017

Last Saved By in PBCS and Hyperion Planning On Premise

I got the idea about this post during my long run, what is really nice about running long distances is the pain you feel while running, the joy, and euphoria when you are done, you also have a lot of time to think about anything to distract yourself from thinking about the 15 Kms remaining...

Who changed the numbers? :@

We always hear that question right?, I want to show how to do it in PBCS (now a standard functionality) and  Planning On Premise (workaround).


This is how you get it in PBCS:

In Calculation Manager create a variable of type "StringAsNumber", select "Use System Defaults" and you'll get the User Name as default value,

You include it in your script just like any other variable, only you can now assign it to a member of type "Text" with getting the famous error "Expected number found string,,,"

Create a sample form, include the business and make it run on save. So my form as shown below has nothing in it

And after I entered some data and saved the form.

That's it, pretty simple and convenient, gotta love PBCS right?

Hyperion Planning On Premise:

Before I start, whatever I'm doing here is just for fun, you don't have to take it seriously. For demonstration purposes I hard coded my form name because I was lazy.

So now I want to show how to do the same in On Premise Planning plus one more tweak, I will add the Last Saved time in a readable format.

In a nutshell, I'm going to do the following :

1. Enable application auditing
2. On save update a custom table with the latest user who saved the form and the time it was saved
3. Run a Groovy script to load the text data using OutlineLoad planning utility

Pretty simple logic, I did struggle in using one of the CDFs thanks to Calculation Manager's excellent, heavenly like documentation!!!

This is my data form before I do anything.

And my business rule:

So what is happening up there???

I'm using the CDF @CalcMgrDBUpdate to update a custom table with the last user who saved the form/and time from Planning table HSP_AUDIT_RECORDS, the update query is no where near perfection but it works fine for my demonstration purposes, I'm getting the last user/time using Oracle timestamps.

After @CalcMgrDBUpdate is executed I'll get the following in the table.

The columns "Last Saved By" and "Last Saved" are my two Account text member I'm going to load.

Finally I'll run my Groovy script to simply load the text data into Hyperion Planning using OutlineLoad utility and that's it.

So back to the form, I'm logging with the user Guardiola and I'm going to delete the figures in January.

And after saving the form, I got the user and the time the form was saved. (Nice huh? )

Now I'll login with MontyPython user, and change the figures in February.

And after saving the form.....

And my log just in case...

Voila! that's it for today, and yes it is working perfectly!

Hope you had fun reading this.

May the Cosmos Be With You!

Sunday, January 15, 2017

PBCS, EPM Automate, and Nightly process automation

This post I'll give a simple example of automating some of your PBCS tasks as an administrator, I'm going to show how to automate a nightly process to manage your PBCS application artifact. I'll give a very simple example that can be expanded to suit whatever requirement you might have.

Before we begin, I assume the reader has some basic knowledge in Windows Batch Scripting.

First thing I need to create an application snapshot:

As I said before, here you can have a number of snapshots (Planning, CalcMgr...) but in this post I will use only one snapshot for demonstration.

Now here is my folder structure, I like to keep things neat and clear.

Its self explanatory:
1. Main folder will have the call me first script to define the variables etc,
2. Script folder you place whatever scripts you need to schedule for nightly runs
3. Log folder will keep the process logs
4. Backup folder will keep the latest exported/downloaded snapshots
5. Archive folder will archive the old snapshots

So Let's Get Read to Rumble!

Before I being with the files, I want to show a snapshot of the backup and archive folders.

Backup folder...

Archive folder...

Archive folder is empty, I'm keeping only five snapshots at a time in my backup folder, and then I'll move the older files to the archive folder. (will show this bit in my code in a bit).

Now my call me first script.

First I'm setting the required variables, I'm also specifying the Application Snapshot (TEST in this example) I want to process, also specifying the time stamp so I can tag my downloaded files and later archive the old ones.

So now my nightly process script.

Its pretty simple, the script is broken to:

1. Define variables
2. Login process
3. Exporting snapshot
4. Downloading snapshot
5. Logout
6. Moving the file to my backup folder
7. Archiving process

All you need to do now is execute/schedule a job to run this on daily basis (I'm not going to cover this part since its pretty standard), I'll just execute the script and check the logs.

As you can see everything is good. now my backup folder will have a new file.

Notice the time stamp, I'm keeping only the most recent, and here is my archive folder.

Run the script again, and my backup files...

Archive files...

This is done with this one liner (basically checking most recent five zip files and moving anything else to the archive folder).

And in case you're wondering about this bit.

%ERRORLEVEL% is standard windows error code returned when executing any commands, and for PBCS any exit code not equal to 0 means there is a problem (check the exit codes here ), so if I'm encountering any issues I'm simply existing the process and reporting the error. Like the following error is returned when using invalid credentials.

Voila, that's it for today.

May the Cosmos Be With You!