KB13870: What is the VLDB property "Sub Query Type" in MicroStrategy SQL Generation Engine?

• Strategy

This article describes the purpose of the Sub Query Type VLDB property in MicroStrategy

Subqueries (or correlated subqueries) are used infrequently but significantly in the Strategy SQL Generation Engine. When they do appear, report designers have some degree of control over the subquery syntax using the Very Large Data Base (VLDB) property "Sub Query Type."

This article describes how Strategy uses subqueries and how the options of the "Sub Query Type" VLDB property control the SQL syntax used.

What is a subquery?

A subquery is a secondary SELECT statement included in the WHERE clause of the primary SQL statement. When a subquery is used for filtering, its results must be correlated with the primary query in one of two ways:

Identification of columns in the subquery's SELECT clause:
SELECT Attributes, Metrics
FROM Tables
WHERE ((FilterAttribute) in (SELECT FilterAttribute
FROM FilterTable
WHERE (Qualification)
))
GROUP BY Attributes
Joins between columns in the primary query and the subquery:
SELECT Attributes, Metrics
FROM Tables
WHERE (EXISTS (SELECT *
   FROM FilterTable
   WHERE (Qualification)
    and MainTable.FilterAttribute = FilterTable.FilterAttribute
))
GROUP BY Attributes

Subqueries should be distinguished from SELECT statements in the FROM clause. Subselect statements in the FROM clause are intended for data processing prior to computing the results of the primary query. They are generally not used for filtering. That type of subquery is more accurately called a derived table or in-line view.

Where do subqueries appear in Strategy SQL?

Subqueries appear in Strategy SQL in the following circumstances:

Relationship filters: A relationship filter performs its filtering on one set of attributes, but outputs elements belonging to a different set of attributes. This requires a separate query.
Many-to-many relationships: When attributes have a many-to-many relationship and the report-level dimensionality does not include all the attributes necessary to describe the relationship completely, a subquery will be used to restrict the combination of the report-level attributes to those reflected in the relationship table between the attributes. Joint-child relationships are a special case of many-to-many relationships and will be handled similarly when needed.
Set qualifications (metric qualifications or relationship filters) combined with other qualifications in the report filter using AND NOT, OR, or OR NOT.
Absolute filtering: When a metric specifies absolute filtering on an attribute, report filters on a child attribute of the dimensional level will be raised to the dimensional level and produce SQL such as "WHERE YEAR_ID IN (SELECT YEAR_ID FROM LU_QUARTER WHERE QUARTER_ID IN (20061))."
Non-aggregatable metrics, in which a metric specifies a dimensional attribute with a grouping level of beginning (fact), ending (act), beginning (lookup), or ending (lookup).
Fact extensions may require subqueries, depending on their definition.
Certain attribute-to-attribute comparisons may require subqueries.

What are the available subquery types?
The subquery type VLDB property has the following options. It is reached in the VLDB properties editor underneath the query optimizations folder.

WHERE EXISTS (SELECT * ...)
WHERE EXISTS (SELECT col1, col2 ...)
WHERE COL1 IN (SELECT s1.COL1...) falling back to EXISTS (SELECT * ...) for multiple columns IN
WHERE (COL1, COL2...) IN (SELECT s1.COL1, s1.COL2...)
Use Temporary Table, falling back to EXISTS (SELECT * ...) for correlated subquery
WHERE COL1 IN (SELECT s1.COL1...) falling back to EXISTS (SELECT col1, col2 ...) for multiple columns IN
Use Temporary Table, falling back to IN (SELECT COL) for correlated subquery

The subquery types exist to support different database platforms' syntax restrictions. Subqueries may be written in one of these general forms:

Single column form:

where (col) in (select col from ...)

Multiple column forms:

where (col1, co12) in (select col1, col2 from ...)
where exists (select col1, col2 from ...)
where exists (select * from ...)

Most databases support only some, but not all, of these syntax forms. Strategy specifies a default value for each database platform that will generate appropriate SQL for the database.

This is also the reason for the subquery types that "fall back to" a different SQL form for multiple columns. Some databases support "column IN (SELECT...)" when there is only one column, but do not support the same syntax for two or more columns. In that case, EXISTS is the alternate syntax that should be used. When the subquery type VLDB property specifies a fallback position, Strategy SQL Generation Engine 8.x will automatically detect which one should be used.

NOTE: While some VLDB properties are intended to alter the report's results, this VLDB property should affect syntax only. Each form of SQL is expected to return the same results for the same Strategy report definition.

Usage examples

The following examples illustrate cases where changes to the subquery type VLDB property result in alterations to the SQL:

Relationship filtering

This example illustrates two of the possible SQL syntax changes that may be specified for relationship filters of queries: first, a change in the subquery form (IN SELECT vs. EXISTS), and second, subquery vs. temporary table.
The report is defined as follows:

Here, the condition in the relationship filter is highlighted in blue, and the subquery's relationship to the primary query is highlighted in red. The subquery is reorganized slightly but its meaning does not change.

This is the target panel’s key
<pr n="IntelligenceServer" v="ISERVER_NAME" desc="The Intelligence Server to use for authentication when creating a certificate" /><pr n="IntelligenceServerPort" v="0" desc="Intelligence Server port - 0 means use the default port" /><pr n="ProjectName" v="PROJECT_NAME" desc="The project to use for authentication" /><pr n="AuthMode" v="1" desc="The Intelligence Server authentication mode" /><pr n="JKSLocation" v="WEB-INF/myKeystore.jks" desc="The JKS containing the signing certificate" /><pr n="JKSAlias" v="myCertificate" desc="The alias of the signing certificate in the key store" /><pr n="JKSPassword" v="password" desc="The password of the JKS keystore" /><pr n="DERCertificateLocation" v="WEB-INF/pub.der" desc="The certificate to use for signing, in DER format" /><pr n="DERPrivateKey" v="WEB-INF/prv.der" desc="The private key of the signing certificate, in DER format" /><pr n="X509Country" v="US" desc="The country to use in the certificate's DN" /><pr n="X509Organization" v="MSTR" desc="The organization to use in the certificate's DN" /><pr n="X509Location" v="vienna" desc="The location to use in the certificate's DN" /><pr n="CRLFile" v="cert-srv.crl" desc="Name of the CRL file - it will be created at the top level of the application path" /><pr n="CDPLocation" v="http://machinename:port/cert/cert-srv.crl" desc="CRL Distribution Point URL is required by some application servers (e.g. IIS). If empty, no CDP will added to new certificates. We also recognize the %HOST% macro (case sensitive), which will be replaced by the Certificate Server's fully qualified host name, e.g. http://%HOST%:8080/CertificateServer/cert-srv.crl" />Year_ID Last_year_ID20132012201420132015201420162015Year_IDYear_dateYear_durationPrev_Year_ID2012Sun Jan 01 00:00:00 BRST 201236620112013Tue Jan 01 00:00:00 BRT 201336520122014Wed Jan 01 00:00:00 BRT 201436520132015Thu Jan 01 00:00:00 BRT 20153652014SyntaxExplanation0Digit placeholder. If the number contains fewer digits than the format contains placeholders, the number will be padded with zeros. If there are more digits to the right of the decimal than there are placeholders, the decimal portion will be rounded to the number of places specified by the placeholders. If there are more digits to the left of the decimal than there are placeholders, the extra digits will be retained.#Digit placeholder. This placeholder functions the same as the 0 placeholder, except that the number will not be padded with zeros if the number contains fewer digits than the format contains placeholders.?Digit placeholder. This placeholder functions the same as the 0 placeholder, except that spaces are used to pad the digits.. (period)Decimal point. Determines how many digits (0's or #'s) are displayed on either side of the decimal point. If the format contains only #'s to the left of the decimal point, numbers less than 1 will begin with a decimal point. If the format contains 0's to the left of the decimal point, numbers less than 1 will begin with a 0 to the left of the decimal point%Displays the number as a percentage. The number is multiplied by 100 and the % character is appended., (comma)Thousands separator. If the format contains commas separated by #'s or 0's, the number will be displayed with commas separating the thousands. A comma following a placeholder scales the number by a thousand. For example, the format ' 0, ' scales the number by 1000 (e.g., 10,000 is displayed as 10).E- E+ e- e+Displays the number in scientific notation. If the format contains a scientific notation symbol to the left of a '0' or '#' placeholder, the number will be displayed in scientific notation, and an 'E' or 'e' will be added. The number of 0 and # placeholders to the right of the decimal determines the number of digits in the exponent. 'E-' and 'e-' place a minus sign by negative exponents. 'E+' and 'e+' place a minus sign by negative exponents and a plus sign by positive exponents.$ - + / ( ) : spaceDisplays that character. To display a character other than those listed, precede the character with a backslash (\) or enclose the character in double quotes. The slash can also be used for fraction formats.\Displays the character that follows it. The backslash itself is not displayed. One or more characters can also be displayed by enclosing the characters in double quotes."text"Displays the text contained within the double quotes.@Text placeholder. Text replaces the @ format character.*Repeats the next character until the width of the column is filled. Only one asterisk is permitted in each format section._ (underscore)Skips the width of the next character. For example, to make negative numbers surrounded by parentheses align with positive numbers, the format '_)' can be included for positive numbers in order to skip the width of a parenthesis.[BLACK]Displays text in black.[BLUE]Displays text in blue.[CYAN] Displays text in cyan.[GREEN] Displays text in green.[MAGENTA] Displays text in magenta.[RED] Displays text in red.[WHITE] Displays text in white.[YELLOW] Displays text in yellow.SQL with Subquery Type 3SQL with Subquery Type 2select   a12.QUARTER_ID QUARTER_ID,
   max(a13.QUARTER_DESC) QUARTER_DESC,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from   STATE_REGION_MNTH_SLS   a11
   join   LU_MONTH   a12
    on    (a11.MONTH_ID = a12.MONTH_ID)
   join   LU_QUARTER   a13
    on    (a12.QUARTER_ID = a13.QUARTER_ID)
where   ((a11.REGION_ID)
in   (select   r11.REGION_ID
   from   STATE_SUBCATEG_REGION_SLS   r11
   where   r11.CUST_STATE_ID in (5)))
group by   a12.QUARTER_IDselect   a12.QUARTER_ID QUARTER_ID,
   max(a13.QUARTER_DESC) QUARTER_DESC,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from   STATE_REGION_MNTH_SLS   a11
   join   LU_MONTH   a12
    on    (a11.MONTH_ID = a12.MONTH_ID)
   join   LU_QUARTER   a13
    on    (a12.QUARTER_ID = a13.QUARTER_ID)
where   (exists (select   r11.REGION_ID
   from   STATE_SUBCATEG_REGION_SLS   r11
   where   r11.CUST_STATE_ID in (5)
    and   r11.REGION_ID = a11.REGION_ID))
group by   a12.QUARTER_IDSQL with Subquery Type 3SQL with Subquery Type 5select   a12.QUARTER_ID QUARTER_ID,
   max(a13.QUARTER_DESC) QUARTER_DESC,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from   STATE_REGION_MNTH_SLS   a11
   join   LU_MONTH   a12
    on    (a11.MONTH_ID = a12.MONTH_ID)
   join   LU_QUARTER   a13
    on    (a12.QUARTER_ID = a13.QUARTER_ID)
where   ((a11.REGION_ID)
in   (select   r11.REGION_ID
   from   STATE_SUBCATEG_REGION_SLS   r11
   where   r11.CUST_STATE_ID in (5)))
group by   a12.QUARTER_IDselect   distinct r11.REGION_ID REGION_ID
into #ZZRF00
from   STATE_SUBCATEG_REGION_SLS   r11
where   r11.CUST_STATE_ID in (5)

select   a13.QUARTER_ID QUARTER_ID,
   max(a14.QUARTER_DESC) QUARTER_DESC,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from   STATE_REGION_MNTH_SLS   a11
   join   #ZZRF00   pa12
    on    (a11.REGION_ID = pa12.REGION_ID)
   join   LU_MONTH   a13
    on    (a11.MONTH_ID = a13.MONTH_ID)
   join   LU_QUARTER   a14
    on    (a13.QUARTER_ID = a14.QUARTER_ID)
group by   a13.QUARTER_ID

drop table #ZZRF00SQL with Subquery Type 3SQL with Subquery Type 5select   a12.QUARTER_ID QUARTER_ID,
   max(a14.QUARTER_DESC) QUARTER_DESC,
   a13.SUBCAT_ID SUBCAT_ID,
   max(a15.SUBCAT_DESC) SUBCAT_DESC,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from   ITEM_MNTH_SLS   a11
   join   LU_MONTH   a12
    on    (a11.MONTH_ID = a12.MONTH_ID)
   join   LU_ITEM   a13
    on    (a11.ITEM_ID = a13.ITEM_ID)
   join   LU_QUARTER   a14
    on    (a12.QUARTER_ID = a14.QUARTER_ID)
   join   LU_SUBCATEG   a15
    on    (a13.SUBCAT_ID = a15.SUBCAT_ID)
where   ((a13.SUBCAT_ID)
in   (select   c22.SUBCAT_ID
   from   REL_CAT_ITEM   c21
      join   LU_ITEM   c22
       on    (c21.ITEM_ID = c22.ITEM_ID)
   where   c21.CAT_ID in (4)))
group by   a12.QUARTER_ID,
   a13.SUBCAT_IDselect   a12.QUARTER_ID QUARTER_ID,
   max(a14.QUARTER_DESC) QUARTER_DESC,
   a13.SUBCAT_ID SUBCAT_ID,
   max(a15.SUBCAT_DESC) SUBCAT_DESC,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from   ITEM_MNTH_SLS   a11
   join   LU_MONTH   a12
    on    (a11.MONTH_ID = a12.MONTH_ID)
   join   LU_ITEM   a13
    on    (a11.ITEM_ID = a13.ITEM_ID)
   join   LU_QUARTER   a14
    on    (a12.QUARTER_ID = a14.QUARTER_ID)
   join   LU_SUBCATEG   a15
    on    (a13.SUBCAT_ID = a15.SUBCAT_ID)
where   (exists (select   *
   from   REL_CAT_ITEM   c21
      join   LU_ITEM   c22
       on    (c21.ITEM_ID = c22.ITEM_ID)
   where   c21.CAT_ID in (4)
    and   c22.SUBCAT_ID = a13.SUBCAT_ID))
group by   a12.QUARTER_ID,
   a13.SUBCAT_ID

By changing to one of the "Use Temporary Table" options, the reorganization is more dramatic. Again, however, the meaning is the same.

For reports with several relationship filters, temporary table syntax may execute significantly faster on the database.

This behavior is documented in greater detail in the following Strategy Knowledge Base article:
KB3859 - How to use the sub query VLDB setting to optimize performance for attribute relationship filtering in Strategy

Attribute filtering where the attribute in the filter has a many to many relationship with a template attribute

The filtering attribute, Catalog, has a many to many relationship with Item (of which Subcategory, on the template, is a parent). Thus, the subquery identifies which subcategories meet the Catalog qualification.

Note that an attempt to eliminate the subquery using the "Use Temporary Table" option does not, in fact, remove the subquery. The fallback position will be used for many to many relationships. This is because joining an intermediate table in this context may result in multiple counting.

NOTE: In Strategy SQL Generation Engine, in some cases it is possible to combine multiple subqueries using database-side set operators to improve performance. Consult the following Strategy Knowledge Base article for more details:

KB13530: How does the VLDB property "Set Operator Optimization" work in Strategy SQL Generation Engine

Comment

0 comments

Details

Knowledge Article

Published:

April 14, 2017

Last Updated:

April 14, 2017