Mungapen SAPLOG

In part two of this tutorial  we’ll take a closer look at Infosets which form the data basis of SAP Queries

Creating an Infoset

The Next and most important step of SAP querying is to create this infoset. The infoset is the core definition of the query. It defines table joins and therefore the uniqueness of the data set below.

Taking a step back to general dataset joins, the more tables I join the more records and duplication of data I may end up with. In the example below I am interested in Sales order information

I might want to join three tables in the sales order structure, VBAK, VBAP, VBEP in the example below I end up repeating the records in VBAK and VBAP to allow for the multiple VBEP entries

Which is necessary if I need the schedule line information in VBEP, however if all the information I need is in VBAK and VBAP then I can simplify the report dramatically by leaving out VBEP. In this way I might generate two separate infosets, one with VBEP and one leaving it out. There are some advanced ways of simplifying the resulting structure which are discussed later on however the main thing to remember is design infosets specific for a use, and do not just add all the tables within a certain application area, as not only will the resulting report be confusing, it will also take considerably longer to process. So keep infosets simple and specific for their intended use.

Within the infoset transaction there are two main screens that you need to be familiar with;

· The graphical join view

· The infoset overview

As its name suggests the graphical join view is where you develop the join criteria between tables

The infoset view is processed after initial join criteria have been established. In this view you determine what fields from the joined tables can be used in queries, along with defining additional fields and tables.

To create an infoset, use transaction SQ02. First select the query area, add a name and press the create button

At this point you will get a dialog where you can enter a description for the Infoset. You should make this as descriptive as possible so that you can easily find the right infoset later on.

At this point you will also have to define the type of data source that will be used, these dictate the basis for the data, and the options are

· Table join

· Direct table read

· Logical database

· Data retrieval by program

What things can be queried

In the list above the most obvious is the table join, this as its name suggests means we define a table join criteria between multiple transparent tables such as joining EKKO and EKPO using a join criteria of common fields. This is probably where ninety percent of your SAP queries will be developed as we are generally looking to get a resulting dataset that is a join of multiple tables.

A direct table read may sound redundant as we can use SE16N to read a single table. However there may be reasons why we would want to create an infoset based on a single table. For example if you do not want to give a user access to SE16N then you can create an infoset based on a single table and even limit the fields visible. You can also augment a single table with additional fields in an SAP query. This can be used where your report is primarily based on the results of one table maybe with single fields from other tables. Instead of joining to those tables in their entirety, you can use some simple ABAP statements to bring in just the fields you want. This is a very powerful concept which we will discuss later

In addition to views which were discussed earlier, SAP also provides a concept of logical database where a group of related tables are joined using predefined program that can be used to read the whole dataset as if it were reading a single table. SAP uses logical databases widely throughout the various functional areas so you can often find a logical database that exists with all the data you need predefined.

In addition to basing infosets on tables you can also reference an ABAP program to retrieve data, this opens up any program that obtain data to being available to reporting, this can also include external programs, giving access to external data. It is beyond the scope of this document to look at this option in any detail.

Joining Tables

The main requirement for developing queries will be to join multiple tables together, based on what fields are used for the join and the type of join will determine what kind of information is returned by the query.

From within the initial screen in the infoset the table joins can be defined in a graphical manner. Additional tables can be added using the insert table button.

The insert table dialog appears

Enter the table name to join to and press the check mark

The system suggests the join criteria

However it is rarely correct, in this case it has correctly suggested joining EKKO-EBELN to EKPO-EBELN however it has also suggested joining EKKO-LPONR to EKPO-EBELP which is incorrect. It is often best to delete the suggested joins and start again based on the logic you want

This can be done by clicking on the joins then right clicking then selecting the delete link option

To make your own joins you can drag and drop from one table to another with the fields highlighted

You will notice a small red box highlighted as you hover over the various destination fields when you are over the appropriate field release the mouse to make the join

Inner Joins

By default all joins are inner joins. This means that the resulting report will only show a dataset where both tables have matching entries. In the case of our join of EKKO and EKPO this is fine as you would expect to have a line item for a purchase order. However this is not always the case. Say for example you are joining MARA and MARC to look at materials in plants; however you still want to see materials that are not extended to plants yet. In this instance you must use an outer join.

Outer joins

An outer join will show all the records that are in the first table along with corresponding records from the second table. It will show blanks if there are no values in the second table. If you wanted to create a report that shows materials not extended to plants then you could perform an outer join between MARA and MARC then look for null values in MARC fields. To make a join an outer join, right click on the join then select the left outer join.

Alias tables

In some cases you may want to join to the same table multiple times (to allow for different criteria). If you try and do this the system will error so you must first create an alias table, whereby you copy the table under a different name or alias first. For example if I wanted to create a Bill of materials report which would show planning parameters from the material master then I would link the material tables to the BOM header material, then I would need to Alias them so that I could link the same tables to the BOM components.

In the example above ZMARC is an alias of MARC

To create an alias table press the define alias table button

From the pop up press the create button then enter the table you want to alias and a unique alias name

You can now use the Alias as if it were an SAP table using the insert table button.

Field Groups

Once you have defined your table joins you can exit the join maintenance screen by selecting the Infoset maintenance button. You can always return to the join criteria and add / remove tables at a later time.

When you exit the join screen via the infoset maintenance button for the first time, you will be asked if you want to include all the fields from the corresponding tables, just key fields or none. SAP provides a way of grouping the fields you want to include in your query into logical groups called field groups.

I generally select all fields when I am first developing a query as I often find looking at the data helps define what is useful. Then when I have a fully functional set of queries I will simplify the infosets removing superfluous fields. If the query will end up with end users there may be fields you do not want to include. The more fields you include in the infoset will mean scrolling through them to select the right ones in a query.

In addition to the automatically generated field groups you can create your own and move fields into logical groupings

It is often useful to create your own field groups if you are going to add extras such as additional fields. To create a field group use the create icon

Enter a two digit key for the field group and a description

Once created the new field group shows up as empty in the field group area. Unassigned fields can be added by dragging and dropping.

The resultant change in field groupings can be seen on both sides.

Extras

In addition to creating table joins between tables. One of the most powerful (and dangerous) functions within the SAP query is the extras section. In this area it is possible to add

· Additional tables

· Additional fields

· Additional structures

· Selection criteria

· ABAP code

All with the use of freely definable ABAP code which may be entered without the need for a developer key.

Word of Warning

The ability to enter free form ABAP code in an SAP query means that this code can be entered into an infoset in any system where queries can be developed including directly into the production system. This combined with the fact that you do not need a developer key means that functional consultants may be tempted to do this themselves (thinking they have full understanding of what they are doing). Never (let me repeat that) NEVER run a query directly in a production client that has ABAP code in it without running the same exact query in other systems first. I know of one real world instance where a consultant ran a query on table VBFA (sales order document flow) and wanting to increase the performance he wanted to load an internal table ZVBFA with the data then reorganize it. Unfortunately due to a typo in the code he reorganized the VBFA table directly in production, bringing the company to a complete halt and requiring much emergency care directly from SAP. So as soon as you start adding extras to your query (no matter how innocuous). Be wise and learn from someone else’s mistake and never run your query untested in production.

That being said the area of extras really allows SAP query to be a powerful and useful tool.

Additional Table

Adding an additional table to an infoset may sound the same as adding it via a table join however you can add more complex selection logic via the additional table functionality. For example in a purchase order you can have multiple partner functions which are keyed by a different partner function such as VN, for vendor, OA for ordering address, PL for delivery plant etc. If I want to create a report just for ordering addresses I don’t want to see the other entries in the partner function table. I could add the table as a join and use my selection criteria to limit the results or I could add the table as an additional table and specify the ‘OA’ directly within the join criteria.

To create an additional table, select the extras button

This brings up the extras screen, showing multiple tabs and a list of any of the extras already defined.

Select the create icon then from the pop up select a table and the additional table radio button

From there you will get a join criteria pop up

In the same way that SAP tries to suggest table joins and doesn’t always get it right you need to validate the join criteria for yourself. In our example for ordering address we need to hard code PARVW to ‘OA’.

We now have the additional table assigned but with no fields allocated to field groups.

Additional Field

If you only need to bring in a single field from a table using specific logic then the additional field option allows for complete freeform logic to populate a custom field. From the extras tab select to add an additional field. You will need to give it a custom name

From the definition dialog you can either define the field directly as a certain type (such as character or date etc)

Or you can use the like command to reference an existing SAP field to copy. You must also set up a sequence for your additional items.

The sequence is important as if you want to use an additional field as the basis of another field as they process in order

Once the field is defined you need to enter the ABAP code to make it do something

You then get the very scary white space where you have full access to the ABAP language

“But I’m not a programmer” you may cry. Well even if you aren’t there are some simple code blocks you can use to do some quite powerful things. (Remember the word of warning though; don’t get in over your head)

Basically you will want to select some values from a table, maybe process them in some way and return a result. You may also want to use some ‘if then’ statements to make alter the data based on certain criteria.

Essentially the code has access to all the fields and data that are part of the table joins and the additional fields are executed at the lowest level of the report.

This means in the example below where I join VBAK, VBAP and VBEP, the additional field is executed for each record i.e. at the VBEP level (so you may get duplication of entries if it’s at a higher level)

This also means that the code may be executed hundreds of times and may slow the performance down.

The basic select statement is as follows.

This can be broken down as follows, my field I am trying to populate is called ZMATNR and its roughly based on MATNR.

The first thing I do is clear any preexisting value from ZMATNR

Then in line to I Select the field I want (MATNR) from the table I want that’s not currently in the query (MARC) and I copy it into my additional field (ZMATNR)

Then I specify the criteria for selecting that record form the large table MARC

I match plant with the plant value form the PO line item (EKPO-WERKS) and MRP controller with the MRP controller in the Po header (EKKO-DISPO)

Then I end my selection. Using multiple ands I can create complex criteria.

If my criteria returns multiple results I can sum them select the first , last etc I can also place hardcoded requirements and If then statements

Below is an example I have used in a custom IDOC report. It hard codes the segment I want to review to get the data I need.

clear ZE1BPEKKOA.

select sdata from edid4 into ZE1BPEKKOA

where DOCNUM = edidc-docnum

and SEGNAM =’E1BPEKKOA’.

endselect.

This returns a long field that has many different pieces of information. I know that the info I was looking for was 90 characters from the start and was 12 characters long hence I added the following logic

data l_str type char20.

l_str = ZE1BPEKKOA+90(12).

ZE1BPEKKOA = l_str.

The example below reads the schedule line table in a PO and sums all the schedule lines to give the total quantity to be delivered

clear zdelqty.

SELECT SUM( wemng ) FROM EKET into zdelqty

WHERE BANFN = eban-banfn

AND BNFPO = eban-bnfpo.

Introduction

SAP query is a great tool for developing reports for both end users and for the implementation team alike. Hopefully you have read my article about the uses of SAP query and why I think it is a must for every consultant to be conversant with this tool.

This  is part one of a three part step by step explanation of SAP query to get you up and running creating queries quickly and efficiently.

SAP query Concept

SAP query is a set of transactions that allow a user or at least a super user to design queries, showing different database fields along with custom formulas and calculations. These queries can be based on a single transparent table, a logical database or a set of table joins defined as part of the SAP query process. This allows a user to quickly and easily obtain a report covering multiple tables with a specific set of join criteria. In addition to basic joins SAP query supports the selection of individual fields by the use of ABAP statements, along with more complex data selections using custom code or existing function modules. SAP splits SAP query across multiple transaction codes so that authorizations can be split between different users. In this way one user might be authorized to define table joins and code in an infoset while another is free to use that infoset to generate different queries showing different information.

SAP query structure

The SAP query structure is split based on four transaction codes. To develop a query there are three main concepts;

· Infosets

· User Groups

· Queries

In addition to this there is an all in one transaction that combines these called Quickviewer for quick personal queries

Infoset [SQ02]

The infoset in SAP is the main underlying concept in developing queries in SAP. The infoset defines the tables, the join criteria and the pool of allowable fields within a query. It contains any ABAP code that also might be used in the processing of the query. From within the infoset set up you can define whether the query is based on a single table, a join of tables, a logical data base or if the query is based purely on a retrieval program. In addition to the table structure the pool of fields can be defined which can be accessed from a query. Custom fields can be added along with ABAP code.

Query [SQ01]

The query is the main report that a user will run to view the SAP query. Queries are tied to infosets that have been previously defined. Multiple queries can be developed on the same infoset selecting different selection criteria and display fields. It is also possible to add additional fields not in the infoset which are calculated based on formulas, i.e. if the infoset contains unit price and quantity but not the total value you could define a calculation based on those two fields. A user may be given access to SQ01 without having access to modify the underlying infoset as a security measure.

User group [SQ03]

Although infosets can be associated specifically with an authorization object, they are also associated with user groups. This enables users with query access to be limited to infosets within a certain user group. This can avoid a super user having access to sensitive information, such as HR related tables.

Quickviewer [SQVI]

Quickviewer is an all in one infoset query transaction that is generally intended for a user to create queries for their own consumption. Quickviews are only available to the user name that created them, however they can be converted into normal infosets and queries.

Query Areas

Queries can be defined in one of two areas, depending on whether they will be defined within the system they are to be used in or if the desire is to make them transportable to other systems and clients. From within any of the querying transactions you can change the query area from under environment> query areas

From there you will get a pop up where you can choose the query area desired

Global

Global queries (and infosets) are client independent and will generate a transport request (depending on the basis settings of your system). If you are creating a query in a development environment that you want to be available in the production system via a transport then this is the best type of query to use.

Local

Local queries are client dependant and the purpose is generally to write them in the system where they will be run. (However SAP does supply tools to move these as well). A local query will not generate a transport request.

Before you Develop a Query

One important step before you dive in and write a query is to make sure that the information you require isn’t freely available by some other method. If you think you will only need to run this query one time or the data comes from a single table then there may be faster ways of obtaining the data you need.

Standard reports

The first place to look is SAP’s standard reporting transactions. I’m the first person to dive into SE16N to pull out something that’s in a standard report, but often with the fact that most reports support ALV grid display it is quicker and often more effective to use standard reports. (Especially if you think end users will want to run these for themselves at some point.)

SE16N

If your data comes primarily from one table then the quickest way to query SAP is of course to use one of the table query transactions such as SE16N, SE17. Additionally if you want to get a quantitative only answer the SE16N “number of entries” button is all you need.

SE16N of course can be used to perform a single table query however if your data sets are small you can also query one table using the results of a previous query, effectively giving you the same results as a table join.

Say for example you want to get a list of the raw materials in a certain plant, normally you might think that this requires joining the tables MARA and MARC together as material type is held in MARA and plant is in MARC.

In the example below we enter our criteria and MARA returns 75 entries a manageable amount

Make sure the maximum number of hits is greater than the number found and execute

Select the column you will use as the ‘join’ to the MARC table, in this case MATNR use the control C keystroke to copy the data column in that column (in long lists you may need to page down to read the full list into memory before doing this)

You will get the following message if that needs to be done

Just page down and then control C to copy

Now exit back to the SE16N main screen and enter MARC as a table

For the ‘joining’ field (MATNR) select the multiple selection options

From the pop up select the paste from clipboard option to copy in the list from MARA.

This will now query MARC only for the raw materials you are interested in. effectively giving you the results you are looking for. Obviously this works well for 75 entries but when the numbers get large this becomes more cumbersome, and if the query needs to be repeated multiple times then a SAP query might be a better option.

Views

Views are predefined table joins that SAP has provided or that have been created by a customer directly to speed up data retrieval for applications. For many common table joins there may already be views existing that can be queried directly from SE16N

To find a view that joins the tables you want you can use the SE15 transaction

From in the repository information system you can select views and additional information

This allows you to enter a primary and base table

The list may look something like below

You can either start with the descriptions or go through them one by one to see what the view definition is.

Double clicking on one of these views will show you the available fields and the join conditions

If you find a pre-existing view which has the correct join criteria and fields you need then you can go ahead and query that view directly in SE16N

This is the end of the first part of this SAP query Step by Step guide. Next time we’ll look in depth at infosets.

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