Microsoft Identity Integration Server 2003 graphic

Delta views for database management agents

This topic explains how Microsoft Identity Integration Server 2003 works when it imports delta (changed) objects from a connected data source. Because users can have different database server types, such as Microsoft SQL Server, Oracle Database, or IBM DB2 Universal Database, it is the responsibility of the users to create a delta view on the connected data source by using the appropriate database programming language for their database type. To learn how to create views and populate them with data from a primary table, see your database documentation or consult your database administrator.

Database design for delta views

To import delta changes of objects in a database, you must provide a separate delta view of those changes. When you configure your management agent, on the Connect to Database page in Management Agent Designer, you can specify a primary table and a delta view. You can use the primary table for full import of all objects into a connector space. On subsequent delta import runs, you can use the delta view. This separate delta view must reside in the same database as the primary table that is selected for import. All exports are full export, and they do not require a separate view.

For example, you can select a table with the following user objects as the primary table. All objects are imported using a full import run.

Primary Table

EmployeeID LastName FirstName Title
E001 Doyle Patricia Sales
E002 DeVoe Michael Vice President
E003 Lysaker Jenny Sales
E004 Truelson Kim Sales

Configuring a delta view

User information can change regularly. It is inefficient to run a full import of the primary table each time a change occurs. It is necessary to import again (Insert, Modify, or Delete) only the objects that have changed. A user-written trigger on the primary table determines these changes and writes the changed objects to a secondary delta view. The delta view with the user objects that have changed is specified on the Connect to Database page in Management Agent Designer.

For a delta view, the following requirements must be met:

In the following example of a delta view, employee E001 has a new title, employee E003 has a last name change, employee E004 has left the company and can be removed, and employee E006 is new and needs to be added. No change has occurred for employee E002; therefore, no object for E002 exists in the delta view. The table in the previous example is specified as the primary table. This delta view has the following columns:

Delta View

EmployeeID LastName FirstName Title change_type
E001 Doyle Patricia Vice President Modify
E003 Lysaker-Diaz Jenny Sales Modify
E004 Truelson Kim Sales Delete
E006 Zeman Michael Sales Add

Configuring a delta view for multi-value attributes

To synchronize changes to objects with multi-value attributes, a primary table, a multi-value table, and a delta view are required. Triggers are required to detect changes to the multi-value table and to translate the change to a change type in the delta view. This delta view can contain both single-value attribute changes and multi-value attribute changes. For more information about configuring a multi-value table, see Related Topics.

In the following example, the primary table has one row containing a user object for department manager Steven Thorpe (employee E005) and one row for newly added employee Michael Zeman (E006). There is a secondary multi-value table that identifies the employees that Steve manages (DIRECTS) to which—in addition to the primary table—Michael Zeman (E006) will be added. Finally, there is a delta view that contains the object-level change that will occur for adding Michael Zeman (E006) and the attribute-level change that will occur to the object for Steven Thorpe for adding a new employee (E006) to those employees that he manages (DIRECTS).

Primary Table

EmployeeID OBJECT_TYPE LastName FirstName
E005 user Thorpe Steven
E006 user Zeman Michael

Multi-Value Table


This delta view has the following columns:

Delta View

E005 Thorpe Steven User DIRECTS Modify_Attribute
E006 Zeman Michael User Add

Related Topics


Multi-value tables for database management agents


Configure columns


Using the management agent for IBM DB2 Universal Database


Using the management agent for Microsoft SQL Server


Using the management agent for Oracle Database