ODBC Connections in Jedox Web Spreadsheets

Jedox Web Spreadsheets can use ODBC connections as a data source for elements such as DynaRanges. To use these data sources, you must first define an ODBC connection in Jedox Administration. Once the connection is configured, you can retrieve data in your spreadsheets with the Paste ODBC Query dialog (figure 1) or the ODBC source option in the DynaRange dialog (figure 2).

Figure 1

Figure 2

When using ODBC queries in a spreadsheet, several restrictions must be considered. For more information, see Data Sources for Data Validation, DynaRanges, ComboBoxes, and ListBoxes.

System DSNs on Windows systems

On Windows, the data source names, or DSNs, to which Jedox Web will connect are defined in the "Data Sources (ODBC)" dialog. The name chosen here for the DSN is also the name that must be used in the connection settings in the Jedox Web Connection Manager.

To create a DSN for your source database system, please refer to that system's documentation.

System DSNs on Linux systems

On Linux, the DSNs to which Jedox Web will connect (using the unixODBC standard) are defined in two configuration files in the Jedox installation directory (e.g. /opt/jedox/ps/): etc/odbcinst.ini and etc/odbc.ini.

In the file odbcinist.ini, general settings for the specific ODBC drivers are specified, such as the name for the ODBC connection type and the driver file's location. In the file odbc.ini, the settings for a specific data source are defined, such as the host, driver to use, and database/schema name.

See Note 3 at the end of this article for setting up the Oracle driver on Linux.

Creating an ODBC connection in Jedox Web

After setting up the DSN on the system where Jedox is installed, you can create a connection to the DSN in the Jedox Web Connection Manager.

As an example, the connection DWH_Biker is supplied with the Jedox Web installation.

In the Administration panel you should first test if the connection works:

If it doesn't work, it may help to install Microsoft Access Database Engine 2010 Redistributable, because the connection DWH_Biker uses an ACCESS database.

Using the ODBC connections in spreadsheets

Open the "Paste ODBC Query" dialog by navigating to Insert→Paste ODBC Query. Alternatively, you can open a DynaRange or ComboBox dialog and select "ODBC Query" as a source.

To demonstrate, we will use the DWH_Biker connection. In a new spreadsheet, click on cell A2 and go to Insert→Paste ODBC Query. Select the ODBC connection "DWH_Biker" and enter the following SQL statement:

Select * from Orders where Years = "2013" and Months = "Apr" and Products = "Trekking-2000 Blue 60"

Click "Preview" to view the first data records. Click on the button "Use This Query" to enter the ODBC start formula into cell A2:

To display all returned rows and columns of the SQL statement in the spreadsheet, enter the following formula in all corresponding cells (do not overwrite A2!):

=ODBCDATA($A$3;1;2)

Note that ODBC DynaRange splits multidimensional SQL result arrray into one two-dimensional array per row. As a consequence, ODBCDATA() functions within DynaRanges do not work in combination with ROW() and COLUMN() functions.

If you enter the formula in more cells than there are records for the SQL statement, you will receive the entry "#VALUE!".

Examples with ODBC connections and DynaRanges can be found in the workbooks "Order Analysis(Drill Through)" and "Time Analysis(relational)" in the folder "Demo Spreadsheets/Bikers Best/Workbooks" of the Report Designer.

The advantage of using ODBC formulas in DynaRanges is that you have only to fill the first line with formulas. In User Mode, the DynaRange will automatically display all required lines for all the records of the SQL statement.

Creating ODBC connections without DSN

For specific database providers, such as MSSQL, MySQL, Oracle, and PostgreSQL, it is also possible to create ODBC connections without a DSN. All necessary connection information can be provided in the Connection Manager. The keys for configuring the driver names are:

Notes

1) ODBC sources for Jedox Web must use /*comment */ for comments. Using "#" for comments will not be recognized and leads to errors.

2) Regarding handling of special characters in ODBC data, it may be necessary to define certain settings on the system where Jedox is installed. For example, when connecting to Oracle servers and using German characters, the following environment variables have to be set:

• LC_ALL=de_DE.UTF-8
• LANG=de_DE.UTF-8
• LANGUAGE=de_DE.UTF-8
• NLS_LANG=GERMAN_GERMANY.AL32UTF8

3) When connecting to MSSQL from Linux, columns of type NVARCHAR aren't supported

4) To connect to Oracle DB on Linux,

• download the latest Oracle drivers for unix from oracle.com. At the time of writing version 12.1.0.1.0:
  

instantclient-basic-linux.x64-12.1.0.1.0.zip
instantclient-odbc-linux.x64-12.1.0.1.0.zip

• unzip both packages to usr/lib for 32bit setup or usr/lib64 for 64bit setup
• edit etc/odbcinst.ini to add entry for Oracle driver:
  

[Oracle 12g ODBC driver]
Description     = Oracle ODBC driver for Oracle 12g
Driver          = /usr/lib64/libsqora.so.12.1
FileUsage       = 1

• edit etc/odbc.ini to add DSN for Connection to Oracle DB:
  

[TestOracle]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle 12g ODBC driver
DSN = TestOracle
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = :/
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID =
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F
SQLTranslateErrors=F
MaxTokenSize=8192 

Make sure HOST_IP, PORT, and SERVICE_NAME match your Oracle setup, e.g. 192.168.2.130:1521/test.BikerDB

Updated September 27, 2022