Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

How to Optimize Data Loading into Cube in SAP BW using DDL-triggers

, 21 Apr 2014
Rate this:
Please Sign up or sign in to vote.
Optimization of data loading based on partition unusable indexes

Introduction

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 [1].

Background

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:

  1. 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 [2].

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 [3].

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
DECLARE
  n_JOB   NUMBER; -- job id
  c_TEXT  VARCHAR2 (1000);  -- text of sql clause
  sql_TEXT ORA_NAME_LIST_T; -- sql text list
  i        PLS_INTEGER;     -- sql text list index
  v_CUBE   VARCHAR2 (100) := 'X_CUBE'; -- name of the cube
BEGIN
  -- set nls parameters to process text data
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LENGTH_SEMANTICS = ''BYTE''';
  
  -- get the text of DDL-sql clause
  i := ora_sql_txt (sql_TEXT);
  FOR l IN 1..i LOOP
    c_TEXT := c_TEXT || sql_TEXT (l);
  END LOOP;
  
  -- delete the end of file character
  c_TEXT := SUBSTR (c_TEXT, 1, LENGTH (c_TEXT) - 1);
  
  IF
    (
      (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%'
    )
  THEN 
    -- set the full specification of fact table name in sql text clause
    c_TEXT := REPLACE (c_TEXT,'"/BIC/F' || v_CUBE || '"', 'SAPSR3."/BIC/F' || v_CUBE || '" ');
    -- remove the "REBUILD" keyword
    c_TEXT := REPLACE (c_TEXT, 'REBUILD');
    -- run the DDL-statement in a database job
    --    after 1 seconds to set the local indexes unusable
    SYS.DBMS_JOB.submit
    (
      job => n_JOB
      , next_date => SYSDATE + 1 / (24 * 60 * 60)
      , what => 'BEGIN EXECUTE IMMEDIATE '''|| c_TEXT ||'''; END;'
    );
  END IF;
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 v_CUBE.

Further Discussions

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.

References

  1. SAP BW courses 310, 330
  2. http://www.toadworld.com/platforms/oracle/w/wiki/4475.alter-table-partition-examples-pc82.aspx
  3. http://jonathanlewis.wordpress.com/2012/09/08/ddl-triggers/

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

rbtinf

Kazakstan Kazakstan
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 21 Apr 2014
Article Copyright 2014 by rbtinf
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid