Consuming DataRails OData Source Using REST – DataRails

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:

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

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

</collection>

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

</collection>

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

</collection>

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

</collection>

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

</collection>

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

</collection>

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

</collection>

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

</collection>

<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">

</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 doesn’t 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.

We're here to help

Basic Queries

Selecting Columns

Filtering Data

Sorting the Results:

Special Characters in Column Names

Related articles