Synchronize Data with Logic

Synchronize Data with Logic
lac42
Logic processing interacts with values that are in the database. It uses strategies to synchronize data with logic. This article explores the strategies for synchronizing data with logic and identifies best practices on which strategy to use.
In this article:
Strategies for Synchronizing Data with Logic  
To maintain high performance, 
CA Live API Creator
presumes that the persistent attribute values match your logic. You can synchronize data with logic using one of the following strategies:
For example, in the 
Demo
 API sample, 
CA Live API Creator
 assumes that the 
Customer balance
 is correct. You can avoid expensive aggregate (
select sum
) queries by adjusting the values on 
purchaseorder
 changes. This kind of logic presumes that the values stored on disk are correct with respect to the rules.
The 
Customer balance
 is initially defined as the sum of the Order AmountTotals (unqualified). You insert data, and now the balance is now $100 consisting of a paid order for $30 and an unpaid order for $70. 
CA Live API Creator
 alters the 
Customer balance
 to the sum of the unpaid order totals. At this point, the balance should be $70, but 
CA Live API Creator
stores it as $100. Altering the rule does not alter the existing data. Deleting the paid order does not reduce the balance. It remains $100, which is still incorrect.
Specify Just-In-Time Recompute for a Rule
You can specify to have 
CA Live API Creator
 recompute the data value for your rule when/as it accesses the data and detects null values, whether for update or retrieval. Having 
CA Live API Creator
 recompute the rules value is particularly useful when you add a new column and a new rule. Newly-added columns are blank and therefore the values are null. Like normal rule operation, the Just In Time (JIT) recompute strategy addresses dependencies, so you can add new chained rules and 
CA Live API Creator
 recomputes your rules when/as it detects null values.
Follow these steps:
  1. With your API open, in the Manage section, click
    Rules
    .
  2. Click to open the derivation rule (formula, sum, count, and min/max) that you want to specify to have 
    CA Live API Creator
     recompute the data values.
    The rule opens.
  3. Select the 
    Just In Time
     checkbox, and then save your changes.
Use SQL to Ensure Conformance
JIT compilation might not fit all of your scenarios. For example, if you use business intelligence tools to access the data, the not-yet-recomputed data will be wrong. As in the
Demo
API sample, you might have existing non-null data that 
CA Live API Creator
needs to correct. 
CA Live API Creator
does not detect this data since it is looking for null values. You can use SQL to bring your current schema into conformance.
 For example, the following (MySQL) queries are used in the
Demo
API sample for the rules:
update lineitem item set item.product_price = (select price from product p where p.product_number = item.product_number);
update lineitem item set item.amount = item.qty_ordered * item.product_price;
update purchaseorder po set po.amount_total = 
(select sum(item.amount) from lineitem item where (item.order_number = po.order_number));
update customer cust set cust.balance = 
(select sum(po.amount_total) from purchaseorder po where po.customer_name = cust.name and po.paid = false);
The following image shows the rules for the
Demo
API sample:
Screen Shot 2015-10-22 at 10.07.44 AM.png
If you are using SQL Server, a similar script is:
update [dbo].[Customers] set Balance =
  (select sum(orders.AmountTotal) from [dbo].[Orders] orders 
    where (orders.CustomerID = [dbo].[Customers].CustomerID));
Synchronize Data with Logic Best Practices
Use JIT when:
  • Your columns are new, and therefore, the values are null.
  • Other non-API-based software does not depend on correct data.
You can use a hybrid approach. If you have existing now-incorrect data, you can issue SQL update commands to nullify the values. This triggers the recompute logic that is described in the "Specify Just-In-Time Recompute" section.
For a list of all best practices, see
CA Live API Creator
Best Practices.