KB483973: How to modify Google BigQuery driver's string column length for optimized memory consumption

Google BigQuery ODBC Driver

In order to improve memory consumption, Strategy recommends that you reduce the default string length to the smallest possible number, meaning the maximum length of a string column in your warehouse's table. When publishing a cube or fetching data from BigQuery, Strategy allocates memory based on the string column length value. Thus, a small number reduces the memory consumption when publishing a cube or fetching data.

On Windows

• Open the ODBC Data source administrator (Run >

  odbcad32.exe).

• Edit the DSN.
• Click on Advanced options.
  

• Change the Default String Column Length to the maximum string length that exists in the warehouse.
  

• Click OK to save your changes on the Advanced Options dialog.
• Click OK to save your changes on the Strategy ODBC Driver for Google BigQuery DSN Setup dialog.
• Restart the Intelligence Server or delete cached connections for BigQuery for the changes to take effect.

On Linux

• Go to the folder where Strategy is installed. For example, the MSTR_HOME folder is

  /opt/mstr/Strategy.

• Edit the

  odbc.ini 

  file where the BigQuery DSN is defined.
• Modify the string length of DefaultStringColumnLength to the maximum string length that exists in the warehouse
  

• Save the

  odbc.ini

  file.
• Restart the Intelligence Server or delete cached connections for BigQuery for the changes to take effect.

Google BigQuery JDBC Driver

Data Import

• In the case that your BigQuery connection is created in data import, edit the database connection in the data import dialog (in Web/Workstation/Desktop).
  

• Click on Show connection string.
• Select Edit connection string.
  

• Add the following string in the Connection String field:
  StringColumnLength=4000
  Change the value 4000 to the maximum string length that exists in the warehouse.
  

Primary Database Instance (in Strategy Developer)

• If the JDBC connection is a primary database instance created in Developer, edit the database instance in Developer.
  

• Click on Modify to edit the database connection.
  

• Copy the connection string parameters to a text editor.
  

• Add the following string to the connection string and paste it back to the Additional connection string parameters field:
  StringColumnLength=XX
  Where XX is the maximum string length that exists in the warehouse.
  

• Click OK to save your changes on the Database Connections dialog. 
• Click OK to save your changes on the Database Instances dialog.