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.

  1. 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.
  2. Click Create Mapping.
  3. Select the data category that the mapping belongs to. For more information, see Data Categories.
  4. Type a display name and description.
  5. 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.

    For more information about mapping types, see Mapping type.

  6. Select a source. For more information, see Sources.
  7. 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.

  8. 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. For more information, see Target group.
  9. If Correction mapping type, select a correction type. This defines whether the correction is an insertion, edit, deletion, or restatement of existing data.
  10. Select a data file type to determine how Visier treats your file uploads. For more information, see Data file type.
  11. Select an override behavior. The override behavior defines how Visier uses newer records to overwrite older records. For more information, see Override behavior.
  12. 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.
  13. Optional: Select Create another mapping. This will generate a new Create mapping dialog after the mapping is created.
  14. When finished, click Create. After the mapping has been created, the next step is configuration. For more information, see Configure a mapping below.

Configure a mapping

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

  1. 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.
  2. In Data category, select the data category that the mapping belongs to.
  3. In the left-hand panel, select the mapping.
  4. Optional: Enable or disable the mapping. By default, new mappings are enabled. If disabled, the mapping is not included in future data versions.
  5. 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.
  6. In Configure, you can change the following settings:
    • Mapping type: One of Regular, Auxiliary, Correction, or Lookup. For more information, see Mapping type.
      • 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 type.
    • Override behavior: The behavior to use newer records to overwrite older records. The override behavior defines how Visier uses newer records to overwrite older records. For more information, see Override behavior.
    • 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. For more information, see Target group.
    • 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.
  7. 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

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

  2. 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.
  3. Select Augment or Replace.

    Note: If Replace, the property must be String data type and mapped to a column or formula.

  4. Click Next.

Step Two: Select properties

The following screenshot shows the Select properties step.

  1. 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.
  2. 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.
  3. 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.
  4. 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:

  1. In Rule display name, type a name for the business rule; for example, HR Business Partner Lookup.
  2. 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).

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

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

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

  2. When finished, click Go to Business Rule or Done.
  3. 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.

  1. In the Connect to lookup dialog, select Use existing.
  2. Click Select lookup mapping and select pay_levels_post_stg from the list.
  3. Select Augment and then click Next.
  4. In Rule display name, type Compensation Maximum Lookup.
  5. 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
  6. Click Select property and then select CompMax. This tells the lookup mapping to augment Employee.Pay_Level_Compensation with values from CompMax.
  7. When finished, click Next and then click Connect.
  8. Visier generates the following business rule:
    Copy
    call augmentingMapping(
        "pay_levels_post_stg",
        Employee.JobPayLevelCode, 
        {
            Employee.Pay_Level_Compensation_Maximum -> "CompMax",
        }
    )
  9. 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.

  1. In the Connect to lookup dialog, select Create new.
  2. In Source, select the data source for the lookup to use to replace Employee.Location.Location_3, such as Location.
  3. 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.
  4. Select Replace and then click Next.
  5. In Rule display name, type New Location Lookup.
  6. In Lookup using source 'Location', select a column to be the mapping key, such as LocationCode.
  7. 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.
  8. When finished, click Next and then click Connect.
  9. Visier generates the following business rule:
    Copy
    call replacementMapping(
        "Location_Lookup"
        Employee.Location.Location_3, 
        "New_Location_Value"
    )
  10. 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.

  1. Preview: Preview mapping records.
  2. Formula Dictionary: Access functions in the formula dictionary.
  3. Column: The columns associated with the record values.
  4. Row: The values associated with a record.
  5. Refresh: After selecting a different record view, adding a record filter, or changing the unique values setting, click Refresh to update the record preview.
  6. 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.
  7. 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.

  1. Preview: A table containing the records in the mapping. The records in the table are affected by the Record view selection, as described next.
  2. Preview distribution: A table containing the count of each value in the mapping.
  3. Refresh: After selecting a different record view, adding a record filter, or changing the unique values setting, click Refresh to update the record preview.
  4. 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.
  5. Expand/Collapse: Expand or collapse the Preview table.

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.