Integrate a Data Source into a GoodData Workspace

You can directly connect any of the following data sources to your GoodData workspace:

Amazon Redshift
Amazon S3
Google BigQuery
Microsoft Azure Blob Storage
Microsoft Azure SQL Database
Microsoft Azure Synapse Analytics
Microsoft SQL Server
Snowflake
PostgreSQL

Each data source has different integration requirements. Before you connect the data source to your workspace, ensure that GoodData can communicate with your data source. This article will explain how to connect the data source to your GoodData workspace.

Contents:

Prerequisites

An active GoodData account that you are logged into with at least one active workspace.
Access to a supported data source with data.

To better understand how GoodData processes rows and columns, we recommend that you use our sample data for your first integration. For more information, see Import Sample Data to Your Data Source.

You will perform the following tasks:

Create a Data Source
You will connect your GoodData workspace with your data source.
Create a logical data model (LDM)
Use the structure of your source data to create 'a map' to arrange data in the workspace.
Load the data from the source to your GoodData workspace
Create a schedule to load your data

You will need an empty GoodData workspace. If you do not know how to create one, see Create a Workspace.

Create a Data Source

A Data Source is a place in your GoodData workspace that stores the information about the connection with your data source.

Select your source and learn what details you need to establish connection between your GoodData workspace and your data source.

Azure Blob Storage Data Source

Click here to expand...

Log in to the Azure Blob Storage account that you plan to use with GoodData. Ensure that the user that you configured in the data source has all necessary privileges and that your Azure Blob Storage account can be accessed by GoodData. For more information about the required privileges, see GoodData-Azure Blob Storage Integration Details.

Ensure that you have the following information ready:

Azure Blob Storage connection string
Path to the source data
GoodData workspace's ID (Find the Workspace ID)

Azure SQL Database Data Source

Click here to expand...

Log in to your Azure SQL Database with the account that you plan to use with GoodData. Ensure that the user that you configured in the data source has all necessary privileges and that your Azure SQL Database can be accessed by GoodData. For more information about the required privileges, see GoodData-Azure SQL Database Integration Details.

Ensure that you have the following information ready:

Azure SQL Database username and password
Azure SQL Database database and schema
GoodData workspace's ID (Find the Workspace ID)

Azure Synapse Analytics Data Source

Click here to expand...

Log in to your Azure Synapse Analytics with the account that you plan to use with GoodData. Ensure that the user that you configured in the data source has all necessary privileges and that your Azure Synapse Analytics database can be accessed by GoodData. For more information about the required privileges, see GoodData-Azure Synapse Analytics Integration Details.

Ensure that you have the following information ready:

Azure Synapse Analytics username and password
Azure Synapse Analytics database and schema
GoodData workspace's ID (Find the Workspace ID)

BigQuery Data Source

Click here to expand...

The easiest way to integrate your BigQuery dataset and your GoodData workspace is by uploading Google service account key file during establishing the connection. You can also fill in all the details manually.

The Google's documentation about creating service account key file will guide you through the process and provide essential information about service account key files.

In short, when you create you will have to:

define the account name
add user roles (bigquery.dataViewer and bigquery.jobUser)
select JSON as the key type

Ensure that you log in to your BigQuery workspace with the account that you plan to use with GoodData. Ensure that the user, that you configured in the data source, has all necessary privileges and that your BigQuery workspace can be accessed by GoodData. For more information about the required privileges, see GoodData-BigQuery Integration Details.

BigQuery and GoodData integration requires the following role levels: bigquery.dataViewer and bigquery.jobUser.

Before you proceed with establishing the connection between Big Query and GoodData, ensure that you have the following ready:

Google service account key file in JSON format. GoodData will extract the following information:
- client email
- private key
- Google project ID
BigQuery dataset name

Microsoft SQL Server Data Source

Click here to expand...

Log in to your Microsoft SQL Server with the account that you plan to use with GoodData. Ensure that the user that you configured in the data source has all necessary privileges and that your Microsoft SQL Server can be accessed by GoodData. For more information about the required privileges, see GoodData-Microsoft SQL Server Integration Details.

Ensure that you have the following information ready:

Microsoft SQL Server username and password
Microsoft SQL Server database and schema
GoodData workspace's ID (Find the Workspace ID)

PostgreSQL Data Source

Click here to expand...

Log in to your PostgreSQL database with the account that you plan to use with GoodData. Ensure that the user that you configured in the data source has all necessary privileges and that your PostgreSQL database can be accessed by GoodData. For more information about the required privileges, see GoodData-PostgreSQL Integration Details.

Ensure that you have the following information ready:

PostgreSQL username and password
PostgreSQL database and schema
GoodData workspace's ID (Find the Workspace ID)

Redshift Data Source

Click here to expand...

Log in to your Redshift cluster with the account that you plan to use with GoodData. Ensure that the user that you configured in the data source has all necessary privileges and that your Redshift cluster can be accessed by GoodData. For more information about the required privileges, see GoodData-Redshift Integration Details.

Ensure that you have the following information ready:

Redshift username and password
Redshift database and schema
GoodData workspace's ID (Find the Workspace ID)

S3 Data Source

Click here to expand...

Log in to your S3 bucket with the account that you plan to use with GoodData. Ensure that the user that you configured in the data source has all necessary privileges and that your S3 bucket can be accessed by GoodData. For more information about the required privileges, see GoodData-S3 Integration Details.

Ensure that you have the following information ready:

S3 bucket
Access key and secret key
GoodData workspace's ID (Find the Workspace ID)

Snowflake Data Source

Click here to expand...

Log in to your Snowflake instance with the account that you plan to use with GoodData. Ensure that the user that you configured in the data source has all necessary privileges and that your Snowflake instance can be accessed by GoodData. For more information about the required privileges, see GoodData-Snowflake Integration Details.

Ensure that you have the following information ready:

Snowflake username and password
Snowflake database, warehouse, and schema
GoodData workspace's ID (Find the Workspace ID)

To connect your data source and your GoodData workspace, follow these steps:

The screenshots in the following steps use the Snowflake data source, but the steps are the same for each data source.

On the top navigation bar, select Data.
Select Sources.
Select the data source to connect to your workspace.
Provide the required information.
BigQuery only
1. When prompted, upload your service account key file.
  
  The following fields will be populated automatically:
  - Service account email
  - Private key
  - Google project ID
2. Fill in the field for the data source name.
Select Test connection. If the connection succeeds, the green confirmation message appears.

Use the Output Stage if you cannot or do not want to download the data directly from the production tables in your data warehouse. For more information, see Direct Data Distribution from Data Warehouses and Object Storage Services.
Select Save.
The screen with your connection details appears.

Connect the Data Source to LDM Modeler

Once you verified that the connection between the data source and your workspace works, follow these steps to connect to LDM Modeler.

On the Data Source Summary page, select Connect.

LDM Modeler opens.
Drag and drop an object from Data Sources onto the Logical Data Modeler canvas.

A preview of the data opens with the data processed according the following guidelines:
- GoodData will attempt to match each column to the correct data model type based on the contents of the field. Numbers are automatically detected as measures; dates are automatically converted into a separated date dataset; and any columns with letters are automatically detected as attributes. Notice what the columns are set to in the following example:
- The order_line_id, order_id, and order_status columns are correctly detected as attributes.
- The date column is correctly detected as dates in the yyyy-MM-dd format and will be converted to a separate Date dataset.
- The price and quantity columns are correctly detected as measures(or facts).
- Columns that contain product identifiers or other numerical values that cannot be numerical constants in equations cannot be used as a measure. In the following example, Product ID is autodetected as a Measure but it should be changed to Attribute.
Select Import after you verify the information is correct.

To avoid having to import data again, any numerical value that cannot be used as a numerical constant in an equation must be set as an Attribute before you select import.
LDM Modeler opens and displays the structure of your dataset.
Select Import and repeat steps 3 through 5 for each additional dataset.
(Optional) Create a Relationship between Datasets.
Publish your Logical Data Model.