Datarails FAQs

Overview

The SQL Connector is a syncing service, pushing data from DataRails application to any 3rd party database, such as the SQL database.

The SQL Connector is used to push data from Excel files mapped to DataRails into SQL database tables.

The data that will be available in these tables is determined according to the Excel file mapping.

Mapping the file is done using DataRails, its a simple process of choosing the relevant data to be later on exported and used.

File mapping is flexible and can be changed throughout time.

Installation instructions

Hardware Requirements:

DataRails SQL Connector Service should be installed on a server machine. It can be an existing server as long as the hardware requirements are met. It does NOT have to be the same server that hosts the SQL Server database.

Operating system: Windows Server 2012 or higher.

Hardware:

Recommended: 4 Processors, 16 GB RAM, 50GB Free disk space

Minimal: 2 Processors, 8 GB RAM, 30GB Free disk space

.NET Framework 4.6.1 version is required (a restart might be required)

The installer requires Admin privileges.

Firewall/Networking*:

104.25.189.22

104.25.188.22

An access to https://app.datarails.com via 443 port is required.

An access to https://static.datarails.com via 443 port is required.

If IP white labeling is required:

* if on-prem, needs access to the onpremise server

Installation:

Download the latest version from here and follow the instructions.

Server Configuration

From the system tray, right click on DataRails icon and select 'Settings': https://app.datarails.com/OData.svc

In the settings dialog, please fill the following settings:

Server Url: https://app.datarails.com/

Server Address:

Username: <username>

Password: <password>

In SQL Connection String field, please fill the relevant connection string for your SQL Server. Sample:

Data Source=.\SQLEXPRESS;Initial Catalog=syncTest;Integrated Security=SSPI;

Please note that the SYSTEM user of the machine that is running the service, must have a login in the SQL server.

Click 'Save'. Click 'Yes' on the 'Restart Service' dialog.

The sync should begin. In order to monitor the progress:

Open your SQL Server Console Management Studio

For each table shared with the SQL user in DataRails, you should see a matching table in the SQL Server.

DRUpdateTimes table is a control table that shows times and status of each table synced.

Extracting log files:

Right click the DataRails SQL Connector icon in the icon tray > Open Folder.

In the opened folder, go to logs sub-folder.

Tables Configuration

After mapping tables in DataRails, its required to share the Filebox with the mapped tables with the DataRails SQL user.

Only then, the tables in the DataRails database will flow to the local SQL database.

Updating version

Download the latest software.

There is NO need to uninstall the previous version.

The previous settings WILL BE saved.

Follow the installer instructions.

After the installation, the service will automatically start.

Start by downloading DataRails application and install it on your machine

Download link will be sent by your Customer Success Manager

Click on Sign Up

Enter your password use at least :

8 characters

One digit

One upper case

One lower case

One special character (~!@#$%^&*)

Files that are connected to DataRails instantly have an audit trail and version control. The versions can be compared to one another, tagged and named for identification purposes. The data from these files can also be extracted for dashboard visualizations, consolidation,and automation processes or used in external databases (e.g. SQL).

Connected files are tracked based on their location and naming convention.

DataRails keep monitoring files even if DataRails application is closed.

A connected file will have a DataRails icon on the lower left corner of the file.

Connect a Single File:

Right click on the file (from any folder or your desktop)

Select DataRails

Click on Connect

There are 2 connection options:

Connect a new file to the system (first version)

Connecting a file to DataRails will create a FileBox

A FileBox stores all of the files versions file history

Connectto an existing FileBox(as a new version of a connected file)

A new FileBox: Simple FileBox or Veriosn Tag FileBox

Simple FileBox

Name your FileBox

Click on Connect

FileBox with Veriosn Tags (recommended setting)

Adding version will enable easy access, filter capabilities and dashboard trend setup

Name your FileBox

Click on the button to Activate Date Tags

Select the time cadence/process period relevant for the file

Example: Monthly forecasting model assign a Month tag. Yearly budget process assign a Year tag.

Assign the tag for the current file (the one you are connecting)

Click on Connect

An Existing FileBox: add a version

A list with all of your FileBoxes will appear

Select the relevant FileBox from the list

Click on Select

Connect Multiple Files:

New FileBoxes will be created to each one of the files

Select multiple files in a folder

Open the right-click menu and click on "Connect"

Check the files you want to connect and hit "Connect"

Step 1 - OKTA side

Public app

Please use the Okta Administrator Dashboard to add the public DataRails application and view the values that are specific for your organization (use classic UI admin, go to the DataRails application-> Sign on -> view setup instructions).

Custom app

If you want to create a custom app and not use the public app, you can configure it with the following params:

Single Sign On URL

https://app.datarails.com/oktasso/?acs

Recipient URL

https://app.datarails.com/oktasso/?acs

Destination URL

https://app.datarails.com/oktasso/?acs

Audience Restriction

https://app.datarails.com/oktasso/metadata

Default Relay State

Name ID Format

EmailAddress

Response

Signed

Assertion Signature

Signed

Signature Algorithm

RSA_SHA256

Digest Algorithm

SHA256

Assertion Encryption

Unencrypted

SAML Single Logout

Disabled

authnContextClassRef

PasswordProtectedTransport

Honor Force Authentication

Yes

SAML Issuer ID

http://www.okta.com/${org.externalKey}

ATTRIBUTE STATEMENTS

Name Name Format Value

Email Unspecified user.email

Username Unspecified user.username

firstName Unspecified user.firstName

lastName Unspecified user.lastName

Step 2 - DataRails side

Login to your DataRails account as an administrator.Navigate to Admin (1) > Organizations (2), then on the right-hand side select your organization settings icon (3) and Edit SAML configuration (4). Then in the Dialog screen enter the following (see screenshot at the end of step for reference):

Name: okta

IDP SSO URL: Copy and paste the following:

Sign into the Okta Admin Dashboard to generate this variable.

IDP Issue (entityID): Copy and paste the following:

Sign into the Okta Admin Dashboard to generate this variable.

Certificate: Copy and paste the following:Sign into the Okta Admin Dashboard to generate this variable.

Click Save.

Admin screen

Dialog screen

Done!

___________________________________________________________________________________

Notes:

The following attributes are supported:

user.firstName

user.lastName

user.email

IdP-initiated flows and Just In Time (JIT) provisioning are supported.

SP-initiated flows are not supported.

New Tables features:

Duplicate Existing Mapper:You can now duplicate a mapper and edit it. Click on the 3 dots menu on the relevant mapper to do so.

Separatingtablefields- Mapped fieldsand System fields are separatedby a blank row and sorted by alphabetical order.

Delete table fields- Fields can now be deleted from a table using the trash bin icon.

New "FilebBox Name" field -will be automatically added when a table is used in more then one FileBox.

New FileBoxSettings:

Data Dump FileBoxsetting - A FileBox can be set as a Data Dump. This will change the FileBox icon in the workspace, there will be noaudit/changes analysis and no size limit on this file's worksheets.To turn on this setting go to: FileBox Settings > Check the Data Dump toggle as on.

Sheet Selection- sheets can be included or excluded from audit/changes analysis using the Sheet Selection option. Excluded sheets won'tappear in the Compare tool and the DataMapper; Included sheets will be the only ones to appear in the Compare tool and the DataMapper.To Include/Exclude type the sheet name and click on Add to Exclude/Include.

Other Added Features:

Compare tool: Dependency View- Select the number of dependency levels to view from a new drop down list. For example: Level 3 will show 3 dependencies connection.

Graph options: Y axis name- By default, the Y axis column label is based on the axis field name. To change this setting go to: Widget editor > Options > Y axis > Toggle off the "Auto Y-axis column label"

DataRails exposes the data extracted from Excel files in a standard called Open Data Protocol (OData). This document describes the syntax to access this data using a REST API.

The basic URL is always: https://app.datarails.com/api/templates/OData.svc

Calling this URL for the first time will require authentication using the same username (email) and password used to connect to DataRails. The available tables will be determined by the permissions of each specific user and according to the data that was shared with them.

The server response is always in JSON format.

The first response after authentication contains the list of tables available for the user. The table name is constructed from the FileBox name + the name of the extracted table + a numeric ID, such as testingodata_testing_7004. This is referred to as <table name> in the following paragraph.

Each basic table will appear in the list in four different variations:

<table name> returns the latest version including extracted data + special metadata columns added by DataRails

<table name> (Cleared) returns the latest version without the special metadata columns

<table name> (Full) returns data from all the versions submitted to the FileBox and designed as included in dashboard.

<table name> (Full)(Cleared) should be disregarded

Each variation of a table has a unique ID that can be used to query the data in the table.

The output may look like this:

<service xmlns="http://www.w3.org/2007/app" xmlns:atom="http://www.w3.org/2005/Atom" xml:base="https://app.datarails.com/api/templates/OData.svc/">

<workspace>

<atom:title>Default</atom:title>

<collection href="MGY4Y2MyZDItOWU2OC00OWM3LTg4MWItNmQ3NTQ4YmNkY2NjTXgkUDcwNg==">

<atom:title>Financial report_Consolidated pnl_5628</atom:title>

</collection>

<collection href="MWU3ZDlhODUtM2NmZi00OWZlLThlM2UtOWZhZTk1ODk2ZmMyTXgkUDcwNg==">

<atom:title>tiny_tiny_5721</atom:title>

</collection>

<collection href="MWU5Y2JhZWQtMjQ4ZC00NjcwLTgyMTUtNzk5ZjIzM2UwMGFmTXgkUDcwNg==">

<atom:title>short_short_5722</atom:title>

</collection>

<collection href="OWEyYjMzZmMtYWI1NS00YzRmLWFkMWMtYmU4MjVjMjU4NjdmTXgkUDcwNg==">

<atom:title>Monthly Reporting Package-1_YTD Financials_5949</atom:title>

</collection>

<collection href="N2NkOWM0YjYtZWJiNi00ODdiLWExOTktMDJiMTQyYWMzNTI1TXgkUDcwNg==">

<atom:title>Monthly Reporting Package-1_Bookings Monthly_5950</atom:title>

</collection>

<collection href="MDI0YWZhYmQtNWViOS00MmU4LWJhNzMtYTUwMDQ0ZDhkZTYwTXgkUDcwNg==">

<atom:title>Forecast_Revenue_6252</atom:title>

</collection>

<collection href="NWMyZjEwZmItODgxOS00MjMwLTlhZDItN2IxMGUwODhkMTk2TXgkUDcwNg==">

<atom:title>Companies Evaluations_Future Value_6475</atom:title>

</collection>

<collection href="NDRlNzg1MDgtMzRmZS00OTRiLTg1ZWEtM2JlMmNjZDRkMDVkTXgkUDcwNg==">

<atom:title>Companies Evaluations_Company Valuation_6474</atom:title>

</collection>

<collection href="MTk3NDExMjMtYWFmMS00NjQ3LTk0ZDAtODllNWU2NmIyOGNiTXgkUDcwNg==">

<atom:title>Budget_Budget Summary_6482</atom:title>

</collection>

The green colored value of the href property is the one used to query the data.

To see the list of all columns and their types, visit:

https://app.datarails.com/api/templates/OData.svc/$metadata

The response might be something such as:

<edmx:Edmx xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx" Version="1.0">

<edmx:DataServices xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" m:DataServiceVersion="2.0">

<Schema xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://schemas.microsoft.com/ado/2007/05/edm" Namespace="ODataDR">

<EntityType Name="Financial report_Consolidated pnl_5628">

<Property Name="Doc_ID" Type="Edm.String" Nullable="true"/>

<Property Name="Doc_version" Type="Edm.String" Nullable="true"/>

<Property Name="Tab_name" Type="Edm.String" Nullable="true"/>

<Property Name="Label" Type="Edm.String" Nullable="true"/>

<Property Name="Submission_Date" Type="Edm.String" Nullable="true"/>

<Property Name="User" Type="Edm.String" Nullable="true"/>

<Property Name="Category" Type="Edm.String" Nullable="true"/>

<Property Name="Item" Type="Edm.String" Nullable="true"/>

<Property Name="Periods" Type="Edm.String" Nullable="true"/>

<Property Name="value" Type="Edm.String" Nullable="true"/>

<Property Name="table_id" Type="Edm.String" Nullable="true"/>

</EntityType>

...

As can be seen in the example, all columns have the identical type of string. The reason is that the source of the data is Excel, which is not typed at all. In columns that should contain numbers or dates, values like Q2 or No Data or #REF are often seen. These values will not work in a numeric or a date type, and if declared as such, the query will fail.

Basic Queries

After deciding on the desired table, users can request its content by using a URI like:

https://app.datarails.com/api/templates/OData.svc/MTk3NDExMjMtYWFmMS00NjQ3LTk0ZDAtODllNWU2NmIyOGNiTXgkUDcwNg== ?

The value after the last slash (/) is the href of a specific table.

Building on top of this basic syntax, users can add parameters that will modify the result. For example,

https://app.datarails.com/api/templates/OData.svc/MTk3NDExMjMtYWFmMS00NjQ3LTk0ZDAtODllNWU2NmIyOGNiTXgkUDcwNg==?$top=10

will return the 10 first rows in the table;

https://app.datarails.com/api/templates/OData.svc/MTk3NDExMjMtYWFmMS00NjQ3LTk0ZDAtODllNWU2NmIyOGNiTXgkUDcwNg==?$skip=10

will skip the first 10 rows; and

https://app.datarails.com/api/templates/OData.svc/MTk3NDExMjMtYWFmMS00NjQ3LTk0ZDAtODllNWU2NmIyOGNiTXgkUDcwNg==?$skip=10&$top=10

will skip 10 rows and return the first 10 rows after skipping.

Selecting Columns

To view specific columns in the table, use the syntax:

https://app.datarails.com/api/templates/OData.svc/MTk3NDExMjMtYWFmMS00NjQ3LTk0ZDAtODllNWU2NmIyOGNiTXgkUDcwNg==?$select=Line_Item,Period,value

Filtering Data

Users can filter data with a syntax such as:

https://app.datarails.com/api/templates/OData.svc/MTk3NDExMjMtYWFmMS00NjQ3LTk0ZDAtODllNWU2NmIyOGNiTXgkUDcwNg==?$filter=Line_Item%20eq%20%27Admin%20Allocation%27%20and%20(Period%20eq%20%27APR%27%20or%20Period%20eq%20%27AUG%27)&$top=1000

The above query filters on two columns: Line_Item=Admin Allocation and (Period=APR or Period=AUG)

https://app.datarails.com/api/templates/OData.svc/MTk3NDExMjMtYWFmMS00NjQ3LTk0ZDAtODllNWU2NmIyOGNiTXgkUDcwNg==?$filter=substringof(%27Sales%27,Line_Item)

In the above case, the column Line_Item should contain the substring Sales.

Sorting the Results:

To request the data sorted by the Period column ascending, use the following URI:

https://app.datarails.com/api/templates/OData.svc/MTk3NDExMjMtYWFmMS00NjQ3LTk0ZDAtODllNWU2NmIyOGNiTXgkUDcwNg==?$select=Line_Item,Period,value&$orderby=Period

In addition,

https://app.datarails.com/api/templates/OData.svc/MTk3NDExMjMtYWFmMS00NjQ3LTk0ZDAtODllNWU2NmIyOGNiTXgkUDcwNg==?$select=Line_Item,Period,value&$orderby=Period%20desc

will return the rows on descending order of Period; and

https://app.datarails.com/api/templates/OData.svc/MTk3NDExMjMtYWFmMS00NjQ3LTk0ZDAtODllNWU2NmIyOGNiTXgkUDcwNg==?$select=Line_Item,Period,value&$orderby=Period%20desc,Line_Item

will return the data ordered by Period descending and then Line_Item ascending.

Special Characters in Column Names

The OData protocol doesnt allow for certain special characters to appear in column names. These special characters are replaced by other strings, and thus the columns can appear awkward.

Examples are:

- is replaced by _minus_

( is replaced by _obrak_

) is replaced by _cbrak_

To avoid these replacements, refrain from using special characters in column headers or simply use different names in the report.

Table of Contents

Linked map

Sync User - Set Sync Directories from Admin Panel

Admin Panel

Auto Synced Folders - Auto Connect by Regex

Compare tool - Change compared version

Linked map:

DataRails linked map provides a holistic view of all files and enables to understand how they are all connected together.

A great way to check for links between worksheets is by using DataRals Linked map.

Admin users can go to Admin Panel > Organization > 3 dots menu > Linked map. DataRails will quickly build a diagram that shows how workbooks are linked to each other and to external sources.

In the diagram, you can move the mouse pointer over any node to see a workbook's FileBox and its last modified date.

Sync User - Set Sync Directories from Admin Panel

Admin users can set synced folders from the Devices tab in Admin Panel. This will help admin users to manage the synced folders configuration without IT involvement. Go to Admin Panel > Devices > Search for Sync User > Click on the 3 dots > Directories

Add folders that you wish to sync. The folders must be in UNC format. (E.g. \\servername\path) and MUST have read/write access by the Windows user that is running the service.

SubFolders: When set to true, the Sync service will sync the folder and all its subfolders. Default: true

AutoSync: Syncs automatically every Excel file located in the folder. If set to false, the Sync Service will sync only the Excel files chosen by Client App. Default: false

Include: An option to sync only a specific folder in path. Default: empty

Exclude: An option to exclude a specific folder in path from syncing. Default: empty

Status: online - folders are accessible, offline - folders are not accessible, review the permissions settings.

Delete - remove row

Check Folder - option to test a single directory permission settings

Save - apply changes

Admin Panel

The navigation bar has been enhanced for easier access and improved user experience. The new navigation bar is now located on the left side and it can be minimized by clicking on the right navigation border.

Device menu - Admin users can now send API requests and Remote commands. This helps Admin users to improve users experience without interfering in their ongoing work. Commands list:

Clear Cache

Delete local DataBase

Receive Logs

ReSync Locations

Turn On Debug log

Turn Off Debug log

Send API Request - App Pooling

Send API Request - Service Pooling

New refresh button in all Admin Panel tabs - . Use it to refresh Admin Panel lists (Organizations, users, files etc.)

Auto Synced Folders - Auto Connect by Regex

The auto connector is used to convert synced files to business process. The automatic connector allows users to convert multiple files to one business process in only one systematic configuration. To set an auto connector:

Go to FileBox Details > Setting tab > Set Auto Connector

Or go to Admin Panel > FileBoxes > 3 dots menu > Set Auto Connector

Paste your auto synced folder path and add a file name

Replace all dates with period buttons (Year, Quarter, Month, Week, Day). Highlight the date in the path box and click on the relevant period button. For example: \\servername\Process\2019\Q3\01\Test20190101.xlsm Should be - \\servername\Process\<Year>\Q<Quarter>\<Month>\Test<Year><Day><Month>.xlsm

Add list - In case you have a variation in one of the paths words, you can create a list of acceptable values. For example: Test, Tests, Testing.

Test - will search and provide a list of matching files found based on the configuration at current path

Generate - creates a list of predicted match results based on configuration

Predicted path list

Predicted dimension list

Check the box to apply this configuration on existing files

Save to apply changes

*To set it right on the first time please contact support for a quick training.

Compare tool - Change compared version

New compare capability - change the previous version within the compare tool. This allows users to better analyze the differences between versions without re-compare different versions.

Click on the rows Icon

Change the previous version (the current version stays the same)

Review the different changes

Mapping of data from unstructured to structured tables is done via the Data Mapping tool. The tool contains instructions on how to map the important data of an Excel file to be extracted. The data is mapped into structured database tables that can be used to visualize the data (Insights), track changes, consolidate or access from other platforms (Power Query, SQL etc.).

To open the data mapper, click on the more menu icon link (by hovering over the right-hand side of the FileBox) and select Data Mapper.

The mapping is based on the latest file version of a FileBox.

To use a specific version of a FileBox, go to the version tab of the FileBox (click on the FileBox name or chooseFileBox Details) and open the Data Mapper from the more menu of the version you wish to use.

Data Mapper Tool

First, name the table.

If you wish to modify an existing table, you can access it by clicking on the drop-down menu next to New Table.

Mapping options-Flexible / Fixed StructuresOnce you name the table you will see the following mapping options:

Flexible Structure Header names will follow an ordered sequence but might not be in the same column as before (New columns may be added in between the existing columns).

Fixed Structure - Header names will be in the same order throughout versions. This type of structure has a shorter processing time.

You may choose which sheet or sheets (Excel tabs) to map. This option allows you to specify the relevant sheets to map and save processing time by scanning the relevant ones only.

Click on Add Structure to map the area in which our relevant information resides (rows/columns/cells).

Structure Role TypesThe following list specifies the types of roles you can assign to a cell/range in a worksheet:

Header define the selected cells as the tables headers. The selected cells will be the column headers of the mapped table, and the data that resides below will populate the values in that column.

Attribute add properties that will apply to the entire table. The value of an attribute is extracted from a cell to the right of the selected attribute cell.

Dimension Group the headers in the selected cells together, as a time dimension for example. The mapped table will have a column with the group name as a header and each one of the selected cells will be a value in that column. It would also generate a Value column with the values for each one of the dimensions cell values.

For example: Time dimension can be Period. We will select the headers of columns with monthly data (Jan, Feb etc.) to classify all the values for these dates under the Period dimension.

Footer Select a cell to define the end of the table to be scanned. Define where to stop scanning for additional data, exclude data that resides below the mapped table but should be excluded from the extracted data. The selected cell and any data below it will not be included in the mapped table.

You do not have to include all cell types when mapping, you can use only one or several relevant roles together.Make sure to click the Save button to save the table structure for the file.

Click here to see data mapping example, using the different role types

There are advanced mapping options in the tool - Match Type, "Data Manipulation" and "Data Filters". We will discuss them in the Advanced Data Mapper articles, found in the Data Mapper section of the Knowledge Base - .

New file versions will be scanned automatically by the table that was created. To get the data from the file versions that are already uploaded to DataRails FileBox, you should rescan them.

To do so, open the Workspace and open the FileBox details.

Select the file versions you would like to process and database by checking the checkbox, then click on the rescanicon.

A pop-up table with the optional tables to scan will open, choose the desired tables and click on Rescan.

A green checkmark will be seen to the right, indicating that the tables were extracted successfully from that version.

If an error icon appears instead, it means that the system could not extract some or all of the tables (respectfully) from that version. Clicking on the icons will prompt a pop-up window stating the issue at hand (Status). You can click on the tables name to get additional information regarding the cells that were not found. Make sure you are looking for the errors under the relevant sheet of the Excel workbook.

New versions that will be uploaded to the FileBox will be scanned automatically and their data will appear in the table you mapped.

You will need to clear the cache upon receiving an OData error specifying a number as a table id (there is a new id format - UUID)

In the excel file click on: new query/Get Data query options clear cache

Within FileBox Details, you can view all previous versions of the file in detail: who created the version, when, see labels and tags, filter & download any one of the versions.

Right-click on the file, select "Version History"in DataRails menu orclick on the FileBox name from the Workspace.

click here

Versions Tab features:

Version details

Search versions

Filter versions

Collapse view by tags

Upload a new version

FileBox information

Compare selected versions

1. Version details:

In this section, you can see the version number you upload/submitted to this FileBox (first column), the original excel file name (second column), submission time date and the user that submitted the file (third column), the label you submitted(fourth column), Diff symbol that indicatesif a change had happenedbetween the versions.

2. Search file versions

You caneasily look for a certain version of your file, click on the magnifying glass and enter your search criteria (note that you can type in a word in the name or simply a few letters and the search function will locate anything with that word or letters).You can search by user, filename, date or label.

3. Filter versions

You caneasily filter the versions list by clicking on the filter icon. After clicking on the filter icon you will see the Versions filter (as shown below).

You cansearch by version properties, submission date, user, tags (date tag or file status) to locate your file faster. To apply the filter click on "Apply", to cancel the filtered view click on "Clear"below.

4.Collapse view by tags

For FileBoxes that were set with tags, you can change the view by the date tags (e.g. month). Click on any of the blue arrows to see the versions of the collapsed period.

5.Upload a new version

Click on the upload button on the right-hand sidetoopen the upload pop-up window. For more information about "Upload a new file to FileBox" .

6.FileBox Information

You can see the following details: owner, created & updated date, file names, synced paths, connected by, date & have an option to disconnect the versions.

7. Compare selected version

DataRails allows you to know exactlywhat has been changed between different versions of your Excel file. To learn more about DataRails compare capabilities click here.

DataRails can be installed on Windows 7 and higher.

Other operating systems are not supported currently.

DataRails Dashboard environment using an assortment of graph types, from the most commonly used column, pie and line charts to spider chart and gauge. Each graph highlights specific aspects and assists in driving meaningful insights from the collected data.

The main capabilities of the Dashboard:

Visualize and Analyze the data extracted from the files

Roll Up

Drill Down

Slice & Dice

Share by Permission

Updated Version History ViewThe new default view for a FileBox with time dimensions is aggregated view by date, with the newest on top.This will help in managing large amounts of versions and navigating between the different time dimensions.

Auto Archive VersionsTo help manage large amounts of versions (using the DataRails sync application, every save creates a new version in the system) we added the Auto Archive Versions capability. Using this capability we reduced the number of visualized versions, and only meaningful versions will appear in the Version History tab. (You can always add all archived versions see the next section.)

It includes a label

It has a status tag (Final, Signed, etc.)

It was modified by a different user

It has a gap of more than 2 hours from current

It has an alert.

When a new version is submitted by the sync service, the previous one (in the same dimension) is archived, unless:

Archived versions will be marked in grey.

To un-archive an archived version, click on the three dots and select Unarchive.

To see all archived versions, click on the filter icon and check Show archived versions and click apply.

Diff Icon Behavior Improvement

From now on, if a FileBox contains a time dimension the comparations will be based on a logical date order and not on a chronological version order. (For example, version 1 tagged as May 2019 and version 2 tagged as April 2019, old behavior: version 1 is the Previous version, while version 2 is the Latest version. New behavior, the order is reversed: version 2 is the Previous and version 1 is the Latest.)

Compare Tool ImprovementsWe improved DataRails compare tool capabilities to help users better track and analyze changes between versions. We focused on improving the dependency view. Now, users can better understand where and why changes were made and how they impact other cells.

Cell Audit Trail List New export to Excel button

Audit trail will include the following points: Current version, versions in which the audited value changed (also if archived), labeled versions (unless archived), and versions in which the audited value carries a comment.

Dependency tree Expand all button

New tool-tip for changes in tree and graph mode with the following information: Current value, Previous value, Delta, Formula.

Dependency mode navigation improvement: A click on a cell location in graph or tree view will automatically direct to the cell in the preview section.

Dependency mode: New full-screen view button

On a cell selection, the active row and column will be highlighted in grey.

New Feature: Add Password for a Specific VersionUntil now, users could provide only one password for a FileBox. We added the ability to add a different password for every connected file to FileBox. (E.g., FileBox can contain a version for January with one password and another version for February with another password.)There are two ways to enter a file password to DataRails:

Click on the i icon

Click on the key button to add the password

FileBox level FileBox details > settings tab > provide the password in the FileBox Password box

Version level provide passwords for every connected file. (E.g., two Excel files connected to the same FileBox with different time dimension and different password.)

New Feature - Business Process for synced folder filesWhen using auto-connect functionality, all files are automatically connected to DataRails as separate processes. A Business Process allows to aggregate all spreadsheets from different time dimensions (e.g. different months, quarters etc) under one roof as well as the ability to define the final version of each time dimension.

Convert file to a Business Process

Only Files that are synced to DataRails via Sync Folder can be converted to Business process.

This process will create a business process and convert the individual file selected into a file with a dimension and status tag within the business process.

Right click on a file > Convert to Business Process

Fill the Business Process name and add time dimensions

Connect file to existing Business Process

This process will convert the individual file selected into a file with a dimension and status tag within an existing business process.

Right click on a file > Convert to Business Process

Select connect to existing Business Process

Select the relevant Business Process from the list

Feature update - AlertsUntil today, it was possible to add alerts on one cell at a time. From now, the same alert condition can be applied on a range of cells.

In the compare tool select a range of cells

Right click on one of the cells

Select Add Alert from the list

Admin Panel - updated tabs:

Files Tab-

This tab shows all files connected to DataRails (with or without a business process) with additional meta-data such as:Id, Organization, Path, Filename, FileBox (only if it is connected to a business process), Dimension (if applicable), Connect Time, Age (the days from creating the file), Update Time (last save upload to DataRails), Connected By, Macro Change Time, Formula Change Time, Users (number of unique users saving the file after creating the connection to DataRails), File size, Cells With Formulas, Macro Modules, # Sheets, # Hidden Sheets, Cells With Errors, External Links, Named Ranges, Tables Extracted, Password Protected, Part of Collection, Error, Auto Sync, Archived

The Grid Icon which allows to customize which columns to display. You may select to show specific or all fields mentioned above.

The Filter Icon allows to filter out archived files. An archived file is a file where the physical file was deleted.

Locations Tab- This tab contains the information for a synced location:Organization, Machine Name, Full Path, Number of synced files, Auto Synced (a green check mark symbolizing Auto Synced location), Is Archived (deleted synced locations)

Feature Update: Compare ViewFor deleted cells that are not a part of structural changes (i.e. delete rows and cols), the deleted cells will appear on the current sheet view in addition to the previous sheet view (until now they were presented only on the previous sheet).

Under the DataRails blue panel along the top of the web page, you will see an encircled plus.

When you hover your mouse over it, you will see an add dashboard instruction.

Click this symbol.

A popup will load and ask you to name your dashboard.

Type the name of the dashboard, and click Add Dashboard:

The Dashboard will appear as a tab, and you can add as many as you like for the user.

Once you click on your named dashboard, you will see the canvas below with the text There are no charts in dashboard.

DataRails allows you to know exactlywhat has been changed between different versions of your Excel file.

This article includes four sections:

Using the Compare Tool

The Compare Tool Symbols

Dependent Changes

Single Cell Changes

Add Comments

Add Alert

Add KPI to Insights

Using the Compare Tool

1. Click on the FileBox you want to compare. In the version tab choose any two versions of your file you wish to review by checking their checkboxes and clickCompare.

2. The Compare tool will open and commence logical comparison between the two versions.Loading time will differ between files sizes and the modifications complexity.

Understanding the Compare tool screen:

1. Switch between the tabs to quickly review the different file versions.

2. The sidebar lists the changes made to your file. You can toggle between changes made to the current sheet or all sheets, and between the changes type preview manual changes only or function results changes as well.

3. The Excel Viewer shows the changes made to each individual cell. Hovering over a cell that has been changed will pop up an informative message in regards to that change.

4. Review the changes in the different sheets of your file.

The Compare Tool Symbols

Dependent Changes

DataRails Compare tool gives you the ability to audit your spreadsheet and analyze all data-flows.

You can easilyanalyze whether a change in one cell affected other cells or alternatively if a change in a cell was derived from a change in another cell.

1.On the Excel viewer of the Compare Tool, right-click on any cell whose change you wish to trace and choose the desired action - you can view eitherChanges that affected this cellorChanges affected by this cell.

2. A dependency view will appear to the right, tracing back all the changes that have led to the variation in that specific cell. The linked cells will be marked according to the level of dependence (a cell with a direct link to the change will be darker than one with an indirect link).

3. In the Dependency View panel, ChangeView typetoGraphin order to visualize the links between the cells ofthe same worksheet whose values have changed. You can also chooseAll file dependenciesto view the data links to and fromthe cell of interestthroughout the entire workbook.

Single Cell Changes

DataRails allows you to see the history of all changes made to a specific cell, right from the point you first connected your file to DataRails. You can also trace how a change in a specific cell affected other cells or how a specific cell was affected by changes in other cells.

View cell history:

1. On the Excel viewer of theCompare Tool, right-click on any cell its history you wish to view and chooseShow cell history.

2. A graph will pop up, tracking all modifications made to that specific cell. You can choose between different graph types. Hovering over the values in the graph willprovide you withfurtherinformation.

Add Comments

You can add comments to cells on the compare tool. When a cell has a comment, an indicator appears in the corner of the cell. When you rest the pointer on the cell, the comment appears.

Use the compare tool

Move the cell pointer to or click the cell to which you want to add the comment.

Right click on a cell and select Insert Comment

A new text box appears

Type the text of your comment in the text box that appears and click save

DataRails marks the location of a comment in a cell by adding a tiny triangle in the lower-left corner of the cell.(This triangular indicator appears in blue)

To display the comment in a cell, position the mouse somewhere in the cell with the note indicator.

To see all comments by versions right click oncellwith the note indicator, select audit trail, change to list view.

Add Alert

Add Alert is an automatic alert mechanism that tracks changes made to specific cells. You can add as many rules as you like and DataRails will keep a track of them. Once the spreadsheet is changed and the values/text/formulas reach the notification rules you have set, you will automatically receive an email to inform you. You can also add multiple email addresses to one notification rule.

Use the compare tool

Move the cell pointer to or click the cell to which you want to add watcher.

Select "Add Watcher" from the drop down list

A new window will pop up

Name the Watcher. Select the value type; Set the conditions; Type relevant emails in the Notify to; Select status; Add

The new Watcher will be listed in Watcher tab locatedinthe FileBox details screen.

Add KPI to Insights

New feature: Sync FoldersFrom now it is possible to connect a folder to DataRails.This means, all spreadsheets (existing and any new spreadsheet) within the folder and subfolders would automatically be connected to DataRails.

New feature: Auto delta in DashboardsAutomatic calculation of changes between TWO selected versions in the dashboard.

Select two versions that you want to compare

Click on Options > Delta > Select field:

Axis - Calculate the delta between columns

Legend - Calculate the delta between rows

You can change the header name (_Delta) and formula if needed

New Excel file type supported in DataRails:

xls (only with sync service and sync application NOT with Badge/Widget)

Improvement - Change time dimension from the Excel Add-in

Improvement - Split feature

File can be split based on two criterias (AND argument) I.e: split data based on department name AND location

Improvement: Rescan-All will run only on "include in dashboard" versions

Renaming: Watcher feature will be named Alert

OData is a connector between DataRails' database and Excel, you may use it in order to get data from different Fileboxesinto Excel.

Using OData for the first time will require a minute to set up, after that you may access OData by clicking on "Recent Sources" in the Data tab of Excel.

OData URL: https://app.datarails.com/api/templates/OData.svc

Setting up OData for the first time:

In Excel, open the Data tab or the Power Query tab (depends on the Excel version you are using)

Click on "Get Data" or "New Query" icon -> Choose "From other sources" -> Choose "From OData Feed"

A pop-up window will require the URL (below) - copy it and paste it into the URL box (shownbelow) -> click "OK"

URL: https://app.datarails.io/api/templates/OData.svc

You are ready to go! However, if an error message pops up, follow the troubleshooting instruction below.

Make sure to check out the next articles on using OData.

Troubleshooting:

If you received an error message when logging in, you may need to add your Datarails credentials to Excel.

There are 2 ways of doing this:

Quick Access (pop up window will automatically appear when getting the error message)

Click on the "Basic" tab on the left, fill in your DataRailscredentials (the user-name and password used to log intoyourDatarails account) and then click Save.

2. Accessing the Credentials from Excel

Go to the "Data" tab in Excel -> Click on "Get Data" or "New Query" icon -> Click on "Data Source Settings"

Click on "Edit Permissions"

Click on "Edit"

Click on the "Basic" tab on the left and fill in your DataRailscredentials (the user-name and password used to log intoyourDatarails account) and then click Save.

Log in

to use Ginger

Limited mode

A="troubleshooting">Troubleshooting

Hi all,

New version is out. Please see below the following updates and features:

Password protected files- To prevent access of unwanted users to password protectedfiles data in DataRails, the users have to provide the file password when using the Compare or DataMapper tools. The password will be saved only for several hours after which the user would be requested to enter the password again.

Sync app menu update - New option - "Show latest changes" opens the compare tool window of the current version vs. previous version changes.

Versions tab update - If a version cannot be processed it will grayed out and a red triangle icon will appear on the right corner. Hovering over the red triangle will reveal a tooltip explaining the issue. Grayed out versions can be downloaded, however the Compare and Datamapper tools will not work.

Newdifficons(Reminder: in thecomparetool, you can click on the question mark foriconslegend)

Audit trail (show history) - improvements-

The current version ishighlighted in the list and graph view.This is helpful when comparing between two versions that are not the latest version.

NewKanbanicon-By default, the cell audit trail is ordered by "submission date". The Kanban Icon allows to:

Group by time dimension inthe list view

Order the by Date Tag and then by submission date in the graph view.

NewFilter icon- filter the list and graphviewsby Date Tag,version status, submission date and user.

New capabilitiesin the versions tab and admin panel tabs-

Group by column

Hide column

Admin Panel - pagination in the users and FileBoxes tabs

New feature- Set Trusted IP Ranges for an OrganizationTrusted IP Ranges define a list of IP addresses from which users can log in.To help protect your organizations data from unauthorized access, you can specify a list of IP addresses from which users can log in. Click here

Click here for the knowledge base article

New feature- DR CommentsYou can add comments to cells on the compare tool. When a cell has a comment, an indicator appears in the corner of the cell. When you rest the pointer on the cell, the comment appears. DataRailsmarks the location of a comment in a cell by adding a tiny triangle in the lower-left corner of the cell.(This triangular indicator appears in blue)

Click here for the knowledge base article

New feature- Add KPI to InsightsKey performance indicators (KPIs) are visual measures of performance. Based on a specific cell, a KPI is designed to quickly evaluate the current value and status of a metric.Add KPI to Insight will create a new tab in DataRails Insight with the cell value.

Click here for the knowledge base article.

New feature- Add WatcherAdd Watcher is an automatic alert mechanism that tracks changes made to specific cells. You can add as many rules as you like and DataRails will keep a track of them. Once the spreadsheet is changed and the values/text/formulas reach the notification rules you have set, you will automatically receive an email to inform you. You can also add multiple email addresses to one notification rule.

A new Watcher Tab is now on the FileBox details screen.

Ifa watcher is triggered - you can see the alert in the version history tab

for the knowledge base article

Trusted IP Ranges define a list of IP addresses from which users can log in.To help protect your organizationsdata from unauthorized access, you can specify a list of IP addresses from which users can log in.

Go to Admin Panel and select the Organizations tab

Hover over your organization and select "Change IP Whitelist"

Add theIP addressesfrom which users can log in, insert each IP address in anew rows and click save

You can retrieve any historical version of a connected file by downloading it.

File versions that were archived or auto submit versions that automatically collapse can also be accessed, contact your Customer Success Manager for more information.

Start by opening the file's version history ( click herefor more info ), hover over the version you would like to access. Click on the 3 dots menu and select Download.

The version will be downloaded to your local machine (usually the downloads folder, according to the machine's settings). It will NOT replace the latest version of the file.

The Collaboration tab has two sections:

Team section - on the left side

Feed section - on the right side

Team section

In this section, you will see a list of owners and other contributors/viewers and you can invite new team members, edit permissions and delete collaborators.

Permission Roles

DataRails offers a number of predefined permission roles that reflect typical use-cases for DataRails users:

Owner- has full permissions to edit, delete, and share the Filebox.

Contributor- has permission to upload versions to the Filebox.

FileBox Viewer -has permission to view versions.

Filebox Dashboard Viewer -only hasaccessto the Dashboard and its visualizations. The viewer will not be able to see the Filebox in the Workspace.

Each Filebox may have multiple users for each one of the roles (e.i a Filebox can have 3 owners and 1 viewer)

Edit Permissions

You mayEDITFileBox permissions once they have been assigned:

Go to the collaboration tab

Hover your mouse over the user you would like to edit

Click on the edit (pen) icon

Select the new role from the drop-down list

Alternatively, you can delete this user entirely by clicking the trash bin icon instead of edit.

Leave a Shared FileBox

Are you receiving notifications from shared FileBoxes you are no longer contributing to? Do you no longer wish to see a FileBox on yourWorkspace? No problem!

You can easily leave any FileBox by following these two steps:

1.Go to DataRails Workspace and move the cursor over the FileBox that you want to leave. Click on the three dots to open the drop-down menu, and choose "Leave FileBox".

2. Inthe dialog box, clickLeave.

Custom Permission Roles

DataRails offers a number ofpredefined permission rolesthat reflect typical use-cases forDataRails users. You can either use these roles as-is or define your own roles and assign them to users in your organization. This allows you to customize permission types that best suit your own organizational structure and workflow.

For instance, you may find the predefined"Contributor" role suitable for your organization's needs but would not like "contributors" to have the ability to take control over a file that is open on another computer. No problem! we can set a new role without the unwanted function.

At this stage,customized permission roles are determined by you and managed by our support team.

For more information please contact us at [email protected]

The Workspace is a management console used to create, organize and track FileBoxes and Processes within DataRails.

You can access the Workspace from the Chrome browser at app.datarails.com or by clicking on the Workspace tab at the top of the version history view (right-click feature).

If you are using a privet cloud/on-prem installation ask your IT or CSM for the web address

Collection Process link

From the Workspace area, you can open folders or FileBoxes by clicking on their name. In order to go back to the main area click on My FileBoxes on the top left corner:

You can create new FileBoxes, folders and collection processes from the Workspace, by clicking on the "new" button. For more information on these topics, click on the links below:

New FileBox and Folders link

What Are Submission Tags?

Setting up Submission Tags

Changing or Applying Submission Tags to Existing FileBoxes

What Are Submission Tags?

When activated, submission tags ensure that every version saved to DataRails will contain information regarding its Status and/or Date.

The Date Tags have five options: Day, Week, Month, Quarter and Year

Choose the Date type based on process cycle. For example, if your financial closing is performed on a monthly basis, you should select the "Month" Date Tag.

The Status Tags have two options: A File Status and a Workflow Status.

With the File Status, there is two type of tags that can be applied to a saved version; Draft or Final.

Any user can apply a Draft tag to a saved version, while only FileBox owners can save a version with a Final tag.

Once a file has been saved with a Final tag, only owners can submit another Final version. Alternatively, they can choose to return the file to Draft. If an owner saves a new version with a Final tag selected and the previous version was already tagged as Final, the tag of the previous version would change to Draft and the new version would be saved and assigned the Final tag.

Note that if Date Tags have also been activated, every Date group could have a Final Tag. For example, if a Month tag has been activated with a File Status, every month could have one Final version.

With the Workflow status, an In progress tag is automatically assigned to any version saved until otherwise specified.

Users that are not FileBox owners can request approval for their saved version by clicking the Submit for approval button. This will apply a pending tag to the version and save it.

Users that are FileBox owners can either approve or reject versions by clicking the approve or reject buttons respectively. This will apply an approved or rejected tag to the version and save it.

Once a file has been saved with an Approved tag, users that are not FileBox owners cannot save or submit any additional versions. Owners can decide to return the file back to working mode by clicking on the Cancel Approval button. This will change the tag on the version from Approved to Rejected.

As with File Status, if Date Tags have also been activated, every Date group could have an Approved Tag. For example, if a Month tag has been activated with a Workflow status, every month could have one Approved version.

Setting up Submission Tags

Submission tags may be assigned when setting up a Filebox; these are Workflow, File Status, and Date:

Workflow - sets up an Approval Process ( click here for more information)

To allow a Filebox owner to review and approve different versions of files as they are submitted by contributors.

File Status - sets up an indication of draft and finalfor every version of a file submitted to DataRails (to a Filebox)

Date - sets up an indication of the time period for the versions

The benefits of assigning submission tags include:

Additional layer of information on top of the Excel version

Allows for easyfiltering and searching capabilities

Visualize and compare the data based on the different dimensions assigned

Additional information about Submission Tags:

Submission tags may be enabled for a new Filebox or for a Filebox that has less than 2 versions uploaded to it.

Submission tagscannotbe applied or changed retroactively in order to maintain the same file logic throughout all versions of a Filebox.

After submission tags are applied, a user can filter and search by status (i.e. draft, approved, final, etc.)

How to assign the submission tags:

Open the Filebox details

Click on the Filebox name, or click on the 3 dots to the right and choose "Filebox Details" from the drop-downmenu click here

A screen with your versions will load

Click the "Setting" tab, and click on the switch to turn "on" your preferred tags

You may choose oneof the two status tags - Workflow tag or File Status tag as they are mutually exclusive. The File Status tags (draft/final) are available as part of the Workflow tags.

Click SaveNote:When adding the date submission tag, you will be prompted for the period interval: Year, Quarter, Month, Week, or Day. This tag should be selected based on the intervals that your data is used for. For example, if your analysis is on a monthly basis, you should select the "Month" option.

Changing or Applying Submission Tags to Existing FileBoxes

The submission tags may be enabled for a new Filebox or for a Filebox that has less than 2 versions uploaded to it. For set-up information

Submission tagscannotbe applied or changed retroactively in order to maintain the same file logic throughout all versions of a Filebox. The submission tag buttons will be inactive andgreyed out after two versions have been uploaded a Filebox.

Since the historical versions of files in a Filebox cannot be tagged once they have been uploaded to DataRails, any filtering and comparing capabilities will be disabled.

Filter the changes presented by the following fields:

Sheet (Excel tab) make sure to select all sheets to enable this option

Change Type e.g. value increase, formula change

Cell search for a specific cell to identify changes

Values search for a specific value within the current version / previous version / delta (difference between the 2 versions)

New expand/collapse option, to show only rows that had changed

View changes of specific ranges, columns and rows. Click on a cell and drag to select a range in the spreadsheet to present its changes list on the right.

You may select any 2 version to view in the comparison tool.

Open the file's version history ( click herefor more info ), you can select 2 consecutive versions or 2 non-consecutive versions.

Compare 2 consecutive versions:

Click on the icon of the version you would like to compare to its previous version

Compare 2 non-consecutive versions:

Select the 2 version you want to compare, by checking their checkbox on the left side

Click on the compare button at the bottom right side of the screen

Version names were created to quickly locate a version (search or filter to named versions from the version history) and to communicate changes of a version.

You can name a version from the version history view, the right-click DataRails menu or from the Excel add-in.

Name a version from:

Version history window - hover over a version, click on theicon and name the version in the pop-up window.

Right-click DataRails menu - open the file explorer, right-click on a connected file, select "Name this version..." in DataRails menu, name the version in the pop-up window.

ExcelAdd-in - opena connected Excel file, right-click on any cell, select "Name Version..." in DataRails menu, name the version in the pop-up window.

Changing the file version's tag, for FileBoxes that were created with tags such as date and status tags (for more info click here)

You can change tags from the version history view or from the right-click DataRails menu.

Change the version's tag from:

Version history window- hover over a version, click on themenu and select "Version Tags". The tag cadence options that appear are according to the FileBox tag setup (e.g. monthly/quarterly/yearly tags). Select the relevant time tag or change the file status and click on "Change".

Right-click DataRails menu- open the file explorer, right-click on a connected file, select "Time dimension..." in DataRails menu. A pop-up window will open, select the relevant time dimension and click on "Save".

Files that are connected to DataRails instantly have an audit trail and version control. The versions can be compared to one another, tagged and named for identification purposes. The data from these files can also be extracted for dashboard visualizations, consolidation,and automation processes or used in external databases (e.g. SQL).

Connected files are tracked based on their location and naming convention.

DataRails keep monitoring files even if DataRails application is closed.

A connected file will have a DataRails icon on the button left corner of the file.

Connect a Single File:

Once DataRails application is installed:

Right click on a file

Select DataRails

Click on Connect

There are 2 connection options:

Connect to an existing FileBox - Connecting a file as a new version (of a connected file, to an existing FileBox)

Connect - Connect a new file to the system (first version)

Connecting a file to DataRails will create a FileBox in the system(stores all of the files versions file history).

Activate Date Tags -

Date Tags ensure that every version saved to DataRails will contain information regarding its Status and Date.

Status tag will applied to every savedversion; Draft or Final

Connect Multiple Files:

Select multiple files in a folder and open the right-click menu

Click connect

Check the files you want to connect

To create a new FileBox from your workspace, simply click on the "New" button in the upper right corner of the screen and then click "New FileBox". Type yourFileBox name choose SubmissionTags if necessary(for more information click here) and click on create.

AFolderis used tosave several FileBoxes in it.To create a new Folder, simply click on the "New" button in the upper right corner of the screen and then click "New Folder".Type folder name and click on create.

You may also add a Collection Process,this is used by advanced users toautomatically consolidate the data from any number of different FileBoxes into one master table in the DataBase. For more information click here.

You may upload a file to a Filebox from the Workspace or the Version View of a Filebox

1. From the Workspace area (the default view when opening DataRails):

Hover over the Filebox's name. On the right-hand side, the Click here icon will appear. Click on it to open the pop-up tab and choose Upload.

2. From the Version View of a Filebox - Click on the upload button on the right-hand sidetoopen the upload pop-up window.

If you wish to upload onlyone file, fill in the information according to the steps:

Choose the file to upload

Label it (optional)

If there are submission tags assigned to the Filebox, add the relevant tags. For more information on setting submission tags, Click here

Check the boxes to include in dashboard / sendnotification (email will be sent to other collaborators)

UPLOAD

If you wish to uploadseveralfiles, Choose the "multiple file upload" and continue to fill in the information according to the steps:

Choose the file to upload

Label it (optional)

If there are submission tags assigned to the Filebox, add the relevant tags. For more information on setting submission tags,

Check the boxes to include in dashboard / sendnotification (email will be sent to other collaborators)

UPLOAD

The sync application is designed to improve and speed the performance of connecting and syncing files to the DataRails environment.

Files that are connected to DataRails instantly have an audit trail and version control that can be accessed from the Workspace. The versions can be compared to one another, tagged and named for identification purposes. The data from these files can also be extracted for dashboard visualizations, consolidation,and automation processes or used in external databases (e.g. SQL).

There are 2 types of Sync Applications, users can have both. The type of application to use is based on the location of the files they wish to connect.

Sync Service, an organizational level sync application. Mainly for files and folders that are used by several users.

Files are saved in a shared folder (shared organizational/departmental folder).

Files are saved in a web-based shared location (SharePoint, Box, Dropbox etc.). To connect these files, the user will need to download from the web (check them out).

Personal Sync, a user level sync application. Mainly for files and folders that are used by one user.

Files are saved on the users machine (on the desktop, downloads folder etc.)

Once the application is installed, DataRails icon will appear in the right-click menu of the file. File connection is done by clicking on this icon, it can be connected to a new FileBox or an existing one.

Connected files are tracked based on their location (files path) and naming convention. As long as these 2 components do not change, a file will continue being monitored. The system is designed to keep monitoring files even if DataRails application is closed. A connected file will have a DataRails indication icon on the button right corner of the file. This icon will not appear on files that are not connected or lost their connectivity.

Renaming a file or replacing a file will maintain the connectivity. Moving a file, saving under a different name or copy & paste will break the connectivity.

There is an indication of the file or files that are connected to the FileBox, click on the icon to open a window with the file list and information on the connected file.

System Requirments

Operating systems: Windows Server 2012 or higher.

16 GB RAM, 4 Processors, 50GB Free disk space.

.NET Framework 4.6.1 version is required

Excel 2010 or higher installed on the server

The installer requires Admin privileges.

Installation: Download the latest version from here and follow the instructions.

Selected shared Folders for sync: Any folder that is required to be synced to DataRails must be accessible by a Local System user on the machine that is running the DataRails sync service software.

Mapping the shared folder:

The Local System user does NOT have the access to mapped drives mapped by the logged-on user. This mapping must be done by the Local System user and placed in the boot policy/script of the machine.

Configuring the mapped drive:

Create a task in Task Scheduler that will be triggered at System Startup and run under the Local System user.Map the drive using the following command line:net use <letter>: <network_name> /persistent:yes

In order to verify that the drive is accessible by the Local System user, you need to run a command line as a Local System user:

Download https://docs.microsoft.com/en-gb/sysinternals/downloads/psexec.

Open an elevated CMD prompt as an administrator.

run PSEXEC -i -s -d CMD

In the command prompt, navigate to the mapped location.

Permissions needed:

As the Sync Service is running as Local System user on the server machine, a Local System user of the machine must have read/write access to the network shared folder.

In order to grant such permission, please run following (don't forget the $ sign at the end of COMPUTER name):icacls <folder_name> /grant DOMAIN\COMPUTER$:(OI)(CI)(OI)F

If you are not able to set the permission, please try to use this tool to check the ownership of the directory.Download: https://www.microsoft.com/en-us/download/details.aspx?id=23510 and execute:subinacl.exe /file <folder_name>

Viewing logs files: We recommend a log file viewer that supports tail.Please download one of the following:

Bartail free version - https://www.baremetalsoft.com/baretail/download.php?p=m

LogExpert - open source - http://www.log-expert.de

Installation verification: After installation, 2 components will be installed on the machine (C:\Program Files (x86)\DataRails Sync)

DRFileSyncService - a Windows Service that should be in running state.

DataRails Sync - shortcut to EXE file that is the UI for the service.

Add a DataRails user for the Sync User: Open DataRails django admin in the server and create new user: name: [email protected].

Go to Home Backoffice User profiles and check Is sync service and Save

Setup user credentials: Locate the DataRails icon in the System Tray, right click Credentials....In the dialog, enter the credentials you have created in the previous section and press OK.

Setup the sync configuration file: Locate the DataRails icon in the System Tray, right click Folder Config....

In the shown dialog, add folders that you which to sync.

After adding the folders, click Save&Validate.

Exporting logs:

Login to the FileSync server.

Go to: C:\Program Files (x86)\DataRails Sync and double click on FileSyncAgent

From the System tray, locate the DataRails icon. Right click on the icon and choose "Zip Logs".

After File Explorer is opened, use the latest zip file.

Step 1:Add a Dashboard ( click here to see how)(if you don't have one)

Step 2:Add the Widget ( Click here to learn "What is a Widget?")

Click the + Add New Widget button on the top right of the page:

After adding the widget,you will be presented with the build tools for your graph/table.

Step 3: Navigate to Select your template found on the left hand side of the page, to the near right of filters:

(The template is based on the file you uploaded. The first part of the text is the name of the table given upon configuration. The text in parenthesis is the name of the Collection Process or the Filebox).

After selecting your chosen template, you will see all the relevant field elements that you will need to create your graph. They will load under the template name.

Dragthe field elements from the template panel to the appropriate graphing section to the right.

There are 4 dynamic areas that can accept the field elements from the chosen template.

These are labeled as follows:

Axis (Category) this is your X-Axis

Legend (Series) this is your Y-Axis

Values here you can choose functions for your data, including:Sum, Count, Count Unique, Unique Values, Average, Minimum, Maximum

Filters here you can filter your data based on selections of your choice. These are in addition to the fields that you already included above.

Step 4:Build the visual component by hovering the mouse over the field elements until it turns into a white crosshair . Left click on the field and drag it over to graphing box 1,2,3, or 4.

Repeat until you have all the fields you wish included in your graph or table.

OnMay 1, ourapp.datarails.iodomain will stop working. It is important to make sure you are using theapp.datarails.comdomain.

Follow these steps to check and change the URL:

Open the DataRails settings, which can be accessed in two ways:

Open the DataRails app (desktop or web) > click on your name (top right) > choose "Settings"

Click on the system tray of the taskbar > right click DataRails' icon > choose "Open Settings"

Check if both "Server URL" and "Notifications Server URL" addresses end with ".com"; this is the recommended setting. If they have an .io address, follow the next steps to change it.

To change the URL address:

Click on DataRails icon six to eight times, until the URL addresses turn white.

Manually change the URL addresses.

Change the Server URL to "https://app.datarails.com"

Change the Notifications Server URL to "https://notifications.datarails.com"

Save the changes by clicking on the "Save" button on the bottomright.

If you do this correctly, it should look like this:

Calculated Field allows creating additional fields using the basic mathematic operators, on the extracted data columns.

To use this feature, start by mapping the columns you wish to calculate and generate a preview. Once the data preview is generated, you can click on the Calculated Fields button.

Example:

Start by mapping the table or the columns you wish to calculate:

Select the relevant sheet > Click on Add Structure > Choose the column type (header, dimension etc.) > Select the column from the preview area > Click on OK > Generate a preview.

The generated preview

Click on the Calculated Fields button to set the new calculated fields

A pop-up window will open to create the new fields:

Name the new field

Set the formula

2.a Select the mapped fields you wish to use

2.b Add basic mathematic operators (+,-,*,/) for the calculations.

Note: if the field name has a / or other operators in the name, you will not be able to use it for the calculations. To resolve the issue, use the Map To field to modify the name (in the main mapper window).

Save and close the pop-up window

The new field will appear in the generated preview area upon closing the pop-up window

Match Type optionsare additional mapping options that enhance the flexibility of scanning the mapped tables. Match Type options define a search pattern for the different roles and broaden the systems ability to extract the desired table even if elements in this table have changed.

click here

The Match Type option enables the tool/table to capture cell values that are not quietly the same.

Match Types arguments, listed from least to most flexible search types:

Value The expected value will be exactly the same as the value of the cell that was mapped.

List In case you have a variation in one of the cells values between different version of your file, you can create a list of acceptable values.

Example: The headers value in this version of the table, located in cell B6, is Line Item. However, in other versions of the file this cells value can have a slight variation in value (e.g. lower-case letters: line item). Choosing Match Type: List, then insert a list of acceptable values for this header. It will allow the following/previous versions to be extracted successfully as well.

Location Use this option when the cell value changes (or might change) throughout versions, but the location of the cell stays the same.

Example: The cell's value is a date that changes daily/every version, but its location stays the same.

Regex Extract values based on a textual or numerical pattern. Regex is a textual syntax for representing patterns for matching text.

Clicking on Suggest will assist you to write a valid regex pattern or contact your Success Manager for further assistance.

Example: The cells values is As of date 1.1.2018. The constant terminology is As of date and the date can change. Using the regex option, we can extract any cell that has As of date in it.

Any Text Used for tables that have constant headers along with some headers that vary between versions and do not have any common identifiers. Mostly relevant for the dimension role, Any Text will scan for any possible header values.

Note: It is the least recommended argument because it takes the longest processing time.

Example: A table has fixed headers in the first 2 columns (Category & Line Item) and following them varied columns (Department Names).

Mapping is as follows:

To capture data from 2 tabs, select All Sheets (the default option).

For the fixed columns (Category & Line Item, B3-C3) assign the Header role.

Select the department columns (D3-F3).

Assign a Dimension role.

Select Any Type as the match type.

Name the dimension as Department

Click on OK

In the generated preview you will be able to see the data for the current sheet only. When you access the extracted data from the Insights tab or when querying the data, you will be able to select the relevant data by choosing the tab it was scanned from (using the Worksheet (tab) element of an Insights widget).

For more information on widgets click here, for all articles on the Insights .

The following options are relevant for the "Header" Role type.

Data Manipulation options

Fill in blank cells

This option is most relevant when you have merged cells and blank cells in the columns. The blank cells will be populated with the value of the cell above them.

Here are examples of mapping with and without this option:

Example 1 - all headers were selected (B9-K9) and fill blanks option is off. The database table, as seen in the Output Table Preview, some line items values (column B) are blank.

Example 2 - all headers were selected (B9-K9) and fill in blanks option was selected. The database table, as seen in the Output Table Preview, all line items values (column B) are populated.

Example 3 - in case there are blank cells throughout the table, but you would like to fill the blanks for a single/several column/s only, select the column/s you wish to fill the blanks for as a separate structure (e.g. header, B9) and check the option of filling the blank. Then, add another structure for the rest of the table as headers (C9-K9 in the example above) but keep the Fill blanks option off.

Convert to Text

Mixed data types (e.g dates and prices) residing in a single column can cause issues with the way the data is presented in the dashboards or when querying the data. In that case, you can convert all values of those columns to text values during the process of data extraction.

Letter Case

Text values can be manipulated by their letter case. It will affect all the versions in the FileBox, so if you have Revenues in one version and revenues in another version, you can use the different letter cases to manipulate both outputs to look the same.

Example of the case options, using different letter case as line items:

For more information, check out other Data Mapper articles on the website or contact your Success Manager at DataRails.

Repetitive Structure feature is relevant for tables with columns that add up/change throughout versions, in a constant pattern.

For example, a file that contains monthly data in which additional columns are added for each month.

One version of this file will contain a line item column and 2 columns with monthly data (Jan, Feb). Another version will contain the line item column and 7 columns with monthly data (Jan, Feb, Mar, April etc.).

To use the repetitive structure, the file must contain at least one column that doesnt change*. It will be the anchor column for which the repetitive structure will start scanning. The anchor column must be a Header type of role.

*The anchors header shouldnt change throughout file versions, however, its data may vary.

For more information on the various Role types, click here.

To use the Repetitive Structure, assign the cell a Dimension type of role. Select the cell or cells that comprise of the pattern you wish to extract. After assigning the role, there are 3 possible Match Types to extract the data; Any Text, Regex or List.

Any Text will find any additional columns added to the file version. It takes the longest processing time.

List If you know the names of the columns that will be added, specify them in a list. This option has the shortest processing time.

Regex If there is a certain logic to the column headers, use the regex feature to specify it. Use the regex suggest feature for assistance in writing the regex formula. You may leave the regex box blank and it should auto find the naming logic. click here

For more information on the various Match Types, .

Mapping Example:

Start by creating a table and map the anchor column:

Name the table

Select the relevant sheet to extract the data from

Click on the Add Structure button

Use the Header type of role

Select the cell to map (B3)

If necessary, change the naming convention

Click on OK to save

Map the repetitive structure cells

Click on the Add Structure button

Use the Dimension type of role

Select the cell to map (C3). The structure in this example has only 1 cell in the structure, but you can capture more than 1 cell to define the structure.

Name the dimension

Select the Match Type (Any Text)

Make sure to check the Repetitive Structure checkbox

Click on OK to save

Generate a preview and check the repetitive structure columns

The mappingexamples include the following structure roles:

Basic table mapping

Role: header

Complex mapping #1

Roles: attribute, header, and dimension

Complex mapping #2

Roles: attribute, header, and dimension

Basic table mapping

Name the table

Select the sheets to process. Extracting data from the relevant sheet will reduce the processing time of scanning multiple sheets of the file. If you have similar tables in several sheets, you can select them all. In case the sheet name may vary between file versions uploaded to the FileBox, you should select all sheets.

Click on Add Structure

Select the role: Header.

Select the relevant cells to define as headers by highlighting them. Click on the row number to highlight all headers in the row (9) or choose one of the cells (B9) and drag to highlight all desired headers (B9-O9)

Click on the OK button

You can generate a preview of the structured data table as it will be stored in our database by clicking on the Generate button (below the spreadsheet preview area).

If the generated tableisn'tpicking up the date properly, you can edit the structures by clicking on the pencil icon or deleting it by selecting the trash can icon .

When you are done, click on the Save button.

Complex mapping #1

First, choose a table name, select the relevant sheet/s and click on the Add Structure button (elaborated above in paragraphs 1.1-1.3 of Example 1).

Next, define the tables different structure types:

Attributes select the data in the upper table using the role type: Attribute. Only the headers (cells B3-B5) are to be selected, the actual attribute value will be that of the cell to its right. Click on the OK button to save.

Click on the Generate button to see how the data is mapped to the databased table. Dates and numerical value formats can be edited.

Header Select cell B9 (Line Items) as the header of the tables vertical axis. You can change the headers name by typing an alternative header name in the Map To box. The original name is the default for each header selected. Click on the OK button to save.

Generated preview

Dimension to group together the months headers, use the dimension role. Select the role: Dimension and highlight the table headers that contain months (C9-N9). Name this new group in the Map to area (e.g. Period) and click on OK.

Generated Preview & pointers

There are advanced options to each one of these roles, found in the box below Match Type. We will discuss them in the Advanced Data Mapper article link.

Complex mapping #2

Assign a table name, select the relevant sheet/s and click on the Add Structure button (elaborated above in paragraphs 1.1-1.3 of Example 1).

Next, define the tables different structure types:

Table ID In this example, the identifier is the table name which indicate the income type. Click on the add structure button, select Table ID as the Role, mark the cells in which the tables unique identifier is located (C2-F2), name the identifier in the Map To box (e.g. Type) and click on the OK button to save.

Header Click on the add structure button, choose Header as the Role. Select cell B3 (Line Items) as the header of the tables vertical axis. To change the headers name, type an alternative name in the Map To box. The original name is the default for each selected header and click on the OK button to save.

Dimension the best way to map the quarters is by grouping them together, as they represent a time dimension. Click on the add structure button, choose Dimension as the Role. Select the cells containing the different quarters (C3-F3) and name the group in the Map To box (e.g. Quarter). Click on the OK button to save.

Footer define where the table ends, to differentiate between data sets of different tables. Click on the add structure button, choose Footer as the Role. Select cell C12 (e.g. Next tables header) and click on OK.

The generated preview will extract data from one table only. When using the extracted data in DataRails Insights, Power Query or elsewhere, the data will be extracted from all tables.

About the Installer

DataRails installer comes in either of the following 2 packages:

Small package - DataRailsInstaller.exe (~150KB)Downloaded from https://www.datarails.com/download/.This file is only a downloader for the big package and it contains an installation wizard (i.e. User Interface) which allows to choose the exact installation path.

Big package - Setup.exe (~65MB)This file contains the actual software files. It is downloaded from https://datarailsdownload-rqyuemdnr8bcm2wku.stackpathdns.com and it runs silently - no UI present, which means users cannot choose the installation path.

Both installation files as well as all the other EXE/DLL files are signed by Symantec Extended Validation (EV) Code Signing Certificate.

Permissions

Both DataRails installer and the DataRails app do NOT require administrator permissions to run as long as the installing user has write permissions to the target directory.

The default target directory is %APPDATA%/DataRails, which would usually translate to c:\Users\<username>\AppData\Roaming\DataRails.

Nevertheless, the security policy in your organization might not allow installing any program in the default target directory. In such case, DataRails should be installed in the Program Files directory, which means that it needs to be activated with administrator permissions.

Machine Host is the Computer Name

Machine ID

User associated with the device

Last Login date

Version of DataRails Installed on the machine

What version has been chosen to Upgrade to

Socket Polling (relevant for DataRails Super Users)

Ignore Certificate Warning (relevant for DataRails Super Users)

Office Status (relevant for DataRails Super Users)

Vailddiagnostics (relevant for DataRails Super Users)

A collection process is a folder containing multiple FileBoxes and is used to automatically consolidate the data from any number of different FileBoxes into one master table in the DataBase. This means, that the data from all versions from all FileBoxes within the collection process, will be automatically saved in the same table in the DataBase.

New Collection Process

Upload files to a Collection Process

Add new FileBox to a Collection Process

NewCollection ProcessTo create a new Collection Process from your workspace, click on the New button in the upper right corner of the screen and then click New Collection Process. Workspace

A new screen with six steps will open:

Step 1 - type the name of the new collection process and clickNext.

Step 2 - set up Submission Tags by clicking on the switch to turn "on" your preferred tags and then clickNext.For more information about setting up Submission,Tags click here.

Step 3- type the Dimension Name you want to create. Then, typethe Dimension Values, a FileBox would be created for each of the values specified. *Use the enterkeyto separate the different values. To complete this step clickNext.

Step 4 -Upload Config file. If you don't have a config file simply click Next.

Step 5-Upload Template file. If you don't have a template file simply click Next.For more information about using templates click here.

Step 6 -Click Save to finish the process.

Upload files to a Collection Process

There are two ways to upload files to a collection process:

Upload files to the Collection Process FileBoxes using

Balk upload:

In the Workspace Hover over the Collection Process

Click on the 3 dots and click Upload Files from the drop-down list.

Add new FileBox to a Collection Process

If you set up a collection process and would like to add another FileBox, follow these steps:

1. Open the collection process from the Workspace, by clicking on its name or icon

2. Click on the "New" button on the right-hand side of the collection process view, and choose "New FileBoxes."

3. Create the new FileBoxes.

Name the new Fileboxes.

Add a template to the Fileboxes, if you wish.

Edit User Details (Name and Email)

Reset Password (Sends a Reset Password Link to the registered Email)

Edit permissions (Assign admin role to users)

Edit Permissions Filter (Define what data a user can gain access to Relevant for Mapped Data Only)

Lock User (Disable / Enable a users access to the system)

The Admin Panelis the interface where administrators and other site officials with appropriate privileges can manage and oversee DataRails' users, FileBoxes and application versions used within the organization.

To accessthe Administrator interface (ifyou have administration privileges), go to app.datarails.io and login using your username and password.

Once logged in, click on the Admin Panel icon on the top-left corner:

On the panel to the right, you will be presented with the available actions:

Users

Displays a list of DataRails' users within the organization, and basic information for each user. Users display allows you to (1) edit the user's information; (2) Reset password; (3) Edit user's permissions; (4) Lock user and (5) Login as user.

Organizations

Normally, only one organization will appear on this list. Here, you can: (1) Change the default logo appearing on each FileBox to your organization's logo; (2) Edit your organization's permissions; (3) Edit the color palette of the BI dashboards charts and (4) Edit default tags for your FileBox's versions.

FileBoxes

List of all FileBoxes in your organization. You can search for a specific FileBox throughout all FileBoxes in your organization and download its latest version.

Devices

View a list of machines running DataRails, see which user is logged in on which computer and id they are online and release software auto-updates for a group of users or all users in your organization.

New Feature:

Edit Version Tags

Editing a version'stag is now available.

In the FileBox Details, hover over the file version you wish to modify, click on the "more" menu and choose "Change Version Tags".

A pop-up window will open and you will be able to change the version's tag.

Changing the tag will be according to the time period that was initially set for the FileBox.

For example: If the assigned tags are monthly you will be able to change July's version to August(or any other month), but you will not be able to assign a quarter or year tag.

To set your organization's permissions, open the "Organization" tab of the admin panel. Click on the permissions icon to open the pop-up window. You may switch the "yes/no" button at any time, according to your organization's needs (see images below).

The following are explanations regarding the most important settings options:

Upload and/or "Connect" to an Existing FileBox:

Enabled: Users are allowed touploadorconnectan Excel file to an existing FileBox.

Disabled: Users arenotallowed touploadorconnectan Excel file to an existing FileBox (i.e. this settingallows users to create new versions using the submit, save or "ctrl+s" using the Widget).(DR Default)

Connect a File That is Already Connected to Another FileBox:

Enabled: Users have the ability to take a file that is already connected to aDataRailsFileBox and create a new FileBox OR connect to another existing FileBox. This action disconnects the file from the original FileBox.

Disabled:Userscannottake a file which is already connected to aDataRailsFileBox and connect it to a new/different FileBox. (The FileBox owner would need to first disconnect the FileBox.)(DR Default)

Auto-Submit Visibility:

Enabled: All "Auto-Submit" versions will be shown in the FileBox version view.

Disabled: Only the last "Auto-Submit" version will be shown in the FileBox version view.(DR Default)