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
What are the drivers
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
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)