Data transformation/ETL

At LGM Digital, we bring together the various skills required to transform technical data in the different contexts mentioned above:

• transformation of unstructured data into structured data
• data migration
• data quality control

LGM Digital's business data specialists, standards experts and data managers complement each other, enabling us to offer data conversion solutions by:

• optimising the costs of implementation,
• controlling structuring constraints
• and guaranteeing the quality of the processed data.

Data issues

This information, the very heart of our skills, this data, is currently the subject of a major challenge for all of us, major industrial accounts and the companies that support them.
This major challenge is that we need to make our data accessible... in order to be exploitable, and to do this, it must be structured.

Data must be made accessible so that it can be used to:

• optimise its business processes thanks to dedicated tools such as:
  • ERP,
  • MES, CAPM, CMMS,
  • ALM and PLM,
  • or any other business software solution.
• share this information in an industrial chain including customers, partners and suppliers.
• interpret them, and deduce trends, exploit our experience feedback, draw conclusions and make strategic decisions.)

Structuring

To share and exploit them, we need to structure them.

For several years now, data standardisation has been developing in order to offer all of us structured formats that enable us to meet this challenge of sharing and exploiting data.

We propose this standardisation by relying on standards and norms (OSLC for life cycle engineering, ASD for support, AIXM for air traffic, IFC for BIM, etc.).
These are all standards on which our specialists at LGM Digital provide our clients with their expertise to help them implement these standards, or to produce information in compliance with these standards or to adapt them to a so-called "proprietary" format, for specific industrial business contexts.

Conversion

It is by supporting our clients in the implementation of digital continuity chains by deploying these pivotal formats through standards that we have been able to make the following observation:

Whatever the target data formats, the problem that we all encounter, at some point, is "now that I have defined and specified the way in which I need to structure my data, my information, whether according to ASD or other standards, or according to a proprietary format, how do I migrate my information which is currently unstructured or poorly structured, or even dispersed in different sources and formats, how do I migrate it and transform it into the target format and according to the specified, standardised structuring?

This is the observation we have been making for some time, since we have been assisting our clients in the implementation of data structuring standards or in technical repository overhaul projects, and we have been called upon several times to respond to this recurring and highly topical issue.

Method

To answer this "how to" problem

The various solutions available to an industry player wishing to migrate its data from an unstructured state to a structured state or from an obsolete format to a new format are

• either: to have the information re-entered by business teams, which will be time-consuming, costly, and introduce a large number of errors into the information, and therefore degrade its quality
• or: which will be much faster and more reliable, we will instead develop adapted, customised tools that will interpret the information, according to specified and developed rules, and transform the data into the target format. Depending on the case:
  • we entrust this development to an IT development team that will develop data transformation (or migration) scripts. Such developments can be carried out by our development teams.
  • or we entrust this transformation to a team specialising in data management (ETL, RPA)

For this last solution, LGM Digital has developed specific skills that allow us to carry out these transformations in an optimised manner.

Data Management is a speciality that calls on the use of databases and ETL tools up to RPA.

We have data management teams made up of consultants who have mastered ETL and RPA tools.

So what is an ETL, ETL stands for Extract Tranform and Load? It is an application that as its name indicates, will be able to extract data, transform it, and load it into a target database or generate output files in the expected format. These tools are usually used by Big Data specialists and are very powerful, robust and reliable.

They are more commonly used in finance, they are still rarely (except in IT departments but for managing information flows, not for converting technical information) used in technical business contexts like ours, but they are extremely relevant for transforming the kind of data and technical information that all of us here handle.

In other words, we do not redevelop specific tools for each problem, we set up ETLs. This has the advantage of taking less time.

RPA solutions (Robotic Process Automation) consist of automating repetitive tasks through the programming of robots. This solution makes it possible to free oneself from the most cumbersome tasks and can even use artificial intelligence to reproduce human behaviour.

The organisation

To introduce you to the organisation of a data transformation project.

At LGM Digital we have:

• specialists in our clients' business data,
• experts in the various target data structuring standards,
• and Data Managers.

We are therefore able to:

• analyse our clients' data as it stands, since we know the data;
• specify how to transform it to make it compliant with a target structuring, since we know the target standards;
• implement the conversion of this specified data, thanks to our mastery of conversion tools.

All these skills enable us to offer turnkey solutions, from consulting, through specification, to implementation.

To give some examples to illustrate the concrete application of our solution:

Word project → S1000D

LGM Digital has extensive expertise in the S1000D standard. Our teams of experts are made up of specialists who participate in the GIFAS mirror group on the S1000D standard and we provide numerous S1000D implementation support services for our clients.

The most recurrent problem to which we bring our solutions today is: "How can I migrate my entire document collection, potentially made up of tens of thousands of pages of unstructured Word documents, and how can I convert it into S1000D XML data modules that can be used by my IETM?”

Analysis: our experts will analyse the source documentation in Word, mapping the data to be migrated.

Specification: our standards specialists will specify the transformation rules, indicating where the source information is located and where and how to migrate it into the standardised target file.

Conversion: Our data management specialists will set up the ETL application to perform the specified migrations and transformations from the various sources to the defined targets, which will perform the transformation into S1000D XML.

The data thus obtained is then exploited by an electronic documentation web viewer in order to be able to exploit the documentary information in an ergonomic and functional web portal rather than in the form of paper or PDF documentation.

The transformation scripts developed in this way can be used once for a one-off need: the transformation of a document collection into a One Shot.

If this transformation is to be replayed regularly in the future by our client, we can provide an executable, compatible with the IT Department constraints, which will replay the same transformation. In this way, our customers will be able to reuse them every time they need them.

Project ATA2200 → S1000D

For a maintenance documentation, our client had an already structured document base, but according to the ATA2200 standard. We were able to support them in converting this entire documentation to S1000D 4.1.

Our ATA2200 and S1000D specialists specified the way in which the data present in one standard should be transcribed to be compliant with the target standard.

Our Data Management teams implemented the specified transformations to produce the XML Data Modules in S1000D.

Data recovery

These project examples show a certain type of conversion from one source format to another. We are dealing with a single format: Word → XML, XML → XML, Excel → XML, Word → XLS, Word → BoD

But we can also perform multi-format transformations.

When the completeness of a target information depends on the information contained in 2 sources of different formats.

For example:
Multiple information contained in Excel files, Access databases, XML, databases, Word all need to be extracted and loaded into the same target database or to a defined XML file.

Data quality

There is an underlying issue in this ambition to make our data usable. We need the information we use to be accurate, consistent and complete.

However, the data we wish to use may be unstructured.

It may be from two potentially contradictory sources, because they are maintained differently, not managed in configuration.

So, when we carry out transformations, or when we structure the data, we can take advantage of this to clean it up, carry out consistency checks, identify errors in the data, report them to the business managers and correct them.

We can set up controls to ensure the correct migration and quality of the data being handled and check the completeness of the migration.

Our experts can do this during a transformation, but also on any existing database or data files.