Query Studio issue: Filters do not show in SQL WHERE clause

[conffa]

Can someone explain the following behavior and possible ways to fix it?

We have a Redshift connection to Cognos 10.2. A package was created in FM for a simple relational data model consisting of central fact tables and surrounding dimension tables. The FM project uses a 3-tier architecture approach: 1. Database layer for modeling, 2. Logical layer for translations, grouping and business transformations and 3. Presentation Layer for shortcuts from Logical layer.

When we access the package in Query Studio and start building reports, there is an issue with filtering the data. There are many cases where the filter does not show in the underlying SQL WHERE clause. It seems that Cognos queries the whole data and applies the filter locally.

This is what I have noticed:

- If a query item is dragged on a layout and that same query item is filtered, the filter does not show in SQL WHERE clause. It seems that the whole table is queried, and filtered locally in Cognos. However, when the column is removed from the layout, the filter is generated to the SQL WHERE clause. This happens for both int and string type items.
- If we let the query run through the whole table and then modify the filter by adding eg. more values to it, the WHERE clause will be generated.
- If "Prompt every time the report runs" is selected, the SQL WHERE clause is generated correctly even with the same query items on the layout
- If a dummy expression (eg. adding an empty string '', using TRIM function) is written in the Logical layer query items expression definition field, the SQL WHERE clause is generated
- This same behavior does not exist when we start building the same scenarios in other studios like Report Studio and Workspace Advanced. But if a Query studio report is directly opened in Report Studio, the problematic behavior is transferred to other Studios.

We will never want to query fact tables with tens of millions of rows as whole, but always with WHERE restrictions in place. Is there a way to turn off this local filtering behavior? The data source is currently set to "Database only".

Thank you in advance!