Innovator uses the term “Federated” to mean data shared between different information systems.
Virtually all businesses are using multiple information systems to address different aspects of their business, these are usually at different stages in their lifecycle.
Many business processes need to draw on information from multiple systems in the most cost effective way.
Aras Innovator has a Service Oriented Architecture and is a Framework; it lends itself to addressing such needs.
This document describes how the Innovator Framework can gather and present Federated Data, and how it can provide data to other systems.
<o:p> </o:p>The Innovator Framework includes
- ‘Federated’ data-type for Properties
- ‘Federated’ Implementation type for ItemTypes.
- ‘Server Events’ which call .Net Methods when Items are selected, added, updated or deleted to implement the Interface.
The following table shows how different technologies can used as the interface to implement Federated Data where Innovator is the destination or source.
<o:p>
|
Interface
|
Innovator as Destination
|
Innovator as Source
|
|
Web Services
|
Methods consume WebService and show data in the Innovator UI transparently
|
Innovator 9.1 “Web Service Configuration” can create Web Services automatically. They can be consumed by remote applications
|
|
SQL
|
Innovator Methods or SQL Items connect to remote database using SQL Server Linked Server or ADO.Net
|
Remote database connects to Innovator database using SQL Server Linked Server, ADO.Net, ODBC or JDBC
|
|
CAD Integration
|
CAD integrations are available at Aras.com/Projects for two-way sharing of data with popular CAD programs
|
|
<st1:place w:st="on"><st1:city w:st="on">Enterprise</st1:city></st1:place> Service Bus
|
Methods put jobs onto the job queue
|
Innovator responds to requests from the job queue
|
|
API
|
Innovator Methods call remote system API
|
Remote system can use Innovator Object Model (IOM) API using COM or .Net
|
|
Other
|
Methods can do anything .Net can do
|
|
</o:p>
When a Property is of data-type ‘Federated’ it still appears on forms and in grids, just like every other Property, but no column is created in the database and values for the Property will ignored in Innovator unless means of providing and acting upon values are created.
The simplest way to provide a value for a Federated Property is in an onAfterGet Server Event. This onAfterGet Server Event says “after you have done what you normally do, execute this Method”. The Method code can get a value in any way it chooses and return it in the context Item for further processing. For a get request this means the value will be stored in the client cache and appear in any Forms or Grids just like all the other properties.
When the value is returned to the server by a save or other code it will be ignored, unless means are provided to act on it.
Note there is also a Foreign data-type which serves a different purpose. Foreign Properties have no column in the database either, but values are provided from Properties of type Item in the same ItemType.
ItemTypes can be of Implementation ‘Table’, ‘Federated’ or ‘Polymorphic’.
· Table is the ordinary implementation used by most ItemTypes.
· Federated means that at least some of the values of the Properties for the Item will come from a source other than the Innovator database.
· Polymorphic is used for PolyItems like Deliverable in the Program Management Solution and Affected Item in the Product Engineering Solution
Selecting Federated Implementation type does not actually change the behavior of an ItemType, the behavior needs to be implemented by Server Events, so Federated Implementation is more of a flag than a switch. (Note this is different for Polymorphic, selecting Polymorphic determines the behavior of the ItemType when it is created and you cannot change Polymorphic to or from other implementation types.)
Federated behavior for an ItemType is provided by using onAdd, onGet, onUpdate and onDelete Server Events. ‘on’ Events are distinct from ‘onBefore’ and ‘onAfter’ events.
· ‘onBefore’ events say “before you do what you normally do, do this”
· ‘onAfter’ events say “after you do what you normally do, do this”
· ‘on’ evens say “instead of doing what you normally do, do this”
If no onAdd, onGet, onUpdate and onDelete Server Events are created for a Federated Implementation, the Item will behave like an ordinary Table Implementation.
If onAdd, onGet, onUpdate and onDelete Server Events are created with Method code that does nothing, the ItemType will exist but there will be no instances and no behavior.
The simplest Federated Item would have an onGet Server Event which would provide values whenever a get request was made. There would be no data in the Innovator database for the ItemType but values would be returned by the Innovator Server and displayed in the Innovator Client . The values would be determined by the Method code. This would be appropriate for the case where it was required to simply show information from a remote data source in Innovator.
For more complex requirements where information about an item may be shared between Innovator and remote data sources, a stub Item may be stored in the Innovator database including a unique key to the remote data and any data managed in Innovator, other data would be retrieved from the remote data source when needed. In this case a combination of onAdd, onGet, onUpdate and onDelete Server Events can be designed to create the behavior required. In order to do the required behavior is must be clearly defined.
When any instance data is added to the Innovator database, system properties such as id, created_on, created_by, state etc are created automatically. When this occurs the Item will be able to have any and all standard Innovator behaviors including Permissions, History, Relationships, Lifecycle and Workflow. These behaviors can augment the data stored in remote systems.
The Event for Sever Events needs to be chosen by considering
a) the intended behavior
b) how Innovator works
For the example:
CI Inventory
· onGet is used because we want to replace standard Innovator behavior
CI Order
· onAfterGet is used because we want to get data from the Innovator database then use it to get data from the remote system
· onAfterAdd, onAfterUpdate is used because we want Innovator to do whqt it normally does, onBefore would probably work as well
· onBeforeDelete is used because we won’t be able to get data we need after we delete it
1. Determine the sources and desired behavior for data to be federated. It helps to document this in a Use Case.
2. Design the Data Model identifying ItemTypes, Relationships, Properties indicating which will be federated and which stored in Innovator.
3. Select and test the interface type to be used.
4. Build the Data Model
5. Implement Server Events
6. Test the application
1. Use case summary
a. In the simple Retail Catalog application used in Admin, Workflow and API training a Retail Catalog has a relationship to Catalog Items, we will add the following Federated Data
b. Catalog Items will have two relationships CI Inventory and CI Order
c. CI Inventory represents the inventory on hand at each Catalog Item at several warehouses
d. CI Order represents orders to replenish inventory for a single Catalog Item at a single warehouse
e. CI Inventory data will be provided by a remote system, no data will reside in Innovator but inventory records will be shown in the Catalog Item relationship grid.
f. Catalog Item will have a property indicating the quantity in stock which will be retrieved from data on the remote system calculated as sum of quantity on hand at all warehouses.
g. CI Order data will be shared between Innovator and the remote system and will shown in the Catalog Item relationship grid.
h. CI Order records may be added in either Innovator or the remote system, the remote system alone will control the quantity received.
i. A Lifecycle will be implemented in Innovator for CI Orders. Orders with qty received less than qty ordered will be in the <st1:place w:st="on"><st1:placename w:st="on">Open</st1:placename> <st1:placetype w:st="on">State</st1:placetype></st1:place> and other orders will be in the Closed state
2. Innovator Items
a. ItemType Retail Catalog
i. Implementation: Table
b. ItemType Catalog Item
i. Implementation: Table
c. RelationshipType Retail Catalog Item
i. Source: Retail Catalog
ii. Related: Catalog Item
iii. Relationship ItemType Implementation: Table
d. RelationshipType CI Inventory
i. Source: Catalog Item
ii. No related
iii. Relationship ItemType Implementation: Federated, no data in Innovator
iv. ServerEvent on get to implement business rules
e. RelationshipType CI Order
i. Source: Catalog Item
ii. Relationship ItemType Implementation: Federated, stub data in Innovator
iii. No related
iv. Lifecyle Open and Closed, World can promote
v. ServerEvents on get, add, update and delete to implement business rules
3. Business rules
a. For Catalog Item show sum of quantity on hand at all warehouses
b. For Catalog Inventory show warehouse and qty on hand
c. For Catalog Item Order show warehouse, qty on order, qty received
a. Since Catalog Item Order has data shared between Innovator and the remote system we need to define where information can be added changed and deleted in these rules. You may choose different rules according the business need.
b. CI Orders will only be displayed in the relationship grid of Catalog Items, ie where the source_id is known.
c. When a new CI Order is added to Innovator and it does not exist on the remote system, it will be added to both systems
d. When a CI Order order quantity is edited in Innovator it will also be updated on the remote system
e. When a CI Order is deleted in Innovator, it will be deleted from the remote system also
f. An action may be run in Innovator which will synchronize data between Innovator and the remote system
i. An CI Orders on the remote system but not in Innovator will be added to Innovator
ii. Any CI Orders not on the remote system but in Innovator will be deleted from Innovator
iii. All CI Orders in Innovator will be promoted to the appropriate state
4. User Interface
a. No special requirements
A SQL interface will be used.
For simplicity ‘remote data’ will be in tables not owned by innovator. Data might just as easily be in a linked server, which can be referenced directly using sql in the Innovator data server
CREATE TABLE [dbo].[fed_ci_inventory](
[wh] [varchar](1) NULL,
[item_number] [nvarchar](7) NULL,
[qty_oh] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[fed_ci_order](
[ordno] [int] NULL,
[wh] [char](1) NULL,
[item_number] [varchar](8) NULL,
[qty_ord] [int] NULL,
[qty_rcd] [int] NULL
) ON [PRIMARY]
// get the item_number, it's not passed automatically
Item qry0 = this.newItem("Catalog Item");
qry0.setAttribute("select","item_number");
qry0.setID(this.getProperty("source_id"));
Item res=qry0.apply("get");
if (res.isError()) return res;
// get federated data
// we use a SQL Item here rather than applySQL(...)
// because SQL PROCESS conveniently takes
// id and type columns from the result set
// and turns them into attributes of the returned Items
Item qry1 = this.newItem("SQL","SQL PROCESS");
qry1.setProperty("name","select_ci_inventory");
qry1.setProperty("PROCESS","CALL");
qry1.setProperty("ARG1", res.getProperty("item_number"));
res = qry1.apply();
return res
create procedure select_ci_inventory (@ci varchar(8))
AS
BEGIN
select replace(NewID(),'-','') id, 'CI Inventory' type, *
from dbo.fed_ci_inventory
where item_number=@ci
END
// onAfterGet may return a single Item for example to populate an Item Window
// or a collection of Items for example to populate the Main Grid
// so we loop through the context Item
for (int i=0;i<this.getItemCount();i++){
Item ci = this.getItemByIndex(i);
// get data for the key to the remote system
string ordno =ci.getProperty("ordno");
string wh=ci.getProperty("wh");
string item_number=ci.getProperty("item_number");
if (ordno==null||wh==null||item_number==null){
// the context Item might only contain the id
// in which case we'll get them from the server
Item ci2= this.newItem("CI Order");
ci2.setID(this.getItemByIndex(i).getID());
ci2.setAttribute("select","ordno,wh,item_number");
ci2=ci2.apply("get");
if (ci2.isError()) return ci;
ordno =ci2.getProperty("ordno");
wh=ci2.getProperty("wh");
item_number=ci2.getProperty("item_number");
}
// retrieve data from the remote system
// in this case we’ll use applySQL(…)
// all the code is in the method, no separate SQL Item
// in C# the @”…” indicates a string literal
// so the string can include linebreaks
// and String.Format(…) makess substitutions
string sql=@"
select qty_rcd from dbo.fed_ci_order
where ordno={0} and wh={1} and item_number='{2}'";
sql=String.Format(sql,ordno,wh,item_number);
Item res =this.getInnovator().applySQL(sql);
if (res.isError()) return res;
// update context Item with retrieved data
if (res.getResult()!=""){
string qty_rcd = res.getProperty("qty_rcd","0");
ci.setProperty("qty_rcd",qty_rcd);
}
}
// pass updated contextItem back to Innovator
return this;
<o:p> </o:p>
// get data for key to remote system from context Item
string ordno=this.getProperty("ordno");
string wh=this.getProperty("wh");
string item_number=this.getProperty("item_number");
string qty_ord=this.getProperty("qty_ord");
// add a new row to the remote system
string sql=@"
insert into dbo.fed_ci_order select '{0}','{1}','{2}','{3}', null
where not exists
(select * from dbo.fed_ci_order
where ordno={0} and wh={1} and item_number='{2}')
";
sql=String.Format(sql,ordno,wh,item_number,qty_ord);
Item result=this.getInnovator().applySQL(sql);
// no further processing in Innovator
// but c# must return something, if it's an error the add will roll back
// get data for key to remote system from context Item
string ordno=this.getProperty("ordno");
string wh=this.getProperty("wh");
string item_number=this.getProperty("item_number");
string qty_ord=this.getProperty("qty_ord");
// update remote system
string sql=@"
update dbo.fed_ci_order
set qty_ord={3}
where ordno={0} and wh={1} and item_number='{2}'
";
sql=String.Format(sql,ordno,wh,item_number,qty_ord);
Item result=this.getInnovator().applySQL(sql);
// no further processing in Innovator
// but c# must return something, if it's an error the update will roll back
return result;
<o:p> </o:p>
// delete server event passes only id in the context Item
// get data for key in remote system
Item o=this.newItem("CI Order");
o.setID(this.getID());
o=o.apply("get");
if (o.isError()) return o;
string ordno=o.getProperty("ordno");
string wh=o.getProperty("wh");
string item_number=o.getProperty("item_number");
// delete row from remote system
string sql=@"
delete from dbo.fed_ci_order
where ordno={0} and wh={1} and item_number='{2}'";
sql=String.Format(sql,ordno,wh,item_number);
Item result=this.getInnovator().applySQL(sql);
if (result.isError()) return result;
// return context Item to continue processing in Innovator
return this;
<o:p> </o:p>
Innovator innov=this.newInnovator();
// find CI Orders on remote system not in Innovator, and add them
string sql= @"
select distinct f.ordno, f.wh, ci.item_number, ci.id as source_id
from dbo.fed_ci_order f
left outer join innovator.ci_order i
on f.ordno= i.ordno and f.wh=i.wh
inner join innovator.catalog_item ci
on ci.item_number= f.item_number
where i.ordno is null";
Item adds =innov.applySQL(sql);
if (adds.isError()) {return adds;}
// if the resultset is empty applySQL returns an empty Result
if (adds.getResult()!=""){
// applySQL returns a resultset which looks like AML
// but we need to add 'type' and 'action' attributes
// we'll build an aml string containing a collection of Items that can be applied once
string aml="<AML>";
for (int i=0; i<adds.getItemCount();i++){
Item a=adds.getItemByIndex(i);
a.setType("CI Order");
a.setAction("add");
// turn add server event off
// we don't want the onAfterAdd event to run!
a.setAttribute("serverEvents","0");
aml+=a.node.OuterXml;
}
aml+="</AML>";
// add the CI Orders to Innovator
adds=innov.applyAML(aml);
if (adds.isError()) {return adds;}
}
// find CI Orders in innovator not on remote system and delete them
sql= @"
delete from innovator.ci_order where id in
(select i.id
from innovator.ci_order i
right outer join dbo.fed_ci_order f
on f.ordno= i.ordno and f.wh=i.wh
inner join innovator.catalog_item ci
on i.source_id = ci.id and f.item_number=ci.item_number
where f.ordno is null)";
Item dels =innov.applySQL(sql);
if (dels.isError()) {return dels;}
// find CI Orders
Item pr_opn = this.newItem("CI Order");
pr_opn.setAttribute("select","qty_ord,qty_rcd,state");
pr_opn=pr_opn.apply("get");
if (pr_opn.isError()&&(!pr_opn.isEmpty())) {return pr_opn;}
// and promote them if necessary
for (int i=0;i<pr_opn.getItemCount();i++){
Item o = pr_opn.getItemByIndex(i);
if ((Int16.Parse(o.getProperty("qty_rcd","0")) <
Int16.Parse(o.getProperty("qty_ord","0"))) &&
o.getProperty("state")=="Closed"){
Item res =pr_opn.getItemByIndex(i).promote("Open","");
if (res.isError()) return res;
}
if ((Int16.Parse(o.getProperty("qty_rcd","0")) >=
Int16.Parse(o.getProperty("qty_ord","0"))) &&
o.getProperty("state")=="Open"){
Item res =pr_opn.getItemByIndex(i).promote("Closed","");
if (res.isError()) return res;
}
}
return innov.newResult("OK");
<o:p> </o:p>