This post explains how to configure Sisense to send email alerts after an Elasticube build.
This plugin enables the user to define SQL code that runs after a build has finished. If this SQL query returns any results, they are emailed out to a list of recipients.
Important - build will not end until the post plugin has finished. Executing heavy queries may take a considerable amount of time and thus will result with a longer ElastiCube build process.
Getting the necessary files - Download the attached files and unzip them into C:\Program Files\Sisense\Prism\Server\Plugins. If this folder does not exist, just create it.
Ensuring the DLLs can run
Right Click each of the DLL files, and choose Properties.
In the General pane, if a button with a Unblock caption exists, click on it (now Windows allows executing the DLL). See image below:
- Restart the ElastiCubeManagementService from the Services window. Sisense needs to be restarted, in order to pick up the new dll files.
Create a SQL Query to run after each build - This query should return any data points that exceed the desired threshold. See below for a sample query that runs against the include [Sample Lead Generation] Elasticube. It returns only the countries that have more than 700k visits. For this example, this text was saved as C:\SisenseScript\AlertSQL.text
SELECT [Country], sum([# Visits]) as [Number of Visits] FROM [Lead Generation] GROUP BY [Country] HAVING sum([# Visits]) > 700000
4. Executing the plugin
- Once in ElastiCube Manager, open ElastiCube ==> Pre & Post Execution Plugins.
- Add to the post plugin section a new plugin called Alert.
- Click on Add to configure how the plugin will behave:
<Attributes FromEmailAddress="FromEmail@gmail.com" FromEmailPassword="FromEmailPasswd" ToEmailAddress="firstname.lastname@example.org,email@example.com" EmailSubject="Sisense Notification" sqlFilePath="C:\\SisenseScripts\\AlertSQL.txt" restApiKey="MyRestApiKey" server="http://ServerAddress:PortNumber" sisenseEmail="SisenseAdminEmail" sisensePasswd="SisenseAdminPasswd"> </Attributes>
- These are the attributes we can add:
FromEmailAddress - the Admin's email address, used to send emails. This needs to be a gmail account.
FromEmailPasswrd - password for the Admin's email address, used to send emails
ToEmailAddress - comma separated list of emails addresses that should be notified if the alert goes off.
EmailSubject - the subject for the email being sent
sqlFilePath- the path to where the SQL Query is stored. This is a file that defines the query to run. When this query gets run any results will be sent off in an email, so this query should be looking for records in the Elasticube that exceed a certain threshold. Make sure to escape backslashed in this attribute. So instead of writing C:\test, it would need to be C:\\test.
restApiKey - the key for Sisense's REST API, which can be found in your Sisense Server's REST API Management web page.
server -the protocol (http://), hostname (or IP address), & port number of the Sisense web server. For example, http://mySisenseServer.com:8081
sisenseEmail - the login email of an Admin user in Sisense, used for API calls
sisensePasswd - the password for the Admin user in Sisense, used for API calls
5. Run build. In the build's finalizing step the plugin execution will appear in ElastiCube manager log window. If there any results from the designated query, they will be sent over email to the distribution list.