Data Integration and ETL Technology

Data integration provides organizations with a unified view of data stored in multiple data sources, and extraction, transformation, and loading (ETL) technology is an early attempt at data integration.

Using ETL, you can extract, transform, and load data from multiple source transaction systems to a single location, such as a company data warehouse. The extraction and loading part is relatively mechanical, but the conversion part is not so easy. To achieve this, you need to define business rules to explain which transformations are effective.

One major difference between ETL and data integration is that data integration is a broader field. It may also include data quality and the process of defining master reference data, such as defining customers, products, suppliers, and other key information related to the provision of business affairs within the company.




Data classification and consistency


Let's look at an example. A large operating company may need to categorize products and customers from several levels to expand marketing activities in segments. For the company's smaller subsidiaries, this can be achieved through a simple product and customer classification hierarchy. In this example, a larger organization might classify a can of Coke as part of a carbonated drink, a beverage, and food and beverage sales. However, smaller subsidiaries may include the same Coke in food and beverage sales without an intermediate classification. This is why classification consistency—or at least an understanding of differences—is needed to get a global view of the company's overall sales.

Unfortunately, knowing who you are doing business with is not always easy. For example, Shell U.K. is a subsidiary of the oil giant Royal Dutch Shell. Companies like Aera Energy and Bonny Gas Transport are Shell entities, and some have other investors. Therefore, business transactions with these companies need to be added to Shell's global view as customers, but from the company name, this relationship is not obvious.

The vice president of a well-known investment bank once told me that they don’t know how much business they have done globally, for example, Deutsche bank, let alone whether the company is profitable. The answers to these questions are buried in various global investment banks. Within the department’s system.



Data quality issues


ETL technology is an early attempt to solve this problem. But to get the conversion steps correctly, you need to define business rules to determine what conversion is effective—for example, how to summarize sales transactions or map a database field. When "m" is used to define male customers, and "male" is used In another meaning. The development of technology is helpful to this process.

Facts have proved that the realization of integrated data is more extensive than ETL and data integration itself. Data quality is also an important factor. What if you find duplicate content in the customer or product files? In a project I participated in, 80% of customer records were duplicates. This means that the number of business customers of the company is only one-fifth of what it thinks.

In raw materials, the repetition rate of the master file is usually 20% to 30%. When a company overview needs to be summarized, these anomalies should be eliminated.

Growing data volume

Although data integration has its advantages for large companies, it is not without challenges. Such as the continuous growth of unstructured data produced by companies.

Moreover, since the data is stored in different formats-sensor data, web logs, call logs, documents, images and videos-ETL tools may be ineffective in this environment because they are not designed to take these factors into account . When there is a large amount of data or big data, these tools will also encounter difficulties. Similar tools such as Apache Kafka try to solve this problem through real-time streaming data, which allows them to overcome the limitations of the previous message bus method on real-time data integration.

From the early ETL to the present, the related technologies and concepts of data integration have undergone great changes. But it still needs to continue to evolve to keep up with the constantly changing needs of enterprises and the new challenges that are emerging in the era of big data.