Dataset

A dataset is a logical object that represents a set of related facts, attributes, and attribute labels.

Datasets are basic organization units of a logical data model (LDM).

You can look at a dataset as a representation of a database table with its primary key and foreign key. A dataset’s primary key (so-called “grain”) defines the cardinality of the dataset. The primary key must be defined via either an attribute or a referenced dataset.

Facts, attributes, and attribute labels related to a particular dataset are automatically tagged with a tag object set to the identifier of the dataset.

Relationships between Datasets

Datasets are associated with each other through relationships. A relationship is a one-directional mapping between two datasets through a single primary key. The primary key functions like a database primary key. It identifies the field in the originating dataset that contains information to uniquely identify the data in other fields in the dataset.

When a relationship is created between two datasets, a foreign key field is inserted into the target dataset. This foreign key is populated by references to the primary key values in the dataset at the other end of the relationship.

The relationship is important because it determines what you slice by what when building your own metrics using MAQL - Analytical Query Language.

Example: Datasets in the LDM

Primary Key Model
  • Fact datasets
    • Order Lines (primary key Order Line ID)
    • Campaign / Channels (primary key Campaign Channel ID)
  • Attribute datasets
    • Customers (primary key Customer ID)
    • Products (primary key Product ID)
    • Campaigns (primary key Campaign ID)
  • Date datasets
    • Date

The direction of the arrow determines which dataset’s data can be analyzed (sliced) by the data from the other dataset. For example, in the LDM above, the relationship between the Customer and Order Lines datasets allows you to slice Quantity by Customer Name.

Date Datasets

A Date dataset is a dataset in the logical data model(LDM) that represents DATE / TIMESTAMP columns in your database. The Date dataset helps you manage time-based data and enables aggregation at the day, week, month, quarter, and year level.

When a relationship exists between a standard dataset and a Date dataset, the related foreign keys are mapped to the DATE / TIMESTAMP columns.

You can share a Date dataset with multiple standard datasets and then slice the facts from those standard datasets by the dates from the Date dataset.

You can create multiple Date datasets and create relationships between these Date datasets and one standard dataset, for example:

  • Date datasets: Created, Last Updated
  • Standard dataset: Tickets containing two foreign keys, Created and Last Updated

A Date dataset in the LDM is identified by the following visual object:

Date Dataset Example

Granularity Levels in a Date Dataset

The Date datasets support the following categories of date/time granularity:

  • Chronological
  • Periodical

When used in the LDM, the Date dataset defines what levels of granularity can be used in your analytics and how the titles of the those levels should be presented.

Category Granularity Identifier Description
Chronological Minute minute Date and time with minute granularity (for example, 2021-04-16 12:34)
Chronological Hour hour Date and time with hour granularity (for example, 2021-04-16 12)
Chronological Date day Date with day granularity (for example, 2021-04-16)
Chronological Week/Year week Week and year (for example, 2021-20)
Chronological Month/Year month Date with month granularity (for example, 2021-12)
Chronological Quarter/Year quarter year
Chronological Year year Date with year granularity (for example, 2021)
Periodical Minute of Hour minuteOfHour Generic minute of the hour (1-60)
Periodical Hour of Day hourOfDay Generic hour of the day (0-23)
Periodical Day of Week dayOfWeek Generic day of the week (1-7)
Periodical Day of Month dayOfMonth Generic day of the month (1-31)
Periodical Day of Year dayOfYear Generic day of the year (1-366)
Periodical Week of Year weekOfYear Generic week (1-53)
Periodical Month of Year monthOfYear Generic month (1-12)
Periodical Quarter of Year quarterOfYear Generic quarter (1-4)

Example:

To compute the revenue for the first day of a month, use the following metric:

select {metric/revenue} where {label/date.dayOfMonth}="01"

The label identifier is date.dayOfMonth where:

  • date is the identifier of the dataset, and
  • dayOfMonth is the identifier of the granularity.