Table analysis – Saptechnicalguru.com

SQVI: quick viewer

SQVI is a tool to quickly build ad-hoc queries.

Start transaction SQVI:

Enter a QuikView name and press create. Now choose the type (in this case we will make a table join):

First add the table:

And add the second table:

The join between the tables is in this simple cases created automatically. In more complex cases you have to define the join conditions yourself.

Press the back button to go back.

Now enter the output fields:

And the selection fields:

Save the SQVI query.

Now you can start to Execute the query:

Enter the input criteria and press execute to get the wanted results:

Bug fix OSS notes

Bug fix OSS notes for SQVI:

SE16N_BATCH program

SE16N_BATCH is a very powerful and dangerous program. You can use it to run SE16 queries for large datasets in batch mode and later pick up the results from the spool.

But it can also be misused to fetch data that you are not authorized to see, but a batch user can see.

Good use

For example you need a lot of records from BSEG:

This might fail online. If you run it in batch mode, you can simply pick up the spool later with the needed data:

Misuse

The program can be used for hacking purposes as well. Suppose you want to get all user password hashes. SE16N for USR02 is blocked. Now run the program in batch (not with your own user, but with a batch job user with rights to read USR02 data). Now all data is available in the spool for the hacker….

OSS notes

3443282 – “submit se16n_batch exporting list to memory” doesn’t work in background run

How to check SE16N usage?

SE16, SE16N and SE16H are frequently used transactions. They can be used in positive way to quickly fetch data. They can also be a security risk, since it might lead to unwanted data display.

Questions that will be answered in this blog are:

Which users used SE16N?
How much data do the user pull using SE16N?
Which tables did the users read using SE16N?
How to check which changes were performed using SE16N?

Which users are using SE16?

Start transaction ST03 or ST03N, and create detailed settings for recording of SE16N:

Save the values and let the system collect the data.

Now in ST03 in the tree below Transaction Profile, the Details for SE16N are shown. Double clicking on the EXEC function will give details on the execution step:

The DB data is normally shown more to the right.

This will give you information on who used SE16N, and how much data transfer was happening.

Which tables were read using SE16N?

If you want to know which table was read during SE16N, you must first activate activity DU9 (generic table access) in the SAP audit log. Go to transaction RSAU_CONFIG and make sure this activity is on:

Now you can use audit log display the audit log with transaction RSAU_READ_LOG or RSAU_READ_LOG_ADM (this is the version without user ID and terminal):

Select DU9 only to make the report faster.

You can now see the tables accessed via SE16N:

In many analysis cases it is sufficient to see which tables are read, and how frequently.

Use RSAU_READ_LOG to see also user and terminal information.

The audit log is a powerful tool. Be aware of privacy related rules in your company.

SE16N performance

Notes on SE16N performance:

Changes done with SE16N

On ECC or S4HANA systems, changes to SE16N are recorded in tables SE16N_CD_DATA and SE16N_CD_KEY. You can display the changes done using report RKSE16N_CD_DISPLAY:

OSS notes for RKSE16N_CD_DISPLAY:

SE16S: generic table and value search

SE16 and SE16N can be used to search specific tables. FIORI search can be used by end users to search business documents for predefined scenarios.

In some cases you might need to search multiple table for a certain value or string. This can be needed from IT point of view or business point of view.

Then transaction SE16S can be your solution: generic table and value search.

Questions that will be answered in this blog are:

How does the generic table and value search transaction SE16S work?

Use of SE16S

As example we want to search for the string DE01 in T tables (customizing).

After starting transaction SE16S you are confronted with a big selection screen. In the top part enter the search string:

In the search type select Create Ad Hoc Search String. If you have to execute repetitive searches, you can setup predefined searches with transaction SE16S_CUST.

In the search section enter the search tables you want to search:

In the technical block make the technical settings:

Best to use parallel processing to speed up and also best to show only the tables with hit.

Now start the query by pressing execute and wait a couple of minutes for this query to end.

Results are shown:

Per item you can drill down to the details:

Transaction SE16SL and SE16H

Also transaction SE16SL exists for searching content. This one is bit faster, but less accurate.

SE16H is the HANA based implementation of SE16N and has specific search functions which might be very useful for your use case. More on SE16H in this blog.

Checking usage of SE16 transactions is explained in this blog.

Background

Master reference note: 2002588 – CO-OM Tools: Documentation for SE16S, SE16SL, and SE16S_CUST. This note has a full explanation of all the options.

Compare and copy table entries from client 000

After a support pack, system upgrade or feature pack upgrade, new customizing can be delivered by SAP (see also OSS note 337623 – Customizing after installation or upgrade). To avoid changes to the main client customizing, this new customizing is delivered in client 000. You need to either manually look up the values in client 000, or compare using the tools mentioned below. There is and option to adjust the records after comparison; pull the data from client 000 to the main client and then save it.

Questions that will be answered in this blog are:

What are the prerequisites for a comparison with client 000?
How can I compare table entries with client 000?
How can I adjust table entries with entries from client 000?

Prerequisites for comparison

In order to execute the comparison you need to set up Transaction SCMP can be used to compare entries between the main client and client 000. Prerequisite is that you have setup an RFC from your main client to client 000 with reading right in client 000.

In SCC4 you must set the proper client comparison rights:

SCMP comparison for single table

Transaction SCMP can be used to compare entries between the main client and client 000.

Start screen:

We use table T005 (countries) as example. Run the comparison:

SCMP is only comparison transaction. You cannot adjust entries.

SCU0 or OY19 comparison for multiple tables

If you want to compare multiple tables, use transaction OY19 or SCU0:

Choose selected activities:

Select the IMG node(s) to compare and click the green OK button:

Select the RFC and start:

Results:

SM31 adjustment

To adjust the settings, start transaction SM31 for the table view V_T005:

Click maintain and select the entry to adjust. Now select menu option Utilities and option Adjustment. In the popup select the RFC to use:

Select the detailed entry and press copy entry:

Reference OSS notes

References:

TAANA improvement to count dynamic subfields

SAP has done an improvement on TAANA to count dynamic subfields. This blog will explain how. More generic information on TAANA can be found in this blog.

Questions that will be answered in this blog are:

How to get the new TAANA function for dynamic subfields?
How to run TAANA dynamic subfields?

How to get the new TAANA function for dynamic subfields?

Simply apply improvement OSS note 2614476 – TAANA: Several dynamic subfields with reference to same reference field.

How to run dynamic subfields in TAANA?

We will use table JEST as example. This table as a pretty annoying setup. The main field OBJNR is in fact 2 fields: the first 2 characters are object identification, and the second part is a number for the object. But if you want to analyze how many objects type you have this is problematic with SE16.

In TAANA we can use the dynamic subfields. Start transaction TAANA and create an Ad Hoc Anlysis for table JEST. First hit Execute to start, enter table JEST and in this screen hit the Ad Hoc Variant button:

Now select the OBJNR field:

Ad hoc analysis with offset and subfield length

In the Offset field fill 0. And in Subfield length 2. This means take first 2 characters of field OBJNR. Press ok and start the run in the background.

The end result is a cross section with counts on the types of the first 2 characters in JEST-OBJNR:

SE16S and SE16H

For some searches, also have a look at SE16S and SE16H.

SE16H: HANA specific implementation of SE16

SE16H is a HANA specific implementation of SE16. This blog will explain the additional functions of SE16H.

Questions that will be answered in this blog are:

How to use SE16H?
Where to find full list of SE16H functions?
Which bug fix notes for SE16H should I apply?

SE16H: HANA specific implementation of SE16

SE16 or SE16N are one of the most used transactions for data analysis on any SAP system. SE16H is the HANA specific implementation which leverages some of the HANA specific strengths.

Transaction code to start is simply SE16H. We now enter VBAK as example table. Just pressing execute will give simple list of first 500 entries. Nothing new.

Now we run again, but tick the Group and Sort tick boxes for the Document Category field:

The output now is a sum of the sales orders in table VBAK grouped by identical Document Category:

TAANA vs SE16H vs SE16S

If you run on HANA, the SE16H transaction is a faster option than the classical TAANA transaction, since SE16H runs online and TAANA runs as batch.

SE16H is for lookup of single table. SE16S can search for content in one or multiple tables. More on SE16S in this blog.

For usage of SE16N, read this blog.

List of all SE16H functions

The full list of all SE16H functions can be found in OSS note 1636416 – CO-OM tools: Functions of transaction SE16H.

Interesting ones are: aggregation, drill down, sorting, totaling, outer joins.

New function is the use of a formula editor. This can be used after applying OSS note 2795867 – CO-OM tools: Implementation of formula editor in SE16H.

SE16H bug fix notes

Please consider the following bug fix OSS notes for SE16H: