Introduction
This exercise aims to help you understand how Datarails works by walking through a simple, end-to-end workflow. You will follow the data as it moves from inputs, through the Data Mapper, and finally to the outputs.
Data Inputs: Data is brought into the system through manual uploads or integrations. At this stage, the data is stored but not yet structured for reporting.
Data Mapping: The data is mapped into tables (databases), where it is structured, transformed, and enriched using custom fields and lookups.
Dashboard Readiness: Once mapped, the data becomes dashboard-ready and can be used to build widgets and dashboards.
Function & Report Creation: Functions connect the databased data to the Excel Add-in or Report Builder, enabling dynamic, formula-driven reporting.
By completing this exercise, you’ll gain a clear understanding of how the database flows within Datarails and how each stage contributes to the overall process. This foundational knowledge is essential for building more advanced databases and for effectively troubleshooting issues as they arise in the future.
Estimated completion time: 20–30 minutes
- Section 1: Inputs
- Exercise 1: Upload Inputs
- Section 2: Data Mapper
- Exercise 2: Data Mapper
- Section 3: Lookup Configuration
- Exercise 3: Lookup Configuration
- Exercise 3.1: Additional Custom Column
- Section 4: Creating Outputs
- Exercise 4: Creating Outputs
Important
For the purpose of this exercise, we recommend completing it in your practice environment (please register here to request access, it may take 1-2 business days), or in your actual environment but mapped to a separate table so it doesn't affect your implementation or current processes.
Instructions on how to map data in a separate table will be provided throughout the exercise.
Section 1: Inputs
We get the data in the system either by manual uploads or through an integration. In this exercise, we’ll be doing a manual upload. If the data is coming from an integration, your Datarails Integration Specialist will help you set that up.
Uploading or receiving data into the environment through an integration doesn’t mean the data is "databased”; it’s just stored in the system.
Exercise 1: Upload Inputs
For this exercise, you’ll create a new filebox and upload a file into your Workspace.
The file you’ll need to upload can be found here.
Video and step-by-step instructions
1. Navigate to Actuals Section
Click the Actuals folder to access the relevant data input area. In this exercise, we already have a filebox called "GL" within the Actuals folder. For more information about creating fileboxes check out this article on Filebox Overview.
2. Open Upload Interface
Click the Upload button to begin the process of submitting your input files.
3. Select the relevant period
Click on the month to specify the reporting period for your data upload. Select the appropriate month from the designated period for your upload. Click the Upload button to confirm and proceed with submitting your inputs.
4. Complete Upload Process
Click here to finalize the upload and ensure your data is successfully submitted.
Section 2: Data Mapper
Now that the data is stored in the environment, it’s time to get it into the database. In Datarails, we do that with the Data mapper. This process includes not only databasing the needed information, but also manipulating, cleaning, and transforming the data with custom fields/lookups.
For this exercise, you’ll need to map the headers and create two custom columns:
- Reporting Month: The goal of this custom column is to transform dates to end-of-month dates, so we can aggregate them monthly. Recommended formula = EOMONTH([Reference Date])
- Amount: Our data has two columns with amounts, one for debits and one for credits. We want to centralize the amounts into one column so we can use that field when creating outputs. Recommended formula = [Debit (USD)] - [Credit (USD)]
Exercise 2: Data Mapper
Video and step-by-step instructions
- Open Data mapper Feature
Click here to access the main menu where you can find the Data mapper feature.
2. Select Data mapper Option
Click "Data mapper" to open the data mapping interface within Datarails.
3. Choose a Table to map the data
Choose the appropriate table (database) from the dropdown menu to indicate where you are mapping the data. In this example, we select the "Financials" table. For the purpose of this exercise, we recommend choosing a table where you haven't mapped any other data yet, to avoid mixing data. To create a new table, click on "New +", give it a name, and then select it.
4. Confirm Selection
Click "Select" to confirm your chosen Table and Mapper Name for the mapping process.
5. Choose Flat Table Format
Click "Flat table" to specify the data structure format for your mapping.
6. Add Headers to Table
Click "+ Add headers" to begin adding column headers to your data table.
7. Complete Header Addition
Click "Done" to finalize the addition of headers to your data table.
8. Add Custom Column
Click "+ Add custom column" to create a new column tailored to your specific data needs.
9. Open Type Selection
Click "Type..." to enter the name of your custom column.
10. Initiate Function Search
Click "Search" to find functions that can be applied to your custom column. Choose the EOMONTH function.
11. Search for Reference Date
Click "Search" again to find the Reference Date field for your function.
12. Apply Custom Column Settings
In this example, we're creating a custom column to display the End-Of-Month date for the relevant period. Click "Apply" to save your custom column configuration with the chosen function. Your formula should look like this: EOMONTH([Reference Date]).
13. Publish and Scan Data Mapping
Click "Publish & scan" to finalize your data mapping and initiate a scan for validation.
14. Confirm Publish Action
Click "OK" to confirm and complete the publishing and scanning process of your data map.
Section 3: Lookup Configuration
In Datarails, lookups provide structure, logic, and hierarchy to the data, acting as data mapping files that enhance your main database by connecting it with additional fields, groupings, or categories from another data source.
For example, a General Ledger input file can connect to a Chart of Accounts lookup to add more data layers.
Similar to an Excel Lookup, there must be at least one common key in the Lookup file and in the database (in the files you already mapped). For example, for the General Ledger to connect to a CoA, our keys would be “Account ID” and “Currency” (or Account ID and Entity, etc.).
Important considerations:
- The name of the key must match the name of the key field in the Data mapper (eg, Account ID vs Account ID number, won't work)
- In the lookup file, the key(s) must be on the left, starting in cell A1. Eg, if my keys are “Account ID” and “Currency”, Account ID must be in cell A1 and Currency in cell B1. All the groupings, mappings, or hierarchies that we are trying to bring to the database would be to the right of those columns.
- The spelling of values in the lookup file vs the spelling of values in the database must also match for the system to find a match.
Exercise 3: Lookup Configuration
Files
For this exercise, we will create a Lookup filebox under the Configuration section in the left side panel, and set up the LUT configuration.
The file you’ll need to upload can be found here.
Video and step-by-step instructions
1. Access Configuration Menu
Click the Configuration menu to begin setting up your lookup file.
2. Open Lookups Section
Click the Lookups option to access lookup file management features.
3. Initiate New Lookup Creation
Click the New + button to start creating a new lookup file.
4. Select New Lookups Option
Click New Lookups to proceed with adding a new lookup file.
5. Enter Lookup File Name
Enter your lookup file name to identify it within the system.
6. Upload Lookup File
Click the upload area to drag and drop your lookup file or browse to select it from your device.
7. Create Lookup File
Click the Create button to finalize the creation of your lookup file.
8. Configure Source and Target Tables
Select the Target Table, Sheet Name and Key Length to configure your Lookup.
9. Save Lookup Configuration
Click the Save button to store your lookup file settings.
10. Open Data Mapper
Navigate back to the Workspace > Actuals folder and click the 3 dots next to the GL filebox. Click Data mapper to review how your lookup data maps to financial reports.
11. Review Data mapper
Notice a binocular icon will appear next to the key fields. Hover over it to see more details about the lookup.
12. Scroll until seeing green columns
Scroll through the Data mapper to see the green columns brought through the lookup configuration.
Exercise 3.1: Additional Custom Column
For this exercise, we will create an additional custom column using some of the fields we have added with the Lookup configuration.
The goal is to modify the “Amount” custom column to multiply by the field “Sign”. This way, we’ll have our amounts in absolute values in the database.
Video and step-by-step instructions
1. Click Custom Column 3 dots
Click here to access the custom column section where you can make edits.
2. Select Edit Option
Click "Edit" to begin modifying the selected custom column.
3. Edit the formula
Enter an asterisk (*) after the existing formula and add the [Sign] field right after it. We want to multiply the amounts by the Sign field.
4. Apply Changes
Your formula should look like this: [Debit (USD)] - [Credit (USD)] * [Sign]. Click "Apply" to save the changes made to the custom column settings.
5. Publish And Scan Column
Click "Publish & scan" to finalize and apply the custom column edits across the dataset.
Section 4: Creating Outputs
Once the data is mapped, it is dashboard-ready! You can now build as many widgets and dashboards as needed with this data. Refer to the dashboards training for more details.
In order to use the data to user reports, the next step is to create a function. A function links the data that we have on the database, with Datarails Formulas in Excel or using the Report builder in the Datarails environment. Once a function is created, you can build as many reports as needed.
Happy building!
Exercise 4: Creating Outputs
In this exercise, you’ll create a function and create your first output using the Report Builder.
Video and step-by-step instructions
Create a function
1. Open Excel Add-In
Click the Excel Add-in on the left side panel.
2. Access Functions Section
Click Functions to view and manage available functions.
3. Initiate New Function Creation
Click here to start creating a new function.
4. Enter Function Name
Enter your function name.
5. Select the table
Select the table from which you would like this function to retrieve information.
6. Open Search Field
Search and drag the relevant numeric field for example, 'Amount', to the Values section.
7. Default Aggregation Field
From the dropdown, select a default aggregation field (e.g., 'Reporting Month') to structure your data
8. Save the Function
Click Save to finalize and store your new function.
Create an output using the Report Builder
Video and step-by-step instructions
1. Open Report Builder
Click the Report Builder to start creating a new report.
2. Start New Report
Click New report to begin building your report. Enter your report name to identify it clearly.
3. Select the relevant Function
Select the dropdown menu and choose the appropriate function you wish to use (e.g., Value).
4. Open Search Field
Click the Search field to find specific data fields. Drag them into the relevant categories (Columns, Rows, Filters).
5. Add filters as needed
Click the filter icon next to each field to access the filter selection menu.
6. Filter by P&L items
In this example, we are filtering by Expenses and Revenue
7. Open Additional Options
Click the indicated area to access sorting options.
8. Open Sorting Options
Choose your preferred sorting method. In this exercise, switch it from "A to Z" to "Z to A" so Revenue shows on top of Expenses.
9. Calculated Values
Click on the calculator icon next to each field to create custom columns or rows
10. Enter Custom Row Name
Enter the name for your custom row to identify it clearly. In this exercise, we are using "Gross Profit".
11. Enter the formula
Click on the white box to start typing your formula. In this example we are using [Revenue]-[Expenses]
12. Select the position
Click Position to select where to add the custom row. In this exercise, we are choosing below "#Expenses".
13. Save Custom Row
Click Save to store your custom row changes.
14. Save Final Report
Click Save to finalize and save your complete report.
© Datarails Ltd. All rights reserved.
Updated
Comments
0 comments
Article is closed for comments.