Connecting a BigQuery warehouse

This guide walks you through connecting a Google BigQuery dataset to Saiku Cloud. Fifteen minutes if you already have a service account; thirty if you also need to create one in IAM.

Tier impact: every Saiku Cloud tier supports BigQuery BYOC.

Driver: Saiku Cloud ships Google’s official BigQuery JDBC driver (Apache 2.0, ~56 MB shaded). Auth is via a service-account JSON key — there’s no password-based auth path.

What you’ll need

• A Google Cloud project with BigQuery enabled.
• A dataset within that project where your analytics data lives.
• IAM Owner or equivalent permission to create a service account.

Step 1 — Network access (good news)

BigQuery’s API is a Google-managed service reachable from any internet-connected host. No firewall allowlist required. Saiku Cloud’s egress IP (87.99.153.244) is one of many that’ll hit BigQuery’s API; Google’s edge handles routing transparently.

Skip to Step 2.

Step 2 — Create a read-only service account

Saiku Cloud needs BigQuery Data Viewer (for SELECT) + BigQuery Job User (for query execution).

In the Google Cloud console:

1. IAM & Admin → Service Accounts → Create service account.

2. Name: saiku-read. Description: Saiku Cloud read-only access.

3. Grant roles:

   • BigQuery Data Viewer — scope it to specific datasets if you want stricter access (Data Viewer at project scope reads all datasets; at dataset scope reads only that one).
   • BigQuery Job User — must be at project scope (BigQuery doesn’t support dataset-scoped Job User).

4. Click into the created service account → Keys → Add Key → Create new key → JSON. The browser downloads a <project>-<id>.json file.

5. This is the credential — save it to your password manager. It contains the private key.

Step 3 — Build the JDBC URL

The shape (semicolon-separated, NOT querystring):

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<PROJECT>;OAuthType=0;OAuthServiceAcctEmail=<SA_EMAIL>;OAuthPvtKey=<INLINE_PEM>;DefaultDataset=<DATASET>

Concrete example with inline PEM:

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=my-analytics-prod;OAuthType=0;OAuthServiceAcctEmail=saiku-read@my-analytics-prod.iam.gserviceaccount.com;OAuthPvtKey=-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkqhkiG9w0BAQEF...-----END PRIVATE KEY-----;DefaultDataset=analytics

Parameter map:

• https://www.googleapis.com/bigquery/v2:443 — the API endpoint. Always this for the public BigQuery service. Private Service Connect endpoints have a different shape (see Enterprise: private network).
• ProjectId= — your Google Cloud project id (the alphanumeric one from the console URL, not the project name).
• OAuthType=0 — service-account key auth. Other values: 1 (user OAuth — not supported in Saiku), 3 (Application Default Credentials — only works if the engine has access to ADC, which isn’t generally true in the cloud-hosted Saiku Cloud deployment).
• OAuthServiceAcctEmail= — the service account’s email, copied from Step 2 step 4. Format: <saname>@<project>.iam.gserviceaccount.com.
• OAuthPvtKeyPath= OR OAuthPvtKey= — one or the other.
  • OAuthPvtKeyPath=/path/to/key.json — driver reads the file at runtime. Doesn’t work on cloud-hosted Saiku Cloud because you can’t drop files on the engine container’s filesystem.
  • OAuthPvtKey=<inline PEM> — the literal -----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY----- contents from the JSON key’s private_key field, with literal \n between lines. Use this for cloud-hosted.
• DefaultDataset= — the BigQuery dataset (the second segment of project.dataset.table). Lets schemas reference unqualified table names like <Table name='sales'/> instead of fully-qualifying every reference. Required for BYOC schemas to load through Saiku Cloud’s Mondrian (the driver’s metadata API only resolves unqualified tables when this is set).

Cube-authoring side: schema/catalog properties for metadata introspection

When you upload a Mondrian schema XML through Saiku Cloud that references unqualified BigQuery tables (e.g. <Table name='salary'/> not <Table name='salary' schema='analytics'/>), Saiku Cloud also needs to know the dataset and project for JDBC metadata introspection. These are passed as connect-string properties at the Mondrian wrapper level, in addition to the URL above:

• JdbcSchema=<dataset> — same value as DefaultDataset= in the URL.
• JdbcCatalog=<project> — same value as ProjectId= in the URL.

The Saiku Cloud connection wizard auto-derives these from the URL’s ProjectId= + DefaultDataset= parameters when you save the connection, so most customers never see them. Mentioned here because the engine’s .sds shape on disk includes them, and they show up in audit logs:

jdbc:mondrian:Jdbc='jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=my-analytics-prod;OAuthType=0;OAuthServiceAcctEmail=...;OAuthPvtKey=...;DefaultDataset=analytics';Catalog=file:/var/lib/saiku/data/<your-schema>.xml;JdbcDrivers=com.google.cloud.bigquery.jdbc.BigQueryDriver;JdbcSchema=analytics;JdbcCatalog=my-analytics-prod

Caution

Two important shape details if you ever need to author this .sds by hand (e.g. for testing on the OSS engine):

1. Single-quote the inner JDBC URL. Mondrian’s connect-string parser uses ; as a top-level property separator — same separator BigQuery’s driver uses INSIDE its URL. The single quotes tell Mondrian to preserve the inner ;s instead of splitting them out. Without them you get IllegalStateException: Invalid Auth type specified: -1 because Mondrian strips the BigQuery-side params before passing the URL to the driver.
2. JdbcSchema= and JdbcCatalog= go OUTSIDE the quoted URL, at the Mondrian property level (alongside Catalog=, JdbcDrivers=). Inside the URL they’d be parsed by the BigQuery driver and ignored — they only matter to Mondrian’s metadata introspection layer.

These two requirements are documented in detail in the BigQuery-Mondrian notes with the failure-mode catalogue.

Extracting the private key from the JSON

The JSON downloaded in Step 2 looks like:

{
  "type": "service_account",
  "project_id": "...",
  "private_key_id": "...",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvQ...\n-----END PRIVATE KEY-----\n",
  "client_email": "...",
  ...
}

Pull out the private_key value, strip the wrapping quotes, and that’s what goes into OAuthPvtKey= (the \n escapes are literal — keep them as backslash-n strings, NOT actual newlines).

Step 4 — Connect via the Saiku Cloud wizard

1. Sign in at https://cloud.saiku.bi/.

2. Navigate to Connections in the left sidebar.

3. Under 1. Pick warehouse type, click the BigQuery tile.

4. Fill in:

   • JDBC URL — the URL from Step 3.
   • Username — anything (e.g. bigquery). The driver ignores this field; auth is in the URL.
   • Password — anything non-empty (e.g. unused-key-in-url). Also ignored.

5. Click Test connection.

Expect a green outcome banner: ✓ Connection successful plus the detected BigQuery version (something like BigQuery 2.0). BigQuery’s first connection takes 5–10 seconds (token exchange + dataset listing).

Step 5 — Save the connection

Same shape as every other dialect — re-enter the placeholder password, give it a label, save.

Caution

Service-account key storage. Saiku Cloud stores the inline PEM in the connection’s encrypted record alongside the JDBC URL. Vault Transit encrypts the whole credential at rest. The key still lives in the URL string at decrypt time, but never in env vars, never in logs, never in connection-list responses. A cleaner future path — extracting the key into Vault as its own resource and templating the JDBC URL with a Vault-resolved reference — is on the roadmap; customers landing today via the inline path can transparently migrate when that ships.

Troubleshooting

✗ Connection failed (AUTH_FAILED)

• OAuthPvtKey formatting — most common cause. The PEM must include literal \n between lines (NOT actual newlines). Double-check: the value should start -----BEGIN PRIVATE KEY-----\nMIIE... (backslash-n, not a newline).
• Service account doesn’t have BigQuery Job User at project scope — even with Data Viewer on the dataset, BigQuery rejects queries from accounts without project-level Job User.
• Service account disabled or deleted — check IAM console.

✗ Connection failed (DATABASE_NOT_FOUND)

ProjectId= is wrong, or the service account doesn’t have any visible datasets in that project. Verify the project id matches what’s shown in the Google Cloud console URL.

✗ Connection failed (HOST_UNREACHABLE) or (TIMEOUT)

BigQuery’s API is rarely unreachable; check the GCP status page. If GCP is healthy, the https://www.googleapis.com/bigquery/v2:443 URL segment is probably mistyped — it must be exact.

✗ Connection failed with "OAuthType=1 requires interactive OAuth"

You set OAuthType=1 (user OAuth) instead of OAuthType=0 (service-account). Switch to OAuthType=0 and provide a service-account key.

Cube renders but with no data

The service account has BigQuery Job User (so queries succeed) but no BigQuery Data Viewer on the dataset (so queries return empty results). Add Data Viewer at the dataset scope.

Anything else

Take a screenshot of the wizard with the red outcome banner visible (Kind: ... line in particular) and send it to support@saiku.bi.

Enterprise: private network {#enterprise-private-network}

For Enterprise customers, BigQuery supports Private Service Connect — your tenant’s BigQuery API traffic stays within a private network. The JDBC URL endpoint changes from https://www.googleapis.com/bigquery/v2:443 to your PSC endpoint. Contact your account team to provision the peering.

Try with FoodMart

Want to test Saiku Cloud with this dialect before connecting your own data? Download the FoodMart sample dataset packaged for BigQuery:

FoodMart sample dataset for BigQuery