BigQuery BI Tool for pivot tables, charts, tabular reports

SeekTable is a self-service BigQuery BI tool that allows users to create pivot tables, charts, and flat table reports directly from BigQuery databases:

• Live BigQuery connection: report queries execute in real-time, meaning your operational reports always up-to-date (no need in refreshes).
• No ETL / zero data extraction: analyze massive datasets directly in your data warehouse, no data imports required.
• Designed for business users: Excel-like web interface enables non-IT users to perform fast data analysis and data visualization without writing SQL or exporting sensitive data.
• Governed data access: safely share common reports and data cubes with RLS. End-users queries are limited to configured data models (no direct SQL access to BigQuery).
• On-premise reporting tool: self-hosted SeekTable meets strict corporate compliance and data governance requirements. It supports fully isolated deployment environments to deliver real-time insights without your data ever leaving your internal network.
• Embedded BI: seamlessly integrate SeekTable published reports and dashboards directly into your web app. Or embed the entire SeekTable app view to let them build their own reports directly inside your app.
• White-labeling: apply your own logo, branding, and UI styles to make SeekTable look exactly like your company's own in-house reporting solution.

This youtube video illustrates how sample 1.3Gb data is aggregated by BigQuery in seconds and displayed as a pivot table in SeekTable.

Setup BigQuery Connection

1. Click the "Connect to Database" to open a new data cube configuration form.
2. Choose Data Source Type: SQL-compatible database (this opens SQL Settings section).
3. Choose Database Connector: Google BigQuery
   
4. Connection String should include settings that are supported by an official BigQuery ODBC driver:

   ClientId=oauth_client_id;ClientSecret=oauth_client_secret;RefreshToken=your_google_oauth_refresh_token;Catalog=your_api_project_id;

   Catalog	The name of your BigQuery project. This project is the default project that the Simba ODBC Driver for Google BigQuery queries against.
   RefreshToken	The refresh token that you obtain from Google for authorizing access to BigQuery. Section below explains how to generate the token.
   ClientId	OAuth Client ID used for refresh token creation (may be omitted if refresh token was generated with ODBC driver's get_refresh_token.py script).
   ClientSecret	Corresponding OAuth Client secret (may be omitted if refresh token was generated with ODBC driver's get_refresh_token.py script).

5. Configure other required settings as described in SQL-compatible data source. As a minimum you need to specify Select Query (which determine's the dataset) and keep Infer dimensions and measures by dataset checked.

How to get a refresh token

If you don't have a refresh token provided by your BigQuery administrator, you can get it in one of the following ways:

• Allow access to BigQuery for SeekTable's Client ID on this page
• Create your own OAuth Client ID and get a refresh token in the OAuth playground
• Use a python script that is provided with BigQuery ODBC-driver: see BigQuery ODBC driver Installation and Configuration Guide for more details and instructions.

Allow access to BigQuery for SeekTable's Client ID

1. Get an access token for SeekTable's Client ID
   This button opens a pop-up window. If you don't see it, please allow pop-ups for this page.
2. Refresh Token
3. Specify the following "ClientId" and "ClientSecret" values in the connection string:

   ClientId=@BQ_SEEKTABLE_CLIENT_ID;ClientSecret=@BQ_SEEKTABLE_CLIENT_SECRET;

Create your own Client ID

1. Go to your Google Cloud Console - Credentials (for an appropriate project selected)
2. Click "Create Credentials" - "OAuth Client ID"
   Fill the form:
   Application Type = Web Application
   Name = SeekTable Client
   Authorized redirect URIs: click "ADD URI" and enter:

   https://developers.google.com/oauthplayground

3. In "Oauth client created" dialog you get "Client ID" and "Client secret" values
4. Go to Google OAuth Playground
5. Click "Gear" icon (top-right corner), click on "Use your own OAuth credentials" and enter your "Client ID" and "Client secret" values.
6. In the left panel ("Select & authorize APIs" step) enter

   https://www.googleapis.com/auth/bigquery

   and click the "Authorize APIs" button.
7. You should see "Refresh token" filled. Use this value in the connection string for SeekTable's BigQuery connector. Don't forget to specify corresponding ClientId and ClientSecret connection string options.

Alternatively, you can (section "Configuring Authentication on a Non-Windows Machine") and generate a refresh token using Simba-provided python script get_refresh_token.py.

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} (0=Sun, 1=Mon, 2=Tue etc) or {0:dddd} (0=Sunday, 1=Monday, 2=Tuesday etc)
   • For quarter: EXTRACT(QUARTER FROM date_column)
   • For week-of-year: EXTRACT(ISOWEEK FROM date_column)