How can we help?
Search for answers or browse our knowledge base
Integration Modes
Principles of data integration
The integration of data from a questionnaire compartment into a host table is done in one of two ways: integration by pivot or update on key fields:
| Pivot Integration | Key Fields Integration |
|---|---|
| Replacement of the complete existing records in the destination table by the equivalent records in the input data You can choose to add only new records | Modification of the values of certain fields for the records in the destination table equivalent to the records of the input data Additional records can be added |
| Detailed behavior | Detailed behavior |
Only common fields (i.e., with the same name) to the input data (from the compartment) and the host table are taken into account during integration. The equivalence between component names and table fields is case-sensitive.
The data from the compartment includes the compartment’s own fields, the fields of all parent compartments, the diffusion axes and the Calame columns (Calame columns are additional information that can be configured in the integration action).
The two modes of integration by pivot and update on key fields are designed in such a way that the integration actions, when properly configured, can be executed several times without generating duplicates in the host table.
Integration is always performed by integrating data from a compartment and all parent compartments into an intermediate/temporary table and then dumping this data into a final host table.
Choosing import by pivot or update on key fields
Choosing between importing by pivot and updating on key fields can be tricky, so here are some typical characteristics:
| Pivot Import | Key Axis Update | |
|---|---|---|
| Characteristics | Update of a set of complete records (all the fields present in the reception table are altered, the other fields return to the default value) | - Update column by column on certain records - No management of record deletions |
| To use when... | - There is no primary key in the data or the primary key includes too many fields (case of row patterns which are raw lists) - When the number of records can change between two integrations (more or less) | We want to preserve the column values (because they are the result of another process) |
| Pitfalls to avoid | Referential integrity by unnatural primary key (Counter ID or AutoNumber) in the reception table Forget the date in the pivot axes if the data is recurring Include the statement date in the pivot axes which will result in a historization of responses (the last response of each session) | - Key fields are not the primary key of the input table - Fields absent from the input table must have a default value when records are to be added - Deletion of lines to be plotted (unless processing the deletion of lines by a field marking the deleted lines) - When you only want to manage new recordings - Key fields including a pattern line number |
CAUTION: in all cases, the pivot fields or the key fields of the update must not contain NULL values (empty strings or no value in the questionnaire cell)
Non-natural external keys cannot be managed directly by the integration action.
In both modes, the import can only really be used if the destination table has a structure close (in terms of fields) to that of the compartment (the input data table is built on the template of the host table).
Intermediate integration tables and views can overcome this limitation
Points to consider
The integration of the results of the questionnaires into the database is usually the end of the project. It may, therefore, seem delicate because all the elements involved must be correctly defined (questionnaire, definition of host tables for the integrated data, etc.).
– think about testing successive campaigns, when the campaigns are recurrent (month and the following month, for example).
– consider testing successive integrations: for the same campaign, the action should be able to be executed twice or more by the manager to integrate the corrective responses from the correspondents
– if two compartments of the same questionnaire integrate data in the same table, the integrations must be defined even more carefully: it is generally advisable to check the order of integration of the compartments and to determine the axes that allow the data of these compartments to be easily separated from each other
– Do not hesitate to use all constraints in the host tables (non-nullity, unique indexes, referential integrity) to avoid integrating incorrect data.