Working-Import Table Wizard/Table Union-1
- 1 Import Table Wizard ... - Table Union window
- 1.1 Union Concepts
- 1.2 Buttons and Fields
- 1.3 Discussion
- 1.4 Quick Start
Import Table Wizard ... - Table Union window
An ETL Table Union will merge two or more source tables into a single table in the data warehouse. The ETL Table Union is based upon the SQL's UNION operator.
- Combine archived and current data so historical and current data can be analyzed from a single table.
- Consolidate multi-company systems into a single data warehouse. The invoices from 3 source ERPs could be merged into a single invoice table for easy analysis.
The fields selected in the Import Table Wizard - Table ... - Select Fields window define the field names and data types of the data that will be combined in the table imported by the union operation; call this the target structure (better name?).
Source Tables and the Target Structure
(How the ETL knows which columns to import)
- When the Table Union option is selected the source table identified in the preceding Import Table Wizard - Select Tables window becomes the first table included in the union operation.
- The source tables may have columns not listed in the target structure. Those columns will not be imported and otherwise will be ignored by the union operation.
The ETL Table Union adds a column named TableID that can be used to indicate the source of the data in each row.
Buttons and Fields
The upper panel (<xr id="fig:upperpanel">callout #1 in figure </xr>) displays a list of one or more source tables that will be merged in the union; the data from these tables will be merged in the union operation. The data entry fields in the lower section (<xr id="fig:upperpanel">callout #2 in</xr>) are for adding and changing the data in the upper panel.
A user defined string which identifies the source table for each row in the resulting imported table.
For the union shown in <xr id="fig:upperpanel"> </xr> the records from the first source table will be flagged with a TableId of "001" and the second with "002".
Name of a source table to be merged in the union operation.
Filter applied to a source table before the data is merged.
Data Source Name
Identifies the Data Source containing the source table. Edit needed: link to Data Source""
Test table and field structure check box
When checked the ETL validates if the column imported from the source tables match the target structure.
NOTE: This process can be time consuming when working with large source tables. Consider disabling this option once you are sure the source tables are compatible with the target structure.
Adds a new source table to the list of tables included in the operation shown in the upper panel ((<xr id="fig:upperpanel"> </xr>).
Remove a source table, other than the first, from the tables shown in the upper panel (<xr id="fig:upperpanel"> </xr>).
NOTE: The first source table shown in upper panel (<xr id="fig:upperpanel"> </xr>) cannot be removed or edited from this window. The first source table (and thus the target structure) must be edited from the Import Table Wizard - Table ... - Select Fields window.
Saves the changes made in this window.
- Sage 300 and Sage 500 have internal columns to support multi-company setups. These fields serve the same function as the TableId field.
Merges two or more source tables into a single table in the data warehouse.
- To Modify a source table: Select a row in the upper panel (<xr id="fig:upperpanel"> </xr>). Modify the data in the data entry fields.
- To Modify the first source table: Click Ok or Cancel. Backup to Import Table Wizard#Import Table Wizard - Table ... - Select Fields window.
- To Add a source table: Click Add Table. Enter parameters in the data entry fields (<xr id="fig:upperpanel"> </xr>).
- To Remove a source table: Select it and click Remove".
- To Save: Click Ok to save your work.