Relational Load
With the relational load, data is written to a relational database system. The relational load writes data with batch inserts, which groups statements to limit round trips to the database.
Settings
Data source | Extract or transform containing the data to be written. | |||||||||
Tree format | If the data source is a tree, the hierarchy format must be entered. See Overview of Tree Formats. | |||||||||
Target connection | Connection to relational database. See Systems Supported by Jedox Integrator for supported databases. | |||||||||
Schema | Not all relational database systems support schemas (e.g. My SQL). | |||||||||
Table | If the database table does not yet exist in the target system, it will automatically be created during the load. Its columns will get convenient SQL data types of the relational target system, depending on the column types of the source. To obtain a relational table with specific SQL data types, the table must be created before starting the relational load, for example, with a SQL CREATE TABLE statement in a RelationalSQL load. |
|||||||||
Mode | Note: in the table below, CREATE, INSERT, etc. refer to the instructions to the relational databases and not to the load modes. These were selected with a view to achieving the greatest possible analogy to the cube load. | |||||||||
|
||||||||||
Aggregation | This parameter defines the aggregation behavior during the data load. | |||||||||
|
||||||||||
If aggregation is active, the aggregation settings define the aggregation level for the columns in the source.
Each field with update mode “key” is a key for the aggregation (not necessarily a primary key in the database table). For the non-key fields, the aggregation function can be set in the update mode. Possible values are: avg, count, first, last, max, min, sum. With update mode "last", a relational UPDATE can be achieved. There is a slight difference between the load modes "add" and "insert": if a numerical column is not specified in the aggregation settings, the default update mode is different. In case of "add", it is "sum"; in case of "insert", it is "last". This makes the relational load modes compatible with the behavior of Cube Load, especially with drillthrough. |
Notes
- To determine the data types of the columns and other settings of the database table, the table must first be created directly in the database system.
- It is not possible to run several relational loads for the database table in parallel jobs.
- Load type RelationalSQL should be used for stored procedures that change data.
Example with various settings
The table below shows a simple example of source data that will be loaded into an existing relational table.
Source | Existing relational table | |||
Customer: A B B C |
Value: 10 20 30 40 |
Customer: C |
Value: 5 |
The tables below show load results using various settings.
1. Mode=Add, Aggregation=true (Customer: Key, Update Mode: sum)
Customer | Value |
A B C |
10 50 45 |
2. Mode ADD, Aggregation=false
Customer | Value |
A B B C C |
10 20 30 40 5 |
3. Mode ADD, Aggregation=true (Customer: Key, Update Mode: last)
Customer | Value |
A B C |
0 30 40 |
4. Mode UPDATE, Aggregation=false
Customer | Value |
A B B C |
10 20 30 40 |
Mode UPDATE, Aggregation=true (Customer: Key, Update Mode: sum)
Customer | Value |
A B C |
10 50 40 |
Updated July 8, 2024