Recovering your data, even from legacy software: our ETL
Is your data spread across a modern SQL database, an old line-of-business application and various online tools? Dataspilot's ETL extracts it, cleans it and brings it together in one place, ready for your reports, your dashboards or a migration.
Article By Administration
In most SMEs, the useful data already exists: it is simply scattered. Some of it sits in the accounting software, some in an online CRM, and some in an old line-of-business program that nobody dares touch any more. To make use of it all together, Dataspilot uses its own ETL.
What is an ETL?
ETL stands for Extract, Transform, Load: extracting data from its sources, transforming it (cleaning, converting, matching) and then loading it into a target database. It is the invisible step that makes a dashboard reliable or a migration lossless.
Sources in very different formats
Our ETL is designed to read data wherever it lives, however old it is:
- Modern databases: the SQL databases used by most business management software.
- Legacy databases: data files from old line-of-business software, sometimes with no vendor or documentation. We analyse their structure to recover customers, products and history.
- API: online software (CRM, invoicing, e-commerce, banking…) queried through its programming interfaces.
- Files: CSV or Excel exports produced regularly by a tool.
Each source is checked on a case-by-case basis during the analysis: we confirm what can be extracted, and how, before committing.
Transform: where quality happens
Extracting is not enough. The same information is rarely encoded the same way from one piece of software to another. The transformation step makes it possible to:
- convert character encodings, date formats and amount formats from legacy systems;
- match the same customer or product appearing in several sources (company number, customer code, reference);
- remove duplicates and flag incomplete data rather than carrying it over as it is;
- apply your business rules: how turnover, margin or an active customer is defined.
Load: a single, up-to-date database
The transformed data is loaded into a target database. Loads can be scheduled (every night, every hour…) and each run is logged: you know what was loaded, when, and what was rejected.
What it is used for in practice
- Reports and dashboards that combine several software packages, with the same figures for everyone. See Data & reporting.
- Migration from legacy software to a new tool, with no re-keying and no loss of history.
- Regular synchronisation between tools that do not talk to each other. See Integrations & API.
- Searchable archiving of data from software that is about to be retired.
How a project runs
- Inventory of the sources and useful data.
- Analysis of the structures, particularly for legacy or undocumented databases.
- Transformation rules defined with you, in writing.
- Initial load and review of the results with your teams.
- Automation of subsequent loads and monitoring.
Do you have data locked away in legacy software or spread across several tools? Let's talk.
Key points
- An ETL extracts, transforms and loads data from several sources into a single database.
- Dataspilot's ETL reads modern databases, legacy software databases, APIs and files.
- The transformation (formats, duplicates, matching) is what makes the result reliable.
- Uses: reporting, migration, synchronisation between tools, archiving.
- ETL
- data
- databases
- API
- migration