BI generators

An explanation of the generators used to calculate Business Intelligence measures

Operating mode

Cloud Suite

ON-PREMISES

Modules

Services & CRM

Budget & Phases

Purchases

Resource Planning

Business Intelligence

Created: 22.04.2020

Updated: 15.05.2025 | Added description of BI generator code, introduced with Vertec 6.8.

To provide data for the Business Intelligence module, we use a data repository according to the data warehouse concept, sometimes also called an "OLAP cube". Depending on the choice of dimensions, a section is cut from a cube.

Essentially, the data warehouse consists of a large data table (cube) with pre-calculated data values. This enables efficient, business logic-independent access to the BI data, and thus a high-performance display, even with large amounts of data.

The calculation is time-independent and with full business logic support via regular scheduled tasks. Thus, if required, the calculation can be done in individual packages (e.g. individual months) in own processes or even parallelized.

The calculation logic is defined in Python scripts called "generators". These are saved on the measures. To view the code of a generator, click on the button with the three dots next to the Generator field on the measure:

The generator must contain a calculation for the measures on which it is saved. The internal name of the measure must match the values returned by the generator.

Standard BI generators

Vertec supplies the following generators by default:

Generator name	Data used	Calculated measures
ServicesGenerator.ServicesGenerator	Service sums (global groupServicesOperator) grouped by project, phase, user and service type.	External fee Internal fee External hours Internal hours Cost External fee charged External fee open External fee written off Contribution margin (CM)
ProjectGenerator.ProjectGenerator	Service sums (global groupServicesOperator) grouped by project and invoice. Only uses productive projects.	Downpayment balance Commenced work
UsersGenerator.UsersGenerator	Users who joined before the end date and have not yet left, or whose exit date is after the start date.	Full-time equivalents (FTE) Headcount Productivity Standard hours Working hours Overtime balance Vacation balance Vacation balance accrued
InvoicesGenerator.InvoicesGenerator	Invoices that have been charged and whose value date is within the calculated date range.	Turnover services Turnover expenses Turnover outlays
PhasesGenerator.PhasesGenerator (before Vertec 8.6)	Top-level phases that have an issue date and whose issue date is before the end date and that have not been completed, or have not been completed until after the start date: BudgetChanges of these phases Service sums of these phases (groupServicesPWG)	Order backlog fee Order intake fee Cumulated hours Total budgeted hours Remaining budgeted hours
PhasesMonthGenerator.PhasesMonthGenerator	Top-level project phases that have an issue date and whose issue date is before the end date and that have not been completed or have not been completed until after the start date: BudgetChanges of these phases Service sums of these phases (groupPerformancePWG)	Order intake fee Total budgeted hours Budget per end of month
PhasesRangeGenerator.PhasesRangeGenerator	Top-level phases that have an issue date and whose issue date is before the end date and that have not been completed or have not been completed until after the start date: BudgetChanges of these phases Service sums of these phases (groupServicesPWG)	External fee per phase Internal hours per phase

Recalculating data

The data is regularly calculated from a scheduled task during the night. It can be found in the folder Settings > Customizing > Scheduled tasks.

This process triggers all the generators stored in the active measures, and calculates the values for the period from January of the penultimate year to the end of the next year.

The status of the task shows whether the calculation was successful or failed. The feedback of the calculation is shown in the output window.

A Vertec app does not have to be in operation, only a running Vertec Cloud Server is required. For the calculation, a user is defined on the scheduled task. This user must have administrator rights in order to perform the calculations.

Execution time

The Next execution field shows when the scheduled task will run next.

In the Cloud Suite, the time on the interface cannot be changed. The calculation always takes place overnight, in a time window between 11pm and 5am

For On-Premises, the time window can be set with the parameters Special task scheduler range start and Special task scheduler range end in the Vertec.ini file.

Initiating recalculation manually

The BI recalculation can also be initiated manually. There are two options:

Execute the scheduled task: Click on Execute now in the context menu of the scheduled task:

This triggers the calculation of all generators on all active measures.

Note: The calculation is started directly in the current Vertec app and not in a separate task runner process as is the case with time-controlled execution. This should not be done during ongoing operations, as the calculation takes a very long time and Vertec may be blocked.
Calculate measures of one generator: The calculation can be initiated manually on individual measures by selecting Recalculate measures... in the context menu:

The generator stored on the measure is triggered and the corresponding measures are recalculated. The date interval can be set:

At the end there is a message indicating which measures have been calculated.

By recalculating measures, existing values are replaced only for the selected period and the relevant measures. All other existing values remain.

Python methods for calculating BI data

There are two Python methods for calculating BI data.

vtcapp.standardprocessbi()

Triggers the calculation of all generators on all active measures.

Requires administrator rights.

Corresponds to the execution of the scheduled task supplied by default, see above.

vtcapp.processbi(from: string, to: string, generator: string)

Triggers the calculation of the generator supplied as a parameter.

Requires administrator rights.

The From and To dates are specified as a string in the format “Year-Month”.
The generator must be specified in the same way as for the measures, i.e. “<modulename>.<generatorname>”.

vtcapp.processbi("2023-01", "2026-12", "ServicesGenerator.ServicesGenerator"

Corresponds to the calculation of the BI data on a measure, see above.

Extending/creating generators

You can extend existing generators or create new generators. To do this, create a new script in the folder Settings > Reports & Scripts > Scripts. And write the generator code in Python.

The designation of the script itself is the module name. Together with the class name of the generator, it forms the generator ID. You can specify this as the generator for the BI measures calculated by this generator:

Module names must be unique. You cannot overwrite an existing generator by writing a module with the same name, but rather by creating a new generator and saving it on the relevant measures.

BI generator code from Vertec 6.8

With Vertec version 6.8, we introduced a built-in module called vtcbi with the following classes:

BiGenerator: New base class for generators
Measure: Class which describes a measure
Dimension: Class which describes a dimension

The module vtcbi is also supplied as a stub file.

You must import the module or classes into the BI generator script:

from vtcbi import BiGenerator, Measure, Dimension

BIGenerator

initialize(self, startdate, enddate)	Optional. Called up once before calculating with the entire period. To pre-calculate data for the entire period, we recommend you use the method `generate_range()` instead.
generate_month(self, startdate, enddate)	Is called up once by the business logic for each calculated month. Startdate and enddate are the first and last day of the month. It must call up `self.store_values()` itself. Only `generate_month()` or `generate_range()` are to be used. If both are declared, an error message appears during execution.
generate_range(self, startdate, enddate)	This method is called once for the entire period to be calculated. Can be used to pre-calculate data. It must call `self.store_values()` itself, and the parameter `date` must be passed. The values are then automatically sorted in at the right date. It may only be used either `generate_month()` or `generate_range()`. If both are declared, an error message appears during execution.
store_values([currency, date])	With `store_values()`, the specified measures and dimensions are calculated and saved. Currency: Optional parameter for the currency. If Vertec works with several currencies, the currency must be passed as an object (e.g. with `project.currency`) when calculating amount and hourly rate values. These values are then converted to the key currency. Date: Optional parameter for the date. If this parameter is set, `date` per row is returned as the date, otherwise `startdate`. If store_values() is called up from the method `generate_range()`, a `date` must always be specified.
finalize(self)	Optional. Called up once after calculation. Can be used to approve loaded data (e.g. service sums).

Measures

The BI measures are defined within a generator as follows:

Measure(InternalName: string, Description: string, Unit: integer, [IsCutOffDateValue: boolean=False])

Internal name	Here you can specify the internal name of the measure.
Description	Specifies the designation of the measure. The designation is used as NV (native) of the label field (MLstring) if the measure is imported directly from the generator.
Unit	Indicates the unit of the measure. 0 = amount (`BiUnit.Amount`) 1 = hours (`BiUnit.Hours`) 2 = % (`BiUnit.Percentage`) 3 = quantity (`BiUnit.Quantity`) 4 = rate (`BiUnit.Rate`) For easier usability, there is the auxiliary class `vtcbi.BiUnit`, which contains the relevant constants. This has to be imported: from vtcbi import BiUnit
IsCutOffDateValue	Optional. Specifies whether the measure is a key date value. – `True`: key date value By default, this value is `False`. Therefore, it only needs to be indicated for key date values.

Dimensions

The dimensions are defined like the measures within a generator:

Dimension(order: integer, type: string[, role: string]

Order	Order of the dimension on the measure. Must match the order on the measure:
Type	Specifies the class name of the dimension.
Role	Optional. The role name of the dimension can be specified here. The value passed is used as the NV (native) of the role field (MLstring).

Example

As an example, a generator is created to show productive and unproductive services separately.

generate_range() calculates the entire time period. store_values() takes care of the correct summation and sorting.

from vtcbi import BiGenerator, Measure, Dimension, BiUnit

class ProductiveGenerator(BiGenerator):

    prodServices = Measure("ProdServices", "Productive Services", BiUnit.Hours)
    unprodServices = Measure("UnprodServices", "Unproductive Services", 1)
    project = Dimension(1, "Project")
    projectmanager = Dimension(2, "User", "as project manager")
    user = Dimension(3, "User")
    
    def generate_range(self, startdate, enddate):
        # calc service sums for the whole period, grouped by month, project and user
        servicesums = vtcapp.evalocl("TimSession.allInstances->first->groupLeistungen('{}', '{}', 'MONTH, PROJECT, USER')".format
            (vtcapp.datetostrgerman(startdate), vtcapp.datetostrgerman(enddate)))
            
        for servicesum in servicesums:    
            self.project = servicesum.evalocl("project")
            self.projectmanager = servicesum.evalocl("project->oclAsType(Project).projectmanager")
            self.user = servicesum.evalocl("user")
            self.prodServices = servicesum.evalocl("(minutesIntOpen + minutesIntCharged)-(minutesIntOpenUnprod + minutesIntChargedUnprod)")
            self.unprodServices = servicesum.evalocl("minutesintOpenUnprod + minutesIntChargedUnprod")
            self.store_values(None, servicesum.evalocl("entrydate"))

BI generator code before Vertec 6.8

The declaration of the generator is as follows:

class <Generatorname>(object):

The generator object can be accessed at any time in the script via self. Within the generator, the following methods are available:

Methodology	Description
get_dimensions(self)	This method specifies for which dimensions the generator calculates values: def get_dimensions(self): return ['Projekt', 'Projektbearbeiter'] The same number of dimensions must be specified in the measures as supplied here, and in the order specified. For example, if you want to show values for both users as project managers and users as key users, the dimension `Projektbearbeiter` must be specified twice: def get_dimensions(self): return ['Projekt', 'Projektbearbeiter', 'Projektbearbeiter']
get_measures(self)	This method specifies which measures are calculated. The return values must match the internal names on the measures. def get_measures(self): return ['OffersQuantity'] The generator can then be saved on all measures that are returned here.
initialize(self, startdate, enddate)	First, the method is run through. The variables `startdate` and `enddate` contain the date interval of the entire calculated period. Here, you can preload the lists of used objects in a performance-optimized way so that you have all the relevant data for the entire period. In the method `generate()`, the data is then calculated per month.
generate(self, startdate, enddate)	The `generate()` method is run as many times as there are months in the calculated period. The variables `startdate` and `enddate` always contain the first and last day of the month, i.e. for January to June, 1.1.23 and 30.6.23. The data preloaded in `initialize()` can now be filtered and calculated per month. Return value The return value is a tuple of dimensions and a tuple of measures, in exactly the order declared above. The return value is not returned as usual in Python with `return()`, but with `yield()`: yield((project, project.projektleiter), (OffersQuantity,)) Using `yield()` means that there is not only one return value (and all data has to be laboriously summarized in an array and then returned). But rather every row can be returned until the entire code has expired. For example, within a project loop, the calculated data per project etc. can be “delivered” continuously (see example code below). Currencies If you work with more than one currency in Vertec, you have to take this into account during calculations. You can optionally enter one currency per return line (example from the InvoiceGenerator supplied): yield ((project), turnover_tuple, project.waehrung) Vertec then manages the correct conversion into the key currency. The values are always shown in the key currency.
finalize(self)	At the end, the `finalize` method is run. With this method, for example, objects that are no longer needed can be removed from memory.

BI generators

Standard BI generators

Recalculating data

Execution time

Initiating recalculation manually

Python methods for calculating BI data

Extending/creating generators

BI generator code from Vertec 6.8

BIGenerator

initialize(self, startdate, enddate)

generate_month(self, startdate, enddate)

generate_range(self, startdate, enddate)

store_values([currency, date])

finalize(self)

Measures

Internal name

Description

Unit

IsCutOffDateValue

Dimensions

Order

Type

Role

Example

BI generator code before Vertec 6.8

Return value

Currencies

Schweiz

Deutschland

Österreich

Netherlands

United Kingdom