Q1. Explain the difference between Start Routine, End Routine, and Expert Routine. When would you use each?
Ans: We have various routines to manipulate or process the data, depending on when and how we want to act on it. Here are the start routine, end routine, and expert routine.
1. START Routine is executed once per the data package before any field-level mapping. We can preprocess or filter the data before executing the transformation rules.
It is run at the start of the transformation and is operated on all the records present in the data package, i.e, the source package of the internal table.
We can filter out all the unnecessary records, initialize the global variables, pre-calculate the values, or cleanse and enrich the data at the bulk level.
For example, we can remove all the records where the status = cancelled before the mapping of the fields.
2. End Routine: It is also executed once per the data package, but after all the field mappings or the routines have been applied. It is used mainly for finalizing the data before loading it to the target.
It executes at the end of the transformation and processes the target data package (the result package). We use it for final data validation, merging, or splitting records.
For example, we can use it to calculate the total order value only after the field routines have derived the prices and quantities.
3. Expert Routine: We can use it when we want to completely take over the transformation for one or more target fields. Here, we can write ABAP code to fill the field entirely by ourselves, ignoring all the standard mappings.
We can run it for individual fields, replace the standard rule logic for any field, etc.
It is commonly used in complex logics where code cannot be achieved using standard transformations. Even when advanced calculations or conditional mappings are required.
For example, if we have to populate the customer group based on a combination of customer region, sales org, or product category, which is too complex to derive using standard rules, then we can use an expert routine.
It executes during field mapping.
Q2: How would you write an ABAP routine to skip records with empty material numbers during a data load?
Ans: We usually do it inside the start routine of the transformation. Here, we have to filter out the records where the material number field is blank before the data mapping starts.
Loop at SOURCE_PACKAGE ASSIGNING <ls_source>.
If <ls_source>-MATERIAL is Initial.
Delete source_package index sy-tabix.
Continue.
EndIF.
EndLoop.
Here, Source_Package is the internal table containing all the records of the current data package from the source.
<ls_source> is the work area for each record in the loop.
Is initial is used to check if the field Material is blank or not filled.
Delete source_package index sy-tabix is used to remove the current record from the data package.
continue is used to skip the rest of the loop for that record and move to the next one.
After this routine runs, only the records with non-empty material numbers remain in the source_package and continue through the field mapping and end routine stages.
Q3. What is the role of RESULT_PACKAGE in an Expert Routine? How does it differ from SOURCE_PACKAGE?
Ans: In a transformation, we create an expert routine; we can take full control of how the data is transferred from the source to the target, using the ABAP code.
We have two key internal tables here, which are source_package and result_package. Both are different.
The source package contains the incoming data from the source object, such as DSO , infoprovider, etc. It helps represent all the records in a single data package that are being processed by the transformation.
It can be used to hold the input data, which we will read or transform. We can not modify it directly in the expert routine. Here,in an expert routine, we can only use its data to build the output or target dataset.
RESULT PACKAGE is the output internal table that is filled in the expert routine. It helps represent the data that will be loaded into the target.
We can populate this table in our code. This data is being loaded into the target object. We can add, change, or remove the records freely.
So basically, in the source package, we can usually only read-only, whereas data must be filled by the routine in the result package. The purpose of the source package is to read and transform the data, whereas the result package is used to build or return the data.
In an Expert Routine, SOURCE_PACKAGE is your input, and RESULT_PACKAGE is your output.
Whatever you place in RESULT_PACKAGE is what BW actually loads into the target object.
Q4. How can you optimize performance in ABAP routines processing large data packages?
Ans: When we work with ABAP routines, handling larger data packages for performance optimization is crucial, as slow routines can bottleneck the entire data load process.
Here’s what we can do to use minimal memory and handle larger datasets efficiently.
– We can process the data in bulk and avoid record-by-record loading. Executing the database query per record may cause massive performance issues. We can fetch lookup data once using FOR ALL ENTRIES or Joins, and then use the internal table for lookups.
– We should use hashed tables or sorted tables with appropriate keys for lookups, as constant time access is equal to faster than linear search.
– Filtering all the unnecessary data early and avoiding loading of extra records, which we don’t want downstream.
– Avoid all the nested loops, as they may cause exponential runtime growth. Instead, we can sort both internal tables and use binary search or hashed table lookups.
– Use parallel processing whenever possible. This is not an ABAP-level optimization, but a configuration-level optimization, and it helps
– Trace the performances and identify the bottlenecks, if any. Prevent overload in long loads by freeing up memory.
The best rule is to fetch once, process in memory, and write once to optimize ABAP routines in BW transformations.
Q5. Describe how hashed or sorted tables can be used in ABAP for lookups in transformation logic?
Ans: When large data packages are being processed, the way we do lookups can make a huge difference. Lookup to enrich the data from master tables. Here, we can use the concept of hashed or sorted tables.
In BW transformations, we often need validation or field mappings, enrichment of the data, and joining the data from other sources.
The approach we use to follow is SELECT SINGLE inside a loop, which is usually slow. Here, we can fetch the master data once into the internal table and then use that internal table for faster lookups in the memory.
To make those lookups fast, we can use hashed or sorted internal tables.
1. Hashed Tables are the ones that store data based on unique keys. They are best used when we need to find the exact match quickly.
They are good to use when we always read with key fields, and no sorting or searching is required. The keys should be unique.
They help with the fastest lookup type in ABAP, and sorting is not required.
The only thing is that they need a bit more memory usage than sorted tables.
2. Sorted Tables are the internal tables which automatically kept sorted by their key fields. Binary search lookups are being enabled, and they can contain duplicate keys if not defined as unique.
Whenever we need to read sequentially or by range, or we need a predictable order, then we can use sorted tables.
They have low memory consumption and are very good. Just slightly slower than the hashed tables.
Thus, if you want exact key lookups, you should use hashed tables. For ordered or range-based reads, use sorted tables.
Do follow for more such content: Vartika Gupta | LinkedIn
For SAP BW4HANA Interview Prep: https://topmate.io/vartika_gupta11/1580120
For Real-time scenerio based on ‘Data Integration’ QnA – https://topmate.io/vartika_gupta11/1742973