A Custom Approach to Using Multiple Corticon Datasources

A Custom Approach to Using Multiple Corticon Datasources

Posted on August 13, 2018 0 Comments
A Custom Approach to Using Multiple Corticon Datasources_870x450

Thanks to the extensibility of the Corticon Advanced Data Connector, you can dynamically connect to multiple databases even in unusual situations. Read on to learn how.

Today business decisions require data that is stored across multiple databases. With the Progress Corticon Advanced Data Connector (ADC), Decision Services can dynamically retrieve data from separate databases. In this post we will investigate a special case where the data for a business decision is stored in two databases with an implied relation (implied join) spanning the two separate data sources, retrieve separate sets of data and integrate them into a Ruleflow.

This is a great example of how to customize a Corticon Service Callout to implement features beyond those of an ADC multiple database configuration. This cross database join can be accomplished by adding some custom code.

Note: This example assumes a working knowledge of Java, Corticon APIs, Corticon Studio, ADC, relational databases and Java Database Connectivity (JDBC).

For this scenario, the business decision will assess a proposal for an investment account to purchase specific assets. The Decision Service will receive as input the investment account and several possible assets for purchase.

It will access internal and external databases for additional information about the account and the assets. Then, it will determine which of the assets the account should purchase. The data required for the decision is retrieved from two databases: an internal database holds private secure account information, and an external database holds public asset information.

Step 1: Set Up the Vocabulary

The vocabulary consists of Proposal, Asset, AssetType and Account entities.  The Account entity attributes match the columns of the Account table in the internal database. One Account can be associated with many AssetTypes which will hold the asset types the account is allowed to purchase.  Asset and AssetType attributes match the columns of the Asset and AssetType tables in the external database. One AssetType can be associated with many Asset entities.

Setup Vocabulary - Corticon

Figure 1: The Vocabulary tree in Corticon Studio

Step 2: Define Data Sources

Start with the internal database. Add an ADC Datasource to the vocabulary and enter a name, such as “internalDB.”  Enter the database connection information.  Import the metadata from the database or enter the mappings manually if needed.

 

Define Data Sources - Corticon

Figure 2: Set up the ADC Datasource for the internal database

Do the same for the external database. Add another ADC Datasource to the vocabulary and enter a different name, such as “externalDB.” Enter the database connection information. Import the metadata from the database or enter the mappings manually if needed.

Define Data Sources 2 - Corticon

Figure 3: Set up another ADC Datasource for the external database

Learn more about setting up ADCs in the Corticon Server: Data Integration Guide.

Step 3: Create a Service Callout

The service callout (SCO) needs to do 10 things:

  1. Connect to the internal database
  2. Using the input data, query Account and create an entity and attributes with the retrieved data
  3. Collect the AssetType keys associated with Account
  4. Get Asset entities from DataManager
  5. Connect to the external database
  6. Query Asset table and enrich the Asset entities
  7. Collect AssetType keys associated with Asset entities
  8. Query AssetType table and create AssetType entities
  9. Associate Asset with AssetType
  10. Associate Account with AssetType entities

 

1. Connect to the internal database

IDatasource lDatasource = aDataMgr.getDatasourceManager().getDatasource("internalDB");
String lstrDatabaseId = lDatasource.getDatasourceDriverId();
 
String lstrDatabaseUrl = lDatasource.getDatasourceConnectionURL();
String lstrUsername = lDatasource.getDatasourceUserName();
String lstrPassword = lDatasource.getDatasourcePassword();
Connection lConnection = aDataMgr.getDatabaseDriverManager().getConnection("internalDB", lstrDatabaseId, lstrDatabaseUrl, lstrUsername, lstrPassword);

 

2. Using the input data, query Account and create an entity and attributes with the retrieved data

Set<ICcDataObject> proposalSet = aDataMgr.getEntitiesByName("Proposal");
ICcDataObject proposal = (ICcDataObject)proposalSet.iterator().next();
 
      // create Account entity
ICcDataObject account = aDataMgr.createEntity("Account");
 
ArrayList<Long> accountAssetTypeIds = new ArrayList<Long>();
Statement lStatement = lConnection.createStatement();
String lstrQuery = "select id,name,cash from Account where id = " +
  proposal.getAttributeValue("accountId");     
ResultSet lResultSet = lStatement.executeQuery(lstrQuery);
lResultSet.next();
         // set Account attributes with query results
Long accountId = lResultSet.getLong("id");
account.setAttributeValue("id", accountId);
account.setAttributeValue("name", lResultSet.getString("name"));
account.setAttributeValue("cash", lResultSet.getBigDecimal("cash"));

 

3. Collect the AssetType keys associated with Account

lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(
  "select assetTypeId from AccountAssetType where accountId = " + accountId);
         // keys collected in accountAssetTypeIds
while (lResultSet.next())
{
accountAssetTypeIds.add(lResultSet.getLong("assetTypeId"));
}

 

4. Get Asset entities from DataManager

Set<ICcDataObject> tradeAssets = aDataMgr.getEntitiesByName("Asset");
         // map asset Id to Asset entity for lookup ease
Map<Long, ICcDataObject> assetIdMap = new HashMap<Long, ICcDataObject>();
for (ICcDataObject asset : tradeAssets)
{
assetIdMap.put((Long)asset.getAttributeValue("id"), asset);
}

 

5. Connect to the external database

IDatasource lDatasource = aDataMgr.getDatasourceManager().getDatasource("externalDB");
String lstrDatabaseId = lDatasource.getDatasourceDriverId();
String lstrDatabaseUrl = lDatasource.getDatasourceConnectionURL();
String lstrUsername = lDatasource.getDatasourceUserName();
String lstrPassword = lDatasource.getDatasourcePassword();
Connection lConnection = aDataMgr.getDatabaseDriverManager().getConnection("externalDB", lstrDatabaseId, lstrDatabaseUrl, lstrUsername, lstrPassword);

 

6. Query Asset table and enrich the Asset entities (Data Enrichment)

Map<ICcDataObject, Long> assetToAssetTypeId = new HashMap<ICcDataObject, Long>();
String lstrQuery = "select id,name,price,assetTypeId from Asset where id in " + assetIdMap.keySet();
lstrQuery = lstrQuery.replace('[', '(');
lstrQuery = lstrQuery.replace(']', ')');
Statement lStatement = lConnection.createStatement();
ResultSet lResultSet = lStatement.executeQuery(lstrQuery);
while (lResultSet.next())
{
   Long assetId = lResultSet.getLong("id");
            // lookup asset by id
   ICcDataObject asset = assetIdMap.get(assetId);
            // set attributes
   asset.setAttributeValue("name", lResultSet.getString("name"));
   asset.setAttributeValue("price", lResultSet.getBigDecimal("price"));
  Long assetTypeId = lResultSet.getLong("assetTypeId");

 

7. Collect AssetType keys associated with Asset entities

   assetToAssetTypeId.put(asset, assetTypeId);
}

 

8. Query AssetType table and create AssetType entities

lStatement = lConnection.createStatement();
lstrQuery = "select id,type from AssetType where id in " + assetToAssetTypeId.values();
lstrQuery = lstrQuery.replace('[', '(');
lstrQuery = lstrQuery.replace(']', ')');
lResultSet = lStatement.executeQuery(lstrQuery);
while (lResultSet.next())
{
            // create AssetType entity
   ICcDataObject assetType = aDataMgr.createEntity("AssetType");
            // set attributes
   Long assetTypeId = lResultSet.getLong("id");
   assetType.setAttributeValue("id", assetTypeId);
   assetType.setAttributeValue("type", lResultSet.getString("type"));

 

9. Associate Asset with AssetType

for (ICcDataObject asset : tradeAssets)
{
    if (assetToAssetTypeId.get(asset).equals(assetTypeId))
    {
       asset.addAssociation("assetType", assetType);
    }
}

 

10. Associate Account with AssetType entities

  if (accountAssetTypeIds.contains(assetTypeId))
   {
      account.addAssociation("allowedAssetTypes", assetType);
   }
}

 

Step 4: Test the Service Callout

To test the data retrieval, create a Corticon Ruleflow and add a Service Call-out (SCO) node. Point this SCO to the method created above. To learn how to set up SCOs, see Corticon Server: Data Integration Guide: Use the ADC Connection in a service callout.

Create a Ruletest with the new Ruleflow as the test subject. And supply input data as shown below.

The internal database has an Account with id = 1.  Accordingly, the input test data includes a Proposal with the attribute accountId set to 1.

The external database has six Assets defined with ids = 1,2,3,4,5 and 6.  Accordingly, the input test data includes six Asset entities associated with the Proposal. Their id attributes correspond to those in the database.

Run the test. If the data connection is successful, the output will include the data enrichment of the Asset entities and the new Account entity with allowed asset types, as shown in the output panel below.

Test Service Callout - Corticon

Figure 4: Input and output for a successful test of the ADC service callouts.

At this point, the necessary data is available to determine which assets the account should purchase, and you can model the decision logic in Rulesheets. The associations between Account and AssetType entities defined in the vocabulary integrate the input data with the data retrieved from two separate sources. The service callout method supports that association as well (i.e., Account.allowedAssetTypes.asset).

Keep in mind, in this example, the data source mappings are exact names.  In reality, the vocabulary name usually does not match the database name, so you need to map database properties to Vocabulary properties. The ICcDataManager provides access to vocabulary metadata for lookup of entity, attribute and role names.  You can also access the data source metadata for column names and return types. To learn more about mapping data, see Corticon Data Integration Guide: Create and map the ADC schema and queries.

You now have the tools to access two databases in one Decision Service for additional flexibility and power in your applications. To learn more, request a demo of Corticon 5.7, and don’t forget to check out all the updates in the latest release.

Eric Moore profile

Eric Moore

Eric Moore is a Software Architect at Progress.

Comments

Comments are disabled in preview mode.
Topics

Sitefinity Training and Certification Now Available.

Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.

Learn More
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

Loading animation