Working-Import Table Wizard/Table Union-1

From DataSelf Knowledge Base
Jump to navigation Jump to search

Import Table Wizard ... - Table Union window

Imports and combines data from the AR_Customer tables from the DSDW_Sage100 and DSMAS90200 data sources respectively.

Union Concepts

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.

Sample Applications
  • 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.

Target Structure

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)

The source tables must have the same column names and data types as the target structure. Only the columns (fields) listed in the target structure are imported from the source tables.

NOTES:

  • 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.

TableID field

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

DataSelf_ETL_Glossary#Union

DataSelf_ETL_Glossary#xUnionxx

mytest

<figure id="fig:upperpanel">

Import Table Wizard ... - Table Union window showing (1) upper panel showing source tables to be merged (2) data entry fields. In this example the second source table has bee n selected to be edited and thus the parameters of the second source table are displayed in data entry fields.

</figure>

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.

TableID

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".

Table Name

Name of a source table to be merged in the union operation.

Data Filter

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.

Add Table button

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 button

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.

Ok button

Saves the changes made in this window.

Discussion

  • Sage 300 and Sage 500 have internal columns to support multi-company setups. These fields serve the same function as the TableId field.


Quick Start

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.