DB Connect also offers you the option of using this type of connection as a source system connection for a BW system and of using tables or views from a database to generate DataSources. DB Connect also allows you to map database data-types to data types in the ABAP dictionary. The enhancement that DB Connect makes to the database interface enables you to transfer data from external applications in the respective database tables or views directly into the BW system.
For the default connection, DB Client and DBSL are preinstalled for the database management system. If you want to use DB Connect to transfer data from other database management systems into the BW system, you need to install both the database-specific DB Client and the database-specific DBSL on the BW application server that will be used to run DB connect.
In the following graphic, the BW system runs on the DBMS Y. You do not, therefore, need to install DBSL and DB Client for the source DBMS Y. However, if you want to load data from a DBMS X table or view, you need to install DBSL and DB Client for the DBMS X.
Before you can transfer data from a database source system, the metadata (the table, view and field information) must be available in BI in the form of a DataSource.
See Requirements for Database Tables or Views
You have connected a DB Connect source system.
You are in the Data Warehousing Workbench in the DataSource tree.
1. Select the application components in which you want to create the DataSource and choose Create DataSource.
2. On the next screen, enter a technical name for the DataSource, select the type of DataSource and choose Copy.
The DataSource maintenance screen appears.
3. Go to the Generaltab page.
a) Enter descriptions for the DataSource (short, medium, long).
b) As required, specify whether the DataSource builds an initial non-cumulative and can return duplicate data records within a request.
4. Go to the Extraction tab page.
a) Define the delta process for the DataSource.
b) Specify whether you want the DataSource to support direct access to data.
c) The system displays Database Table as the adapter for the DataSource.
Choose Properties if you want to display the general adapter properties.
d) Select the source from which you want to transfer data.
■ Application data is assigned to a database user in the Database Management System (DBMS). You can specify a database user here. In this way you can select a table or view that is in the schema of this database user. To perform an extraction, the database user used for the connection to BI (also called BI user) needs read permission in the schema of the database user. If you do not specify the database user, the tables and views of the BI user are offered for selection.
■ Call the value help for field Table/View. In the next screen, select whether tables and/or views should be displayed for selection and enter the necessary data for the selection under Table/View. Choose Execute.
■ The database connection is established and the database tables are read. Then Choose DB Object Names screen appears. The tables and views belonging to the specified database user that correspond to your selections are displayed on this screen. The technical name, type and database schema for a table or view are displayed.
■ Only use tables and views in the extraction whose technical names consist solely of upper case letters, numbers, and underscores (_). Problems may arise if you use other characters.
■ Extraction and preview are only possible if the database user used in the connection (BI user) has read permission for the selected table or view.
Some of the tables and views belonging to a database user might not lie in the schema of the user. If the responsible database user for the selected table or view does not match the schema, you cannot extract any data or call up a preview. In this case, make sure that the extraction is possible by using a suitable view. For more information, see Database Users and Database Schemas.
5. Go to the Proposal tab page.
The fields of the table or view are displayed here. The overview of the database fields tells you which fields are key fields, the length of the field in the database compared with the length of the field in the ABAP data dictionary, and the field type in the database and the field type in the ABAP dictionary. It also gives you additional information to help you check the consistency of your data.
A proposal for creating the DataSource field list is also created. Based on the field properties in the database, a field name and properties are proposed for the DataSource. Conversions such as from lowercase to uppercase or from “ “ (space) to “_“ (underlining) are carried out. You can also change names and other properties of the DataSource field. Type changes are necessary, for example, if a suitable data type is not proposed. Changes to the name could be necessary if the first 16 places of field names on the database are identical. The field name in the DataSource is truncated after 16 places, so that a field name could occur more than once in proposals for the DataSource. When you use data types, be aware of database-specific features. For more information, see Requirements for Database Tables and Views.
6. Choose Copy to Field List to select the fields that you want to transfer to the field list for the DataSource. All fields are selected by default.
7. Go to the Fieldstab page.
Here you edit the fields that you transferred to the field list of the DataSource from the Proposal tab page.
If the system detects changes between the proposal and the field list when you go from tab page Proposal to tab page Fields, a dialog box is displayed in which you can specify whether or not you want to copy changes from the proposal to the field list.
a) Under Transfer, specify the decision-relevant DataSource fields that you want to be available for extraction and transferred to BI.
b) If required, change the values for the key fields of the source.These fields are generated as a secondary index in the PSA. This is important in ensuring good performance for data transfer process selections, in particular with semantic grouping.
c) Specify whether the source provides the data in the internal or external format.
d) If you choose an External Format, ensure that the output length of the field (external length) is correct. Change the entries, as required.
e) If required, specify a conversion routine that converts data from an external format into an internal format.
f) Select the fields that you want to be able to set selection criteria for when scheduling a data request using an InfoPackage. Data for this type of field is transferred in accordance with the selection criteria specified in the InfoPackage.
g) Choose the selection options (such as EQ, BT) that you want to be available for selection in the InfoPackage.
h) Under Field Type, specify whether the data to be selected is language-dependent or time-dependent, as required.
8. Check the DataSource.
The field names are checked for upper and lower case letters, special characters, and field length.
The system also checks whether an assignment to an ABAP data type is available for the fields.
9. Save and activate the DataSource.
10. Go to the Previewtab page.
If you choose Read Preview Data, the specified number of data records, corresponding to your field selection, is displayed in a preview.
This function allows you to check whether the data formats and data are correct. If you can see in the preview that the data is incorrect,
try to localize the error. See also: Localizing Errors
The DataSource is created and is visible in the Data Warehousing Workbench in the DataSource overview for the database source system under the application component. When you activate the DataSource, the system generates a PSA table and a transfer program. You can now create an InfoPackage. You define the selections for the data request in the InfoPackage. 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 you have a VirtualProvider in the definition of the data flow.
Code Page and Sort Sequence for Source System: SAP kernel-based systems, like the BI system, work on the assumption that the database was created with code page cp850 and is using sort sequence ‘bin’. The source system configuration may be different from this. If the sort sequence is different, sample search ( like ) and area search ( between, >,<) operations for character fields may return different results.
If you use multibyte code pages in the source system to store data for character records with more than 256 characters (special characters for Japanese (kanji and hiragana), Korean, Chinese, and so on) there is a risk that some of the characters may be corrupted.When you create the DataSource, you can check the result of the extraction in the preview to determine whether this problem has occurred. Since data conversion problems and unexpected sort results may arise if the database source system and BI do not use the same code page, It is recommend that you use the same code page in both the database source system and in BI.
DB Data Types : As a rule, the only data types that can be supported are those that can be modeled on ABAP Dictionary data types. When you use DB data types, refer to the database-specific SAP notes for DB Connect shown below. You can use database views to convert data types, if necessary. For more information see Database Users and Database Schemas .
List of Database-Specific SAP Notes
If you are using an MSS database, refer to SAP Note 512739.
Oracle database, refer to SAP Note 518241.
SAP DB or MaxDB database, refer to SAP Note 520647.
IBM DB2/390 database, refer to SAP Note 523552.
IBM DB2/400 database, refer to SAP Note 523381.
IBM DB2 UDB database, refer to SAP Note 523622.