Wednesday, August 23, 2017

Sequence of loading data in SAP BW

While loading data into SAP BW systems one aspect of it comes to the picture. It is the one related to sequence of data loading. Normally in general the data loading sequence shall be like following. Master data loads come first followed by transactional data loads.

Loading sequence within the master data (MD) shall be as follows. First to load MD attributes then texts and finally hierarchies. But the attributes and texts can be loaded in parallel. Due to more complex operation while loading of the hierarchies are going on I suggest to put the hierarchy loads as last step of MD loads.

Now on topic of loading MD before transaction data (TD). All MD shall be loaded before TD is loaded. If it is not the case the TD loads triggers insertion of MD rather empty master data entry. What is meant by insertion here is that SID needs to be generated for the characteristic attributes, and new records have to be inserted in the MD tables. 

This can cause performance issues as the load can take twice as much due to inserting the MD during load of TD. Also if there are parallel loads in place there may locks appear in SID table update. Another issue that can happen is while there are master data lookup in the transformation of the TD. If the MD is not present then there is nothing to be looked up and as a result there is a data quality issue.

More information:
652856 - Loading hierarchies with process chains

1597364 - FAQ: BW-BCT: Extraction performance in source system

No comments: