KB7233: Special considerations when using outer join SQL with a Microsoft Access warehouse in MicroStrategy SQL Generation Engine

 
By default, the Strategy SQL Generation Engine does not generate outer joins against Microsoft Access. While Access does allow outer join SQL, special considerations must be met for Access to execute properly: 

1. Microsoft Access support both SQL 89 and SQL 92 for inner joins.
2. Microsoft Access 7.0 only supports SQL 89 syntax for cross joins.
3. Microsoft Access 7.0 only supports SQL 92 syntax for outer joins.
4. Microsoft Access 7.0 can only support either SQL 89 or SQL 92 (not both) in a given single pass of SQL.

 
Because of these considerations, outer joins are disabled by default for Access.
 
In the Strategy Very Large Database (VLDB) editor, there is a VLDB setting called 'Join Type' that has four possible settings:

1. 'Join 89'
2. 'Join 92'
3. 'SQL 89 Inner Join and Cross Join and SQL 92 Outer Join'
4. 'SQL 89 Inner Join and SQL 92 Outer Join and Cross Join'

 
None of these are optimal for Microsoft Access; and depending on the situation, some will work and some will not.
Access defaults to 'SQL 89', effectively disabling outer joins. This guarantees that no report fails as a result of join syntax conflicts.
 
 
If outer joins are required for a specific report, the 'SQL 92' setting will work in most situations. Specifically, outer joins generated via the Metric Join Type settings or by the 'Preserve all final pass result elements' VLDB setting should generally work properly.
 
 
However, when the 'Preserve all lookup table elements' VLDB setting is enabled, syntax errors will often occur as a result of combining cross joins, outer joins and inner joins in various ways. This issue should be resolved by either changing to another warehouse type which fully supports outer joins or by analyzing the circumstances and designing a syntax workaround specific to the problem (using filtering changes, VLDB settings, etc.)
 
 
Note: Microsoft Access 2000 and later versions are more flexible in their support of outer joins. For these versions of Access, set the 'join type' VLDB property to 'Join 92' and most outer join scenarios should execute correctly.
Note also that Microsoft Access is neither supported nor certified as a production warehouse or metadata. It may be used for prototyping but should not be used for development of projects that will be moved into production. Users should not expect the same level of SQL compatibility with Access as they would obtain from other certified database platforms.