This is not the latest version of Identity Director documentation.
View available documentation.

Combine organizational data from different SQL database tables

If you want to use organizational data from an external Data Source to create a hierarchical organizational context in Identity Director, you need to synchronize the external data of only one Data Source. This is complicated if the data is stored in multiple database tables that have a hierarchical relationship.

This procedure describes how to configure a Data Source that unifies two SQL database tables with an SQL statement. This allows you to synchronize data from both SQL database tables in a hierarchical organizational context, instead of ending up with organizational context with no hierarchy. This example uses two database tables from the Microsoft AdventureWorks sample database. You can download this database, AdventureWorksDB.msi, at http://msftdbprodsamples.codeplex.com/releases/view/4004.

In this example, we synchronize organizational context from the database tables Person.StateProvince and Person.CountryRegion with one Data Source and one data connection.

Person.StateProvince

In the database table Person.StateProvince, each row in the table contains a reference to a country or region. It contains the following data:

StateProvinceID StateProvinceCode CountryRegionCode Name
1 AB CA Alberta
2 AK US Alaska
3 AL US Alabama
4 AR US Arkansas
5 AS AS American Samoa
6 AZ US Arizona
7 BC CA British Columbia
8 BY DE Bayern
9 CA US California
10 CO US Colorado


Person.CountryRegion

In the database table Person.CountryRegion, each row in the table contains a reference to a country. It contains the following data:

CountryRegionCode Name
AD Andorra
AE United Arab Emirates
AF Afghanistan
AG Antigua and Barbuda
AI Anguilla
AL Albania
AM Armenia
AN Netherlands Antilles
AO Angola
AQ Antarctica
... ...
US United States
... ...

Configuration

  1. In the Management Portal at Organization, create the organizational element Regions.
  2. In the Setup and Sync Tool at Data Model > Data Sources, create an ODBC data source Regions.
  3. In the ODBC Data Source window, click ODBC Data Source Administrator to create a data source for the AdventureWorks database.
  4. In the Data table field, select Custom > <SQL statement>.
  5. In the field that opens, type the following SQL statement:
    SELECT 'CR.' + CountryRegionCode AS RegionCode,
    Name AS RegionName,
    NULL AS ParentRegionCode
    FROM Person.CountryRegion
    UNION
    SELECT 'SP.'+ StateProvinceCode AS RegionCode,
    Name AS RegionName,
    'CR.' + CountryRegionCode AS ParentRegionCode
    FROM Person.StateProvince
  6. The data source should look like this:
  7. Click the Diagnostics tab to view the output of the SQL statement.
  8. In the Management Portal at Data Model > Data Connections, create the organization data connection Regions.
  9. In the Data Source field, select the data source Regions, which you created in the previous steps.
  10. Click the Mappings tab and configure it as follows:
  11. Click Synchronize. You can view the results in the Management Portal at Organization.