fbpx

Union (ETL)

The union view allows for merging one ore more tables/views that share the same structure.

As an example, let's say we are sourcing sales data from two different data sources with similar structure. For consolidation purpose, we'd like to create a cube with all the data. This can be done by merging sales tables from the both data sources using a "Union" view.

Input : Americas and Europe Sales Data

Our schema contains both data sources : americas_data and europe_data. In each data source, a sales table contains the following information:

the Americas region:

COUNTRY, PRODUCT, SALES_2014, SALES_2015
Canada,  Bike ,   10,         20
Canada,  Car ,    20,         40
USA,     Bike ,   100,        200
USA,     Car,     500,        1000
    

and the Europe region:

COUNTRY, PRODUCT, SALES_2014, SALES_2015
France,  Bike ,   10,         20
Spain,   Car ,    100,        200
    

Output : World Sales Data

To generate a table with both the Americas and the Europe data, we're creating a union view (sales_union) as following:

Browsing the sales_union view will display both the data of Americas and the data of Europe:

COUNTRY, PRODUCT, SALES_2015, SALES_2014
Canada,  Bike ,   10,         20
Canada,  Car ,    20,         40
USA,     Bike ,   100,        200
USA,     Car,     500,        1000
France,  Bike ,   10,         20
Spain,   Car ,    100,        200
    

Walkthrough

The schema mentioned in this page can be downloaded here. It requires icCube v5.x onwards and a JRE 1.8 (for the Javascript code transformation).

Next chapter: Columns To Rows describes how transform columns into rows.