Data connection

Data connection module is used to set up connections with files or databases. GMDH Shell accesses data files or databases in the beginning of each forecasting launch.

There are a number of data connection options:

• CSV/XLS/XLSX connection is used to connect one or more Excel spreadsheets or text files with delimiters.
• ODBC/OLEDB connection is used to connect databases or third party programs via the ODBC interface.
• Order list connection available only in the Business Forecasting package is used to extract historical demand from the raw list of customer orders.
• Sage Accounting connection available only in the Business Forecasting package is used to extract retail sales data from 'Sage 50' and other versions of Sage accounting software.

File > New project > CSV/XLS/XLSX connection is used to connect data files, this can be one or several Excel files or ASCI files with delimiters, for example, .csv, .txt, etc. At all stages you can keep your files opened for editing, for example in Excel or Notepad. The following limitations apply:

• The module works with columns. If series of observations are rows (not columns), Transpose data option must be turned-on.
• The module cannot read password-protected spreadsheets.

In the right part of the connection dialog there is a preview window with preliminary import results. It uses the following color-based indication of data types:

• Numeric cells - black.
• Text cells (categorical data) - blue.
• Date/time cells - green.
• Missing values - light gray. (if the cell is not empty)
• Discarded columns - gray cells.

Column labels are used as variable names, therefore you should either instruct GMDH Shell to Use 1st row for this purpose or to generate labels automatically: x1, x2, x3…. Both variants can be used simultaneously with Custom labels allowing you to replace any labels with your own. For example, Custom labels ,,Date, will be interpreted as labels x1, x2, Date, x4

Row labels are used to refer to observations, Row labels must be unique like date and time or like identifiers.

If your dataset store timestamps elements (year, month, day, week and time) in separate columns, you can compose the timestamp from several columns. For this purpose use the option Compose ID from several columns. All timestamp elements in the dataset must be neighboring columns and the option Read row lables from column N must point to the first of them. Supported combinations are: Year + Month, Year + Week, Year + Quarter, Year + Month + Day, Date + Time.

Import module is responsible for detection of missing values. It replaces various types of missing values with regular NULL values and thus allows us to handle them properly at the Preprocess stage.

Missing value mark is used to type in the missing mark or to select one of the standard marks.

Consider text cells as missing is used to replace all non-numeric cells with regular NULL values.

Delimiter is used to set the delimiter type such as coma, space, tab, or any other. This option is applicable only to ASCI files with delimiters (.csv, .txt, etc.).

Import all files with the same extension is used to connect all files with the same extension within the current directory.

Import all sheets of a workbook is used to connect all sheets of one or many .xls or .xlsx workbooks.

Transpose tables, i.e. read columns from rows is used to support data series formatted as rows (not columns).

Reverse order of rows is used to support data series where the most resent observations are in the top of the table and the oldest observations are in the bottom.

Import rows starting from is used to skip a number of rows in the top of the table, in particular this option is used to skip the header information.

File > New project > ODBC/OLEDB connection is used to connect various databases. Most database vendors provide at least a minimal ODBC driver with their database. This import module requires knowledge of SQL quires.

File > New project > Sage Accounting connection is used to easily extract retail sales data from 'Sage 50' and other versions of Sage accounting software (www.sage.co.uk). The module is only available in the 'Business forecasting' version of GMDH Shell.

~~UP~~