Describing a dataset via a source SQL query


To use subqueries as a source, in the connection settings, enable Raw SQL levelAllow 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.


    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.ShopIDWHERE    toYear(t1.OrderDatetime)='2019' -- condition for selecting sales for the specified year (2019)GROUP BY "Store", "Product type" -- group by Store and Product typeORDER BY "Store", "Product Type" -- sort by Store and Product type