23 June, 2014

FIM 2010 - SQL MA: Setting up multi-valued tables

Experience tells me that setting up the combination of a multi-value table and the FIM 2010 SQL MA calls for some extended documentation and walkthrough. The documentation on the FIM 2010 product once again excels in it’s level of (lack of) quality.

So – let’s cut to the bone straight away. I have a table that holds all my Staff, but with singular values only for all properties. Let’s for the sake of this walkthrough agree that I wish to provide all staff with the option of filling more than one job function at the same time. This calls for a multi valued field called JobFunction.

I wish to be allowed to model this situation in my database (source) as well as in my FIM 2010 MV (MetaVerse). Let’s start with the source system (the database) then move on the the FIM 2010 part of things.

External System: Database (source)

Below is the main table (left) and the secondary table (right). The left table holds all single value fields whereas the right will hold all fields on a (referenced) user that can take multiple values.

image                           image

The same tables are seen below with data.

image

image

The key to all of this modeling is that the secondary table needs to have 3 columns only. A reference [employeeID] to the staff in question (CA0000011), the name of the attribute [AttributeName] this row describes (JobFunction) and the value of this attribute [AttributeValue] (Janitor, Sales Rep…).

In more pragmatic terms the above means that the staff member (CA000011, John Carpenter) has 3 job functions namely Janitor, Sales Rep. and Teacher.

With this in place we can set up FIM 2010 to understand this model.

FIM 2010 (SYNC):

First of – we need to set up a field on the Person object in the metaverse to receive these incoming multi values. This fields I’ll call JobFunction and mark it as multivalued.

First – setup the user (Person) with a property of type string and multivalued.

image

Setup of the MA: the name of the table (tblStaffMultiValues) that holds the multivalue fields.

image

Press “Multi-value” on the Columns page.

image

Here is the key to all of this: The dropdown control should hold the NAME of the database table column that holds the property Name. In my case this is the AttributeName column (value = JobFunction).

Next is the column that holds the value. Only string types are supported so only one option there. This dropdown should hold the NAME of the database table column that holds the property Value. In my case this is the AttributeValue column (values = Janitor, Sales Rep…)

image

Finally – upon import and sync of data you should see a Person with a multivalue JobFunction as per below.

image

That’s how (easy?) it is to setup the multivalue thing on the FIM SQL MA.

2 comments:

mohammadali said...
This comment has been removed by the author.
mohammadali said...

Thanks a lot Claus :)
it was a great article and a great help :)

InRiver: Not loading your extensions?

(You really need to in the loop to appreciate the issue this post addresses). Man, I've been fighting this problem for hours before I ...