Creating DataSources for File Source Systems 

Use

Before you are able to transfer data from a file source system, the metadata, meaning the information in the files and fields must be available in BI in the form of a DataSource.

Prerequisites

Note the following with regard to CSV files:

●     Fields that are not filled in a CSV file are filled with a blank space if they are character fields and with a zero (0) if they are numerical fields.

●     If separators are used inconsistently in a CSV file, the wrong (not defined in the DataSource) separator is read as a character, and both fields are merged into one field and possibly shortened. Subsequent fields are then no longer in the correct order.

Note the following with regard to CSV files and ASCII files:

●     The conversion routine being used determines whether you have to specify leading zeros. See also Conversion Routines in the BI System.

●     For dates, you usually use the format YYYYMMDD, without internal separators. Depending on the conversion routine being used, you can also use other formats.

Notes on Uploading

When you upload external data, you are able to load the data from any workstation into BI. For performance reasons, however, you should store the data on an application server and load it from there into BI. This means that you can also load the data in the background.

If you want to upload a large amount of transaction data from a flat file, and you are able to specify the file type of the flat file, you should create the flat file as an ASCII file. From a performance point of view, uploading the data from an ASCII file is the most cost-effective method. Under certain circumstances, generating an ASCII file might involve a larger workload.

Procedure

You are in the DataSource tree in the Data Warehousing Workbench.

...

       1.      Select the application components in which the DataSource is to be created and choose Create DataSource.

       2.      In the next screen, enter a technical name for the DataSource, select the type of the DataSource and choose Copy.

The DataSource maintenance screen appears.

       3.      Maintain the General tab page.

                            a.      Enter descriptions for the DataSource (short, medium, long).

                            b.      If necessary, specify if the DataSource builds an initial non-cumulative and supplies duplicate data records within a request.

                            c.      Specify whether the PSA should be created with character-like fields.

       4.      Maintain the Extraction tab page.

                            a.      Define the delta method for the DataSource.

                            b.      Specify whether the DataSource supports direct access to data.

                            c.      Real-time data acquisition is not supported for transferring from files.

                            d.      Select the adapter for the data transfer. You can load text files (CSV) or binary files (ASCII) from your local work station or from the application server. With Properties you can display the general adapter properties.

                            e.      Select the path to the file that is to be loaded, or enter the name of the file directly, for example C:/Daten/US/Kosten97.csv.

You can also create a routine that determines the name of your file. If you do not create a routine for the name of the file, the system directly interprets the file name in the File Name field.

                              f.      Depending on the adapter and the file to be loaded, make further settings.

                            g.      Select the data and the data format, and if desired make further settings, depending on your selection.

       5.      Go to the Proposal tab page.

Here you create a proposal for the field list of the DataSource based on the sample data of your file.

                            a.      Specify how many data records you want to load and choose Load Sample Data.

The data is displayed in the format of your file in the upper part of the tab page.

The system displays the proposal for the field list in the lower part of the tab page.

                            b.      Where necessary, remove the Copy to Field Lists selection for fields that are not to be transferred to the field lists.

       6.      Maintain the Fields tab page.

                            a.      By setting the Transfer indicator, select the fields that should be provided for extraction.

                            b.      If required, specify InfoObjects of the BI system for the fields under Template InfoObject. The system proposes them for assignment to a field when defining the transformation. It is possible to transfer the technical properties of the InfoObject into the DataSource field.

                            c.      If required, change the data type of the field.

                            d.      If required, change the values for the key fields of the source.

                            e.      Define if lowercase is supported.

                              f.      If required, change the output length for a field.

                            g.      Specify whether the source provides the data in an internal or external format.

                            h.      If required, specify a conversion routine that converts the data to internal format.

                              i.      Select the fields for which you want to be able to set selection criteria when you schedule a data request using a BI InfoPackage. Data for this type of field is transferred in accordance with the selection criteria specified in the InfoPackage.

                              j.      Choose the selection options (such as EQ, BT) to be provided for selection in the InfoPackage. If required, in the field type, define if the data to be selected is language-dependent or time-dependent.

       7.      Check, save and activate the DataSource.

       8.      Go to the Preview tab page.

If you select Read Preview Data, the specified number of data records, according to your field selection, is displayed in a preview.

With this function you are able to check whether the data formats and data are correct.

Result

The DataSource is created and visible in the DataSource overview for the file source system in the application component in the Data Warehousing Workbench.

You can now create an InfoPackage in which you define the selections for the data request. The data can be loaded into the entry layer of the BI system, the PSA. Alternatively you can access the data directly if the DataSource supports direct access and if you have defined the data flow with a VirtualProvider.