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:

The delta view must have all of the columns that exist in the primary table.
The delta view must have at least one additional column of string type. This is for the change type.
If the Configure attribute-level change type synchronization option is selected in Configure delta, in the Configure Columns page in Management Agent Designer, an additional string-type column is required for the delta view. This column contains the names of the attributes that have been modified.
The delta view must be constructed so that the rows are returned in the order in which entries were made into the delta view. This can be achieved, for example, by sorting based on a time stamp column.
After a delta import run, it is necessary to remove the rows from the delta view that have already been imported. If the rows that have already been imported are not removed, they will be imported again on subsequent delta runs, making the same changes that have already been applied.

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:

The EmployeeID column is required because it exists in the primary table. This column serves as a reference\anchor column, guranteeing uniqueness of each object.
The LastName column is required because it exists in the primary table. This column contains each user object's last name.
The FirstName column is required because it exists in the primary table. This column contains each user object's first name.
The Title column is required because it exists in the primary table. This column contains each user object's job title.
The change_type column is required because it is used to identify the change type operation for each delta object.

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

EmployeeID	ATTRIBUTE_NAME	STRING_VALUE
E005	DIRECTS	E001
E005	DIRECTS	E002
E005	DIRECTS	E003
E005	DIRECTS	E006

This delta view has the following columns:

The EmployeeID column is required because it exists in the primary table. This column serves as a reference\anchor column, guaranteeing uniqueness of each object.
The LastName column is required because it exists in the primary table. This column contains each user object's last name.
The FirstName column is required because it exists in the primary table. This column contains each user object's first name.
The OBJECT_TYPE column is required because it exists in the primary table. This column is used to identify the object type.
The ATTRIBUTE_NAME column is required only if the Enable attribute-level change type synchronization option is selected in Management Agent Designer. This column is used to identify a target attribute. This column can be left blank for object-level changes if the delta view will contain both attribute-level changes and object-level changes.
The CHANGE_TYPE column is required for the change type. This column maps a change that occurs in the primary table or multi-value table to a change type in the delta view. If the Enable attribute-level change type synchronization option is selected, this column can contain the Modify_Attribute change type for attribute-level change or an Add, Modify, or Delete change type for object-level change. If the Enable attribute-level change type synchronization option is not selected, this column can contain only Add, Modify, or Delete. You can configure the change type—if it is something other than the default Add, Modify, or Delete—in Management Agent Designer.

Delta View

EmployeeID	LastName	FirstName	OBJECT_TYPE	ATTRIBUTE_NAME	CHANGE_TYPE
E005	Thorpe	Steven	User	DIRECTS	Modify_Attribute
E006	Zeman	Michael	User		Add