Using Powershell in Planning Analytics

Written by Chris Sands

Rather than have external batch files, you can get your IBM Planning Analytics application to build and run its own Powershell commands. Powershell is Microsoft’s modern powerful scripting and cross-platform solution that runs on Windows, Linux, and macOS.  So that I can keep everything in one place I like to build my Powershell command file, run it and then delete it from within a TM1 process. This means that I reduce the risk of running the wrong batch file, or one that isn’t not there at all. In this blog, I’ll talk you through how I use Powershell.

I create where I want to put the temporary files that I will be using in the process. If you are very organised, you will have a cube in your model that will hold these standard places where you put things such as temporary processing files and you could do a DB lookup to get the value. I’m not, so I’ve hard-coded this in.

# Create the path for the Output files (the Powershell and Text file)

vDevice           = ‘S:’;

vFilePath         = ‘\MyDataBase\TemporaryFiles’;

# Create a unique file name i.e., Process name + date & time in yymmddhhmmss format

vFileProcessID    =     GetProcessName();

vFileUniqueID     =     TIMST (Now(), ‘\y\m\d\h\i\s’);

You will then create the full file name for your Powershell file from your components and add the .ps1 file type to tell the operating system to use Powershell to run it.

# Create the Powershell Script filename

vFileType.       = ‘.ps1’;

                         vfile=vDevice|

                         vFilePath|

                         vFileProcessID|

                        ‘-‘|

                        vFileUniqueID|

                        vFileType;

Then you will create the commands that you want to automate. In an email generation command, I will set up my connection details to the email service and then use the ‘Send-MailMessage’ utility and fill out all the switches that I want to use, such as the SMTP server, who the email is from, who is it to, the Subject, the priority and so on.

I then write the Powershell command lines using the echo command I want to the ‘.ps1’ file.

# Create the 3-line PowerShell script in a uniquely identified file using multiple ‘echo’ commands

vCommandLine =  ‘cmd /c “echo ‘ | vConnectionManagement | ‘ >> ‘ | vfile |’ & ‘|

                ‘echo ‘ |vSendMailCMD | ‘ >> ‘ | vfile|

                ‘ & ‘ |

                ‘echo ‘ |’exit’| ‘ >> ‘ | vfile |  ‘”‘;

ExecuteCommand ( vCommandLine, 1 ) ;

Now we have the file built we can execute it using the Powershell command.

# Run the Powershell Script

vCommandLine =  ‘cmd /c ‘ | ‘Powershell ‘    |vfile; 

ExecuteCommand (vCommandLine,1) ;

After running the command, we then tidy up by deleting the file that we’ve created in the process.

# Delete the Powershell Script

vCommandLine =  ‘cmd /c  del /Q ‘ | vfile;

ExecuteCommand (vCommandLine,1);

One tip that I find useful when I am developing these processes is to output the text that I am building to a text file so that you can see where I must make a tweak to my command line code for it to behave exactly how I want it to. For me, it’s often that I’ve missed an apostrophe or got the wrong type of quote. So, I add a parameter that defaults to ‘No’ but which if I change it to ‘Yes’ will allow me to check the command lines I have generated as they won’t be deleted.

if (pIsThisATest @=’Yes’);

ASCIIOUTPUT (vASCIIOUTPUTfile , vCommandLine);

ENDIF;