How to Calculate Manager Reference Value from PeopleSoft SQL View

The recent TechNet Wiki article A Practical Alternative to the PeopleSoft MA showed you how to pull data directly from a SQL view for this ERP system.  This article builds upon this prior work, shows how to calculate an employee's manager based upon standard PeopleSoft HR data and flow into Active Directory all via FIM.

A Brief Overview of PeopleSoft HR

PeopleSoft HR is typically referred to an HCM system.  It is used to onboard new employees as well as handle transfers along with leavers from the company.  The PeopleSoft HR database is vast and contains nearly 5,000 different tables.  For the sake of clarity and simplicity, this article will not cover how to design a SQL query to pull PeopleSoft employee data.  Unless you are a PeopleSoft DBA/developer you should engage the local SMEs at your company or job site to accomplish this task.  A key takeaway from this article is knowing what to ask for from the PeopleSoft HR team.

As mentioned in this wiki article, if you want to use PeopleSoft as the system of record to trigger new account provisioning/terminations by FIM then you need to request a SQL view that holds unique employee objects.  The employeeID is the primary key in most PeopleSoft implementations.  Additionally, PeopleSoft holds other employee data such job title, location and office phone just to name a few.

In order to accomplish the primary goal of this article, the PeopleSoft employee data view needs to contain two other fields to calculate an employee's manager.  Those two fields are:

1. PositionID - This is the unique job ID of the employee in question.  For example, the Director of IT, Mary Jones, has a PositionID of 1234 to identify her unique job in the organization. Unique sometimes doesn't mean unique in this case since some people might share jobs or have multiple positions in the organization.  Keep this in mind through the rest of this article.
2. ReportsToID - This is the PositionID of the employee's direct supervisor.  To keep with our example John Smith reports to the Director of IT whose position ID is 1234.  We will use this information in FIM to calculate the DN of the manager.  

In summary, our data appears in the PeopleSoft MA as follows.  The column Desired ManagerDN means this data is not in the CS but this is what we would like to calculate it to be.

With this information, we have enough to line up our solution.

FIM Method 1

If you stick with the SQL view method as the source of your PeopleSoft data, the next step is to create two custom metaverse attributes that will hold the PositionID and ReportsToID for the given employee. Flow these values directly into the metaverse from your PeopleSoft SQL MA.  Please note, these two attributes should be indexed in the FIM metaverse to optimize performance.

Your next step is to join your Active Directory data to the metaverse entry where your PeopleSoft employee resides.  Ideally, your Active Directory user objects will have data that will allow them to be easily joined to their matching PeopleSoft counterpart.  Placing the PeopleSoft employeeID in the AD employeeID attribute of the AD user object is the preferred way to create a strong match.  If this data, or a similar key, is not present then you will have a lot of cleanup work ahead of you to create a strong join.

So let's carry this forward and assume that we have a good match between AD and PeopleSoft.  The MV entries would appear as follows:

The goal is to now get the DN of John Smiths manager, who is Mary Jones, into the manager field of John's AD user object.  One option is to use a method demonstrated in Shawn Rabourn's blog post.

The solution requires you to create a rule extension project for your Active Directory management agent and then create a switch for the following code for the export flow rule. Our code would look like the following:

//Start manager lookup
if (mventry[mvReportsToField].IsPresent)
{
    MVEntry[] mveManager;
    mveManager = Utils.FindMVEntries(mvPositionIDField, mventry[mvReportsToField].Value);
  
    //performs a search
    if (mveManager.Length == 1)//if we get only one return (which we should)
    {
        if (mveManager[0][mvDN].IsPresent)//if there is the DN on that return
        {
            csentry["manager"].Value = mveManager[0][mvDN].Value; //set the DN as the manager
            break;
        }
        else
        {
            break;//dn may not be populated yet - it will occur on the next run
        }
    }
    else
    {
        break;
  
        //should never happen (employeeID is unique)- we'll fall through if it does
    }
}
else
{
    break;
}
//end manager lookup

The net result is the code above will look up the manager DN based on the ReportsToID of the person object in the MV.  It will then populate the manager DN of the user object in CS.  Export the pending change from FIM to AD and validate the expected results by checking Active Directory.

The downside of the method demonstrated above is that you could take a big performance hit in a large environment.  You need to test this thoroughly before you implement it anywhere and understand what impact if any this will have on your sync engine.

FIM Method 2

The other option is the query the FIM Sync database directly and find the value in question. Before you go down this road, you must understand that this method is not supported by Microsoft and you use this at your own risk.  Now that we have gotten that out of the way there are a couple of other recommendations for this method:

1. Use nolock or Set the transaction isolation level to read uncommitted prior to running your query
2. Encapsulate the query in a SQL stored procedure, keep it short and do not use cursors
3. Never update the data in the FIMSync database via SQL unless you want to blow yourself up
4. Test this thoroughly before promoting to production

Your SQL to find the manager DN in the FIM MV based on the ReportsToID would be the following:

use [FIMSynchronizationService];

select acmeCorpDN from [dbo].[mms_metaverse] with (nolock) where object_type='person' and acmeCorpPostionNumber='myPositionNumber?

As mentioned prior, the aforementioned code should be placed in a stored procedure and called from a rule extension project for your target Active Directory management agent to calculate the manager in DN.  So your full SQL call in C# would be the following:

//To get the manager DN query by the reportsToID value from the user object in question
public string   GetManagerDN(string  ReportsToNumber)
            {
            string managerDN = System.String.Empty;
            //Place this in a stored procedure
            string FIMDBquery = "use [FIMSynchronizationService]; acmeCorpDN from [dbo].[mms_metaverse] with (nolock) where object_type='person' and acmeCorpPostionNumber='" + ReportsToNumber+ "'";
            string targetConn =mySQLConnectionString; 
//Add your own SQL conn string here or in global var
            SqlConnection FIMSyncDBconn = new   SqlConnection(targetConn);
            SqlCommand cmd = new   SqlCommand(FIMDBquery, FIMSyncDBconn);
            try
                {
                FIMSyncDBconn.Open();  //Open FIMSync SQL database 
                cmd.CommandType = System.Data.CommandType.Text;  //Set query type
                managerDN = (string)cmd.ExecuteScalar();  //make the call and get results
                }
            catch (Exception e) //Catch any problems
            {
                throw  e;  //Add your own custom logging to the Windows Events Log
            }            
            finally
            { 
                //Clean up your SQL connection and close it up
                if  (null  != cmd)
                {
                    cmd.Dispose();
                    cmd = null;
                }
                  
                if  (null  != FIMSyncDBconn)
                {
                    FIMSyncDBconn.Dispose();
                    FIMSyncDBconn = null;
                }
            }
            return managerDN; 
            //Return the DN
    }

Finally, in the MapAttributesForExportmethod of your AD rule extension project, wire this function up to the manager value in AD.  Pass in the 'ReportsTo' value from the user object in question to find their manager?s DN in FIM Sync SQL.

void IMASynchronization.MapAttributesForExport (string FlowRuleName, MVEntry mventry, CSEntry csentry)
    {
        try
        {
            switch (FlowRuleName)
            {
                case "FindManager":
                if  (mventry[mvReportsToField].IsPresent) 
                {
                    string managerDN=GetManagerDN(mventry[mvReportsToField].Value); 
                  
                    //Call our SQL code from the prior example
                    if  (managerDN.Length > 0) 
                    //If value returned then up the manager value in the CS for export
                    {
                        csentry["manager"].Value = managerDN;
                    }
                }
            break;
        }
    }

Conclusion

The article demonstrated how to calculate the manager DN from PeopleSoft data and flow it into Active Directory.  It also discussed a couple of methods to do this with supported and unsupported code.  As always, test everything thoroughly before promoting to production and make sure you have good SQL backups!