how to debug a complicated DMR based report

Check list of all posts

This document will have walk through to demonstrate how to debug a complicated DMR report.
1. Problem
Below is Discontinuous Crosstab reports with problem in the marked area. However, when checking data with Cognos workspace advanced, we get correct data, and therefore we consider it as report problem.

This report above is Discontinuous crosstab, which has many measures with calculation on X side, and many different dimensions on Y side 

2. Analysis
it is very difficult to pin down the problem. We get correct result when removing most of measures and all other dimensions.

 This report above is Discontinuous crosstab, which has many measures with calculation on X side, and many different dimensions on Y side . the only solution is to remove measures and dimensions one by one, respectively.

3. Debug
Step 1. Remove measure 1, 2 , 3, to save report as X1, X2, X3, ….

Step 2. Define a new job and run all these reports and then identify until which measure get this problem

Step 3. Remove this identified measure(s), but the result still NOT correct. in this sample, it is almost impossible to pin down the issue, as there are two many combinations.

Step 4. However, when looking into generated SQL between report with problem and report without problem, we find the generated SQL is very different, from JOIN to UNION. but it is very difficult to see the real problem.

Step 5. Remove dimension 1, 2 , 3, to save report as Y1, Y2, Y3, ….

Step 6. Define a new job and run all these reports and then identify until which dimension get this problem

Step 7. Define a new job and run all these reports and then identify until which dimension get this problem

Step 8. Remove this identified dimension, check report and confirm that the generated report is correct. then we can be sure that this dimension has problem.

Step 9. Generate two SQLs below and compare them
- SQL generated with correct result, aka GOOD SQL
- SQL generated with wrong result, aka  BAD SQL
For this case, it is INNER JOIN with this identified dimension.  There are some dimension items that exist in FACT table, but does NOT exist in this dimension, therefore remove some data from FACT table as long as this dimension is used.