Analyzing Traces Crystal to SAP ERP and BW

Duncan Dortants,  Active Global Support, SAP, provided this ASUG webcast on Crystal Reports with SAP Tracing, intended to provide tips for enabling trace features on an SAP back end, allowing you to analyze these traces.  He reviewed the various drivers available, how to enable traces for drivers, tips and tricks on how to analyze traces, and how use this against meaningful searches

 

 

Agenda:

What are the drivers

Analyze traces

 

Crystal Reports

 

Figure 1: Source: SAP

 

Figure 1 shows 4 drivers available with BW and some against ECC

BW MDX Query driver is the driver that Crystal uses against BEx Queries and Cubes

The other 3 drivers – can be used against BW and ECC systems – specifically against underlying database clusters and InfoSets

 

How turn on traces?

 

Figure 2: Source: SAP

Figure 2 shows the Windows 64 bit registry location.  It can be used for other operation system versions as well.

Figure 2 above shows that 2 keys are set:

1)      SAP folder holds TraceDir  – what location for trace files to go to

2)      Change Trace from no to Yes – sample above shows BW MDX Query Driver –same applies to other drivers

 

How to Analyze Traces

 

Figure 3, Source: SAP

 

Example is OpenSQL driver trace; this applies to the others.  Figure 3 above shows Timestamp and ThreadID.  Two values can be important if want to filter on a specific ThreadID and see how long specific thread can be run.  You can have multiple threads going.

 

Figure 4, Source: SAP

 

Figure 4 shows logon handle or connection string.  Client, language, user name to see connection used

Open SQL will go through a series of functions that all start with a /CRYSTAL – part of the Crystal namespace.

 

Figure 5, Source: SAP

 

Figure 5 shows the /CRYSTAL/GET_OSQL_TABLE_INFO function against SFLIGHT table on BW server (same steps can be shown on an ECC backend).

    

Figure 6: Source: SAP

 

On the backend (BW or ECC), go to transaction SE37, which is the function builder on SAP system  – to call these same functions

In this example in Figure 6, the table parameter is SFLIGHT.  When you execute, you get the run time which Duncan said is valuable.  Figure 6 shows the function returns 14 fields

If run traces, you can use that to compare against SAP back end to see if data is valid.  Figure 6 shows that it took only 20 microseconds to run this function module.

Duncan said this was a good way of comparing the trace on the backend for analyzing long run time performance problems

MDX Driver

 

Figure 7: Source: SAP

 

Figure 7 shows the Timestamp and ThreadID  and that trace is enabled and the language used

 

Figure 8, Source: SAP

 

Figure 8 shows the select statement starts with MDX Query String, SELECT

The bottom line shows a cell limitation of one million which is a 7.01 BW limitation – later versions do not have that limitation, according to Duncan.

 

Figure 9: Source, SAP

 

Figure 9 shows that you can manually execute by going to MDXTEST, enter catalog and cube, copy and paste select statement and run it and it will give you sample data on what is returned.

 

Figure 10: Source, SAP

 

Sample data is returned and it is limited to what it will show.  If it ran successfully – means XI, Crystal and BW select is correct.

 

Related OSS Notes

1541104 – Enabling Crystal Report tracing

1235111 – Enabling MDX/MDA tracing

1609534 – Enabling BICs (SAPJCO tracing)