May 1

Written by: JohnWeathington
Thursday, May 01, 2008 

For the last couple of weeks, we’ve been talking about common controls that you will see in the mitigation of risks at your company. First we talked about reconciliation, then last week we addressed approvals. This week we’ll wrap up this series, by talking about “SOD.”

No, I’m not suggesting that you install a new lawn. SOD stands for Segregation of Duties ( or sometimes it’s referred to as Separation of Duties ). It’s not an acronym, so you’ll never hear it pronounced like the word “sod”. However, it’s commonly shortened to its initials – S. O. D.

Essentially what SOD means, is that you want separate duties or activities in a chain of process events, physically executed by different people. This is not an approval control, so it’s not about somebody approving or checking somebody else’s work. Usually the different people in the process flow are peers.

For example, your company might require that the person receiving money be different than the person depositing the money. Or, that the person approving a request for access not be the same person that is actually requesting the access ( believe me, I’ve seen it! )

This type of control has a few qualities to it:

• The implementation of the control is something you typically only see in a compliance context. Reconciliation and approvals serve many types of purposes, but SOD is generally only applied in a compliance domain.
• This is an attempt to control a more narrow set of risks. In almost all cases, SOD is trying to prevent unethical behavior by removing the temptation.
• Because of the nature of the control, SOD requires more thought and planning, and will probably be more intrusive in its implementation. I’ve seen entire databases created solely for the purpose of SOD, and process designers spend hours upon hours trying to get things mapped out and documented correctly.

Here are some key tips for designing SOD into your compliance data system.

Tip # 1 – Downstream the Correct Data From Your HR Database

This is the same tip I gave you last week on approvals. You will need the same set of data. To reiterate, here’s the data that you need from your HR Database. I’m going to use Oracle ERPish column names in case they are familiar to you, however you should be able to translate if you’re not using Oracle ERP:

• USER_ID – You need some way to link in your employee to your transactional data. For obvious reasons, you need to bring this in.
• EMPLOYEE_ID –This is only optional in very small companies or in companies where the EMPLOYEE_ID and USER_ID is the same. Employee ID is important to distinguish different people that have the same name.
• EMPLOYEE_NAME – Duh. Make sure you bring in the full name of the employee. You do not want to be in the middle of an audit, trying to decipher what “JAMESF” means.
• PHONE – The employee’s phone extension. Very handy when you need on the spot answers.
• EMAIL – The employee’s email address. Once again, if the employee doesn’t answer the phone, it’s nice to be able to shoot off a quick email. These kinds of things demonstrate to the auditor that you are running an efficient compliance operation.

Once again, I suggest you denormalize this information out to every data point that references an employee. In a star schema, this will be tricky if your process is evolving, but your best design is to have a separate dimension ( could be virtual ) for each step in the process. Your fact would be at the grain of the process instance, and the person who executed each step of the process would be referenced out to a separate dimension. For instance, you would have a “RECEIVED BY” dimension, and a “DEPOSITED BY” dimension.

The other option would be to build a fact at the grain of the process step, and just have one “EXECUTED BY” option. I don’t like this approach, because in the reporting you are going to be required to put different rows of the fact into one logical row, and do comparisons. Unless you have some really good reporting tools, this is going to be a mess.

Tip # 2 – Solve for the Status of Compliance in the Database

It may be tempting to stop the design at merely capturing the data. You need to go the extra mile, and design in the transformations that highlight the actual status of the compliance at any point in time. What I mean by that is this. You should have transformations that physically analyze the people who have executed the different steps of the process, and determine at load time whether or not there was a compliance violation.

To do this, you need to understand the policy, and not merely the players involved in the process. To illustrate, let’s use the example above of a policy that states that you cannot have the same person who is receiving the money, deposit the money. You might build a table that includes the following columns:

• PROCESS_ID
• PROCESS_INSTANCE_ID
• PROCESS_DESCRIPTION
• RECEIVED_DATE
• RECEIVED_BY
• DEPOSIT_DATE
• DEPOSITED_BY
• FLAG_SOD_COMPLIANT

Your load routine would need to compare RECEIVED_BY and DEPOSITED_BY to determine if they were the same person, then populate the FLAG_SOD_COMPLIANT with a ‘Y’ or ‘N’ based on the findings. In this way, the reporting can highlight out of compliant situations with a simple filter on FLAG_SOD_COMPLIANT.

Tip # 3 – Consider a Separate Subsystem for SOD, and Keep the Design Flexible

As mentioned above, building in SOD controls requires more focus and attention. It is worthwhile to segment this area of controls off in its own area. It’s not uncommon to see a separate loading area, operational data store, data warehouse, and data marts specifically built for the implementation of SOD.

Also, be prepared for a lot of changing requirements. The evolution of SOD control development is usually somewhat chaotic. The business will be experimenting with a lot of options before they get comfortable with an optimal solution. If you have the skills and resources, I would recommend architecting in an agile fashion, so that changing requirements don’t become a headache. Otherwise, I would hold off for a while until requirements get firmed up a bit, and buy a lot of Aspirin.

To summarize, when building your compliance data system, you will invariable be faced with the concept of SOD, which stands for Segregation of Duties. In short SOD controls are trying to mitigate the risk of unethical human behavior, by forcing different steps of a process to be executed by different people. Making sure you downstream the correct data from your HR database and solving for SOD compliant status in the database are critical to your success. It may also be worthwhile to segment off the entire function of SOD, as these types of controls will require more diligence than some of the others. Start today by reviewing your SOD requirements with your internal auditors, and suggesting a new SOD management system to the executives.