Customizing the Semantic Layer
  • 27 Jan 2023
  • 7 Minutes to read
  • Contributors
  • Dark
    Light

Customizing the Semantic Layer

  • Dark
    Light

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.

Changing Settings

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

Via the Datasets Page

In the Toolbar, navigate to Datasets. The Datasets page will appear.

Dataset_toolbar_option.png

This page features a table that lists all of your datasets. To edit a dataset, simply hover your cursor 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_dataset_button.png

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_dataset_from_explore.png


Source

In the Source tab, you can manage the source powering your dataset. It's possible to specify the type (Physical or Virtual), and also map it to a database and schema.

For Physical Datasets, users can specify the table powering it. For Virtual Datasets, it's possible to set a title and a SQL query to power it.

Dataset_source.png

To prevent un-intentional changes, users need to click on the padlock icon to modify these settings.


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, select the Metrics tab.

Dataset_metrics.png

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.

Dataset_Metrics_Expanded.png

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.

Adding a Metric

To add a new metric, select + Add Item.

Dataset_Add_metric.png

...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.

Dataset_Columns.png

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.

Dataset_columns_expanded.png

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 fetch updated metadata from the dataset source (either a table or a SQL query), and reflect changes to the columns (add new columns and remove deleted ones).

Dataset Sync Columns From Source.png

While performing modifications to the dataset source, the Sync columns from Source button is greyed out. Finish modifying the source configuration to be able to sync columns.


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.

Dataset Calculated Columns.png

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:

Dataset calculated columns expanded.png

  • 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 external-link_10x10. 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.

Create Calculacted Column.png

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

New Calculated Column Template.png

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 external-link_10x10.

When done, select Save.

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


Was this article helpful?

What's Next