SYMPTOM
A user has created a metadata partition mapping with two data slices: one for current data and one for historical.
Metadata partitioning logic issues one "partitioning pre-query" per data slice, where filtering qualifications related to the partition level attributes are intersected against the data slice definition. In Strategy SQL Generation Engine 8.1.x, the above partition mapping produces the following two pre-queries when the report filter is Year in (2008).
select count(*) AS WJXBFS1
from a21
where (a21. in (2008)
and a21. in (2008))
select count(*) AS WJXBFS1
from a21
where (a21. in (2008)
and a21. <= 2007)
The second pre-query will obviously return a count of 0, because there is no year that is both 2008 and 2007-or-earlier at the same time. Thus, the second data slice is not required for the metric calculation and the corresponding partition table need not be queried.
In Strategy SQL Generation Engine 9.x, only one pre-query is generated for the same report.
select count(*) AS WJXBFS1
from a21
where (a21. in (2008)
and a21. in (2008))
Users might wonder if the partition mapping is being considered.
CAUSE
This behavior is an optimization starting in Strategy SQL Generation Engine 9.x. As of version 9, the SQL Generation Engine includes logic to compare filter expressions to determine if there is an overlap between the qualifications (i.e., if the intersection is empty or not). This logic supports the Dynamic Sourcing feature, which attempts to match standard reports against published Intelligent Cubes. For a report to match a cube, the report filter must be more restrictive than the intelligent cube's filter; the filtering comparison logic makes this determination.
For metadata partitioning, it is not important which filter is more restrictive. But, if there is any potential overlap between the data slice filter and the report filter and/or metric condition, that data slice could be relevant to the metric. If the Engine can determine in advance that the qualifications do not overlap at all, then it is a redundant operation to execute SQL against the warehouse. The pre-query for that data slice will only confirm what the Engine has already analyzed from the expressions themselves.
ACTION
To verify that the metadata partition mapping is being used, generate SQL for the report using a different filter or prompt answer. In the above example, if the filter is changed to Year in (2007), the pre-query changes:
select count(*) AS WJXBFS1
from a21
where (a21. in (2007)
and a21. <= 2007)
If the pre-query is different for different filtering elements, this is proof that the Engine is checking all of the data slices, but writing SQL only for the ones that could produce results.
Further Reading
For more information on the types of filters that can be matched using Dynamic Sourcing rules, consult the Strategy In-memory Analytics Guide Guide PDF manual, Chapter 8, "Dynamic Sourcing," under "Best practices for supporting dynamic sourcing." If a data slice qualification, or a qualification in the report filter or metric condition that is related to the partitioning level, uses features that are not supported for Dynamic Sourcing, then this optimization will not apply and the SQL Generation Engine will produce the pre-query regardless of the filtering values.
