Derived dataset

You can create a derived dataset by combining existing datasets (source or derived) to create a compound dataset. A fundamental distinction between source and derived datasets is that you define source datasets by mapping tables in the data warehouse. In contrast, derived datasets are based on other datasets within Analytics.

You can create a derived dataset from multiple datasets with custom SQL statements. 

The following are the different sections available for dataset definition:

Insert your SQL query into the SQL box. You can insert dataset names or columns into the box with the auto-complete feature that displays a list of selectable options. Click Run Query to preview your query results.

Inspect the properties of individual columns. The following properties are defined:

These properties have default values, which makes constructing queries for explorations easier.

A primary key is a column or set of columns in a dataset that uniquely identifies each row within the dataset. While configuring a primary key is optional in Optimizely Analytics, it is a highly recommended best practice for ensuring the quality and reliability of your data.

The primary key's main job is to ensure that every row is unique. Without it, you could have duplicate rows that inflate your metrics. For example, if you have two identical rows for the same user, you might incorrectly count them as two separate users, leading to flawed analysis and business decisions. A primary key acts as a safeguard against this.

To define a primary key, ensure that a dataset within Analytics does not include fully duplicated rows, because you cannot establish a primary key for such a dataset.

Health check

The primary key health check is a tool designed to verify that the primary key is functioning correctly, letting you proactively detect errors and build confidence in your data foundation.

Use Check health in the Primary Keys tab to evaluate a given set of columns designated as a primary key to determine how many distinct key values have duplicate rows.

For datasets identified as event streams, the check automatically filters data to the last 60 days. To ensure predictable performance and cost efficiency, the scan is limited to a maximum of 10 million rows.

There are three different health statuses possible, and they are as follows:

In this section, you can define new derived columns and cohorts of rows that represent logical groupings within the respective tabs. This is an optional step. Learn more about cohorts and derived columns.

The Related datasets tab lets you define logical relationships between datasets, similar to how tables relate in a data warehouse. You can combine event and attribute data for richer analysis without understanding the underlying data structure. Analytics uses these relationships to determine which datasets to join in an exploration and only shows relevant options in the UI. For example, using common user ID columns, you can form a relationship between an events dataset and a users dataset.

The Check health option in the Related Datasets tab evaluates a defined relationship between two datasets. It identifies how many distinct values on the many side of the relationship do not have a corresponding match on the one side. This helps uncover broken links or orphaned records that could impact data integrity.

For datasets identified as event streams, the check automatically filters data to the last 60 days. To ensure predictable performance and cost efficiency, the scan is limited to a maximum of 10 million rows.

There are three different health statuses possible, and they are as follows:

Lets you annotate your dataset as an actor, decision stream, event stream, or SCD.

Represents entities that perform actions. When Actor is chosen, click Add Preferred Property and add properties.

Captures decision events such as experiment bucketing. If Decision Stream is chosen, you must configure the following fields:

Contains time-series actions performed by users or other actors. This is often behavioral data, such as clicks, page views, or transactions. When you select Event stream, click Add Event Stream and configure event and actor properties.

Custom partition time column improves query performance by applying exact filters on event time and relaxed filters on a mapped partition column, based on a set time skew.

Use the Index Time Range field to apply a time filter when indexing distinct column values and JSON fields, overriding the app-level setting if specified. You can reset this to the default value when required.

The Session Identifier field in the Event Stream Properties section determines how events are grouped for each actor. It is crucial for defining what constitutes a session for a user within your data. The chosen session identifier, such as a user_id or session_id, should be unique and consistent across your tables to ensure accurate event grouping and analysis.

You can see the events in the dataset when you click See all events.

SCD lets you handle historical changes in your data more effectively, with minimal configuration. SCD2 datasets require only a many-to-one relationship with the actor dataset. When you configure this, Optimizely Analytics auto-joins any event stream that joins with the actor dataset (for example, Users) with the SCD2 table; you do not have to perform any additional setup.

Model this type with the following required fields:

Once you save your dataset, you can view and access it on the Dataset page.