Challenges and Solutions of Real-Time Data Integration Techniques by ETL Application

Introduction

Traditional Data warehouse (DW) use to store static data. In DW, strategic analysis is performed on Business data which is integrated from heterogeneous data source. The data is captured, aggregated, cleaned and analyzed for deriving better decisions (Wrembel, 2006; Hong et al., 2009). The analytical decision depends not only on data processing applications, but also on the derived data. The data should be accurate, relevant and timely in nature. The more timely processed data ensure the more better decision making. The decision making process is often delayed in traditional DW due to late propagation of data from the source system to DW in time. Presently, organizations want to access up-to-date data for decision making. So, the concept of real-time ETL technique is introduced (Biswas et al., 2020).

In traditional batch processing ETL, DW refreshment is performed in offline mode in daily, weekly or monthly basis (Vassiliadis, 2009; Biswas et al., 2019). Data is extracted from different types of sources, then it is cleaned and transformed and at last loaded into the DW. These activities are generally performed at night during the warehouse downtime. Any interference is unwanted during the loading and query processing on the DW. These historical data is stored for future analysis purpose.

The way of organizations accessing data is rapidly changing. Nowadays organizations want to access real-time transactional data for taking immediate decision. Currently many industries such as stock exchange, e-commerce, air traffic control, telecommunication etc. have the requirements of correct report based on fresh data in DW as operational decision can be made speedy.

This can’t be performed on the status report of yesterday.

Nowadays, the web is considered as important source. In this case, the transactional data that emerge at the source side are not always possible to collect later, if off- line refreshment is performed on the warehouse. Besides, the volume of data for analysis is becoming very high and the response time is shortening. So, the demand is increasing for superior ETL tool. Therefore, the time window is shortening for loading in DW. So the main focus for Business Intelligence (Waas et al., 2013) lies in the DW and the ETL process for supporting continuous data flow (Tho and Tjoa, 2003; Polyzotis et al., 2007) and decreasing downtime.

So, how to define fresh data? Freshness is signifying from minutes to seconds or sub-seconds of data flow delay. The trends of “near real-time” (J¨org and Dessloch, 2009; Chen et al., 2010; Wibowo, 2015) or “real-time” (Cuzzocrea et al., 2014; Patel and Patel, 2022) is going to be the new challenges in technological solutions. Some commercial systems like Informatica PowerCenter, Infosphere Datastage, Oracle Data Integrator, Talend Open Studio, Azure Data Factory etc. are working towards getting fresh data in DW.

In traditional ETL, data warehouse refreshment is done periodically in off-peak time. Due to periodical updation fresh data could not be accessed for analysis purpose. The late arrival of data in warehouse is termed as data latency. Another problem of traditional ETL is off-peak hour loading when all operational and analysis process should be paused. With the introduction of web applications, e-commerce, retail, banking, network traffic monitoring etc. demand running system in 24x7 hours along with latest updated data. To address these issue “near real-time” and further “real-time” mechanism is introduced. Here the main focus is to minimize data latency by identifying the changes immediately in the operational data source and propagate it to warehouse quickly.

Moving towards achieving “real time” implementation, the simple way is to shorten the refreshment cycle. Generally, five to fifteen minutes latency can be categories as “near real time” approach (Vassiliadis and Simitsis, 2008). This solution does not require much changes in the existing operational system. Some tools are proposed based on this approach in literature (Vassiliadis et al., 2001; Italiano and Ferreira, 2006; Abrahiem, 2007; J. and Bernardino, 2008). It is a good option for organization dealing with small data warehouse and tolerant of low latency. Workable solution towards near real-time warehouse is introduced in literature (Jain et al., 2012; Ferreira et al., 2013; Suzumura et al., 2010).