Whether it be for reconciling unit price information from a custodian, mapping a trial balance to a standard set of accounts or combining information from legacy systems with data collected from customers, the integration of data has become an integral element of day-to-day business activity.
The information you need rarely sits in one place; rather it must be extracted from multiple systems such as the general ledger, a product system, a data warehouse or somebody else’s spreadsheet. In addition, external information, such as that provided by customers, clients or business partners may supplement the information collected internally. And the volume of data is growing every day:
Identifying further data sources for analysis and consumption is easy enough. The challenge, rather, is in integrating multiple data sources in a way that renders the information useful and retains a level of data quality.
Complex and tedious manual processes are often created for staff to extract and “map” sets of data together on keys or identifiers (such as account codes). A favourite for many people is to use the “VLOOKUP” formula in Excel to look up items across two sets of data. These tasks tend to require the collection and preparation of the various data sources (e.g. identifying a “key”), followed by “connecting” the data and an effort to identify and act on anomalies (e.g. a new account code in a trial balance):
A key challenge for many organisations is the time consumed by the manual integration of data on a repetitive basis (e.g. for a month end report). In addition, the intricate details of how the data is to be connected, and how to deal with specific anomalies, are often tucked away in the minds of the individuals responsible for the work. This knowledge tends to be lost when those individuals leave the organisation or are away on leave, leading to a degradation in the quality of the process, and hence the results, over time.
How we help
The need to combine multiple sources of data is a fundamental reason for using SolveXia.
Rather than forcing you to fit your processes to someone else’s methodology, you can seamlessly automate the manual “steps” that you follow when to combine your various data sources. You therefore continue to understand, own and manage your data integration processes. However, these processes can now be completed in a fraction of the time through the use of industrial strength technology to carry out the steps, rather than requiring manual effort each day, week or month.
Users have the ability to add “placeholders” for each data source – as many as needed. The software works with CSV, Excel, Access, Word and other file formats and allows for individual data parameters such as Text, Numbers and Dates so all of your data sources and process parameters can be incorporated.
Specific and purpose-built “robots” can then be added to automate the “steps” to integrate your data. By way of example, a robots such as that to “Extract Text and Copy” will find and extract any text that conforms to a specified pattern (such as a four digits to represent a client code). Another example is the “Match List” robot that will compare two sets of data and instantly provide a report of items in one list but not the other, and the “Map Data by Key” is a powerful robot that can map together two sets of data and identify and communicate unmapped items, all in one step. Combining these robots together allows you to automate the end-to-end process and produce consistent and meaningful reports such as a reconciliation:
Senior staff and process owners can rest easy in the knowledge that all of the information related to how the data is combined and integrated is captured and can be called upon in a moment’s notice using a “Process Specification Report” which details each step as well as a history of any changes made to the process or files.
Some examples include:
- Life Insurer – Reconciliation of General Ledger data with information from custodian to reconcile unit prices of funds
- Accounting practice – Collection and mapping of client trial balances to a common set of accounts allowing for streamlined and consolidated servicing (e.g. to prepare regulatory reports and business plans)
- Commercial Bank – Integration of data from different source systems housed in two separate entities (resulting from acquisition) to prepare margin lending credit dashboards for senior staff