---
title: "Connecting to BigQuery"
description: "In xP&A, you can connect to the following spreadsheet file formats: Google Sheets, Excel Online, Excel Upload, CSV Upload\nThe following three data formats for spreadsheet files are supported: Tables, Transactions, Time-series\nEach connected file needs to be formatted in a certain way so that the data can be imported into xP&A."
source_url: https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/integrating-data/connecting-to-a-data-source/connecting-to-a-bi-data-warehouse-system/connecting-to-bigquery
language: en
last_updated: 2023-08-16
---
# Connecting to BigQuery

## 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.

{% info-box %}
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](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/integrating-data/connecting-to-a-data-source/connecting-to-a-bi-data-warehouse-system/defining-database-queries.md).
{% /info-box %}

## 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.

{% idea-box %}
To reduce the size of your data, aggregate it to the granularity you intend to use in xP&A (day, week, month) using SQL.
{% /idea-box %}

## 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](https://cloud.google.com/iam/docs/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](https://cloud.google.com/iam/docs/service-accounts-create).

To create the service account in Google:

{% stepper %}
{% stepper-step %}
Open your **Google Cloud Dashboard**, and go to **Service Accounts** page.

Google Service Accounts
{% /stepper-step %}
{% stepper-step %}
Click **\+ Create service account.**
{% /stepper-step %}
{% stepper-step %}
Enter a **name** and an **ID** for the service account and click **Create and continue**.

Enter service account name and ID
{% /stepper-step %}
{% stepper-step %}
Grant xP&A access to BigQuery by assigning the Role **BigQuery Admin** and click **Continue**.

Assign 'BigQuery Admin' role
{% /stepper-step %}
{% stepper-step %}
Go back to the **Service Accounts** page and click on the service account you just created.
{% /stepper-step %}
{% stepper-step %}
Open the **Create key** menu and choose **Create new key.**

Create new key
{% /stepper-step %}
{% stepper-step %}
In the next dialog, slect **JSON** as **key type** and click Create to **create** and download the key.

Key type 'JSON'
{% /stepper-step %}
{% /stepper %}

{% stepper %}
{% stepper-step %}
Choose one of the following options:

- Open the **Data** workspace from the overview on the start page and click **\+ New.**

New data source connection from the Data workspace

- 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**:

New data source connection from within a model
{% /stepper-step %}
{% stepper-step %}
In the **Data sources** dialog, open the **BI/Database** tab and choose **BigQuery.**

'BI/Database' tab
{% /stepper-step %}
{% stepper-step %}
The **New Data Source** dialog is displayed as follows:

'New Data Source' dialog for BigQuery
{% /stepper-step %}
{% stepper-step %}
Configure the steps as described in the following section.
{% /stepper-step %}
{% stepper-step %}
Click **Create data source**.
{% /stepper-step %}
{% /stepper %}

### Set-up Steps

#### 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](#google))

Uploading the JSON file

#### 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](https://support.lucanet.cloud/en/documentation/xp-a---extended-planning-and-analysis/integrating-data/connecting-to-a-data-source/connecting-to-a-bi-data-warehouse-system/defining-database-queries.md).
- 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.)

{% idea-box %}
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**.
{% /idea-box %}