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:
CREATING A NEW TARGET COLUMN
- 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.
- 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.
ADVERITY - SQL DATATYPE EQUIVALENCIES
As a rule, Adverity uses field types as per SQLAchemy.
Below is a list of the most common field types, giving their Adverity titles and SQLAlchemy equivalents.
|Adverity UI||SQLAlchemy||Additional information?|
|String||Unicode||Length is applied|
|JSON||String||Length is applied|
|Float||Numeric||Precision: 13, Scale: 4|
|DateTime||DateTime||If Snowflake: TIMESTAMP_NTZ|
|Formula||String||Only used for Google Sheets to send formulas e.g. =A1 + B1.|
How these later appear in each specific engine type (e.g. PostgreSQL, Redshift, BiqQuery etc.) depends on the database engine dialect definition in SQLAlchemy, which is based on what the database actually supports.
COLUMN TYPES - UNIVERSAL
COLUMN TYPES - ADVERITY ONLY
- 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.
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).|
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.