Microsoft Identity Integration Server 2003 graphic

Multi-value tables for database management agents

This topic explains how Microsoft Identity Integration Server 2003 works when it imports objects from a connected data source that can have multiple values for an attribute. Because each user can have different database server types, such as Microsoft SQL Server, Oracle Database, or IBM DB2 Universal Database, the user must create a multi-value table on the connected data source by using the appropriate database programming language for the user's database type. To learn how to create tables and populate them with data from a primary table, see your database documentation or consult your database administrator.

Database design for multi-value attributes

In a common database, each row in the database represents a single object. For example, an Employees table in a Human Resources database may have one row for each employee in the company, with each column in that row having only one value for an attribute.

EmployeeID object_type LastName FirstName Title
E005 user Thorpe Steven Sales Manager

When an object in a single row has an attribute that can have more than one value, that attribute is referred to as a multi-value attribute. There is no standardized table configuration for objects in a single row that have multi-value attributes. For example, the following table combines each value of a multi-value attribute in a single column (Directs).

EmployeeID object_type LastName FirstName Title Directs
E005 user Thorpe Steven Sales Manager E001, E003, E004

Microsoft Identity Integration Server 2003 does not support this type of table, where multiple values exist in a single column. Because a single column for a single row can have more than one value, there is no guaranteed way of changing any one value. For example, if Sales employee E003 moves to a different department and will no longer report to Sales Manager E005, there is no way to guarantee that E003 is in any particular location among all the individual values in the Directs column.

One solution is to create a table with a row for each separate value for an object with multi-value attributes. For example, a single user object may exist in three rows, with each row containing the same columns of single values and a single instance of each of the three multiple values.

EmployeeID object_type LastName FirstName Title Directs
E005 user Thorpe Steven Sales Manager E001
E005 user Thorpe Steven Sales Manager E003
E005 user Thorpe Steven Sales Manager E004

This multiplication of a single object or row to contain only a single value of a multi-value attribute is inefficient, causing excessively large storage overhead and, potentially, diminished performance.

Creating a separate table for multi-value attributes

To import objects with multi-value attributes from a database, both a primary table and a secondary table for multi-value attributes must be specified. This separate table must reside in the same database as the primary table. When you configure your management agent, on the Connect to Database page of Management Agent Designer, you can specify both the primary table and the multi-value table. You use the primary table to import all objects into the connector space and export all objects from the connector space. You use the secondary, multi-value table to import and export the multi-value attributes.

The following requirements must be met for the primary table:

For example, a table with the following user objects can be selected as the primary table. The primary table must contain only single-value columns.

EmployeeID object_type LastName FirstName Title
E001 user Doyle Patricia Sales
E002 user DeVoe Michael Vice President
E003 user Lysaker Jenny Sales
E004 user Truelson Kim Sales
E005 user Thorpe Steven Sales Manager

An additional secondary table is required for those attributes that have multiple values. The secondary, multi-value table must contain the following columns:

The secondary, multi-value table must also contain one or more of the following columns for their associated value types:

In the following example of a secondary, multi-value table, user E005, Steven Thorpe, has three employees that report to him (referred to as Directs in the previous example). Each Direct is entered into the STRING_VALUE column as an individual row. The first column, EmployeeID, is an anchor column. The anchor column uniquely identifies each object in reference from the primary table. The second column, ATTRIBUTE_NAME, contains only an attribute name, configured as a multi-value attribute of string type. The third column, STRING_VALUE, is the column containing each individual value of the multi-value attribute.

This table, which contains only the required anchor attributes, reference attributes, and multi-value attributes, allows for the most scalability while maintaining optimum performance. This is the recommended configuration for use with Microsoft Identity Integration Server 2003.


Configuring the management agent in Management Agent Designer

When you configure a management agent for use with a multi-value table, you must select both a primary table and a secondary, multi-value table on the Connect to database type page in Management Agent Designer. You must also configure the additional columns on the Configure Columns page. For more information, see Configure columns.

When you run the management agent, an object that has attributes in both the primary table and the secondary, multi-value table are then consolidated as a single object in the connector space. Both import and export processes occur just as they do for any other management agent.

For information about how to import multi-value attributes that have changed using a delta table or view, see Related Topics.

Related Topics


Delta views 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