Describing a dataset via a source SQL query

Note

To use subqueries as a source, in the connection settings, enable Raw SQL level → Allow subqueries in datasets when creating or editing a connection.

To add data to a dataset using a SQL query:

  1. In the left-hand panel, click Datasets and select the dataset you need. If you do not have a dataset, create one.

  2. In the top-left corner, select the Sources tab.

  3. In the left part of the screen, under Connection, click Add.

    Note

    The Add button is only available if the source is a database.

  4. Enter a Source name and enter the SQL code in the Subquery field.

  5. Click Create.

Example of SQL query

A SQL query joins data from samples.MS_SalesFacts, samples.MS_Products, and samples.MS_Shops and groups them on the Store and the Product type fields. A sales total is computed for each group. At the same time, only sales records from 2019 are taken into consideration:

SELECT 
    t3.ShopName AS "Store",
    t2.ProductCategory AS "Product type",
    COUNT(t1.OrderID) AS "Number of sales",
    SUM(t1.Price*t1.ProductCount) AS "Sales total"
FROM
    samples.MS_SalesFacts t1
    INNER JOIN samples.MS_Products t2 ON t2.ProductID=t1.ProductID
    INNER JOIN samples.MS_Shops t3 ON t3.ShopID=t1.ShopID
WHERE
    toYear(t1.OrderDatetime)='2019' -- condition for selecting sales for the specified year (2019)
GROUP BY "Store", "Product type" -- group by Store and Product type
ORDER BY "Store", "Product Type" -- sort by Store and Product type
Previous
Updating fields in datasets
Next
Adding parameters to a dataset