Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar
delete

Search for    in all of Sybase.com
view all search results right arrow
  blank
 
 
 
 
 
 
 
 
 
 

 
 
CLICK TO EXPAND NAVIGATION
CLICK TO EXPAND NAVIGATION
 
 
 
 
Support > Technical Documents > Document Types > Partner Certification Report > IBM Cognos BI Server 10.1.1 with Sybase IQ 15.4, R...

IBM Cognos BI Server 10.1.1 with Sybase IQ 15.4, Report 382, August 2012

This report describes the certification of IBM Cognos Business Intelligence Server version 10.1.1 with Sybase IQ version 15.4 using ODBC connectivity.
 
RSS Feed
 
 
 

 

Contents


Overview

This report describes each of the products tested and how they were used during testing. Only the platforms and products tested are reflected in this certification, but other possible combinations of these products and platforms may be used. For more information about Sybase products, including known issues not encountered during this certification or those occurring on other platforms, see the Release Bulletin for the specific product, platform and version. For more information about any of the products, please refer to the product literature or manufacturer website.

  • IBM Cognos Business Intelligence Server version 10.1.1 (hereafter called "Cognos Server") served as the Cognos enterprise reporting software. It includes the Cognos Report Server, Cognos Configuration, Cognos Connection Web portal, Report Studio and Query Studio report design tools, which are Web applications. The 64-bit versions of the software were installed on a 64-bit Microsoft Windows 2008 machine as well as a SUSE Linux Enterprise 11.0 machine. See the Cognos 10.1.1 Business Intelligence Architecture and Deployment Guide for an overview of the Cognos Server architecture. This guide includes a discussion of workflows and environmental configuration options. The Cognos Server installation program provides the option to install the following required software component, which was done in the test environment:
    • Apache Tomcat Application Server version 6 (hereafter called "Tomcat") hosted the Cognos Connection Portal, Report Studio and Query Studio Web applications.
  • Java 6 satisfied the requirement for a Java environment. Different versions were used on the different platforms:
    • On Linux, the IBM Java 6 Development Kit (hereafter called "Java 6 JDK") was downloaded from the IBM website, installed and used.
    • On Windows, the Java 6 Java Run-time Environment (hereafter called "Java 6 JRE") from the Sybase ASE product installation was used.
  • Web servers were configured to serve Cognos content. The Web server is not a Cognos installation option like the application server. A Web server must be obtained separately. Two different servers were used for the different Cognos Server platforms:
    • Microsoft Internet Information Server (IIS) version 7.5 (hereafter called "Microsoft IIS") served as the Web server for the Cognos BI Server installed on the Windows machine. It was configured to serve Cognos content from the Cognos installation webcontent and cgi-bin directories. IIS is included as an option you can enable in the Windows 2008 R2 Server operating systems.
    • IBM HTTP Server version 7 (hereafter called "IBM HTTP Server") served as the Web server for the Cognos BI Server installed on SUSE Linux. It was also configured to serve Cognos content form the Cognos installation webcontent and cgi-bin directories. Note that the SUSE Linux Installation Manager provides the option for you to install the Apache Web Server. However, it only installs the 64-bit version because it detects it as appropriate for the operating system. The Cognos BI Server on Linux requires a 32-bit server. Thus, you cannot use the Apache version. Thus, the IBM HTTP Server was installed. It is available from IBM as a supplement to the IBM WebSphere application server.
  • IBM Cognos Business Intelligence Modeling version 10.1.1 (hereafter called the "Cognos Modeling Software") provided the meta-data management and modeling software. The modeling software is a 32-bit Microsoft application. It was installed and run on a 64-bit Microsoft Windows 7 machine. Two of its components were tested:
    • Cognos Framework Manager facilitated the import and packaging of Sybase IQ meta data for use in Cognos reports. Being a 32-bit application, Framework Manager requires the 32-bit Sybase IQ ODBC driver to connect to Sybase IQ.
    • Cognos Configuration served as the Cognos environmental configuration tool. It was used to configure the modeling software to connect to the two different Cognos BI Server applications.
  • Sybase IQ Analytics Server version 15.4 64-bit (hereafter called "Sybase IQ") served as the reporting data source. The Sybase IQ tested was a 64-bit application installed and running on a 64-bit Sun SPARC Solaris workstation. This workstation was on a different machine but on the same TCP/IP network as the machines where the Cognos software was installed.
  • 32-bit Sybase IQ ODBC driver version 12.0 (hereafter called the "Sybase IQ ODBC driver") enabled ODBC connectivity between the Cognos reporting engine and the target Sybase IQ data source. The Sybase IQ ODBC driver is installed with the Sybase IQ 15.4 Database Client. The version for Windows was installed on both the Cognos BI Server and Cognos Modeling software machines. The version for Linux was installed on the Cognos BI Server Linux machine. Note that the 12.0 driver is the version that is installed with the 15.4 version of the Database Client.
  • Microsoft Internet Explorer version 7.0 (hereafter called "Internet Explorer") served as the browser that provided user access to the Cognos Connection Portal, in which the Report Studio and Query Studio Web applications were run.
  • Sybase Adaptive Server Enterprise version 15.7 (hereafter called "ASE") hosted the Cognos BI Server repository, which is called the Cognos Content Store. Two 64-bit ASE servers were configured to host Cognos Content Stores: one for the Cognos BI Server on the SUSE Linux machine and one for the Cognos Content Server on the Windows machine.
  • Sybase jConnect JDBC driver version 6.05 (hereafter called the "Sybase JDBC driver) enabled connectivity between the Cognos BI Server components and the Content Store in Sybase ASE. Sybase JDBC drivers were installed and configured on each of the Cognos BI Server machines.

Click here for trademark and registration information.

For more detailed information about the machines used in this environment, see the section Test Environment later in this report.

The following diagram illustrates the test environment.

environment

Back to Contents


Tests Performed

This section contains a detailed explanation of the tests performed.

  • Used the Cognos Configuration application for the following tasks on the Cognos BI Server machines:
    • Configure the Cognos BI Servers Gateway, Dispatcher and Portal Services properties
    • Configure the Cognos BI Server to use Sybase ASE as the Cognos Content Store (repository)
    • Start the Cognos service.
  • Used the Cognos Configuration tool on the Cognos Modeling machine to configure the Cognos BI Server to which the Cognos Modeling software deployed. The same Cognos Modeling software machine was used to test both the Cognos servers on Windows and Linux. Congnos Configuration was used change the configuration environment to point to the different servers.
  • Used the Cognos Framework Manager application to import Sybase IQ meta data, create a relational model, create a package of Sybase IQ data, and publish the package to the Cognos Connection portal for use in Cognos reports. The Framework Manager tests included exercising the following features:
    • Creating projects
    • Defining data sources for Sybase IQ in the projects. See Test Note 1.1 for information about Framework Manager data sources and ODBC data sources.
    • Using the Meta Data Wizard to import Sybase IQ meta data into the projects. Note that some of the data were imported with the primary and foreign key definitions defined in Sybase IQ and some were imported without relationships. Also note that different import options were tested like whether or not to create inner or outer joins on tables. See Test Note 1.2 for information about this option and how it may influence query results in Report Studio.
    • Viewing and validating the models created from the imported data.
    • Creating and publishing packages of Sybase IQ data to the Cognos Connection portal.
  • Used the Cognos Connection portal, via Internet Explorer on a Windows machine, to access the Query Studio and Report Studio design tools and available data packages.
  • Used Query Studio to build simple reports.
  • Used Report Studio, and its Query Builder functionality, to build reports with more complex query functions. This included using the following Query Builder and Expression Builder features:
    • sub-queries
    • CASE statement constructs
    • CAST statements
    • aggregate (summary) functions
    • aliases
    • automatically applied aggregates like Total
    • data value selection
  • Tested other Report Studio features:
    • data groupings, which are applied in the Page Explorer view
    • formatting reports using the provided List template
    • using the underlying query Processing property set to "Database Only" to force the processing to occur in the Sybase IQ reporting database. For information about how to specify this option, see Test Note 1.5.
  • Viewed and published reports through the Cognos Connection portal.
  • Tested the effects of a Sybase IQ reporting database failure during report run-time. See Test Note 1.7 for the resulting behavior.
  • Evaluated Cognos components for their ability to handle supported Sybase IQ data types. The results are documented in the Data type tests notes section of this report.

Back to Contents


Setup and Configuration

The following information relates to the installation, setup and configuration of the test environment. This information is divided into the following sections:


Tasks on the Sybase IQ server machine

Install the Sybase IQ 15.4 server according to the Sybase IQ Installation and Configuration Guide for your platform. If you wish to use the Sybase IQ demo database, see the section "Creating and using an IQ demo database" in the Sybase IQ Quick Start Guide.

Back to Contents


Tasks on the Cognos BI Server machines

This section describes the setup tasks required on the Cognos BI Server machines:

Back to Contents | Back to Setup and Configuration


Install the Cognos pre-requisites

Before you install Cognos, read the Cognos Installation and Configuration Guide 10.1.1, especially the "Preparing to Install" section. This section describes how to install the Cognos BI Server pre-requisites.

At the time this report was written, the Cognos Business Intelligence 10.1.1 Supported Products page provided a list of supported products and versions at: http://www-01.ibm.com/support/docview.wss?uid=swg27021368.

Install Java 6

Several of the Cognos BI Server 10.1.1 components use Java version 6. This section describes how the Java environment was installed and configured in the Cognos BI Server environments:

  • On Linux, the IBM Java 6 SDK was downloaded from the IBM website and installed.
  • On Windows, the Java 6 installation that was included with the Sybase ASE 15.7 server, which hosted the Cognos Content Store, was used. If you want to use this version, proceed to the section about installing Sybase ASE to host the Cognos Content Store.

Install and Configure the Cognos Content Database

The Cognos BI Server supports several databases to host its repository, the Cognos Content Database. The installation programs provide the option to install the Apache Derby Database. Sybase ASE is supported and was used in the test environment. Sybase IQ is not supported as a Cognos Content Database.

While it is not required, you can install and configure the Cognos Content Store before you run the Cognos BI Server installation. The installation process does not interact with the Cognos Content Store. The configuration process does. You use the Cognos Configuration tool to configure the Cognos Content Database and Store.

Installing and configuring Sybase ASE

If you choose to use Sybase ASE to host the Cognos Content Store, see the Sybase White Paper: Cognos BI Server 10.1.1 Content Store in Sybase ASE 15.7A summary of the tasks described in the white paper follows:

  1. Install Sybase ASE and create a server with a page size of 8K and UTF-8 character set according the Sybase Adaptive Server Enterprise Installation and Configuration Guide for your platform.
  2. Create a database device, database, login and user in Sybase ASE to host the Cognos Content Store.
  3. Enable the ASE database option select into/bulk copy/pll sort.

Installing and configuring the Sybase JDBC driver

  1. Install Sybase jConnect 6.05 JDBC driver to enable connectivity between the Cognos Configuration tool, or the Cognos Server once its up and running, and the Cognos Content Store in ASE.
  2. Configure jConnect by setting the required environment variables for connectivity: $JAVA_HOME, $JDBC_HOME and $CLASSPATH as described in the aforementioned Sybase white paper. These variables need to be set in the environment where the Cognos BI Server installation program will run and in the Cognos BI Server run-time environment.

Back to Contents | Back to Setup and Configuration


Install the Sybase IQ database client

Cognos supports ODBC connectivity to Sybase IQ as a reporting database. The Cognos report engine, which is part of the Cognos Application Tier, is the component that actually connects to Sybase IQ. Thus, the Sybase IQ ODBC driver, which is included in the Sybase IQ Database Client software, must be installed on the same machine as the Cognos Application Tier. IBM provides instructions for setting up Cognos connections to the reporting databases under the topic "Database connectivity for the Reporting Database" in the Installation and Configuration 10.1.1 guide (http://publib.boulder.ibm.com/infocenter/cbi/v10r1m1/index.jsp). Note that this section is provided in both the "Installing and Configuring IBM Cognos BI Components on One Computer" and in the "Installing and Configuring IBM Cognos BI Components on Different Computers."

Sybase provides information about configuring the Sybase IQ ODBC driver in the Sybase White Paper: Basic ODBC Connectivity for Sybase IQ 15.4 and SA 12.0.1.

  1. Install the 32-bit Sybase IQ ODBC driver on the Cognos Application Tier and reporting engine machine. The Sybase IQ Database Client software installation differs on the Windows and Linux platforms as follows:
  • On the 64-bit Microsoft Windows operating system on which the 64-bit Cognos BI Server was installed, the Sybase IQ 15.4 Database Client includes both the 32-bit and 64-bit ODBC drivers, as well as the jConnect 7.0 JDBC driver and other client files. To avoid environment conflicts with the jConnect 6.05 JDBC driver that provides connectivity to the Cognos Content Store in ASE, we recommend performing a Custom-type installation of the Sybase IQ Database Client and selecting only to install the ODBC drivers. The following image shows the Custom installation selections made during testing on Windows Cognos Server machine.
  1.  
    • On the 64-bit SUSE Linux machine, the 32-bit Sybase IQ Database Client was installed. It installs the 32-bit ODBC Driver, other client files and the jConnect 7.0 JDBC files. To avoid environment conflicts with the jConnect 6.05 JDBC driver that provides connectivity to the Cognos Content Store in ASE, we recommend performing a Custom-type installation of the Sybase IQ Database Client. In this case, we recommend selecting Sybase IQ Client files and ODBC driver. The Sybase IQ Client files include utilities, such as iqdsn, which can be helpful while configuring ODBC connectivity on Linux and UNIX. The following image shows the Custom IQ Client installation selections made during testing on Linux Cognos Server machine.
  2. Sybase IQ Client install selelctions

    Sybase IQ Client selections Linux

  3. Configure a data source name (DSN) for the Sybase IQ reporting database using the Sybase IQ ODBC driver. Instructions for configuring a DSN for Sybase IQ for Windows and UNIX/Linux operating systems are provided in the Sybase White Paper: Basic ODBC Connectivity for Sybase IQ 15.4 and SA 12.0.1. In summary, creating a data source involves using the ODBC Administrator on Windows or an odbc.ini file on Linux and setting the $LD_LIBRARY_PATH and $ODBCINI environment variables in the Cognos Server run-time environment.

Back to Contents | Back to Setup and Configuration


Set the environment variables for the Cognos run-time environment

In order for the Cognos BI Server to run properly, it needs to access many different files and libraries, including those for Sybase jConnect JDBC connectivity for the Cognos Content Store in ASE and ODBC connectivity for the Sybase IQ reporting database. Thus, you need to be sure all the required environment variables are set.

Windows

On Windows, most of the installation programs set the environment variables for their products. For example, the Sybase ASE data server and the Sybase IQ Database Client and ODBC driver installation programs set the variables and registry entries they need. The exception to this are the variables required by the Sybase jConnect JDBC driver.Thus, you need to manually set the jConnect variables on the Windows Cognos BI Server as follows.

Sybase jConnect JDBC driver required variables

  • %JAVA_HOME% - set to the Java installation. If you use the Java 6 JRE from the default Sybase ASE 15.7 installation location, %JAVA_HOME% would be set to c:\Sybase\jre64.
  • %JDBC_HOME% - set to the Sybase jConnect 6.05 installation directory, which is the jConnect 6.05 root installation directory and the jConnect-6_0 directory.
  • %CLASSPATH% - set to include the jConnect 6.05 jar file and the class directory. If you install jConnect 6.05 in a directory on the c drive named jconn6, the entries in the class path should be C:\jconn6\jConnect-6_0\classes\jconn3.jar;C:\jconn6\jConnect-6_0\classes.

Linux

On Linux, the Sybase installation programs do not set the environment variables but provide scripts to do so. There are, however, exceptions to this. None of the Sybase scripts set variables for the jConnect JDBC driver or the odbc.ini file. For the Cognos run-time environment, you will need a mix of the variables set in the Sybase scripts and the ones you need to set manually. We recommend creating a script to set all the variables you need.

  • For jConnect, the variables listed above are also required.
  • For the Sybase IQ ODBC driver, the lib32 directory of the Sybase IQ Database Client install must be added to the $LD_LIBRARY_PATH and the $ODBCINI variable must be set to the location of the odbc.ini file where the data sources names (DSNs) for the Sybase IQ reporting databases are defined. The /usr/lib, Cognos bin64 and cgi-bin directories were also included in the $LD_IBRARY_PATH.
  • Other variable settings: In the console where Sybase ASE ran, the SYBASE.sh script was run and the $LANG variable was set to en_US.UTF-8. In the console where the IBM HTTP Server ran, the $LD_LIBRARY_PATH was set to load the Cognos cgi-bin directory. These environments were kept separate from the Cognos BI Server environment.

Back to Contents | Back to Setup and Configuration


Install the Cognos BI Server

Before you install Cognos, read the Cognos Installation and Configuration Guide. Chapters 6 (for installing all Cognos compononents on one machine) and 7 (for installing Cognos components on different machines) include a checklist of tasks to perform for the installation. Several of the Cognos components run as Web applications. The Cognos Server installation provides the option to install the 64-bit Apache Tomcat application server, which was done in the test environment.

Install the Cognos BI Server components as described in the installation guide.

Windows

Proceed through the installation program on Windows. Note the following things while installing:

  • The default installation directory is C:\Program Files (x86)\ibm\cognos\C10_64, which is unusual for 64-bit applications. Most 64-bit applications are installed under C:\Program Files by default.
  • The default component selections were: Application Tier Components, Gateway, and Content Manager. The Cognos Content Database component was not selected by default and should not be selected if you plan to use Sybase ASE to host the Content Store.

Windows BI Server install panel

This completes the Cognos BI Server installation process, which does not configure the server. Instructions for configuring the server are provided later in this report. Proceed to the section "Install and configure the Web Server" to continue setting up the Cognos BI Server components.

Linux

Proceed through the installation program on Linux. Note the following things while installing:

  • The default installation directory is /opt/ibm/cognos/c10_64. Be sure you have sufficient space for the installation in whichever directory you choose as described in the Cognos installation guide.
  • The default component selections were: Application Tier Components, Gateway, and Content Manager. The Cognos Content Database component was not selected by default and should not be selected if you plan to use Sybase ASE to host the Content Store.

This completes the Cognos BI Server installation process, which does not configure the server. Instructions for configuring the server are provided the following section, "Install and configure the Web Server," to continue setting up the Cognos BI Server components.

Back to Contents | Back to Setup and Configuration


Install and configure the Web Server

The Cognos BI Server reporting environment includes a Web server, which will serve the reports for viewing via the Web. The Cognos BI Server installation programs do not provide the option to install one, however. They provide the option to install the Tomcat application server, but not a Web server. Thus, you must obtain and install a Web server separately. The Cognos Planning Installation and Configuration 10.1.1 guide provides information about this task in Chapters 6 and 7, "Configure the Web Server," and a list of supported Web servers is provided on the IBM Supported Software site for Cognos BI Server 10.1.1.

How you configure the Web server varies and the one you use depends on the platform on which you are installing. This task is divided into the following three sections:

Back to Contents | Back to Setup and Configuration


Install and configure IIS on Windows

For a Cognos BI Server installed on the 64-bit Windows machine, you can use the Microsoft Internet Information Server (IIS) Web server. IIS is included with the 64-bit Windows 2008 operating system as a feature you can enable. See the Test Environment section near the end of this report for the exact version of the Windows operating system used in the test environment. To enable this feature, use a login with administrative privileges and perform the following steps:

  1. Display the Turn Windows features on or off dialog box by navigating to Start | Control Panel | Programs | Turn Windows Features on or off. It may take a few minutes for the list to populate.

Windows Features IIS

  1. Select Internet Information Services, Web Management Tools and World Wide Web Services.
  2. Use the Server Manager to select the Role Services for IIS as follows:
    • Common HTTP Features: Static Content, Default Document, Directory Browsing, HTTP Errors, HTTP Redirection and WebDAV Publishing
    • Application Development: CGI, ISAPI Extensions and ISAPI Filters
    • Health and Diagnostics: HTTP Logging and Request Monitor
    • Management Tools: IIS Management Console, IIS Management Scripts and Tools, and Management Service
    • Other selections made in the test environment: Security: Request Filtering, Performance: Static Content Compression
    • When you finish, you should see a messaging saying "Installation of Web Server role" successful. You can then display the services. If they are not listed with "(Installed)" after them, then you will need to install them.
      IIS Role Services
  3. Open IIS Manager by navigating to Control Panel | System and Security | Administrative Tools | IIS Manager. The IIS Manager home page opens.
    IIS Manager Home page
  4. Verify the IIS Default Web Site is up by displaying its home page in a Web browser. For example, the IIS Default Web Site runs on port 80. You can enter http://machine_name:80 to display its Welcome page. These instructions describe using the Default Web Site. If you are not using a non-default IIS web site, take that site into consideration while following these instructions.
    IIS Welcome page
  5. From within IIS Manager, you create two virtual directories for Cognos as described in Chapters 6 abd 7 of the Cognos Installation Guide by right-clicking the Web site and selecting Add Virtual Directory. The Add Virtual Directory dialog box opens
    1. Create the ibmcognos directory, which maps to the c10_location\webcontent directory first as shown below, and click OK to save it. The c10_location directory is the Cognos installation directory.

    2. Repeat the above step for the cgi-bin directory, which maps to the ...\ibm\cognos\c10_64\cgi-bin directory and click OK to save the directory. When you finish, you should see both directories listed under the Web Site. Note that you may have to expand the ibmcognos directory to see the cgi-bin directory.
  6. Use the default permissions on these directories, which for "Users" are "Read & Execute," "List Folder Contents" and "Read permissions." You can see the permissions on a folder by selecting it, right-clicking and choosing Edit Permissions. The Cognos documentation says to edit the permissions on both directories, setting the ibmcognos directory to "Read" and the cgi-bin directory to "Execute." It also says to "...enable Read and Directory browsing properties for the URL you want to access." Directory Browsing is an IIS folder attribute. However, these settings seem to be for an earlier version of IIS.
  7. Edit the ISAPI and CGI Restrictions and choose the option to "Allow unspecified...modules" for each CGI and ISAPI.
    Edit ISAPI and CGI extensions
  8. Stop and start the Web site and verify it is working.

This completes the instructions for installing and configuring IIS. Proceed to the section Verify the Web server configuration to verify the IIS configuration for Cognos.

Back to Contents | Back to Setup and Configuration


Install and configure IBM HTTP Server on Linux

The 64-bit Cognos BI Server for Linux requires a 32-bit Web server. The IBM Supported Software page for Cognos 10.1.1 says that the version 7.0 32-bit IBM HTTP Server is supported on Linux and it was used in the test environment. The IBM HTTP Server is available from IBM as a supplement for the IBM WebSphere 7.0 Application Server, although you do not have to install WebSphere to use it. IBM provides information about configuring this Web server in the Planning Installation and Configuration 10.1.1 guide, in the section "Steps for Apache Server 2.2.x and IBM HTTP Server 7.0," in the "Additional Configuration Options" section: http://publib.boulder.ibm.com/infocenter/cfpm/v10r1m0/index.jsp?topic=%2Fcom.ibm.swg.im.cognos.cp_inst_cr_winux.10.1.0.doc%2Fcp_inst_cr_winux_id11149ConfigureApachegateway.html

To summarize, it lists the following tasks for Linux:

  1. Install the IBM HTTP Server, or stop it if it is running.
  2. Open the httpd.conf file from the conf directory of the IBM HTTP Server installation in a text editor. We recommend making a back-up copy of the original file.
  3. Add the following to the end of the LoadModule list. Note that c10_location shown below and in the rest of the examples should be replaced with the directory structure for the Cognos installation in your environment.
LoadModule cognos_module "c10_location/cgi-bin/mod_cognos.so"
  1. Add the following to the end of the AddModule list:
AddModule mod_cognos.cpp
  1. Add a ScriptAlias for the Cognos cgi-bin directory. Important! Add the ScriptAlias before the Alias in the httpd.conf file. Note that the IBM instructions list this as step 8, "Add the following Apache Directive." The IBM instructions combine the Alias and ScriptAlias entries in one directive that is formatted differently than the one used in the test environment. It says the "Directory" directive is optional but it was used in the test environment. In the test environment, the ScriptAlias entry created by default was modified for the Cognos environment as shown below:
ScriptAlias /ibmcognos/cgi-bin "c10_location/cgi-bin" <--This was originally ScriptAlias /ibmhttpserver/cgi-bin  "ibmserverinstall/ibmhttpserver/cgi-bin"







<IfModule mod2_2_cognos.c> CGIBinDir "c10_location/cgi-bin" </IfModule>
<Directory "c10_location/cognos/cgi-bin"> AllowOverride None Options None Order allow,deny Allow from all </Directory>
  1. Add an Alias directory for the Cognos webcontent directory after the ScriptAlias for the Cognos cgi-bin directory. In the test environment, the Alias was added below the ScriptAlias entry as shown below:
Alias /ibmcognos "c10_location/webcontent"
<Directory "c10_location/webcontent"> Options Indexes MultiViews AllowOverride None Options None Order allow,deny Allow from all </Directory>
  1. Add the following to the server status reports section:
<Location /ibmcognos/cgi-bin/mod_cognos.so>
	 SetHandler cognos-handler

</Location>
  1. If you want to enable the Gateway diagnostics page, add the following to the server status reports section:
<Location /ibmcognos/cgi-bin/diag_mod_cognos.so>

SetHandler cognos-handler
</Location>
  1. Add the following to the user directory section:
<IfModule mod_cognos.cpp>
CGIBinDir "c10_location/cgi-bin"
</IfModule>
  1. Save and close the httpd.conf file.
  2. Add the Cognos cgi-bin directory to the $LD_LIBRARY_PATH and set it in the IBM HTTP Server run-time environment.
  3. Restart the IBM HTTP Server by changing to the IBM HTTP Server installation bin directory and entering the command: ./apachectl start. It will report any issues with the http.conf file to the console. Troubleshoot any errors you may see. Otherwise, you can grep for ibm to see the IBM HTTP Server process running. The command to stop the IBM HTTP Server may also be run from the bin directory: ./apachectl stop.

Proceed to the following section, Verify the Web server configuration, to verify that the IBM HTTP Server is configured properly for Cognos.

Back to Contents | Back to Setup and Configuration


Verify the Web Server configuration

This section describes how to verify that you correctly configured your Web server for Cognos. These steps are appropriate for whichever Web server you are using:

  1. Open a Web browser. In the "Verify System Requirements" section of the "Preparing to Install" section of the Cognos Installation and Configuration Guide, IBM says to set the following browser settings for all browsers: cookies and JavaScript. For Microsoft Internet Explorer only, also set Run ActiveX controls and plug-ins, Script ActiveX controls mared safe for scripting, Active Scripting and Allow META REFRESH. The settings available do vary with the version of the browser.
  2. Navigate to the following URI:
http://host_name:port/ibmcognos/cgi-bin/cognos_module
where host_name is the machine name or ip address of the machine where your Web Server is running and port is the port on which your Web server is running. This is 80 by default for IIS or the IBM HTTP Server. If the Cognos service is not running, and these instructions have not told you to start it yet, you will see a message indicating the Cognos gateway is unable to connect to the Cognos server. The section Verify the Cognos BI Server shows that the Cognos Connection portal displays when the Cognos service is running and the Web servers are configured properly.

Verify Web Server without Cognos Service

Proceed to the next section for information about configuring the Cognos BI Server.

Back to Contents | Back to Setup and Configuration


Configure the Cognos BI Server

Configuring the Cognos BI Server involves the following tasks:

  • Copying the JDBC driver file for Cognos Content Database access to the Cognos installation directory structure
  • Using the Cognos Configuration tool to specify the JDBC connection information for the Content Database
  • Changing all occurrences of localhost in the gateway and dispatcher URIs in the configuration.
  1. Configure Cognos to use the Sybase jConnect JDBC driver to connect to the Content Store in Sybase ASE by copying the jconn3.jar file to the Cognos c10_location/webapps/p2pdWEB-INF/lib directory, where c10_location is the Cognos BI Server installation directory.
  2. Launch the Cognos Configuration tool to configure the Cognos Server environment. On Windows, you launch Cognos Configuration from the Start menu. On Linux, you launch the cogconfig.sh script from the bin64 directory in the Cognos installation directory structure.
    1. Select Environment in the left-pane and change all occurrences of localhost to the machine name in the dispatcher and gateway properties. The following screen shows the occurrences of localhost replaces with aejsmiths11b.
      Cognos Configuration Environment
    2. Select Portal Services and change the occurrence of localhost there.
    3. Configure the security settings. The Cognos documentation describes these changes but they were not tested.
    4. Configure connectivity to the Cognos Content Database, which includes providing the connection information for the target Sybase ASE database. By default, IBM Cognos content database is specified for the Type. You will need to select Cognos Content Store in the left pane and delete this database. Then select the Cognos Content Store and choose New | Resource | Database and add information about the Sybase ASE database. See the Sybase White Paper: Cognos 10.1.1 BI Server Content Store in Sybase ASE 15.7 for details of this configuration. Select Cognos Content Store in the left-pane again, right-click and choose Test from the pop-up menu. This will test whether or not Cognos can connect to the Content Store.

    Cognos Content Store configuration

    1. Save the changes. Cognos Configuration will display the status of the tasks it performs when saving the changes. One of these tasks, Generating Cryptographic information, may or may not pass. If it does not, it is okay to ignore the warning.
  3. Start the cognos service by locating IBM Cognos services in the Explorer Window, selecting IBM Cognos beneath it, right-clicking and choose Start from the pop-up menu. The IBM Cognos Configuration tasks dialog box opens reporting the status of the start. For example, it may list two tasks: "Checking the upgrade status" and "Starting the service 'service name.'" If the service starts successfully, the ASE Content Store and Cognos BI Server environment are ready for use. If not, troubleshoot any problems based on the errors.

Back to Contents | Back to Setup and Configuration


Verify Cognos Server environment

With all the Cognos BI Server components installed and configured, you can now verify the environment is working properly. IBM provides instructions for doing this in the section "Test the Installation and Configuration" in the Installation and Configuration 10.1.1 guide. Note that this section is provided in both Chapter 6, "Installing and Configuring IBM Cognos BI Components on One Computer," and Chapter 7, "Installing and Configuring IBM Cognos BI Components on Different Computers."

To summarize, the tests IBM provides are to verify that the Content Manager is running, and the dispatcher and gateway are available. You do this using a Web browser as follows:

  • Content Manager URI: http://host_name:port/p2pd/servlet, note that the default port is 9300
    Content Mgr Verification
  • Internal Dispatcher URI: http://host_name:port/p2pd/servlet/dispatch, note that the default port is 9300.
  • Gateway URI: http://host_name:port/ibmcognos/cgi-bin/cognos_module, note that the port is 80. The section Verify the Web server earlier in this report showed what this URI displays when the Cognos Service is not running.
    Verify Cognos Server Gateway

This completes the discussion of the tasks you need to perform on the Cognos BI Server machines. Proceed to the next section for information about the tasks you need to perform on the Cognos BI Modeling machine.

Back to Contents | Back to Setup and Configuration


Tasks on the Cognos BI Modeling Machine

The Cognos BI Modeling software includes the Framework Manager application, which is the application for creating packages of data for use in the Cognos reports. Framework Manager version 10.1.1 is a 32-bit application that is only available on Microsoft Windows. You use the Framework Manager application to create the data packages and then publish them to the Cognos BI Server. The Cognos report development Web applications, such as Report Studio, allow you to build reports based on the data packages. The tasks you need to perform on the Cognos BI Modeling machine are as follows:

Install the Sybase IQ Database Client on the Cognos BI Modeling software machine

Because the Framework Manager application interfaces directly with Sybase IQ, you need to install the 32-bit Sybase IQ ODBC driver on the same machine. The ODBC driver is included with the Sybase IQ Database Client software. You need to install and use the 32-bit driver because Framework Manager is a 32-bit application. In the test environment, a 64-bit Windows machine was used. For version 15.4, the Windows database client includes both the 32-bit and 64-bit ODBC drivers. Be sure you use the correct ODBC Administrator to configure the 32-bit ODBC data sources. See the Sybase White Paper: Basic ODBC Connectivity for Sybase IQ 15.4 and SA 12.0.1.

Install the Cognos BI Modeling software and configure it to work with a Cognos BI Server

This section addresses installing Framework Manager, which is the Cognos modeling software you use to create packages of meta data for use in reports. It is included in the Cognos BI Modeling software. The Cognos Modeling software are 32-bit Microsoft Windows applications. Also included is the Cognos Configuration tool, which allows you to configure the Cognos BI Server with which Framework Manager will interact.

Install the Cognos BI Modeling software

Information about installing the Cognos Modeling software follows:

  1. Read "Installing and Configuring IBM Cognos Framework Manager" in Chapter 8 of the Cognos 10.1.1 BI Installation and Configuration Guide.
  2. Install the Cognos Modeling software on a Microsoft Windows machine as documented in the IBM guide.
  3. Proceed through the installation. When you reach the Component Selection panel, select the products you want to install. In the test environment, Framework Manager was installed but not Map Manager because Map Manager was not tested. Cognos Configuration is not listed but was included when just Framework Manager was selected.

Framework Manager install Component Selection

  1. Continue through the installation and click Finish when the installation is complete.
  2. Verify the success of the install by opening Framework Manager from the Start menu by choosing Start | IBM Cognos 10 | IBM Cognos Framework Manager. The Framework Manager application opens. Close it for now.


Configure the Cognos Modeling software to work with a Cognos BI Server

After you install the Cognos Modeling software, you need to configure it to connect to a Cognos BI Server. To do this you use the Cognos Configuration tool in much the same way you use it when you configure the Cognos BI Server as described in Configure the Cognos BI Server.

  1. Launch Cognos Configuration from the Start menu by choosing Start | IBM Cognos 10 | IBM Cognos Configuration. The Cognos Configuration application opens.

Framework Manager configure BI Server

  1. Select Environment in the Explorer pane on the left and then specify the following URIs for the Cognos BI Server you want Framework Manager to work with:

Gateway URI: http://<CognosBIServer_host>:80/ibmcognos/cgi-bin/cognos_module (or mod_cognos.dll for Windows or cognosisapi.dll for Windows).

Dispatcher URI: http://<CognosBIServer_host>:80/ibmcognos/p2pd/servlet/dispatch

  1. Choose File | Save to save the changes and exit the application. Now when you re-open Framework Manager it should connect to the Cognos BI Server you specified. If it does not, troubleshoot the problem based on any errors you see.

That completes the setup and configuration section of the report.

Back to Contents | Back to Setup and Configuration


Feature Example

This section provides an example of how to use the Cognos software with Sybase IQ. It contains the following sections:

Use Framework Manager to create a project and data source with Sybase IQ data

Use the Cognos Framework Manager modeling tool to create a project, data source and package of Sybase IQ meta data. "Publish" the package of IQ meta data to make it available for use in the Cognos Connection portal. Once they are available in the portal, packages of IQ meta data are utilized during the report creation process.

  1. Open Framework Manager by choosing Start | All Programs | IBM Cognos 10 | IBM Cognos Framework Manager. The Framework Manager application opens.

Framework Manager create project

  1. Create a new project by clicking Create a new project....The New Project dialog box opens with the default name "NewProject1" and location

  1. Provide a name and the location for the project and click OK. A status dialog window displays and then the Select Language dialog box opens. If you installed the Cognos Modeling software with English as the default language, it will be selected by default.
  2. Click OK to select the default English language. The Metadata Wizard - Select Metadata Source panel opens.

  1. Select Data Sources and click Next. Note that data sources here are data sources defined within Cognos and not necessarily ODBC data sources you defined with an ODBC Administrator. The Select Data Source panel opens. The first time you create a project this list will be blank. Otherwise, any other data sources defined within Cognos will be listed here.

  1. Click the New button to create a new data source. The Welcome - New Data Source wizard panel opens.
  2. Click Next. The Specify name and description panel of the New data source wizard opens. Enter and name and description for the new data source.

  1. Click Next. The Specify the connection panel opens with the connection Type defaulted to IBM Cognos Finance.

  1. From the Type field drop-down list, choose ODBC. Accept the default "Use the default object gateway" as the isolation level and click Next. The Specify ODBC connection string panel opens.

  1. In the ODBC data source field, enter the name of the system ODBC data source you created for Sybase IQ. This value is the ODBC data source created using the 32-bit ODBC data source administrator installed with Sybase IQ as described in the section Install the Sybase IQ Database Client on the Cognos BI Modeling software machine. For additional information regarding the configuration of an ODBC data source see the Sybase White Paper: Basic ODBC Connectivity for Sybase IQ 15.4 and SA 12.0.1. Leave the default values for the other field in the top half of this pane.
  2. In the Signons area of the screen, specify the user and password authentication by accepting the default selected Signons, then selecting Password, and providing the user password for the target Sybase database.

  1. Test the connection by clicking Test the connection... at the bottom of the pane. The Test the connection panel opens showing the connection string that Framework Manager builds based on the information you entered on the previous pane.

  1. Click Test to test the connection. Framework Manager attempts to connect to the target Sybase database using the connection string. If the connection succeeds, the reported status will be "Succeeded". If the connectivity test fails, the reported status will be "Failed" and you will need to troubleshoot the connection.
  2. Click Close twice to dismiss the test results and test connection panes. The New data source wizard remains with only the Finish button available.
  3. Click Finish to complete the Data source wizard.
  4. Click Close to close the wizard. The Metadata Wizard - Select Data Source pane returns. It now shows the data source you created in the prior steps.
  5. Select the data source you just created and click Next. The Metadata Wizard - Select Objects panel opens, showing the data source object folders listed by owner. For example, you will see the objects owned by DBA, which was the user specified for the data source. You will also see GROUPO. This is the owner of the Sybase Demo database objects.
  6. Expand the GROUPO item to show the folders underneath and then expand the Tables folder to show the list of Sybase Demo tables.

  1. Select Customers table and click Next. The Generate Relationships panel opens.

  1. Because this example is only showing the import of one table, accept the default values and click Import. If you import tables with relationships, you should pay more attention to the options on this panel. For example, for queries in which you want to perform outer joins, you may want to change the "Indicate how you want to generate relationships..." option to "Convert to inner join (1..n)" to "Create outer join (0..n)." The Metadata Wizard imports the tables and reports information about the import process on the Finish panel. For the one Customers table imported in this example, the Finish panel reported:
"Completed the import process.
     Created the following objects:
     Type: Query Subject, Count 1"
  1. Click Finish to exit the Metadata Wizard.

  1. After project creation is complete, you can apply additional settings to the model, including more options for joins, by choosing Edit Governors... To edit the Governors settings, navigate to the Tools pane. From within the Tool pane, select the Summary tab, and click the Edit Governors option.. The Governors window is shown below. Otherwise, project creation is complete.

  1. Choose File | Save to save the project.

Proceed to the next example for information about creating a data package.

Use Framework Manager to create and publish a package of Sybase IQ data to the Cognos Portal

This example highlights the publishing of a "package" of IQ meta data to the Cognos Connection portal. The published package will then be used to build reports.

  1. Publish a package to Cognos Connection by launching Framework Manager, if it is not already open, and then opening the project that contains the meta data for which you want to create and publish the package.
  2. In the Project Viewer, select Packages, right-click and choose Create | Package. The Create Package wizard opens displaying the Create Package - Provide Name panel.
  3. Enter a package name, an optional description, and click Next.The Create Package - Define object panel opens displaying the available objects. The Customers table is shown selected below.

  1. Select the objects you wish to include in the package and click Next. The Select Function Lists panel opens with all functions selected by default.
  2. Leave the default function selections and click Finish to complete the Create Package wizard. A message appears indicating the success or failure of the package creation. When it succeeds, a dialog box opens with the message: "You have successfully created your package. Would you like to open the Publish Package wizard?"
  3. Click the Yes button on the the dialog box. The Publish Wizard - Select Location Type panel opens.

  1. Accept the default option to publish the package to the Public Folders in the Cognos portal, accept the rest of the default values on this panel, and click Next. The Add Security panel opens with the "No security defined".

  1. Accept the default for now and click Next.to continue.The Options panel opens.

  1. Accept the default values and click Publish to publish the package. The wizard publishes the package and reports the status to the Publish Wizard - Finish panel. A successfully published package results in a status similar to the following: "You have successfully published the package to the following location...." The panel provides the option for exiting the wizard (default) or publishing another package.

  1. Click Finish to close panel and exit the wizard.

The package of IQ meta data is now available in the Cognos Connection portal. Continue to the next example for information about using the package of Sybase data in a Cognos report.

Use Cognos Connection Portal and Report Studio to create and run a report

This section describes how to create and publish a Cognos report using the Report Studio tool. The published Sybase IQ meta data package, which is now available via the Cognos Connection portal, will be used for creating and querying a report.

  1. Open a Web browser that has been configured with the Cognos required settings and enter the URL for the Cognos Connection portal (for example, http://host_name/cognos8/cgi-bin/cognos.cgi). The required browser settings are listed earlier in this report, in the section Install the Cognos pre-requisites. The Cognos Connection home Public Folders page should open.

  1. Launch the Report Studio application by choosing Launch in the upper-right corner and then Report Studio from the drop-down list. The Select a package window dialog opens. If you have been creating reports with other packages, they will be listed in the Recently used packages window.

  1. Select the package you published to the portal. For example, SybaseIQ_iqdemo_js is the package created from the Sybase Demo database. The Report Studio application opens prompting you to create a new report or open an existing one.
  2. Select the option to create a new report. The New dialog box opens prompting you to select a template.

  1. Select the List template and click OK. A status dialog box reports the status of the package import. Report Studio opens with the package objects displayed in the Content pane on the left..The List report template is displayed in the Page Explorer view on the right.

Report Studio provides the report layout and design features in the Page Explorer view. For example, the order in which the columns display or results are grouped in a report can be applied in the Page Explorer view. For simple reports, you can drag and drop tables or columns from the Source pane directly in the Page Explorer. However, the query functionality available in this view is limited. To build reports based on more complex queries, Report Studio provides the Query Explorer view. Query Explorer allows you to build complex, underlying queries, which may involve expressions or use columns you do not want to display in the report results.

  1. Display the Query Explorer view by locating the Page Explorer tab in the middle of the screen and selecting the icon below it, which is the Query icon. Drag the Query icon out to the right and the middle bar will widen to display the Query Explorer.

  1. Select Query1. The Query Explorer view will open on the right divided into four panes: Data Items, Detail Filters, Summary Filters and Slicer. The Source pane remains open on the left with an additional tab that looks like a tool box. It provides other features and functions available for use in building queries.
  2. Select columns from a table in the Source pane and drag them to the Data Items pane. For example, display the SalesOrder table columns and drag (or double-click) the ID, Region and OrderDate. They will be added in the Data Items pane. Note that the Properties pane becomes available for the Query in the lower-left pane. You can see the default manipulations on the columns in the Properties, such as aggregates and filters. You can also set them yourself here. For example, you can also force the query processing to occur in the database as described in Test Note 1.5.

  1. Experiment with the Query Explorer query capabilities. Report Studio may automatically apply functions or aggregates to a column based on the type of information it contains, such as the number of sales. This type of information is referred to as a fact. See the Report Studio on-line help for more information. Check columns for any automatically applied aggregates. For example, select to filter based on the date of a sale. The Properties change for the selected column. Notice the Expression property contains the column name qualified with the package and table names.
  2. Click the Expression property. An ellipses button will appear in the right-side of the field and then the Expression Builder utility will open showing the column name in the Expression Definition window. You can enter expressions in this window by simply typing them or by using those available in the Expression Builder. Expression Builder provides five tabs of options below the Available Components window with operators, aggregates and functions. Any data source vendor functions you selected during package import will be available under Vendor functions on the f(x) tab. Refer to the Cognos documentation or help for more information about the tabs and options. The package options tab is displayed by default.
  3. To use the provided functions, you place the cursor in the Expression Definition window by clicking after the column name, display the functions tab, and select what you want. For example, you can display the functions tab (f(x)) for a list of operators, aggregates and functions. Select Operators, scroll to the equals sign (=), and double-click it to add it to the expression. It should appear where you placed the cursor. Then display the package tab again. Scroll to the SalesOrder table, select the OrderDate column, and right-click. The pop-up menu contains the option Select a Value. Select this option. A dialog box containing list of values from the selected column in Sybase IQ opens. Select one of the regions and click OK. The value should be added to the right of the equal sign in the Expression Definition window.

  1. Test the validity of the expression by clicking the green check mark button in the Expression Builder toolbar. The Information window below the Expression Definition window will report the status of the validity check. If errors occur, troubleshoot the problem based on the errors. Otherwise, the message should report that the expression is valid.
  2. When you finish building the expression and it passes the validity test, click OK to close Expression Builder. The column Expression property will contain the expression you built with the column.
  3. Display the Page Explorer view again by selecting the top icon in the middle bar, dragging it to the right, and selecting Page1.
  4. Under the Source pane, display the middle Query tab. The Query you just built should be available.
  5. Drag and drop first the region column, and then the ID and Order Date columns to the Page Explorer view. You can group results from within Page Explorer by selecting the column on which you want to group and choosing Structure | Group/Ungroup from the Report Studio toolbar. For this example, we group by region. An icon appears next to the column to which you apply the grouping.

  1. Double-click "Title" to edit the report title text. This allows you to enter a name for the report. A text dialog displays for you to enter text. Provide a name for the report.
  2. Save the reports by choosing File | Save.
  3. Run the report. Note that you can run the report from within Report Studio or the from Cognos Connection portal. This example describes running reports from within Report Studio.
  4. Click the run (blue, right-facing arrow) button in the toolbar or select Run | Run Report - html (or one of the other format options available). A status dialog box reports the status of the run. When you run a report from within Report Studio, the Cognos viewer opens in a separate browser window. The following screen shows the first page of the sample report. Note that the run date, report navigation options (Top, Page Up, Page Down and Bottom) and page numbers are included by default.

  1. Close the report browser window by clicking the red X in the upper-right corner. The report layout remains open in Report Studio. Note that you can see the SQL generated for the report by choosing Tools | Show Generated SQL/MDX. The Generated SQL dialog box opens with the Native SQL displayed.

You can also see the Cognos SQL by choosing IBM Cognos SQL from the drop-down. Following is the Cognos SQL for the Native SQL query shown above:

select
 SalesOrders.Region  as  Region,
SalesOrders.ID  as  ID,
SalesOrders.OrderDate  as  OrderDate
from
 SybaseDemoDB..GROUPO.SalesOrders SalesOrders
where
 (SalesOrders.OrderDate > TIMESTAMP '2000-01-09 00:00:00.000')
order by
 Region as
  1. When you are finished creating reports you can close Report Studio and exit the browser. This completes the feature examples for Cognos.

Back to Feature Example | Back to Contents


Test Notes

This section contains information gained during testing, which may be helpful. The notes are divided into the following sections:
1. Cognos software notes
Test Note 1.1: Cognos data sources and ODBC data sources
Test Note 1.2: Specifying to create JOINs as INNER or OUTER in Framework Manager
Test Note 1.3 Framework Manager Publish Package Wizard displays warning when publishing iqdemo database
Test Note 1.4: Stored procedures may need preparation before being used in Framework Manager packages
Test Note 1.5: Force query processing to occur in Sybase IQ database
Test Note 1.6: Applying the COUNT aggregate in Report Studio Page Explorer may result in error
Test Note 1.7: Report behavior when Sybase IQ is shut down during report execution
Test Note 1.8: Including character columns/data items in reports to which operators are applied in expressions
2. Data type notes


1. Cognos software notes

Test Note 1.1: Cognos data sources and ODBC data sources

Cognos uses the term "data sources" to refer to sources of data defined within the Cognos environment using either Framework Manager or the Cognos Connection portal. Note that these Cognos data sources are not the same as ODBC data source names (DSNs) defined with a database driver in an ODBC Data Source Administrator. You can, however, specify an ODBC DSN as the basis for a Cognos data source, which is how it is configured for Sybase IQ. This configuration is described earlier in this report in the section Use Framework Manager to Create a Project and Data Source with Sybase IQ data. For information about creating and configuring an ODBC DSN using the Sybase IQ ODBC driver, see the section Install the Sybase IQ Database Client and Configure an ODBC Data Source and the Sybase White Paper: Basic ODBC Connectivity for Sybase IQ 15.4 and SQL Anywhere 12.0.1, which is referenced in that section.

Test Note 1.2: Specifying to create JOINs as INNER or OUTER in Framework Manager

When you use the Framework Manager Metadata Wizard to import Sybase IQ meta data for a project and model, there is an option on the Generate relationships panel for you to specify how you want to create relationships between the query subjects (see below). The default is to create INNER JOINs. But be aware of how this selection influences query results when you build queries and reports using packages generated from this model in Report Studio. For example, when the default of creating INNER JOINs was selected, attempts to specify OUTER JOINs in the Report Studio report Query failed to produce expected results. However, changing the Generate Relationships import setting to the non-default to create OUTER JOINs instead produced the expected results because the OUTER JOIN relationship was defined in the meta data.

Test Note 1.3: Framework Manager Publish Package Wizard displays warning when publishing iqdemo database

When a Framework Manager project was created from the Sybase iqdemo database tables and then a package was created and published from that model during testing, the Publish Package wizard reported the nine errors and 1 warning on the Finish panel. If you choose to view the errors and warnings you will see several objects, including the stored procedures, have a status of "invalid." See Test Note 1.4 for how to handle stored procedures. You will also see a loop error on the Departments and Employees table

BMT-MD-2010 This package will publish 'Query Subject' '

[SybaseIQ_js].[Departments]', which has the following warnings.

BMT-MD-2015 This 'Query Subject' and 'Query Subject' '[SybaseIQ_js].[Employees]'

have the following multiple relationships, or relationship shortcuts, defined between them:

'Relationship' '[SybaseIQ_js].[Departments <--> Employees]' and 'Relationship'

'[SybaseIQ_js].[Employees <--> Departments]'. Based on the relationship cardinalities,

this creates a loop.

No effects from this loop were noticed during testing. However, the Framework Manager on-line help describes the problems with such loops in the "Cardinality" section of the topic "Understanding IBM Cognos Modeling Concepts." You can modify the relationships in the model and then create and publish the package again to resolve such issues.

Test Note 1.4: Stored procedures may need preparation before being used in Framework Manager packages

Although stored procedures in Sybase IQ may be imported into Framework Manager projects, they may not necessarily be ready for use in packages. In the Project View, if an imported stored procedure icon looks broken, it may need to be tested or require parameter/argument values. Note that if you attempt to publish a package with a broken stored procedure, the Create Package wizard validation test will report "invalid" errors similar to the following:

"BMT-MD-2007 This package will publish 'Query Subject' '[SybaseIQ_js].[ShowCustomers]',
which contains the following errors:
BMT-MD-2007 This objects has a status of 'Invalid'. Unable to test the object."

To prepare stored procedures for use in packages, perform the following steps:

  1. Double-click the stored procedure to display the Query Subject Definition dialog box. If the stored procedure requires parameters, a list of those parameters are provided on the Definition tab. Specify parameter values on the Definition tab.
  2. Then you can test the stored procedure by displaying the Test tab using the Test Sample option in the lower-right corner. Once a stored procedure is successfully tested, its icon should no longer be broken in the Project View and it should be ready for use in a package and publishing.

Test Note 1.5: Force query processing to occur in Sybase IQ database

Cognos Report Studio and Query Studio generate SQL statements based on the Query and data containers in a report. However, the generated SQL may not be valid for where it will be processed. Where the SQL statement is processed--in the database or in in the Cognos environment--is configurable in a couple of places in the Cognos environment. One place is in the data source in the Framework Manager model, where you can specify SQL generation as "Database Only" or "Limited Local." using the following steps:

  1. In the Project Viewer, select the data source you want to change,
  2. From the Properties pane, in the Query Processing list box, click either Limited Local or Database Only.

The other place you can set it, which overrides the model setting, is in the report Query Properties (see below) in Report Studio. To determine if generated SQL is valid for where the processing will occur, choose Tools | Validate Report from the toolbar within Report Studio. If the requirements of the layout and query cannot be fully expressed to Sybase IQ in a SQL statement and the Query processing property is set to "Database Only," an error will be thrown and the property should be changed to "Limited local." See Test Note 1.6 about an error that occurs with this setting in Page Explorer.

Test Note 1.6: Applying the COUNT aggregate in Report Studio Page Explorer may result in error

Attempts to apply the COUNT aggregate to a column in the Page Explorer view of a report built with the List template cannot be processed with the Processing property set to "Database Only." Attempts to do so during testing, resulted in the following error:

"RQP-Df-0177 an error occurred while performing operation 'sqlPrepareWithOptions' status='-120'. 
Details: UDA-SQL-0458 PREPARE failed because the query requires local processing of the data.
The option to allow local processing has not been enabled.
UDA-SQL-0475 The OLAP function 'count'contains a clause (AT or PREFILTER) that is not supported
in the database. The operation requires local processing of the data..."

Test Note 1.7: Report behavior when Sybase IQ is shut down during report execution

When Sybase IQ is shut down while a Cognos report is running in the Microsoft Internet Explorer browser tested, Cognos opens a new browser window with a user and password prompt. If you supply the user and password and click OK, Cognos returns the message:

"Attempts to connect to the data source failed. There may be a configuration issue with
 the database. The system administrator may need to test and reconfigure the connection."

If you leave this login window with the error open, restart Sybase IQ, supply the user and password again and click OK, the report will run and return results as expected.

Test Note 1.8: Including character columns/data items in reports to which operators are applied in expressions

In the test environment, character columns that are specified in an expression may not display expected values from the data source when included in the display of a report. For example, when an expression filtering to just show the "central" region was built in a Query Item of the Region column from the Sybase iqdemo SalesOrder table, the report containing it did not return "central" as the region as expected. Instead, it showed a number in the Region column when the report was run. However, when the same filtering criteria were specified in an expression in a filter object instead of the column Query Item expression, the results returned listing the region as "central" as expected.

2. Data Type Notes

A sampling of the following Sybase IQ 15.4 supported data types were tested. Large values (where maximum values are theoretical and restricted by hardware) and minimum values as well as null and not null values were tested.


Types

Sybase IQ 15.4 types

Type mappings in Cognos Framework Manager and handling notes

Exact Numeric Integer (int) Types

Tinyint, smallint, int, unsigned int, bigint, and unsigned bigint

tinyint and smallint mapped to "Int16," int and unsigned int mapped to "Int32," bigint and unsigned bigint mapped to "Int64"; all values displayed as expected.

Exact Numeric Decimal Types

Decimal, numeric, smallmoney and money

All four types mapped to "Decimal"; Columns declared with precision of 31 and scale of 0 (31,0) displayed 31 digits but only 21 significant digits, which is to say the last 9 digits were zeros. All other test values displayed as expected.

Approximate Numeric Float Types

Float, real and double

Float and real mapped to "Float32," Double mapped to "Float64"; minimal rounding occurred.

Character (char) Types

Char in lengths 1 and 254 and varchar in lengths 10 and 254

Char and varchar mapped to "Character Length 16"; all values displayed as expected.

Date/Time Types

Date, time, smalldatetime, datetime and timestamp

Date mapped to "Date," time mapped to "Time," smalldatetime, datetime and timestamp all mapped to "Date Time." All test values displayed as expected with one exception: the value '4567-12-31 23.59.59.999' rounded to year "4568."

Binary (bin) Types

Binary and varbinary

Binary mapped to "Binary" and varbinary mapped to "Binary16." Binary data is not supported by Cognos. Framework Manager is able to import and identify columns declared as binary types and create packages with tables and columns of binary type. However, attempts to add columns of these types to a Report Studio report failed with the following error: "...Unrecognized variant datatype, unable to determine a formatter type....".

Image Binary Large Object (BLOB) and Image Types

BLOB and image

The image column mapped to "Binary16." Attempts to add this column to a Report Studio report failed with the following error: "...Unrecognized variant datatype, unable to determine a formatter type...."

Character Large Object Types (CLOB) and Long Varchar Types

CLOB and long varchar in length 1 and 300

Both types mapped to "Text Blob"; Initially, the test table containing these types was created without any keys defined. Attempts to run a report in Report Studio against this table failed with the error: Re-creating the table with a primary key definition, creating a new project and package with this table and then running a report against it returned data as expected.

User-Defined Types

Test user-defined data types created from char, date, numeric and identity types

user type created based on char mapped to "Character Length 16," user type based on date mapped to "Date," and user type based on numeric mapped to "Decimal." All values displayed as expected.

Multi-byte Data

Multi-byte data tested in the form of Simplified Chinese data inserted into a varchar column in a UTF8 database - Note that the Microsoft Asian Language packs installed on the Cognos software host machine as well as the appropriate Sun Solaris language packs were installed on the Sybase IQ host Solaris workstation.

Not tested.

Back to top of Notes section | Back to Contents


Issues and Incompatibilities

No major issues were encountered during testing.

Back to Contents


Test Environment

This section provides details about the test environment.

Client Machine

Hardware or Software Description or Version

Operating Environment

Intel(r) Xeon(r) CPR E7320 @ 2.13 GHz, 64-bit OS
Memory

6 GB

Disk 40 GB
Operating System Windows 7 Professional, Service Pack 1
Web browser Windows Internet Explorer 9 (9.0.8112.162410)
Client product, for example Reporting software IBM Cognos Framework Manager version 10.1 including Report Studio, Query Studio. IBM Cognos Configuration version 10.1.
Connectivity software

32-bit Sybase IQ ODBC driver for Windows, version 12.0, installed from the Sybase IQ Database Client for Windows, version 15.4 64-bit version which includes both the 32-bit and 64-bit Sybase IQ ODBC driver.

Windows Cognos Server Machine

Hardware or Software Description or Version

Operating Environment

2-2.13 GHz Intel(R) Xeon(R) CPUs
Memory

4 GB

Disk 40 GB
Operating System Microsoft Windows Server 2008 R2 (64-bit) Enterprise Service Pack 1
Java Java(TM) SE Runtime Environment (build 1.6.0_24-b07), 64-bit
Cognos Content Store (repository) Sybase Adaptive Server Enterprise, 15.7 EBF 19496 SMP, 64-bit
Web server Microsoft Internet Information Services, version 7.5.7600.16385
Business Intelligence software IBM Cognos Business Intelligence Server, 10.1.9.0-0
application server Apache Tomcat. 6.0.18.10.0-0, 64-bit, installed by Cognos
Reporting database client

Sybase IQ Network Client 15.4 (64-bit), provides both 32-bit and 64-bit client files, including the 32-bit Sybase IQ ODBC driver, version 12.00.01.6567

Linux Cognos Server Machine

Hardware or Software Description or Version

Operating Environment

2-2.13 GHz Intel(R) Xeon(R) CPUs
Memory

4 GB

Disk 2 hard disks, 10 GB and 30 GB
Operating System Novell SUSE Linux Enterprise 11 (64-bit)
Java IBM SDK for Linux, Java Technology Edition, Version 6
Cognos Content Store (repository) Sybase Adaptive Server Enterprise 15.7 EBF 19805 SMP ESD #1, 64-bit
Web server IBM HTTP Server, version 7.0 32-bit
Business Intelligence software IBM Cognos Business Intelligence Server, 10.1.2t9.0-0
application server Apache Tomcat. 6.0.18.10.0-0, 64-bit, installed by Cognos
Reporting database client Sybase IQ Network Client 15.4 (32-bit), EBF 20052, includes the 32-bit Sybase IQ ODBC driver, version 12.

Sybase IQ Server Machine

Hardware or Software Description or Version

Operating Environment

64-bit Sun Ultra SPARC IV, 4-1.8 GHz processors
Memory

32 GB

Disk 1 TB
Operating System 64-bit Sun Solaris 10 (SunOS 5.10)
Reporting database

64-bit Sybase IQ 15.4.0.6567

Back to Contents


Other Sources of Information

Sybase IQ Resource Page

Sybase provides a site dedicated to the Sybase IQ product. On this page, there are links to Data Sheets, White Papers, Webcasts / Podcasts, News, Case Studies (customer success stories), Events, Analyst Reports, the Sybase Analytics Blog, Videos, Awards, Newsletters, and Migration Resources. There is also a link on this site to a free download trial of Sybase IQ.

The Sybase IQ Resource Page is located at: http://www.sybase.com/IQ

Sybase Partner Program

For more information about the Sybase Partner Program, please visit the Sybase Partner Program website.
http://www.sybase.com/partner

Back to Contents


Vendor Contact Information

Vendor Name: International Business Machines (IBM) Corporation
Website:www.ibm.com

Back to Contents



 

Related Links

DOCUMENT ATTRIBUTES
Last Revised: Aug 08, 2012
Product: Sybase IQ
Technical Topics: Middleware, Data Mining, Data Analysis, Data Modeling
  
Business or Technical: Technical
Content Id: 1098936
Infotype: Partner Certification Report
 
 
 

 
© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics