Skip to Content
Load lots of data “Faster” with Buffering Number Range  I have been loading lots of data in to SAP BI, and have taken care of almost every thing suggested by BI Gurus. Here are few of those suggestions –   •     Check the parameter settings of the database.  •     Import the latest BW Support Package and the latest kernel patch into the system.  •     Before loading the transaction data make sure that ALL relating master data has been loaded to BI system. •     Load the data from a file on the application server and not from the client workstation as this reduces the network load. •     Use a fixed record length when load data from a file (ASCII file). For a CSV file, the system only carries out the conversion to a fixed record length during the loading process. •     Always use TRFC (PSA) as the transfer method instead of IDocs.  •     When loading large data quantities from a file, split the file into several parts. Recommendation is using as many files of the same size as there are CPUs. Load these files simultaneously to the BW system in several requests •     When loading large quantities of data in InfoCubes, delete secondary indexes of Cube.  As Lance Armstrong (famous cycling phenomenon) rightly named sequel to his autobiography (It’s Not about the Bike: My Journey Back to Life) “Every Second Counts”… when it comes to loading millions of records…for me and my clients also every Second counts. And I gasp for more and more… to save every second in my data loading process.   I wish I could do more …Save few more seconds …. And to my surprise there was some thing more I could do … Which was nothing but –   “But Number Buffering”   •     So what is this concept? image It is a pretty simple concept “Buffer the Numbers ( which system picks up from database -in case of Master Data – SIDs & in case of Transaction Data Dimension Ids) and use these buffered numbers rather than hitting the database (for each new master data records or for each new combination of SIDs to create a new Dimension Ids). So the concept is –   “SID Number Range can be Buffered Instead of Accessing the Database for Each SID”  •     How do I do it for InfoCube Data Loading?  a)     When loading large quantities of data in an InfoCube, the number range buffer should be increased for the dimensions that are likely to have a high number of data sets.  b)     Use function module RSD_CUBE_GET to find the object name of the dimension that is likely to have a high number of data sets. Go to SE37 & put in the function name. image c)     Enter the following in function module settings –  •     I_INFOCUBE = ‘InfoCube Name’ •     I_OBJVERS = ‘A’ •     I_BYPASS_BUFFER = ‘X’ •     And Execute.   image d)     The numbers for the dimensions are contained in table ‘E_T_DIME’. Double click on it to see the dimensions. image e)     Go to Column “NOBJECT”, you get the relevant number range (for example BID0002145). image f)     Move Right.  image  g)     Use Transaction SNRO to display number range for the dimension used in BI (BID0002145). By double-clicking this line, you get to the number range maintenance. image h)     Choose Edit -> Set-up buffering -> Main memory, to define the ‘No. of numbers in buffer’. image i)     Set this value to 500, for example. The size depends on the expected data quantity in the initial and in future (delta) uploads. image  •     How do I do it for Master Data Loading?  a)     Use function module RSD_IOBJ_GET to find the object name of the dimension that is likely to have a high number of data sets. Go to SE37 & put in the function name.  b)     Enter the following in function module settings –  •     I_IOBJNM = ‘InfoObject Name’ •     I_OBJVERS = ‘A’ •     I_BYPASS_BUFFER = ‘X’ •     And Execute.  c)     The number for the info object is in table ‘E_S_VIOBJ’, column ‘NUMBRANR’. Enter ‘BIM’ in front of this number to get the required number range (for example BIM0000649)  d)     Use Transaction SNRO to display number range for InfoObject used in BI (BIM0000649). By double-clicking this line, you get to the number range maintenance.  e)     Choose Edit -> Set-up buffering -> Main memory, to define the ‘No. of numbers in buffer’.  f)     Set this value to 500, for example. The size depends on the expected data quantity in the initial and in future (delta) uploads.  •     Word of Caution!!!  a)     Don’t buffer the number range for the Package Dimension.  b)     Don’t buffer the number range object for the Characteristic 0REQUEST.  c)     If possible, reset it to its original state after the load in order to avoid unnecessary memory allocation.  
To report this post you need to login first.

33 Comments

You must be Logged on to comment or reply to a post.

  1. Anonymous
    “Increase Load Performance by Buffering Number Ranges” by Gary Nolan (Independent BI Strategic Architect) in the BWEXPERTONLINE first issue of 2006.
    The only difference ?
    Here it is free!

    Good and clear explanation.
    (Maybe next year I can save something on the subscription fee…)

    Bye,
    Roberto

    (0) 
  2. Anonymous
    “Increase Load Performance by Buffering Number Ranges” by Gary Nolan (Independent BI Strategic Architect) in the BWEXPERTONLINE first issue of 2006.
    The only difference ?
    Here it is free!

    Good and clear explanation.
    (Maybe next year I can save something on the subscription fee…)

    Bye,
    Roberto

    (0) 
  3. Anonymous
    “Increase Load Performance by Buffering Number Ranges” by Gary Nolan (Independent BI Strategic Architect) in the BWEXPERTONLINE first issue of 2006.
    The only difference ?
    Here it is free!

    Good and clear explanation.
    (Maybe next year I can save something on the subscription fee…)

    Bye,
    Roberto

    (0) 
  4. Anonymous
    Hi,
    Very nice explanation.  I already have number ranges buffering setup for some of my IOBJs and Cube Dims.  I just want to know what the other options in the Edit-> Set-up buffering means.  Mainly “Parallel” and “Loc.file + proc. ID”

    Thanks

    (0) 
  5. Anonymous
    Hi,
    Very nice explanation.  I already have number ranges buffering setup for some of my IOBJs and Cube Dims.  I just want to know what the other options in the Edit-> Set-up buffering means.  Mainly “Parallel” and “Loc.file + proc. ID”

    Thanks

    (0) 
  6. Anonymous
    Hi,
    Very nice explanation.  I already have number ranges buffering setup for some of my IOBJs and Cube Dims.  I just want to know what the other options in the Edit-> Set-up buffering means.  Mainly “Parallel” and “Loc.file + proc. ID”

    Thanks

    (0) 
  7. Jens Schwarz
    Hi,

    thanks alot for this nice explanation. I wrote a report for this, to make changes easier:

    REPORT zsap_infocube_buffering .

    TYPE-POOLS: rsd.

    TABLES: rsdiobjv, tnro.

    PARAMETERS: icube    TYPE rsd_infocube.
    PARAMETERS: p_bsize  TYPE tnro-noivbuffer default ‘1000’.
    PARAMETERS: p_set RADIOBUTTON GROUP gr1,
                p_del RADIOBUTTON GROUP gr1.
    PARAMETERS: p_update TYPE c AS CHECKBOX.

    START-OF-SELECTION.

      DATA: l_t_dime  TYPE rsd_t_dime,
            w_t_dime  LIKE rsd_s_dime.

      CALL FUNCTION ‘RSD_CUBE_GET’
        EXPORTING
          i_infocube      = icube
          i_objvers       = ‘A’
          i_bypass_buffer = ‘X’
        IMPORTING
          e_t_dime        = l_t_dime.

      LOOP AT l_t_dime INTO w_t_dime
          WHERE iobjtp = ‘CHA’.
        SELECT SINGLE * FROM tnro WHERE object = w_t_dime-nobject.
        IF p_set = ‘X’.
          tnro-buffer     = ‘X’.
          tnro-noivbuffer = ‘1000’.
          WRITE :/ w_t_dime-infocube,
                   w_t_dime-dimension,
                   w_t_dime-txtsh,
                   tnro-object,
                   tnro-buffer,
                   tnro-noivbuffer.
        ELSE.
          CLEAR tnro-buffer.
          CLEAR tnro-noivbuffer.
          WRITE :/ w_t_dime-infocube,
                   w_t_dime-dimension,
                   w_t_dime-txtsh,
                   tnro-object,
                   tnro-buffer,
                   tnro-noivbuffer.
        ENDIF.
        IF p_update = ‘X’.
          UPDATE tnro.
        ENDIF.
      ENDLOOP.

    END-OF-SELECTION.

    (0) 
  8. Jens Schwarz
    Hi,

    thanks alot for this nice explanation. I wrote a report for this, to make changes easier:

    REPORT zsap_infocube_buffering .

    TYPE-POOLS: rsd.

    TABLES: rsdiobjv, tnro.

    PARAMETERS: icube    TYPE rsd_infocube.
    PARAMETERS: p_bsize  TYPE tnro-noivbuffer default ‘1000’.
    PARAMETERS: p_set RADIOBUTTON GROUP gr1,
                p_del RADIOBUTTON GROUP gr1.
    PARAMETERS: p_update TYPE c AS CHECKBOX.

    START-OF-SELECTION.

      DATA: l_t_dime  TYPE rsd_t_dime,
            w_t_dime  LIKE rsd_s_dime.

      CALL FUNCTION ‘RSD_CUBE_GET’
        EXPORTING
          i_infocube      = icube
          i_objvers       = ‘A’
          i_bypass_buffer = ‘X’
        IMPORTING
          e_t_dime        = l_t_dime.

      LOOP AT l_t_dime INTO w_t_dime
          WHERE iobjtp = ‘CHA’.
        SELECT SINGLE * FROM tnro WHERE object = w_t_dime-nobject.
        IF p_set = ‘X’.
          tnro-buffer     = ‘X’.
          tnro-noivbuffer = ‘1000’.
          WRITE :/ w_t_dime-infocube,
                   w_t_dime-dimension,
                   w_t_dime-txtsh,
                   tnro-object,
                   tnro-buffer,
                   tnro-noivbuffer.
        ELSE.
          CLEAR tnro-buffer.
          CLEAR tnro-noivbuffer.
          WRITE :/ w_t_dime-infocube,
                   w_t_dime-dimension,
                   w_t_dime-txtsh,
                   tnro-object,
                   tnro-buffer,
                   tnro-noivbuffer.
        ENDIF.
        IF p_update = ‘X’.
          UPDATE tnro.
        ENDIF.
      ENDLOOP.

    END-OF-SELECTION.

    (0) 
  9. Jens Schwarz
    Hi,

    thanks alot for this nice explanation. I wrote a report for this, to make changes easier:

    REPORT zsap_infocube_buffering .

    TYPE-POOLS: rsd.

    TABLES: rsdiobjv, tnro.

    PARAMETERS: icube    TYPE rsd_infocube.
    PARAMETERS: p_bsize  TYPE tnro-noivbuffer default ‘1000’.
    PARAMETERS: p_set RADIOBUTTON GROUP gr1,
                p_del RADIOBUTTON GROUP gr1.
    PARAMETERS: p_update TYPE c AS CHECKBOX.

    START-OF-SELECTION.

      DATA: l_t_dime  TYPE rsd_t_dime,
            w_t_dime  LIKE rsd_s_dime.

      CALL FUNCTION ‘RSD_CUBE_GET’
        EXPORTING
          i_infocube      = icube
          i_objvers       = ‘A’
          i_bypass_buffer = ‘X’
        IMPORTING
          e_t_dime        = l_t_dime.

      LOOP AT l_t_dime INTO w_t_dime
          WHERE iobjtp = ‘CHA’.
        SELECT SINGLE * FROM tnro WHERE object = w_t_dime-nobject.
        IF p_set = ‘X’.
          tnro-buffer     = ‘X’.
          tnro-noivbuffer = ‘1000’.
          WRITE :/ w_t_dime-infocube,
                   w_t_dime-dimension,
                   w_t_dime-txtsh,
                   tnro-object,
                   tnro-buffer,
                   tnro-noivbuffer.
        ELSE.
          CLEAR tnro-buffer.
          CLEAR tnro-noivbuffer.
          WRITE :/ w_t_dime-infocube,
                   w_t_dime-dimension,
                   w_t_dime-txtsh,
                   tnro-object,
                   tnro-buffer,
                   tnro-noivbuffer.
        ENDIF.
        IF p_update = ‘X’.
          UPDATE tnro.
        ENDIF.
      ENDLOOP.

    END-OF-SELECTION.

    (0) 
  10. Witalij Rudnicki
    Is it a kind of change to be done directly in Production or it has to be done in Development and then transported?

    If it is to be done directly in Production – is any special authorization needed?

    Thank you
       Vitaliy

    (0) 
    1. Mangesh Kulkarni
      These changes can not be transported as the number ranges in each system are generated automatically i.e. the number Range ID BID0002145 would be different in different systems.

      Hence you need to do these changes in each of the BW system where you want to improve the load performance.

      (0) 
  11. Witalij Rudnicki
    Is it a kind of change to be done directly in Production or it has to be done in Development and then transported?

    If it is to be done directly in Production – is any special authorization needed?

    Thank you
       Vitaliy

    (0) 
    1. Mangesh Kulkarni
      These changes can not be transported as the number ranges in each system are generated automatically i.e. the number Range ID BID0002145 would be different in different systems.

      Hence you need to do these changes in each of the BW system where you want to improve the load performance.

      (0) 
  12. Witalij Rudnicki
    Is it a kind of change to be done directly in Production or it has to be done in Development and then transported?

    If it is to be done directly in Production – is any special authorization needed?

    Thank you
       Vitaliy

    (0) 
    1. Mangesh Kulkarni
      These changes can not be transported as the number ranges in each system are generated automatically i.e. the number Range ID BID0002145 would be different in different systems.

      Hence you need to do these changes in each of the BW system where you want to improve the load performance.

      (0) 
  13. Eric Niedling
    Dear Agrawal,

    Thanks for the nice article, in fact most of seems to be adapted from SAP note 130253 – General tips on uploading transaction data to BW
    Isn’t it?

    Out of courtesy, you should reference SAP notes and other material which you used.

    (0) 
    1. Vikash Agrawal Post author
      Hi Eric,

      Thanks for your comment.

      SAP note suggested by you wasn’t the only source for this weblog.

      I completely agree with you to mention refrences & will keep in mind for future weblogs for sure. I am not sure whether I can edit it to put references now.

      It was my first weblog and I see myself moving on learning curve with critical inputs from friends like you.

      Thanks again

      Vikash

      (0) 
  14. Eric Niedling
    Dear Agrawal,

    Thanks for the nice article, in fact most of seems to be adapted from SAP note 130253 – General tips on uploading transaction data to BW
    Isn’t it?

    Out of courtesy, you should reference SAP notes and other material which you used.

    (0) 
    1. Vikash Agrawal Post author
      Hi Eric,

      Thanks for your comment.

      SAP note suggested by you wasn’t the only source for this weblog.

      I completely agree with you to mention refrences & will keep in mind for future weblogs for sure. I am not sure whether I can edit it to put references now.

      It was my first weblog and I see myself moving on learning curve with critical inputs from friends like you.

      Thanks again

      Vikash

      (0) 
  15. Eric Niedling
    Dear Agrawal,

    Thanks for the nice article, in fact most of seems to be adapted from SAP note 130253 – General tips on uploading transaction data to BW
    Isn’t it?

    Out of courtesy, you should reference SAP notes and other material which you used.

    (0) 
    1. Vikash Agrawal Post author
      Hi Eric,

      Thanks for your comment.

      SAP note suggested by you wasn’t the only source for this weblog.

      I completely agree with you to mention refrences & will keep in mind for future weblogs for sure. I am not sure whether I can edit it to put references now.

      It was my first weblog and I see myself moving on learning curve with critical inputs from friends like you.

      Thanks again

      Vikash

      (0) 
  16. MANISH ANGARAI
    Nice blog.
    Question: After reading your blog,BWExpert article by Gary and information on http://help.sap.com/saphelp_nw04/helpdata/en/aa/dbc9b4b56143bb8f2ae909d7d040fa/content.htm
    Reseting the buffer is the recommendation once data load is done. But confusion is, even after deactivating the buffer for objects via SNRO (No Buffering), noticed that Buffer entries for the same objects are still sitting in SM56 ‘Number Range Buffer Statistics’ and never go away until and unless system is cycled or bounced. Why is that? Do those entries hurt system’s shared memory buffer in any sense. Or Reseting could only be done via SM56 – Reset Buffer (Global or Local)?. How is it handled or best SAP recommendation to handle that? You help would be appreciated.
    Thanks.
    (0) 
  17. MANISH ANGARAI
    Nice blog.
    Question: After reading your blog,BWExpert article by Gary and information on http://help.sap.com/saphelp_nw04/helpdata/en/aa/dbc9b4b56143bb8f2ae909d7d040fa/content.htm
    Reseting the buffer is the recommendation once data load is done. But confusion is, even after deactivating the buffer for objects via SNRO (No Buffering), noticed that Buffer entries for the same objects are still sitting in SM56 ‘Number Range Buffer Statistics’ and never go away until and unless system is cycled or bounced. Why is that? Do those entries hurt system’s shared memory buffer in any sense. Or Reseting could only be done via SM56 – Reset Buffer (Global or Local)?. How is it handled or best SAP recommendation to handle that? You help would be appreciated.
    Thanks.
    (0) 
  18. MANISH ANGARAI
    Nice blog.
    Question: After reading your blog,BWExpert article by Gary and information on http://help.sap.com/saphelp_nw04/helpdata/en/aa/dbc9b4b56143bb8f2ae909d7d040fa/content.htm
    Reseting the buffer is the recommendation once data load is done. But confusion is, even after deactivating the buffer for objects via SNRO (No Buffering), noticed that Buffer entries for the same objects are still sitting in SM56 ‘Number Range Buffer Statistics’ and never go away until and unless system is cycled or bounced. Why is that? Do those entries hurt system’s shared memory buffer in any sense. Or Reseting could only be done via SM56 – Reset Buffer (Global or Local)?. How is it handled or best SAP recommendation to handle that? You help would be appreciated.
    Thanks.
    (0) 

Leave a Reply