BI connectivity with Odata-based linking

To bring the data from the FCC to a BI solution, the BI connectivity app is used. This app enables the creation of a link based on Odata.

Best practices

To get the best possible lead time of data ingestion, the following best practices should be followed:

• Retrieve only the necessary columns
  • Use $select to select the appropriate columns
• Pick up only the necessary rows
  • Use the $filter to select rows. A filter on SystemModificationDate can be used to retrieve only modified objects as of a certain date

Authentication

This interactive guide explains how to add a BI connector to create an OData link and authentication key. The Odata link and authentication key can be used to connect FCC to other systems.

Objects 

Endpoints are available for the different objects. Use the root level to retrieve the different endpoints.

Selecting relevant columns

The $select query parameter can be used to select the relevant columns.
Example: selecting the Name and Portfolio columns of the projects.

$select=Name,Portfolio

Full example URL:

https://documentation.fortes.nl/service-odata/odata/69838e0a-f419-4e8c-9e9d-e400fa6b771e/BiConnectivityOdataConnector/2462450/Project?$select=Name,Portfolio

Selecting relevant rows

The $filter query parameter can be used to select relevant rows.
Example: selecting rows for projects from a specific portfolio.

$filter=Portfolio eq 12368613

Full example URL:

https://documentation.fortes.nl/service-odata/odata/69838e0a-f419-4e8c-9e9d-e400fa6b771e/BiConnectivityOdataConnector/2462450/Project?$select=Name&$filter=Portfolio eq 12368613

Pagination

The endpoints use server-driven paging.
The server enforces a maximum page size of 1000 records per request, even when the client requests a higher value using $top.

Use of $top > Page Size

When $top=1500

• The first request returns 1000 records.
• The response includes an @odata.nextLink to retrieve the remaining 500 records.
• Example:

"@odata.nextLink": "https://example.com/odata/EntitySet?$top=500&$skip=1000"

Use of $skip

The client can use $skip to start reading data from a different point in the table,
but the server will still enforce the row limit of 1000 records per request.

• Example:
  $skip=100 starts at record 101 and returns records 101–1100 (1000 records total).

Optimal $skip values

• For best performance, use $skip in multiples of the page size (1000). This ensures efficient paging and better server-side processing.

✅ Optimal: $skip=1000, 2000, 3000, ...
❌ Not optimal: $skip=750

The end of the data

When $skip exceeds the total number of records, the response will return an empty array without an @odata.nextLink.

Example:

{
  "value": []
}

Archived items 

Archived items are excluded by default.
To include them in your query results, add the following filter:

$filter=_archived eq 1 or _archived eq 0

Retrieving the total number of rows 

To retrieve the total number of rows, use the following query parameter:

$inlinecount=allpages

Entry rules

There are three available endpoints for booking entries:
/CostEntry, /HourEntry, and /ObjectiveEntry.

These are special endpoints.

• $filter: Supports basic filters such as eq, gt, lt, and ne.
  • https://www.odata.org/getting-started/basic-tutorial/#filter 
  • Example:

$filter=Portfolio eq 176465213 and Type eq 'Capacity'

• $select: The selected columns are used to group and aggregate the returned values.
  • The default values are: Startdate, Type, and {value field}.
  • Check the metadata (e.g. /...tor/176492180/$metadata) to see which fields are available.
    Note:
    • Grouping by value fields (Actual, Allocation, Reserved, etc.) has no effect, as this grouping is applied by default.
    • When selecting a display value or key column, both values will be returned in the response.
• Timeline horizon
  • scale = [Days, Weeks, Months, Quarters, Years] (default: Months)
  • startdate: YYYY-MM-DD format (default: {current year}-01-01)
  • horizon: Positive integer representing the desired duration in months (default: 12)
  • Invalid values will result in an error response.

Example URL:

https://documentation.fortes.nl/service-odata/odata/69838e0a-f419-4e8c-9e9d-e400fa6b771e/BiConnectivityOdataConnector/2464074/HourEntry?$select=Portfolio,Portfolio_Item&$filter=ProjectReport eq '176467405'&$inlinecount=allpages&scale=Quarters&startdate=2024-07-11&horizon=24