In this tip, we describe the method to optimize the parallel or non-parallel data loading process into cube in SAP BW system. This method is based on the algorithm the SAP BW uses to load data within the cube fact table partition .
To understand the optimization method, it's required to explain the steps the SAP BW system uses before starting insertion (within the loading process) of rows of data into the fact table.
Some of these steps which are important to understand our method are lised below:
- The command in format "
ALTER TABLE <FACT TABLE> MODIFY PARTITION <PARTITION NAME> REBUILD UNUSABLE LOCAL INDEXES" is applied to the fact table "
<FACT TABLE>" within the partition "
<PARTITION NAME>" before insertion.
As you see, this command rebuilds the data-storage local indexes in the specific partition of fact table. If we would instead of this command apply the DDL-command to set the partition local indexes unusable, this should be faster for about 3 times .
To do this, we can catch the command described in the 1st step in a specific DDL-trigger and replace it with the command to set local indexes unusable .
After the data-loading into cube is complete, the local indexes of the fact table newly created partitions can be finally rebuilt.
Using the Code
The DDL-trigger code is as follows:
CREATE OR REPLACE TRIGGER before_ddl
AFTER ALTER ON SAPSR3.schema
n_JOB NUMBER; c_TEXT VARCHAR2 (1000); sql_TEXT ORA_NAME_LIST_T; i PLS_INTEGER; v_CUBE VARCHAR2 (100) := 'X_CUBE'; BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LENGTH_SEMANTICS = ''BYTE''';
i := ora_sql_txt (sql_TEXT);
FOR l IN 1..i LOOP
c_TEXT := c_TEXT || sql_TEXT (l);
c_TEXT := SUBSTR (c_TEXT, 1, LENGTH (c_TEXT) - 1);
(SYS.DBMS_STANDARD.DICTIONARY_OBJ_OWNER = 'SAPSR3')
AND (SYS.DBMS_STANDARD.DICTIONARY_OBJ_TYPE = 'TABLE')
AND (SYS.DBMS_STANDARD.DICTIONARY_OBJ_NAME = '/BIC/F' || v_CUBE)
AND UPPER (c_TEXT) LIKE '%PARTITION% %REBUILD%'
c_TEXT := REPLACE (c_TEXT,'"/BIC/F' || v_CUBE || '"', 'SAPSR3."/BIC/F' || v_CUBE || '" ');
c_TEXT := REPLACE (c_TEXT, 'REBUILD');
job => n_JOB
, next_date => SYSDATE + 1 / (24 * 60 * 60)
, what => 'BEGIN EXECUTE IMMEDIATE '''|| c_TEXT ||'''; END;'
In this code, the DDL-trigger "
before_ddl" is created on the schema "
SAPSR3" (the default schema where the fact tables and other SAP BW system objects are stored). The cube name is parametrized in a variable
In this tip, this optimization method is applied to the cube
"X_CUBE". Of course, this can be a universal method with some checkpoints for a list of cubes. The final checkpoint is required to rebuild all the local indexes after the data-loading process is complete.
Please send your opinion about correctness and reliability of the optimization method described.
- SAP BW courses 310, 330