Quickly find queries which use specific InfoObjects (part II)

In addition of one of my previous blogs I adjusted the function module RSZ_I_BASIC_KEYFIG_WHERE_USED and saved it to a new version.
The problem with the standard function module is that is requires to fill in a MultiProvider or CompositeProvider in order to get a result set.
Where used key figures function module
Because I had the requirement to get a complete overview without any restrictions I used and changed the ABAP code. The new function module will only ask for a InfoObject name and will show you a list of all relevant queries.

Transpose internal table using GROUP BY function

Imagine you have a table with an account number (0ACCOUNT), currency type (0CURTYPE) and an amount (0AMOUNT). The account number and currency type are part of the key. For every account three different records are created, namely Currency Type 00 (Document Currency), 10 (Local Currency) and 20 (Group Currency). From reporting perspective you want to have the three separate records on one line.
Tranpose internal table SAP BW
The basic idea behind transposing is that you want to loop over an unique combination of characteristics and then loop over it’s members. As of SAP ABAP 7.4 you are able to do this very easily using the GROUP BY function. So let’s state you want to loop over the unique accounts within the internal table above? Then you should specify 0ACCOUNT as your GROUP BY clause.
LOOP AT GROUP BY statement

The above ABAP statement will add two records (100001 and 100002) to a new internal table. Very nice, but it does not cover our full requirement. As an additional option we can loop over it’s members and transpose them into a single line. This results in the complete code below.

Don’t forget, that because this is default SAP, it is HANA optimized, which implies that the GROUP BY statement is pushed down to the HANA database!

Reset RSA1 transaction code by resetting user settings

This week I had the problem that my transaction code RSA1 was not working.It didn’t matter what I tried, but after executing this transaction code my screen ended in a dump. As a BW consultant the t-code RSA1 is most used transaction, so without being able to access this BW modeling is not possible.

After some analysis it seemed that this all had to do with an incorrect value stored in my personalization settings. In order to reset these these values I came across the class CL_PERS_ADMIN. This class contains different methods which can be used to manipulate user data. It includes a method called DELETE_DATA_USER which can be used to reset personalisation data.
With the use of use of P_PERS_KEY you can reset different values. These values matches the objects found in the SU01 Personalization tab. After I resetted my user settings, RSA1 worked again!Personalization settings

Delete overlapping requests DataStore Object using ABAP

Do you want to snapshot on a daily basis but only want to keep one request per calendar week? The attached ABAP makes this possible! You can use it the start routine of a transformation.

The logic uses the current calendar week (based on the system date) to retrieve a list of requests which are loaded in the same calendar week. This list is then sorted descending based on the loading date. Then one by one the loads are being removed from the DataStore Object (DSO), leaving only the current request active. When this load is executed on a daily basis only one request per calendar week will remain in the DSO.

Loop over table columns dynamically

In case of data cleanse activities there could be a necessity to loop over columns. Doing this dynamically will enable you to easily verify each column without writing to much ABAP code, as the code is valid for all columns.

Since everybody knows how to loop over rows but not over columns I began my quest of finding the most proper solution. I stumbled across this blog explaining me how to achieve this with just a couple lines of ABAP. The ABAP uses a standard SAP class cl_abap_typedescr to retrieve data.

Be sure to have a good look at this class since it has more nifty methods available which might get in handy during your developments in SAP BW.