When we were talking last week about Compliance Data Systems, I emphasized the fact that you need to prove that you are innocent. This is a very important concept to keep in mind, when designing your compliance system. In many cases, your company is doing the right thing.

Although fraud and malintent do exist, and we do need to control for it, most companies operate with all the good intentions. However honesty alone will not protect you in an audit. The rule of thumb to follow in any audit is:

Always keep this in mind. This is one of the big differentiators between a compliance data system and a traditional strategic intelligence system. Here are 4 key strategies for proving innocence with a compliance data system.

Look for data sources that contain scans of physical documents ( i.e. contracts, invoices, receipts, etc. ), and downstream from them. Physical documents, especially signed and dated physical documents, are a terrific form of proof. When your internal audit team is in the middle of an audit, and a specific document is requested, there’s nothing more powerful and impressive than to produce it right from your compliance system.

Maintaining a pointer of some sort to the document is not as good as having the physical document stored. Only use this strategy if there’s no other way. For instance, if your company does not currently scan your contracts, and has no interest in starting, then you may be forced to store a reference to the contract’s location instead of the actual contract.

Not only are document references less time efficient, they can get stale, which can really get you into trouble with your credibility. This can even happen with electronic pointers. Imagine storing a document reference that points to a website where the contract should be, then trying to navigate there, only to find a 404! I’ll bet you picked up on the fact that this scenario is not that far from reality!

The second best thing to physical evidence, is manufactured evidence. It’s perfectly okay to transform your way to evidence. This is done by combining different pieces of information, possibly from different sources, to prove an assertion.

For example, let’s say your company is trying to control the risk that in inexperienced processor will record bad operational data, so they set a control that a supervisor must be present whenever operational data is being collected. All personnel on the floor must clock in and clock out through a timecard system, however the timecard system only stores employee ids. To know that a supervisor is present, you have to merge this data with the HR system which contains pay grades and titles.

The combination of this data creates your evidence. You can use the timestamps from the timecard system, and merge that with the organizational structure maintained in the HR system, to get a comprehensive view of when your supervisors were present.

In this particular case, to protect your company, I would go a step further. I would create an exception table, which highlighted all the instances when there were processors on the floor with no supervisors. Then, you could fold this logic into a real-time system, that would catch the act as it happens, turning it into a prevention system. But now I’m getting a little carried away …

The point is, evidence can be manufactured by using the same techniques we use to transform data for strategic reasons. Of course, the transformation logic of any manufactured evidence can ( and will ) be challenged, so you also have to do your diligence to support your transformation assertions ( I may deal with this more later ).

Always, always, always … capture three pieces of information about everything; who, what, and when.

Follow the example set in large ERP systems like Oracle ERP. In just about every single table, you will see these four columns:

·         CREATED_BY

·         CREATION_DATE

·         LAST_MODIFIED_BY

·         LAST_MODIFIED_DATE

This information is crucial, and should be attached to any and all pieces of information. You will need to establish an audit trail, and this is the only way you can do it.

In systems like the above, you will notice that CREATED_BY and LAST_MODIFIED_BY are actually IDs to other tables. Denormalize this out in your compliance system. Ids will not help anybody in an audit, and it’s disruptive in the middle of an audit, to hunt down the real person behind employee id 83882. Also store contact information for these people, in a denormalized fashion. It’s good to have a contact name – it’s even better to have their extension handy.

You’ll also notice that there is no “what” in the above system. You should manufacture the “what” if you need to. The “what” that you are capturing, is some sort of action. If it needs to be inferred from the context, spell it out with your transformation.

For instance, if you have a RECEIPTS table that has CREATION_DATE and CREATED_BY, and you know that a row in this table means the receipt was created by this person, at this time, spell out the action in a new target. Your target table would have columns CREATION_DATE, CREATED_BY_NAME, CREATED_BY_EXT, and ACTION_PERFORMED. In the ACTION_PERFORMED column, you would store the value “Receipt Created”.

Also, because of the need to always capture this data, forget about Type 1 dimensions completely. If you’ll remember, a Type 1 dimensions is a dimension that does not maintain change data history at all. There’s no place for these in a compliance data system.

This is so important.

One of the extremely powerful things about any data warehousing environment, including a compliance data system, is that you can rewind time. Point-in-time history is absolutely vital in trying to understand how the state of the system got to where it is today, and proving that even though something looks funny now – everything was fine for the time period that’s being audited.

And, when dealing with your source systems, never take on faith, any assertions about their data and how it changes. You will hear statements like, “Oh, the data in that table is never deleted”, or “that’s just a static table – it never changes”.

Don’t buy it. Put your own change data capture system in place, and maintain the history in your compliance system. That way you know for sure what has changed, and when it changed. It’s funny how often something “magically” deletes from a table that should never be deleted from.

Proving your company’s innocence is one of the foremost goals of a compliance data system. There are four key strategies you can employ, to accomplish this goal:

·         Store images of physical documents

·         Create evidence through transformations if necessary

·         Always record who, when, and what

·         Always maintain change data capture from all your sources

Strive to have all four strategies employed in your compliance data system, but for starters do an inventory of systems that capture scans of important documents, and start working this data into your system.

0 comment(s) so far...

Login to Add Your Comment