The Friday Night Project #12 – Embedded Processing Part 1

UDA
The UDA channel is for Teradata’s Unified Data Architecture including the Analytical Ecosystem and other UDA influences. This channel provides information specific to the integration and co-existence of multiple systems, in particular when a mix of Aster, Teradata, and Hadoop are present. It is also meant to support information around the UDA enabling technologies so products like Viewpoint, Data Mover, Connectors, QueryGrid, etc.
Teradata Employee

The Friday Night Project #12 – Embedded Processing Part 1

Last time we introduced the Macro and the Stored Procedure as a means to provide for Isolation between the SQL call and the underlying database structure.

This week we are going to keep on the core Teradata trail by looking into Stored Procedures as means to provide for Embedded Processing.

Stored Procedures

Teradata Stored Procedures provide two different types of capability. The first approach, looked at last week provides a means of isolating applications from the underlying database. The second approach extends upon this isolation concept to allow for analytically and mathematical processing to be conducted within the bounds of the Stored Procedure and therefore within the database itself. While neither of these approaches fully exploits the Parallel Nature of the Teradata Database it can improve the performance of Tactical Applications through bringing to bear the CPU power of the Teradata Database to filter or process Result Set information in order to evaluate some function (as opposed to transmitting a large Result Set from the database to the querying application, wasting Intranet Bandwidth and CPU power within the querying application).

A Stored Procedure definition allows us to control the application calling mechanism for a given request (name of the Stored Procedure, type and order of parameters). As part of this definition we control both the Input parameters and the Output parameters, giving us a true Interface layer, the Implementation of which we can provide through which ever mechanism we chose.

Creating an Embedded Analytic Stored Procedure

In order to explore the concept of Embedded Analytics we will investigate how we can conduct a part of the Quotation Engine calculation within the Database.

Start by returning to the TZA-Database project within the FridayNightProject by starting up Eclipse and selecting the appropriate workspace.

Workspace Launcher

In the Project Explorer View expand the TZA-Database Project and open up the src/sql directory as this is where we will augment the appropriate Create and Delete files for the Stored Procedure definitions.

To add the Embedded Analytic Stored Procedure definition to the CreateTZA_Procedures.sql file add the following code segment within the CreateTZA_ Procedures.sql file using copy and paste.

--/**
-- *
-- * Stored Procedure to Apply Risk Factors to a Quote based upon a ZipCode
-- *
-- */
CREATE PROCEDURE "ApplyRiskFactorsToQuoteSP"
(
IN ZipCode INTEGER,
IN inQuotation NUMERIC(18,2),
OUT outQuotation NUMERIC(18,2))
BEGIN
DECLARE FireRisk FLOAT;
DECLARE FloodRisk FLOAT;
DECLARE TheftRisk FLOAT;
DECLARE SubsidenceRisk FLOAT;
DECLARE OtherRisk FLOAT;
DECLARE workingQuote NUMERIC(18,2);
--
SELECT FireRisk, FloodRisk, TheftRisk, SubsidenceRisk, OtherRisk
INTO FireRisk, FloodRisk, TheftRisk, SubsidenceRisk, OtherRisk
FROM ZipCodeRiskFactors
WHERE ZipCode BETWEEN StartZipRange AND EndZipRange;
--
SET workingQuote = inQuotation;

SET workingQuote = workingQuote * FireRisk;
SET workingQuote = workingQuote * FloodRisk;
SET workingQuote = workingQuote * TheftRisk;
SET workingQuote = workingQuote * SubsidenceRisk;
SET workingQuote = workingQuote * OtherRisk;

SET outQuotation = CAST (workingQuote as NUMERIC(18,2));
END;
/
COMMIT;
/

This second Stored Procedure “ApplyRiskFactorsToQuoteSP” illustrates the idea of embedding business or analytical processing within the Database itself in order to capitalize on the CPU Power available within the Teradata Database and to run closer to the data, thus reducing network latency and bandwidth issues. In this example we pass in the ZipCode and the base Insurance Quotation (calculated by a modified version of the Simple Quotation Engine, which we will see later) and let the Stored Procedure use our normal Select statement to get the risk factors associated with the ZipCode provided as input. However, we then let the Stored Procedure logic carry out the quotation adjustment, within the Database environment, on the workingQuote variable, based upon the risk factor values returned from the Select statement. Finally we cast this workingQuote value back to the output parameter (outQuotation) as the result of our embedded business process.

Note: the “/” delimiter characters are required within the Stored Procedure code in place of having white space when this is run within an Ant SQL task. I don’t know why this is necessary. The linkage between Stored Procedure definitions should look like this, with no white space).

END;
/
COMMIT;
/
--/**
-- *

DropTZA_Procedures.sql

As before we add a line to the DropTZA_Procedures.sql file to hold the drop statement.

The DropTZA_Procedures.sql can then be built up by inserting the following code segment within the DropTZA_ Procedures.sql file using copy and paste.

DROP PROCEDURE ApplyRiskFactorsToQuoteSP;
COMMIT;

Run the Ant Build

Having added these new SQL statements we can return to the Ant View and expand the TZA-Database project. The Ant targets CreateTZA-Macros, CreateTZA-Procedures, DropTZA-Macros and DropTZA-Procedures can now be seen in the Ant view and these can be exercised individually if you require.

Ant Build Targets

However, we configured the dependencies within DropTZA_DB and InitialiseTZA_DB so that a single Ant target (InitialiseTZA_DB) could be used to rebuild TZA_DB in a repeatable manner so make sure and Double Click on this at some point before proceeding.

  • Finally in the Ant View Double Click on the InitialiseTZA_DB task to clear any existing data and rebuild your database.

Note if you haven’t run the CreateTZA-Macros or CreateTZA-Procedures targets then when the InitialiseTZA_DB target is first run the DropTZA-Macro and DropTZA-Procedure targets may fail (as nothing has been created for them to Drop), however, the onerror="" flag in the SQL Task will let this pass. A subsequent run of InitialiseTZA_DB will run as follows:

Ant Build Run

Testing the Embedded Analytics Stored Procedure

Assuming everything is configured correctly we should now be able to exercise this new Database element. To do this we will use the Data Source Explorer (DSE) provided by the Eclipse DTP (Data Tools Project) in combination with the Teradata Plug-in for Eclipse.

If you have not already done so open up the Data Source Explorer View

  • Window -> Show View -> Other..
  • Connectivity -> Data Source Explorer
  • Select OK

Show View - Data Source Explorer

Open Connection AS TZA_USER

Within the Data Source Explorer view select the Local-12-AS-TZA_USER Connection (as we need to run the Stored Procedure from the same User Name as it was created with, or issue appropriate Grants).

  • Right Click on Local-12-AS-TZA_UUSER
  • Select Connect

Connect AS TZA_USER

In the Data Source Explorer view expand the TZA_DB item, then the Schemas item to see the TZA_DB database. Expand this to see its contained objects including Macros and Stored Procedures. Expand the Stored Procedures icon to see the GetZipCodeRiskFactorsSP Stored Procedure and the new ApplyRiskFactorsToQuoteSP Stored Procedure.

ApplyRiskFactorsToQuoteSP Stored Procedure

Exercise ApplyRiskFactorsToQuoteSP Stored Procedure

To exercise / test the ApplyRiskFactorsToQuoteSP Stored Procedure Right Click on it and select Run...

In the Configure Parameters dialog that opens click on the Value entry for the ZipCode Parameter and insert a valid Zip Code, say 92127. Click on Value entry for the inQuotation Parameter and insert a valid Quotation amount, say 100. Click on OK to run the Stored Procedure with these parameters.

Configure Parameters For Stored Procedure

In the SQL Results view you will see the parameter returned by the Stored Procedure based upon the Input Parameter values provided.

SQL Results For ApplyRiskFactorsToQuoteSP

You can experiment to see if the calculation is correct by changing say the Fire Risk value in the ZipCodeRiskFactors Table for a given ZipCode range. Double the RiskFactor from 1.0 to 2.0 and the outQuotation value should double to say 200. Half it from 1.0 to 0.5 and the outQuotation value should half to say 50.

Integrating Embedded Processing into the Business Process

Having created this Embedded Analytics Stored Procedure it is now time to show how to integrate this into the TZA-InsuranceProcess Project. This initially involves creating a Data Access Object to reflect the different approach to the Quotation Engine which is to use the ApplyRiskFactorsToQuoteSP Stored Procedure as a part of the Business Logic, but running within the Database. As before we will need a DAO Interface and Implementation to actually access the ApplyRiskFactorsToQuoteSP Stored Procedure, however, we will need to change the Business Service to accommodate and utilize this new DAO Interface and approach.

So we return to the TZA-InsuranceProcess project and drill into the src/java folder and the com.teradata.tza.insurance.process.quoteEngine.repository package. There we find the ZipCodeRiskFactorsDao Interface and its Implementations (JdbcZipCodeRiskFactorsDao, JdbcZipCodeRiskFactorsMao and JdbcZipCodeRiskFactorsSPao).

Transform Data Access Object Interface

Our final Data Access Object actually represents an Embedded Analytic access mechanism, which in this case is a relatively simple Transform function (based upon the ApplyRiskFactorsToQuoteSP Stored Procedure). This Transform function requires a different DAO Interface, commensurate with the Input and Output parameters of the Stored Procedure and an appropriate implementation of this interface (as per our Isolation architectural requirement).

Firstly add an Interface definition to support a Database embedded Adjust Quotation business process.

  • Within the TZA-InsuranceProcess Project Right Click on src/java folder
  • Select New -> Interface
  • Browse to find the Package: com.teradata.tza.insurance.process.quoteEngine.repository
  • Set the Name: ZipCodeRiskFactorsTransformDao

New Java Interface ZipCodeRiskFactorsTransformDao

  • Click Finish and a template file will open within the Eclipse editor.
  • Replace the template code with the following code segment using copy and paste.
/**
* The contents of this file are Teradata Public Content and have been released to the Public Domain.
* Please see the license.txt file in the top level directory for more information.
*
* @author Mike.Coutts@Teradata.com
*/
package com.teradata.tza.insurance.process.quoteEngine.repository;

import java.math.BigDecimal;

import com.teradata.commons.access.TaoSession;
import com.teradata.commons.access.exception.DataAccessException;

public interface ZipCodeRiskFactorsTransformDao
{
public void setTaoSession(TaoSession taoSession);
public void setStoredProcedureName(String storedProcedureName);

public BigDecimal getTransformedQuote(String zipCode, BigDecimal quote) throws DataAccessException;
}

Transform Access Object

Finally we add an implementation of the ZipCodeRiskFactorsTransformDao Interface that uses the ApplyRiskFactorsToQuoteSP Stored Procedure to apply the embedded algorithm. To create the JdbcZipCodeRiskFactorsTransformTao.java class:

  • Within the TZA-InsuranceProcess Project Right Click on src/java folder
  • Select New -> Class
  • Browse to find the Package: com.teradata.tza.insurance.process.quoteEngine.repository
  • Set the Name: JdbcZipCodeRiskFactorsTransformSPao

New Java Class JdbcZipCodeRiskFactorsTransformSPao

  • Click Finish and a template file will open within the Eclipse editor.
  • Replace the template code with the following code segment using copy and paste.
/**
* The contents of this file are Teradata Public Content and have been released to the Public Domain.
* Please see the license.txt file in the top level directory for more information.
*
* @author Mike.Coutts@Teradata.com
*/
package com.teradata.tza.insurance.process.quoteEngine.repository;

import java.sql.CallableStatement;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.math.BigDecimal;

import com.teradata.commons.access.TaoSession;
import com.teradata.commons.access.exception.DBException;
import com.teradata.commons.access.exception.DataAccessException;
import com.teradata.tza.insurance.process.quoteEngine.repository.ZipCodeRiskFactorsTransformDao;

public class JdbcZipCodeRiskFactorsTransformSPao implements ZipCodeRiskFactorsTransformDao
{
/** Logger for this class **/
private static Log log = LogFactory.getLog(JdbcZipCodeRiskFactorsSPao.class);

private TaoSession taoSession = null;
public void setTaoSession(TaoSession taoSession)
{
this.taoSession = taoSession;
}

private String storedProcedureName = null;
public void setStoredProcedureName(String storedProcedureName)
{
this.storedProcedureName = storedProcedureName;
}

/**
*
* @param zipCode
* @param quotation
* @return quotation
* @throws DataAccessException
*/
public BigDecimal getTransformedQuote(String zipCode, BigDecimal quotation) throws DataAccessException
{
BigDecimal workingQuote = quotation;

String sCall = "{CALL \"" + storedProcedureName + "\"(?,?,?)}";
CallableStatement cStmt = null;

// Make the Stored Procedure Call
try {
cStmt = taoSession.prepareCall(sCall);
cStmt.setInt(1, new Integer(zipCode).intValue());
cStmt.setBigDecimal(2, workingQuote);
cStmt.registerOutParameter(3, java.sql.Types.NUMERIC);

cStmt.executeQuery();

workingQuote = cStmt.getBigDecimal(3);
}
// Check for unrecoverable database errors
catch (SQLException ex)
{
// Translate SQLException to DBException
log.error(TaoSession.FindError + ":" + ex.getMessage());
DBException dbEx = new DBException(TaoSession.FindError, ex);
throw dbEx;
}
catch (DBException ex)
{
log.error(TaoSession.FindError + ":" + ex.getDbMsg());
throw ex;
}
finally
{
// Close cStatement to prevent leaks
try
{
if (cStmt != null)
cStmt.close();
}
catch (SQLException ex)
{
log.error("Error closing result set or statement" + ex);
DBException dbEx = new DBException ("Error closing result set or statement", ex);
throw dbEx;
}
}

return workingQuote;
}

} // end JdbcZipCodeRiskFactorsTransformSPao

This code follows our established DAO pattern and is similar to the previous JdbcZipCodeRiskFactorsSPao code in so much as we require to execute the setup and tear down associated with a Stored Procedure.

Firstly consider the setup process for the ApplyRiskFactorsToQuoteSP call:

            String sCall = "{CALL” + storedProcedureName + “\"(?,?,?)}";

cStmt = taoSession.prepareCall(sCall);
cStmt.setInt(1, new Integer(zipCode).intValue());
cStmt.setBigDecimal(2, workingQuote);
cStmt.registerOutParameter(3, java.sql.Types.NUMERIC);

Note how we define the Stored Procedure “CALL” with it’s “?” Parameters and prepare this call, through the taoSession, into the Call Statement (cStmt) before setting the input parameters (zipCode and workingQuote) and registering the output parameter. We let the actual Stored Procedure Name become a configuration parameter of the SP Access Object so we can easily swap over one ApplyRiskFactorsToQuote Stored Procedure Implementation for another (more of this next week Toto).

Now consider how we execute the ApplyRiskFactorsToQuoteSP Stored Procedure and obtain the results of the operation from the output parameter:

            cStmt.executeQuery();
workingQuote = cStmt.getBigDecimal(3);

Note how we specifically map from the Stored Procedure output parameters (maintained within the cStmt and indexed by the parameter number) into the workingQuote Business Object variable.

Implementing the Simple Quotation Engine (Quotation Transform)

Now we have a means available to access an Embedded Analytic function (such as ApplyRiskFactorsToQuoteSP) that can take as input a Quotation for a Property located within a given ZipCode and Transform that Quotation we will need a new Implementation of the Simple Quotation Engine that operates in a similar manner. Note we do not change the Simple Quotation Engine Interface just it’s Implementation (with appropriate re-wiring of the Spring Framework configuration files).

So we add an implementation of the QuotationEngine Interface that uses the ApplyRiskFactorsToQuoteSP Stored Procedure to Transform the quotation. To create the TransformQuotationEngine.java class:

  • Within the TZA-InsuranceProcess Project Right Click on src/java folder
  • Select New -> Class
  • Browse to find the Package: com.teradata.tza.insurance.process.quoteEngine.businessProcess
  • Set the Name: TransformQuotationEngine

New Java Class ZipCodeTransformQuotationEngine

  • Click Finish and a template file will open within the Eclipse editor.
  • Replace the template code with the following code segment using copy and paste.
/**
* The contents of this file are Teradata Public Content and have been released to the Public Domain.
* Please see the license.txt file in the top level directory for more information.
*
* @author Mike.Coutts@Teradata.com
*/
package com.teradata.tza.insurance.process.quoteEngine.businessProcess;

import java.math.BigDecimal;
import java.lang.NumberFormatException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.teradata.commons.access.TaoSession;
import com.teradata.commons.access.TaoSessionManager;
import com.teradata.commons.access.exception.DBException;
import com.teradata.commons.access.exception.DataAccessException;
import com.teradata.tza.insurance.process.exception.ApplicationException;
import com.teradata.tza.insurance.process.exception.SystemException;
import com.teradata.tza.insurance.process.businessObject.Ownership;
import com.teradata.tza.insurance.process.businessObject.Property;
import com.teradata.tza.insurance.process.businessObject.PropertyType;
import com.teradata.tza.insurance.process.quoteEngine.businessProcess.QuotationEngine;
import com.teradata.tza.insurance.process.quoteEngine.repository.ZipCodeRiskFactorsTransformDao;

public class ZipCodeTransformQuotationEngine implements QuotationEngine
{

/** Logger for this class **/
private static Log logger = LogFactory.getLog(ZipCodeTransformQuotationEngine.class);

private static final long serialVersionUID = 1L;

/**
* Number of decimals to retain. Also referred to as "scale".
*/
private static int DECIMALS = 2;
private static BigDecimal HUNDRED = new BigDecimal("100");
private static BigDecimal THOUSAND = new BigDecimal("1000");

private TaoSession taoSession = null;
private TaoSessionManager taoSessionManager = null;
public void setTaoSessionManager(TaoSessionManager taoSessionManager)
{
this.taoSessionManager = taoSessionManager;
}

private ZipCodeRiskFactorsTransformDao zipCodeRiskFactorsTransformDao;
public void setZipCodeRiskFactorsTransformDao(ZipCodeRiskFactorsTransformDao zipCodeRiskFactorsTransformDao)
{
this.zipCodeRiskFactorsTransformDao = zipCodeRiskFactorsTransformDao;
}

public BigDecimal getQuotation(Property details) throws ApplicationException, SystemException
{
logger.info("getZipCodeTransformQuote for " +
details.getHouseNameNumber() + " " + details.getStreetAddress1() + " " + details.getZipCode());

BigDecimal buildingsQuote = new BigDecimal(0.00);
BigDecimal contentsQuote = new BigDecimal(0.00);
BigDecimal finalQuote = new BigDecimal(0.00);

// If Buildings Cover required
if (details.isBuildingsCover())
{
// initial quote is based on Amount insured / 1000
buildingsQuote = details.getBuildingsAmountInsured();

// check amount is greater than 1000
if (buildingsQuote.compareTo(THOUSAND) == 1)
buildingsQuote = buildingsQuote.divide(THOUSAND, BigDecimal.ROUND_HALF_UP);
else
buildingsQuote = new BigDecimal(10.00);

// Convert Year built into a Number
int yearBuilt = 2000;
try
{
Integer x = new Integer(details.getYearBuilt());
yearBuilt = x.intValue();
}
catch (NumberFormatException ex)
{
}

// The Property Age adds an appropriate % (older costs more to replace)
if (yearBuilt < 1837)
{
// Pre_1837: +25%
buildingsQuote = buildingsQuote.add(getPercentage(buildingsQuote, new BigDecimal("25")));
}
else if (yearBuilt < 1919)
{
// _1837_1919: +20%
buildingsQuote = buildingsQuote.add(getPercentage(buildingsQuote, new BigDecimal("20")));
}
else if (yearBuilt < 1945)
{
// _1920_1945: +15%
buildingsQuote = buildingsQuote.add(getPercentage(buildingsQuote, new BigDecimal("15")));
}
else if (yearBuilt < 1979)
{
// _1946_1979: +10%
buildingsQuote = buildingsQuote.add(getPercentage(buildingsQuote, new BigDecimal("10")));
}
else if (yearBuilt < 1989)
{
// _1980_1989: +5%
buildingsQuote = buildingsQuote.add(getPercentage(buildingsQuote, new BigDecimal("5")));
}
else
{
// _1990_Onwards: +0%
}

// The Property type adds/subtracts a small %
if (details.getPropertyType().equals(PropertyType.Detached))
{
// Detached-House(0) - 10%
buildingsQuote = buildingsQuote.subtract(getPercentage(buildingsQuote, new BigDecimal("10")));
}
else if (details.getPropertyType().equals(PropertyType.Duplex))
{
// Semi-Detached House(1) -5%
buildingsQuote = buildingsQuote.subtract(getPercentage(buildingsQuote, new BigDecimal("5")));
}
else if (details.getPropertyType().equals(PropertyType.Townhouse))
{
// Townhouse(2) + 0%
// buildingsQuote = buildingsQuote.add(getPercentage(buildingsQuote, new BigDecimal("10")));
}
else if (details.getPropertyType().equals(PropertyType.Condominium))
{
// Condominium(3) + 5%
buildingsQuote = buildingsQuote.add(getPercentage(buildingsQuote, new BigDecimal("5")));
}
else if (details.getPropertyType().equals(PropertyType.Apartment))
{
// Appartment + 10%
buildingsQuote = buildingsQuote.add(getPercentage(buildingsQuote, new BigDecimal("10")));
}

// number of bedrooms adds 5% per
if (details.getNumBedrooms() > 0)
buildingsQuote = buildingsQuote.add(getPercentage(buildingsQuote, new BigDecimal("5").multiply(new BigDecimal(details.getNumBedrooms()))));

// ownership -5% owned, flat mortgaged, +5% rented
if (details.getOwnership().equals(Ownership.Owned))
{
// Owned(1) -5%
buildingsQuote = buildingsQuote.subtract(getPercentage(buildingsQuote, new BigDecimal("5")));
}
else if (details.getOwnership().equals(Ownership.Rented))
{
// Rented(2) + 5%
buildingsQuote = buildingsQuote.add(getPercentage(buildingsQuote, new BigDecimal("5")));
}

// if Alarm fitted subtract 5%
if (details.isAlarmed())
buildingsQuote = buildingsQuote.subtract(getPercentage(buildingsQuote, new BigDecimal("5")));

// if security patrolled /neighborhood watch -5%
if(details.isSecurityPatrolled())
buildingsQuote = buildingsQuote.subtract(getPercentage(buildingsQuote, new BigDecimal("5")));

// if accidental damage to building covered +5%
if(details.isBuildingsAccidentalCover())
buildingsQuote = buildingsQuote.add(getPercentage(buildingsQuote, new BigDecimal("5")));

// modify the quote based upon ZipCode use the zipCodeRiskFactor object for this
//buildingsQuote = zipCodeRiskFactorsTransformTao.getTransformedQuote(details.getZipCode(), buildingsQuote);

}

// if contents are covered work out the contents quote
if (details.isContentsCover())
{
// initial quote is based on Amount insured / 1000
contentsQuote = details.getContentsAmountInsured();
if (contentsQuote.compareTo(THOUSAND) == 1)
contentsQuote = contentsQuote.divide(THOUSAND, BigDecimal.ROUND_HALF_UP);
else
contentsQuote = new BigDecimal(1.00);

// Single Item Limit banded to add percentage
if (details.getSingleItemLimit().compareTo(new BigDecimal(1500)) == -1)
{
}
else if (details.getSingleItemLimit().compareTo(new BigDecimal(2000)) == -1)
contentsQuote = contentsQuote.add(getPercentage(contentsQuote, new BigDecimal("5")));
else if (details.getSingleItemLimit().compareTo(new BigDecimal(2500)) == -1)
contentsQuote = contentsQuote.add(getPercentage(contentsQuote, new BigDecimal("10")));
else if (details.getSingleItemLimit().compareTo(new BigDecimal(3000)) == -1)
contentsQuote = contentsQuote.add(getPercentage(contentsQuote, new BigDecimal("15")));
else if (details.getSingleItemLimit().compareTo(new BigDecimal(3500)) == -1)
contentsQuote = contentsQuote.add(getPercentage(contentsQuote, new BigDecimal("20")));
else
contentsQuote = contentsQuote.add(getPercentage(contentsQuote, new BigDecimal("25")));

// ownership -5% owned, flat mortgaged, +5% rented
if (details.getOwnership().equals(Ownership.Owned))
{
// Owned(1) -5%
contentsQuote = contentsQuote.subtract(getPercentage(contentsQuote, new BigDecimal("5")));
}
else if (details.getOwnership().equals(Ownership.Rented))
{
// Rented(2) + 5%
contentsQuote = contentsQuote.add(getPercentage(contentsQuote, new BigDecimal("5")));
}

// if Alarm fitted -5%
if (details.isAlarmed())
contentsQuote = contentsQuote.subtract(getPercentage(contentsQuote, new BigDecimal("5")));

// if security patrolled /neighborhood watch -5%
if(details.isSecurityPatrolled())
contentsQuote = contentsQuote.subtract(getPercentage(contentsQuote, new BigDecimal("5")));

// if accidental damage to building covered +5%
if(details.isContentsAccidentalCover())
contentsQuote = contentsQuote.add(getPercentage(contentsQuote, new BigDecimal("5")));
}

logger.info("Buildings Quote = " + buildingsQuote +
" Contents Quote = " + contentsQuote +
" Final Quote = " + buildingsQuote.add(contentsQuote));

try
{
// Create Session
taoSession = taoSessionManager.getSession();
logger.debug("Session created:" + taoSession);

// Initialize DAO objects - set current session
zipCodeRiskFactorsTransformDao.setTaoSession(taoSession);

// Begin session / transaction
taoSession.beginSession();
taoSession.beginTransaction();

// Transform this quotation using the propertyDetails.zipCode
finalQuote = zipCodeRiskFactorsTransformDao.getTransformedQuote(details.getZipCode(), buildingsQuote.add(contentsQuote));
logger.info("Transformed Quote = " + finalQuote);

// Commit transaction
taoSession.commitTransaction();
}
// Catch recoverable application error - zipCodeRiskFactors not found
catch (DataAccessException ex)
{
// Rollback Transaction
taoSession.rollbackTransaction();

// Throw Application Exception
ApplicationException appEx = new ApplicationException("getTransformedQuote exception", ex);
throw appEx;
}
// Catch unrecoverable database error
catch (DBException ex)
{
// Rollback Transaction
taoSession.rollbackTransaction();

// Throw System Exception
SystemException sysEx = new SystemException("getTransformedQuote exception", ex);
throw sysEx;
}
// Always end session
finally
{
taoSession.endSession();
}

return finalQuote;
}

private BigDecimal getPercentage(BigDecimal source, BigDecimal percentage)
{
// Ensure we have sufficient scale for the calculation
if (percentage.scale() < 4)
percentage = percentage.setScale(4);

// Calculate the percentage - multiply by 100 divide by percentage.
BigDecimal result = source.multiply(percentage);
result = result.divide(HUNDRED, BigDecimal.ROUND_HALF_UP);
return rounded(result);
}

private BigDecimal rounded(BigDecimal aNumber)
{
return aNumber.setScale(DECIMALS, BigDecimal.ROUND_HALF_UP);
}
} // end ZipCodeTransformQuotationEngine

In general the algorithm to calculate the actual base Quotation amount is identical to the one used in the ZipCodeQuotationEngine.java implementation back in FNP #6 (and if we were really cleaver we would make one class extend from another or something). However, the primary difference between these two Implementations of the Quotation Engine is that in the ZipCodeQuotationEngine we use a DAO to get the Zip Code Risk Factors that are then applied to the Quotation Amounts for Buildings and Contents Quotes while in the ZipCodeTransformQuotationEngine we apply the total quotation amount through a Stored Procedure to an embedded analysis algorithm like this:

            // Transform this quotation using the propertyDetails.zipCode
finalQuote = zipCodeRiskFactorsTransformDao.getTransformedQuote(details.getZipCode(), buildingsQuote.add(contentsQuote));
logger.info("Transformed Quote = " + finalQuote);

Add the transformQuoteEngineAppContext.xml

Spring Application Context files are used to "wire" Plain Old Java Objects (POJO's) together. We will create a transformQuoteEngineAppContext.xml to configure how this part of the application will operate. We will use this file to create a version of the SimpleQuoteEngine that uses the ZipCodeTransformQuotationEngine and the ApplyRiskFactorsToQuote Stored Procedure.

To add transformQuoteEngineAppContext.xml start with the TZA-InsuranceProcess project:

  • Expand the src/resources folder
  • Right click on the com.teradata.tza.insurance.process.quoteEngine package
  • Select New -> Other.. -> XML -> XML
  • Set the file name as transformQuoteEngineAppContext.xml
  • Select Finish and a template XML file will open within the Eclipse editor.

The transformQuoteEngineAppContext.xml file can then be built up by replacing the template code with the following code segments within the transformQuoteEngineAppContext.xml file using copy and paste.

<?xml version="1.0" encoding="UTF-8"?>

<beans
xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

<!-- TZA-InsuranceProcess Transform based Quotation Engine -->
<bean id="transformQuotationEngine"
class="com.teradata.tza.insurance.process.quoteEngine.businessProcess.ZipCodeTransformQuotationEngine"
scope="prototype">
<property name="taoSessionManager" ref="jdbcTaoSessionManager"/>
<property name="zipCodeRiskFactorsTransformDao" ref="zipCodeRiskFactorsTransformDao"/>
</bean>

<bean id="zipCodeRiskFactorsTransformDao"
class="com.teradata.tza.insurance.process.quoteEngine.repository.JdbcZipCodeRiskFactorsTransformSPao"
scope="prototype">
<property name="storedProcedureName" value="ApplyRiskFactorsToQuoteSP"/>
</bean>

</beans>

Note how we define a new bean called transformQuotationEngine, implemented by the ZipCodeTransformQuotationEngine, which has a property of ZipCodeRiskFactorsTransformDao that is implemented using the JdbcZipCodeRiskFactorsTransformSPao object and in this instance the ApplyRiskFactorsToQuoteSP Stored Procedure.

Rebuild the TZA-InsuranceProcess.jar

  • Within the Ant View: Open up the TZA-InsuranceProcess - Local Build file.
  • Double Click the jar default task to create the TZA-InsuranceProcess.jar file.

Refresh Eclipse

Because the Ant build works at the Hard Disk Level you need to Refresh Eclipse so it can pick up changes such as the creation of the /dist directory and the TZA-InsuranceProcess.jar.

  • Select the TZA-InsuranceProcess project and use the F5 function key to refresh the environment.
  • Open up the /dist directory and the TZA-InsuranceProcess.jar file will be visible.

Testing the Embedded Analytic Stored Procedure Integration

So now that we have created the new TZA-InsuranceProcess.jar file with a Stored Procedure that can access the Zip Code Risk Factors table and perform Embedded Analytics upon the results we better test if this works.

Update the TZA-InsuranceProcess.jar

First task is to copy the new version of the TZA-InsuranceProcess.jar from the /dist directory of the TZA-InsuranceProcess project to the /lib directory of TZA-InsuranceService.

  • Expand the /dist folder within TZA-InsuranceProcess
  • Right click on TZA-InsuaranceProcess.jar
  • Select Copy (or use Ctrl-C)
  • Expand the WEB-INF/lib folder within TZA-InsuranceService
  • Right Click on the lib folderSelect Paste (or use Ctrl-V)
  • On the Resource Exists dialog select Yes to overwrite with the new version

Defining the “transformQuotationEngine” Bean in applicationContext.xml

Now we revisit how the Quotation Engine is defined within the applicationContext.xml file.

  • Expand the web/WEB-INF folder within TZA-InsuranceService.
  • Open applicationContext.xml (Double Click it, or Right click it and select Open or F3)
  • Search for the following line of code
    <!-- Define the simpleQuotationEngine bean as one or other of sql, macro or storedProc -->
<bean id="simpleQuotationEngine" parent="storedProcQuotationEngine" scope="prototype"/>

The simpleQuotationEngine bean will be defined as sql, macro or storedProcQuotationEngine (depending upon the last approach you have been testing with). Modify the simpleQuotationEngine definition to make an association with the transformQuotationEngine as follows:

    <!-- Define the simpleQuotationEngine bean as one or other of sql, macro or storedProc -->
<bean id="simpleQuotationEngine" parent="transformQuotationEngine" scope="prototype"/>

<!-- Import the TZA-InsuranceProcess QuoteEngine context -->
<import resource="classpath:com/teradata/tza/insurance/process/quoteEngine/sqlQuoteEngineAppContext.xml" />
<import resource="classpath:com/teradata/tza/insurance/process/quoteEngine/macroQuoteEngineAppContext.xml" />
<import resource="classpath:com/teradata/tza/insurance/process/quoteEngine/storedProcQuoteEngineAppContext.xml" />
<import resource="classpath:com/teradata/tza/insurance/process/quoteEngine/transformQuoteEngineAppContext.xml" />

Note the fourth import statement used to bring the transformQuoteEngineAppContext into the Application Context.

Start the Application Server

If you had closed down your application server you will need to start it up.

  • Start / Restart the Server using the Start Icon on the Server View

Restart Server

Start the Web service Test Client

You will probably need to start up the Web service Test Client again also. You can simply browse to it by pointing your favorite Web browser at http://localhost:8080/TZA-InsuranceServiceClient/samplePropertyInsurance.... Alternatively you can stay within Eclipse.

  • Open up the TZA-InsuranceServiceClient project WebContent/samplePropertyInsuranceProxy/ directory.
  • Right Click TestClient.jsp
  • Select Run As.. -> Run on Server -> Finish
  • Within the Web Services Test Client browser select the getQuote method in the Methods frame.
  • Scroll down through the Inputs frame and Select the Invoke button.
  • Examine the Web service response in the Result frame.

Web Service Result using ApplyRiskFactorsToQuoteSP

Examine the Query using DBQL

We can use DBQL to examine exactly what queries are being used as we exercise the getQuote method of the Web service. Clear any existing DBQL entries and Begin Query Logging by selecting the appropriate SQL within the ManageDBQL.sql file.

  • Expand the src/sql folder within TZA-Database.
  • Open ManageDBQL.xml (Double Click it, or Right click it and select Open or F3)
  • Search for the following line of code
--
--Clear Existing DBQL and BEGIN QUERY LOGGING;
--
DELETE FROM DBC.DBQLogTbl;
BEGIN QUERY LOGGING ON ALL;

Right Click on the selected text and select Execute as Individual Statements.

Run the getQuote Web service Method again by selecting Invoke within the Web service Test Client. Check the Quotation amount against the one you wrote down last week (it’s probably on a scrap of paper beside your keyboard!).

Lock down the Query Logging by running the appropriate SQL within the ManageDBQL.sql file:

--
-- END QUERY LOGGING and Show the current DBQL;
--
END QUERY LOGGING ON ALL;
SELECT QueryBand, QueryText FROM DBC.DBQLogTbl order by StartTime;

Once the execution is complete we can drill into the final operation “SELECT QueryBand..” and open up the Result1 View so we can see how each statement was executed with “CALL “ApplyRiskFactorsToQuoteSP” (?,?,?)” displayed as the Query Text.

SQL Result when using ApplyRiskFactorsToQuoteSP

So there you go Embedded Analytics using the Stored Procedure language to perform the simple mathematics necessary to apply the Zip Code based Risk Factors to an Incoming Quotation value in order to calculate and return an Outgoing Quotation value. Obviously once you are operating within this Stored Procedure environment you can conduct a wide range of Analytic operations, issuing multiple SQL requests (from within the Database), using procedural logic and mathematical processing, without ever leaving the database, in order to come up with some final output result or predefined set thereof.

While we did not cover it here Stored Procedures can also return Result Sets (in the same way that a normal SQL Statement does) so if appropriate your applications can continue to operate against relational data even when a Stored Procedure is used to actually conduct the Embedded Analysis.

Next Week we will look at an alternative mechanism for providing Embedded Analytics namely Java External Stored Procedures (JXSP).