How Do You Debug OLAP DML from BI Beans Part 2

This is part two of a post covering how to debug OLAP DML from BI Beans. The first session explained how to enable tracing of OLAP DML from BI Beans. This session explains the output from the various debug options. There are three basic options

• Prgtrace
• Monitor
• Trackprg

In addition Badline can be used to uncover additional information about an error.

Prgtrace
Controls whether each line of a program is recorded in the current session. It is used to uncover problems by tracing the execution of a program and records the name of the current program and shows the line of code being executed. In the example below the program being called is RUN_FCAST_MODEL, which in turn calls CALC_FCAST_MODEL. Prgtrace can be used to examine program flow within a session. Below is an example of the output when prgtrace is enabled:

(PRG= UTILS!RUN_FCAST_MODEL) argument T_PRESCONTEXT text
(PRG= UTILS!RUN_FCAST_MODEL) argument T_DIMCONTEXT text
(PRG= UTILS!RUN_FCAST_MODEL) argument T_DEBUGCONTEXT text
(PRG= UTILS!RUN_FCAST_MODEL) argument T_TIME_FP text
(PRG= UTILS!RUN_FCAST_MODEL) argument T_TIME_PP text
(PRG= UTILS!RUN_FCAST_MODEL) argument T_METHOD text
(PRG= UTILS!RUN_FCAST_MODEL) argument T_MEASURE0 text
(PRG= UTILS!RUN_FCAST_MODEL) argument T_MEASURE1 text
(PRG= UTILS!RUN_FCAST_MODEL) argument T_MEASURE2 text
(PRG= UTILS!RUN_FCAST_MODEL) argument T_MEASURE3 text
(PRG= UTILS!RUN_FCAST_MODEL) argument T_MEASURE4 text
(PRG= UTILS!RUN_FCAST_MODEL)
(PRG= UTILS!RUN_FCAST_MODEL) variable T_MEASURE text
(PRG= UTILS!RUN_FCAST_MODEL) variable T_MODE text
(PRG= UTILS!RUN_FCAST_MODEL) variable T_VAR text
(PRG= UTILS!RUN_FCAST_MODEL)
(PRG= UTILS!RUN_FCAST_MODEL) trap on ERROR
(PRG= UTILS!RUN_FCAST_MODEL) pushlevel 'RUN_FCAST_MODEL'
(PRG= UTILS!RUN_FCAST_MODEL) push SHAWPRODUCTS, SHAWPROMOTIONS, SHAWCHANNELS, SHAWGEOGRAPHIES, SHAWT_TIME, prgtrace, badline
(PRG= UTILS!RUN_FCAST_MODEL)
(PRG= UTILS!RUN_FCAST_MODEL) context T_PRESCONTEXT apply
(PRG= UTILS!RUN_FCAST_MODEL)
(PRG= UTILS!RUN_FCAST_MODEL) switch upcase(T_METHOD)
(PRG= UTILS!RUN_FCAST_MODEL) case 'SEASONAL':
(PRG= UTILS!RUN_FCAST_MODEL) T_MODE = 'WINTERS'
(PRG= UTILS!RUN_FCAST_MODEL) break
(PRG= UTILS!RUN_FCAST_MODEL)
(PRG= UTILS!RUN_FCAST_MODEL)
(PRG= UTILS!RUN_FCAST_MODEL) if T_MEASURE1 eq 'on'
(PRG= UTILS!RUN_FCAST_MODEL) else if T_MEASURE2 eq 'on'
(PRG= UTILS!RUN_FCAST_MODEL) else if T_MEASURE3 eq 'on'
(PRG= UTILS!RUN_FCAST_MODEL) else if T_MEASURE4 eq 'on'
(PRG= UTILS!RUN_FCAST_MODEL) then T_MEASURE = 'SHAWSALES_AMOUNT_SOLD_VARIABLE'
(PRG= UTILS!RUN_FCAST_MODEL)
(PRG= UTILS!RUN_FCAST_MODEL) if T_MEASURE eq 'SHAWSALES_MARGIN_PERCENT'
(PRG= UTILS!RUN_FCAST_MODEL) else CALL calc_fcast_model(T_MODE, T_MEASURE, 'SHAWSALES_AMOUNT_SOLD_FCAST_BF_VARIABLE')
(PRG= UTILS!CALC_FCAST_MODEL) "============================================================================================================================
(PRG= UTILS!CALC_FCAST_MODEL) " Created by Keith Laker November 2002
(PRG= UTILS!CALC_FCAST_MODEL) " Modified by Dave Wild : 17th Dec 2002
(PRG= UTILS!CALC_FCAST_MODEL) " Modified by Keith Laker : 29 Aug 2003
(PRG= UTILS!CALC_FCAST_MODEL) "============================================================================================================================
(PRG= UTILS!CALC_FCAST_MODEL) " This Program creates AW forecasts using one of the following AW Foprecasting methods
(PRG= UTILS!CALC_FCAST_MODEL) " These methods are passed into this program as arguments via _method
(PRG= UTILS!CALC_FCAST_MODEL) (PRG= UTILS!CALC_FCAST_MODEL) "
(PRG= UTILS!CALC_FCAST_MODEL) " T_METHOD arguments include:
(PRG= UTILS!CALC_FCAST_MODEL) " WINTERS - Holt Winters Seasonal
(PRG= UTILS!CALC_FCAST_MODEL) " TREND - Trend
(PRG= UTILS!CALC_FCAST_MODEL) " EXPO - Exponential


Each line of code that is shown contains important information. For example, if the start of the line includes an equals sign, this indicates the code is executed as a compiled line of code:

(PRG=RUN_MODEL)limit SHAWT_TIME to '1813'

if line includes a colon, this indicates execution of uncompiled code. Typically, uncompiled code is slower to execute than compiled, as it has to be parsed and validated prior to execution. If the line is executed within a loop then this could consume a large amount of processing time.

(PRG:RUN_MODEL)limit SHAWT_TIME to &arg1

Uncompiled code usually occurs when dynamic substitution is used to alter the way a line is executed as shown above. In this case the time dimension is limited to a specific dimension member that is not known until execution time. Hence the &arg1 is used to parse the first argument passed to the program.


Badline
This can be set to get more detailed error messages in the log file and may help to provide more information regarding a particular issue.

Monitor
The MONITOR command records data on the performance cost of each line in a specified program. It is used to identify the time-consuming lines within those programs.–If the program executes a given line repeatedly, MONITOR records the cumulative cost of all the executions on the single line –A line of code is considered to have a high performance cost if it takes a long time to execute. If you wish, you can use both TRACKPRG and MONITOR simultaneously.


Trackprg
Tracks the performance cost of every program that runs while you have tracking turned on. Each time each program is executed, TRACKPRG stores its cost data as one entry in its tracking list. A program or line of code is considered to have a high performance cost if it takes a long time to execute. Use TRACKPRG to identify programs that have relatively high costs and then use the MONITOR command to identify the time-consuming lines within those programs. If you wish, you can use both commands simultaneously.


With the help of these three debugging options it is possible to determine how and why programs are being executed within the AW. Once you know where the problem is, then you can use the OLAP Worksheet within Analytic Workspace Manager to edit the program and make the required changes.

It is always a good idea to test any OLAP DML program within the OLAP Worksheet first before attaching the program a JSP page or calling it from another application. That way you can resolve the more obvious issues first. More technical issues can then be resolved using PRGTRACE, MONITOR and TRACKPRG as described