In Datarails, data is stored in Fileboxes, each containing multiple file versions differentiated by tags. After creating a Filebox and uploading your files, they need to be transformed and stored in the database. The Data Mapper tool handles this transformation, organizing your data to fit the structured format of the database.
Data Mapper is used to:
- Import and integrate your data into the database efficiently.
- Apply advanced filters to clean and refine your data.
- Assign tags such as Entity, Departments, etc., to categorize and organize your data.
- Create and include custom columns to meet your specific data requirements.
Access the Data Mapper
- Navigate to the Workspace and select the relevant Filebox.
- Click on the three dots ellipsis icon and select 'Data Mapper'.
- The Data Mapper will open with a preview of the latest version of the most recent time dimension.
- To open the Data Mapper for a specific version, go to the Filebox, choose the desired version, click on the three dots ellipsis icon, and select 'Data Mapper'.
Data Mapper Structure
To create a new Data Mapper, it's essential to first understand the structure. A Data Mapper consists of three main sections.
Version preview
The top section of the Data Mapper displays your data as received, either from an uploaded Excel file or incoming data via automated integration from your ERP system. You can navigate between different sheets to preview specific ones.
Highlighted purple headers indicate the columns being processed into the database. You can choose to include or exclude certain headers, ensuring that only the necessary data is used for reporting.
Database preview
The bottom section displays how Datarails interprets your data. Here, you can adjust how the data will be translated into the database. Learn about Column types and colors.
The preview displays your data with applied manipulations, custom columns, and filters, offering a view of the finalized database version. You can navigate between sheets to see the data's final form.
In the database preview, you can customize which fields are displayed by using the filter options. By default, Lookup fields and custom columns are shown. However, System fields and Hidden fields are not displayed initially. You can add these fields by ticking the corresponding boxes. Read more about System fields and Hidden fields.
Mapper Information
The upper-left panel provides essential Mapper information, including:
- Table name: Represents the Database in the environment where you save the version data. Learn more about Tables.
- Mapper Name: Automatically chosen and can be edited as needed.
- Real-time update toggle: Allows you to switch between real-time and manual updates for the Database preview.
- 'Create New' icon: Explore how to Create a new Data Mapper and utilize all functionalities.
Mapper editor
On the left panel, you have the option to customize your Data Mapper. Choose, edit, filter headers, add Custom Columns, and make any additional adjustments you need. Learn how to Create a new Data Mapper.
This Datarails University video offers a comprehensive tour of the primary features of the Data Mapper:
Column Types
To proficiently construct a data mapper, it's essential to understand that there are eight distinct column types, each represented by a different color. While utilizing all eight types is not mandatory, the commonly employed ones include Headers, Dimensions, Lookups, and Custom columns.
Headers
Fields selected from the version preview are highlighted in Purple. In the Headers section, you can change the fields names. This is useful for making fields easier to understand for the cross-functional team without changing the names in your ERP system. Learn more about Headers Match Type, and how to apply Data manipulations and filters.
Dimensions
Columns chosen from the version preview in case of dimension structured data, marked in Pink. In the Dimensions section, you convert certain columns into rows in the database. Learn more about Dimensions Match Type, and how to apply Data manipulations and filters.
Lookups
Fields sourced from a Lookup connection, based on Key Fields, colored in Green. Hovering over a green Lookup fields reveals the source Filebox and the key used for joining. Learn more about Lookup connections in Datarails.
Custom Columns
Calculated Columns generated using the Custom Column editor, colored in Beige. Explore more about Custom Columns and Custom Column Functions.
System Fields
Fields created on the backend, colored in Blue. System Fields are not displayed by default but can be shown by using the filter icon on the bottom right. Learn more about System Fields.
Table Attributes (optional)
Columns indicating the property applying to the entire table, colored in Teal. The value of an attribute is extracted from a cell to the right of the selected attribute cell.
Table ID (optional)
Columns displaying properties applicable to the entire table, colored in Dark Green. The value of an attribute is extracted from the selected cell.
Table Footer (optional)
Selects a cell to designate the end of the table to be scanned. The Mapper determines where to cease scanning for additional data, excluding any data below the mapped table. Although not visible in the preview, these are colored Beige.
Key Field Indicator
Fields acting as keys for other Lookup table fields are indicated by a red dot next to their names. This visual cue helps you quickly identify which fields in your Data Mapper are used as keys for the Lookup connection, assisting in the resolution of related issues. Hovering over a Key column reveals the list of Lookup Fields associated with it.
Fields Match Type
Match Type refers to the method used to create a Mapper column within the dataset based on the underlying file.
By choosing a Match type, you define the rules and criteria by which the column is generated, whether through exact value matching, pattern, visual styling, or other specified criteria.
Match Type can be adjusted for Headers, Dimensions, Table Attributes, and Table Footer, and is accessible through each column's 'Advanced Options' menu via the three dots ellipsis.
Any Text
Refers to a method of creating a column based on the presence of any text within the underlying file, regardless of the specific content or value of the header.
List
Refers to a method of creating a column based on predefined lists of values within the underlying file. The mapper column will be created only if the column name in the underlying file matches one of the list values.
Location
Refers to a method of creating a column based on the positional location of the header within the underlying file rather than the specific value of the header itself. When set to Location, Mapper column will be created regardless of whether there is a direct match in values between the datasets.
Regex
Refers to a method of creating a column based on predefined patterns using regular expressions (regex). The mapper column will be created only if the column regex in the underlying file matches the mapper column Regex.
Style
Refers to a method of creating a column based on the visual characteristics or style attributes of the data rather than its content. Mapper column will be created only if column has same style attributes.
Value
Refers to a method of creating a column based on exact Value. Mapper column will be created only if column value in underlying file matches the mapper column Value.
Create a New Data Mapper
Learn how to Create a new Data Mapper.
Multiple Data Mapper
You can create multiple Data Mappers for a single file. For instance, if your data source comprises multiple sheets with varying structures, you'll need to create multiple mappers to extract the data effectively. Learn how to Create multiple Data Mappers.
Data Mapper CSV file
You can download the Data Mapper CSV file to see how your input file was mapped into the database. The CSV file will contain all fields, including headers, custom columns, and lookup fields, along with all the data.
Downloading the CSV file can help you investigate the data in your environment. This allows you to verify if your data was translated correctly into the database and to ensure all data appears as expected.
- Select the relevant Filebox that contains the data you want to download.
- Within the selected Filebox, choose the version of the data you need.
- Locate and click on the 'Included in DB' status to access the download option.
- Click on the download icon under the 'Download as CSV' option.
- The Data Mapper CSV file will be downloaded to your computer.
Delete an existing Data Mapper
Filebox can have one or multiple data mappers applied. If you want to delete a specific mapper from the Filebox and remove the associated data from the table, follow these steps:
- Go to the Tables section and locate the relevant table.
- Click the number displayed under the 'Connected Fileboxes' column.
- The Table mappings list will be displayed. Note that different rows can represent the same Filebox but with different mappers.
- Identify the mapper you want to delete by its name.
- Click the three dots ellipsis icon next to the mapper's name and choose 'Delete'.
- Confirm the deletion by clicking 'Yes' in the delete confirmation message.
- The selected data mapper will be removed from the Filebox, and the associated data will be deleted from the table.
Data Mapper Errors
In situations where an issue arises during the Data Mapper process, you'll receive an indication in both the Filebox and the Data Mapper preview. This indication includes a detailed error description along with steps for resolution.
Common Data Mapper Error and Resolution Steps:
- Error: Structure changes in the new file from the previous one
Resolution Steps: Check misalignment in header configuration. Adjust cell name or change match type to Location in 'Advanced Options'.
- Error: Incorrect Syntax in Custom Column creation
Resolution Steps: Recreate the Custom Column with the correct syntax. Learn more about Custom Columns.
© Datarails Ltd. All rights reserved.
Updated
Comments
0 comments
Article is closed for comments.