Smartstore ETL, your autopilot for data warehousing according to ISO 9001
Thursday, November 21, 2019

Smartstore ETL, your autopilot for data warehousing according to ISO 9001

Extract Transform Load (ETL) is a process in which data from several data sources, which may be structured differently, are combined in a target database. Extraction of the relevant data from different sources Transformation of the data into the schema and format of the target database Load. In other words. Different data sources can be extracted via an ETL process and thus prepared for integration into a data warehouse. source: Wikipedia

These are the three main phases of the ETL process

Extraction: The extraction is the first step of the ETL process. This is where the data is selected in various source systems and prepared for the transformation phase. In most cases, the process extracts only partial areas from individual source databases. Extractions take place on a regular basis in order to continuously supply the data warehouse with updated data. Event-driven or request-driven extractions are also possible.

Transformation: The extraction is followed by the transformation phase. The delivered data is adapted to the format and schema of the target database. The transformation process again passes through several individual steps. These individual steps can be, for example, the following:

  • Defining basic aspects of formatting
  • Correction of corrupted data
  • Check for similar information and any data duplicates with subsequent deletion and exclusion of such data
  • Group, sort and aggregate the data
  • final adaptation to target formats and target schemas

Load: The third and final step is to load the previously checked and enriched data. In this step, the actual integration into the target database or data warehouse takes place. The data is physically moved to its target without blocking the database for too long while loading. The integrity of the loaded data must be ensured. All changes in the target system are documented by detailed protocolling and logging. Via logging it is possible to restore old data statuses if required.

In which specific application case could ETL be applied?
ETL processes are often used in the environment of Big Data and Business Intelligence applications. Especially the processing of large data volumes benefits from the structured ETL approach. If your information is distributed on different subsystems, e.g. in an ERP or warehouse software etc., there is usually redundancy, and this is exactly where the use of an ETL process makes sense. During the process, the heterogeneously structured data from different sources is merged and prepared. The quality of the data is ensured and its consistency is established in the data warehouse. This is where Smartstore benefits from its search mechanism, and is thus able to provide the appropriate information from millions of data within milliseconds.

So far so good. For example, could we integrate our ERP directly into Smartstore via ETL?
Yes, that's exactly how it works. It's definitely a better alternative than developing all the steps individually using residual APIs. This type of data integration saves time and accelerates processes, but the most benefit is gained by constantly comparing the data at runtime. Typical areas of application for ETL can be found where data has to be processed, provided and analyzed in large quantities. Examples of application areas are:

  • Data storage in a data warehouse
  • Data provision for BI applications
  • Data extraction from distributed database environments or cloud based databases
  • Migration of data between different applications
  • Replication of data for backup and redundancy purposes
Do you offer Smartstore's own ETL as a cloud service?
Yes, you can obtain Smartstore ETL from us as a cloud service. Furthermore we offer you the possibility to use Smartstore ETL as an on-premises service.

Which Smartstore Edition does this form of data processing work with?
As this type of data processing is used in industrial environments, we offer Smartstore ETL Cloud and Smartstore ETL On-Premises only starting with Smartstore Enterprise Commerce.

Ready to get started?
Contact us or try Smartstore for free. Contact sales

Leave your comment