Supported DBMS

Foresight Analytics Platform addresses DBMS server to access repository data during the work. 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 the list of DBMS supported in the platform:

Repository Connection DBMS

DBMS type	Description	Comments
PostgreSQL/Postgres Pro	Support of work with the DBMS: PostgreSQL: 11.x\12.x\13.x\14.x\15.x. Postgres Pro: 11.x\12.x\13.x\14.x\15.x.	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 website. In Windows OS, after installation, add a path to the bin folder containing driver library to the PATH system variable. For example, c:\Program Files\psqlODBC\0905\bin\.
Oracle	Support of work with Oracle DBMS 12.x\18.x.	It is required to install DBMS front end. To correctly connect to Oracle DBMS, bitness of Foresight Analytics Platform and Oracle DBMS front and must match. Work with Oracle 12.x is not supported if Oracle Multitenant was enabled on installing server back end. The front end and installation guide are available at the Oracle official website. When installing and setting up back end and front end take into account the language settings to be used on data transfer and storage. For details about the language settings, the NLS_LANG parameter and methods for its setting see the developer website. NOTE. It is not recommended to use the settings that use the variable length of character encoding UTF8 and UTF16.
Microsoft SQL Server	Support of work with Microsoft SQL Server DBMS 2008\2012\2014\2016\2019.	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 website. NOTE. The front end version must not be earlier the DBMS server version.
Teradata	Support of work with Teradata DBMS 15\16.	It is required to install DBMS front end. The front end is available at the website http://developer.teradata.com/, use the CLIv2 version. Work in Linux OS is executed via the Generic ODBC driver.
SQLite	Support of work with SQLite DBMS 3.x.	The required driver is included to the selected software package of Foresight Analytics Platform. NOTE. Work with Foresight Analytics Platform is available only in the desktop application in single-user mode.

If there are some issues on creating a repository or on connecting to repository, see the Solving Possible Problems in Foresight Analytics Platform subsection.

NOTE. . When working in 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 subsection.

Driver type	Description	Comments
Greenplum/Pivotal HD Hawq/Arenadata DB	Support of connection to data sources based on EMC Greenplum and PostgreSQL/Postgres Pro DBMS. NOTE. They are not used to connect to repository.	It is required to install OLE DB drivers in the operating system. The Greenplum driver can be used to work with databases created based on PostgreSQL/Postgres Pro DBMS, in the read mode and without support of the Lo data type. In this case it is not necessary to install ODBC driver.
ClickHouse	Support of connection by means of ODBC drivers installed in Windows OS or Linux OS. NOTE. . They are not used to connect to repository.	An ODBC driver is available at the ClickHouse official website. Connection is similar to Generic ODBC. Work with ClickHouse DBMS is executed taking into account the following constraints: Available: creating of standard cubes, extracting of changed data, server data aggregation. Using subqueries when retrieving data. Creation of tables and data insertion are not supported. Creating stored procedures/functions and table triggers is not supported. The following repository objects cannot work with DBMS: automatic cubes, multidimensional calculation on database server, time series database.
OLE DB	Support of connection to various data sources via OLE DB providers installed in the operating system. 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. Support of queries and DBMS commands alone. Support of creating and use of tables, views, procedures and functions depending on the functionality of a certain DBMS and its correspondence to SQL standards.
DB2	Support of work with relational databases. NOTE. They are not used to connect to repository.	It is required to install DBMS front end.
WEB Service	Connection to the repository is established by means of addressing the BI server, which executes all required functions for DBMS authorization and for further data stream management.	It is not required to install DBMS front end to the user workstation. All required drivers should be installed where the BI server is installed. NOTE. Work with Foresight Analytics Platform is available only in the desktop application.
HP Vertica	Support of 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 website. Working with HP Vertica DBMS is executed considering the following restrictions: Creating of tables and queries is available. Available: creating of standard cubes, extracting and saving of changed data, server data aggregation. Only MDM dictionaries can be created as dictionaries, without supporting element deletion in linked dictionaries. Creating stored procedures/functions and table triggers is not supported. The following repository objects cannot work with DBMS: automatic cubes, multidimensional calculation on database server, time series database. When working with DBMS by means of Dal assembly resources, quick insert and asynchronous command execution are not supported. Work in Linux OS is executed via the Generic ODBC driver.
Generic ODBC	Support of connection to various data sources via ODBC providers installed in the operating system. NOTE. They are not used to connect to repository.	It is required to install ODBC drivers in the operating system. Support of queries and DBMS commands alone. Support of creating and use of tables, views, procedures and functions depending on the functionality of a certain DBMS and its correspondence with SQL standards. It can be used to connect to supported DBMS (PostgreSQL/Postgres Pro, Oracle, and so on), data sources (Greenplum, SAP, and so on) by specifying DSN or creating a connection string. The required ODBC driver can be obtained from appropriate manufacturer.