StaR Installation

Structural changes in the underlying database tables require a complete removal of all existing StaR tables for all older versions. We recommend dropping all existing tables, however if this is not desired, then please see section 6.3 for how to migrate data to the new tables.

It is also important to note down that the recent security vulnerabilities due to the log4j dependency of version 2.10.0 in previous StaR versions were overcome thanks to the updates of this dependency to version 2.16.0 and 2.17.1 respectively.

The directory <path-to-compass>/config/statisticalmodule contains one configuration file: statisticalmodule.properties

The entries in this file configure the target database that is used by StaR to store the data extracted from the COMPASS system.

The following entries in the file are required:

According to the designated target DBMS the configuration must be adapted to that environment.

Depending on your requirements a decision regarding the creation of the target db tables must be made. The default behaviour when creating the target tables is triggered by the option “hibernate.hbm2ddl.auto=<value>”.

Seven possible values may be set:

Setting this to update induces the following behaviour:

Setting this to create induces the following behaviour:

The DB specific DDL can be copied over from the tools output (console, logfile). Gen Re does not provide DB specific support regarding DDL issues nor provide a DDL for the DBMS.

The option hibernate.dialect specifies the dialect to be used by StaR when writing extracted data to the target database. The entry must match your target database system i.e. when you use Oracle9i please use the corresponding Oracle9i dialect. Following are the dialects of most common databases in the market;

For further dialects supported by Hibernate 5.4 see chapter 9.

The option hibernate.connection.driver_class=com.mydatabase.Driver specifies the JDBC driver class for the target database. If you use the same database that is used by COMPASS you can copy & paste the entry value from the COMPASS configuration file:
<path-to-compass>/config/server/compass.properties

In all other cases please refer to the documentation of the database system vendor.

The options hibernate.connection.username and hibernate.connection.password should be filled with the username and password for the database user. The user must have the following access rights for the designated target database/schema: Schema level: drop, alter, and create tables Table level: all rights to insert, update and alter data

The option hibernate.target.url is required for connecting to the database and also persisting the entries into there.

The option hibernate.target.schema has a relationship with the hibernate.target.url. This option is not required to be placed in the properties data. The name of the target database to be created by StaR is determined by this property. This option is recommended to be set however if it is not set then the target database will be determined by the option hibernate.target.url.

The option hibernate.connection.url specifies the JDBC connection string for the target database. If you use the same database that is used by COMPASS you can copy & paste the entry value from the COMPASS configuration file:
<path-to-compass>/config/server/compass.properties. When StaR is run, it will firstly access this data

The option data.encoding refers to the encoding of the CaseData and ResultData XML interfaces. It is an optional field however in the provided section above, it can be seen that it is set to the value of ISO8859-1. Generally the parsed XML interfaces is of Unicode format and in such cases the encoding does not need to be set explicitly and can be left blank, however for the format ISO8859-1 it must be set to this value otherwise an exception will be thrown at runtime.

Technically StaR uses the Object Relational Mapping (ORM) tool named Hibernate^[2] and also JDBC^[3]. For a more detailed explanation of the properties please refer to the product documentation.

The logging of StaR is configured using the file
<path-to-compass>/config/log/Starlog4j2.xml
The configuration can be changed but comes with a useful default configuration. For possible modifications please refer to the log4j – documentation.

To start StaR there are two recommended approaches:

During its execution StaR creates various tables and stores statistically relevant data in these tables.

Call - Parameters:
Upon calling the application the compass.properties

Mandatory parameters:

-systemids
the system id parameter, refers to the source schema where case and result datas are contained. The case and results are found in the tables c40_casedata and c40_resultdata^[4].

-subsystems
the subsystem id parameter.^[5]

Example of mandatory parameters:
-subsystems subsystem -systemids prod1

Please note that only one system id and subsystem id can be provided when calling the application.

Optional parameters:
-properties
path to an alternative database configuration property file
(default value: ./config/statisticalmodule/statisticalmodule.properties)

-cleartargetdb {true|false}
TRUE forces the creation of new tables. In this case a DROP DATABASE <db_name> statement is executed. The existing database will be dropped and a new one along with the tables is created. Default is FALSE. Do not use this parameter unless required.

-deleteData {true|false}
TRUE forces the deletion of all data in all tables but does not change the table structure and/or recreate the database/schema. Default is FALSE, which implies that data that may be converted during a previous run of the tool will not be deleted. If this parameter is set true, a series of DELETE FROM db_table statements are executed and the existing entries will be removed from the database tables.

Note that this parameter is overshadowed by the parameter cleartargetdb. If it is set to true, this parameter will then be ignored.

-acceptEmptyResult
accepts cases with empty results. Default value is false. This parameter is incompatible with the parameters dateBegin and dateEnd, which are based on the AssessmentDate in the result-XML.

-dateBegin
Optional parameter: Specifying a begin date in order to reduce the number of processed applications.
Date format: yyyyMMdd, e.g.: 20161231

-dateEnd
Optional parameter: Specifying an end date in order to reduce the number of processed applications.
Date format: yyyyMMdd, e.g.: 20180101

Example of mandatory and optional parameters:

VM options:
-logconfig
path to an alternative log4j xml configuration file
(default value: ./config/log/statisticalmodulelog4j.xml)

The data structure used by StaR resembles the COMPASS Input Interface and the COMPASS Result interface.

Data tables:
CASEANDRESULT
CASEATTRIBUTE
CASEOBJECT
CASERESULT
CASERESULTATTRIBUTE
CASERISK
MISSINGINFO

Join tables:
CASEOBJECT_CASEOBJECT
CASEOBJECT_CASERISK
CASERESULT_CASERESULTATT
CASERESULT_MISSINGINFO
CASERISK_CASERESULT

Since only assessed cases are inspected by StaR some information from both the Case-XML element and Result-XML element are merged into one table called “CASEANDRESULT”. The table contains the following fields:

Every case that has been assessed leads to an entry in this table. Many basic queries to the system can be done with this table. For instance you could:

In general, every xml element of the case/result xml will be mapped into a specific table. Every xml attribute will be mapped as a column of a table.

The xml element CaseObject leads to this entry in the table CASEOBJECT:

The first field is an automatically generated id. The other fields represent the xml attributes of the CaseObject.

The nested CaseAttribute elements can be found in the table “CASEATTRIBUTE”. (In order to keep the example readable the columns “CASEATTRIBUTE_UNIT” and “unitName” are not shown in the table.)

Both tables must be joined using the field “CASEOBJECT_HJID” in the table “CASEATTRIBUTE” and the field “CASEOBJECT_HJID” in the table “CASEOBJECT”. The example above shows that both entries ‘belong’ to the CaseObject with the Primary Key (PK) 7.

We have mentioned previously that we have changed table structures and column mappings. There were several purposes for these structural changes. These can be outlined as filtering out insignificant data, preventing loss of information and achieving an easier to understand database column – data attribute mapping.

One of the most important improvements in the new StaR system is that the empty CaseAttributes are filtered out and not persisted in the target database.

The this empty CaseAttribute Period can be provided as an example. Even though there is a CaseAttributeValue available within the CaseAttribute, it can be ignored as it only contains Source information and no significant values.

We have also introduced several new changes in data persistence scheme. For instance, consider the following CaseAttributeType.

In the old StaR creates the following entry for this CaseAttribute.

It can be easily inferred that there are inconsistencies in the column names, firstly we updated the column names so that they are all written entirely in capital letters, furthermore we also changed how the attribute values are mapped to the columns.

The new StaR creates the following entry for the given CaseAttribute.

All of the SingleValue attribute values are now mapped to the corresponding columns with the same names and thus persisted in the target database. The column mappings have now become clearer and do not lead to confusions.

A similar improvement regarding the CaseResultAttributes was also implemented. The following CaseResultAttribute can be provided as an example;

This CaseResultAttribute was persisted by the old StaR in the target database as follows;

The new StaR persists this CaseResultAttribute in the target database as follows;

There were two improvements made here; firstly, the column CASERESULTATTRIBUTE_UNIT2 was renamed to CASERESULTATTRIBUTE_VALUE2. Furthermore, as in the following case of CaseAttributes, the attribute values were mapped to the columns accordingly.

Another example to the improved column mappings scheme in new StaR can be provided by the PeriodValues.

This PeriodValue is persisted by the old StaR in the target database as follows;

It can be seen that none of the provided Date and Duration values were written to the database, this might indicate a loss of important data. This issue was solved in the new StaR and all of the provided Date and Duration values are now persisted into the target database.

The display format is <dd.mm.YYYY;dd.mm.YYYY;value;UnitName>. The value of the column CASEATTRIBUTE_UNIT is always set to period.

A new feature was added related to several specific CaseObject elements and their CaseObjectRelations. In two specific cases new child-parent relationships are defined.

One of these cases occurs when a CaseObject with Disease Consolidated exists in the result interface. This CaseObject is special because it is only available in the result interface and thus the new this object will now be attached as a child to the Assured Person.

As it can be seen, these CaseObjects are identified with assigned hjid’s 9 and 23 respectively. Now an entry in the caseobject_caseobject table exists with these hjid’s.

The other scenario is more specific; it indicates that if CaseObjects of type Disease and Disease Consolidated are both available in the result interface and in the RiskCaseObjectRelations, then a new child-parent relationship is defined between them. In the following table, we see that both case objects exist in the input interface with id Tess_NI_01_EM. These case objects are identified with the assigned hjid’s 13 and 23.

In the caseobject_caseobject table, now a parent-child relationship is available between these two case objects.

In general, it is recommended to drop existing tables and then let StaR generate the new tables and data, however there might be cases where it is desired to keep existing data and migrate them into the new tables generated by the StaR.

The general rule of thumb is to change the table structures and make column names of the old tables to match exactly with the column names of the new tables to enable a smooth data migration. We have already stated the data structures of the new tables in section 5.1. Here is the CASEANDRESULT table generated by the old StaR;

When compared with the new tables, it can easily be seen that there are significant changes in the column names, firstly in the new StaR, the column names were made consistent and written all in capital letters with underscores in between, however the column names of the old tables were inconsistent. In this specific case where we want to migrate the data from the old CASEANDRESULT table to the new one, the following SQL Query must be executed;

For each table a separate SQL must be created and executed, so that the table structures are completely compatible with each other. It is also important to remind that the tables explanation and explanation_caseresult were removed, therefore if these tables are existing, they must be dropped and do not need not to be created again.

After changing the table structures, the dump function can be executed to create a backup of the data. Please keep in mind that to calculate the required disk space, approximately 40 kilobytes per case and result are estimated. For instance, let us assume that we have a database namely ‘compass_stats’ and a MySQL DBMS, for creating a backup of this database, the following command can be executed;

As a result of this command an SQL script with the same name is generated on the indicated directory. If this SQL script is executed against the existing database, then we would have successfully migrated the data from existing old StaR tables to the new ones. There are several options for executing the SQL script, either the script can be executed over the CLI of the DBMS or a database visualizer tool can be employed, another option is to create a batch file for the execution of the script. The preferred manner is up to the choice of the operator.

Finally, please refer to the corresponding database dump function of the preferred system of choice when creating a backup, the new StaR employs Hibernate 5.4.20.Final for generating the tables, in the ongoing sections we have enlisted the compatible dialects.

In case of a MySQL database the resulting data looks like this, if there are three cases in the database:

The objective is to determine which results are associated with Assured Persons (AP). The AP information is wrapped into a “CaseObject”. Therefore we need to select data from the “caseobject” – table. Furthermore we need the result for the AP. This information is stored in the table named “caserisk”. The relation between these tables is determined by the M:N join-table “caseobject_caserisk”. Since the “caseobject” – table contains not just AP related information we have two options:

The final SQL for the first option can look like this:

In the “where” – clause we limit the results to entries that have the CASEOBJECT_TYPEID = “27”. This typeid marks AP objects.

To implement the second option, simply leave out the “where” – clause that puts a constraint on the CASEOBJECT_TYPEID. The resulting data must be filtered to only show AP relevant entries. This can easily be done in Excel using the standard filters.

This example can be found in the Excel Workbook file named “AP_Results.xls”.

To retrieve all AP results and their disorders the following statement can be used.

This example can be found in the Excel Workbook file named “AP_with_disorders.xls”

This report incorporates the tariffs and their resultlevels.

This report shows the requested information on application level. In the example below are many occurrences of consolidated and subsumed GPR’s. On application level three GPR’s were finally requested.

This report retrieves all potentially requested information for all tariffs. In this example the typeid’s of the requested information are restricted to 1 (GPR), 9 (questionnaire), 10 (medical examination) and 20 (document).

This query encompasses listings of all occupations, drugs, disorders, nationalities or pursuits which have been found in applications, but were not found in the COMPASS system.

CaseAttributeTypeIDs:

45 - Foreign travel
61 - Nationality
115 - Disorders
163 – Sport/Hobby
209 - Occupations
352 - Drugs (Medication)