ODBC Connections in Jedox Web Spreadsheets

image_pdfimage_print

Jedox Web is able to use data from ODBC connections as a data source for elements such as DynaRanges. To use these data sources, you must first define a connection of type “ODBC” in the Connection Manager. Then you can use either the “Paste ODBC Query” dialog or the “ODBC” source option in the DynaRange dialog to retrieve data.

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:

Using the ODBC connections in spreadsheets

Open the  “Paste ODBC Query” dialog by navigating to Tools→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 Tools→Paste ODBC Query. Select the ODBC connection “DWH_Biker” and enter the following SQL statement:

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

Click “Preview” to view the 6 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$2,ROW()-1,COLUMN())

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.

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

image_pdfimage_print
Was this post helpful?
NoYes (+1 rating, 1 votes)
Loading...