Data crunching

There are a few fairly common situations where organisations might require additional data processing systems and/or services.

Custom reporting databases

Sometimes even the best enterprise system doesn't quite meet your needs. You might need a special report that the built-in reporting engine doesn't support or you might be using a field for a special purpose that means something to you, but not to the system.

You are not alone.

Many enterprise systems store your data in standard database engines, typically some variety of SQL server. Most of these can be accessed from other applications through a generic database connection. Life is easier if you have details of the database structure (tables, fields, relationships etc) but a good user-perspective knowledge of the system can be enough.

Take Care Crossing the Road

Before accessing your enterprise data through 'alternative' means like this, you need to take steps to ensure that you don't corrupt your live data.

If possible, use a read-only database connection that is unable to modify your live data.

Use a test server, or backup copy of your database rather than the live data (certainly during development and possibly always).

Ask your support people before you start - they may have useful information about the database and recommendations or limitations on what you can/should do. You may also incur additional support costs if something goes wrong and they have to recover your live data.

Data import and transformation

You might have some external system that produces information that you need to use in your main system; but it's never in quite the right format...

With a bit of coding it is usually possible to extract and transform data from different sources then export it in a form suitable for your target system. Depending on your specific situation, you may need to include data from other systems or write the imported data directly into them (after testing of course).

Sometimes the data has to go the other way - exported to a specific format that some other system requires. This might be to meet a customer or supplier requirement or for importing into another in-house system.


Some data-crunching solutions Dropbear Consulting has implemented include:

Reporting database

The client has a ERP system which meets most of their needs, but doesn't do many of the reports required by different departments and individuals. We developed a reporting database that uses data directly from the main system and defines a large set of additional reports that allows users to get the information they need, when they need it.

Order import process

Several clients have external systems that generate orders (from sales staff on the road or directly from customers). These order details need to be validated and reformatted for importing into their respective main systems.

Log Analysis

In order to analyse their mail traffic, a large multi-site organisation had previously been importing their mail logs into MS Access and running reports. Although this worked, it was horribly slow. A new mail server provided the incentive to redevelop this log analysis system in Perl, which reduced processing time from several hours to a few minutes.

For a different client, I developed a web-based system which provided a graphical analysis of traffic logs generated by two slightly different systems with consolidated output and interactive querying support.

Data Analysis

The client has a large case-oriented operation required detailed financial analysis of the transaction detail and case summary information with allocation rules not supported by the source system. This required a separate spreadsheet 'application' with a combination of formulae and macros to import the source data, perform the analysis and produce the required reports as well as a data export for re-importing into the source system.

PDF Reports

The client needs to produce PDF files of client statements each month. Their previous system worked, but was slow and could not be modified. In association with their existing support organisation, I developed a reporting application in Python which uses the data from their main system to produce PDF reports based on a simple description language.

Add comment

Security code