ODBC provides direct data communications between Forecast Pro and a wide variety of databases. This is accomplished through intermediary ODBC drivers that lie between Forecast Pro and your database. You must obtain and install the driver from the database manufacturer or a third party.
ODBC drivers are available for most major database products including Access, Oracle and SQL Server.
Database structure
Forecast Pro reads data from structured tables or views defined in the database. Ordinarily, the tables created for the Forecast Pro interface are just a subset of the entire database.
Here is an example of a of a data table formatted for Forecast Pro. The file is installed with the program and is named Sample Historic Data – ODBC.mdb.
The above example does not use attribute fields to define product groupings. This is often desirable and is discussed in the Defining the Hierarchy section.
In our example, each data record consists of six header items in columns 1 through 6, followed by the corresponding historic data point. It is important to note that each data record must contain the aggregated value for the given period.
The fields are defined as follows.
- ItemId0. This is a text field containing the variable name.
- Description. This is a text field containing the variable description. It can be up to 256 characters.
- Hist_Year. This is a number field with field size double containing the year.
- Hist_Period. This is a number field with field size double containing the period.
- Ppy. This is a number field with field size double containing the periods per year.
- Ppc. This is a number field with field size double containing the periods per cycle.
- Hist_Value. This is a number field with field size double containing the aggregated historic data value for the record’s “date” as defined by the Hist_Year and Hist_Period.
See Setting up the Historic Data for more details on these items.
The Forecast Pro project needs to connect to an ODBC database before you can access the tables and queries therein. The first time you select Add > ODBC in the Data Manager for a given project you will be prompted to establish ODBC data source. This is done by selecting an ODBC driver and then selecting a database.
Once the project is connected to the database, the tables and queries are displayed, including those that have nothing to do with Forecast Pro. A table from the database is treated just like a file when you are using the Data Manager. Thus, you should arrange data into the tables or views that make logical sense as entries in the Data Manager.
Formatting rules
ODBC drivers vary a great deal. If you experience any difficulties connecting to the database, the first thing to check is that you are using the latest ODBC driver available for your database.
Missing values are indicated by whatever rules are used by the native database. Every database recognizes the distinction between zeroes and missing values. If you want, you can encode leading and trailing missing values as zeroes. In that case you must make sure that “Ignore leading zeros” and/or “Ignore trailing zeroes” is selected on the Formats tab of the Options dialog box.
If you are not using attribute fields each variable name must be unique. If you are using attribute fields to define product groupings within a hierarchy, each variable name within a given group must be unique.
Oracle Users: Some Oracle drivers will only work if the Hist_Value field is defined as FLOAT (not NUMBER). All other numeric fields can be NUMBER with the decimal places set to zero.