SAP BW ETL (Extraction, Transformation, and Loading)

Welcome to the tutorial about SAP BW ETL. We aim to provide you with basic and in-depth information regarding the three stages of the ETL process. After completing this tutorial, you will be able to define what is ETL, expound on the different stages of the ETL process and weigh in the advantages and disadvantages of developing your own ETL tool as well as availing the ETL tools in the market such as SAP BW ETL.

ETL stands for Extraction, Transformation, and Loading. ETL is considered as an essential component of a data warehousing system.

To avoid interference with the source systems, a temporary working area needs to host the extracted data. Commonly addressed as the data staging area, some writers also refer to this as the construction site of a data warehouse. The warehouse shows the needed data and how to store it. The sources of the different data may be located at various servers across the company’s computer network. All data extracted from multiple sources must be held at one place. After that, data transformation can be efficiently performed. The data stagingarea requires reconciling of data structures of both the source systems and the warehouse. The created flat files and/or databases meet these needs. In order to automate a  data warehouse population process, an ETL procedure must be developed.

Ideally, developing an ETL tool may consume about half of the time of a warehouse project. An ETL tool must map the source and the destination for each piece of data. It must be specified with the correct paths of the data sources and corresponding destinations. This enables the ETL tool to pull the data from the given sources and send it to the right destinations in the warehouse. Moreover, the ETL tool must also clearly define what data to be pulled from each source and what transformation is to be performed for it. SAP BW ETL provides a collection of objects and tools that allow users to import, export, and transform heterogeneous data between one or multiple types of data formats, such as MS Excel, text files, SAP ECC, etc.

Data Extraction

The first stage of a SAP BW ETL process is data extraction from the numerous source systems. In almost all the cases, this is the most difficult aspect of ETL. Correct extraction of the data sets the stage for how consequent processes go further.

A big part of creating a data warehouse is pulling data from various data sources and placing it in a central storage area. Hence, this is a very challenging step to accomplish.

Data extraction is basically the process of selecting, transporting and consolidating the source data to the SAP BW ETL environment. The extraction phase converts the data into a common format which would be suitable for transformation processing.

SAP BW offers standard extractors; however, you can still design your own extractor based on your own requirements. Most extractors that extract SAP application transaction data are delta-enabled. During the time of posting, the transactions are written to the delta queue. They are then extracted to SAP BW. You can also extract data directly from the tables/views which use DB Connect and UD Connect interfaces. Similarly, flat files interface allows extraction to SAP BW from flat files. There are many other extraction interfaces in SAP BW. These include staging BAPIs, web services, etc.

Acquiring data requires info packages. You can set various parameters to acquire data as per the following screen:

SAP BW’s staging layer (Persistent Staging Area – PSA) stores the extracted data.

Data Transformation

This stage transforms and relates the data extracted from numerous resources; this is another important task after data extraction. In the transformation step, a series of functions or rules apply to the extracted data from the source. This derives the data for loading into the final target. Some data sources will require very little or even no manipulation of data. Take for example if an organization has much of its data in flat files and operational systems while we build a data warehouse. We have to relate data from all of these numerous sources to handle data extracted from any of these source systems.

Data Loading

The last stage of the SAP BW ETL process is data loading. For the data to generate reports, you need to fill the data targets with the data in the staging database. This step only looks uncommon. Several lookups may be necessary to perform before calculating some values for the data target.

Take into account that such data transformations can be performed at one of the two stages: while extracting the data from the beginning or while loading data into the dimensional model. Clients need to wait for complete data extraction before transforming it to make sure that they can extract the data first. Prior to extraction, if you have any information about dimensions, proceed and transform the data while extracting it.

SAP BW’s Data Transformation Process (DTP) pushes the data to the data targets.

Conclusion

Organizations can build their own data transformation tool. This is the ideal way for a small number of data sources that reside in the same type of storage. Because of the similar system architecture and common data structure, the work involved in developing the needed transformation lessens. This method also saves license costs and training the employees in the new tool. However, if the transformations become more refined during the time or there is a need to integrate other systems, the intricacy of such an ETL system raises and the manageability falls considerably. Also, building an own tool from scratch often is a waste of time.

Many ETL tools are available on the market. Increasingly, corporations are purchasing ETL tools to help in the creation of ETL processes. The significant advantage in using available ETL tools is that they are optimized for the ETL process. They provide connectors to common data sources such as xml, mainframe systems, databases, flat files, etc. These tools also implement data transformations across multiple data sources with ease and consistency. Readily available features include joining, aggregation, and sorting.

SAP BW’s reliable data acquisition and information processing capabilities make it one of the best among many other renowned commercial tools.

—

Did you like this tutorial? Have any questions or comments? We would love to hear your feedback in the comments section below. It’d be a big help for us, and hopefully it’s something we can address for you in improvement of our free SAP BW tutorials.