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.
Using SQL Datasets with Workspace Data Filters
When defining a WDF using a
wdf__<columnBaseName> column, the SQL query that defines the dataset must return the specified column.
Edit your logical data model and drag & drop the SQL dataset into the model’s canvas.
Name the dataset and define a SQL query.
Note that the ID of the dataset will be generated based on its name.
You may Run your query to verify it works as intended.
Click Create dataset.
The SQL dataset is created.
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
Select a dataset, click More … and select Map dataset to.
Select SQL query and click Save.
The usual SQL dataset creation dialog opens, with the appropriate SQL query already pre-generated.
You may edit the query, for example by combining two columns into one.
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.
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.