EducationSoftwareStrategy.com
StrategyCommunity

Knowledge Base

Product

Community

Knowledge Base

TopicsBrowse ArticlesDeveloper Zone

Product

Download SoftwareProduct DocumentationSecurity Hub

Education

Tutorial VideosSolution GalleryEducation courses

Community

GuidelinesGrandmastersEvents
x_social-icon_white.svglinkedin_social-icon_white.svg
Strategy logoCommunity

© Strategy Inc. All Rights Reserved.

LegalTerms of UsePrivacy Policy
  1. Home
  2. Topics

KB201860: A redundant WHERE clause is seen in the final pass of SQL when the metric formula involves multiple facts and one fact contains a fact extension for one of the component facts in MicroStrategy 9.x-10.x


Community Admin

• Strategy


SUMMARY
This issue has been classified as a defect in Strategy 9.x-10.x and is specific to the scenario discussed below. A fix for this issue is currently not available in the current releases, but is being evaluated for a future Strategy release.  
 
 
SYMPTOM
In Strategy 9.x-10.x, a redundant WHERE clause is seen in the final pass of SQL when the metric formula involves multiple facts and one fact contains a fact extension for one of the component facts.  
The SQL calls fact table twice and the redundant WHERE pass has the format of “where  (exists (select...... “
 
The issue is specific to metrics defined with a formula of (fact A x fact B) and fact B contains a fact extension on fact A. 
 
STEP TO REPRODUCE
The steps below are based on the Strategy Tutorial project.

  1. Create a fact called f01
  2. Create another fact called f02, and f02 has a fact extension based on fact 01
  3. Create a metric called M1 with the formula of “Sum((f01*f02)){~}”
  4. Create a report with attribute Month, Region and metric M1
  5. Execute the report and notice that the report SQL has a redundant WHERE clause as shown below:


select      a11.MONTH_ID  MONTH_ID,
                a12.region_id  region_id,
                sum((a11.MONTH_DURATION * a12.TOT_COST))  WJXBFS1
from         LU_MONTH            a11
                join          STATE_REGION_MNTH_SLS       a12
                  on          (a11.MONTH_ID = a12.MONTH_ID)
where      (exists (select        c21.MONTH_ID,
                                c21.region_id
                from         STATE_REGION_MNTH_SLS       c21
                where      c21.region_id = a12.region_id
                and         c21.MONTH_ID = a11.MONTH_ID))
group by  a11.MONTH_ID,
                a12.region_id

 
 
CAUSE
This is work by design. The WHERE clause seen in the final pass of SQL is generated by the correlated join logic for fact extension to remove double counting.
 
ACTION
A fix for this issue is currently not available in the current releases, but is being evaluated for a future Strategy release.  Contact Strategy Technical Support for an update on this issue.
 
The Strategy Internal Reference Number for the issue discussed in this technical note is 960783. 


Comment

0 comments

Details

Knowledge Article

Published:

May 30, 2017

Last Updated:

May 30, 2017