KB14562: How to use a relationship filter to control expensive subqueries in MicroStrategy SQL Generation Engine

• Strategy

This article describes how to use relationship filters to control filter and subquery logic in MicroStrategy.

The Strategy SQL Generation Engine uses correlated subqueries found in the WHERE clause of a SQL statement when the relationship between one or more filtering attributes in the template attributes is more complex than hierarchies of attributes in one-to-many relationships. Some of the conditions that can cause a subquery to be generated for a filtering condition are as follows:

A many-to-many relationship between a template attribute and a filtering attribute
A filtering attribute that shares a common child with one of the fact table keys (this scenario will be illustrated in this article)
Joint-child attributes

NOTE: The presence of one of these conditions may not cause a subquery to be generated in every case. These are possible reasons why a subquery might be generated.
NOTE: This is not an exhaustive list of Strategy's uses of subqueries. More details on subqueries in Strategy SQL may be found in the following Strategy Knowledge Base article:
KB13870 - What is the VLDB property "Sub Query Type" in Strategy SQL Generation Engine
In cases where the attribute relationships are potentially ambiguous, the Strategy SQL Generation Engine will attempt to convert the attributes in the filtering qualification to the level of the template attributes. All of the template attributes will be considered as a unit. This sometimes produces SQL that is expensive to run on the database, particularly if multiple attribute lookup tables are combined using cross joins. In this case, the report SQL may not be possible to complete on the database.
By using relationship filters, Strategy SQL Generation Engine's automatic filter conversion can be replaced with a manual filter conversion, and the manual conversion can be targeted to specific attributes that avoid the combination of multiple attributes in the same subquery.
For an example of this situation, consider the following report definition and schema:

Note that two of the filtering conditions, Date2 and Filtering Attribute, are related to the report level and Date1 attributes through a branch of the relationship level attribute other than the branch containing the fact table. This creates an implicit many-to-many relationship and subqueries might be expected.

The report generates the following SQL:


select   a12.RptLevelID RptLevelID,
   max(a13.RptLevelDESC) RptLevelDESC,
   sum(a11.FactValue) WJXBFS1
from   KB823_FactTable   a11
   join   KB823_LU_Rel   a12
    on    (a11.RelLevelID = a12.RelLevelID)
   join   KB823_LU_RptLevel   a13
    on    (a12.RptLevelID = a13.RptLevelID)
where   (((a12.RptLevelID)
in   (select   c22.RptLevelID
   from   KB823_Alt_Fact   c21
      join   KB823_LU_Rel   c22
       on    (c21.RelLevelID = c22.RelLevelID)
   where   c21.Date2ID = CONVERT(datetime, '2006-08-16 00:00:00', 120)))
and ((a12.RptLevelID)
in   (select   c23.RptLevelID
   from   KB823_LU_FilterAttr   c21
      join   KB823_Alt_Fact   c22
       on    (c21.FilterAttrID = c22.FilterAttrID)
      join   KB823_LU_Rel   c23
       on    (c22.RelLevelID = c23.RelLevelID)
   where   c21.FilterAttrID = 100))
and a12.Date1ID = CONVERT(datetime, '2006-08-23 00:00:00', 120))
group by   a12.RptLevelID

Note that two separate subqueries are generated, one for each indirectly related attribute (Date2 and Filtering Attribute). Report SQL of this type may time out on the database.
To simplify the SQL, it must be noted that the two attributes for which subqueries were generated are related through the Alternate Key attribute. Further, this attribute may not be used itself to filter the fact table because it is neither a key of the fact table or a parent of one of the fact table keys. However, if an Alternate Key filtering level may be converted to the Relationship Level Key attribute, then this qualification may be applied directly to the fact table.
A relationship filter may be created for this purpose. The relationship filter's output level should be the Relationship Level Key attribute (the level at which the qualifications should apply), and because the two qualifications are closely related, they may both be rendered in the same filter qualification, as shown below:

Also be sure to clear the option in the advanced dialog to apply the filter independently of the relationship filter, as shown below. If this option is selected, the subqueries will still be generated.


select   a12.RptLevelID RptLevelID,
   max(a13.RptLevelDESC) RptLevelDESC,
   sum(a11.FactValue) WJXBFS1
from   KB823_FactTable   a11
   join   KB823_LU_Rel   a12
    on    (a11.RelLevelID = a12.RelLevelID)
   join   KB823_LU_RptLevel   a13
    on    (a12.RptLevelID = a13.RptLevelID)
where   (((a11.RelLevelID)
in   (select   r11.RelLevelID
   from   KB823_Alt_Fact   r11
   where   (r11.Date2ID = CONVERT(datetime, '2006-08-16 00:00:00', 120)
    and r11.FilterAttrID = 100)))
and a12.Date1ID = CONVERT(datetime, '2006-08-23 00:00:00', 120))
group by   a12.RptLevelID

Relationship filters by default do generate subqueries as in the SQL statement. However, the revised report features a much simpler subquery that combines both relevant qualifications and applies only at the specified level. The SQL using the relationship filter is certain to execute more efficiently on any database platform.
Further, if another attribute were added to the template, such as Date1, the SQL for the first report would increase in complexity while the relationship filter SQL would remain essentially unchanged (there may be an additional join to a lookup table, but the subquery would stay the same).
Thus, relationship filters are a viable way to override the default behavior of Strategy SQL Generation Engine when resolving potential filtering level difficulties and to tailor the SQL to use specific, carefully chosen attributes and tables to establish the proper relationships.

Comment

0 comments

Details

Knowledge Article

Published:

April 12, 2017

Last Updated:

August 24, 2022

select a12.RptLevelID RptLevelID, max(a13.RptLevelDESC) RptLevelDESC, sum(a11.FactValue) WJXBFS1 from KB823_FactTable a11 join KB823_LU_Rel a12 on (a11.RelLevelID = a12.RelLevelID) join KB823_LU_RptLevel a13 on (a12.RptLevelID = a13.RptLevelID) where (((a12.RptLevelID) in (select c22.RptLevelID from KB823_Alt_Fact c21 join KB823_LU_Rel c22 on (c21.RelLevelID = c22.RelLevelID) where c21.Date2ID = CONVERT(datetime, '2006-08-16 00:00:00', 120))) and ((a12.RptLevelID) in (select c23.RptLevelID from KB823_LU_FilterAttr c21 join KB823_Alt_Fact c22 on (c21.FilterAttrID = c22.FilterAttrID) join KB823_LU_Rel c23 on (c22.RelLevelID = c23.RelLevelID) where c21.FilterAttrID = 100)) and a12.Date1ID = CONVERT(datetime, '2006-08-23 00:00:00', 120)) group by a12.RptLevelID