Loading Data using Flat Files

Team-Fly

---

Loading data in SAP BW is a simple process. The design of the loading process is done by an SAP BW application developer, but production data load tasks are done by the Basis and SAP BW operation team.

The data loading process from flat files is very similar to loading data from SAP R/3 with one key difference: the metadata. When you define an SAP R/3 source system in SAP BW, by updating metadata, SAP BW automatically brings in the needed metadata from the SAP R/3 instance for all InfoSources. Because SAP BW does not know the external data file structure, you must first define metadata associated with individual data flat files. After metadata has been defined, the rest of the InfoCube construction and data load process is the same. Logically, an InfoSource in SAP BW associated with a flat file is the same as if an InfoSource is connected to SAP R/3 objects.

You can load the following types of data in SAP BW via flat files:

• Master Data

• Text

• Hierarchies

• Transaction Data

SAP BW accepts most common data file formats, such as Microsoft Excel's Comma Separated Files (CSV), ASCII (ASC), or other known field delimiters of your choice to define a data file.

File definitions for master data, text, and transaction data are very simple. However, hierarchy data file definitions can be very complex based on levels of hierarchy nodes. Often, it is worth building hierarchies manually after the needed InfoObjects and their master data have been loaded in SAP BW.

I'll show you how to define a flat file data source, then load master data, texts, and transaction data in SAP BW. Finally, you'll see how to load transaction data in a predefined InfoCube.

Flat File Scenario

In this situation, you want to load master data for products. The master data consists of an 18-character product code and its 9-character group. Text associated with products is in English and German, E and D, respectively. The transaction data consists of customer order data that references the products in the product flat files. All files are in Microsoft Excel CSV format.

Defining a Flat File Data Source in SAP BW

From the Administrator Workbench, click the Source systems tabstrip. You will see the source system tree. Select the Source systems node, right-click, and select Create source system. Select the flat file source system icon (blue PC), and click the check mark to accept your choice, as shown in Figure 10-1.

Figure 10-1: Defining a Flat File Data Source.

You are then asked to select the source system type. Enter a source system technical name and description, as shown in Figure 10-2. The descriptive name will be displayed in the source system tree list. After saving your source system, you will see this source system tree list.

Figure 10-2: Creating a Source System for Flat File Data Sources.

Until now, SAP BW did not know the structure of this new data source and had no association with any other objects. Association between this new flat file and the rest of the SAP BW data load environment is made via an InfoSource.

In the Administrator Workbench, click the InfoSources tabstrip. Then create a new InfoSource in an Application Component to be used for flat files. SAP BW supports two types of InfoSources: transaction and master data types. Here you are asked to identify the type of flat file source, either transaction or master data, as shown in Figure 10-3.

Figure 10-3: Defining InfoSource Type for Flat File Data Sources.

Select master data and click the check mark, as shown in Figure 10-3. When asked, enter the InfoObject you want to associate with and save. In this example, an InfoObject ZPRODUCT is selected. After the InfoSource has been created, the next step is to link this InfoSource to a data source, the flat file.

Defining Metadata for Flat Files

From a metadata point of view, master data and transaction data flat file sources behave differently. Through SAP BW 1.2B Patch 12, the master data InfoSource is associated with one InfoObject coming from one data source. This logic has changed in SAP BW 2.0.

On the other hand, a transaction InfoSource can consist of several InfoObjects coming from several data sources. For this reason, you will notice that metadata for a master data flat file InfoSource is automatically pulled from its InfoObjects, and you will be asked to enter metadata manually for the transaction InfoSource flat file. This metadata transformation takes place when you assign a source system to an InfoSource.

To assign a source system to an InfoSource, right-click the InfoSource and select Assign source system. Select the flat file source system, for example, SS_FF_PROD, as shown in Figure 10-4. Because the InfoSource type is master data, SAP BW displays metadata for the master data, text, and hierarchies, as shown in Figures 10-5, 10-6, and 10-7.

Figure 10-4: Assigning a Flat File Source System to a Master Data InfoSource.

Figure 10-5: Attributes Definitions for Master Data.

Figure 10-6: Texts Definitions for Master Data.

Figure 10-7: Hierarchies Definitions for Master Data.

It is important to note that for master data flat file InfoSources, the input data file must conform to the field layout as defined in the metadata window. For example, Figure 10-5 shows that each record in the master data Attributes file must consist of a product and its group type value separated by a field delimiter. Similarly, the Texts master data file (see Figure 10-6) defines the structure of a data file as language key, product, and product description, each separated by a field delimiter.

Note 

When defining a time-dependent master data, you need two additional fields, DATEFROM and DATETO, in master data records attributes. In the case of time-dependent hierarchies, you also need to define these two date fields for each item. This master data time-dependency plays an important role in the modeling of analytical application objects such as InfoCubes. Multidimensional modeling is discussed in Chapter 12, "SAP BW-Defining Custom InfoCubes."

As mentioned earlier, SAP BW supports two types of InfoSources: transaction and master data types. If an InfoSource is of transaction type, SAP BW displays the Maintain Meta Data window. At this moment, SAP BW has no physical connection to a transaction data file to read the file structure. You need to manually define the transaction file structure. Assign appropriate InfoObjects that map to the flat file field definitions, as shown in Figure 10-8 for customer sales data.

Figure 10-8: Metadata Definition for Transaction Data Flat Files.

Once you have metadata for master data and transaction data files, the rest of the data loading process is very similar to loading from SAP R/3.

Remember that this metadata is used to define the transfer structure for the related InfoSource. Make sure that it is complete.

Data Type for Flat Files

It is a common practice that flat files contain plain ASCII data. After defining the input data file layout, make sure that the data values stored in the flat files are consistent with the SAP BW data types used to define the metadata for the file. The following guidelines must be followed when preparing data files.

• Data files must not include a header line. The actual data values must start from the very first record.

• Dates must be in YYYYMMDD format.

• Time must be in HHMMSS format.

• Sort input files, when possible, based on characteristics that identify each record uniquely. This improves data load performance.

• Due to IDOC record size limitations, you are restricted to 1,000 bytes per record. This 1,000 bytes record limit does not apply when data is to be stored in ODS. When data consists of more than 1,000 bytes and you do not want to store it in ODS, split the file but keep the same characteristics in the same order in all files, each assigned to an individual InfoSource. Because each file has the same characteristics or dimensions, when data is loaded in an InfoCube, records from individual data files are rolled up correctly for end-user queries and reports as if they came from one data file.

---

Team-Fly