Mungapen SAPLOG

Introduction

It still amazes me after fourteen years of implementing SAP, that many seasoned consultants are not familiar with SAP query (formerly ABAP query). SAP query is SAP’s built in querying transactions that allow users or IT staff to quickly and easily create multi table queries which can either be used as reports or used in extracts to excel and other desktop applications. SAP has developed this functionality to allow the quick ad hoc joining of tables with the ability to add custom formulas and even access to the full use of the ABAP/4 language. However this functionality is often sidelined and ignored due to lack of experience by consultants or in many cases the functionality is vetoed due to a lack of understanding.

Why SAP query is a dirty word (or two words)

SAP query has been around for a while in SAP and I think this has been one of its downfalls in its usage. SAP query has developed a reputation of being a resource hog as the technical guys always like to point out that it doesn’t generate efficient code. I’m not a technical consultant so I cannot comment on the efficiency of the code generated by the tool in comparison to a developer, and it is generally true that tools that generate code automatically rarely will do it as succinctly as a person.

Additionally a little knowledge is a dangerous thing and SAP query will allow a user to generate a report that could perform a large number of non key field searches of large tables and tie up system resources for a long time.

SAP query does ‘open the door’ to perform ABAP development with a development key and one could bypass the formal process of promoting to production via testing under the control of the change and transport system. I have even heard horror stories of major tables been corrupted by the use of ABAP code in SAP queries. So there are defiantly some cautions you should heed before using the tool.

SAP has developed better reporting solutions, with the development of BI and now BO the query tool has been developed into an offline data warehouse where the performance of reports does not impact the performance of the ERP system. So SAP’s strategy is for all reports where latency isn’t a concern to be directed to BI/BO. So in summarizing

• It does not write efficient code
• An inexperienced designer can write complex non key searches that tie up resources
• It opens the door to uncontrolled ABAP objects that are not created by developers.
• It can bypass the change and transport system
• SAP provides better off line tools such as BI/BO

All of these are good points to consider; however I do not feel they necessitate the ‘outlawing’ of SAP query as a valid tool in the implementation.

The Case for the defense

However I would contend that SAP query is a useful and irreplaceable tool in certain circumstances, which with a little understanding and restraint can be used not only as an end user reporting tool, but more importantly as a support tool for the implementation team and MIS department. So in defense of the claims above

# SAP query does not write efficient code and it can tie up resources running badly designed reports.

Back when I started in SAP in the mid 90’s it was very true that a badly written SAP query could run forever and have a noticeable impact on the system’s performance. However it was also true back then that I’ve seen a consultant on a project whose job it was to monitor how many sessions the consultants had open using SM04 to prevent the system from crashing if we each had more than three. Thankfully since those days the hardware we run our SAP systems on has grown by an order of magnitude and the laptop I’m writing this on probably has more power than servers did back in those days. So I think the old fear of the inefficient code SAP query writes is mitigated by the raw processing power of today’s hardware. As a second rebuttal its true maybe SAP query does not write the most efficient code, however on today’s ‘competitive’ implementations there is often a large component of very junior off shore development staff. The most capable of which end up writing the custom transactions, interfaces and conversions, while the more junior developers are left with reports and forms. And they don’t always write the most efficient code too.

As an additional defense SAP themselves use SAP query throughout their applications, one good example of this are the document overviews supplied in the MM enjoy transactions, such as purchase requisitions and PO’s. The document overview functionality here is all designed using SAP query, so any implementation using these is by default using SAP query.

# An inexperienced designer can write complex non key searches that tie up resources

This is true, but with a little training these issues can be avoided so an experienced developer of SAP queries can limit the processing required for a given query. Additionally SAP provides a separation with the query itself and the info set on which the joins are built so that once an infoset has been optimized and tested. Multiple queries showing different data can be developed on top of it.

# It opens the door to uncontrolled ABAP objects that are not created by developers

SAP query is at heart a powerful tool that allows the user to write and execute lines of ABAP code, and this code can be written without the need for a developer key so it can fly below the radar of the technical leads. Additionally SAP query is more of a functional consultant’s domain rather than a developer, so it could lead to functional consultants ‘hacking’ code together which might not be up to the professional standards on the project. In answer to this I would say that functional consultants generally have enough access during a project to severely damage the system without having SAP query so you need to trust in the integrity of the consultants not to push beyond their abilities without the appropriate testing. To consultants I would say this is why you need to learn and understand this tool so you can use it from a position of understanding.

# It can bypass the change and transport system.

Ok this could be seen as a benefit in terms of speed in developing a quick query and getting the results but this is a tuff one when considered with the previous capability of being able to write ABAP code without a developer key. This could lead to problems, depending on how you set up authorizations you could give consultants the ability to run untested ABAP code in your productions environment. This reminds me of a horror story I heard of where this exact thing happened and corrupted a major transactional table in a client’s production environment, bringing their business to a halt. This is a real example where an incorrect ABAP statement blew away large amounts of critical data and corrupted the system. This incident should serve as a warning to others and it pushes the onus on the consultant to follow procedures and not execute any code in a production environment without having tested it first in the QAS environment. So as Cesar Milan says there are no bad dogs just bad owners. I say there are no bad transactions just bad usersJ. Caution is the keyword here, project manager’s consultants and team leads need to instill and manage the migration to production path whereby nothing runs in production without first being run in QAS first.

# SAP provides better off line tools such as BI/BO

This is true, SAP’s strategy is to push all non real time reports to an offline data warehouse and I would agree. For any user report that doesn’t have latency concerns the first solution should be to see if BI has the necessary data. However there is the need for many operational reports for end users that need to be real time so the alternatives are SAP standard, SAP query or custom development. In these cases SAP query can be an effective cost saving tool to avoid custom ABAP development.

And to be honest there is a whole class of reports that will never be run by an end user, which may not warrant developing a report or setting up BI cubes. These are reports that consultants and project team members can use for themselves as part of an implementation before handing over to the end users. These reports are the reason I recommend that every SAP consultant and project team member should have a working understanding of SAP query. And they’re the reason for this article

Before you Query

The reports that a consultant would write for themselves are by nature transitory, in that they will be useful for a limited period of time then discarded. With this in mind it’s important not to spend too much time developing the report as it may take longer than is worthwhile. So before you dive into writing a query, there are a couple of checks you might want to do to see if there is a quicker method of getting your data.

SE16N

This is probably my favorite transaction and it should be the first thing to check. If the query you’re writing is based on a single table then SE16N is a report already waiting for you.

In the same way that you can use SE16N to view the contents of a single table, you can also use it to look at the contents of a view. A view is a predefined join of multiple tables and may contain all the information you need.

Similar to views you can also use a logical database but that really needs the use of SAP query or at least the quick viewer portion of it. However it is possible to run a report on a logical database from within the SE36 transaction by using the test function. Although the results are ugly so I would use logical databases under an SAP query.

Multiple extracts joined in Access

I sometimes do this is SAP query isn’t available in the system or I’m not sure what information I want. In this way I might use SE16N to extract some related tables to spreadsheet format then import them into Microsoft Access with joins that replicate SAP’s joins. This may allow me to quickly get a feel for some data which I can analyze in excel via pivot tables etc.

However there are many cases where I will use the SAP query functionality along with quick viewer for my own (or the project manager’s purposes) so examples of how I use SAP are detailed in the next section.

Phase by Phase Usage

If you think SAP query is just a quick way of satisfying the end users desire for real time reports then that’s only half the story. I’ve personally found many uses for queries throughout the different stages of an implementation, some ideas are detailed below but if you have your own I look forward to hearing your own.

Project Preparation

Well this might sound weird, how can you use SAP query during the project preparation phase? Well if this is the initial implementation then clearly you cannot. However if you are preparing for a major project on a current productive SAP system then a few SAP queries may really help narrow down scope and separate fact from fiction.

When I am deployed to a new client that has a productive SAP system, I always take some time to get a feel for the environment. I will always use SE16N to understand the plant structure and get a feel for the material types in use. However I often want to look at some multi table relationships for example to understand what processes are running in the various plants I can look at the material types and groups by plant. Often using the check tables of material group etc to get a sensible description so a quick join of MARA, MARC and T023 will give me an idea of what goes on where.

When establishing scope for future projects you can often read the previous blueprint documents to see how the system was supposed to work, you can interview users to get an opinion on how they think it’s working, but you cannot argue with the hard data of how it’s actually working. For example in a purchasing project I might quickly run a query on EKKO, EKPO with some other tables to get a report in excel I can pivot to see how things are really running.

So in the project preparation phase I would use SAP query for broadly two types of report

• On boarding – to understand the environment
• Scoping – to information to support scope.

Blueprint

Again during blueprint you might primarily only use queries if there is an existing live production environment to pull data from.

I find pulling reports as an input to specific workshops is a great way of having factual information to support decisions.

• Decision support – to give quantitative data to drive decisions

Realization

In this phase regardless of whether there is a preexisting system or not you’ll be developing your development environment and rolling into the QAS environment.

A few well placed queries can be useful to quickly identify the manual data set up required to support the design (i.e. not being delivered by a conversion) Running the same query in Dev and QAS can quickly identify missed set up. These reports are reusable in the final prep phase where in cutover you have to perform manual data set up in sequence with conversions in a limited amount of time.

Examples of this can be condition records that often don’t have good reports, in this way you can quickly compare two systems. Another query I often use is also batch jobs where I quickly want to see TBTCO, TBTCP and TBTCS in a single line to quickly confirm the set up is accurate on multiple jobs without having to drill into them.

Also during this period there maybe custom interfaces that are not complete or ready for testing. This can often hold up testing of downstream systems. A simple query extracted to excel or a flat file can often help glue a manual point to point interface together to allow testing of downstream systems until something more robust comes along

As you enter into formal testing you can leverage queries to back up metrics in your formal testing tools such as quality center etc are producing. I.e. to ensure that tests are being performed accurately suing the correct data.

During this phase conversions will be tested and this is a key use of SAP queries that many projects ignore. The business will be responsible for validating the result of a conversion in both scope and accuracy and many of them will not be overly familiar with the SAP environment and will want to be able to quickly get into excel where they can manipulate date to tie out with legacy systems.

Also these initial validations will be honed and used under the pressure of cutover to quickly tie out to legacy before a go no go decision so you don’t want to give a user seven standard reports to find the information they need. If the data validators have queries that they are progressively becoming efficient with then this will be key for a smooth and quick cutover. This is a key use of the query tool and if all the consultants are familiar with the tool then there will be plenty of fast responsive help for validators.

• Set up Progress – to track the manual set up in building environments
• Environment check – run in the target environment to quickly compare
• Interface mock up – temporary extract to allow downstream system testing without completion of interfaces
• Test data validation – ensure testers are testing what they are supposed to be testing
• Conversion result validation – to quickly ensure all data is being converted accurately as planned.

Final Prep

During final prep the same activities will be continuing potentially with formal mock systems , so multiple system builds, cutovers and validations all the queries from the previous phases should be used under the pressure of timed conversions. And when it comes down to the real cutover you’ll be glad you have validation queries to quickly identify missing manual set up, check batch jobs and tie off conversions.

Go live and Support

Here’s where the fun can begin, (hopefully not) but if the go live is less than smooth there’ll often be the need for ad hoc stabilization reports that cannot wait for the latency of a data warehouse. If a problem prevents the warehouse form shipping on day one the CEO isn’t going to accept a 24 hr latency to find out whether day 2 was any better. At this point practice you’ve had in the previous phases will pay off as you’re quickly able to deliver queries to the business to track everything is ok

• Stabilization reports – to monitor and fix issues found at go live

Conclusion

So in conclusion SAP query is a useful and powerful tool for developing not only end user reports but also for ad hoc and throwaway reports that can help the project team deliver a project on time and without issue. It is a great string to any consultants bow and I would highly recommend becoming familiar with both SAP query and quick viewer tools for your own benefit. Though a word of caution, even though SAP query will allow you to write ABAP code directly in a production environment don’t do it. Always write your queries in a non production environment then test them first.

So are you fired up to start using SAP query? If so use the link at the top of the page to sign up for my monthly newsletter as the February edition will provide links to a free step by step instructional document on how to use SAP query.

Also if you have additional uses, please leave a comment

Have you ever been frustrated trying to find  which table and field a piece of data is stored in. You can see it on the screen, and the old faithful F1 – F9 results in some useless structure information. Or have you ever started looking at a piece of functionality you are unfamiliar with wanting to find the table structures behind it in SAP. Well  this article shows my favorite five ways of digging under the hood to find out what’s going on. Let me just preface this by saying  I’m a purely functional consultant and I’m sure the more ABAP literate probably have their own ways. If so I’d appreciate hearing from you. But anyhow here’s my top 5  (in no particular order).

#1 Use a Different Field

when you click on a field then hit F1-F9 and it shows a structure and not a real field, sometimes the easiest way to find the real transparent table behind  is to try another field on the same area of the screen.

In the example below I want to know where SAP stores purchase order confirmation information. On the confirmation tab I click on the delivery date field and press F1 then F9 (technical information) in this case it shows me a structure and not a transparent table.

So trying another field  like quantity I get the answer I’m looking for

#2  Use Where Used on the Data Element

An alternative when faced with a structure is to use the ‘where used’ on the  data element. This can have mixed results but can often show a list of tables that can be quite useful.

From within the technical information pop up (F1-F9) click on the data element then press navigate

In the above example there were only two choices, I could then view both of these tables using SE16N to see if the data relating to my purchase order is there.

#3 Environmental Analysis

This is one of my current favorites for finding all the tables related to a transaction I might be unfamiliar with.  Its a bit broader than finding a specific field and table on a screen, but very powerful and quick. For example if I wanted to find out how SAP stores object classification information i would proceed as follows. Find a suitable transaction that focuses on object classification, there are a umber but I’ll use CL24N for this example.

from within the transaction find out what the program name is using System > Status

From here you can find the program name in this case SAPLCLFM. Copy the program name into the clipboard then in a new session enter it into an SE38 transaction.

From here you can select the environmental analysis option.   next you get a list of objects to analyze for.

you can either just select tables or leave them all selected and execute. This gives the resulting tables used by the transaction code along with the package code which can be used in the SE80 method above.

I generally  ignore the T tables as configuration tables  and can quickly understand how the tables interact by looking at their data in Se16N.

#4 Pot Luck in Table Naming / SE80 Object Navigator

I often use this method to get a ‘feel’ for what tables are related to a certain piece of functionality as SAP often uses a fairly consistent naming convention for tables. In my purchase order example I know that the PO header table is EKKO  and that PO item table is EKPO,  so within SE16N I can search for all tables starting with EK* to see if anything pops out

from the list above I can see that vendor confirmations is EKES.

A similar but more comprehensive way of achieving this is to use the SE80 Object navigator transaction in SAP, here you can find all the data dictionary related objects associated with some functionality in one place.

I tend to use the ‘package’ as a way of finding objects and the application hierarchy to identify the correct package.

In this example we can look at all the dictionary objects in package ME

#5 Traces (Runtime Analysis and SQL trace)

If all else fails you can always trace the transaction to see what its up to and SAP provides two very useful traces that can be as useful for functional consultants as developers. About ten years ago I  used to use these traces all the time, I must say i use them much more infrequently these days as I think the repository tools mentioned above have become more sophisticated  (or maybe I have become more sophisticated). However sometimes performing a trace on a transaction gives you an idea what the program is up to.

SE30 Runtime Analysis

Executing transaction SE30 allows you to perform the desired transaction from within the trace

Give the trace a name then enter the tcode you want to analyze and execute.

run though the transaction make sure you enter data in the field or area of the screen you want to track. Complete the transaction by saving and you will return to the trace screen.

now you can evaluate the trace

The initial screen shows performance information which we’re not interested in

select the hit list button   You now get a sequential list of what the transaction did, which can be overwhelming

Not being a developer I only look for a few things in this list such as Fetch and Select statements as they generally relate to table reads and writes and these are all of the type ‘DB’ .  (I’m sure an ABAP savvy person might have a better criteria)  you can filter the results using the ALV filter button.

Select the type column for filtering

then enter your criteria of ‘DB’

you now have a pretty exhaustive list of tables that were hit during the trace (so make sure you populate values in the fields you’re trying to track down when doing the trace.)

ST05 SQL Trace

This is a similar form of trace to the runtime analysis, however it works using two sessions, it also generates a lot of data so you need to prep what you trace before hand.

in one session set up the transaction you want to trace, then open a new session and select ST05

Select the SQL trace and activate. Alt Tab to your other session, enter some data in the area you want to trace then save that transaction. Immediately alt tab back to the trace and deactivate it

now you can display the trace

somewhere in the resulting mess of a report will be the data you changes and the resulting table objects

good luck hinting around.

Conclusion

To be honest I haven’t needed to use a trace to find a table and field in a long time. I think the object navigator and the environmental analysis have replaced the need for these. However they are definitely available tools. I know this list isn’t exhaustive and many of you may have better methods. If you do please leave a comment. Like I said I’m  a functional guy so I’m sure the technical guys out there have a few tricks

In a similar vein to my previous posting about extracting the IMG in Excel, you may also want to get a list of Transaction codes out of SAP into Excel to allow for various management tasks. I have used these to track the development of BPP’s assist in designing authorization roles etc, and general project management.

Anyhow this is a much more simple process than extracting the IMG as we’re just extracting a table to Excel.

Basically the transaction code and description information is held in a transparent table TSTCT to get the descriptions in all languages maintained. Using transaction SE16N you can query the table directly and extract to excel. Limit the display to show only the language you are interested in

This table holds a lot of entries (over 100k in each language) so you may want to perform multiple extracts for different areas. For simplicity I am going to extract only the ME* transactions in materials management of which there are 310 in this example

Make sure that if you check the number of entries you should enter that number in the maximum number of hits (if its higher)

Now you have a list of transaction codes with their descriptions that you can easily export to excel

from within the results press the export icon then local file (depending on the version of the sapgui and the version of excel you can directly press the spreadsheet option, but I have encountered problems with office 2007 using this method)

from the local file menu select spreadsheet

give the file a name and save it to the desired location

compared to the IMG extract there is very little clean up required

I like to remove the white space and language column then format it as a table

Another table  you can use instead of TSTCT is TSTCV this also has the program name and screen number which you may find useful (or not)

I have seen copies of this list being sold as a PDF on ebay and elsewhere, when it really only takes five minutes to get your own list. (maybe a bit longer if you want to run this report for all transaction codes and not limit it in any way.

Anyway, if you find this article useful please let me know via the comments