Setting Up Dynamic Query Mode in Cognos for SQL Server Database

Dynamic Query Mode (DQM) is an intelligent data caching mechanism which is used to improve the performance of OLAP style queries. DQM supports cubes built from TM1, Essbase, SAP BW, Microsoft SQL Server Analysis cubes, and the most common relational databases, specifically DB2, Netezza, SQL Server, Teradata and Oracle.

In order to enable DQM mode, we first need to configure the server with corresponding .jar files. Configuration steps are as follows:

1. Download latest SQL Server JDBC drivers from the Microsoft website: http://www.microsoft.com/en-gb/download/details.aspx?id=11774

2. Download sqljdbc_4.0.2206.100_enu.exe

3. Unzip the file to a file folder location where the Cognos application server is installed. In my machine, i am extracting it in the following location.

C:\Program Files\ibm\cognos\c10\SQL Server JDBC Drivers

 

4. Copy the file sqljdbc4.jar from the extracted folder and paste it in the following locations:

From:    C:\Program Files\ibm\cognos\c10\SQL Server JDBC Drivers\sqljdbc_4.0\enu

To:          C:\Program Files\ibm\cognos\c10\webapps\p2pd\WEB-INF\lib

To:          C:\Program Files\ibm\cognos\c10\v5dataserver\lib

5. Copy sqljdbc_auth.dll and paste it in the bin folder where Cognos is installed.

For 64-bit Cognos install, copy the file

From:    C:\Program Files\ibm\cognos\c10\SQL Server JDBC Drivers\sqljdbc_4.0\enu\auth\x64

To:          C:\Program Files\ibm\cognos\c10\bin64

For 32-bit Cognos install, copy the file

From:     C:\Program Files\ibm\cognos\c10\SQL Server JDBC Drivers\sqljdbc_4.0\enu\auth\x86

To:          C:\Program Files\ibm\cognos\c10\bin

6. Add JDBC driver paths to the file "databaseDriverLocations.properties" which is located in the following path:  C:\Program Files\ibm\cognos\c10\v5dataserver

Note: paths should use double back slashes instead of single back slashes

Open the file ‘\v5dataserver\databaseDriverLocations.properties.sample ‘ and modify the lines

Form:     databaseClasspath=

To:          databaseClasspath= C:\Program Files\ibm\cognos\c10\v5dataserver\lib

From:    databaseJNIPath=

To:          databaseJNIPath= C:\Program Files\ibm\cognos\c10\SQL Server JDBC Drivers\sqljdbc_4.0\enu\auth\x86

If you are using distributed Cognos installation with multiple dispatchers, you need to perform above steps in all the servers. Please note that, no need to copy and update these files in gateway servers.

7. Open Cognos Connection and navigate to Administration panel and go to Data Sources.

8. Click on New Data Source

 

9. Name the Data Source as Go Sales DQM

10. Select Microsoft SQL Server Native Client as connection type and enable "Configure JDBC Connection".

11. Specify the server name where SQL Server database is located and also specify the database name.

12. Provide sign-on details

 
 

13. In the next window, you will need to update JDBC connection details

 

14. Test and Connection and click on Finish.