KB16357: How to ignore report filter qualifications below security filter level while preserving security filter qualifications in MicroStrategy SQL Generation Engine 9.x-10.x.

• Strategy

This document discusses how to ignore report filter qualifications below security filter level while preserving security filter qualifications in MicroStrategy SQL Generation Engine

SYMPTOM
A metric configured to ignore filtering on a given attribute is nonetheless filtered on the attribute when a security filter is present using an attribute from the same hierarchy.

For example, in the Strategy Tutorial project, a metric is defined as follows. The Call Center attribute is included in the metric's dimensionality settings, with Ignore filtering, as illustrated below:

Sum(Revenue) { ~+, % }
A report is created using the above metric, as illustrated below:

When this report is run by a user who has no security filter, all call centers appear in the report results because the report filter qualification on Call Center is ignored per the metric definition. This is reflected in the report SQL as well, as shown below:

select   a11.CALL_CTR_ID CALL_CTR_ID,
   max(a12.CENTER_NAME) CENTER_NAME,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from   CITY_CTR_SLS   a11
   join   LU_CALL_CTR   a12
    on    (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
group by   a11.CALL_CTR_ID
However, if a user with a security filter Region = Mid-Atlantic runs the same report, only the Washington DC call center appears. Both Washington DC and Charleston would be expected, if the Call Center qualification in the report filter were ignored.

In the report SQL, both the Region and Call Center filters apply.

select   a11.CALL_CTR_ID CALL_CTR_ID,
   max(a12.CENTER_NAME) CENTER_NAME,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from   CITY_CTR_SLS   a11
   join   LU_CALL_CTR   a12
    on    (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
where   (a11.CALL_CTR_ID in (5)
and a12.REGION_ID in (2))
group by   a11.CALL_CTR_ID
CAUSE
This issue is the result of an interaction between the way "ignore filtering" is resolved when a security filter applies to a report.

In Strategy SQL Generation Engine 9.x-10.x, the logic to apply or ignore filtering qualifications is at the hierarchy level--that is, either all filter qualifications from a given hierarchy will apply, or they will all be dropped. In the absence of a security filter, if any attribute in a hierarchy has Ignore filtering, qualifications from any level of the hierarchy will be ignored.

By design, security filters must apply to every SQL pass where it is possible to establish a relationship between the fact table and the security filtering attribute(s). In order to do this, current engine logic overrides the Ignore filtering setting for the entire hierarchy (because, in Strategy SQL generation Engine 8.x, it is not possible to set Ignore filtering for different attributes in a hierarchy). This override causes the security filter to apply, with the side effect that other qualifications, defined in the report or in the metric, will also apply.

ACTION
This issue is currently under review. Contact Strategy Technical Support for an update on the status of the issue.

WORKAROUND
Instead of ignoring qualifications below security filter level, absolute filtering can be used to "raise" the lower-level qualifications to the level of the security filter itself. That is, if the report definitions are expressed verbally, ignore vs. absolute filtering can be compared as follows:

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 ResolutionPage SizeBody HeightRectangle SizeLayout1Layout2Layout1Layout2Layout1Layout2iPhone 7 view1334*750750*12941334*7501294750750*12941334*750iPhone 4s view960*640750*12941334*7501078889750*10781334*889Ignore filteringAbsolute filteringFor call centers in the Mid-Atlantic region (security filter), show Revenue data (and ignore the report filter qualification for Washington DC).For call centers in the Mid-Atlantic region (security filter), show Revenue data for the region containing Washington DC.

In the Absolute filtering case, Mid-Atlantic is the region containing Washington DC, so the filter for the report is effectively Region = Mid-Atlantic. There is no need to introduce the Ignore filtering logic because the Call Center qualification still applies, albeit at a higher level.

If the metric is defined simply with the Region attribute set to absolute filtering and standard grouping, the metric will calculate the revenue across the entire region (not broken down by Call Center). If it is necessary to aggregate at Call Center level, add the Call Center attribute to the metric's dimensionality with filtering = None and standard grouping, as shown below:

Report results for a user with the Region security filter, and SQL, follow. As desired, both Call Centers appear in the results.

Report filter qualification
Security filter qualification
Level of filter application

select   a11.CALL_CTR_ID CALL_CTR_ID,
   max(a12.CENTER_NAME) CENTER_NAME,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from   CITY_CTR_SLS   a11
   join   LU_CALL_CTR   a12
    on    (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
where   ((a12.REGION_ID)
in   (select   s21.REGION_ID
   from   LU_CALL_CTR   s21
   where   (s21.CALL_CTR_ID in (5)
    and s21.REGION_ID in (2))))
group by   a11.CALL_CTR_ID

Comment

0 comments

Details

Knowledge Article

Published:

April 7, 2017

Last Updated:

April 7, 2017