May 8

Written by: JohnWeathington
Thursday, May 08, 2008 

Should your compliance data system be read only, or should you be able to write back? I’ve often compared compliance data systems to data warehouses, because they downstream transactional systems. So the generally accepted axiom of data warehouse gurus is, “A data warehouse is a read-only system.”  What about a compliance data system?

Well, just like everything else, it depends. In general however, when approaching your requirements for building a compliance data system, you will most likely need to fulfill the functional requirement for end users to add compliance specific data to your system that is not currently present in the transactional systems. So unless you have a very robust transactional environment, or very simple compliance requirements that can be managed from the existing transactional data, plan to include a way to provide user input to your new data system.

I’ll give you a good example. In a recent GSA effort I was in, we designed a compliance data system that included “user maps”. We needed to classify customers based on the type of agreement they signed with the company. The input was the customer’s agreement type ( which was captured in the transactional system ), and the output was the GSA customer type ( nowhere to be found in any transactional system ). To make the translation, the users maintained “maps” that would make the translation from agreement type and GSA customer type. They update these maps every month, and resubmit them to the system, just before the new month’s data is processed. When we designed the system, we had to allow for the functionality of handling these maps; how they were uploaded, how they were processed into the system, and how change data capture was maintained.

This is not uncommon. The options we had were as follows:

• Design the functionality to reside within the system ( which we did )
• Appeal to the keepers of the transactional system, to hold and maintain this functionality
• Create a new transactional system, purely for the purpose of compliance
• Create a new transactional system that primarily served another function, but also covered our functional requirements

All four options are good if your organization supports it; each with its pros and cons. Unfortunately for us, none of the other options would have worked. Those processes were already infested with bureaucracy and over-bloated release management, so we would have never been able to get things done correctly in time.

Here are some common reasons why your end users will need to submit supplemental data:

• As noted above, to categorize or “map” existing transactional data
• To acknowledge authorization or approval of something
• To submit hard-copy evidence, or links to hard-copy evidence
• To provide explanatory notes on existing transactional activity
• To provide explanatory notes on summary or reconciliation activities that happen within the system

Most data warehouse professionals don’t consider requirements for putting data into the data warehouse, so here are some tips when designing the system:

Tip # 1 : Pay Attention to Denormalization.

Data warehouse professionals can be somewhat reckless when it comes to this, because we don’t need to worry about “foreign interfaces”, however whenever you have users maintaining data, you must be careful. I’m not suggesting that you go all the way to 3^rd normal form, but it’s not a bad idea to follow the advice of your transactional system designers. If users are allowed to maintain data that violates normalization rules, then you need to scan your data for integrity violations, and notify the user during the upload process.

Tip # 2 : Watch your Leading and Trailing Spaces.

Leading and trailing spaces can kill you when you’re trying to process user data. My attitude in the beginning was, “Garbage in, garbage out. Let the user enter in anything they want.” That quickly changed after about the 3^rd post-Midnight troubleshooting session on why things weren’t matching up.

Tip # 3 : Make it Easy for the User To Make Updates.

In my “user map” scenario above, I created two sets of reports. One set was for viewing what was already there. Then I created one set of reports that was purely for maintenance. Every month, the end users had to maintain these maps, so I created reports that would highlight entries that needed to be added. For instance, knowing that each agreement type needed a customer type, I would scan the transactional database for “new” agreement types, and highlight them in an exception report, so the end users could attach the appropriate customer type.

Tip # 4 : Handle your Change Data Capture.

Make sure you handle change data capture, even if the user doesn’t request it. Change data capture is extremely important in any compliance data system. You should never, never, never delete, or overwrite anything – never! It’s important that you capture what changes were made to the user input, and when they were made. All of your reports will be time-sensitive ( there’s no way around this ), so you need to absolutely know for sure what the user input looked like when the report was generated.

In summary, although data warehouses strictly prohibit write-backs to the database, it’s actually a pretty common thing for compliance data systems. There are several reasons for this including the ability to maintain “user maps”, or the ability to provide notes and approvals to supplement transactional data. Start thinking about the design of your input system today. Make sure to pay attention to denormalization, leading and trailing spaces, and change data capture. Also make sure to make it easy for your user to update and maintain the value-added data.