Create SQL Datasets

Unlike regular datasets, SQL datasets are not directly tied to a single table or view. Instead, SQL dataset relies on a specified SQL query, similar to a view (or virtual table) in an SQL database. GoodData executes this SQL query each time the dataset is utilized in a visualization, dashboard or a metric. Unlike a view, the SQL dataset is not permanently stored in the database; it is executed as needed, and it lets you create virtual views with just a read-level access to the database.

Unlike standard datasets that map to a single table or view in your data source, SQL datasets execute the specified SQL query each time they are used in a report.

Please note that GoodData does not validate the dataset-defining query. You are responsible for providing an accurate query that effectively describes the dataset’s semantics. Data sampling is not supported for SQL datasets, and the complete set of results is always returned.

Create SQL Dataset

The defining query for the dataset should be a SELECT query, potentially beginning with WITH (CTE expression). Ensure the SQL complies with the dialect used in the data source, as GoodData does not rewrite query for the specific dialect supported by the data source.

We highly recommend defining aliases using the AS keyword in the SQL query. For example, the query SELECT SUM(numbers) lacks an alias for the SUM function result, causing the underlying database driver to supply a default alias, which is typically non-descriptive (e.g., ?column?) and subject to change, potentially causing errors in dataset mapping.

Steps:

  1. Edit your logical data model and drag & drop the SQL dataset into the model’s canvas.

    Create SQL Dataset 1

  2. Name the dataset and define a SQL query.

    Note that the ID of the dataset will be generated based on its name.

    Create SQL Dataset 2

  3. You may Run your query to verify it works as intended.

    Create SQL Dataset 3

  4. Click Create dataset.

    The SQL dataset is created.

    Create SQL Dataset 4

Convert Dataset to SQL Dataset

You can also convert a regular dataset into a SQL dataset. This might be useful if you want to add some sort of computed field or combine columns together using CONCAT.

Steps:

  1. Select a dataset, click … and select Map dataset to.

    Convert To SQL Dataset 1

  2. Select SQL query and click Save.

    Convert To SQL Dataset 2

  3. The usual SQL dataset creation dialog opens, with the appropriate SQL query already pre-generated.

    Convert To SQL Dataset 3

  4. You may edit the query, for example by combining two columns into one.

    Convert To SQL Dataset 4

  5. Click Save.

    The dataset is converted to a SQL dataset.

Note that you can use map an SQL dataset back onto a real table using the same procedure.

Schema Referencing

Before executing the query, GoodData sets the database connection context to the schema outlined in the data source configuration. Consequently, data source schema tables and views do not require full qualification, meaning schema names can be omitted when referencing database objects.

Database objects from other schemas must include the schema name. Note that data source managers (Drill and Dremio) utilize multi-element paths to database objects, requiring the use of fully qualified paths when creating SQL-based datasets.

Create the Model Manually
Advanced Use Cases