Skip to Content

Usage of transparent filter flag

The transparent filter flag has been introduced with SPS09. Its purpose is to avoid the inclusion of attributes in aggregations when the attributes are only needed for filtering. For example, the following query

 

SELECT 
	field1, field2, SUM(m) 
FROM 
	View
WHERE
	field3 = 'X'
GROUP BY
	field1, field2

 

will force “field1” and “field2” to be kept when aggregating field “m” due to the GROUP BY. In addition, the usage of “field3” in the filter will force “field3” into the aggregation as well. By setting the transparent filter flag for “field3” you can request that “field3” should not be kept when doing the aggregation unless it is requested by the query itself.

 

Let’s have a look at two examples

The first example will lead to different results depending on whether an additional filter that actually does not reduce any data is included in the query or not. In the second example the transparent filter flag will be used and by this the same results will be returned by the queries with and without filter (remember that for illustrative purposes in these examples the filter is defined in a way that no records are removed by it).

 

Example 1 – no usage of transparent filter flag

  1. Create an example table
create column table exampleTransparentFilter(day DATE,TNR NVARCHAR(10),TTYP NVARCHAR(10));
insert into exampleTransparentFilter values ('2015-10-10','1','SL');
insert into exampleTransparentFilter values ('2015-10-11','1','SL');

 

  1. Create a Calculation View CV1 that consumes this table as a data source and defines a field C as count distinct on TNR:

  1. Create another Calculation View CV2 that simply consumes CV1 and passes through all fields
  2. Write a query on CV2 that only requests attribute “TTYP” but has a filter on attribute “Day”
SELECT
	"TTYP",
	SUM("C") AS "C"
FROM
	"CV2"
WHERE
	"DAY"!=''
GROUP BY
	"TTYP";​
  1. In the results you will see the value “2” for the count distinct:
  2. Comment the WHERE clause which does not reduce the data because the filter will never evaluate to FALSE and execute the query without the filter:
SELECT
	"TTYP",
	SUM("C") AS "C"
FROM
	"CV2"
-- WHERE
--  "DAY"!=''
GROUP BY
	"TTYP";
  1. You will now see the distinct count value “1”

 

Explanation:

When a filter is defined the respective attribute is used in the grouping for the count distinct per default. In the example above this would be attribute “Day”. The result from view CV1 therefore looks like:

The SQL query on top of the Calculation Views sums up the distinct count field “C” because only TTYP is requested in the GROUP BY and therefore the same TTYP enters twice – for each day once.

 

Example 2 – Usage of transparent filter flag

Set the transparent filter flag for the field “DAY” and run the same two queries from above. Both will return the value “1”:

 

These two examples should have demonstrated that it is important to specify what semantics is expected for a filter. Should the attribute that is used in the filter be included when aggregating, or not. If the intended behavior is to ignore the filter attribute during aggregation then the transparent filter flag can be used.

 

4 Comments
You must be Logged on to comment or reply to a post.
  • Hi Jan, if I use  input parameter to apply the filter on a column instead of where clause, does this column will be taken in Aggregation node group by?  I am assuming that, transparent filter is not required in case of Input parameter filter. can you confirm this please?

    Thanks

    Sreekanth

    • Hi Sreekanth,

      Thanks for this interesting comment. In principle you will also need a transparent filter flag for columns on which input paramter filter. However, with input parameters you have the option to define the filter below the crucial aggregation. In that case you would not need the transparent filter flag. In the example above this would mean to define an input parameter in CV2 and CV1, map the input parameters, and define the filter below the count distinct aggregation.

      Best,

      Jan