KB422082: How to query keyword objects in Salesforce.com with the MicroStrategy ODBC Driver for Salesforce Wire Protocol driver

Prior to the release of the Strategy 9.3.1, Strategy users were unable to use the Strategy ODBC Driver for Salesforce Wire Protocol to directly query a keyword object like the CASE or GROUP objects in Salesforce.  When users attempted to query a keyword object an error similar to the following would be returned:

Unexpected token: CASE in statement

• Create DSN that uses a valid Salesforce.com account.
• Open Strategy Developer.
• Connect to an existing Strategy Project such as the ‘Strategy Tutorial (Direct)’ project.
• Create a new project from the Schema menu drop-down.
• When prompted for the WH Database Instance, choose to create a new instance.  Use the newly created Salesforce DSN.  For the login information provide the proper uid/pwd.  Click OK when done.
• After the Warehouse Catalog opens, check the left window pane for the CASE or GROUP object.  Right mouse click on the table and choose to ‘Show Sample data…’.
  

• You should see the following error message:
  

• To better understand the true error message refer to the DSSErrors.log file which should show the following:
   
  

  
  2013-09-04 10:03:00.837-05:00 Database error <>: Execute Query failed. 
  
  Error type: Odbc error. Odbc operation attempted: SQLExecDirect. Unexpected token: CASE in statement

  
   
  
• At this point do not add this or any other keyword object to the Strategy project since it cannot be queried by the Strategy SQL Engine in Strategy reports.  
• Exit out of Developer and proceed to the work around section.

 
How to work around the keyword issue using the KeywordConflictSuffix key on Windows:
1. Open the 32-bit Microsoft ODBC driver manager and choose to edit the Salesforce DSN.
2. Find and choose the Advanced tab of the DSN.
3. Find the Config Options section and append to the end the following:  KeywordConflictSuffix=TABLE (you can replace ‘TABLE’ with any string up to five characters).
4. While still in the Advanced tab choose to ‘Apply’ to save the new key in the DSN.
5. Test the connection to Salesforce.com  using valid Salesforce.com credentials.

6. When you go to ‘Test Connect’ you should see the following error
:

 
7. To work around this error message you must recreate the local database that is created on the first connection to Salesforce.  On the Advanced tab of the Salesforce DSN find the Create Database option and change it to ‘1 - ForceNew’.  Hit the ‘Apply’ button to save the change in the DSN (Please see technote KB44653 for more details on the "Create Database" setting).
 
8. Choose to ‘Test Connect’ again using the valid credentials.  You should see a successful connection (note: it will take longer to connect since the local database is being recreated).
 
9. On the Advanced tab of the DSN change the ‘Create Database’ value back to ‘2 - NotExist’ and hit the ‘Apply’ button to save the change.  Then exit the DSN editor.
 
10. Re-open Strategy Developer and access the Warehouse catalog for the project.  If you still see the error message  “The configuration options used to open the database do not match the options used to create the database” it means that there are multiple copies of the local Salesforce database on your local machine.  Open the Salesforce DSN and change the ‘Create Database’ back to ‘1- ForceNew’.  Re-open the Warehouse catalog. 

 
11. You should now see the keyword objects have been mapped to a new name that includes the original object name and the suffix supplied in the KeywordConflictSuffix.

12. Note:  Once you verify that you can see the renamed objects in both 2-tier and 3-tier projects you can set back the ‘Create Database’ value to ‘2- NotExist’ so that you don’t incur the overhead cost of recreating the local database with each new connection.
13. Right mouse click on the renamed object and verify that you can query the table for  data.  You should not see any error.

 
How to work around the issue using the KeywordConflictSuffix key on UNIX/Linux:

1. Open for editing the odbc.ini that comes with Strategy Intelligence Server.
2. Find the Salesforce DSN being used for the Strategy project.
3. Locate the ConfigOptions line and edit it so that it includes the KeywordConflictSuffix value.  For example:
   
   Sample configuration option:
   
   

   
   ConfigOptions=AuditColumns=AuditOnly;MapSystemColumnNames=0;CustomSuffix=Include;KeywordConflictSuffix=TABLE
   

   
   
   
   
4. Locate the CreateDB line and edit it so that it uses a value of 1.  Note:  This is the equivalent of ForceCreate which will force the driver to recreate the local Salesforce database.  You need to make this change so that you can avoid the following error message when connecting with the Salesforce driver: “The configuration options used to open the database do not match the options used to create the database”.
5. Save and close the odbc.ini.
6. Open Strategy Developer and re-run the Warehouse Catalog.
7. Verify that you can see the renamed keyword Salesforce objects in the Strategy Warehouse Catalog.
8. Return to the odbc.ini to edit the CreateDB line so that the value is set to 2 (which is equivalent to NotExist).
9. Save and close the odbc.ini.