Loading Transactional Data from a Flat File

In the next step in our scenario, we want to load cost center plan items, which are stored in flat files, into a Standard DataStore Object. Again we'll use the Graphical Data Flow Tool to define a DataSource, Transformation and Data Transfer Process.

A DataSource, a Transformation and a Data Transfer Process have to be created to complete the data flow.

By executing the Data Transfer Process, the data is loaded into the Inbound Table of the Standard DataStore Object. In our scenario, we have to activate the request to make it available for reporting. Later in this course, a file with updated data will be loaded and the related request should also be activated to reflect the changes in SAP BW/4HANA accordingly.

If you load from an external system using flat files, you define the DataSource in SAP BW/4HANA using a File Source System.

Before you can transfer data from a file source system, the meta data (the file and field information) must be available in SAP BW/4HANA in the form of a DataSource.

The following list gives some of the features of the SAP BW file adapter and file-based DataSources:

• Automatic field proposals at design time

• Automated conversion of external data types and formats provided

• Preview option allows a double check of file parsing

• Fields can be selected as Not Transferred

Nearly every company finds it necessary at some stage to load a flat file. It may be during a pilot project, or when data is purchased (a flat file is often the way in which purchased data is delivered). The basic terms are the same, but there are many more features.

You have the option of loading data from any workstation into SAP BW/4HANA. For performance reasons, however, you should store the data on an application server and load it from there into SAP BW/4HANA. This means that you can also load the data in the background.

If you want to load a large amount of transactional data into SAP BW/4HANA from a flat file, you can specify the file type of the flat file. Create the flat file as an ASCII file. From a performance point of view, loading data from an ASCII file is the most cost-effective method. Loading from a CSV file takes longer because, in this case, the separator characters and escape characters have to be sent and interpreted. In some circumstances, however, generating an ASCII file may involve more effort.

Text-type files contain only characters that can be displayed and read as text. CSV and ASCII files are examples of text files. For CSV files, you have to specify a character that separates the individual field values. In SAP BW/4HANA, you have to specify this separator character and an escape character, which specifies this character as a component of the value, as required. After you have specified these characters, you have to use them in the file. ASCII files contain data in a specified length. The defined field length in the file must be the same as the length of the assigned field in SAP BW/4HANA.

There's a Routine icon located next to the name of the file. The purpose of this icon is to allow you to create programs that, in turn, create the actual name and location of the flat file dynamically. By using program logic that includes the current day or month in the resulting file name (01.2023_sales.csv, for example), you can ensure the same file is not loaded twice. This is because for next month's load, the required file on the server would need to be 02.2023_sales.csv.

Be careful to correctly define the format of the file. SAP suggests the use of a header row, because the system can use this header information to help define the fields on the file.

The Proposal tab reads the header row and proposes field names and types based on what it finds. These proposed field names, sizes, and types can be changed on the second most important tab, the Fields tab.

The Transfer checkbox and the Internal/External dropdown are on the far right on the Fields tab. The Internal/External format toggle tells the parsing program if the data being sent is in the format that the user sees in an application, or in the format stored on the database. An example might be the fiscal period 01.2023 (external) versus the period 2023001 (internal). You must also identify or correct the data type if it is proposed in error by the system. For example, during a test load of a cost center transactional load, the system proposes the data type RAW, when, it should have been a CHAR field.

To set the meta data of the fields, InfoObjects can be used as a template. By using InfoObject Templates, the meta data of an InfoObject is copied to the meta data of a source field.

Because a mistake in the Field or the Extraction tabs can corrupt your data load, it is recommended to check it with real data. The Preview tab does this for you with the number of records you request after you activate the DataSource.

Let's have a look at the activation of requests in a Standard DataStore Object with an example.

On the figure above, a DTP has been executed (Request 1) and the record with Doc. No. 4711 and Value 10 is loaded in the Inbound Table.

So, what happens if this request is activated?

On the figure above, request 1 is activated. Active Data and Change Log are updated and Request 1 is deleted from the Inbound Table. Also note the RECORDMODE, which has value N (new).