Business Intelligence: From Source System to Data Warehouse: ETL process

Follow FullPress on Google

Add us to your preferred sources.

There are several phases in an ETL process. Extraction, the first of them, involves setting up routines capable of reading the records contained in the source database and preparing them for the subsequent transformation phase. Transformation Most tools provide extraction routines specific to the most well-known ERP systems or the most widespread applications. After collecting the data, the transformation routines prepare them for their new location. There are various transformation techniques. Aggregation allows replacing numerous detailed records with a few summary records stored in Business Intelligence solutions. Data marts and data warehouses, which constitute the core of Business Intelligence solutions, are mini-databases that enable users to analyze metadata — that is, sets of data previously aggregated and summarized based on defined criteria — presented in quite intuitive ways (such as charts), to verify trends, macro-trends, and salient features of a given phenomenon. Data marts do not require detailed information but aggregated data, so they can be used directly by decision-makers, reducing times and improving information usage. For example, the marketing managers of a company can use data marts to get an idea of the outcomes of advertising and communication campaigns over the past five years, while sales managers can use ‘concentrated’ sales data to examine the revenue of each product over recent years of activity.Filtering/Cleaning ETL applications aggregate data with little effort. Translating data into values is a commonly used transformation technique. Operational databases actually store coded information to minimize the space occupied. However, data marts contain summarized information and are designed for easy use. Besides deriving fields simply from the union of two or more previous fields, ETL software also allows creating new ones using mathematical or statistical functions and logical analyses. A transformation routine is cleaning, meaning the use of algorithms to eliminate from the system data that are inaccurate or inconsistent with the phenomenon being evaluated. Cleaning functionalities can also be used to replace multiple versions of the same data appearing in various forms within the database with a single value: for instance, the name Fiat may appear under different fields such as Fiat S.p.A., Fiat Auto, Fiat, etc., and during the cleaning phase, the multiple versions of the same data are replaced by a single, unique datum.Loading The final phase of the ETL process is the loading of data into the target database. In the loading phase, there are two distinct models of data replication. In ‘push’ replication, the application pushes the transformed data to the destination database. In ‘pull’ replication, conversely, the application or destination database requests the data according to current specific needs. ETL systems are indeed the key infrastructure to support decision-making in Business Intelligence systems. They enable transforming disorganized data and content into strategically useful information to make critical decisions and operate effectively. This is called Information Asset Management, which allows collecting, validating, organizing, managing, and distributing the correct information to the right person, at the necessary time, and with the most appropriate tool.The Data Warehouse, as mentioned earlier, is the collection of data structures and tools necessary to provide managers with decision-making support. The useful information in this sense is derived from operational data produced and managed by an innovative enterprise information system. The Data Warehouse is not only a technological innovation but also a strategic one, as the analysis work shifts from specialized personnel to all company managers. Understanding trends and making comparisons before making important decisions becomes easier thanks to aggregated information that can be quickly visualized and analyzed. Access to detailed data is performed as needed. The Data Warehouse is the basis of every Analytical CRM application because it represents the means to collect, validate, integrate, and historize company customer data coming from classic channels or the Web channel, which constitute the input for analytical activities (the databases it relies on are relational, that is, directly queryable via SQL, Structured Query Language). It is also the set of data structures and tools necessary to obtain, from operational data produced and managed by an information system, information useful to managers as decision support. A Webhouse is a Data Warehouse that also collects data from the web channel (clickstream, user registrations, etc.). The Data Warehouse is a database with the following characteristics: • Subject-oriented: data are organized according to a specific business process. • Integrated: data flowing into the Data Warehouse usually come from various sources (transactional, operational systems, other databases). They are cleaned and transformed to allow simple and effective access. • Non-volatile: data access is read-only and extremely fast. • Time-variant: data are organized according to the temporal dimension, fundamental for business analyses. Unlike Source Systems — particularly OLTPs — DWHs are designed and optimized for analytical processing. In collaboration with other tools and applications, DWHs constitute OLAP systems (On-Line Analytical Processing System). Among the most widely recognized definitions of Data Warehouse, two particularly identify its peculiar characteristics: “A platform on which data coming from different organizational areas are stored and managed; such data are updated, integrated, and consolidated from operational systems to support all decision support applications” (Gartner Group) “A subject-oriented, integrated, time-variant, non-volatile data collection constructed to support the decision-making process” (W.H. Inmon) It thus emphasizes first and foremost the Data Warehouse’s characteristic as a data collection supporting the management decision-making process. The Data Warehouse groups decision-making data by areas or themes of interest and organizes them based on final utilization; this differs from traditional databases whose design is driven by the requirements of applications supporting operational processes. In the Data Warehouse field, information assumes a corporate value rather than a departmental one, because it is the integrated result of data coming from multiple sources, including external to the company. All information is made consistent by a common data model and the definition of corporate standards (naming conventions, units of measure, codifications). It has a broad time horizon, ensuring the retention of historical information to enable comparative analysis activities over different time periods. The information is consolidated, consistent over time, and not modifiable by users who access it in read-only mode. The term Data Mart indicates collections of information targeted to a departmental user base and oriented towards a specific theme. The level of data aggregation in the Data Mart is often higher than in the Data Warehouse, which also contains detailed data, as it is designed to directly and explicitly satisfy the end user’s needs. The importance of the principle of separation between operational environments and informational, decision-making environments is evident. The concurrent presence of two classes of users with different nature and functions on the same database would cause contention issues, creating unpredictable response times often unacceptable for transactional processes. Moreover, the necessary separation of environments, obtained through a mere replication process of transactional environments, does not meet the needs since operational databases are process-oriented and do not provide an integrated view of the data of interest. The goal of the Data Warehouse is to provide timely and correct answers to decision problems related to institutional activities of the Administration, ensuring better results in terms of effectiveness and efficiency. The informational need is obviously not new, but from a technological standpoint, today there are conditions to support this request adequately. The enabling technological aspects are the availability of computational power to manage large volumes of data at low cost and the possibility of distributing information via intranet/internet technology. However, it should be emphasized that while the technological factor is a necessary prerequisite for developing Data Warehouse solutions, the critical success factor is end-user involvement. Only adequate sponsorship from management can ensure the necessary contribution in the project from various company departments with their related knowledge of processes and data. ALL RIGHTS RESERVED.