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.
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.
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.
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:
- Connect to the internal database
- Using the input data, query Account and create an entity and attributes with the retrieved data
- Collect the AssetType keys associated with Account
- Get Asset entities from DataManager
- Connect to the external database
- Query Asset table and enrich the Asset entities
- Collect AssetType keys associated with Asset entities
- Query AssetType table and create AssetType entities
- Associate Asset with AssetType
- 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 entityICcDataObject 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 resultsLong 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 accountAssetTypeIdswhile (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 easeMap<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.
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
Eric Moore is a Software Architect at Progress.