Extraction, Transformation and Loading (ETL)

SAP BW offers flexible ways of integrating data from various sources. Depending on the data warehousing strategy for your application scenario, you can extract the data from the source and load it into the SAP NetWeaver BW system, or directly access the data in the source, without storing it physically in the Enterprise Data Warehouse. In this case the data is integrated virtually into the Enterprise Data Warehouse. Sources for the Enterprise Data Warehouse can be operational, relational datasets (for example in SAP systems), files or older systems. Transformations allow you to run a technical cleanup and to consolidate the data.

Extraction and Loading

Extraction and transfer processes to the initial layer in SAP BW as well as direct access to data are possible using various interfaces, depending on the origin and format of the data. SAP BW thus allows integration of SAP data and non-SAP data.

• BW Service API (BW Service Application Programming Interface)

  BW Service API allows extraction and direct access to data from SAP systems in standardized form. These can be SAP application systems or SAP NetWeaver BW systems. The data request is controlled from the SAP NetWeaver BW system.

• File Interface

  The file interface permits the extraction from and direct access to files, such as csv files. The data request is controlled from the SAP NetWeaver BW system.

• Web Services

  Web services permit you to send data to the SAP NetWeaver BW system under external control.

• UD Connect (Universal Data Connect)

  UD Connect permits the extraction from and direct access to relational data. The data request is controlled from the SAP NetWeaver BW system.

• DB Connect (Database Connect)

  DB Connect permits the extraction from and direct access to data located in tables or views of a database management system. The data request is controlled from the SAP NetWeaver BW system.

• Staging BAPIs (Staging Business Application Programming Interfaces)

  Staging BAPIs are open interfaces, which SAP NetWeaver BusinessObjects DataServices and certified third party tools can use to extract data from older systems. The data transfer can be triggered by a request from the SAP NetWeaver BW system or by SAP BusinessObjects DataServices or a third party tool.

Transformation

With transformations, data loaded within the SAP NetWeaver BW system using the specified interfaces is transferred from a source format to a target format in the data warehouse layers. The transformation allows you to consolidate, clean up and integrate the data, thus synchronizing it and allowing it to be evaluated. This is done using rules that allow any degree of complexity when transforming the data. The functionality includes a 1:1 assignment of the data, the use of complex functions in formulas and custom programming of transformation rules. For example, you can define formulas that use the functions of the transformation library for the transformation. Basic functions (like "and", "if", "less than", "greater than"), various functions for character chains (like displaying values in uppercase), date functions (like calculating the quarter from the date), mathematical functions (like division, exponential functions) are offered for defining formulas.

Availability Requirements for Data in SAP BW

It might be necessary to have data which is more up-do-date or less up-to-date, depending on the business issue.

For example, if you want to check the sales strategy for a product group each month, you need the sales data for this time span. Historic, aggregated data is taken into consideration. The scheduler is an SAP BW tool that loads the data at regular intervals, for example every night, using a job that is scheduled in the background. In this way, no additional load is put on the operational system. We recommend using standard data acquisition, scheduling regular data transfers to support your strategic decision-making procedure.

If you need data for the tactical decision-making procedure, data that is mostly up-to-date and granular is usually taken, for example if you analyze error quotas in production in order to optimally configure the production machines. The data can be staged in the SAP NetWeaver BW system based on its availability and loaded in minute intervals. A permanently active SAP background processing job is used here. This job is controlled by a special daemon. This data staging procedure is called real-time data acquisition.

By loading the data into a data warehouse, performance in the source system is not affected during data analysis. The load processes require administrative time and effort however. If you need data that is very up-to-date, and the users only need to access a small dataset sporadically, or only a few users run queries on the dataset at the same time, you can read the data directly from the source during analysis and reporting. In this case the data is not archived in the SAP NetWeaver BW system. Data staging is virtual. You use the VirtualProvider here. This procedure is called direct access.