Help & Support ↗ Lucanet Learning Space ↗
Getting Started
Global Functions
Administration
Extended Planning & Analysis (xP&A)
xP&A Video Tutorials
User Roles in Lucanet xP&A
Lucanet xP&A User Interface
All Steps at a Glance
Basic Concepts and Elements
Creating a Model
Integrating Data
Connecting to a Data Source
Connecting to a Spreadsheet
Connecting to an Accounting System
Connecting to a BI/Database System
Defining Database Queries
Connecting to BigQuery
Connecting to Postgres
Connecting to Databricks
Connecting to Microsoft SQL Server
Connecting to MySQL
Connecting to Redshift
Connecting to Snowflake
Connecting to Looker
Connecting to a CRM System
Connecting to an HRIS/Payroll System
Connecting to a Lucanet CFP Data Source
Connecting to a Source System Using a Webhook
Sharing Data Connections
Working With Integrated Data
Guideline for Exchanging Data Between xP&A and CFP
Modeling Your Data
Viewing and Visualizing Data
Working with Models
Using Comments and Descriptions
Comparison: xP&A vs. Excel Performance
Consolidation & Financial Planning
ESG Reporting
Disclosure Management
XBRL Tagger
Lease Accounting
Banking & Cash Management
Data Collection
Tax Compliance & Reporting
Glossary

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.

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.

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:

1

Open your Google Cloud Dashboard, and go to Service Accounts page.

Shows the Dasboard menu of Google Cloud. Highlighte dis the section "Service Accounts"
Google Service Accounts
2

Click + Create service account.

3

Enter a name and an ID for the service account and click Create and continue.

Shows the dialog 'Service account details'. Highlighted are the fields to enter the name and the ID of the account
Enter service account name and ID
4

Grant xP&A access to BigQuery by assigning the Role BigQuery Admin and click Continue.

Shows the dialog 'Grant this service accpunt access to project'. Highlighted is the dropdown menu 'Role' and the 'Continue' button
Assign 'BigQuery Admin' role
5

Go back to the Service Accounts page and click on the service account you just created.

6

Open the Create key menu and choose Create new key.

Shows the details of the service account. Highlighted is the dropdown menu 'Add key' and the 'Create new key' option
Create new key
7

In the next dialog, slect JSON as key type and click Create to create and download the key.

Key type 'JSON'
Key type 'JSON'
1

Choose one of the following options:

  • Open the Data workspace from the overview on the start page and click + New.
Shows the start page of Lucanet xP&A. Highlighted are the buttons that need to be used to create a new data source connection from the start page.
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:
Shows the view of a model. Highlighted are the buttons that need to be used to create a new data source connection from the model view.
New data source connection from within a model
2

In the Data sources dialog, open the BI/Database tab and choose BigQuery.

Shows the Data Sources dialog. Highlighted in red is the 'BI/Database' tab.
'BI/Database' tab
3

The New Data Source dialog is displayed as follows:

Shows the 'New Data Source' dialog for a BigQuery connection
'New Data Source' dialog for BigQuery
4

Configure the steps as described in the following section.

5

Click Create data source.

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)

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

Last updated on Aug 16, 2023

Table of Contents
Connecting to BigQueryOverviewLimits for Data Imported from BigQueryPrerequisites for the Setup
Related Articles

Company

Legal Notice

© Copyright 2026 - Lucanet AG