Using 'Eliminate Duplicate Rows'

This FAQ addresses the use of the 'Eliminate Duplicate Rows' checkbox within Cognos Impromptu. It is maintained by griffindm. Suggestions for additions and corrections can be addressed to him via the 'Send a Comment' link below the FAQ. All suggestions are intended for the current release of Impromptu, unless otherwise noted.

Background

When report writing, the author will occasionally come across situations where data in the report appears to contain duplications. When this occurs, it is very important for the report writer to ascertain why this is happening in order to be sure that the proper steps are taken to avoid the problem, without other issues appearing as the report is used.

Two Root Causes: First Case

When this situation happens, it usually has one of two root causes. I will cover the one most users are trying to eliminate first. Let's consider some test data:


While most of this data is from a single fact table, the state is from a child address table of a parent Vend table. Let's look at what that table might look like.



Note that vendor ABC has two addresses. When this data is pulled into your report, the data for ABC would appear twice. This could happen for all rows, if they all share the same many-to-one relationship, or for none, if all only have one now. That could change tomorrow if a vendor gets another valid address.

In this case using the 'Eliminate Duplicate Rows' checkbox in the filter of the report may be valid as long as the remaining data for the duplicated row is unique. It will give you the correct data in the above case as long as any additional rows for a vendor are from the same state. A better solution would be to determine the correct, or default address used, and only include that in your report. This is often recognizable by another row column that designates it. Failing that, you could use a Max function on the state to ensure you only get a single row returned.

Second Case: When NOT to use it

Another common situtation that gives apparent duplicate rows is NOT appropriate for this type of solution. This happens when the report does not include the key values of the fact table. Based on the limited data you see, rows that are in fact different appear as duplicates. Consider sample data again:


In this scenario, the first two lines share a common voucher number, but have different descriptions and line numbers. If your report query (not necessarily just the output) did not include either the line number or the descriptions, then Impromptu would see the first two lines as duplicates and would suppress one of them. This is NOT what you want.

When creating a report that may contain apparent duplicates, I always recommend turning off the option to 'Retrieve only the required rows' (found under menu option Report | Query | Access', proving the numeric validity of the results returned from just the fact table data only. Then after confirming this, add the other dimensional lookups while checking that the row count and report totals do not change.

What Impromptu Does When Suppressing Duplicates

When you check the box for this, Impromptu will include a distinct clause in the resulting SQL. This results in the elimination of data that are identical in the data returned. That's all it does. This process can also slow the report down significantly if there are a lot or rows and columns in the report. Impromptu has no more idea than you do as to what is truly unique to the row within the application holding the data.

While the information given here may provide a good rule to follow, the bottom line is understanding your data thoroughly is the best aid to being an effective report writer.