Planning Analytics with Watson

Sending HTML email from the Cloud

By Errol Brandt posted 26 days ago

  

Sending HTML Email from the Cloud

When building business applications, it's easy to overlook the importance of sending email notifications to users. We spend a lot of time perfecting the business logic and even more time making the user interface looking right, but we can forget that users sometimes just need a high level summary or a gentle nudge. This is where email notifications can be highly impactful.

Forecast Confirmation

We found a good example of this in monthly forecast process. Users would sometimes claim that the forecast they submitted was somehow changed after they locked it. This was almost impossible because the submission values were snapshotted as a part of the locking process, and the values in the cube were stored in a measure that was disconnected from the rules. A more likely explanation was that the users did not pay attention to all of the numbers and simply submitted an error without realising.

Our solution was to implement a simple confirmation email, showing exactly what the user had submitted. When a user complained that the number had changed, we simply go back to the confirmation email and tick off the numbers.   

Forecast Notification


By implementing this simple confirmation email, we found that the submission errors decreased significantly. They no longer believe that numbers were being changed for them - they have what they submitted in writing!

Sending HTML emails

There's no doubt that IBM has put a lot of effort into designing beautiful PAW visualizations. Even our most hardcore users - those who insist on working with cubes in Excel - have to admit that it is now easier to work in PAW.  Better still, the visualations are also management-ready, which means that users are now interacting with PAW in real-time during meetings. This saves time extracting data for presentation into PowerPoint!

With this in mind, it seems incongruous that TM1 cannot natively send HTML email. This is quite a limitation because, without HTML, it's almost impossible to send nicely formatted tables and use fonts/colour to draw attention to specific items. Wouldn't it be nice if this were added in the future so we could complete the user experience? Anyway, to get around this on our on-premise solution, we used a simple tool called blat. Our TI processes would build temporary HTML files and blat would run at the end to distribute the email. 


Unfortunately, when we moved to the cloud, blat was not going to work because because of security requirements (this was another one of things I wish I’d known about earlier). We looked around for alternatives and ultimately decided that a powershell script was the best option. Although I'm sure there are thousands of models doing this, I couldn't find a single working example for TM1, so I had to build my own - even though I'd never written a powershell script before.

Below is a copy of the sys.send-email proces that I created to replace blat.  It is used to send hundreds of emails a day, such as the one above.  You are welcome to try it in your models, but will require a bit of tweaking, which will explain below.  

sys.send-email

Sending HTML emails can be achieved by creating the TM1 Turbo Integrator process.

In our models, this process is called sys.send-email and is called at the epilog section of numerous TI processes.


#****Begin: Generated Statements***
#****End: Generated Statements****
#==============================================================================
# PowerShell Email Uility
#
# This process sends a formatted HTML email using a powershell script
#
# Parameters:
#
# pSubject Subject line for the email (mandatory)
# pSendTo Email address of recipient (mandatory)
# pBodyHTML path to a formatted HTML email file (mandatory)
# pAttachFile path to an attachment file (optional)
# pKeepFiles 0=Do not keep files, 1=Keep files (optional)
#
#==============================================================================

#==============================================================================
# Variables
#==============================================================================

sYear = TIMST(NOW(), '\Y',1);
sMonth = TIMST(NOW(), '\m',1);
sDay = TIMST(NOW(), '\d',1);
sHour = TIMST(NOW(), '\h',1);
sMin = TIMST(NOW(), '\i',1);
sSec = TIMST(NOW(), '\s',1);

#==============================================================================
# Settings from Control Cube
#==============================================================================

sScriptDirectory = CellGetS ('SYS control', 'Script Directory', 'Value');
sFrom = CellGetS ('SYS control', 'Send Email From', 'Value');
sSendMail = CellGetS ('SYS control', 'Send User Notifications', 'Value');
sSMTPServer = CellGetS ('SYS control', 'SMTP Server', 'Value');
sSMTPPort = CellGetS ('SYS control', 'SMTP Port', 'Value');

sCopyTo = 'administrator@email.com';

sScript = sScriptDirectory | sYear | sMonth | sDay | sHour | sMin | sSec | '.ps1';

#==============================================================================
# Other settings
#==============================================================================

DatasourceASCIIQuoteCharacter = '';

#==============================================================================
# Check the override flag
# Email notifications should be suspended during maintenance
#==============================================================================

IF ( sSendMail @='Yes' );
sTo = pSendTo;
sCC = sCopyTo;
ELSE;
sTo = sFrom;
sCC = '';
ENDIF;

#==============================================================================
# Check there is a subject
# Email subject is mandatory
#==============================================================================

IF (pSubject @='');
sLogEntry = 'The email subject was not was not specified';
LogOutput('WARN', sLogEntry);
ProcessBreak;

ELSE;
sSubject = pSubject;
ENDIF;

#==============================================================================
# Check there is a HTML file
# HTML file is mandatory
#==============================================================================

IF (pBodyHTML @='');
sLogEntry = 'The HTML file ' | pBodyHTML | ' was not specified';
LogOutput('WARN', sLogEntry);
ProcessBreak;

ELSEIF (FileExists(pBodyHTML)=0);

sLogEntry = 'The HTML file ' | pBodyHTML | ' was not found';
LogOutput('WARN', sLogEntry);
ProcessBreak;

ELSE;
sBodyFile = pBodyHTML;
ENDIF;

#==============================================================================
# Check if there is an attachment
# Not mandatory but file must exist if specified
#==============================================================================

IF (pAttachFile @='');
sAttachFile = '';
ELSEIF (FileExists(pAttachFile)=0);

sLogEntry = 'The file attachment ' | pAttachFile | ' was not found';
LogOutput('WARN', sLogEntry);
ProcessBreak;

ELSE;
sAttachFile = pAttachFile;
ENDIF;

#==============================================================================
# Build Powershell Scripts
#==============================================================================

Asciioutput(sScript, '# Powershell mail script');
Asciioutput(sScript, '');
Asciioutput(sScript, '[System.Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12');
Asciioutput(sScript, '');
Asciioutput(sScript, '$body = Get-Content ' | pBodyHTML | ' -Raw ');
Asciioutput(sScript, '');
Asciioutput(sScript, '# Define the Send-MailMessage parameters');
Asciioutput(sScript, '');
Asciioutput(sScript, '$mailParams = @{');
Asciioutput(sScript, ' SmtpServer = ' | CHAR(39) | sSMTPServer | CHAR(39));
Asciioutput(sScript, ' Port = ' | CHAR(39) | sSMTPPort | CHAR(39));
Asciioutput(sScript, ' UseSSL = $true');

IF (sAttachFile @<>'');
Asciioutput(sScript, ' Attachment = ' | CHAR(39) | sAttachFile| CHAR(39));
ENDIF;

Asciioutput(sScript, ' Subject = '| CHAR(39) | sSubject | CHAR(39));
Asciioutput(sScript, ' From = '| CHAR(39) | sFrom | CHAR(39));
Asciioutput(sScript, ' To = '| CHAR(39) | sTo | CHAR(39));
Asciioutput(sScript, ' CC = '| CHAR(39) | sCC | CHAR(39));
Asciioutput(sScript, ' DeliveryNotificationOption = '| CHAR(39) | 'OnFailure'| CHAR(39));
Asciioutput(sScript, ' Body = $body');
Asciioutput(sScript, ' BodyAsHtml = $true');
Asciioutput(sScript, '}');
Asciioutput(sScript, '');
Asciioutput(sScript, 'Send-MailMessage @mailParams');
Asciioutput(sScript, '#');

#Section Epilog
#****Begin: Generated Statements***
#****End: Generated Statements****

#==============================================================================
# Execute Powershell script through a command statement
#==============================================================================

sCommandScript = 'powershell -ExecutionPolicy Bypass -File ' | sScript;

ExecuteCommand( sCommandScript , 1 );
LogOutput('info', sCommandScript );

#==============================================================================
# Delete the script
#==============================================================================

sCommandBody = 'CMD /C DEL ' | sCommandScript;
ExecuteCommand(sCommandBody,1);

#==============================================================================
# Delete the body file
#==============================================================================

IF (pKeepFiles = 0);
sCommandBody = 'CMD /C DEL ' | sBodyFile;
ExecuteCommand(sCommandBody,1);
ENDIF;

#==============================================================================
# Sleep to avoid conflict
#==============================================================================

SLEEP (1000) ;

As you can see, the process has five parameters:

  • pSubject Subject line of the email (mandatory).
  • pSendTo Email address of the recipients (mandatory).
  • pBodyHTML Path to a formatted HTML file (mandatory).
  • pAttachFile Path to an attachment.
  • pKeepFiles Flag to indicate whether the files should be deleted at the end.

In addition to the parameters, we store the configuration details inside our SYS controlcube.

  • Script Directory This references the subdirectory where we store scripts (e.g. s:\prod\TM1\Scripts\) .
  • Send Email From This contains the email address (e.g. XXXX@mail.planning-analytics.ibmcloud.com).
  • SendMail This is a system-wide email kill switch. We use this to stop notifications going out while we are doing maintenance.
  • SMTP Server This is the SMTP server (e.g. mail.planning-analytics.ibmcloud.com).
  • SMTP Port This is the SMTP port (e.g. 587).

Once the TI process has been created and tested, it is then a matter of modifying other processes to build a HTML file in the scripts directory, and then passing the required parameters in the epilog section. 

ExecuteProcess(sTI_SendMail, 'pSubject', sSubject, 'pSendTo', sEmail, 'pBodyHTML', sFileHTML, 'pAttachFile', sFileCSV, 'pKeepFiles', 0  );
You will notice that in the process, all emails are being copied to a local email account (specified as sCopyTo). In our system, this address points to a local shared mailbox, which all Planning Analytics can monitor.

If you're working on the cloud, I would also suggest reading IBM support document 1115787 (  https://www.ibm.com/support/pages/planning-analytics-cloud-smtp-send-email-process-using-powershell-script ) for more details about configuring cloud email.

Conclusion

A simple notification email can significantly improve the user experience, especially as a way of alerting users to important information, or confirming the details of a submission.

Unfortunately TM1 does not provide a native way to send well-formatted, visually appealing HTML emails.  This makes it difficult to use objects such tables, fonts and colours, to draw user attention.

To overcome this, I developed a TM1 process that generates a powershell script to send a HTML file through email. I don't claim to be particually knowledgeable out powershell, so I am sure there are others could enhance this even further. 

Nonethless, here it is for the benefit of others. Please let me know if you found this useful, or have improvement suggestions.

​​
0 comments
23 views

Permalink