How to improve the performance of data loading in ODS.

[Former Member]

Hi all,

Will you please tell me , how to improve the performance of data loading in ODS.

Thanks,

Vincent

[Former Member]

Hi Antony,

To ensure a good ODS object loading performance, take the following into account:

1.Initialization, then deltas

To load data into an ODS object, initialize first and then load deltas. Also note that the table for new data must not have more than one million data records. If necessary, carry out the initialization in several steps, using selection criteria.

2.Avoid SIDs

Extracting SIDs takes a long time and can be avoided by:

Not setting the indicator for BEx Reporting if you are using the ODS object only as a data store. If you do set this indicator, SIDs are extracted for all new characteristic values.

If you use line items (for example, document number, time stamp) as characteristics in the ODS object, in the characteristic maintenance, flag these as "Attribute Only".

3.DB partitioning in active data tables (technical A table)

By partitioning by database level, you can delete data from the ODS object much more quickly. As a partitioning criterion, choose the characteristic by which you want to delete

4.Indexing

Use selection criteria for queries for ODS objects. If the key fields are specified, the existing primary index is used. The more frequently accessed characteristic should appear on the left-hand side.

Hope this helps...

Best Regards,

DMK

*Assign point if it serves your purpose...

[edwin_harpino]

hi Antony,

take a look oss 565725-Optimizing the performance of ODS objects in BW 3.0B (or 384023 if you are in 2.x)

Symptom

This note contains recommendations for improving the load performance of ODS objects in Business Information Warehouse Release 3.0B and 3.1 Content.

Other terms

Business Information Warehouse, ODS object, BW, RSCUSTA2, RSADMINA

Solution

To obtain a good load performance for ODS objects, we recommend that you note the following:

1. Activating data in the ODS object

In the Implementation Guide in the BW Customizing, you can implement different settings under Business Information Warehouse -> General BW settings -> Settings for the ODS object that will improve performance when you activate data in the ODS object.

2. Creating SIDs

The creation of SIDs is time-consuming and may be avoided in the following cases:

a) You should not set the indicator for BEx Reporting if you are only using the ODS object as a data store.Otherwise, SIDs are created for all new characteristic values by setting this indicator.

b) If you are using line items (for example, document number, time stamp and so on) as characteristics in the ODS object, you should mark these as 'Attribute only' in the characteristics maintenance.

SIDs are created at the same time if parallel activation is activated (see above).They are then created using the same number of parallel processes as those set for the activation. However:if you specify a server group or a special server in the Customizing, these specifications only apply to activation and not the creation of SIDs.The creation of SIDs runs on the application server on which the batch job is also running.

3. DB partitioning on the table for active data (technical name:

The process of deleting data from the ODS object may be accelerated by partitioning on the database level.Select the characteristic after which you want deletion to occur as a partitioning criterion.For more details on partitioning database tables, see the database documentation (DBMS CD).Partitioning is supported with the following databases:Oracle, DB2/390, Informix.

4. Indexing

Selection criteria should be used for queries on ODS objects.The existing primary index is used if the key fields are specified.As a result, the characteristic that is accessed more frequently should be left justified.If the key fields are only partially specified in the selection criteria (recognizable in the SQL trace), the query runtime may be optimized by creating additional indexes.You can create these secondary indexes in the ODS object maintenance.

5. Loading unique data records

If you only load unique data records (that is, data records with a one-time key combination) into the ODS object, the load performance will improve if you set the 'Unique data record' indicator in the ODS object maintenance.

[Former Member]

Hi Antony,

Have a look.

Performance Optimization for ODS Objects

http://help.sap.com/saphelp_nw04/helpdata/en/e6/bb01580c9411d5b2df0050da4c74dc/content.htm

Hope it Helps

Srini