MySQL is quite possibly the most commonly used database today due to its free license tier making it a very low risk investment. While it is not well geared towards the large queries of reporting and analysis, there may be a need to report directly from an existing MySQL database for any number of reasons.
The issue with MySQL in Cognos, is that it is only supported via general ODBC drivers. The trick to remember with ODBC drivers is that even if the OS and Cognos installation are 64-bit, ODBC drivers must be 32-bit to work with Cognos. Interestingly enough, Cognos Insight can use 64-bit drivers (probably due to it inheriting more from TM1 than Cognos proper). Also note that ODBC drivers cannot run in Dynamic Query Mode. This guide will step you through the process of setting up a MySQL data source specifically, but can be easily adapted to any ODBC driver.
Download and install 32-bit ODBC drivers
- Go to the MySQL ODBC download page and download the 32-bit version of the drivers. The msi version is probably the more convenient of the two, but the zip version should work as well.
- Once downloaded, a simple double click of the msi should start the installation wizard (the zip version is probably different, it should have instructions included).
Set up a 32-bit ODBC connection
- In 64-bit versions of windows, the default ODBC connection program that can be accessed from the start menu is the 64-bit version. To open the 32-bit one, go to Start->Run and enter the following: C:\Windows\SysWOW64\odbcad32.exe (this may vary slightly based on the system setup).
- Select the System DSN tab and click the Add button. This allows any user access to the connection information.
- From the list, select the MySQL 3.51 Driver from the list (version number may vary).
- Set up the connection info to reflect the MySQL database you are trying to connect.
- Press the OK button and it should now show up in the list.
Create a new Cognos Datasource
- Open a web browser and log in as an admininstrator to Cognos.
- If not already open, go to IBM Cognos Administration and select the Configuration tab. The Data Source Connections should be automatically selected.
- Select the New Data Source button on the upper right part of the screen.
- Give it a name and press the Next button.
- Select ODBC from the Type list. Leave everything else as it is and hit Next.
- Enter the ODBC data source, User ID and Password as they appear on the ODBC database. Use the Test Connection link on the bottom to confirm the setup.
- Press Finish and the connection should now be visible in the list:
Change the Project Query Mode
- Open up Framework Manager and project the data source is for.
- Select the Project at the top of the tree view on the left side of the window.
- Under the properties tab at the bottom of the screen, change the Query Mode option from the default Dynamic to Compatible.
It should now be possible to use this MySQL database in any packages in this project.