Filtering data allows you to execute queries against smaller datasets and boost performance. The author describes two techniques for doing so that are not commonly used: filtering records coming out of R/3 and filtering in update or transfer rules. Both require ABAP code, but offer the ability to use more complex selection criteria.
Data-modeling techniques suggest splitting data into smaller datasets to improve performance while still satisfying user requirements. Often, using simple selection criteria is not enough to achieve the proper segregating of data. In this case, you must implement complex filters to segregate data when loading from R/3 to BW, BW to SAP’s Advanced Planning and Optimization (APO) application, or even to an ODS or InfoCube within the same BW system.
The goal is to remove irrelevant data from the dataset. The primary tools to filter data are the selection criteria fields in the InfoPackage Selection tab (Figure 1). Sometimes you need a more dynamic approach to determine the filter criterion, such as when the filters differ each time the data load takes place. Custom ABAP routines in InfoPackages allow for a more dynamic selection. This is useful if the selection criterion can change for each data load or the selection criterion is too complex. The article, “Transform an InfoPackage into a Smart Extraction Tool,” explains how to use this method.
Figure 1
The InfoPackage selection criteria fields
You have two other options that might meet your filtering requirements:
Tip!
InfoPackage selection routines are stored only in the InfoPackage, which is not transported and typically can be deleted by a user. If an InfoPackage is deleted, the routine is gone. It is a good idea to make a backup of this code.
- Filter records coming out of R/3. This technique takes advantage of an ABAP user exit to filter or flag data on the R/3 source system.
- Filter in the update or transfer rules. Filters are applied during the loading process, and they allow for selection of data using ABAP code.
Although both methods require that you or an ABAPer on your team write code, they are relatively easy to set up. I’ll tell you when to use each option and show you how to carry out each one.
Filter Records from R/3
You can apply a filter in the R/3 source system to eliminate data that does not need to go to BW. This reduces network traffic and boosts performance by sending a smaller extracted dataset. For example, Company A has 40 million invoice records in R/3. Only one division is relevant to BW. You need to reduce the 40 million records to 300,000 records representing the one division. Using selection criteria in a BW InfoPackage would bring all 40 million records over to BW for filtering there, generating a great deal of unnecessary network traffic. To eliminate the unneeded records, you can apply a filter in R/3 via custom ABAP code in an SAP-supplied user exit.
Tip!
InfoPackage selection routines are stored only in the InfoPackage, which is not transported and typically can be deleted by a user. If an InfoPackage is deleted, the routine is gone. It is a good idea to make a backup of this code.
Use transaction CMOD in your R/3 system to implement the user exit (Figure 2). Create a user- defined project. Assign enhancement component RSAP0001 to the project. RSAP0001 can be used to enhance or modify data used in the extraction engine. The user exit function EXIT_SAPLRSAP_001 is called from within RSAP0001 whenever a transactional DataSource extracts data. (See transaction SMOD for details on this component, function, and sample ABAP code.) All code is added to the user exit program included in the component function. Add the ABAP user exit code shown in Figure 3 to program ZXRSAU01. Be sure to activate the project when the code is completed (Figure 4).
Figure 2
Create your project
data L_S_MC11VA0ITM like MC11VA0ITM,
L_tabix like sy-tabix.
case i_datasource.
when '2LIS_11_VAITM'.
loop at c_t_data into L_S_MC11VA0ITM.
l_tabix = sy-tabix.
if L_S_MC11VA0ITM-SPART ne '10'.
Else.
delete c_t_data index l_tabix.
continue.
endif.
modify c_t_data from L_S_MC11VA0ITM index l_tabix.
endloop.
Endcase.
|
| Figure 3 |
Program ZXRSAU01 user exit to filter records in R/3 |
|
In the example, you want to remove all values from the extracted data on R/3 prior to reaching BW for those records that are not division (SPART) ‘10’. The added code in ZXRSAU01 removes values from the internal table C_T_DATA when they are not SPART = ‘10’. It loops through table C_T_DATA, which houses all extracted data during the extraction process, looking at each record due for extraction. Any record removed from C_T_DATA is no longer in the extraction data package and is not brought over to the BW system. The continue statement moves the system onto the next record after deletion. It is important to keep the indexes updated properly using sy-tabix.
Tip!
To troubleshoot the user exit code, use a breakpoint in ZXRSAU01 and test the transactional load using transaction RSA3 (Figure 5) on the R/3 system. The system stops the extraction at the breakpoint, allowing you to single-step through the process. You can then see which records are being filtered. You can also run RSA3 to see how specific records are filtered.
RSAP0001ZXRSAU01For example, you may create an InfoCube in BW to show “strategic” sales — sales of large value or from certain high-profile customers. The list of high-profile customers is stored in a custom Z-table in R/3. A flag for these sales is added to the export DataSource. All sales are extracted and loaded to a BW ODS. However, records with the “strategic” flag are loaded into a specific InfoCube. A BW InfoPackage is used to filter the values using the flag once in BW. The new field allows for flagging and validating data on R/3, thus enabling easy segregation on the BW system. This is particularly useful if the filter’s determining factors are not available in BW.
Figure 4
Activate the project after adding the user exit code
Tip!
Newly appended fields are automatically hidden. You must manually remove SAP’s hide flag in transaction RSA6 to use the appended field. If this flag is not removed, the system does not recognize the new field in the DataSource.
RSA6RSAP0001ZXRSAU01YNY
Figure 5
Use transaction RSA3 to test the transactional load
Tip! Do not forget to replicate DataSources whenever DataSource fields are added or removed
The user exit ZXRSAU01 is also valid when exporting data from BW to APO, from BW to mySAP Customer Relationship Management (CRM), or from one BW instance to another using export DataSources. For example, large data volumes may exist in BW, but only APO-relevant data should be extracted from BW to the APO system. A filtering technique could be applied to BW data during extraction using an export DataSource. The system also uses the user exit ZXRSAU01 when extracting data from BW. To implement, follow the steps outlined above. Substitute a BW export DataSource in place of the R/3 DataSource. Apply ABAP code in BW using transaction CMOD to a BW project and user exit ZXRSAU01. This code is on the BW system because the goal is to reduce data volume on the source system. In this case, BW is the source system.
Tip!
SAP development does not recommend adding fields to a BW export DataSource via append structures. Thus, the technique for adding fields to BW DataSources is not the same as adding fields to DataSources in the R/3 system. The export DataSource was not designed to have append structures. To append fields to a BW DataSource, add them to the InfoCube or ODS and do not fill them in update rules. The fields remain empty in the InfoCube or ODS and are in the generated export DataSource. These fields can then be filled upon extraction.
EXIT_SAPLRSAP_002EXIT_SAPLRSAP_003ZXRSAU02Filter in Start Routine of Update or Transfer Rules
Complex criteria can be added to filter data via transfer rules or loading to an InfoCube or ODS via update rules. For example, the custom attribute APO relevant appears on the master data table for 0MATERIAL. This designates master data records that are planned in APO. You might need to load only these records from an ODS to an InfoCube. However, some records in the ODS have both Y and N flags for APO. Thus, the master data attributes of each record need to be examined to determine the record’s relevancy for the InfoCube.
data: i_out_package like DATA_PACKAGE,
l_tabix like sy-tabix.
Tables: /BI0/PMATERIAL
loop at DATA_PACKAGE.
l_tabix = sy-tabix.
Select single /bic/z_aporel into /bio/pmaterial-/bic/z_aporel from
/bio/pmaterial where material = DATA_PACKAGE-MATERIAL and
OBJVER = ‘A’.
If /bio/pmaterial-/bic/z_aporel = ‘Y’.
else.
delete DATA_PACKAGE index l_tabix.
continue.
endif.
modify DATA_PACKAGE.
endloop.
ABORT = 0. |
| Figure 6 |
Start routine to filter records |
|
Tip!
To troubleshoot the start routine, add a breakpoint to it and simulate the data load when loading from the PSA. Right-click on the data package you want in the PSA and choose Simulate/ Cancel Update (Figures 7 and 8).
A start routine in the update rules can filter the records, leaving only those records that are relevant (Figure 6). During data load, the full data package is stored in an internal table called DATA_PACKAGE. To filter the records, you need to remove values from the internal table. The start routine loops through DATA_PACKAGE, selects out the material master table /BIO/ PMATERIAL to determine if the record’s /BIC/Z_APOREL (APO relevant flag) is set to Y. If it is, the record is ignored; if not, the record is removed from DATA_PACKAGE.
Figure 7
Select the data package in the PSA, and then select Simulate/Cancel Update
Figure 8
Choose Simulate Update to begin simulating the data load
Tip!
Update rules allow the use of RETURNCODE in characteristic routines to skip records. It is often better for performance to use a start routine. Start routines allow all filters to be in one place, and can prevent inefficient looping through the same code on each record.
