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? 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. c) Enter the following in function module settings – • I_INFOCUBE = ‘InfoCube Name’ • I_OBJVERS = ‘A’ • I_BYPASS_BUFFER = ‘X’ • And Execute. d) The numbers for the dimensions are contained in table ‘E_T_DIME’. Double click on it to see the dimensions. e) Go to Column “NOBJECT”, you get the relevant number range (for example BID0002145). f) Move Right. 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. h) Choose Edit -> Set-up buffering -> Main memory, to define the ‘No. of numbers in buffer’. i) Set this value to 500, for example. The size depends on the expected data quantity in the initial and in future (delta) uploads. • 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.