Connecting QuerySurge to IBM Cognos Analytics

Versions: 8.0+

Versions:6.0+

QuerySurge's end-to-end data testing solution provides testing of all legs in the ETL process, and the QuerySurge BI Tester data source feature extends the solution to the Business Intelligence layer. BI Tester for IBM Cognos Analytics allows QA team members to automate the process of retrieving data from Cognos reports and validating it against any supported data source. Users can use powerful SQL syntax to query the data query layer of reports, compare report data to data from upstream or downstream systems, create suites of multiple BI report tests, and schedule tests for execution according to BI system availability.

For an IBM Cognos Analytics connection, you will need the following information (check with a Cognos administrator or other knowledgeable resource in your organization):

• Server version
• Server hostname and port (default Cognos server port: 9300)
• Username and password
• Authentication Namespace (see section below on finding the namespace ID)
• Dispatcher Servlet path (default: p2pd/servlet/dispatch)
• Cognos SDK JAR files

Note: QuerySurge's BI Tester feature is licensed separately. Contact QuerySurge Sales for information.

Deploy Required Cognos SDK JAR Files

To establish connectivity with Cognos, you will need to extract several components from the Cognos Software Development Kit (SDK). Download the Cognos SDK version for your Cognos server, or obtain it from a Cognos-knowledgeable resource in your organization, and install the SDK. The following JAR files can be found in <SDK install dir>/sdk/java/lib; copy them to your QuerySurge Agent driver folder, <QS install dir>/agent/jdbc.

• activation.jar
• cognos-axis.jar
• cognosClient.jar
• commons-discovery-0.2.jar
• commons-logging-1.1.jar
• commons-logging-adapters-1.1.jar
• commons-logging-api-1.1.jar
• jaxen-1.1.1.jar
• jaxrpc.jar
• mail.jar
• saaj.jar
• testIBMFakeTrustSocketFactory.jar
• wsdl4j-1.5.1.jar

Note: Copy only the files listed above; do not include any other JAR files from the SDK installation. It is recommended that even for open-source libraries, that you use the libraries distributed with the SDK.

Find the Authentication Namespace

In order to find the correct Authentication Namespace for the desired Cognos user, a Cognos administrator or server administrator (depending on available permissions) can look it up in one of the following configuration views:

• Using the Cognos web interface (useful when Cognos server access is not available):
  1. Log in to Cognos as an administrator user
  2. Navigate to Manage -> People -> Accounts
  3. Mouse over the relevant namespace, click the menu button, and click Properties
  4. In the namespace properties pane, click the Advanced header
  5. In the Search Path field, note the value enclosed in the parenthesis and double quotes (for example, a Search Path of CAMID("CorpDirectory") would yield a namespace ID of CorpDirectory).
• Using the IBM Cognos Configuration utility (useful when Cognos is installed on Windows servers):
  1. Connect to the Cognos server
  2. Launch IBM Cognos Configuration (see the Cognos documentation for OS-specific steps)
  3. In the Explorer tree, navigate to Local Configuration -> Security -> Authentication, and then the relevant namespace
  4. In the Resource Properties pane, note the listed Namespace ID
• In Cognos configuration files (useful on Linux servers or when GUI access to the Cognos server is not available):
  1. Connect to the Cognos server
  2. Open the file <Cognos install dir>/configuration/cogstartup.xml
  3. Search in the file for the namespace name; it should appear in an element like <crn:instance name="Corporate Directory" class="LDAP">
  4. Locate the “id” parameter below that element; it should appear in an element like <crn:value xsi:type="xsd:string">CorpDirectory</crn:value>

Connect to Cognos

1. Log into QuerySurge as an Admin user. Navigate to the QuerySurge Admin view.
2. To configure a Cognos Connection, launch the Connection Wizard. Select Configuration > Connection in the Admin view tree (at the left).
3. Click on the Add button at the bottom left of the main panel to launch the Connection Wizard. Check the Advanced Mode checkbox. Click Next.
   
   
4. Provide a name for your Connection. Then, select "BI Tester: Cognos" as the Data Source.
   
   
   Note: The BI Tester JDBC driver is bundled with QuerySurge; install this driver on your Agent(s) from your QuerySurge Installer.
5. Once you have selected your BI Tester Data Source, the Wizard will tell you what information you are likely to need in order to create your Connection. Once you have collected this information, you are ready to click Next.
   
   
6. Provide the connection information to your Cognos server. This includes the server hostname, port (the default port will automatically populate), Dispatcher Path, Authentication Namespace, Cognos version, and login credentials. Required fields for your Connection Type are marked by an *. Click Next.
   
   
   
   Note: QuerySurge only supports specific Cognos versions at this time. If your version does not appear in the dropdown, contact QuerySurge Support to see if there are any options.
   
   
7. In the Advanced Mode, several additional options are available. Adjust these options as needed and click Next.
   
   Max String Width: Maximum size of all "string" or "text" columns returned in a query on this connection. Values beyond this size will be truncated to fit. Defaults to 500 bytes.
   
   Temporary Data Location: Whether data retrieved from Cognos should be processed in memory, or in a temporary file on disk. In Memory provides better performance, but requires enough Agent heap memory for the datasets to be queried through this connection. (See instructions for adjusting heap on Linux or Windows.) On Disk provides capacity for larger result sets at the expense of slower performance. Defaults to In Memory.
   
   Protocol: When connecting to Cognos installations configured for SSL/TLS, select https to handle the encrypted traffic. Defaults to http.
   
   
8. Click on the Test Connection button.
   
   
   
   Note: You must have an Agent running with the driver deployed for this Connection deployed in order to test the Connection.
9. Click the Save button to save the Connection.
10. Congratulations! You’ve created a QuerySurge Connection. Again, make sure that you have deployed the driver for this Connection to all your QuerySurge Agents. (See Adding a JDBC Driver to your QuerySurge Agent for instructions.)