Configure the steps as described in the following section.
Connecting to BigQuery
Last updated on 2025-11-07
Overview
You can establish a direct connection to BigQuery to pull in data into xP&A.
This article contains a description of the prerequisites and the individual steps of the set-up process.
The fields that are pulled in must be defined during the set-up process using a database query. For a detailed instruction on how to structure such a query, see Defining Database Queries.
This article contains the following sections:
Limits for Data Imported from BigQuery
In order to prevent the accidental import of large amounts of data, there is a limit on the number of rows (150,000) and the number of items (100) you can have in a dimension when importing data from BigQuery.
Data sources that exceed these limits can negatively affect the performance of your model.
If you would like to remove these limits, please reach out to your Lucanet contact person.
To reduce the size of your data, aggregate it to the granularity you intend to use in xP&A (day, week, month) using SQL.
Prerequisites for the Setup
Creating a Google Service Account and Key
Before connecting to BigQuery, you need to create a Google service account and an associated key.
A service account is a special kind of account for non-human users. For more information, see Understanding Service Accounts. The key is required during the set-up process of the connection in xP&A.
You might need privileged access to your Google infrastructure in order to create a service account. For more information, see Create service accounts.
To create the service account in Google:
Open your Google Cloud Dashboard, and go to Service Accounts page.
Click + Create service account.
Enter a name and an ID for the service account and click Create and continue.
Grant xP&A access to BigQuery by assigning the Role BigQuery Admin and click Continue.
Go back to the Service Accounts page and click on the service account you just created.
Open the Create key menu and choose Create new key.
In the next dialog, slect JSON as key type and click Create to create and download the key.
Connecting to BigQuery
To connect to Postgres:
Choose one of the following options:
- Open the Data workspace from the overview on the start page and click + New.
- Open the model into which you would like to integrate the data, click the + sign next to Data in the overview, and choose New data source:
In the Data sources dialog, open the BI/Database tab and choose BigQuery.
The New Data Source dialog is displayed as follows:
'New Data Source' dialog for BigQuery
Click Create data source.
Set-up Steps
Step
Description
Choose a connection
Choose an existing connection, or, if you have not configured a connection yet, click New Connection and upload the JSON file you created for the Google service account (as described in Creating a Google Service Account and Key)
Complete the query form
Enter the following:
- Data source name
- Query to define the fields which are to be pulled in. For more information, see Defining Database Queries.
- Name of the Date column, which must be one of BigQuery's date formats
- Names of the columns that contain variables (which must have a numeric data type.)
Any remaining columns will be treated as dimensions, and must have a string data type.
An exception is the cohort dimension, which must be a date, with the column header explicitly labelled Cohort.
