CONTEXT The company has multiple subsidiaries that use different software systems—or the same software—but store data in different databases.
TASK
The company wants to collect all sales data in a single database for consolidation and BI analysis.
This GRAVITY use case corresponds to the examples presented in the CONSOLIDATIONS section of this chapter.
In the examples below, we focus exclusively on how the OUTPUT SQL operator is used.
Example use cases of the OUTPUT SQL operator
Interaction with source data
In this example, we present a configuration that involves interaction with source databases.
Standardization When data is consolidated from multiple sources, unique identifiers need to be standardized—that is, adjusted to avoid duplicates—during the write operation.
Input data stream When saving each record, it is possible to set a data retrieval flag in each source database. This allows you to build queries in INPUT SQL operators with a condition that restricts the retrieval of the input data stream exclusively to records that have not been retrieved before.
In the illustration below, we indicate that in this case the ACTION FOR THE SAME RECORD setting needs to be set to ‘Pass’.
On the top toolbar of the table designated as the target table, select the STANDARIZATION action (see the illustration below).
After selecting the STANDARIZATION action, the system will prompt you to provide the source identifier. Note that the standardization dictionary may contain multiple data sources; therefore, you must assign a name to your source or specify an input data bus bit that contains a unique source name.
To set a flag on the source data, you must select the SETUP INPUT action in the SQL ACTIONS area (see the illustration below) for each source.
To complete the configuration of the INPUT SQL action, you must specify the following (see the illustration below):
INPUT→ specify the INPUT-type operator from which data is loaded into the data bus
INPUT TABLE → specify the source table from the INPUT operator that you want to update
COLUMN FOR UPDATE → specify the column in the source table that will be updated (where the flag is set)
VALUE FOR UPDATE → specify the value to be written (the flag value)
ID SOURCE RECORD → define how the record is identified by specifying a unique column and the corresponding column from the input data bus of the OUTPUT SQL operator
You can also implement the task differently: in this case, the process is fed by as many INPUT SQL operators as there are data sources, but the data is merged into a single data bus (for example, using the MERGE OF BUSBAR), and only one OUTPUT SQL operator is configured. In this case, however, you need to define in the OUTPUT SQL operator as many ACTIONS SQL of type SETUP INPUT, as there are sources—one action per source—together with an execution condition. It is possible to define a condition for executing a SETUP INPUT action based on the value of a data bus bit (column).
EXAMPLE USE CASES OF THE OUTPUT SQL OPERATOR
Interaction with source data
In this example, we assume that interaction with the source databases is not possible.
Standardization
Because data is consolidated from multiple sources, unique identifiers need to be standardized during the write operation (that is, adjusted to avoid duplicates).
As the OUTPUT SQL operator works in conjunction with INPUT SQL operators, it is worth considering different ways in which these operators can interact. Below, we examine the effects of different configuration settings—specifically, the data retrieval scope configured in the source INPUT SQL operator and the SQL Actions configured in the OUTPUT SQL operator that control the deletion of existing data in the target table.
INPUT SQL
OUTPUT SQL | Full data retrieval | Incremental data retrieval | Incremental data retrieval based on an incremental column |
Deleting all records from the target table
SQL Action
* DELETE ALL RECORDS
* DELETE ALL RECORDS FOR SOURCE | Advantages:
– always complete and unambiguous information in the target table
Disadvantages:
– time-consuming operation | Forbidden.
You cannot perform incremental data retrieval and delete all data from the target table, because this would result in incomplete information. | Forbidden.
See the note alongside. |
Deleting records from the target table within a fixed, predefined scope
SQL Action
* DELETE DECLARED SPACE | Identical to the case above.
This action differs from the example above in that separate identifier ranges are assumed for each independent source, which means that standardization is not required.
A case without standardization! | Forbidden.
See the note above. | Forbidden.
See the note above. |
Deleting records from the target table within a scope derived from the input data stream
SQL Action
* DELETE SPACE BY MIN MAX | Possible, but risky.
Justification: there may be records outside the scope derived from the data stream that have been deleted in the original source; in such cases, they will not be removed from the target table. | Condition:
– records from the input data stream that have already been retrieved must not be deleted or modified
Advantages:
– processing speed | Condition:
– input data stream records with identifiers below the “tough position” must not be deleted or modified
Advantages:
– processing speed |
Deleting or updating a record that has been retrieved again
SQL Action
* DELETE OLD RECORD
* UPDATE OLD RECORD | Identical to the case above. | The effect of the action is identical to the one described above. | Do not use.
The effect of the action is the same as above, but worsened by the situation in which records that were previously retrieved have been deleted in the input data stream within the range above the hard position. |