How Many Problems with Persistent Data Does a Unique Identifier Solve?

The answer is zero.  A unique identifier adds nothing to any logical problem we have with our data.  Let’s see why this is true.  I have two sets of data from different systems, which represent information or attributes about a real world user.  Those data elements are indistinguishable from each other.  Perhaps they are first name, last name, city and state.  They are identical as far as I can tell.  If I add a unique identifier do I know anything more about them?  I just know they are no longer identical and yet they may be in the real world the same person. By adding a unique identifier I may have made a distinction, which is false.  It’s impact will only be deleterious never beneficial.  The unique identifier becomes ornamental.  Metaphorically it is like placing a medallion around the neck of the famous twins and still not knowing if it’s Tweedledee or Tweedledum.  At least in this case I could re-name them to something like Dee and Notdee, which would be meaningful to an observer.  However, in the foregoing example, we are dealing already with a representation of an entity and it adds nothing. Now let’s add several more attributes, for example, title and department.  If I can now distinguish easily whether they are the same person or not I have accomplished my goal and I still have not added a unique identifier.  The smallest subset of elements that distinguishes one set from another is a suitable key if the data is in a database and I still haven’t added a unique identifier.  So then how are unique identifier’s useful?  They are useful within a context in which we are programmatically creating many closely similar but not identical objects whose existence is ephemeral.  When we are combining data from many different contexts, they solve nothing; they are just another attribute.

Advertisements

HCM and NetWeaver Identity Management Integration Tips

The landscape document from SAP that explains how to export from HCM to VDS to Identity Center has sections that are less than clear so I thought I would list common issues that have caused problems in the past.  First the architecture.  The way the export works is as follows:

  1. A report is run in SAP HCM which extracts the necessary data formated as LDAP data.
  2. SAP connects to the VDS and pushes the data.
  3. VDS connects to the Identity Center information store and uploads the data.

A couple common problems I have seen.

  • The field names inside SAP are misnamed or the export names to LDAP are.
  • The LDAP libraries in SAP Basis are not installed.
  • VDS Template:  The one you want to use is this one “HR Export to IdM Identity Center.xml” this one will not work “HCM LDAP EXTRACT for IDM.xml”
  • Bad credentials or passwords (of course)
  • VDS Tree for HCM is broken in some way.  If in doubt recreate your setting from the template.

Troubleshooting Tips.

  • First determine where you are broken.
  • Turn on verbose logging at VDS and see if HCM is even connecting.
  • If you are connecting to VDS but no data is reaching the Identity Store then check the LDAP extract for misspellings.   One error in your path and the whole thing breaks.
  • If VDS shows database errors then check the error logs in the identity center for problems with the task configuration

Finally, because HCM does not support event triggers — which can be tricky — I usually filter at HCM LDAP report for the data I want.  In most cases a nightly run is sufficient.  SAP recommends a full upload every time but this is not practical for large numbers of employees.

MSKEYs and MSKEYVALUEs, Views and Tables

It does not take long before the NW IDM Administrator starts peeking under the hood to see what sits in the database. This is not a bad thing since there is a lot of good information that one can gain here, especially in the way of analytics and audit data. Understanding the database structure is also important in terms of developing and troubleshooting access rules and other Identity Store SELECT criteria. This posting will talk about a couple of key characteristics of the NW IDM Identity Store database. One of the great things about NW IDM is that there are virtually no differences between the Oracle and MS SQL database schemas so this article works for both environments.

This article will be one of several in a series covering the NW IDM Database structure.  We would be very interested in hearing about other areas of the back end that should be discussed.

When looking at these tables one of the first things that people notice is that there is seemingly no direct reference to the MSKEYVALUE attribute and that there is a value for something called an MSKEY. Consider this extract from a sample Identity Store regarding the user Johnny Scooter:

mskey attr_id aValue SearchValue
187 2 JScooter JSCOOTER
187 1 MX_USER MX_USER
187 4 Johnny Scooter JOHNNY SCOOTER
187 36 Johnny JOHNNY
187 37 732 123 4567 732 123 4567
187 39 Scooter SCOOTER
187 40 Information Systems INFORMATION SYSTEMS
187 41 732 765 4321 732 765 4321
187 42 Systems Engineer SYSTEMS ENGINEER
187 55 CN=JScooter,OU=IdM,DC=nwidm,DC=local CN=JSCOOTER,OU=IDM,DC=NWIDM,DC=LOCAL
187 49 Johnny.Scooter@nwidm.local JOHNNY.SCOOTER@NWIDM.LOCAL

This is a snapshot from the MXI_VALUES table listing four columns, mskey, attr_id, aValue and SearchValue. (For a complete listing of the NW IDM Identity store schema take a look at this document.) The aValue and SearchValue show the attributes’ values in the form that they were entered into the system and a globally consistent format for easy searching respectively. So going back to our question from above, where’s the MSKEYVALUE. If we look in MXI_ATTRIBUTES we’ll find:

Attr_ID AttrName
2 MSKEYVALUE
1 MX_ENTRYTYPE
4 DISPLAYNAME
36 MX_FIRSTNAME
37 MX_PHONE_ADDITIONAL
39 MX_LASTNAME
40 MX_DEPARTMENT
41 MX_PHONE_PRIMARY
42 MX_TITLE
55 MX_MAIL_PRIMARY
49 AD_DN

So this answers the first question, how do you match up those Attr_ID’s anyway as there is a direct link between the two tables via the attr_id column. The second and more important question is that a value of “2” for Attr_ID corresponds to MSKEYVALUE. So what is going on here, anyway? To begin; let us look at an excerpt from the view MXIV_SENTRIES about our friend Johnny Scooter, which is a separate, friendlier representation of MXI_VALUES since there’s more name information rather than value references.

mskey AttrName aValue SearchValue
187 MSKEYVALUE JScooter JSCOOTER
187 MX_ENTRYTYPE MX_USER MX_USER
187 DISPLAYNAME Johnny Scooter JOHNNY SCOOTER
187 MX_FIRSTNAME Johnny JOHNNY
187 MX_PHONE_ADDITIONAL 732 123 4567 732 123 4567
187 MX_LASTNAME Scooter SCOOTER
187 MX_DEPARTMENT Information Systems INFORMATION SYSTEMS
187 MX_PHONE_PRIMARY 732 765 4321 732 765 4321
187 MX_TITLE Systems Engineer SYSTEMS ENGINEER
187 AD_DN cn= JScooter,OU=IdM,DC=nwidm,DC=local CN= JSCOOTER, JSCOOTER,OU=IDM,DC=NWIDM,DC=LOCAL
187 MX_MAIL_PRIMARY Johnny.Scooter@nwidm.local JOHNNY.SCOOTER@NWIDM.LOCAL

Basically, there are two identifiers that are in use by NW IDM, the first is the more publicly known MSKEYVALUE. This unique identifier is exposed via the Workflow User Interface and is easily seen in MXIV_SENTRIES. However when we look at MXI_VALUES we see that the references to MSKEYVALUE are harder to root out as we need to go through the Attributes table. We also see a reference to the MSKEY field, which appears to be an identifier in both the MXIV_SENTRIES view and in the MXI_VALUES table. This tells us that NW IDM uses the MSKEY field as an internal unique Identifier. Why is this? I would say that the most basic reason is that the MSKEYVALUE is subject to change and would thus be ineligible for use as a link between tables. Even though we have only spoken about user identities, all entries in the Identity store (users, roles, privileges, entry types) have both MSKEYs and MSKEYVALUES. Therefore, if the MSKEYVALUE were the only identifier it would be a poor choice for linking between tables as a foreign key link as referential integrity issues would soon arise.

So to wrap up, what can we conclude from all of this?

  • MSKEYVALUE is the unique identifier used by NW IDM at the workflow layer and is changeable
  • MSKEY is the internal unique identifier and is not changeable. It is also used as a foreign key link between various NW IDM tables
  • MXI_VALUES is an NW IDM table holding information about all of the objects in NW IDM
  • MXIV_SENTRIES is a NW IDM View that holds a “friendlier” representation of MXI_VALUES

A Deeper Look Into the Database

Unlike some identity management solutions Netweaver Identity Management uses a SQL database instead of LDAP directory using a database storage engine.  There are distinct advantages to this. For one you can subsume a hierarchy in SQL database but you can’t do set relations in LDAP. The layout of the database can be somewhat confusing so I thought I would pick the most used view and discuss its composition.  Nearly every job and task are run against views and there are many in the backend database.  Most the work is performed against a view called mxiv_sentries.   Before we dig deeper into its composition, it pays to review some of the flaws of SQL which are maddening:

  • NULL values are permitted and it seems that everyone implements it differently.  This flaw cannot be bypassed.  Try to avoid filling your database up with NULL values.
  • Duplicate Rows  are permitted, you will type SELECT DISTINCT many times.
  • Duplicate Column Names are tolerated.  Be careful naming temp tables and using AS,  for example SELECT Col1 AS Name, Col2 AS Name.

There are others you can search them out on the web.  Returning to  the view mxiv_sentries.  As previously  stated this is the most commonly used view, it is denormalized and at first rather confusing to look at.   You will find yourself doing many subselects to extract the information you need.  The diagram below shows you which table columns are used to create the view.

mxiv_sentries3

The columns with which you will work with the most are MSKEY, AttrName,  aValue, and SearchValue.  MSKEY is primary key which is generated by the database.  It changes across environments. AttrName is the name of the attributes that you either create or import;  aValue stores the attribute values, for example, the MSKEYVALUE.  SearchValue is aValue in all caps.  The table below shows what we have learned from analysis since the internals of the database are not published.  If you see an error or would like to add something send us a comment.

mxiv_sentries_col3

Loading image

Click anywhere to cancel

Image unavailable