Supported DBMS

On working with Foresight Analytics Platform addresses DBMS server to access repository data. The system addresses the server via the DBMS front end or by means of any other additional drivers that enable access to DBMS. Different driver versions can be used to work with different versions of DBMS. Below is a list of supported DBMS that are used to create a platform repository and features of working with it:

DBMS type Description Comments
PostgreSQL/Postgres Pro

Supports working with the DBMS:

  • PostgreSQL: 9.4\9.5\9.6\10;
  • Postgres Pro: 9.5\9.6\10.3\10.4\10.5.
 It is required to install an application that contains a driver to connect to PostgreSQL, for example:
  • Single ODBC driver (recommended).
  • PostgreSQL DBMS back end.
  • Other commercial applications or open-source applications developed and provided to work with PostgreSQL.

To correctly connect to PostgreSQL/Postgres Pro DBMS, bitness of Foresight Analytics Platform and installed driver must match. Various driver versions are available at the PostgreSQL official site.

After installation, add a path to the bin folder containing driver library to the PATH system variable. Example, c:\Program Files (x86)\PostgreSQL\9.5\bin\ or c:\Program Files\psqlODBC\0905\bin\.
Oracle Working with the Oracle DBMS of the 11.x\12.x versions is supported. It is required to install DBMS front end.
To correctly connect to Oracle DBMS, bitness of Foresight Analytics Platform and Oracle DBMS front end must match.
  • Installing the Oracle 11.x client requires installation of the Administrator configuration.
  • Working with Oracle 12.x is not supported if Oracle Multitenant technology option is enabled on installing back end.

The front end and installation guide are available at the Oracle official site.

When installing and setting up back and and front end note the locale to be used on data transfer and storage. For details about the locale purpose, the NLS_LANG parameter format and methods for its setting see the developer website.

NOTE. It is not recommended to use the locales that use the variable length of character encoding UTF8 and UTF16.
Microsoft SQL Server Supports working with Microsoft SQL Server DBMS 2008\2012\2014\2016\2017. It is required to install DBMS front end (Native Client). Connection via the OLE DB and ODBC interfaces is supported.
The front end and installation guide are available at the Microsoft official site.

NOTE. The front end version must not be earlier the DBMS server version.
Teradata It supports working with Teradata DBMS 15\15.10\16. It is required to install DBMS front end.
The front end is available at the site
http://developer.teradata.com/, use the CLIv2 version.
SQLite It supports working with SQLite DBMS 3.31.1 The required driver is included to the selected software package of Foresight Analytics Platform.

NOTE. Work with Foresight Analytics Platform is available only in desktop application in single-user mode.

Working with Teradata DBMS is supported only in Windows OS. Working with other DBMS is supported in Windows OS and Linux OS. If there are some problems on creating a repository or on connecting to repository, see theSolving Possible Problems in Foresight Analytics Platform subsection.

NOTE. . On working with Linux OS also take into account existing constraints.

Additional Connection Features

Apart from connecting to DBMS server Foresight Analytics Platform contains a series of alternative drivers allowing to connect to data sources. Connection is executed via the Database repository object.

Driver type Description Comments
OLE DB Supports connection to various data sources via OLE DB providers installed in Windows OS. To make the setup easier, Foresight Analytics Platform has selected providers:

  • OLE DB (ODBC HIVE).

  • OLE DB (DB2).

  • OLE DB (ODBC).

  • OLE DB (VISTADB).

NOTE. They are not used to connect to repository.

 It is required to install OLE DB drivers in the operating system.
It supports execution of queries and DBMS commands alone. It supports creating and use of tables, views, procedures and functions depending on the functionality of a certain DBMS and its correspondence to SQL standards.
DB2 Supports working with relational databases.

NOTE. . They are not used to connect to repository.

 It is required to install DBMS front end.
Greenplum/Pivotal HD Hawq Supports connection to data sources based on EMC Greenplum.

NOTE. They are not used to connect to repository.

 It is required to install OLE DB drivers in the operating system.
WEB Service Connection to repository is established by addressing to security server or BI server of Foresight Analytics Platform, which execute all necessary functions of authorization in DBMS and further management of data stream. It is not required to install DBMS front end to the user workstation. All necessary drivers must be installed where security server or BI server is located.
HP Vertica It supports connection to HP Vertica analytical DBMS: 7.0\8.0\8.1\9.0.

NOTE. They are not used to connect to repository.

 The ODBC driver is available at the Vertica official site.
Working with HP Vertica DBMS is executed considering the following restrictions:
  • Creating stored procedures/functions and table triggers is not supported.
  • The following repository objects cannot work with DBMS: MDM repository, automatic cubes, multidimensional calculation at database server, time series database.
  • On working with DBMS by means of Dal assembly resources, quick insert and asynchronous command execution are not supported.
  • Creating of tables and queries is available.
  • Only table MDM dictionaries can be created as dictionaries, without supporting element deletion in linked dictionaries.
  • Available: creating of standard cubes, extracting and saving of changed data, server data aggregation.
Generic ODBC It supports connection to various data sources via ODBC drivers installed in Windows OS or Linux OS.

NOTE. They are not used to connect to repository.

 It is required to install ODBC drivers in the operating system.
It supports execution of queries and DBMS commands alone. It supports creating and use of tables, views, procedures and functions depending on the functionality of a certain DBMS and its correspondence to SQL standards. For details see the articles: Preparing ODBC Driver to Connect to Microsoft SQL Server and Preparing ODBC Driver to Connect to MySQL.

Total List of Identifiers

Below is a list of identifiers of the drivers that can be used to set up various repository objects. Identifiers are used by various properties on setting up objects by means of macros in the Fore or Fore.NET language, and also on creating a list of repositories in the Metabases.xml file and a list of custom drivers in the Drivers.xml file:

Identifier DBMS driver
ORCL8 Oracle versions specified above
MSSQL2008 Microsoft SQL Server 2008
MSSQL2012 Microsoft SQL Server 2012\2014\2016\2017
MSSQL2012ODBC Microsoft SQL Server (ODBC)
OLEDB(ODBC HIVE) OLE DB (ODBC HIVE)
OLEDB(DB2) OLE DB (DB2)
OLEDB(ODBC) OLE DB (ODBC)
OLEDB(VISTADB) OLE DB (VISTADB)
OLEDB OLE DB provider
DB2 Relational DB2 database
TRDT Teradata versions specified above
POSTGRES PostgreSQL/Postgres Pro versions specified above
POSTGRES(NO_LO) Greenplum/Pivotal HD Hawq
SQLITE SQLite 3.31.1
VERTICA HP Vertica versions specified above
WSF Web service of Foresight Analytics Platform
ODBC Generic ODBC

NOTE. The number of available drivers may differ for different repository objects.

See also:

System Requirements for Client Environment