Database Tables As Destinations in BW BI

Use

You can select a database table as an open hub destination.

Features

Deleting Data from the Table

With an extraction to a database table, you can either retain the history of the data or just store the new data in the table. Choose Delete Table Before Extraction when defining your destination if you want to overwrite the fields. In this case, the table is completely deleted and regenerated before each extraction takes place. We recommend that you use this mode if you do not want to store the history of the data in the table. If you do not select this option, the system only generates the table once before the first extraction. We recommend that you use this mode if you want to retain the history of the extracted data.

Generating Database Tables

The generated database table has the prefix /BIC/OHxxx (xxx is the technical name of the destination).

How the database table is generated differs depending on whether you use a BAdI for the transformation:

...

1. If you do not use a BAdI, the table is created with the field list of the extract structure for the InfoSpoke (prefix /BIC/CY).

2. If you do use a BAdI, the table is created with the field list of the target structure for the BAdI. If the field list for your database table differs from the generated extract structure, you should create your own target structure for each BAdI and then change the field list using dictionary maintenance.

...

Table Key Fields

Note that when extracting to a database table, the generated DB table can have a maximum of 16 key fields. Check whether your field list includes too many key fields. If this is the case, you can set the Technical Key indicator. A unique key is then added that consists of the technical fields OHREQUID (open hub request SID), DATAPAKID (data package ID), and RECORD (running number of a data record to be added to the table within a data package). These fields display the individual key fields for the table.

Using a target table with a technical key is useful in the following instances:

...

1. Delta extraction from DataStore objects: the data is read in the change log in which several records with different RECORDMODEs can exist for a specific semantic key (after/before image). Otherwise extracting to a table with the semantic key of the DataStore object leads to duplicate records and, ultimately, a short dump.

2. Delta extraction from an InfoCube: first the system reads records from the fact table (F table) and then records from the table of compressed InfoCube data (E table). If there are records for a specific characteristic key in both the F table and the E table, a short dump is produced in the open hub target table when the system tries to add the record from the E table. To solve this problem you can either set the Technical Key indicator or, as an alternative, compress all data found in the InfoCube before extraction.

3. Extracting in full mode to a table that does not need to be deleted before the extraction: if, for an extracted record, a record already exists with the same key, a short dump is produced because of the duplicate records.

4. If the source has more than 16 key fields, no DB table can be created with these key fields. In this case, the table has to include a technical key.