BI API

API for the Business Intelligence Module

Product line

Standard

Expert

Operating mode

CLOUD ABO

ON-PREMISES

Modules

Services & CRM

Budget & Phases

Purchases

Resource Planning

Business Intelligence

Created: 17.11.2020

Updated: 18.01.2024 | Description for authentication via API token adapted.

As of Vertec 6.4.0.14 there is an API (Aplication Programming Interface) for querying BI data from external applications. The call is made via <ServerURL>/api/bi .

In the configuration file Vertec.ini there is the parameter BI API :

[CloudServer]
BI API=True

Standard is True . This means that the BI API will run even if the parameter is not specified.

The BI API can only be used if the Business Intelligence module is licensed.

The BI user rights are used.

Here you will find a detailed BI API – Sample application with Postman/Excel .

Authentication

Authentication to the BI API takes place via API Token . This must be provided as a bearer token in an authorization header:

import requests
url = 'http://localhost:8081/api/bi/measures'
api_token = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.MGVhZmUzMzYtNmVhMi00MDdhLTgxNjQtZDYxZmI0NzU2MWZi._r16YlvWmZCMJ3qdDX3bK5_DJHwcczTYaWoKUYUNZuk’
headers = {'Authorization’: 'Bearer %s’ % api_token}
r = requests.get(url, headers=headers)
measures = r.text

Note: In Vertec versions 6.6, authentication was done via HTTP Basic Auth with username and password. The corresponding handling can be found in the article at the bottom .

Endpoints

Under /api/bi the following GET endpoints are available:

/getdata

The BI data become via endpoint /getdata queried. It accepts the following parameters via query string:

measures	Mandatory. Comma-separated list of internal metric names (e.g. `"feesext,minutesint"` ). For the list of internal names of the standard metrics provided, see here .
Dimension	Mandatory, case-insensitive. List of dimensions by which the result is grouped (e.g. `"Projekt,Projektbearbeiter"` ). For the list of dimensions of the supplied standard metrics, see here . As a pseudo-dimension, `"Month"` cut-off date values such as OvertimeBalance are only accepted for time series. For these values, therefore, `dimension=month` otherwise an error message will appear. If different Role exist for the dimension, it can be accessed via a suffix [index 0-4]. `Projekt` selects the dimension project without Role. `Projekt_1` selects the dimension project on index 1 on the metric. This must then be a Role on project. etc. Example when querying the Kpis Feesext (external fee): dimension0 = project dimension1 = project manager_1 dimension2 = project manager_2 dimension3 = Project Phase dimension4 = Servicetype dimension5 = Month If several KPI with different “dimension structure” are requested and the requested dimensions cannot be supplied across all KPI, an error message will appear. In such a scenario, the Kpi with the same dimension structure must be bundled in one query and several of these queries must be dropped in order to extract all the data.
_variable and OCL expression	Optional. As of Vertec 6.5.0.11 you can specify alias parameters with an OCL expression as a value for each dimension parameter (dimension0=project, dimension1=project editor, etc.), e.g. `dimension0_Projekttyp=typ.bezeichnung` . This causes a new field to be added in the answer `Projekttyp` which contains the designation of the project type of the project dimension value. Any number of alias parameters can be specified per dimension. If the parameter used corresponds to a name that is already in use (as measure names, class names or their translations, or alias of another dimension), an error is reported.
startDate	Optional. Start date in ISO 8601 format (2020-01-01)
endDate	Optional. End date in ISO 8601 format (2020-12-31)
selectExpression	Optional. OCL expression for the Selection .
useDescriptions	Optional, True or False. Default: False. Determines whether the column names are delivered in plain text and translated or as internal technical designations that are language-independent.
f	Optional. Specifies the format in which the result is output: csv: returns the result as a CSV file json: returns the result formatted in json. The parameter can be specified as an Accept Header instead of a query string. If both are present, the query string is used.

The endpoint returns results in the following format:

Header (column names)	Name of the requested Kpi (Internal name). If the parameter `useDescriptions = true` is, instead of the internal name, the plain text designation of the Kpis is used and is returned translated according to the current language of the session.
Data row	Dimensional values Object Id of dimensions (from Vertec 6.5.0.11): For each dimension value there is a property with the same name and suffix `_Id` which contains the object Id of the dimension value as a value: `{"Bearbeiter":"Administrator","Bearbeiter_Id": 301,"Monat":"2020-01-01", ...}` . Dimensions Month appear without `_Id` -Field in the result. KPI figure Value

Example query FTE for the first quarter of 2023

api/bi/getdata?measures=fte&dimension0=Project Editor&useDescriptions=true&dimension1=month&startDate=2023-01-01&endDate=2023-03-31

Returns:

“Worker,””Worker_ID,””Month,””Full-time positions”
“Christoph Keller,”676,”2023-01-01,”0.8
“Christoph Keller,”676,”2023-02-01,”0.8
“Christoph Keller,”676,”2023-03-01,”0.8
“Judith Feller,”682,”2023-01-01,”1
“Judith Feller,”682,”2023-02-01,”1
...

/measures

The GET endpoint /measures returns all available metrics .

The optional parameter f can be specified (see above).

The return contains the following fields:

Name
Description
Dimensions (string[] of class names)
Help text
Unit

The return values are translated into the language that the user has set in the web app.

Support for HTTP Basic Authentication

With Vertec version 6.6 API Tokens introduced for increasing the login security of Web API accesses. HTTP Basic Authentication with username and password is therefore no longer supported from Vertec version 6.7 onward.

The started session has a default timeout of 5 minutes. This can be done in the [Cloud Server] Section in Vertec.ini file with XML Session Timeout = 5 (the BI API and the XML Server share the session timeout setting).
After the timeout expires, the session exits and the process disappears.
If another request with Basic Authentication and the same user arrives during this time, the session is reused.

If both API Token and Username/Password are specified in a request, only the API Token will be considered. If it is invalid, a login attempt with Username/Password will not be made automatically.