Performing a data union
It is possible to unite data from several data models.
Performing a data union
Data is combined vertically. The data is aggregated under each column.
- In the Create a new data model box, click on the Union button in the Other section.
The Union dialog box appears. - Select the data models you wish to use by clicking the Add button and then choosing Existing (or New to create a new data model.)The dimensions and measures of the selected models are aggregated according to their name and type. If an object has the same name and type in the different models, it is considered to be the same object (and will therefore only appear once in the aggregated data model).
- Select how you wish to manage duplicates from the drop-down list:
- No exclusion : this is the default value. Duplicates in data models are retained.
- Deduplicate all: Select the dimensions that will be used as a key to determine duplicates. Only the first key value found is retained. Duplicates (whatever the data model) are all eliminated.
- Duplicate all lines except the first data source: Select the dimensions that will be used as the key to determine duplicates. Duplicates found in the first data source are retained, those found in subsequent data sources are deleted.
- Duplicate all lines except the one in the same data source: Select the dimensions that will be used as the key for determining duplicates. Duplicates found in the same data source are retained, those found in subsequent data sources are deleted.
Alternatively, select Associate key columns to create associations between one or more columns of "key" dimensions from different sources.
This option allows you to join data without performing a cartesian product. The data displayed for the different dimensions is based on the key column.
For example, an associative union is performed on the following sources, with Date as the key column, in order to obtain Sales and Target based on Date in the same table
We want to display the sales and the annual target. For sales, there is a single value per year. For the target, the values for each year are summed.
If we add the Sales Rep column, the sales value is always based on the key column Date and is not linked to the Sales column.
➡ The key columns identified by the software are displayed in the Key Columns table (except for the No Exclusion option). Tick the key dimension(s) on which the union will be performed. The key dimensions must exist in all the selected models.
💡 If the dimensions have different identifiers in the data models, you can create a join key by clicking on the Add a key button .
Examples of creating a join between two tables
Examples of deduplication
The user wants to create a union between two data models: Sales1 based on the Sales1.xls file and Sales2 based on the Sales2.xls excel file.
File Sales1.xls:
File Sales2.xls:
- By choosing the key columns Date, Product and the option All deduplicate, only the first values found for the key are retained. If you create a table based on this new data model, you will obtain the following result:
- By selecting the key columns Date and Product and the option Deduplicate all rows except those from the same source, only duplicates from the same source are retained. If you create a table based on this new data model, you will obtain the following result:
- If you choose the key Date, Product and the option Deduplicate all rows except the one from the first data source, only the duplicates from the first source are kept. If you create a table based on this new data model, you will obtain the following result:
Example of an associative union
The user has two data sources.
The first Source 1 displays the amount per project and customer:
The second Source 2 displays the time spent per project for each employee:
We want to display the time spent for each customer and the amount invoiced. If we use a simple union (no exclusion), the result is as follows:
If we use an associative union with the Project key, we obtain the following result:
A list of projects is drawn up for each customer. The Project key is then used to determine the time spent on each of them.
For example, Nasa has 2 projects, Rocket and Shuttle. On Rocket, the time spent is 40 (10 by John and 30 by Henri) and on Shuttle the time spent is 20. The total is therefore 60.