Using an intermediate integration table and an intermediate integration view

These objects are used to transform the raw data from the questionnaire before integrating it into the host table.

The creation of the intermediate table and the intermediate view are the responsibility of the developer in the client database.

When these objects are not used, the data from the compartment is integrated into a temporal table created by GTServer using the reception table as a template. The data in this temporal table is used as the basis for the integration operations described above.

When using these objects, the data from the compartment is first integrated into the intermediate integration table (designed by the developer). The data from the integration intermediate view (designed by the developer and using data from the intermediate table or other tables) will serve as input for the integration operations (update by pivot or on key fields).

The intermediate integration table is purged of all data before the records from the qst compartment are inserted.

Since the view can be based on the data in the intermediate integration table and associate data from any other table or view, a number of data transformations and row deletion operations become possible.

Some specific cases of use:

• Renaming fields

Fields from the questionnaire do not have the same names in the host table.

• Deleting rows in the data from the compartment

“Deleting rows is done simply by excluding certain rows (e.g., using a WHERE filter) from the intermediate integration view; these rows will not be moved to the final table.

Records from patterns where the first and only row is blank.
We do not want to add these “empty” records to the data.

Records from the same compartment are destined for different host tables according to a compartment criterion.

• Integrating only  data from the current month

When you only want to integrate data from the current month and the transposition filter (such as Date=M0 with an anonymised date to select only the current month) cannot be used.

• Transforming the data

The SQL functions of the DBMS can be used in the view to perform transformations and conversions on the data.

• Reconstructing an unnatural external key

If the host table uses an unnatural external key (numeric id, counter or sequence), two options are possible:

• Produce the id in the questionnaire.

For example, in the case of a drop-down list, the items in the drop-down list are supplied by a pattern containing the Id in the database for each item. A check with a search function allows the id of the chosen item to be retrieved from the list.
If new IDs need to be created, it is preferable to create them at the time of integration. The questionnaires being desynchronised, it will not be possible to guarantee the uniqueness of the Ids created in the questionnaire alone.

• Produce the Id at the time of integration

A secondary key is required (item or combinations of items) to retrieve the corresponding Id from the reference table.

The view will associate the Ids of the reference table with the labels or values from the compartment data. The values stored in the final table will only be the Ids.

If data in the questionnaire needs to define new Ids, these will first be integrated into the reference table, the integration into the foreign key table will be carried out in a second step (the scheduling of the integrations into the different tables is done via the pop-up menu when editing the integration action in GTClient)

Purging the host table

The option to purge the table deletes all data from the host table before integration.

This option is useful when you want to store data from one compartment in a table before integrating it with other data when integrating another compartment.

The case of stacked patterns (two patterns whose contents will be dumped into the same host table) is a typical example of use.

Specifying the integration of Excel errors

When a formula result is integrated into the database, via a GTCONTROL (or when an error value is entered directly in an input box without constraint), the developer can specify the way in which Excel errors are transformed into the database:

• Empty: errors are transformed into a NULL (default behaviour).
• Excel string : the Excel string of the error will be integrated.
• Excel code: the Excel code of the error will be integrated.
• Fixed string: all errors are transformed into a string specified by the developer.

Using pivot tables and key tables

Pivot tables and key tables are generally used when you want to reduce the rights of the client database user used by GTServer.

In a standard integration, the client database user must have table creation and index creation rights on the created tables.

By combining the intermediate integration table and the pivot table (or the intermediate integration table and the key table), the client database user only needs SELECT, INSERT, UPDATE rights on the client database tables.

During an integration, a temporal table is created to dump the data directly from the compartment (the intermediate integration table is substituted if specified). The key data is then reduced in a temporal table (the construction of this table depends on the integration mode).

In the case of integration by pivot, the pivot table is used and must contain the pivot axes (a single index on the pivot axes is recommended).

In the case of integration by update on key fields, the key table is used and must contain all the input fields (a single index on the key fields is recommended).