Articles in this section

Data Schema

Avatar
Documentation Team
  • Updated
Follow

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). 

Schema_Mapping_Basics_-_Annotated.png

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.
  1. Navigate to the 'Transfer' Element,
    Click_Transfer.png
  2. Navigate to "Data Schema".
    Transfer_-_Click_Data_Schema.png
  3. Select "Add Target Column". Click_Add_target_column.png
  4. 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).
    Add_Data_Schame_Column_-_Configuration_annotated.png
  5. 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.

  1. a. Open the Workspace Picker and; 
    b. Select the Workspace from which you wish to export.
    Connect_-_Workspace_Picker_to_Selecting_Export_Workspace.png
  2. Within the new Workspace, navigate to 'Transfer'.
    In_Workspace_for_Export_-_Click_Transfer.png
  3. Click on 'Data Schema'.
    In_Export_Workspace_-_Transfer_-_Click_Data_Schema.png
  4. a. Click on Schema Actions, and then;
    b. Click "Export to...", choosing your format of preference.
    Workspace_for_Export_-_Schema_Actions_-_Export_to_file.png

  5. a. Return to the Workspace Picker, and; 
    b. Select the Workspace to which the Data Schema should be imported.
    Return_to_original_Workspace_for_Import.png
  6. 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.
    Importing_Schema_back_into_your_workspace.png
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.

 

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
Long BigInteger  
Float Numeric Precision: 13, Scale: 4
Date Date  
DateTime DateTime If Snowflake: TIMESTAMP_NTZ
Boolean Boolean  
Percentage Float  
Currency Float  
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

 

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.

Related articles

Comments

0 comments

Article is closed for comments.