In Datarails, after creating a Filebox and uploading your files, these files must be transformed and stored in the database. This involves translating and organizing the data to fit the database's structured format. The Data Mapper handles this transformation, ensuring your data is organized and ready for use.

To create a new Data Mapper in Datarails, it's important to understand its structure and key areas. Learn more about the Data Mapper before diving into this article.

Create a new Data Mapper

Creating a Data Mapper in the collection process applies to all Fileboxes within it.

1. Navigate to the Workspace and select the relevant Filebox.
2. Click on the three dots ellipsis icon and select 'Data Mapper'.
3. The Data Mapper opens 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 the ellipsis icon, and select 'Data Mapper'.
4. In the Tables selection window, select an existing table or create a new one by clicking 'New +', typing the table name, and hitting 'Save'. The 'Mapper name' will be automatically generated, combining the Filebox name and the table name. You can keep the auto-filled mapper name or change it. Click 'Select'. Tables function as the database within your environment. Learn more about Tables. 
5. In the 'Select table type' window, choose the table type according to your file structure. the table type will auto-map your data. for flat files, use Flat table. For files with dimensions, use dimension table. For complex files with many tables, choose multiple tables. 
6. The top section of the Data Mapper displays your data as received. Explore the Data Mapper structure. 
   
7. If you have more than one sheet, navigate to the relevant one. you can also add a sheet filter using the filter icon. This ensures the Data Mapper picks up data from the correct sheet only.

8. The mapper name appear in the mapper information section on the top left. Change it by clicking the pencil icon. Changing the mapper name helps with identification when you have multiple mappers.
   

9. Add headers by clicking 'Add header' in the Headers section. Select the relevant ones from the file preview. Each selected header will be highlighted in purple in the top preview and added to the database preview in purple. from the Headers section, you can also change the header name or Match Type. Add only the necessary headers to avoid reducing performance and increasing refresh times. If needed, you can change the Field Type and add Filters.
10. If you picked the wrong header, remove it by clicking the three dots ellipsis next to the header name. you can also remove all headers by clicking the trash icon in the Headers section.  
    
11. To add dimensions from a dimension structured file, click 'Advanced Mapping' and Dimensions. Pick all headers or only the first one and click 'repetitive' (for headers sharing the same format or wording). Click 'Done'.
    Repeat the process to add more dimensions. Each selected dimension will be highlighted in pink in the top preview and added to the database preview in two separate columns: the dimension cells in pink and the values in purple. From the Dimensions section, you can also change the dimension or value name or Match Type. If needed, you can change the Field Type and add Filters.
12. If you picked the wrong dimension, remove it by clicking on the three dots ellipsis next to the dimension name. you can also remove all dimensions by clicking on the trash icon in the Dimensions section.  
    
13. Add custom columns by clicking on 'Add custom column' under the Custom Columns Section. In the 'Add custom column' window, choose an existing name or create a new one, add a value or function and click 'ok'. Learn how to use the custom columns creator and custom columns functions.
    Each custom column you create will be added to the database preview in yellow and will create a value for each row in the database. 
    
14. If you have a Table Footer, Table Attribute or Table ID, add those by clicking on 'Advanced Mapping'. 

15. Review the database preview at the bottom part. Ensure all custom column were created successfully, all headers were selected and the Key Field indicator exist with all connected Lookup fields in place. 

16. To save the new Data Mapper or changes made, click 'Publish & Scan Latest Version'. The system will auto-rescan the mapper, which may take several minutes depending on the amount of data.
    

Apply an existing Data Mapper

If you have created a Data Mapper and you'd like to use it another Filebox, you don't have to re-create it from scratch, you are can Publish & Apply an existing Data Mapper. 

To publish and apply an existing Data Mapper, follow these steps:

1. Click the arrow icon next to the 'Publish & Scan Latest Version' button.
2. Select 'Publish & Apply to'.
3. Carefully mark all Fileboxes you want to apply the Data Mapper to.
4. Verify you have chosen only the relevant Fileboxes.
5. Click 'Save'.
   

Multiple Mappers

You can create multiple Data Mappers for a single file. For example, if your data source includes multiple sheets with different structures, creating multiple mappers allows you to extract data effectively.

1. In existing Data Mapper, click on the arrow next to 'Create New'.
2. Choose one of the following options:
   To create a new Data Mapper for the file in the same table, click on 'Create new mapper for "Table Name"'.
   To duplicate the Data Mapper for the file in the same table, click on 'Duplicate current mapper to same table'.
   To duplicate the Data Mapper for the file in another table, click on 'Duplicate current mapper to another table'.
3. Clicking any of these options will open a new tab with the selection action. Make any necessary changes or create a new Data Mapper. 
4. Click on Publish & Scan. 
5. To verify that the new Data Mapper was added, click the arrow next to 'Create New'. All existing mappers will be listed there.
   

Apply Data Mapper from Library

Datarails offers a comprehensive library of pre-built Data Mapper templates for your convenience. When you apply a Data Mapper from the Library, it automatically includes a predefined list of Fields that you can utilize within your mapper. Additionally, you have the flexibility to add any additional Fields required to tailor the mapper to your specific needs.

1. In your current Data Mapper, click the arrow icon next to 'Create New'.
2. Select 'Choose mapper from mapper Library'.
3. Search for the relevant mapper in the list of saved templates.
4. Click 'Apply' to implement the selected mapper.
   

Updated October 08, 2024 09:50