Difference between revisions of "Import Table Wizard"

From DataSelf Knowledge Base
Jump to navigation Jump to search
(Data Source Type (Type column))
(Import Table Wizard - Data Source Settings window)
 
(21 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
{{#breadcrumb:DataSelf ETL }}
 
{{#breadcrumb:DataSelf ETL }}
 
[[Category:DataSelf ETL]]  [[Category:DataSelf ETL Import Table Wizard]]
 
[[Category:DataSelf ETL]]  [[Category:DataSelf ETL Import Table Wizard]]
{{TOCFloatRight}}
+
 
 
== ''Import Table Wizard - Start'' window ==
 
== ''Import Table Wizard - Start'' window ==
  
 
[[File:ImportTableWizard-Start1.png|Import Table Wizard - Start window|400px]]
 
[[File:ImportTableWizard-Start1.png|Import Table Wizard - Start window|400px]]
  
<!-- https://dataselfsupport.com/attachments/179/Aug._14_14.1844.jpg-->
+
The Import Table Wizard - Start wizard shows all the {{egx|Data Source|data sources}} that are currently configured and available in the ETL.   
 
 
This window shows data sources that are found on the host computer’s ODBC Data Source Administrator or  connections/data sources that are defined and saved in current ETL projectEDIT (see comments)
 
  
 
   <!-- If the DSN for a ODBC connection is deleted from the host computer then that data source will no longer be shown on the '''Import Table Wizard - Start''' window.
 
   <!-- If the DSN for a ODBC connection is deleted from the host computer then that data source will no longer be shown on the '''Import Table Wizard - Start''' window.
 
Contrast the information shown on the Import Table Wizard with the “data sources” shown in the DataSelf ETL’s '''Data Source Name * column on the'''Browse Table* window. The column in the '''Browse Table''' window reflects only the pre-mapped metadata for the each table. This column does not indicate that a corresponding data source connection does or does not exist on the host computer. -->
 
Contrast the information shown on the Import Table Wizard with the “data sources” shown in the DataSelf ETL’s '''Data Source Name * column on the'''Browse Table* window. The column in the '''Browse Table''' window reflects only the pre-mapped metadata for the each table. This column does not indicate that a corresponding data source connection does or does not exist on the host computer. -->
 
+
<!-- place to floating TOC after the image -->
 +
{{TOCFloatRight}}
 
=== Context ===
 
=== Context ===
A {{egx|Data Source|data source}} is a named collection of {{egx|Metadata|metadata}} that describes how to connect to {{egx|Source Data|source data}}. The source data may be in a database such as SQL Server or Excel file.<br > The names of the data sources already defined are listed in the [[#''Data Source'' column|'''Data Source''' column]].
+
A {{egx|Data Source|data source}} is a ''named'' collection of {{egx|Metadata|metadata}} that describes how to connect to {{egx|Source Data|source data}}. See {{egx|Data Connection|data connection}} for more information. <br >
 +
The names of data sources already defined are listed in the [[#''Data Source'' column|'''Data Source''' column]].
 
==== Data Source Type (Type column) ====
 
==== Data Source Type (Type column) ====
 
The {{egx|Data Source|data source}} types are:
 
The {{egx|Data Source|data source}} types are:
* ODBC - Configured by Windows ODBC Data Source Administrator.<br >
+
* ODBC - Configured by Window's [[ODBC Data Source Administrator]].<br >
: Can be used to connect to SQL Server, other ODBC compliant databases, Excel files, Access database, and others. The data sources types listed below are usually more efficient and are recommended as the preferred method of connecting to {{egx|Source Data|source data}}.
+
: Can be used to connect to SQL Server, other ODBC compliant databases, Excel files, Access databases and other {{egx|Source Data|source data}}. But note that the data source types listed below use technologies that are usually more efficient and are the recommended way to connect to those types of {{egx|Source Data|source data}}.
 
* SQLSERVER - Configured by [[#'''Import Table Wizard - Data Source Settings''' window|'''Import Table Wizard - Data Source Settings''' window]], <tt>MS SQL SERVER</tt> option. A more efficient data connector recommend by Microsoft. The preferred method for connecting to Microsoft SQL Server databases.  
 
* SQLSERVER - Configured by [[#'''Import Table Wizard - Data Source Settings''' window|'''Import Table Wizard - Data Source Settings''' window]], <tt>MS SQL SERVER</tt> option. A more efficient data connector recommend by Microsoft. The preferred method for connecting to Microsoft SQL Server databases.  
 
* EXCEL - Configured by [[#'''Import Table Wizard - Data Source Settings''' window|'''Import Table Wizard - Data Source Settings''' window]], <tt>MS Excel</tt> option.  A more efficient data connector recommend by Microsoft. The preferred method for connecting to Microsoft Excel spreadsheet (workbook) files.
 
* EXCEL - Configured by [[#'''Import Table Wizard - Data Source Settings''' window|'''Import Table Wizard - Data Source Settings''' window]], <tt>MS Excel</tt> option.  A more efficient data connector recommend by Microsoft. The preferred method for connecting to Microsoft Excel spreadsheet (workbook) files.
Line 26: Line 26:
  
 
===== ''Data Source'' column =====
 
===== ''Data Source'' column =====
 +
Displays the names assigned to {{egx|Data Source|Data Sources}}.<br />
 +
<!--
 +
* For data sources connected via ODBC: Refers to the DSN (name) of an ODBC connection.
 +
* For data sources connected via other providers (such as Excel): Refers to the data source name saved for each connection. -->
  
* For data sources connected via ODBC: Refers to the DSN (name) of an ODBC connection.
+
Click on a row to select it for further action.
* For data sources connected via other providers (such as Excel): Refers to the data source name saved for each connection.
+
 
 +
See also the discussion above on the [[#Data Source Type (Type column)|data source types]] displayed in the [[#Data Source Type (Type column)|'''Type''' column}}.
  
 
===== ''Driver'' column =====
 
===== ''Driver'' column =====
 
===== ''Type'' column =====
 
===== ''Type'' column =====
 
+
See the [[#Data Source Type (Type column)|discussion above]].
  
 
===== ''Add'' button =====
 
===== ''Add'' button =====
Line 39: Line 44:
  
 
===== ''Edit'' button =====
 
===== ''Edit'' button =====
 +
Edits the selected [[#''Data Source'' column|'''Data Source''']].
 
===== ''Delete'' button =====
 
===== ''Delete'' button =====
 +
Deletes the selected [[#''Data Source'' column|'''Data Source''']].
 +
===== ''Next'' button =====
 +
Configures a new {{egx|Imported Table|imported table}} using the selected [[#''Data Source'' column|'''Data Source''']].
 +
 +
==== Discussion ====
 +
See [[Import Table Wizard#Discussion_.28Import_Table_Wizard.29|Import Table Wizard - Discussion]].
  
===== ''Next'' button =====
+
 
 +
<br />
  
 
== '''Import Table Wizard - Data Source Settings''' window ==
 
== '''Import Table Wizard - Data Source Settings''' window ==
  
 
[[File:ImportTableWizard_DataSourceType_DropdownMenu.png|thumb|none|'''Data Source type''' options of '''Import Table Wizard - Data Source Settings''' window.|500px]]
 
[[File:ImportTableWizard_DataSourceType_DropdownMenu.png|thumb|none|'''Data Source type''' options of '''Import Table Wizard - Data Source Settings''' window.|500px]]
Navigation to here: From '''Import Table Wizard - Start''' click '''Add'''.<br />
+
Navigation to here: From '''Import Table Wizard - Start''' window click '''Add'''.<br />
 
There are three versions of this wizard depending on the '''Data Source type'''. The options are: MS SQL SERVER, MS Excel, and MS Access.  
 
There are three versions of this wizard depending on the '''Data Source type'''. The options are: MS SQL SERVER, MS Excel, and MS Access.  
  
 
<!-- <div style="float:right;>    </div>-->
 
<!-- <div style="float:right;>    </div>-->
 +
 +
<br>
 
=== Data Source type = "MS SQL SERVER" ===
 
=== Data Source type = "MS SQL SERVER" ===
[[File:ImportTableWizard_DSS_MSSLQSereverOption.png|thumb|left|500px]]
+
Navigation to here: From '''Import Table Wizard - Data Source Settings''' window select <tt>"MS SQL SERVER"</tt>.
 +
[[File:ImportTableWizard_DSS_MSSLQSereverOption.png|thumb|none|500px]]
  
 
===== ''Server Name'' field. =====
 
===== ''Server Name'' field. =====
 
In the Server Name field enter the instance name of SQL Server. The instance name is the same name as used to login using Microsoft SQL Server Management Services.
 
In the Server Name field enter the instance name of SQL Server. The instance name is the same name as used to login using Microsoft SQL Server Management Services.
<br clear=all>
 
  
===== SQL Server Instance Names =====
+
====== SQL Server Instance Names ======
 
*  “local” should be written as “(local)”, “localhost” or the name of the machine.
 
*  “local” should be written as “(local)”, “localhost” or the name of the machine.
 
* If an name/instance requires the “Instance Name&gt;” then it would be “(local)instance name&gt;”.
 
* If an name/instance requires the “Instance Name&gt;” then it would be “(local)instance name&gt;”.
  
NOTE: The ''instance name'' of an SQL Server is informally known as the ''server name''.<br />SQL Server Setup sets the instance name to the computer name during installation.
+
====== NOTES: ======
==== Discussion ====
+
:* The ''instance name'' of an SQL Server is informally known as the ''server name''.<br />{{revise|SQL Server Setup|What is this? Window's installer for SQL Server?}} sets the instance name to the computer name during installation.
===== To find an SQL Server’s Instance Name: =====
+
:* Make sure SQL Server and SQL Server Browser services are running, otherwise you won’t be able to connect.
# open SQL Server Configuration Manager (search for it in the Start menu).
+
:;To find an SQL Server’s Instance Name:
# Click on SQL Server Services.<br /> The instance name of SQL Server is in parenthesis inline with SQL Server service. If it says MSSQLSERVER, then it’s the default instance.
+
:# open SQL Server Configuration Manager (search for it in the Start menu).
# To connect to it in Management Studio, just type . (dot) OR (local) and click Connect.<br /> If the instance name is different, then use .instance name] to connect to it (for example if the instance name is SQL2008, connect to .2008).
+
:# Click on SQL Server Services.<br /> The instance name of SQL Server is in parenthesis inline with SQL Server service. If it says MSSQLSERVER, then it’s the default instance.
 +
:# To connect to it in Management Studio, just type . (dot) OR (local) and click Connect.<br /> If the instance name is different, then use .instance name] to connect to it (for example if the instance name is SQL2008, connect to .2008).
  
Also make sure SQL Server and SQL Server Browser services are running, otherwise you won’t be able to connect.
 
  
 +
<br>
 
=== ''Data Source type'' = “MS Excel” ===
 
=== ''Data Source type'' = “MS Excel” ===
  
Line 81: Line 97:
 
NOTES:
 
NOTES:
 
* For Excel files the data connector (the software which runs on the host computer) is also known as  an ''Excel provider''.
 
* For Excel files the data connector (the software which runs on the host computer) is also known as  an ''Excel provider''.
 
  
 
==== ''Excel version'' pull-down list box ====
 
==== ''Excel version'' pull-down list box ====
Line 87: Line 102:
 
The '''Microsoft Excel 2007''' option is compatible with all versions of Excel files (e.g. .xls, xlsx, xlsm)
 
The '''Microsoft Excel 2007''' option is compatible with all versions of Excel files (e.g. .xls, xlsx, xlsm)
  
 +
 +
<br >
 
=== ''Data Source type'' = “MS Access” ===
 
=== ''Data Source type'' = “MS Access” ===
  
Line 179: Line 196:
  
 
<small>NOTE:</small> This process can be time consuming when working with large source tables, so you may consider disabling when you are sure the source data structures match.
 
<small>NOTE:</small> This process can be time consuming when working with large source tables, so you may consider disabling when you are sure the source data structures match.
 +
 +
 +
== Discussion <small>(Import Table Wizard)</small> ==
 +
* [[DataSelf_ETL#Multi-company_Consolidation|Multi-company consolidation]]

Latest revision as of 06:35, 7 April 2014

Import Table Wizard - Start window

Import Table Wizard - Start window

The Import Table Wizard - Start wizard shows all the data sources that are currently configured and available in the ETL.

Context

A data source is a named collection of metadata that describes how to connect to source data. See data connection for more information.
The names of data sources already defined are listed in the Data Source column.

Data Source Type (Type column)

The data source types are:

Can be used to connect to SQL Server, other ODBC compliant databases, Excel files, Access databases and other source data. But note that the data source types listed below use technologies that are usually more efficient and are the recommended way to connect to those types of source data.

Buttons & Fields

Data Source column

Displays the names assigned to Data Sources.

Click on a row to select it for further action.

See also the discussion above on the data source types displayed in the [[#Data Source Type (Type column)|Type column}}.

Driver column
Type column

See the discussion above.

Add button

The Add button opens the Import Table Wizard - Data Source Settings window which allows the creation of new data sources.

Edit button

Edits the selected Data Source.

Delete button

Deletes the selected Data Source.

Next button

Configures a new imported table using the selected Data Source.

Discussion

See Import Table Wizard - Discussion.



Import Table Wizard - Data Source Settings window

Data Source type options of Import Table Wizard - Data Source Settings window.

Navigation to here: From Import Table Wizard - Start window click Add.
There are three versions of this wizard depending on the Data Source type. The options are: MS SQL SERVER, MS Excel, and MS Access.



Data Source type = "MS SQL SERVER"

Navigation to here: From Import Table Wizard - Data Source Settings window select "MS SQL SERVER".

ImportTableWizard DSS MSSLQSereverOption.png
Server Name field.

In the Server Name field enter the instance name of SQL Server. The instance name is the same name as used to login using Microsoft SQL Server Management Services.

SQL Server Instance Names
  • “local” should be written as “(local)”, “localhost” or the name of the machine.
  • If an name/instance requires the “Instance Name>” then it would be “(local)instance name>”.
NOTES:
  • The instance name of an SQL Server is informally known as the server name.
    SQL Server Setup sets the instance name to the computer name during installation.
  • Make sure SQL Server and SQL Server Browser services are running, otherwise you won’t be able to connect.
To find an SQL Server’s Instance Name
  1. open SQL Server Configuration Manager (search for it in the Start menu).
  2. Click on SQL Server Services.
    The instance name of SQL Server is in parenthesis inline with SQL Server service. If it says MSSQLSERVER, then it’s the default instance.
  3. To connect to it in Management Studio, just type . (dot) OR (local) and click Connect.
    If the instance name is different, then use .instance name] to connect to it (for example if the instance name is SQL2008, connect to .2008).



Data Source type = “MS Excel”

MS Access option of Import Table Wizard - Data Source Settings window

In versions of the ETL prior to version 2013.002.xx access to Excel files required an ODBC connection. While the older ODBC connections still work the Import Table Wizard - Data Source Settings window configures a connection using a technology called an Excel provider.

Finish button

Clicking the Finish button attempts to configure a data connection to the specified Excel file on the host computer.

NOTES:

  • For Excel files the data connector (the software which runs on the host computer) is also known as an Excel provider.

Excel version pull-down list box

The Microsoft Excel 2007 option is compatible with all versions of Excel files (e.g. .xls, xlsx, xlsm)



Data Source type = “MS Access”

MS Access option of Import Table Wizard - Data Source Settings window


Import Table Wizard - Select Tables window

Navigation to here: From Import Table Wizard - Start click Next.

Import Table Wizard - Select Tables window.

Advanced button


Import Table Wizard - Table ... - Select Fields window

Navigation to here: From Import Table Wizard - Select Tables click Next.

Import Table Wizard - Table ... - Optional: Select Data Grouping window

Navigation to here: From Import Table Wizard - Select Fields click Next.


"var char" message

NOTE: The MediaWiki software does not allow us to show "var char" as a single word.

Import Table Wizard - Finish window

Table Union option selected.




Import Table Wizard ... - Table Union window

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

Alternative version of this section

Union Concepts

An ETL Table Union will merge two or more source tables into a single table in the data warehouse. Here are some examples of application of this feature:

  • When combining archived and current data (ex.: invoices) so people can analyze all historical data from a single imported table.
  • When consolidating multi-company systems into a single data warehouse. For instance, the invoices from 3 source ERPs will merge into a single imported invoice table. Users will be able to easily analyze invoices from all 3 ERPs from a single imported table, and then use filters to slice it.

The ETL Table Union is based upon the SQL's UNION operator. In SQL the UNION operator combines the result of two or more SELECT statements.

Target Structure

The fields selected in the Import Table Wizard#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?).


The source tables must have the same column names and data types as the target structure.

For instance:

  • Source table 1 columns: CustName (char 50), CustNo (char 10), CreditStatus (int)
  • Source table 2 columns: CustName (char 50), CustNo (char 10), City (char 50)
  • target structure: CustName (char 50), CustNo (char 10), TableID.


NOTE: 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.

Table Union Configuration

The top white panel shows the current Table Union configuration. To change the parameters configuration:

  • Click "Add Table" button to add a line and configure its parameters in the boxes below the white panel.
  • Select a line in the white panel and enter/adjust the values in the boxes below the white panel.
  • To remove a line, select it and click the "Remove" button.
  • To change the 1st line: you'll have to get out of the Table Union wizard. Use Import Table Wizard#Import Table Wizard - Table ... - Select Fields window.

Here's an explanation of the parameters:

TableId column

The ETL Table Union adds a column named TableID that can be used to indicate the source of the data in each row. On the example pictured above, the table union is combining records from the AR_Customer table from DSDW_SAGE100 and DSMAS90200 data sources. In the target table, records from DSDW_SAGE100 will be flagged with TableID 001 and the other with 002.

Sage 300 and Sage 500 already have internal columns to handle multi-company setups. So you do NOT have to populate TableID when unioning tables from these systems.

Table Name

Name of the source tables to be unioned.

Data Filter

Filter applied during the data import for each table unioned.

Data Source Name

Data source from where the table will be imported from.

Test table and field structure check box

When checked the ETL validates if columns mapped from the source tables match the target structure.

NOTE: This process can be time consuming when working with large source tables, so you may consider disabling when you are sure the source data structures match.


Discussion (Import Table Wizard)