Reference Table Lookup Definitions and the Lookup() Function

Most synchronization solutions such as MIM leverage look-up or reference table data to translate codes and/or numeric values into corresponding string descriptions.  One example of their use is to translate HR codes (cost center, location, etc.) into a user-friendly string name for synchronization to downstream systems such as Active Directory or Entra ID.

These lookup values are usually stored in the same physical file/table with both lookup type and lookup key identifiers to facilitate both the retrieval of a unique lookup value for a supplied type/key, as well as retrieving the complete set for selection in a UI (e.g. in a drop-down list).

Sample Lookup Data

The following table is an example of a reference data collection that might be required in a typical identity synchronization solution to synchronize user-friendly names to AD attributes such as employeeType and physicalDeliveryOfficeName (office location):

Lookups in MIM

A typical MIM practice is to store lookup codes in either an XML lookup file (in the Extensions folder) or in a SQL database, whereby a .Net rules extension might be used to look up a name for a corresponding unique key.  Invariably a common issue when implementing such a design was the duplication of effort required to maintain these list values, e.g. in an XML config file as well as in one or more RCDC XML documents.

A SQL representation of the above would obviously be a single database table, whereas an XML lookup might have nodes that look something like this:

...
<Lookup ID="1" Type="EmployeeType" Key="E" Category="Employee" Value="Employee Full Time" />
...

Reference data as Silos

With Identity Panel, reference data is best loaded via an appropriate Provider into a single identity silo.

If this has been historically maintained in a SQL table, it may be tempting to use the SQL Database Provider, the downside of this is the need to maintain the values over time, even when they only change occasionally (e.g. a new office location comes online).  This overhead generally adds to the overall total cost of ownership of your Identity solution.

A better solution is to export the data from SQL in CSV format (with column headers) for loading into Microsoft Excel, and thereafter using the Excel Provider as follows:

• Initial creation of a Reference Data silo by adding a new Excel Provider as an Additional System within an Export Connections Provider, with the following options set ON:
  • Create Silo
  • Empty Strings are Null (noting that without this setting Excel will import empty cells as zero length strings!)
• Initial upload of data via the provider's Upload Excel File link (data will be immediately searchable upon browser refresh)
• From this point consider the values present in the Reference Data silo as authoritative
• Download of data to a temporary Excel file for making changes via the Download Current Data link, and subsequent re-upload

The following is how such a provider would look when configured for the above table:

HyperSync Lookups

Identity Panel provides a special function for interpreting Reference Data stored as a silo in the above fashion.  It will also work when the same data is loaded via any other data provider, provided the schema is consistent with the above approach (see notes below).

This appears in the Identity Panel Rule Helper as follows:

• Lookup(lookupName, search{})
  Returns graph nodes based on the designated lookup definition and search parameters.
  Matches on case-insensitive string value. If target attribute has value 'default' it will be included with lower priority. Sample Usage: Lookup("Name", attr1="val", attr2="other").lookupAttr Lookup("Name", attr1="val", attr2="other").0.lookupAttr Matches: { attr1: val, attr2: other } or { attr1: val, attr2: default }

The Lookup() function has the following characteristics:

• The first parameter is the name of the definition (which maps to a silo and object type containing the data to search)
• The second parameter is a params dictionary containing at least one matching attribute (may also match DN)
• All matches are made as case-insensitive string comparisons, null matches null or “default” (reserved word)
• Specify fallback entries in the template data that match anything, using the case-insensitive keyword “default” in the silo data.
• Will return all matches, with the more specific matches returned at the top of the list (i.e. default matches are lower priority)
• Match progressively against multiple attributes. Attributes listed first in the function parameters have higher weight for result priority
• Indexing into the result with an attribute name, Lookup(…).emailSuffix is equivalent to Lookup(…).0.emailSuffix
• List processing functions (Map, Filter, Find, SortBy, etc.) may be used with a Lookup(…) result
• Lookup data is cached in the sync’s data load phase, so it does not incur a database penalty per call (unlike LookupObject(…) for example).
• There is no need to build a custom form to maintain the data - just use Excel!

Notes:

• The Lookup() function is only available in the context of HyperSync or Access Panel synchronization.
• There may be circumstances where an alternative to the Excel provider should be considered.  This includes (but is not limited to)
  • if the number of records in Excel exceeds say 10,000 then a memory/performance impact can be expected
  • if the dataset includes records which are more volatile
  • if the data is maintained as part of BAU operations by a separate team
• For more info on the use of the Excel Provider see Q&A: How to perform Bulk Actions on Accounts