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})"
          "Day": "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).