Fusing
The Fusing transform combines the rows and columns of two tables together by appending them.
Its behavior is a combination of different aspects of the Union and Join transforms. Rows are appended like a Union without requiring the columns of the two tables to match. It can combine different columns from two tables like a Join, but without matching and combining rows by key values.
1. Input
Choose the Fusing transform from the toolbar, which requires two inputs.
As an example, the following two sheets from Excel have been dragged onto the data cube canvas. They each contain numeric counts tracked by dates or timestamps, where it is common and expected for them to not match:
Click and drag from your inputs to the fusing transform to connect them. You can also click to select an existing connection from an input before adding the transform to insert it there.
2. Configure
Click or right-click the transform and choose Configure in the toolbar or menu, or double-click as a shortcut.
Under Settings, uncheck any elements (columns) from the first input that you do not want included in the output.
Choose the second input from the Inputs dropdown, then update the checkboxes the same way for its elements.
The dropdowns next to each input element allow you to choose whether they should be mapped with any of the elements from the first input, which will combine them into a single column in the output. Some of these may have been set for you automatically based on matching names. Any element that you want to remain a separate column can be set to (none).
For our example data, if we want all the dates to be combined into a single column, we can map the Validation Date column to the Creation Date column.
3. Output
After submitting the configuration dialog, you can preview the output by opening the Data Preview window with the transform selected.
In the example above, the rows from the two inputs have been appended, the two numeric columns have been appended, and the two date columns have been combined. We could re-open the configuration dialog, click Output elements, and edit the date column's name to simply Date.
Data is commonly grouped and aggregated once used in a metric set in Dundas BI, which can combine the appended rows. For example, clicking Add To New Metric Set in the toolbar and adding all three elements automatically visualizes the data like the following:
The Fusing transform accomplishes this result when it could be more difficult with alternatives:
- A Union transform (like a UNION in SQL) matches and combines every column from the first input with a column from the second, and could not output a Created column separately from the Validated column.
- A Join transform (like a JOIN in SQL) is based on matching key values, but our non-numeric data consists of only dates that do not match and are not expected to. A full (outer) join could return all of the rows from each input, but beware of duplicated key values: if there were any duplicated dates in one of our example inputs, the matched row of data in the other input would be duplicated including its numeric values.