Derived Dataset
Outline
In this module, you will build a derived dataset in Optimizely Analytics by combining existing datasets with a SQL query.
Learning Objectives
By the end of this module, you will be able to:
Configure a derived dataset using a SQL query.
Classify a dataset as actor, decision, event, or SCD.
Evaluate dataset health using primary key and relationship checks.
Key terms to know
Select a card to reveal the definition.
Configure the dataset definition
Expand each section to see how to set it up on the dataset definition page.
Write the SQL that defines your dataset:
- Open Query and enter your statement in the SQL box. Auto-complete suggests dataset and column names as you type.
- Click Run Query to preview the results.
- Confirm the preview returns the rows and columns you expect before you continue.
Review each column the query returns. For every column you can set:
- Its name, SQL definition, description, and data type.
- Whether it is a measure for analysis or an attribute for grouping and filtering.
- Default aggregation: the operation Analytics applies when an exploration uses the column.
- Data index: caches distinct values so dropdowns load faster, on by default for string columns.
- Enabled: controls whether the column is visible and usable.
Adjust only what your analysis needs.
Set a primary key to keep every row unique and stop duplicates from inflating your metrics.
- Click + Add, then click the box under Columns Group.
- Select the column that identifies each row.
- Click Check health to confirm the values are unique.
A primary key is optional but recommended. Check health returns Healthy when all values are unique, Warning for a few duplicates, or Unhealthy for many. You cannot define one on a dataset that already contains fully duplicated rows.
Define how this dataset connects to others, the same way tables relate in a warehouse.
- Click + Add Related Dataset and choose the target dataset.
- Set the cardinality: many to one, one to many, or one to one.
- Pick the columns that must match, then click Save.
Analytics uses these relationships to decide which datasets to join. Check health reports the join null rate, the share of values on the many side with no match on the one side, which flags broken or orphaned links. It is not available for one to one cardinality.
Semantics annotation types
Annotate a dataset so Analytics understands what it holds, then configure the fields each type requires.
| Annotation type | What it represents | Key fields to configure |
|---|---|---|
| Actor | Entities that perform actions, such as users or accounts | Add Preferred Property for each property you want |
| Decision stream | Decision events such as experiment bucketing | Actor dataset, experiment ID and name, variation ID, decision timestamp |
| Event stream | Time-series actions like clicks, page views, or transactions | Event and actor properties, plus a Session Identifier that groups events per actor |
| SCD | Slowly changing dimensions that track historical changes | Actor dataset, a start timestamp column, an end timestamp column |
Note: For datasets identified as event streams, both health checks look back over the last 60 days and scan a maximum of 10 million rows to keep performance and cost predictable.
Video Walkthrough
See a derived dataset built from one SQL query, then configured with column properties, a primary key, related datasets, and semantics before saving.
Check your understanding
Select an answer, then choose Check Answer to see how you did.
What makes a derived dataset different from a source dataset in Optimizely Analytics?
Why is configuring a primary key on a dataset a recommended practice?
You add a many to one relationship and run Check health. Analytics reports a 25% join null rate. What does this indicate?