Working with a dataset in DataLens On-premises

In this section, you will learn how to work with a dataset:

• Creating a dataset
• Adding data
• Configuring links
• Creating fields
• Configuring fields
• Performing operations with fields
• Adding parameters
• Adding filters
• Disabling data export
• Deleting a dataset
• Undoing and redoing changes to a dataset

Creating a datasetCreating a dataset

You can create a new dataset within a workbook:

1. Open the workbook to create a dataset in.
2. In the top-right corner, click Create and select Dataset.

Adding dataAdding data

You can add data to a dataset from tables available in the source or run an SQL query against the source.

1. In the dataset, go to the Sources tab.

2. In the connection panel, click Add.

   

3. Select a data source connection.

4. Add data from the data source:

   Table

   SQL query

   Drag the tables you need from the table panel to the workspace. The system will automatically link these tables together based on the first matching field name and data type.

   Warning

   • The maximum number of tables per dataset is 32.
   • The maximum number of fields per dataset is 1,200.

   Note

   • To use subqueries as a source, enable Raw SQL level → Subqueries only when creating or editing a connection.
   • Describing a dataset via a SQL query does not support parameters. However, you can use parameterized queries in QL charts.

   1. In the table panel, click Add.

      

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

   3. Click Create.

      Example of an SQL query

      An SQL query joins data from the samples.MS_SalesFacts, samples.MS_Products, and samples.MS_Shops tables and groups them by the Store and Product type fields. A sales total is calculated for each group; however, only the sales records from 2019 are used:

      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" -- Grouping by Store and Product type
      ORDER BY "Store", "Product Type" -- Sorting by Store and Product type
      

Replacing a connectionReplacing a connection

To replace a connection in a dataset:

1. In the dataset, go to the Sources tab.

2. Delete all the tables from the workspace.

3. In the connection panel, click next to the connection and select Replace connection.

   

4. Select another connection.

5. Drag to the workspace the tables that will serve as sources of data for the dataset.

6. Edit or delete the calculated fields which use fields not present in the new table.

7. In the top-right corner, click Save.

Configuring linksConfiguring links

You can join data from source tables.

1. In the dataset, go to the Sources tab.

2. Click the table link icon.

   

3. Select the link type: inner, left, right, or full.

   

4. Select the fields to join the tables on. Hovering over a field displays a tooltip with the field full name. You can search fields by name.
   You can only link fields with the same data type. DataLens gets information about the field's data type directly from the connection. If you need to join tables by fields with different data types, use one of these options:

   • Convert all the fields to the same data type at the source level.

   • Describe the dataset with an SQL query. Use the CAST or CONVERT functions to convert the data type. Here is an example:

     SELECT * FROM lead INNER JOIN user ON lead.assigned_by_id = CONVERT(user.external_id, CHAR)
     

5. If required, link other fields in the tables. To do this, click Add link.

6. Optionally, you can disable the Optimize link option to make the link required. In this case, the JOIN operation will be performed even if you select fields from a single table.

7. Click Apply.

By default, when you add a new table from a data source to the workspace, it is automatically linked to the first table listed there. If required, you can link a new table to a specific table. To do this, hover the new table over a previously added one and release when you see a gray border around the latter. A new link to the specified table is created.

You can replace a previously added table. When dragging a new table, hover it over the icon with circular arrows to the right of the table you are replacing for the icon background to turn blue, then release.

Creating fieldsCreating fields

You can add fields to a dataset from data source tables or create calculated fields using formulas.

1. In the dataset, go to the Fields tab.

2. At the top of the screen, click Add field.

   

3. Create a field:

   Calculated field

   Data field

   1. Enter a name for the field.

   2. Enter a formula to calculate the field value using data source functions.

   3. Optionally, enable Don't display in wizard to hide the field in the wizard when creating a chart.

   4. Click Create. The field will appear in the dataset.

      

   1. Enter a name for the field.

   2. Go to the Field from source tab.

   3. Specify the field parameters:

      • Field ID: ID of the field you are creating that is used to provide parameters for filtering by this field on the dashboard or in the chart URL. It may contain lowercase Latin letters, as well as the _ and - characters.
      • Source: Table in the data source with the field you need.
      • Source field: Field from the selected table in the data source.
      • Field type: Field data type.
      • Aggregation: Aggregation function applied to the field.

   4. Optionally, enable Don't display in wizard to hide the field in the wizard when creating a chart.

   5. Click Create. The field will appear in the dataset.

      

Duplicating fieldsDuplicating fields

You can duplicate any dataset field, including the calculated field you previously created. Proceed as follows:

1. On the right side of the field row, click (the icon appears when you hover over the row) and select Duplicate. In the list on the left, you will see a new field with the same type and settings as the original one.
2. Optionally, rename the duplicated field, change its type or formula.

Configuring fieldsConfiguring fields

You can perform the following operations on dataset fields:

1. Rename a field: To rename a field, click its current name and enter a new one.

2. Change the source: If the table schema in the source has changed, you can select a new field from the source to match the dataset field.

3. Change the field data type: Data type determines how to process data and which functions can apply to the field. To change the data type, click the current type and select a new one from the list of available types.

4. Select an aggregation type: Aggregation type determines the aggregation function to apply to the field. To change the aggregation type, click the current type and select a new one from the list of available aggregation types.

5. Setting up field display. For dimensions, you can choose a color and set a display format for numeric types; for metrics, you can only set a display format for numbers. These settings apply when you use these fields in charts built from this dataset.

   Field display settings

   

6. Configure access permissions: You can restrict access to field values for different users or user groups. To do this, click and set the access configuration in 'field_value': user_email format. For more information, see Row-level security (RLS).

7. Sort fields: You can sort fields by name, data type, aggregation type, and other parameters. To do this, use the sorting buttons at the top of the Fields table.

8. Add a description: You can add a comment to a field in the Description column. This can help other users understand what the field is used for or how it is calculated.

9. Hide fields: You can hide fields you do not need at the moment. Other users will not see these fields in the wizard when creating a chart. To do this, click .

   

Updating fieldsUpdating fields

You need to update fields if you changed the schema of a table (or tables) in the data source or manually deleted fields from a dataset.

To update fields in a dataset:

1. In the dataset, go to the Fields tab.

2. At the top of the screen, click Update fields.

   • If a field is missing in the dataset but present in the data source, you will see that field in the list.
   • If a field is present in both the dataset and the data source, the field will be updated, e.g., its type will change.
   • If a field is present in the dataset but missing in the data source, that field will be marked in red. Delete it from the dataset or replace it with a different field from the data source.

   

3. In the top-right corner, click Save.

Replacing fieldsReplacing fields

If a field has been removed from a data source, it will not be updated when a dataset is updated. Charts featuring this field will show the ERR.DS_API.FORMULA.UNKNOWN_SOURCE_COLUMN error. You can delete this field from the chart or replace its source in the dataset.

To replace the field source in the dataset:

1. In the left-hand panel, click Datasets and select the dataset you need.

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

3. In the dataset, select the field you want to replace.

4. Go to the Field source column.

5. In the field settings, select the field you wish to replace the deleted field with from the Field from source list.

   

6. Click Save.

Performing operations with fieldsPerforming operations with fields

To perform operations with fields, click (the icon appears when you hover over the row) on the right side of the field row and select:

• Duplicate: Create a copy of the field with identical properties.
• Edit: Change the field settings, its formula, or source.
• Copy ID: Copy the field ID to use it for providing parameters for filtering by this field on the dashboard or in the chart URL.
• Delete: Delete the field.

Performing bulk operations with fieldsPerforming bulk operations with fields

You can select multiple dataset fields at once to delete, hide, or display them, change their type or aggregation.

To select the field range, hold the Shift key and set the beginning and end of the range with your mouse. You can select multiple ranges. To select each of the field ranges, hold Ctrl and click the first field in the range, then hold Shift and click the last field in the range.

Adding parametersAdding parameters

In datasets, you can use parameters in calculated fields.

1. In the dataset, go to the Parameters tab.

2. Click Add.

3. In the Add parameter window, specify:

   • Name: Sets the parameter name.
   • Type: Parameter data type.
   • Default value. This is a required value. It is used when no parameter value is set on the dashboard, chart URL, or chart settings.

4. Click Add.

You can also create parameters at the chart level.

Adding filtersAdding filters

At the dataset level, you can add default filters that will automatically apply to any new chart created from the current dataset.

1. In the dataset, go to the Filtering tab.
2. Click Add.
3. Select the field to apply the filter to.
4. Set a filtering rule.
5. In the bottom-right corner, click Apply filter.

Disabling data exportDisabling data export

To disable data export from charts based on a specific dataset, click in the upper right corner of the dataset and uncheck Allow data export.

Charts based on this dataset will no longer display the data export option in their menu, and users will be unable to export data from those charts. However, users will still be able to copy data from the charts and take screenshots.

To re-enable data export, click in the upper right corner of the dataset and check Allow data export.

Deleting a datasetDeleting a dataset

Before deleting a dataset, check which objects are using it. To do this, at the top of the dataset, click → Linked objects. If an error appears when opening the linked object window, try clicking the Retry button.

To delete a dataset:

1. At the top of the dataset, click → Delete.
2. Click Delete.

Useful linksUseful links

• DataLens On-premises dataset settings

Undoing and redoing changes to a datasetUndoing and redoing changes to a dataset

When editing a dataset, you can now undo or redo any change introduced within the current version:

• To undo changes, click in the top-right corner of the screen or press Ctrl (Cmd) + Z.
• To redo changes, click or press Ctrl (Cmd) + Shift + Z.

You undo and redo changes that result in creating a new version.

Unsaved changes in the current version are reset:

• When refreshing the page.
• When saving a dataset.
• When switching to another version.