|Share this article:|
Vertica Integration with IBM Cognos Analytics: Connection Guide
About Vertica Connection Guides
Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. These documents provide guidance using one version of Vertica and one version of the third party vendor’s software. Other versions of the third-party product may work with Vertica. However, we may not have tested those other versions.
Vertica and Cognos Analytics: Versions Tested
IBM Cognos 11 Release 10 Version 220.127.116.11
Windows Server 2012 R2
Vertica JDBC 9.1.0-0
Vertica Database 9.1.0-0
Cognos Analytics Overview
IBM Cognos Analytics is a web-based solution for building applications that perform reporting and analysis. You can use Cognos Analytics on cloud or on prem.
For details about IBM Cognos Analytics, see the IBM Cognos Analytics documentation.
Install Cognos Analytics
You can download and install a free trial of Cognos Analytics for cloud deployment. Follow these steps:
- Navigate to the Cognos Analytics Support portal.
- Check the prerequisites and follow the instructions on the download page.
Install the Vertica JDBC Driver
IBM Cognos Analytics server uses the Vertica JDBC driver to connect to Vertica. To install the JDBC driver:
- Navigate to the Vertica Client Drivers page.
Download the JDBC driver that matches your operating platform and your version of Vertica.
Note Vertica drivers are forward compatible. You can use an older driver version to connect to a newer server version. For more information about client and server compatibility, see the Vertica documentation.
Place the JDBC .jar file in the Cognos driver directory:
Note If you upgrade your Vertica client, you must repeat these steps to replace the .jar file.
Connect Cognos Analytics to Vertica
Open the Cognos Analytics home page:
The home page displays as shown:
- Click Manage.
Click Data server connections.
Click the plus (+) sign to add a data server:
From the list of databases, select HP Vertica:
Provide the database connection name and JDBC URL, then click Test.
When the connection is successful, the Success button turns green:
- Click Save.
Select Data module and click New.
Click Data Servers and select VMart:
Right click the database schemas that you want to include in your data module and click Load metadata:
When the metadatad is successfully loaded, the status button turns green.
Drag and drop the tables you want to include in your data module:
Cognos does not support the following Vertica data types: Boolean, IntervalMonth, IntervalSeconds, TimeTz, TimestampTz, Binary, Varbinary, Longvarbinary, or UUID.
Values of these data types in Vertica are not loaded into the Cognos reporting environment.
- The -infinity and +infinity values of the Vertica Float data type are also not loaded.
Typical values of Time, Time0, Time6, Timestamp, DateTime, SmallDateTime and Timestamp6 are truncated. Milliseconds are rounded off to 8 decimal points. For example, [122.333444455555] is displayed as [122.33344446]
For More Information
- IBM Cognos Analytics documentation
- Cognos Analytics Support portal
- Vertica and Cognos: Tips and Techniques
- Vertica QuickStart for Cognos Analytics
- Vertica Community Edition
- Vertica User Community
- Vertica Documentation