Web pivot tables by Google BigQuery Data Source Setup

It is possible to use BigQuery cloud service as a data source for PivotData microservice reports with help of ODBC driver:

1. Install Simba BigQuery ODBC driver (provided for Windows, Linux and MacOS).
2. In appsettings.json define custom ODBC-based connector in "PivotDataService" section:

   "DbConnectors" : [
     {
       "BaseConnector": "odbc",
       "Connector": "odbc-bigquery",
       "IdentifierFormat": "`{0}`",
       "ConnectionStringTpl": "{0}",
       "SelectWithLimitTpl": "{0} LIMIT {1}",
       "SqlFuncTpl": {
         "Year": "EXTRACT(YEAR FROM {0})",
         "Month": "EXTRACT(MONTH FROM {0})"
         "DayOfMonth": "EXTRACT(DAY FROM {0})"
       }
     }
   ]

3. In appsettings.json configure the cube based on this new SQL connector in "Cubes" section (add an entry):

   "Cubes": [
   {
     "Id": "bigquery-test",
     "Name": "BigQuery Test",
     "SourceType": "SqlDb",
     "SourceDb": {
       "Connector": "odbc-bigquery",
       "ConnectionString": "Driver=Simba ODBC Driver for Google BigQuery;OAuthMechanism=1;RefreshToken=[Token];Catalog=[Project];",
       "SelectSql": "SELECT * FROM `database_name`.`table_name`"
     },
     "InferSchema": true,  /* dimensions and measures are determined automatically by SELECT query resultset */
     "Dimensions": [],
     "Measures": [],
     "Parameters": []
   }
   ]
   

   Connection string can be different as described here.

4. Now you can try to build some reports by bigquery-test cube. All SQL source type options are applicable and you can use them (like custom SQL expression for dimensions, conditional JOINs for lookup resolution etc).