Data Schema

Schema mapping is the process by which you can realign, combine, and rename individual data types in the transition from your data source, to Datatap, and beyond into your visualization software. Schema can be used to great effect in analyzing data from disparate sources and formats.

Schema mapping is used to map source columns in extracts (a) to Target Columns in a Destination (b). This allows columns from different data sources, with different names but tracking the same elements, to be unified.

The schema further indicates whether a field should be treated as a metric (c) or a dimension (d). For most data sources, Adverity has system defaults loaded in to simplify the schema mapping process (e).

The column color indicates:

Grey: Unmapped.
Green: Target column is a metric (c).
Blue: Target column is a dimension (d).

IMPORTANT: Schema mapping will only populate once you have fetched at least one extract with one row of data, so that column names can be extracted. If your schema mapping is empty, please perform a successful fetch first.

CREATING A NEW TARGET COLUMN

This process creates a new column type in your Destination, which can then be connected to a source column from your data source. For instance, creating a Target Column "Date", to which you can then connect and unify your data source columns "{date}" and "Day" from different data sources.

Navigate to the 'Transfer' Element,
Navigate to "Data Schema".
Select "Add Target Column".
Fill in the following configuration details:
a. The column Name - lower-case letters only, no special characters allowed, and please use underscore "_" instead of spaces " ".
b. Column Type (e.g. data format: string, metric, etc.).
c. Select Length (sets a max character limit, relevant if using the String column type (optional)).
d. Select Measure (expressed as a mathematical function, relevant if using the metric column type).
Click Save.

EXPORTING AND IMPORTING DATA SCHEMA

Data Schema cannot be inherited from one Workspace to another, but it is possible to copy all of the Data Schema Columns and import them.

a. Open the Workspace Picker and;
b. Select the Workspace from which you wish to export.
Within the new Workspace, navigate to 'Transfer'.
Click on 'Data Schema'.
a. Click on Schema Actions, and then;
b. Click "Export to...", choosing your format of preference.
a. Return to the Workspace Picker, and;
b. Select the Workspace to which the Data Schema should be imported.
Navigate back to Transfer > Data Schema, and then:
a. In the top right corner click on Schema Actions, then
b. Import from File, and insert the previously saved mapping.

PLEASE NOTE!

When the target Destination is the Insights module or an SQL database, Dimensions can be defined as Key Columns to uniquely identify data sets.
Each target column can be used only once per Datastream.
To export to the Insights module, map at least 1 dimension, 1 metric and 1 date (to target column day).
It is possible to define Schema Mapping Defaults per data source.

COLUMN TYPES - UNIVERSAL

String	For dimensions (non-numerical entries), e.g. names, titles, descriptors.. Length: 200 characters if field is left blank, limit/extend by entering required number of characters. Measure: always use ----.
Long	For full-integer metrics without decimal points (e.g. 16).
Float	For non-integer metrics with digits after a decimal point (e.g. 16.523).
Date	A date object. Common Date patterns will be recognized, we recommend ISO 8601 format "YYYY-MM-DD". Available in all Destinations EXCEPT Insights module. To display dates in Insights, use either the default column 'day', or a column set up as type 'string'.
DateTime	Should contain information about date & time. Common DateTime patterns will be recognized, we recommend ISO 8601 format "YYYY-MM-DDThh:mm". Works with all Destinations EXCEPT Insights.
Boolean	For fields that can either be 0 (false) or 1 (true). Obviously works with binary metrics 0 and 1, but can also be used to evaluate strings, where an empty string will be 0, and a string present will be 1.

COLUMN TYPES - ADVERITY ONLY

Currency	For metrics that should be displayed with a currency symbol. Only available for Adverity platform.
JSON	For layered metrics. Only available for Adverity platform.
Percentage	For rates and percentages. Automatically multiplies value by 100 (e.g. 0.1 is displayed as 10%). Only available for Adverity platform.

Length

Only applicable to String-type fields, used to limit/extend the maximum number of characters allowed in the field.
- 200 characters is the default if "Length" is left blank.
Adjusting column length is usually only required when using key columns for a database Destination.

Measures

Default	Any column set up with this measure is created as a String, regardless of the selected column type.
Sum	Applicable for most metrics that have values that can be summed up across several datasets (e.g. clicks, impressions, etc.).
Don't Aggregate	For metrics that must not be summed up across several datasets (e.g. campaign reach).
Average	Displays averages for all values imported by calculating (total value)/(number of datasets).
Count	Number of datasets.
Min	Displays the minimum single value across all datasets.
Max	Displays the maximum single value across all datasets.

SCHEMA MAPPING IN ADVERITY PLATFORM

When exporting to Explore, we have two limitations in place:

Variable name is limited to 60 characters.
Dimension value is limited to 2700 characters. This limitation can be extended if necessary.
If you're having trouble converting Metrics to Dimensions or vice versa when exporting to Explore, please see this article.

Articles in this section

CREATING A NEW TARGET COLUMN

EXPORTING AND IMPORTING DATA SCHEMA

COLUMN TYPES - UNIVERSAL

COLUMN TYPES - ADVERITY ONLY

Length

Measures

SCHEMA MAPPING IN ADVERITY PLATFORM

Comments

Articles in this section

CREATING A NEW TARGET COLUMN

EXPORTING AND IMPORTING DATA SCHEMA

COLUMN TYPES - UNIVERSAL

COLUMN TYPES - ADVERITY ONLY

Length

Measures

SCHEMA MAPPING IN ADVERITY PLATFORM

Related articles