ShowTable of Contents
Introduction
Metrics is a new component in IBM® Connections 4.0 supported by IBM Cognos® Business Intelligence, which is installed as a part of a Connections deployment. If Cognos BI is installed on Linux® or AIX, in order to connect to a Microsoft® SQL Server database, an ODBC driver must be installed for the Cognos server.
This article shows you how to set up an ODBC connection to an SQL Server from Cognos BI on Linux/AIX and then configure an ODBC Connection to the SQL Server in Cognos Administration for Connections Metrics.
Installing Process DataDirect Connect for ODBC
Currently, the Process DataDirect Connect for ODBC is the only ODBC driver that IBM Cognos supports for connecting to data located in SQL Server. For detailed information, refer to IBM Support Techdoc #7021368, “
Cognos Business Intelligence 10.1.1 Software Environments."
To install DataDirect software, follow these steps:
1. First, download the Process DataDirect ODBC driver (32-bit),
Connect for ODBC - 32-bit UNIX/Linux Driver, from
http://www.datadirect.com/download.html
NOTE: This is licensed software, and you must register to download it, but you can get a free,15-day trial to test it out.
2. Install the DataDirect ODBC driver on the Cognos BI server. The detailed installation instructions are in “
Installation on Unix and Linux" on the DataDirect Web site.
Setting up ODBC Connection to SQL Server from Cognos BI
To do this:
1. Create a file in the directory called .odbc.ini (i.g, ~/.odbc.ini). A template .odbc.ini is provided where the DataDirect ODBC driver is installed.
2. Edit the .odbc.ini, configuring the SQL Server data source:
- In the [ODBC] section, specify the ODBC root directory and whether Driver Manager tracing is enabled.
- In the [ODBC Data Sources] section, define the name and driver for each data source.
- Create a copy of the [dsn-name] section for each data source defined in the [ODBC Data Sources] section.
- For each data source defined in the [ODBC Data Sources] section, define additional details, using a [dsn-name] section.
Here is a Linux-based example of the odbc.ini configuration with data source for METRICSDS:
[ODBC Data Sources]
- metricsds=DataDirect 7.1 SQL Server Wire Protocol
[ODBC]
- IANAAppCodePage=4
InstallDir=/opt/Progress/DataDirect/Connect_for_ODBC_71
Trace=0
TraceFile=odbctrace.out
TraceDll=/opt/Progress/DataDirect/Connect_for_ODBC_71/lib/ivtrc27.so
[metricsds]
- Driver=/opt/Progress/DataDirect/Connect_for_ODBC_71/lib/ivsqls27.so
Description=DataDirect 7.1 SQL Server Wire Protocol
Database=metrics
HostName=lwptsthink68.cn.ibm.com
PortNumber=1511
To verify that DataDirect and the data source are configured correctly, follow these steps:
1. Set the appropriate library path environment variable to specify the location of the ODBC libraries for your operating system:
Linux: LD_LIBRARY_PATH
AIX: LIBPATH
For example, on Linux:
- export LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect_for_ODBC_71/lib/
3. Go to the "demo" subdirectory in the DataDirect installation directory, and run the command:
“demoobc”
- cd /opt/Progress/DataDirect/Connect_for_ODBC_71/samples/demo
- ./demoodbc -uid metricsuser -pwd ***** metricsds
(set the password as the Metrics database password of your SQL Server)
If you receive the message below, it means your ODBC driver works fine and can connect to your Metrics database (the "EMP" error does not matter):
./demoodbc DataDirect Technologies, Inc. ODBC Sample Application.
./demoodbc: will connect to data source 'metricsds' as user 'metricsuser/password1'.
......SQLExecute has Failed. RC=-1
SQLSTATE = S0002
NATIVE ERROR = 208
MSG = [DataDirect][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid object name 'EMP'.
Defining JVM Variables to Cognos Server
Define ODBCINI and LD_LIBRARY on linux, LIBPATH on AIX, JVM variables to Cognos server. Add the variable to JVM environment variable list:
- Log in to the WAS Administration console of Cognos server.
- Click Servers --- Servers type --- WebSphere application servers
- lick the link of the --- Cognos_server
- Click Java and then Process Management --- Process definition --- Environment entries.
- Edit LD_LIBRARY_PATH on linux, LIBPATH on AIX, adding the "<ODBC Driver Location>/lib" to it.
- Add ODBCINI entry and set "<.ODBC.INI Location>/lib"
- Save the change and restart cognos_server.
Configuring ODBC connection to SQL Server in Cognos BI Administration
To do this:
- Log in Cognos Administrator to Cognos server via http://hostname:port/cognos/servlet/dispatch/ext.
2. Select Launch --- IBM Cognos Administration (see figure 1).
Figure 1. IBM Cognos Administration menu option
3. Click the Configuration tab, select Data Source Connection on the left-hand navigation panel, and click on "METRICS_CUBE_DS" (see figure 2).
Figure 2. Configuration tab
4. Click the Properties button for “METRICS_BUBE_DS” (see figure 3).
Figure 3. Properties button
5. On the Connection tab, change the type to Microsoft SQL Server (ODBC), and then click Edit button (pencil icon; see figure 4).
Figure 4. Connection tab
6. Click the Edit button for "Connection String" and type "metricsds" (the one set in .odbc.ini) in the ODBC data source field (see figure 5).
Figure 5. ODBC data source field
Also, remember to check “Password” in the Signon section at the bottom of the page (see figure 6).
Figure 6. Signon section
7. Click the JDBC tab and input the database information, setting the Database name field to “metrics” (see figure 7). Click OK, to save configuration change.
Figure 7. JDBC tab
Now test the connection to see whether ODBC and JDBC are both working:
1. Click the “Test the connection” link (see figure 8).
Figure 8. “Test the connection” link
2. On the Test Connection page, click “Test”. If the database driver works fine, the ODBC connection test will be successful (see figure 9).
Figure 9. Test Succeeded
Configuring Cognos BI
Add the environment variables ODBCINI and LD_LIBRARY_PATH on linux, LIBPATH on AIX to cron jobs of Congos server.
1.use the following command to edit cron jobs:
crontab -e
2. Add the environment variabls for scheduled cube generation tasks:
For example on Linux, add the following to "daily-refresh.sh" and "weekly-rebuild.sh" lines.
export LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect_for_ODBC_71/lib/; export ODBCINI=/root/.odbc.ini
3. Save the change and restart cognos_server.
/opt/Cognos/Transformer/metricsmodel/trxschelog.log
Verify Metrics works correctly
First, verify the Metrics cube can be built:
1. Set the appropriate library path environment variable to specify the location of the ODBC libraries for your operating system:
Linux: LD_LIBRARY_PATH
AIX: LIBPATH
For example, on Linux:
export LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect_for_ODBC_71/lib/
2. Go to the metrics cube folder, cognos_transformer_install_location/metricsmodel; for example:
/opt/Cognos/Transformer/metricsmodel
3. Run the build-all.sh script to the build cube, and then check the log to verify there is no error. For AIX or Linux:
/opt/Cognos/Transformer/metricsmodel/trxschelog.log
Second, verify that Metrics Report can display properly:
Finally, verify the community metrics can be updated successfully:1. Create a community, using “Start a community”, and select Metrics on left-hand navigation pane.
2. Click Update metrics; Metrics should be able to complete updating successfully and the reports display without error.
Conclusion
You should now understand how to set up an ODBC connection to an SQL Server by DataDirect connect for ODBC from Cognos BI on Linux, and then configure the ODBC connection in Cognos so that Metrics can work to generate and display reports successfully.
Tell us what you think
Please visit this link to take a one-question survey about this article:
http://www.surveymonkey.com/s/9Q6ZKGNResources
About the author
Rong Rong Wang is a Staff Software Engineer based at IBM's Beijing, China, Lab. She currently works as the Team Lead for the Functional Verification Test team for the Connections Moderation feature and also tests Metrics in IBM Connections. She can be contacted at rrwang@cn.ibm.com.