Multi-Value Tables for Database Management Agents
This topic explains how Microsoft® Forefront Identity Manager (FIM) 2010 R2 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 |
FIM 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:
All columns must contain only a single value.
More than one anchor columns are supported if the columns are of the same type.
Tables can contain an optional object type column that defines the object type for each row.
If a reference column is used, you must also use a single anchor column. In certain cases, to guarantee a unique object identifier, you must also create a separate column that contains an identifier (such as employeeID) with an additional column containing the object type, as shown in the previous examples.
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:
Anchor column or columns
Attribute name column
The secondary, multi-value table must also contain one or more of the following columns for their associated value types:
A string column
A numeric column
A binary column
A large string column
A large binary column
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.
| EmployeeID | ATTRIBUTE_NAME | STRING_VALUE |
|---|---|---|
E005 |
DIRECTS |
E001 |
E005 |
DIRECTS |
E003 |
E005 |
DIRECTS |
E004 |
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 FIM.
Note
Indexing a multi-value table can significantly improve performance.
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 See Also.
See Also
Concepts
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