Share this article:

Vertica QuickStart for IBM Cognos Business Intelligence

Click here to view this document in PDF format.

About the Vertica QuickStart BI Examples

The Vertica QuickStarts are sample applications that show how complementary technologies can work together to deliver outstanding benefits to end users. Each QuickStart uses a different BI client tool with the Vertica Analytic Database. Each BI client is a product of a Vertica technology partner.

The QuickStarts are available for download free of charge on the Big Data Marketplace in the QuickStart Examples category

About this Document

This document explains how to deploy and use the Vertica QuickStart for IBM Cognos Business Intelligence. The document includes the setup information that you need to get up and running, and it provides an overview of the Cognos reports and the Vertica data source.

About the Vertica QuickStart for IBM Cognos

The Vertica QuickStart for IBM Cognos is a set of sample reports created using IBM Cognos Business Intelligence and powered by the Vertica Analytic Database. The dashboards present sample retail data for analysis. The QuickStart shows how retail companies could use Vertica and IBM Cognos to quickly explore, visualize, and gain insight into their data stored in Vertica.

For a quick introduction to the Vertica QuickStart for Cognos, see this short video:

Requirements

The Vertica QuickStart for Cognos is available for version 10.2.1 and version 10.2.2 of Cognos Business Intelligence. The only difference between the two installation packages is the map names that changed in 10.2.2. Because of the separate installation packages, 10.2.2 users do not need to rename several of the maps.

The QuickStart was developed using Cognos Business Intelligence Server on Linux and Cognos Framework Manager on Windows with Vertica JDBC Client 7.1.x and Vertica Server 7.1. The QuickStart leverages Cognos Dynamic Query mode for JDBC.

Note Cognos 10.2.1 requires fix pack 3 or greater to ensure compatibility with the JDBC driver used by the QuickStart.

The QuickStart requires the following:

  • Vertica Server 7.1.x with a default installation of the VMart example database
  • Vertica Client 7.1.x
  • IBM Cognos Business Intelligence Server 10.2.1 or 10.2.2

If you want to modify the QuickStart example application, you must also install Cognos Framework Manager 10.2.1 or 10.2.2.

To determine if your web browser is supported by your version of Cognos Business Intelligence, see IBM Cognos Business Intelligence Supported Software Environments.

Install the Software

To install the software that is required for running the QuickStart, follow these steps:

Install the Vertica Database Server

The Vertica database server runs on Linux platforms. If you do not have Vertica, you can download the RPMs or a virtual machine free of charge from the Vertica Community Portal at https://my.vertica.com/.

To download and install Vertica Community Edition:

  1. On my.vertica.com, under Vertica Community Edition, click Signup Now to register for a Community Edition Account.
  2. Provide your information and click Signup.
  3. Follow the on-screen instructions to download and install the Vertica Community Edition.

Install the VMart Example Database

The Vertica QuickStart for Cognos assumes a default installation of the Vertica VMart example database. Follow the tutorial in the Vertica Getting Started Guide to install VMart.

In the tutorial:

The VMart example database includes three schemas: Public, Online Sales, and Store. The schemas are interrelated and share many dimensions. For details, see VMart Example Database Schema, Tables, and Scripts.

Install IBM Cognos

IBM Cognos Business Intelligence is a web-based solution for building applications that perform reporting and analysis. The components of Cognos Business Intelligence are:

  • Web Server: Allows web-based reporting
  • BI Server: Includes Content Manager, Application Tier components, and gateway
  • Framework Manager: Includes additional component for creating, modeling, and managing metadata for reporting and analysis

If you do not already have IBM Cognos Business Intelligence, you can install it as follows:

  1. Go to the Cognos Business Intelligence page:
  2. Select the downloads
  3. Download and install IBM Cognos Business Intelligence 10.2.1 or 10.2.2.

Note Install the components described above. Framework Manager is required only if you want to modify the Vertica QuickStart example application.

For Cognos Business Intelligence documentation, see the IBM Knowledge Center.

Install the JDBC Client Driver

IBM Cognos Business Intelligence Server uses the Vertica JDBC driver to connect to your Vertica database. The JDBC driver is included in the Vertica client package. To download and install the Vertica client package:

  1. Go to the Vertica Client Drivers page.
  2. Download the version of the Vertica client package that is compatible with the architecture of your operating system (32- or 64-bit) and the version of your Vertica server.

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.

For Cognos BI Server to work with Vertica using JDBC, the .jar file that you downloaded with the client package must reside in the IBM Cognos directory for external Java libraries. Follow these steps to place the .jar file in the appropriate location:

  1. Locate the Vertica .jar file from the Vertica driver location. For example:

 For a 64-bit Vertica driver package installed on a Windows machine, the Vertica driver location is:

\Program Files\Vertica Systems\JDBC\vertica-jdbc-XXX-0.jar

On a non-Windows machine, the directory is:

\opt\Vertica\JDBC\vertica-jdbc-XXX-0.jar.

The XXX represents the version of your Vertica database.

  1. Place the .jar file in the directory that contains the external Java libraries for Cognos. The default directory is:
     <Cognos_Install_Directory>\webapps\p2pd\WEB-INF\lib

Note If you upgrade your Vertica client, you must repeat these steps to replace the .jar file.

Deploy the QuickStart

The QuickStart example application is available as an export file for you to import into IBM Cognos. Separate export files are available for Cognos 10.2.1 and 10.2.2. To deploy the QuickStart, follow these steps.

Download the QuickStart

  1. Go to the Big Data Marketplace and log in with your Marketplace credentials.
  2. Select QuickStart Examples.
  3. Select Vertica QuickStart for Cognos.
  4. Click Download.

Import the QuickStart Package

Before you can run the QuickStart reports, you must deploy or migrate the package that you downloaded from the Big Data Marketplace into IBM Cognos. The package includes the QuickStart content and connection information. Follow these steps:

  1. Navigate to the folder where you downloaded the QuickStart compressed file:

    For Cognos 10.2.2, use VMartQS1022.zip.

    For Cognos 10.2.1, use VMartQS1021.zip.

  1. Copy the appropriate compressed file to the Cognos deployment directory:
      <Cognos_Installation_Directory>\deployment
  1. In your web browser, open the Cognos portal. For example:
      http://<my_cognos_server>/Cognos_URL/
  1. Click Launch.
  2. Select IBM Cognos Administration.

Note The IBM Cognos Administration option will only be available if you have the proper security access defined in IBM Cognos.

  1. On the IBM Cognos Administration page, select the Configuration tab, and click Content Administration.

      image002.png

  1. Click the New Import icon to start the New Import
  2. Select the radio button for the VMart QuickStart that corresponds to your version of Cognos: VMartQS1022 or VMartQS1021. Click Next.

image004.png9.  On the Specify a name and description page, verify the name, description, and screen tip and click Next.

image005.png

10. On the Select the public folders, directory and library content page, select the top check box, VMart Analysis, and click Next.

11. On the Select the directory content page, select Include data sources and connections and Keep existing entries and click Next.

 image006.png

12. On the Specify the general options page, select these options and click Next:

     Under Entry ownership, select The owner from the source.

     Under Apply to, select New and existing entries.

 image006.png

13. On the Review the summary page, review the information and click Next.

14.On the Select an action page, select Save and run once and click Finish.

15. On the Run with options page, select Now and click Run.

16. Select View the details of this import after closing this dialog, then click OK.

17. Click the Refresh icon a few times until you can verify that the import operation was successful.

18. Review the import results and verify that all 194 objects were imported successfully.

 image008.png19. When all the reports are imported click Close.

20. Click the Home icon and verify that the QuickStart reports are available in Public Folders.

Connect to the VMart Data Source

The QuickStart example application uses JDBC to connect to your VMart database. Follow these steps to create the connection:

  1. Open the IBM Cognos BI portal. For example:
     http://<servername>:<portnumber>/ibmcognos/cgi-bin/cognos.cgi
  1. Click Launch.
  2. Click IBM Cognos Administration.
  3. On the IBM Cognos Administration page, go to the Configuration tab and Select Data source connections.
  4. Select VMart from the list of data sources and click More.
  5. On the Perform an action page, click View connections.

image009.png7. On the View Connection page, click more.

8. On the Perform an action page, click Set Properties and select the Connection tab.

9. Modify the JDBC URL to connect to your Vertica database. The format of the connection string is as follows:

     jdbc:vertica://<my_server>:<my_port>/<my_database>

10. Select Signon and specify a user ID and password for connecting to the VMart database. The VMart user must have the same security access required for all QuickStart users.

11. Test the connection and click OK.

Open the QuickStart in IBM Cognos Business Intelligence

To run the reports, follow these steps:

  1. Click the Home icon.
  2. In Public Folders, select the VMart Analysis package.image010.png

     The content is available in two modes: as a set of dashboard, or as a workspace.

     IBM Cognos workspaces are web-based environments that allow you to create content and dashboards.

  1. To run the report, click the Start button next to VMart Sample Dashboards.
  2. On the Run with options page, under Format, select HTML to display the VMart sample dashboard.

 image011.png

  1. When prompted, enter the ID and password for the VMart database.

About the QuickStart Dashboards

The QuickStart dashboards present sample business and operational data that a large retail chain might track over time. The chain operates brick-and-mortar stores and an online marketplace. It sells a wide variety of products that it purchases from different vendors.

Note The data in your dashboards will not match the data in the screen shots in this document. This is because the VMart data generator generates data randomly.

Store Sales Dashboard

The Store Sales Dashboard is one of the reports included in the QuickStart. It displays sales-related metrics for the physical stores owned by this retail chain. Here the report displays sales data for 2007.

 image012.jpg

Call Center/Employee Dashboard

Another report included in the QuickStart is the Call Center/Employee Dashboard, which displays performance metrics relative to the performance of retail personnel in both stores and call centers. Here the report displays data for 2007.

 image013.jpg

Inventory Dashboard


Yet another report included in the QuickStart is the Inventory Dashboard, which displays information about products held in inventory. Here the report displays information about medical products in inventory in 2007.

image014.jpg

Find More Information

Topic Link
   

Vertica

https://my.vertica.com/

Vertica Documentation

http://my.vertica.com/docs/latest/HTML/index.htm

Big Data Marketplace

https://marketplace.saas.hpe.com/big-data 

IBM Cognos Business Intelligence

http://www-03.ibm.com/software/products/en/business-intelligence

 

Contact Us

The Vertica QuickStart for IBM Cognos is intended as an example of complementary technologies: IBM Cognos Business Intelligence with the Vertica Analytic Database. As such, it is freely available for demonstration and educational purposes to anyone wishing to explore these technologies. The QuickStart is not a product, and is not governed by any license or support agreement

We welcome your feedback. If you have questions, comments, or suggestions, please click Contact developer on the download page for the QuickStart on the Big Data Marketplace.

moreinfo.png

Share this article: