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 Ivanti 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.

Was this article useful?    

The topic was:

Inaccurate

Incomplete

Not what I expected

Other