Using data modelling to successfully extract, transform, analyze and then correlate millions of data points. Systematically model data to enable a reconciliation between telemetry data and actual
client billing.
Shareholders of a large logistics company required a detailed audit of a companies systems and data to validate a number of concerns raised by whistle blowers.
The preamble of this audit was that no help would be provided by company staff and the data audit team needed to have sufficient understanding of the business
processes and toolsets common in the logistics industry (ERP, Scheduling, Temetery).
Shareholders of a large logistics company required a detailed audit of a companies systems and data to validate a number of concerns raised by whistle blowers.
The preamble of this audit was that no help would be provided by company staff and the data audit team needed to have sufficient understanding of the business
processes and toolsets common in the logistics industry (ERP, Scheduling, Temetery).
Problem
- A key issue on this project was that the client architecture consisted of
3 completely disparate systems. The finance system (SAP Business One)
had no logical connection to the activities in the inhouse trip booking
system (SQL). Finally – the data residing in telemetry history tables was
disparate and had no logical connection to that of the trip booking or
finance system. - The project revealed 15 valid audit points that were material in nature
and were tabled for follow-up and investigation
Solution
A once-off data extract was conducted from all three source systems (SAP
Business One, Bookings, Telemetery). Due to the number of tables in SAP,
a list of tables relevant to the audit was determined and then used to
reduce extraction time.
The raw tables were scripted and transformed into problem specific
data models to answer specific audit questions. Examples of the types of
models created include Sales & Invoicing, Movements, Income Statement
and Balance Sheet.
Outputs of the models depended on the problem set but this included
movement maps, pivoted reconciliations, heatmaps and change
movement charts.