What's New in Release 3.0.0

With the release of i-refactory 3.0.0 we've made a variety of stability, performance, functional and usability improvements.

New features summary

Physical deletes

When creating a delivery on a logical validation model we now support the option to treat "removed records" as either logical deletes or physical deletes. For deliveries on a generic data access model we now support the option to allow for physical deletes.

{warning} Physical deletes are executed in a single transaction and may have impact on system resources.

Instead of the default behaviour with logical deletes where we never physically remove data with the physical delete option enabled we do physcially remove data from the fact store. We do, however, only delete the contextual data. Business keys, registered in the anchors of the fact store will not be physically deleted but marked as such. With this feature you now have the ability to delete sensitive data if required to do so. We also still have a full audit trail of this feature which give you the ability to prove that a physical delete actual took place on a given point in time, for a given business key for a specific delivery of a third party.

{info} A delivery with physical delete enabled must be completed and persisted in the fact store before you can create a new delivery on a logical data model. This in contrast to a logical delete scenario where a new delivery can be created as soon as the data in the logical validation layer is accepted.

Cleanup violated constraint log

For a completed delivery you can now request to physically delete all registered logging in violatedRecord and violatedConstraint. The log of violations counts per constraint will not be removed.

You need to have the role of "DataManager" to be able to clean the log. The property delivery.logCleanupInfo will register the status of the cleanup process.

Trusted and Untrusted setting

In a logical validation model we store the difference between subsequent deliveries. Our logical validation layer acts as an intermediate change log buffer allowing for parallel loading of deliveries. This buffer however also enables us to optimize the data load to the central fact store. Despite the fact that delivered entities are delivered as a complete set we do have the ability to process the delivered set of records as a delta load to the central fact store thereby significantly improving the load performance to the central facts layer.

We are also capable of reasoning if the data stored in a logical validation entity can still be trusted. If we do not trust the data anymore we will simply apply the isComplete setting of the delivered entity when loading to the central fact store.

However, in some situations, and more specifically in case of deliveries with context based enddating we are sometimes not capable anymore of correctly determining the trustfullness of the registered data of a logical entity in the logical validation model. This is only true if someone deleted or modified records.

So if someone (a DBA) decides to remove data from a logical validation entity and it is known that due to this action we cannot trust promoting a full delivery on a logical entity to a delta delivery when loading to the central facts layer we introduced the capability to mark an interface or entity in a logical validation model as untrusted. If the computed conclusion on entity level is false we will never promote a full load to a delta load but will honour the full load setting at the cost of decreased performance.

Task runs

We've introduced a new concept in our metadata model: TaskRun. In our previous release we weren't capable of managing state other than an update of a target entity. With our new approach we now have the ability of executing many kinds of tasks and keep track of their state, composition and dependencies. An example of a specific non target entity related task we needed to execute in our previous release is the cleanup of intermediate storage used during the processing of a delivery. If this cleanup failed for some reason we needed to stop the iRefactory server because we couldn't register the failure state of these tasks.

Application Changes

Performance enhancements

1. Retrieving the delivery statistics in the web app was slow with the increasing number of completed deliveries. We have added an additional index and tuned the query statement. Retrieving delivery statistics should now return results within acceptable boundaries (less than a few seconds).

2. The performance of validating the basic constraints: primary key, datatype, mandatory, enumeration, attribute value, entity record has been improved. We typically see an improved of factor 2 to 4.

3. We now store the intermediate results of context based filters more often. This reduces the complexity of long join paths when context based enddating should be applied but at the cost of more intermediate storage (the results are not stored in temp space). We do however expect an overall improvement of the throughput as a result of this measure.

4. We decided in a number of cases to first calculate a difference set, store this difference set as an intermediate result and use it in next steps when processing the entity updates for a delivery. It is hard to predict in which cases this will result in higher throughput and less resource usage. With this measure we do however expect a more predictable throughput due to reduced complexity of the execution plans the database engine needed to evaluate and generate.

5. We've improved the function of converting a database query result to a JSON document in our Rest API.

6. The import of metadata is significantly faster, especially for completely new data models.

7. From this release, we only store the derived constraint metadata for a logical validation model (attribute datatype, attribute mandatory, ...). This reduces the amount of unnecessary metadata in the database.
8. The web application monitor and delivery statistics page retrieves and renders data much more efficient.
9. We changed the indexes on the tables for basic and set constraint validation. For the basic constraint validation tables the index is now a nonunique clustered index. For the set constraint validation tables the index is now a clustered unique index. This should reduce the sort overhead involved in joining.
10. We from now on use set based inserts in our application server regarding the maintenance of metadata. This reduces the amount of network roundtrips and results in higher throughput and reduces resource consumption.

OAuth2 server

1. You now have the ability to create you own private/public key pair and configure the location of these key pairs when using our openID server.
2. You now have the ability to specify an expiry time per client besides the default expiry time for all clients.
3. The implementation is from now on based on OAuth2. In the next major release the OpenId Connect related implementation will be removed.

Bi-Temporality in a logical validation model

We have changed the behaviour of determining if a record for a bitemporal entity is a removed record or a changed record. In previous releases a bitemporal record in the logical validation model was considered removed if you did not deliver a record on the valid time start anymore (or when specifically delivered in a delta delivery). From now on we will mark records as appended when a delivered timeline overlaps with an existing timeline. This behaviour was already implemented on the central facts layer. Due to the fact that we now support physical deletes in which we need to recursively delete overlapping timelines we needed to align the behaviour of handling bitemporal context in a logical validation model with the behaviour of handling bitemporal context in a central facts layer.

Filtering rows

If a delivery is created where context based enddating applies we will filter rows delivered in a child entity which do not match with the parent context entity. For example: Given a datamodel with entities PART, SUPPLIER and PART_SUPPLIER where PART_SUPPLIER is a dependent entity on PART and SUPPLIER. And given a delivery with a delta load on PART and a full load on PART_SUPPLIER (partial delivery). And given the fact that the delta set for PART only contains a single record where PART.NBR === 1. And given the fact that the full set on PART_SUPPLIER contains a single record where PART.NBR === 2 && SUPPLIER.NBR === 1. We will filter the row in PART_SUPPLIER because it doesn't match with the delivered set on PART (no match between PART_SUPPLIER.NBR and PART.NBR).

This filter is required as it will or could result in loading errors on the fact store. And if a delivered record cannot be stored in the fact store we shouldn't store it in the logical validation model as well.

Reference lookup in central facts context entities

Despite the fact whether a reference column in a central facts entity was mapped to from a logical validation model we always executed a join lookup for each reference column.

From this release we will only generate a lookup to a parent entity if a reference column is mapped to.

Default value expressions in central facts models

If a default value expression is specified on an attribute of a central facts datamodel and no default value expression is specified on the attribute in a logical validation model which is the source of an attribute mapping we from now on do not apply the default value expression of the central facts datamodel attribute but we simply use the value as specified in the logical validation model.

Constraint violations on bitemporal data

If a constraint violation is detected on a record in a bitemporal table and this row should be skipped we will skip all other delivered bitemporal records belonging to the same business key.

The reason for doing this is that we cannot no longer trust the consistency of the delivered timelines if one of them fails.

The implicit skipped rows are not registered as constraint violations, neither in the constraint violation count, neither as a violatedRecord and neither as a violatedConstraint.

Creating an actual view for set based constraint checks

For set based constraint validation we currently append the delivered set of rows with the existing actual rows already stored in the logical validation layer. We append rows if:

1. Rows are skipped because of a constraint violation with skipRow = true.
2. The data provided is specified as a delta load for the specific logical entity.

In our previous release, for bitemporal context, we simply added rows with an equality lookup on the primary key.

From this release on now we will append rows for bitemporal data with an overlaps query.

Non surrogated key columns in a dependent entity

For dependent entities prior to this release it was mandatory to register the non-reference primary key columns always after the reference related primary key columns. From now you can register these primary key columns on the desired position in the anchor. The code generator will resolve the key lookup properly.

Deadlocks in SQLServer 2019 CRUD transactions

Testing our software on SQLServer 2019 resulted in deadlock issues during CRUD transactions. This is caused by SQLServer merge joins where more rows than strictly necessary are locked in different order than another concurrent request.

We've added 2 hints on these queries: inner loop join and force order. This fixed the deadlock issue.

Dependency management

From this release on we will check on inconsistencies between several application dependencies.

1. If a meta model export file is passed as input to our import API we will check if the metadata export file is supported by the current running application version. For each application version we now keep track which metadata version is supported. If versions do not match we will raise an error.
2. If the DDL version registered in the database does not match the required DDL version of the application server we will raise an error indicating the application server you would like to start is outdated.
3. If for some reason registered metadata for a logical or generic data access model is not up to date with the supported metadata versions of the application server you cannot create new deliveries. Trying to do so will raise an error.

Web App Changes

Access to menu's and actions

Depending on the roles of the authenticated and authorised user menu items and actions will be enabled/disabled accordingly.

Delivery statistics

1. Anyone with at least a DataViewer role has now access to the delivery statistics page. However, in case of constraint errors viewing the sample of records on which a constraint violated is restricted to those users with DataManager role. We consider viewing record information as a possibility to view sensitive data. For that reason only users with this role are allowed to view the details of a source record.
2. On de the delivery statistics page you now have the ability to delete all registered violatedRecords and violatedConstraints with the "erase" button on the left hand side of each delivery. The count of constraints violations however is not deleted.
3. Zooming and panning the graph is now more consistent.
4. The graph with elapsed time and rows processed not takes into account the logarithmic scale and 0-values (which cannot be represented in a logarithmic scale).

Tabpages on delivery and interface

1. We have changed the layout of creating deliveries. For a logical model and generic data access model we now have 2 separate tab pages.
2. Settings on interface and entity are now accessible through the Interface menu. For each interface model you can now view a list of entities and set some specific properties per subtype.

Reference lookup

The look and feel of reference lookups is improved.

Rest API

GET Changed

1. /acmDatadef/interface and /acmDatadef/deliverableInterface

   Removed properties:

   • ddlExportVersion
   • metadataExportVersion
   • dbmsImplementationVersion

   New properties:

   • metadataVersion: is automatically set in the Powerdesigner UAM modelling components and contains the semantic version of the metadata export interface.
   • modellerVersion: is automatically set in the Powerdesigner UAM modelling components and contains the semantic version of the Powerdesigner UAM Modeller release.
   • dependsOnRuntimeVersion: is automatically set in the Powerdesigner UAM modelling components and contains the semantic version of the i-refactory runtime engine dependency.
   • logicalValidationInterfaceTrusted.

2. /acmDatadel/delivery and /acmDatadel/activeDelivery

   New properties:

   • logicalValidationDeliveryDeleteType
   • genericDataAccessDeliveryDeleteAllowed
   • logCleanupInfo

3. /acmDatadef/baseEntity

   New properties:

   • logicalValidationEntityTrusted
   • logicalValidationEntityTrustedConclusion

4. /acmDatalog/violatedRecord

   You need to have the DataManager role to execute this request.

PATCH Changed

1. /acmDatadef/interface

   You can set the property logicalValidationInterfaceTrusted on a logical validation model.

2. /acmDatacon/database

   Ability to change the value of description.

POST Changed

1. /acmDatadel/delivery

   New properties:

   • logicalValidationDeliveryDeleteType: indicates if we should treat "removed records" logically or physically. Default value is "LOGICAL".
   • genericDataAccessDeliveryDeleteAllowed: indicates if it is allowed to physically delete records with the CRUD interface on the database. Default is False.
   • logCleanupInfo: Is the process of cleaning the log active or executed.

GET Added

1. /acmDatalog/taskRun

   Get data for a taskRun. A taskRun is the execution of a task: a load to a technical staging table, a set of constraints validations, a load to a logical validation table, dropping temporary tables, ...

2. /acmDatalog/taskRunDependency

   Get data for a taskRunDependency. A taskRunDependency registers the taskRuns predecessors and successors and as such represent a dependency graph.

POST Added

1. /acmDatadel/logicalStagingDelivery(...)/cleanConstraintViolations

   Deletes all logging in violatedRecords and violatedConstraints for the given delivery.

PATCH Added

1. /acmDatadef/baseEntity

   Ability to change the value of logicalValidationEntityTrusted.

2. /acmDatalog/taskRun

   Set properties of a taskRun: the statusCode, rowAfter, messageNbr, messageText.

Deprecated Rest API's

The following Rest API calls are deprecated. We strongly encourage you to use the new endpoints instead.

NodeJS

The i-refactory runtime engine runs on NodeJS LTS version 10 and 12.

Config file changes

driverConnectionProperties configuration

As a result of upgrading to the latest version of the driver which is responsible for connecting to the SQL Server database a small change in the configuration is required with regard to the connection properties.

The configuration regarding driverConnectionProperties:

{
    "driverConnectionProperties": {
        "server": "",
        "userName": "",
        "password": ""
    }
}

Should be changed to:

{
    "driverConnectionProperties": {
        "server": "",
        "authentication" : {
            "userName": "",
            "password": ""
        }
    }
}

httpRestApi configuration

Our Rest API requires a valid OAuth2 token in each request. For security reasons this token should be encrypted. Our Rest API server needs to know the location of the public key and the signature algorithm used to encrypt the token. You could use your own OAuth2 compliant server to grant access tokens (for example Windows Active Directory). To reflect these changes we slightly changed our configuration for our Rest API.

The configuration regarding httpRestApi:

{
    "httpRestApi": {
        "enabled": true,
        "openIdPublicKey": "crypto/openId.pem",
        "https": {
            "port": 3000,
            "host": "localhost",
            "key": "crypto/ssl.key",
            "cert": "crypto/ssl.crt"
        }
    }
}

Should be changed to:

{
    "httpRestApi": {
        "enabled": true,
        "https": {
            "port": 3000,
            "host": "localhost",
            "key": "crypto/ssl.key",
            "cert": "crypto/ssl.crt"
        },
        "accessToken": {
            "publicKey": "crypto/key_public.pem",
            "signatureAlgorithm": "RS256"
        }
    }
}

{info} If you choose to use the i-refactory OAuth2 server the generated publicKey should be encrypted with the RS256 algorithm. Our authorization server always use the RS256 algorithm to encrypt tokens.

uiServer configuration

To eliminate confusion which kind of authorization server is used for our Web Application we've decided to change the property name opendId to authorizatioinServer. And we've removed the openIdResourceUri property (which is a reference to the URI for requesting an openId token) because we do not need an openId token but only an valid OAuth2 token.

The configuration regarding uiServer:

{
    "uiServer": {
        "enabled": true,
        "https": {
            "port": 3002,
            "host": "localhost",
            "key": "crypto/ssl.key",
            "cert": "crypto/ssl.crt"
        },
        "apiUrl": "https://localhost:3000",
        "openId": {
            "clientId": "i-refactory-ui",
            "authorizationEndPointUri": "https://localhost:3003/authorize",
            "tokenEndPointUri": "https://localhost:3003/token",
            "openIdResourceUri": "https://localhost:3003/openid"
        }
    }
}

Should be changed to:

{
    "uiServer": {
        "enabled": true,
        "https": {
            "port": 3002,
            "host": "localhost",
            "key": "crypto/ssl.key",
            "cert": "crypto/ssl.crt"
        },
        "apiUrl": "https://localhost:3000",
        "authorizationServer": {
            "clientId": "i-refactory-ui",
            "authorizationEndPointUri": "https://localhost:3003/authorize",
            "tokenEndPointUri": "https://localhost:3003/token"
        }
    }
}

openIdServer configuration

To eliminate confusion of the intent of the i-refactory authorization server we've decide to change the property name opendIdServer to authorizationServer.

The properties privateKey and publicKey no longer have a default value and should be specified explictly. This prevents security breaches where for example in our releases prior to 3.0.0 we created a private/public key pair and you accidently used this key pair without even knowing.

The last change is related to registering client application. A system client should be able to request an accessToken without an explicit login process. A system client should have the ability to provide it's username/password directly. In OAuth2 this grant type is called: clientCredentials. For system clients you should therefore add the clientCredentials value to the grants array.

The configuration regarding openIdServer:

{
    "openIdServer":
    {
        "enabled": true,
        "https":
        {
            "port": 3003,
            "host": "localhost",
            "key": "crypto/ssl.key",
            "cert": "crypto/ssl.crt"
        },
        "clients": [
            {
                "clientId": "i-refactory-ui",
                "clientSecret": null,
                "redirectUri": "https://localhost:3002",
                "grants": [ "authorization_code", "refresh_token" ]
            },
            {
                "clientId": "exampleNonWebClient",
                "clientSecret": "12345",
                "grants": [ "password", "refresh_token" ]
            }
        ],
        "users": [
            {
                "id": "info@i-refact.com",
                "username": "administrator",
                "password": "abcd123",
                "email": "info@i-refact.com",
                "roles": [ "DataViewer", "SystemManager", "DataManager", "DataOperator", "Developer" ]
            },
            {
                "id": "exampleUser@i-refact.com",
                "username": "exampleUser",
                "password": "12345",
                "email": "exampleUser@i-refact.com",
                "roles": [ "DataViewer", "SystemManager", "DataManager", "DataOperator", "Developer" ]
            }
        ]
    }
}

Should be changed to:

{
    "authorizationServer": {
        "enabled": true,
        "https": {
            "host": "localhost",
            "port": 3003,
            "key": "crypto/ssl.key",
            "cert": "crypto/ssl.crt"
        },
        "privateKey": "crypto/key_private.pem",
        "publicKey": "crypto/key_public.pem",
        "clients": [
            {
                "clientId": "i-refactory-ui",
                "clientSecret": null,
                "redirectUri": "https://localhost:3002",
                "grants": [
                    "authorization_code",
                    "refresh_token"
                ]
            },
            {
                "clientId": "exampleNonWebClient",
                "clientSecret": "12345",
                "grants": [
                    "password",
                    "client_credentials",
                    "refresh_token"
                ],
                "roles": [
                    "DataViewer",
                    "SystemManager",
                    "DataManager",
                    "DataOperator",
                    "Developer"
                ]
            }
        ],
        "users": [
            {
                "id": "info@i-refact.com",
                "username": "administrator",
                "password": "abcd123",
                "email": "info@i-refact.com",
                "roles": [
                    "DataViewer",
                    "SystemManager",
                    "DataManager",
                    "DataOperator",
                    "Developer"
                ]
            },
            {
                "id": "exampleUser@i-refact.com",
                "username": "exampleUser",
                "password": "12345",
                "email": "exampleUser@i-refact.com",
                "roles": [
                    "DataViewer",
                    "SystemManager",
                    "DataManager",
                    "DataOperator",
                    "Developer"
                ]
            }
        ]
    }
}

Upgrade

Upgrade Notes

Bug Fixes