BI generators

For the deployment of the data for the business intelligence module, we use a data storage according to the data warehouse concept, in some cases also called “OLAP Cube”. Depending on the selection of the dimensions, a slice of a cube is then cut accordingly.

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. 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 stored on the measures. To view the code of a generator, click on the browse button next to the field Generator on the measure:

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

Expand generators

Generators can also be extended or new generators written. To do this, create a new script in the folder Settings > Reports & Scripts > Scripts and write the generator code as a Python script.

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

Module names must be unique. You do not “overwrite” an existing generator by creating a module with the same name, but by creating a new generator and storing it on the corresponding measures.

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:

Method	Description
get_dimensions(self)	This method specifies for which dimensions (classes) the generator calculates values: def get_dimensions(self): return ['Projekt', 'Projektbearbeiter'] The measures must include as many dimensions as are provided, in the order given: For example, if you want to show values for both users as project leaders and users as key users, the dimension Projektbearbeiter must be specified twice: def get_dimensions(self): return ['Projekt', 'Projektbearbeiter', 'Projektbearbeiter'] And on the measure:
get_measures(self)	This method specifies which measures it will calculate. The return values must match the internal names on the measures. def get_measures(self): return ['OffersQuantity'] The generator can then be stored on all measures that are returned here.
initialize(self, startdate, enddate)	The first step is to run through this method. The variables startdate and enddate include the date range of the entire calculated period. Here you can preload the lists of used objects in a performance-optimized way. This way you have all relevant data for the entire period. In the method generate(), the data are then calculated per month.
generate(self, startdate, enddate)	The generate method will run as many times as it has months in the calculated period. The variables startdate and enddate always contain the first and last day of the month, i.e. January to June, for example, 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 returns a tuple of dimensions and a tuple of measures, in exactly the order stated above. The return value is not returned with return() as usual in Python, but with yield(): yield((project, project.projektleiter), (OffersQuantity,)) Using yield() means that there is not only one return value (and all the data must be laboriously combined into an array and then returned), but 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 sample code below). Currencies If you work with more than one currency in Vertec, you have to take this into account when calculating. For this purpose, you can optionally provide one currency per return line (example from the included InvoiceGenerator): yield ((project), turnover_tuple, project.waehrung) Vertec then takes care of the correct conversion into the key currency. The values are always shown in the key currency.
finalize(self)	Finally, the finalize method is run through. For example, objects that are no longer needed can be removed from the memory.

Sample code

# Generator for data related to project offers.
#
# 12.03.2020, Vertec AG, sth: Version 100: created

VERSION = 100

class OffersGenerator(object):
    """
    Calculate all offers 
    """        
    def get_dimensions(self):
        return ['Projekt', 'Projektbearbeiter']
        
    def get_measures(self):
        return ['OffersQuantity']
        
    def initialize(self, startdate, enddate):   
        """ 
        Preload all projects that could have offers in given date range
        """         
        # collect all corresponding offers
        offers = vtcapp.getwithsql("Offerte", "datum<=%s AND datum>=%s" % (vtcapp.sqldateliteral(enddate), vtcapp.sqldateliteral(startdate)), "") 
        if offers:
            # collect all corresponding projects        
            self.projects = vtcapp.getwithsql("Projekt", "bold_id IN (SELECT projekt from offerte where bold_id in (%s))" % offers.idstring(), "")
        else:
            self.projects = []
      
    def generate(self, startdate, enddate):
        """
        Calculate measures for given period. Uses the stored projects from the initialization step.
        """
        for project in self.projects:
            # calculate quantity for current month
            OffersQuantity = project.evalocl("offerten->select((datum<=%s) and (datum>=%s))->size" % (vtcapp.ocldate(enddate), vtcapp.ocldate(startdate)))
            
            yield((project, project.projektleiter), (OffersQuantity,))

    def finalize(self):
        pass

Standard BI generators

Vertec supplies the following generators as standard:

Calculate Data

The data is calculated periodically over one (or more) scheduled task during the night. The scheduled tasks can be found in the Settings > Customizing > Scheduled Tasks folder.

This process triggers all generators stored within the active measures and calculates the values for the period from January of the year before last 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 displayed in the output window.

A Vertec App does not have to be running, only a running Cloud Server is required. For the batch calculation, a user is defined on the scheduled task, which will be used for the calculation. This user must have administrator rights in order to be able to perform the calculations.

Cloud Suite

In the Cloud Suite, the time cannot be changed on the interface. The calculation is always done overnight. The exact time is shown under Next execution:

Start calculation manually

A BI calculation can also be initiated manually. There are two variants:

• Execute the scheduled task now: To do this, click the Execute now item in the context menu of the scheduled task:

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

Caution: The calculation is started directly in the running Vertec app, and not in a separate task runner process as in the timed execution. This should not be done while running, as the calculation takes a very long time and Vertec may be blocked.

• Initiating the calculation of one generator: The calculation can be initiated manually on the individual measures by selecting Recalculate measures in the context menu:

The generator stored on the measure is started and the corresponding measures are recalculated. The date interval can still be set in each case:

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

Once calculated values for a period of time and the relevant measures remain available in the system.

If the data is recalculated, existing values are deleted only for that period and the measures concerned, and the new values are inserted. All other values remain.

Python methods for calculating BI data

There are two Python methods for calculating BI data.

vtcapp.processbi("2018-01", "2020-02", "OffersGenerator.OffersGenerator")