Sunday, 18 August 2013

Accessing MySQL Database in Cognos using ODBC

Installing MySQL Server
Install MySQL server on the same system where Cognos server is installed. Create a Database with different tables.
Assigning Access Permission
To get access of MySQL database, access permission must be granted by administrator. We can use GRANT command to assign the permission.
·         Grant usage on *.* to user@[IP address] identified by 'password';
Flush privileges;
·         Grant all privileges on *.* to root@'%' identified by 'password';
Flush privileges;
·         Grant all privileges on *.* to root@[IP address] identified by 'password';
Flush privileges;
Installing MySQL Connector
MySQL Connector is the driver for connecting to MySQL database server to ODBC.
Creating ODBC DSN
Create a System DSN for MySQL with all details

Creating Cognos Data Source
Create a Data Source in Cognos Administration for MySQL. Select ODBC data source and select newly created system DSN.
Creating Data Model
Create a data model in Cognos Framework Manager for MySQL Data Source.
Developing Reports
Start developing different reports.

Note:    1.) Cognos Server and MySQL Server must be installed on same system
2.) Users of different system must have the access permission which needs to be assigned from   MySQL
3.) Must check the MySQL Driver compatibility with Cognos