Merge Block

The Merge block of a Vision query allows to incorporate information from 2 different data sources in the same widget. It is a stage of the query equivalent to a lookup operation (excel, NoSQL) and partly to a join in SQL.

A Vision query proceeds as a succession of individual query blocks, in a main pipe. The merge block allows the establishment of a second auxiliary row, with its own associated data source or dataset, so that the output of this row is combined with the main row at the corresponding query stage. The type of combination of both lines is indicated by the user, establishing the relevant equivalences or association rules between both lines.

Example

A typical example helps to understand in a simple way what a merge block does:

1. A query has as its main line data from dataset A, which includes the customer ID and associated sales. The customer ID is a useful field for efficient storage of information, but it does not provide information to a user viewing data. It is normal to want to decode that ID.
2. A second dataset B has the equivalences between customer ID and its associated sales agent. A secondary query is defined with data source dataset B, and that combines with the main line by the customer ID field. Therefore, for each customer ID in the main line, its agent is searched for using the secondary line.
3. The result of the merge block will therefore be 3 fields: the customer ID and the associated sales, which come from the main line, and the sales agent, which come from the sub-line.

Particularities of the Merge Block

This block is potentially dangerous, since the combination of 2 data sets can generate an intermediate data set so large that its management consumes the available computational resources, as, for example, happens with joins in SQL.
Therefore, it is very important to be clear about how the merge block is computationally solved:

• The block preceding the merge block determines the main row data to be combined: N rows of data.
• For each row or data record of the main row, the query of the child row is executed: N queries are therefore resolved sequentially, with a consequent impact on the performance of the overall query. Unlike what sometimes happens with SQL queries, there is no "optimization" of the query: repeated combinations or equivalences are not grouped or associated with possible indexes to execute the combined query more efficiently.
• Each record in the main row passes to its child query the specific values of the equivalence fields. These values are initially included as a filter in the executed secondary query: the secondary query thus works on a subset of the secondary dataset and not on the total.
• The secondary query is executed, and may return:
  • No record: in that case the original record of the main row is reported with empty child row fields in case the equivalence type is left join, or discarded (i.e. not returned) in case the type is inner join.
  • 1 record: the original record of the main row plus the fields of the child row obtained at the end of the query are returned
  • mrecords: m records are returned where the values of the fields of the main line are always the same, and those of the secondary line are those corresponding to their corresponding secondary record.

Other Particularities

• Filters and dependencies in the block. The selector filters of the tab where the widget with merge block is placed affect/do not affect both the dataset of the main line and the dataset of the secondary line, i.e., they are not allowed to affect one line but not the other. It is important to note that a filter associated to a field that is not in a dataset does not affect the query that has that dataset as data source, so it can happen that a filter affects the main line but not the secondary line, and vice versa.
• Same data source in main and secondary. It is possible to use the same dataset of the main line as the data source of the secondary line. Therefore, the result of a merge block may return duplicate fields, i.e. they are in both rows. To avoid such duplicity, the fields of the main dataset are prioritized, i.e. in case the same field is in both rows, the duplicate field is discarded from the secondary row contribution.
  Note: If both fields are needed, it is the user who must disambiguate: simply create a new "alias", i.e. a formula field equal to the original field in one of the lines, and disable the original field.
• Formulas. It is possible to define formulas in both the main and sub-line queries. It is important to correctly manage dependencies in the sub-line formulas, because if they are not explicitly enabled at the end of the combine block, they will be added automatically (and if a grouping criterion needs to be applied, this is the sum function for measures and the first function for dimensions).