Google BigQuery pivot table reports

Google BigQuery can be used as a data source with SQL-compatible database connector and Simba ODBC driver.

There are no any limitations on the dataset size and in this you can get reports by billions-size datasets in the near real-time. This youtube video illustrates how sample 1.3Gb data is aggregated by BigQuery in seconds and displayed as a pivot table by SeekTable.

BigQuery connection setup

Connection String should be a valid connection string for Simba ODBC driver for Google BigQuery; for example:

Driver=Simba ODBC Driver for Google BigQuery;OAuthMechanism=1;RefreshToken=your_google_oauth_refresh_token;Catalog=your_api_project_id;

How to get a refresh token

If you don't have a refresh token you get it in one of the following ways:

• [simple way] Use Simba-provided credentials as described in Simba's documentation or just follow these steps:
  1. Open this link to generate Authentication Token.
  2. Enter your Authentication Token here to get a refresh token:
  3. Use this value for RefreshToken option in the connection string
• Use Google OAuth 2.0 Playground to generate a refresh token for 'bigquery.readonly' scope.
  Important notice: Playground's RefreshToken works only for 24h; to generate a permanent RefreshToken:
  1. Use your Google user account to generate Google OAuth client ID credentials
  2. In Playground, go to "OAuth 2.0 configuration" ("Gear" icon on the right), enter your "OAuth Client ID" and "OAuth Client secret"
  3. Don't forget to specify your ClientId and ClientSecret in the connection string.

Inferring schema for tables with ARRAY columns

If your table has an ARRAY-type columns and you got an error like Error converting invalid input with source encoding UTF-8 using ICU:

1. exclude these columns from the Select Query, for example:

   SELECT * except(array_col1, array_col2) FROM some_table

   and save the cube form.
2. Then go to the cube configuration form; you should see dimensions & measures that are populated automatically.
3. Change the query to:

   SELECT * FROM some_table

   do NOT check Infer dimensions and measures by dataset. Save the form.

Date-part dimensions calculated with SQL expressions

To define date-part dimensions (date's "year", "month", "day" etc) you may add dimensions defined with an appropriate SQL expression:

1. add new dimension with Type=Field
2. fill Name with some unique value: say, "date_column_year"
3. add one Parameter which should be a date-part BigQuery SQL expression:
   • For year: EXTRACT(YEAR FROM date_column)
   • For month: EXTRACT(MONTH FROM date_column) + Format={0:MMM} (Jan, Feb etc) or {0:MMMM} (January, February etc)
   • For day: EXTRACT(DAY FROM date_column)
   • For day-of-week: EXTRACT(DAYOFWEEK FROM date_column)-1 + Format={0:ddd} (Mon, Tue etc) or {0:dddd} (Monday, Tuesday etc)
   • For quarter: EXTRACT(QUARTER FROM date_column)
   • For week-of-year: EXTRACT(ISOWEEK FROM date_column)