How to query Decision Insight data and send a status email
Context
You want to send an email every hour describing the Account statuses.
Create the query
In data integration menu, open the Queries tab and create a new query:
- Name: getAccountStatuses
- Dimensions: Account
- Inputs: customerName, inProcessCount, inProcessCountStatus
Save this new query:

Query parameters
The Parameters panel references the existing parameters used for the query. The ReferenceTime parameter will always be present. It is used to indicate the reference time for query execution.
Create a route that sends the result by email once an hour
Enable use of camel QUARTZ component for hourly query
See Library in Camel QUARTZ component documentation
Enable use of Camel MAIL component
See Library in Camel MAIL component documentation.
To format the result of the query in a mail we will use the templating engine called stringtemplate. The template will be saved as a new Resource.
Create a new resource that will build the mail message from the results of the query:
- Name:
mail_content
- Type:
Text
- Charset:
UTF-8
Content:
<strong>Last accounts status</strong>
<br>
<table border="1">
<tr>
<th border="1" align="left"> Customer </th>
<th border="1" align="left"> Payments in process </th>
<th border="1" align="left"> Status </th>
</tr>
$body.rows : {row |
<tr>
<td border="1"> $row.customerName.value$</td>
<td border="1" align="center" padding="5">$row.inProcessCount.value$</td>
<td border="1" align="right">$
if(row.inProcessCountStatus.valueByIndicatorDataName)$ $row.inProcessCountStatus.valueByIndicatorDataName.Label$ $
else$ null $
endif$</td>
</tr>
}$</table>
Route configuration
Create a routing context to send emails. The route must contain a number of the properties used to define the receiver, the sender, the server URL and the password of the email account.
Create properties for your route
On the left menu, click Properties and define the following properties:
- emailReceiver – the email of the person who will receive the email
- mailServerHost – the host name of the mail server
- emailSender – the login used to send the mail.
- emailPassword – the password of the account used to send the mail.

Create the route
In the data integration menu, open the Routes tab and create a new route:
- Name: sendAccountStatuses
Definition:
<?xml version="1.0" encoding="UTF-8"?>
<routes xmlns="http://camel.apache.org/schema/spring" xmlns:u="http://www.systar.com/aluminium/camel-util">
<route>
<from uri="quartz://loop?cron=0+1+*+*+*+?"/> <!-- the route is executed every hour -->
<!-- Build the parameter map for the query -->
<setProperty propertyName="tnd-query.parameters">
<u:map-create>
<!-- "ReferenceTime" entry is mandatory -->
<u:map-entry key="ReferenceTime">
<u:date-add date="NOW" scalar="-1" type="minutes"/> <!-- remove one minute to reproduce product lag and avoid null results -->
</u:map-entry>
</u:map-create>
</setProperty>
<to uri="tnd-query:getAccountStatuses"/>
<to uri="string-template:tnd-resource/mail_content?delimiterStart=$&delimiterStop=$"/>
<setHeader headerName="subject">
<constant>Accounts status</constant>
</setHeader>
<setHeader headerName="to">
<constant>{{emailReceiver}}</constant>
</setHeader>
<!--Send the email -->
<to uri="smtps://{{mailServerHost}}?contentType=text/html&username={{emailSender}}&password={{emailPassword}}"/>
</route>
</routes>
Save and then launch the route. Every hour on the hour, an email containing the result is sent:

To see this example in action, you can use this application and properties containing the example.