How can business data integrity be ensured?
Efficiency question :
Data integrity is essential to the proper functioning of an organization. Incorrect data can create costs, cause discrepancies in reports and, most importantly, lead to poor decisions.
Ensuring data integrity therefore means putting several control measures in place, from data entry or retrieval through to its use in reports. The objective is to make sure the figures produced truly reflect the organization’s data.
Key considerations:
The measures required to ensure data integrity vary considerably from one organization to another. Data may come from several systems, be entered by different users or be retrieved from different sources.
There is therefore no single method that applies to every situation. Control mechanisms must be adapted to the origin of the data, how it is used and the organization’s own processes.
Challenges:
Users do not all have the same level of skill or experience. A first challenge is therefore to control data quality upstream, before the data is integrated into systems or used in reports.
Mechanisms such as drop-down lists and controls on entered data can better structure information at the point of entry. These controls must nevertheless be adapted to the organization’s specific needs.
Another measure is to produce control reports that compare different data sources and different reports in order to identify potential discrepancies. For example, when financial data from several organizations is combined to produce compiled financial statements, a control report can verify that the total data from the different sources matches the result obtained after compilation.
Technology:
The technologies used depend on the data, systems and controls that need to be implemented. Microsoft Excel can be used to integrate validation mechanisms, create control reports and automate certain checks.
VBA and macros can also automate report production. The formulas required for calculations can therefore be built into the automated process rather than relying on formulas that could be modified or overwritten by a user.
Developer's advice:
Data integrity often depends on an accumulation of small control measures rather than on a single verification.
When creating a report, automatically add its production date in the footer. This makes it possible to determine precisely when the presented data was produced.
Also automate report and calculation creation when appropriate. In Excel, VBA and macros can integrate formulas into the automated process and reduce the risk that a formula will be overwritten or modified over time.
Finally, plan for control reports. Comparing source data with the results obtained after processing makes discrepancies easier to identify and provides an additional measure for preserving data integrity.