Data Mapper: Mapping a new data source (Exercise 1 of 3)

In this exercise, we will guide you step-by-step through the process of using the Data Mapper to bring a new data source into Datarails and consolidating it with your existing data.

Common use cases:

Consolidated financials of multiple legal entities
A new P&L from a geographical expansion
Changing reporting needs due to restructuring or M&A

Try it now: mapping a new data source

Download the Excel file "Company Two Transactions" here. If the link opens in Google Sheets, go to File --> Download --> Microsoft Excel to download the Excel version of the file. It contains the financial transactions of a new (fictional) company.
Access your Datarails practice environment (click here for instructions)
Navigate to the Actuals folder and rename the filebox "GL" to "Company One" by using the three-dot menu.
Create a new filebox by clicking on the New+ button. Name it "Company Two", click on Upload File, and select the Excel file from Step 1. Then you can close the processing window.
Since the structure of the financials of Company Two is very similar to those of Company One, we can save time by applying the existing Data Mapper from Company One. Use the three-dot menu of the filebox "Company One" and select "Data Mapper".
In the lower left corner, you'll see a button called Publish & Scan Latest Version. Click on the white triangle symbol and select Publish & apply to
In the menu pop-up, select the triangle next to "Actuals" to expand the filebox list and select "Company Two".
Click Save to apply the Data Mapper of Company One to Company Two
By default, the two Data Mappers stay connected, meaning any changes you apply to the Company One Data Mapper get automatically applied to the Company Two Data Mapper. Let's break the connection between the two mappers. Repeat the steps outlined in points 6-8 and deselect "Company Two".
Now it's time to edit the Data Mapper of the new dataset. So, let's go back to the "Actuals" folder and open the Data Mapper of Company Two.
You'll notice that there is an error message. That's ok. The data structure (ie column names) of Company Two is slightly different than that of the company we copied the Data Mapper from. So, we need to make a few changes. On the left hand side it says "Header". Click on the trash icon to remove them. Then add them back as shown in the following video.
When you are done with adding back the headers, click Publish & Scan Latest Version to save your changes.

How do you check if the Data Mapper is complete?

Now, let's check if we are done. There are two ways of doing this. We can go to Dashboards and check if we can see the numbers for Company Two. In our case, we know that income for Company One is just under $ 1 million, while Company Two income is around $2 million. So, I'm expecting to see $3 million in total consolidated income. However, I don't see that in my Income dashboard:This indicates that the Data Mapper isn't complete. The other way of checking it is to see if the columns from the Chart of Accounts are populating correctly. Those are the green columns. Let's have a look in the Data Mapper of Company Two:
As you can see, the green columns are empty. In addition, we are missing some hierarchy columns that we have in the Chart of Accounts.
So, let's take a look at our Chart of Accounts, to remind ourselves what that looks like:
Notice that the first column in the CoA is called "Account #". In a way, you the Data Mapper works like an (incredibly advanced version of) the Excel formula Vlookup. We are using the account number as a unique identifier to map your transactional data to your Chart of Account. The green columns in the Data Mapper represent Chart of Account columns.
The column that shows the account numbers need to have the same name in the CoA as in the transactional data to get a match. Notice, that this is currently not the case:
Currently, The column with account numbers is called "Account #" in the CoA, and "Acct" in the Data Mapper of Company Two. Fortunately, it's easy to fix that. You don't need to change the column name in your data source (like your accounting system). You can make the change in Datarails.
In the Data Mapper for Company Two, on the left hand side under Header, find the Name "Acct" and change it to "Account #" to match the CoA. Then, click on Publish & Scan Latest Version to save. When you scroll to the right, you will notice that the green columns are filled out, meaning we successfully established a connection with the CoA.
Lastly, we need to tell Datarails that these transactions are related to Company Two. To achieve that, we create a Custom Column that tags each transaction with the name of the legal entity. At the left hand side of the screen, find the Custom Column section and scroll down to "Entity".
Hover over "Entity", click on the three dots and on Edit.
In the following menu, change the Formula from "CompanyOne" to "Company Two" and click on Ok.
Click Publish & Scan Latest Version to save.
Let's take a look at the Dashboards to make sure everything looks ok. Click on the Datarails logo in the top left corner and click on Dashboards.
You can see that the income is about $2 million higher than before, which is what we are expecting. To make sure, let's add a Global Filter for "Entity". Click on the pencil icon in the top right corner.
Click Manage Global Filter and Add.
Name the filter "Entity", select "Text" under Filter Type and drag and drop "Entity" into the Fields box
Click Save to get back to Dashboards.
Click on Filters at the top and select the "Entity" filter to change which transactions are being displayed. Notice that the "Company Two" values show as expected.
Congratulations, you successfully mapped your first data source!