Add a Mapping

Learn how to create new mappings in a project.

Overview

To connect your source data to objects in Visier, you must create mappings. Mappings define the link between your source data and Visier through column association or formula calculations. This article describes how to create new mappings and select the right settings.

Tip: To learn more, take the Visier University eLearning course: Mapping your data.

Add a new mapping

Prerequisites: Your Visier tenant must contain data to map and target objects (like subjects or properties) to map data to.

Do one of the following:
- In a project, on the navigation bar, click Data > Mappings.
- In a project, on the navigation bar, click Model > Analytic Objects. Select an analytic object and then click Mappings.
Click Create Mapping.
Select the data category that the mapping belongs to. For more information, see Data Categories.
Type a display name and description.
Select the mapping type, as described next.
- Regular: Mappings that convert records in a source into instances in the analytic model. Conception and termination system events are created for the analytic object from the source's records.
- Auxiliary: Mappings that augment data without the loader creating system events, such as conception or termination events.
- Correction: Mappings that update source data or contain corrected data.
- Lookup: Mappings that connect additional data to the data load target using any column as the key to join tables with the regular mapping. Lookup mappings require formulas or business rules to define the key column and properly augment the additional data. For more information, see Business Rules.
Select a source. For more information, see Sources.
Select a target. This is the Visier object you want to map your data to.
Note: Not relevant if you created the mapping through Model > Analytic Objects.
Optional: Type a target group name. This is a label that groups mappings together for updates. Jobs compare the changes between mappings in the same target group so that data in other mappings isn't considered missing or terminated.
If Correction mapping type, select a correction type. This defines whether the correction is an insertion, edit, deletion, or restatement of existing data.
Select a data file type. For more information, see Data file types below.
Select an override behavior. For more information, see Override behavior below.
Optional: Select or de-select Run automap. By default, Run automap is selected and will automatically connect source columns to target properties by matching similar names. If de-selected, the automap feature will not run once the mapping is created.
Optional: Select Create another mapping. This will generate a new Create mapping dialog after the mapping is created.
When finished, click Create. After the mapping has been created, the next step is configuration. For more information, see Add a Mapping below.

Configure a mapping

To connect your data to Visier objects, you must configure a mapping, including its settings and mandatory properties.

Do one of the following:
- In a project, on the navigation bar, click Data > Mappings.
- In a project, on the navigation bar, click Model > Analytic Objects. Select an analytic object and then click Mappings.
In Data category, select the data category that the mapping belongs to.
In the left-hand panel, select the mapping.
Optional: Enable or disable the mapping. By default, new mappings are enabled. If disabled, the mapping is not included in future data versions.
In Map, you can do the following:
- Optional: Column melting: Converts multiple columns into two columns, Melted column name and Melted column value. For example, your Compensation source data might have columns for Salary, Bonus, and Allowance, however, Visier expects a column for Compensation Type. With column melting, you can melt Salary, Bonus, and Allowance together to map to the Compensation Type property in Visier. In formulas, use meltColumns() to get the column names and column(meltColumns()) to get the column values.
  - Include: Combines all selected columns into two melted columns.
  - Exclude: Combines all non-selected columns into two melted columns.
- Optional: To automatically connect source columns to target properties by matching similar names, click Automap.
- In Properties, expand the property you want to map.
  Note:
  - To quickly find a specific property, use the Search box.
  - To filter the list by properties that aren't mapped, de-select Mapped and select Unmapped, as shown next.
- Select one of the following:
  - Map from column: Select the data column to associate with the property.
  - Map from formula: Type a formula to identify the data column to associate with the property and any other details about the property, such as if/else statements. You can optionally click Full Editor to write the formula in full screen, preview the record values, and use the Formula Dictionary. For more information about the syntax, see Visier Extraction Language (VEL).
- To connect a property to a lookup mapping, click the Connect property to lookup button . For more information about connecting a property to a lookup, see Connect property to lookup.
- To check which business rules use a property, click the View business rules button .
  The dialog shows the existing business rules that use the property in the context of any other business rules for the subject. For example, if the Employee subject uses the EmployeeID property in one business rule, the dialog shows all other Employee business rules to help you understand the context of the EmployeeID business rule.
  You can go to the business rule in the same tab or open the business rule in a new tab .
- Optional: In Intermediate Properties, add any required intermediate properties. Intermediate properties allow you to use business rules to load source data for properties that aren't in Visier's analytic model. In lookup mappings, you can add intermediate properties for all columns in the lookup's source data so that you can assign the columns to properties through business rules.
- To preview the included or excluded values in the property, click Preview. For more information, see Preview records in a mapping below.
In Configure, you can change the following settings:
- Mapping type: One of Regular, Auxiliary, Correction, or Lookup.
  - Regular: Mappings that convert records in a source into instances in the analytic model. Conception and termination system events are created for the analytic object from the source's records.
  - Auxiliary: Mappings that augment data without the loader creating system events, such as conception or termination events.
  - Correction: Mappings that update source data or contain corrected data.
  - Lookup: Mappings that connect additional data to the data load target using any column as the key to join tables with the regular mapping. Lookup mappings require formulas or business rules to define the key column and properly augment the additional data. For more information, see Business Rules.
- Source: A loaded data source. For more information, see Sources.
- Target: The Visier object to map your data to. For more information, see Analytic Model.
- Data category: The data category that the mapping belongs to. For more information, see Data Categories.
- Correction type (if correction mapping): One of Insert, Edit, Delete, Restatement.
- Data file type: One of Transactional profile, Transactional log, or Temporal. For more information, see Data file types below.
- Override behavior: The behavior to use newer records to overwrite older records. For more information, see Override behavior below.
- Target group: Groups mappings together. When there are multiple mappings in a target group, jobs compare the changes between mappings in the same target group so that data in other mappings isn't considered missing or terminated.
- Optional: In Record filter, type a formula. The record filter defines logic to select or ignore records in a source. To access a preview of the records and the formula dictionary, click Enter full screen. For more information about record filter expressions, see Record filter below.
When finished, run a job to generate a data version in the project. You can then preview your changes in the solution. For more information, see Run a Job.

Connect property to lookup

In a mapping, you can replace or augment a property's values using a lookup mapping.

Augment: Fills in empty or null values in the property with the values from the lookup mapping.
Replace: Replaces existing values in the property with the values from the lookup mapping. Must be String data type.

The lookup mapping joins the data tables for a target property using a different property. To make this possible, you must identify a property in the target and a property in the lookup mapping to act as keys to join the data tables. The target key and the mapping key work together to find matching records to then join data tables from the target and the lookup mapping.

This workflow generates a business rule that augments or replaces the property's values as defined by you.

Step One: Select lookup and behavior

To connect a property to a lookup mapping, in a mapping, find the property to connect to a lookup mapping and click the Connect property to lookup button .
In the Connect to lookup dialog, do one of the following:
- To create a new lookup mapping, select Create new. Like other mappings, you must select a source and map the EventDate property to a column or formula. You can change the lookup mapping's default settings after creating the lookup mapping.
- To use an existing lookup mapping, select Use existing. If a lookup mapping already exists that you want to use, select it from the list or turn on Use target group and select a target group from the list.
Select Augment or Replace.
Note: If Replace, the property must be String data type and mapped to a column or formula.
Click Next.

Step Two: Select properties

The following screenshot shows the Select properties step.

Target key: The target property to use as the key. In the screenshot, HR_Business_Partner is the target key. If unmapped, you can map it to a column or formula.
Mapping key: The mapping property to use as the key. In the screenshot, the mapping key isn't mapped. You can map it to a column or formula for the selected source.
Target property: The property to augment or replace with data from the lookup property.
Note:
- If Replace, the target property acts as the target key joined with the mapping key. In the screenshot, the target property and target key are both HR_Business_Partner. This is so that you can completely replace the target property values. If the lookup mapping can't find a target key value in the mapping key values, the target property value is set to empty.
- If Augment, the target property is different from the target key so that you can fill in nulls or missing values in the property with values from a different property.
Lookup mapping property: The property whose data will augment or replace the target property's data. In the screenshot, the lookup mapping property isn't mapped. You can map it to a column or formula for the selected source.

To complete this step, do the following:

In Rule display name, type a name for the business rule; for example, HR Business Partner Lookup.
If the target property was unmapped, as shown in the following screenshot, map it now. This is the target key (as shown in the Select properties screenshot).
If creating a new lookup mapping:
- In the top-right column, map the mapping key.
- In the bottom-right column, map the lookup mapping property. Optionally, assign the property an object name.
If using an existing lookup mapping, in the bottom-right column, select a lookup mapping property to augment the target property.
Note: The properties in the list are the same data type as the target property (bottom-left column).
Optional: If Augment, click Add Property to add additional lookup mapping properties to augment the target property. You can then select another target property and lookup mapping property.

Step Three: Connect the properties and review the generated business rule

Click Next and then click Connect.
Result: Visier generates a business rule. If creating a new lookup mapping, Visier creates the lookup mapping and its properties.
When finished, click Go to Business Rule or Done.
Navigate to the business rule and rearrange the business rules into the correct order. For more information, see Business Rules.

Example: Let's say you want to fill in nulls or empty values for the Employee.Pay_Level_Compensation_Maximum property with values from the CompMax property. You have an existing lookup mapping called pay_levels_post_stg that has a CompMax property containing those values.

In the Connect to lookup dialog, select Use existing.
Click Select lookup mapping and select pay_levels_post_stg from the list.
Select Augment and then click Next.
In Rule display name, type Compensation Maximum Lookup.
Click Select target key and then select JobPayCodeLevel.This tells the lookup mapping to use JobPayLevelCode as the target key to join the tables together. The lookup mapping key is the MappingKey property. This might be mapped to a JobPayCodeLevel column in the lookup mapping
Click Select property and then select CompMax. This tells the lookup mapping to augment Employee.Pay_Level_Compensation with values from CompMax.
When finished, click Next and then click Connect.

Visier generates the following business rule:

Copy

call augmentingMapping(
    "pay_levels_post_stg",
    Employee.JobPayLevelCode, 
    {
        Employee.Pay_Level_Compensation_Maximum -> "CompMax",
    }
)

Click Done.

Example: Let's say you want to replace the property Employee.Location.Location_3 with New_Location_Value, but you don't have an existing lookup mapping to do this.

In the Connect to lookup dialog, select Create new.
In Source, select the data source for the lookup to use to replace Employee.Location.Location_3, such as Location.
Optional: To map EventDate for New_Location_Value, in Map 'EventDate', do one of:
- Select Map from column and then select the EventDate column from the list.
- Select Map from formula and then type dateColumn("EventDate") in the formula box.
Select Replace and then click Next.
In Rule display name, type New Location Lookup.
In Lookup using source 'Location', select a column to be the mapping key, such as LocationCode.
In Replace, click Select a column and then select New_Location_Value. This tells the lookup mapping to replace Employee.Location.Location_3 with values from New_Location_Value.
When finished, click Next and then click Connect.

Visier generates the following business rule:

Copy

call replacementMapping(
    "Location_Lookup", 
    Employee.Location.Location_3, 
    "New_Location_Value"
)

Click Done.

Preview records in a mapping

After mapping properties to columns or formulas and optionally applying a record filter, you can preview the records in the mapping. You can access record previews in the following ways:

To preview values for the entire mapping, in Record filter, click Enter full screen. In the record filter dialog, the Preview tab shows the record preview. You can filter by all records, records included by the mapping's record filter, or records excluded by the record filter.
To preview values for a specific property, expand a mapped property and then click Preview.

The following screenshot shows the record preview for the entire mapping and record filter (if available). In this example, the mapping is Employee.

Preview: Preview mapping records.
Formula Dictionary: Access functions in the formula dictionary.
Column: The columns associated with the record values.
Row: The values associated with a record.
Refresh: After selecting a different record view, adding a record filter, or changing the unique values setting, click Refresh to update the record preview.
Settings: Change the file for the mapping, change the record view, or turn on unique values only.
- File: The data file from which to view records.
- Column: If column melting is set, select one of the columns to preview values for.
- Record view: The records to preview. The record views are affected by the record filter. If you select a different record view, always click Refresh to update the record preview.
- Unique values only: Whether the record preview shows all values or only unique values. If enabled, each unique value in the mapping is listed once. If you change the unique values setting, always click Refresh to update the record preview.
Columns: Select or de-select the columns to include in the preview.

The following screenshot shows the record preview for a specific property. In this example, the property is EventDate.

Preview: A table containing the records in the mapping. The records in the table are affected by the Record view selection, as described next.
Preview distribution: A table containing the count of each value in the mapping.
Refresh: After selecting a different record view, adding a record filter, or changing the unique values setting, click Refresh to update the record preview.
Settings: Change the file for the mapping, change the record view, or turn on unique values only.
- File: The data file from which to view records.
- Column: If column melting is set, select one of the columns to preview values for.
- Record view: The records to preview. The record views are affected by the record filter. If you select a different record view, always click Refresh to update the record preview.
- Unique values only: Whether the record preview shows all values or only unique values. If enabled, each unique value in the mapping is listed once. If you change the unique values setting, always click Refresh to update the record preview.
Expand/Collapse: Expand or collapse the Preview table.

Data file types

The data file type specifies the temporal nature and function of the records in the source.

This setting reflects how the data was captured with implications on its shape, completeness, and meaning. Some types are snapshots that include all values in a record and all records at the snapshot time. Some types are journal-like and report changed values.

Tip: Consider the shape of data:

Some data is wide where an ID appears once in each row that has many columns.
Some data is tall with few columns and where an individual record ID can appear in many rows.

The following table describes data file types for subject mappings.

Data file type	Description
Temporal	Temporal data is snapshot-style data comprising a set of records at a point in time. This data is "wide" and includes all the columns in the schema for each record. Typically, temporal data is loaded period-to-period. The loader compares the incoming data and infers if members of the subject need a system event (conception or termination). The loader needs few rules to move the records into the platform.
Transactional log	Transactional log is journal-style data comprising changes to data over an interval. This data is "tall" and lists only the changed attributes with the new attribute value. Each line includes a time associated with the change. This data file type makes it simpler to find changed values and fewer records are moved. Changes are not inferred but taken from the log. The loader needs additional extraction rules to move the data into the platform.
Transactional profile	Transactional profile is a hybrid of temporal and transactional log types. The data excludes any unchanged records and includes each changed record in its entirety. The data is "wide", where each row is included if one or more attributes have changed. The time associated with the change is included in the record. For example, you might send employee data to Visier as a transactional profile to update employee records that changed and omit unchanged records. Transactional data, both log and profile types, are less verbose than temporal data and require more data business rules to create system events.
Validity range	Validity range is a snapshot expressing the state of records. The data is "wide" and the records include an explicit validity start and validity end time. This type is often used with records that are associated with an interval, such as performance, or external data used as a reference.

The following table describes the data types for event mappings. Each data type is tall and includes a key for the record that includes the key of the associated member of a subject and a timestamp.

Data type	Description
Regular events	Regular events are journal-style data types where each record is an occurrence of an event—anything that happens to a subject.
Conception events	Conception events are journal-style data types where each record is the start of the validity interval for a member of a subject. For example, employment start is event data that indicates the start of an employee profile (a profile conception event).
Termination events	Termination events are journal-style data types where each record is the end of the validity interval for a member of a subject. For example, employee exit is event data that indicates the end of an employee's profile (a profile termination event).

The following table describes data file types for lookup mappings.

Data file type	Description
Temporal	Temporal data is snapshot-style data comprising a set of records at a point in time. This data is "wide" and includes all the columns in the schema for each record. Typically, temporal data is loaded period-to-period. The loader compares the incoming data and infers if members of the subject need a system event (conception or termination). The loader needs few rules to move the records into the platform.
Transactional log	Transactional log is journal-style data comprising changes to data over an interval. This data is "tall" and lists only the changed attributes with the new attribute value. Each line includes a time associated with the change. This data file type makes it simpler to find changed values and fewer records are moved. Changes are not inferred but taken from the log. The loader needs additional extraction rules to move the data into the platform.
Transactional profile	Transactional profile is a hybrid of temporal and transactional log types. The data excludes any unchanged records and includes each changed record in its entirety. The data is "wide", where each row is included if one or more attributes have changed. The time associated with the change is included in the record. Transactional data, both log and profile types, are less verbose than temporal data and require more data business rules to create system events.
Static map	Static map is a special transactional profile data for lookup mappings that do not require business rules to augment the data. Static maps are useful when a lookup is used in mapping formulas.

Available data types by mapping type

The available data types change with the selected mapping type. The following table shows the data types that can be used per mapping type.

Mapping type	Data types
Regular	Temporal, transaction log, transactional profile, validity range
Auxiliary	Temporal, transaction log, transactional profile
Lookup	Temporal, transactional log, transactional profile, static map
Correction	Various

Override behavior

The override behavior defines how Visier uses newer records to overwrite older records. Visier determines whether a data file is newer or older using snapshot time. By default, snapshot time is the file's upload time, but you can set a different snapshot time in the global workspace in Data > Sources > Uploaded Files. For more information about snapshot time, see How Time Is Represented in a Source.

If newer files have records that conflict with records from older files, Visier ignores the conflicting records in older files. When you set the override behavior in a mapping, it sets the properties to compare to detect conflicts.

The four override behaviors reflect different combinations for the date attribute and whether to use the subject ID to detect conflicts. Generally, Event date is the default.

Event date: Ignores records in older files that have the same event date as records in newer files. This override behavior is commonly used for snapshot data files, such as snapshots at the end of each month.
Example: Let's say that you previously uploaded data files with the event date 2023-02-01 and you've now uploaded new data files with the same event date but a more recent snapshot time. In the mapping, if you select the override behavior Event date, Visier ignores any records in the older file that have the same event date as records in the newer file.
Event date and subject member ID: Ignores records in older files that have the same event and subject member ID as records in newer files. This override behavior is commonly used for corrections or insert-only data.
Example: Let's say that you previously uploaded data files with the event date 2023-02-01 and you've now uploaded new data files with the same event date but a more recent snapshot time. Both files contain the subject member ID "Employee-123". In the mapping, if you select the override behavior Event date and subject member ID, Visier ignores any records in the older file that have the same event date and subject member ID as records in the newer file.
Record period date: Ignores records in older files that have the same record period date as records in newer files. This override behavior is commonly used to replace a set of events with a new set.
Example: Let's say that you previously uploaded data files with the record period date 2023-02-01 and you've now uploaded new data files with the same record period date but a more recent snapshot time. In the mapping, if you select the override behavior Record period date, Visier ignores any records in the older file that have the same record period date as records in the newer file.
Record period date and subject member ID: Ignores records in older files that have the same record period date and subject member ID as records in newer files. This override behavior is commonly used for partial data files, such as to restate a single employee's full history.

Example: Let's say that you previously uploaded data files with the record period date 2023-02-01 and you've now uploaded new data files with the same record period date but a more recent snapshot time. Both files contain the subject member ID "Employee-123". In the mapping, if you select the override behavior Record period date and subject member ID, Visier ignores any records in the older file that have the same record period date and subject member ID as records in the newer file.

The following table is a matrix to determine the override behavior you should use for your mapping.

	Date attribute: Event date column	Date attribute: Record period date
Consider subject member ID	Event date and subject member ID	Record period date and subject member ID
Don't consider subject member ID	Event date	Record period date

Record filter

Record filters are Boolean expressions. If the record filter expression evaluates to true for a record, Visier selects the record. If false, Visier ignores the record.

Each record filter expression may include the record's attributes and information associated with the source, such as the name of a file, and date of the folder containing the file.

The following table provides examples of record filter expressions.

Note: For more information about the syntax, see Visier Extraction Language (VEL).

Example	Record filter expression
Exclude records with blank employee IDs	column("EmployeeID") != ""
Exclude interns and cooperative education workers	column("EmployeeType") != "Student"
Exclude records for employees in Denver	column("Location-Location_3") != "Denver"
Exclude records with an event date before a threshold	date(column("EventDate"), dateFormat("yyyy-MM-dd")) > date("1997-07-01", dateFormat("yyyy-MM-dd"))
Combine two filters with Boolean operators: Exclude blank ID or students column	("EmployeeID") != "" or column("EmployeeType") != "Student"
Combine two filters with Boolean operators: Exclude records with blank event date or employee ID	column("EventDate") != "" OR column("EmployeeID") != ""
Comment complicated logic	// This is a comment. This filter selects active employees with a start date after 1995-Jan-01.