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.

CategoryGranularityIdentifierDescription
ChronologicalMinuteminuteDate and time with minute granularity (for example, 2021-04-16 12:34)
ChronologicalHourhourDate and time with hour granularity (for example, 2021-04-16 12)
ChronologicalDatedayDate with day granularity (for example, 2021-04-16)
ChronologicalWeek/YearweekWeek and year (for example, 2021-20)
ChronologicalMonth/YearmonthDate with month granularity (for example, 2021-12)
ChronologicalQuarter/Yearquarteryear
ChronologicalYearyearDate with year granularity (for example, 2021)
PeriodicalMinute of HourminuteOfHourGeneric minute of the hour (1-60)
PeriodicalHour of DayhourOfDayGeneric hour of the day (0-23)
PeriodicalDay of WeekdayOfWeekGeneric day of the week (1-7)
PeriodicalDay of MonthdayOfMonthGeneric day of the month (1-31)
PeriodicalDay of YeardayOfYearGeneric day of the year (1-366)
PeriodicalWeek of YearweekOfYearGeneric week (1-53)
PeriodicalMonth of YearmonthOfYearGeneric month (1-12)
PeriodicalQuarter of YearquarterOfYearGeneric 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.