How to save hours of frustration when working SAP Application Process Designer (APD)

Working with SAP Application Process Designer (APD), however, was anything but straight-forward. The Business Explorer (BEx) query report was showing one set of numbers while the flat-file seemed to display whatever it wanted at the moment. After hours of banging my head against my desk, I have discovered 3 tips that will help to save you the frustration and recoup several hours from your workday.  Here is a summary of the three tips followed by a more detailed explanation.

Three Rules for Working with SAP APD

1. Unhide all fields

2. 'Display Data'

3. Create formulas in APD

Before explaining the three rules in detail, I want to show you a before picture.  Below is a screen-capture, comparing the query results with the generated flat file.  The value of "50" for Safety Stock in the report is correct but the value of "0.000" in the APD flat file is wrong. (Click on the image below for a larger picture)

What is causing the values to jump around and be displayed in arbitrary columns?  For that, we will need to take a closer look at the query definition. 

Notice below how there are a series of green and blue icons in the columns section of the query designer.  The green icons are your visible fields and the blue icons signify that a field (key figure, calculation, formula, restricted key figure, etc.) is hidden from view.  Not only is that field hidden in the report output but it is hidden from the APD mapping as well.

Rule 1: Unhide all fields in APD

Your life will be much better for it. See below.

Apparently, un-hiding the fields in Query Designer help APD to put the output values in the correct buckets.  Otherwise, selectively displaying and hiding column fields will make numbers omelet look like scrambled eggs.

So now that you are displaying all of the report fields, how do you go about verifying that the flat-file output will match the Business Explorer (BEx) query?  Well, you test the APD of course!  How you go about testing the APD is another thing altogether.

To start this topic, here is a sample APD. Notice there are five distinct steps between the initial query result (step 1) to the final flat-file layout (step 5).

So the question is "If the query result from step 1 does not match the data in the final flat-file in step 5, then how do you find out what went wrong and where it went wrong"?

One approach (that I am embarrassed to admit guilt of doing) is to run the ADP, compare the results from step 1 and step 5, and adjust the APD.  That is, I tried adjusting the join in step 2. When that didn't work, I tried adjusting the calculation in step 3. When that didn't work, I tried adjusting the display/hide field mapping in step 4. When that didn't work, I would bang my head against the desk, rinse, and repeat.  It's not the best approach or most methodical approach.

Rule 2: Display Data

A more pragmatic approach is to use the "Display Data". See below:

Performing a Right-Click on a node (steps 1 through 5) will display the data at it exists at that point in the execution of the APD transformation. 

For instance, I realized that my selective display and hide of the report fields was causing an issue after having verified the query results by selecting "Display Data" on the Query Result node at Step 1.

Rule 3: Create formulas in APD

After verifying the query output in the APD at step 1 matches my expectations, I select "Display Data" at the Join node in Step 2. At this stage, I find that my calculation for Open Purchase Order Quantity and Open Sales Order quantity was displaying the results in scientific notation.  For example, the value "24" was displayed as "2.4000000000000000E+01".  That was easily fixed by performing the calculation in the APD as a node, instead of the performing the calculation in the query.

Well, those are my 3 tips to making working with APD a better experience. I hope the tips saved you a few hours. =D