KB14791: Reports in SQL view are not the same when using MicroStrategy Administrator – Object Manager 9.x and 10.x to migrate a report from one project to another project

SYMPTOM:
After using Strategy Administrator - Object Manager 9.x and 10.x to migrate a report from one project to another project, the report SQL is different.
 
STEP TO REPRODUCE:
Follow the steps below to reproduce the issue in the Strategy Tutorial project:
 

• Create a report 'Report sample', with the attribute 'Region' and the metric 'Revenue'. Select to view the report in SQL view, as shown below, and save and close the report:
  

•  
  select   a12. AS REGION_ID,
           max(a13.) AS REGION_NAME,
           sum(a11.) AS WJXBFS1
  from      a11,
             a12,
               a13
  where    a11. = a12. and
           (MID(a12., 1, 3) &a12.) = (MID(a13., 1, 3) & a13. and
           a12. = a13.
  group by a12.
  
• Use Strategy Administrator - Object Manager 9.x and 10.x to move the report 'Report sample' from the Strategy Tutorial project to a Duplicated Tutorial project.
• In the Duplicated Tutorial project, select to view the report 'Report sample' in SQL view. Notice the SQL generated is different than that of the original report, as shown below:
  

•  
  select   a12.REGION_ID REGION_ID,
           a13.REGION_NAME REGION_NAME,
           sum(a11.TOT_DOLLAR_SALES) AS WJXBFS1
  from     CITY_CTR_SLS a11,
           LU_CALL_CTR  a12,
           LU_REGION    a13
  where    a11.CALL_CTR_ID = a12.CALL_CTR_ID and
           (SUBTR(a12.REGION_ID, 1, 3) a12.COUNTRY_ID) = (SUBTR(a13.REGION_ID, 1, 3) a13.COUNTRY_ID) and
           a12.REGION_ID = a13.REGION_ID
  group by a12.REGION_ID
           a13.REGION_NAME
  
• Both report's VLDB settings are set at default values without any check mark, as shown below:
  

CAUSE:
The VLDB settings for both reports are set to the default, but at the database instance level, the VLDB settings for the projects are different.
 
For the example above, in the original Strategy Tutorial project, the Query Optimizations for WHERE Clause Driving Table is set to the default 'Use fact table', as shown below:
 

 
In the Duplicated Tutorial project, the Query Optimizations for Where Clause Driving table is set to 'Use lookup table' as shown below:
 

 
ACTION:
Ensure that the VLDB settings for both project and report levels are the same. When reviewing the VLDB settings, it is best to create a report of the settings in each environment by selecting 'Create VDLB settings report' from the Tools menu, and then compare the reports to see the differences, as shown below:
 

 
NOTE: For easier comparison, users can select to show the description of each VLDB setting in the report, as shown below:
 

 
Additionally, there are freeware Microsoft Windows tools for visual file comparison available to assist users in comparing the text files, such as ExamDiff which can be downloaded.
 
Third Party Software Installation:
The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.