Semantic Layer
  • 11 Oct 2021
  • 6 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

Semantic Layer

  • Dark
    Light
  • PDF

Overview

Datasets that you add to Preset from your database can be customized to more closely align with your unique audience, such as defining availability of data in fields, specifying date formats, and changing labels. Within each dataset you can modify its metrics, columns, and also create calculated columns.

In this article we will discuss how to make these change and customize your dataset so that it can better serve your audience.


Access Dataset Details

Changing dataset settings is done via the Edit Dataset panel. This is accessible from two locations on Preset: the Datasets page and the Explore page.

Via the Datasets Page

In the Toolbar, hover your cursor over Data and then select Datasets. The Datasets page will appear.

Semantic_Layer_Select_Datasets

This page features a table that lists all of your datasets. To edit a dataset, simply hover your curcor over a dataset row and, under the Actions column header on the right, select the pencil Edit icon. The Edit Dataset panel will appear.

Edit_Dataset2

Via the Explore Page

The Edit Dataset panel can also be launched via the Explore page when exploring a chart. In the Dataset panel, on the far left, select the ellipsis icon and then select Edit dataset.

That's it!

Edit_Dataset3


Metrics

Metrics are used to perform operations with a set of data (i.e., applies to more than 1 row). They are predominantly used for:

  • Calculations based on aggregation
  • Calculations to pivot rows to columns

Metrics are replaced by a SQL expression in a SQL statement, but defined metrics are not available in SQL Lab.

To begin, in the Edit Dataset panel, select the Metrics tab.

Select_Metrics_Tab

A listing of all metrics within the dataset are displayed as a table. Fields directly editable on the table include:

  • Metric: The name of the metric.
  • Label: Text field that displays as the metric's label — this is what the user sees as the metric's name.
  • SQL Expression: The SQL expression associated with the metric — this appears as a tooltip when the user hovers over the question mark "?" icon.

To edit other fields associated with the metric, select the expansion arrow on the left side of the metric row.

Metrics_Columns1

Editable fields here include:

  • Label: Text field that displays as the metric's label — this is what the user sees as the metric's name (same editable field in collapsed metric row).
  • Description: A short description of the metric — this appears as a tooltip when the user hovers over the letter "i" icon.
  • D3 Format: Format values based on the D3 format. Example:
    • Given a value of 1234.567
      • .2% = 123456.7%
      • .2k = 1K
      • ,.2r = 1,200
  • Certified By: Name of certifying organization/person.
  • Certfication Details: Details about the certification.
  • Warning: A warning message that, if defined, appears in the Metric selection field.
Changing Settings

Changing a metric's settings will affect all charts using this dataset, including charts owned by other people.

Adding a Metric

To add a new metric, select + Add Item.

Add_Item_Metric

...a new row will appear, named <new metric>.

To define details, expand the new row. Complete all fields based on the field descriptions above, starting with selecting <new metric> and changing the name, as needed.

When done, select Save.

To see examples of defined metrics, please read Using Metrics and Calculated Columns.


Columns

Now, select the next tab: Columns.

Interface_Columns_Tab

The Columns tab displays all column data within the dataset, displayed as a table.

The table headings are as follows:

  • Column (editable): Displays the name of the column.
  • Data type: Displays the type of data within the column (e.g., integer, float, variable character, timestamp, etc.).
  • Is temporal (editable): Select the checkbox to indicate that the column is a timestamp and should be available as an option in the Time panel.
  • Is filterable (editable): Select the checkbox tio indicate that the column should be included as an option in the Filter field.
  • Is dimension (editable): Select the checkbox to indicate that the column should be included as an option in the Group By field.
  • Delete: Select the trash can icon to delete the column.

Select the expansion arrow on the left side of the column row to define additional column data.

Columns_Fields

Editable fields here include:

  • Label: Change the label of the column — this is what the user will see as the column's name.
  • Description: Enter a brief description of the column — this appears as a tooltip when the user hovers over the letter "i" icon.
  • Datetime Format: Enter the format of the date and time stamp in Python format. Examples:
    • %m-%d-%Y= 02-27-2020
    • %a %d, %y = Tue 2, 2020
    • %x %X = 02/27/2020 17:41:00
    • %B-%Y = February-2020
    • Epoch_m = If date is an epoch integer

The Sync Columns from Source button is used to reconnect to the data source and add New and Remove columns.

Sync_Cols_from_Source


Calculated Columns

First, let's discuss some concepts behind Calculated Columns. Calculated columns are used for data munging, which is simply the process of refining raw data into a format that is more meaningful to end-users, with the purpose being to provide meaningful data based on the end-user audience.

Examples of data munging methods include:

  • Data Transformation: Changing data to different formats.
  • Data Enrichment: Adding meaningful information to the data.
  • Data Validation: Correcting and validating data.

In Preset, Calculated Columns are replaced by a SQL expression in a SQL statement, but they are not available in SQL Lab.

To start, select the Calculated Columns tab.

Calc_Columns_Interface

The Calculated Columns tab displays all calculated column data within the dataset, displayed as a table.

The table headings are as follows:

  • Column (editable): Displays the name of the column.
  • Data type: Displays the type of data within the column (e.g., integer, float, variable character, timestamp, etc.).
  • Is temporal (editable): Select the checkbox to indicate that the calculated column is a timestamp and should be available as an option in the Time panel.
  • Is filterable (editable): Select the checkbox tio indicate that the calculated column should be included as an option in the Filter field.
  • Is dimension (editable): Select the checkbox to indicate that the calculated column should be included as an option in the Group By field.
  • Delete: Select the trash can icon to delete the calculated column.

Select the expansion arrow on the left side of the calculated column row to define additional data.

Editable fields here include:

Calc_Columns_Table

  • SQL Expression: The SQL expression associated with the calculated column — this appears as a tooltip when the user hovers over the question mark "?" icon.
  • Label: Change the label of the column — this is what the user will see as the calculated column's name.
  • Description: Enter a brief description of the calculated column — this appears as a tooltip when the user hovers over the letter "i" icon.
  • Data Type: Displays the type of data within the column (e.g., integer, float, variable character, timestamp, etc.).
  • Datetime Format: Enter the format of the date and time stamp in Python format. Examples:
    • %m-%d-%Y= 02-27-2020
    • %a %d, %y = Tue 2, 2020
    • %x %X = 02/27/2020 17:41:00
    • %B-%Y = February-2020
    • Epoch_m = If date is an epoch integer

Adding a Calculated Column

To add a Calculated Column, select + Add Item.

Add_Calc_Col

...a new pre-expanded row will appear. By default, the Is filterable and Is dimension checkboxes are selected.

Add_Calc_Col1

To start, under the Column header, select the <new column> text placeholder and enter a name for your new calculated column.

Next, in the SQL Expression field, enter a SQL expression for the new calculated field. This will also appear as tooltip text when a user hovers their cursor over the calculated column's "?" tooltip icon.

In the Label field, enter a label name for the calculated column — this is what the end-users will see.

In the Description field, enter a brief description of the calculated column. This will appear as tooltip text when a user hovers their cursor over the calculated column's "i" tooltip icon.

In the Data Type field, select an appropriate data type for the calculated column.

Lastly, in the Datetime Format field, enter a date & time format using the Python format.

When done, select Save.

To see examples of Calculated Columns, please read Using Metrics and Calculated Columns.


Was this article helpful?

What's Next