ODBC

New data storage tables are defined from the NEW button on the My Data area of the home or Nodal Display section of the Metamation screen. Alternativlly, existing table definitions can be amended by selecting an existing table from the My Data using he mouse pointer, or selecting the properties icon ( ) against the table. Once the table properties screen is displayed, new tables can be defined or selected parts of existing tables can be amended.

Tables can be defined to allow data to be entered directly within the Metamation database, or can be set to import (pull) information in from a number of different data sources including ODBC connection, Excel spread sheets or Microsoft Project Plans.

When an ODBC connection is made for a table, the columns returned from the ODBC connection can be linked to the defined table columns. As records are returned (pulled) from the ODBC data source, records are created or updated in the linked tables, with the map between the imported fields and the defined table columns. The definition of the ODBC connection details exactly how the import will work.

To define an ODBC connection for a table, once the table properties are on screen, use the selection list on the External Data

Connection to set the connection type to ODBC connection, and then use the Define Connection button to define the links to the ODBC data source.

Table External Link Definition - ODBC - Connections

An ODBC connection for a table is set through the table properties window. Using the selection list on the External Data Connection to set the connection type to ODBC connection, and then use the Define Connection button , the ODBC properties window is then displayed.

ODBC properties are set through the three pages of the ODBC settings process:

• ODBC connection
• ODBC Refresh
• ODBC Filtering
  

On the ODBC connections screen, ODBC connections are set in three parts:

1. Specify the data source
As with standard Microsoft ODBC settings, you have the ability to select an existing data source (on the Metamation server) from the pull down list, or specify a new connection string manually.

If you want to re-use a data connection, you can use the new DNS icon ( ) to build a DNS entry. Please note, any DNS entry build will be created on the main Metamation server, not your local client PC. Once you have created a new DNS entry, use the DNS refresh icon) to refresh the DNS list from the server.

If building a new connection string, the string can be entered manually, or you can use the build icon ) to build a connection using a wizard.

2. Enter user name and password
These entries are only required for secure data connections, and are provided to allow secure access to the data source. For some databases (Excel, Project, Access etc), these may not be required and can be left blank.

3. Initial Catalogue
For the majority of data sources, an initial catalogue (database or sub-database) is required. Select the database from the drop down list. If no databases are listed, then the selected database from item 1 needs connection through the re-connect icon ).

Once the first page is complete, use the tabs at the top of the screen to move to the refresh tab.

Table External Link Definition - ODBC - Filtering

An ODBC connection for a table is set through the table properties window. Using the selection list on the External Data Connection to set the connection type to ODBC connection, and then use the Define Connection button , the ODBC properties window is then displayed.

ODBC properties are set through the three pages of the ODBC settings process:

• ODBC connection
• ODBC Refresh
• ODBC Filtering

On the ODBC build screen, a large window is available to enter free format SQL to select records and columns to be used from the linked data source. The SQL is mandatory to allow Metamation to know what information to be included in the database.

Within the free hand SQL, macros can be included to perform data selections. When run, these macros will be converted to dates as follows:

Macro Use and conversion (on select)
@SYEAR@ Start of the current year. When converted in script, takes the first day of the current year. If the current date is “12/06/2005”, then the macro is converted to “01/01/2005” in the SQL Script when executed.
@EYEAR@ Last day of the current year. When converted in script, takes the last day of the current year. If the current date is “12/06/2005”, then the macro is converted to “31/12/2005” in the SQL Script when executed.
@SMONTH@ Start date of the current month. When converted in script, takes the first day of the current month If the current date is “12/06/2005”, then the macro is converted to “01/06/2005” in the SQL Script when executed.
@EMONTH@ Last day of the current month. When converted in script, takes the last day of the current month. If the current date is “12/06/2005”, then the macro is converted to “30/06/2005” in the SQL Script when executed.
@SQUARTER@ Start of the current quarter. When converted in script, takes the first day of the current quarter. If the current date is “12/06/2005”, then the macro is converted to “01/04/2005” in the SQL Script when executed.
@EQUARTER@ Last day of the current quarter. When converted in script, takes the last day of the current quarter. If the current date is “12/06/2005”, then the macro is converted to “30/06/2005” in the SQL Script when executed.
@SWEEK@ First day of the current week – based on calendar weeks. When converted in script, takes the first day of the current week. If the current date is “12/06/2005”, then the macro is converted to “06/06/2005” in the SQL Script when executed.
@EWEEK@ Last day of the current week. When converted in script, takes the last day of the current week. If the current date is “11/06/2005”, then the macro is converted to “12/06/2005” in the SQL Script when executed.

The alternative to entering free format SQL is to use the BUILD button. This will launch a new window which will connect to the specified data source, and will allow you to select tables, links, sort options, filter criteria etc.

Once the SQL has been entered, use the tabs at the top of the screen to return back to the Connections page, to test the connection.

Table External Link Definition - ODBC - Refresh

An ODBC connection for a table is set through the table properties window. Using the selection list on the External Data Connection to set the connection type to ODBC connection, and then use the Define Connection button , the ODBC properties window is then displayed.

ODBC properties are set through the three pages of the ODBC settings process:

• ODBC connection
• ODBC Refresh
• ODBC Filtering
  

On the ODBC refresh screen, ODBC connections are defined on the type of connection (what to do with the data) and also the refresh time. When entering a refresh time, it is important to select a time which provides the correct balance of providing Metamation with the required data in a timely manor, but also does not put a strain on the original data source.

The two entries available are:

Refresh Type
The refresh type drop down indicates what should happen with the imported data. The options available are:

Refresh Entire Table - Before the ODBC connection is made, the current contents of the table will be cleared and the ODBC will re- create the contents.

Add new Items only - The ODBC import will look for new records and where the records do not exist, they will be created. Existing records will not be amended, and records missing from the ODBC source will not be deleted within Metamation

Add new Items and Amend Changed - The ODBC import will look for new records and where the records do not exist, they will be created. Existing records will also be updated to reflect the ODBC data source. However, any records missing from the ODBC source will not be deleted within Metamation

Add new Items, amend changed and mark deleted - The ODBC import will look for new records and where the records do not exist, they will be created. Existing records will also be updated to reflect the ODBC data source. Where records are missing from the ODBC source, these will be marked as deleted (making them unusable, but not actually deleting them) within Metamation

Refresh Time
The refresh time is how long in minutes, between every data refresh. This entry will only accept a numeric. 60 will be a refresh once an hour, 1440 will be a refresh once a day, 10080 will be once a week, and 40320 will be once a month. Any numeric is valid.

Below the selected refresh time, the last date and time of refresh will be displayed.

Once the refresh page is complete, use the tabs at the top of the screen to move to the filter tab.