Configure MySql with IBM Cognos BI

I was installing and configuring Cognos BI in my system.

There is very little space in my machine so it was difficult to install some heavy database like Oracle or SQLServer. For now the only available database in my machine is MySql. So I need to manage with that.

MySQL connection configuration does not come by default with Cognos BI. So it need to connect via ODBC connection.
Its strange but I have found in internet that it need to use 32bit version of ODBC driver though your machine is 64bit and even you installed CognosBI 64bit version. I am not sure why but may be it will be changed one day.

Lets download mysql-connector-odbc from https://dev.mysql.com/downloads/connector/odbc/. Download 32bit version. When I am writing this blog, the latest version is 5.3.4. Install the ODBC connector.

Now its the time to make the ODBC configuration.
Lets run C:WindowsSysWOW64odbcad32.exe for the 32 bit ODBC datasource administrator.

Aah! we need a mysql database for the connection.

Wamp is installed in my machine. Lets start wamp server to up the mysql services. You can install mysql separately.
Open phpmyadmin to play with mysql. Create a database.

Lets back to ODBC datasource administrator window.

Go to system DSN -> Click on Add.

Now select MySQL ODBC from the list -> click Finish

It will open MySQL Connector/ODBC Data Source configuration.
Give a datasource name and description (any value) -> If your mysql is running in local machine then enter localhost in the server field-> Enter mysql username/password. If the connection is correct then it will show available tables in the dropdow. You can click on test to check the connection. Select the required database.

Now come to Cognos BI Manager.
Click on Data Sources -> Click Add… -> Select Data Source type ODBC -> Give any name -> Put the created ODBC name in ODBC Data Source -> Click Retrieve Database Objects -> If your connection is correct then value will come in Database Object dropdown list -> Select all and Ok.

In my case below error was coming. SDK-ERR-0111 An error occurred during the creation of the data source shucogno. Please ensure the schema name is correct and the schema contains selectable objects. See the Release Notes for more information.

Because I have created the database but there are no tables created. So remember your database should have at least one table to make successful connection. After I created one table, I retried to create Data Source and the connection was successful.

Now MySQL database is connected with IBM Cognos BI. Lets play 🙂