Working with the Cognos BI Server Using the Greenplum Database -- Interoperability and Connectivity Configuration for Linux Users

Working with the Cognos BI Server Using the Greenplum Database -- Interoperability and Connectivity Configuration for Linux Users

• 1.
  White Paper Working withthe Cognos BI Server Using the Greenplum Database Interoperability and Connectivity Configuration for Linux Users Abstract This white paper explains how the Cognos BI Server running in the Linux environment can be configured and used with a Greenplum database. Included in this paper are detailed instructions for configuration and connectivity verification. March 2012
• 2.
  Copyright © 2012EMC Corporation. All Rights Reserved. EMC believes the information in this publication is accurate of its publication date. The information is subject to change without notice. The information in this publication is provided “as is”. EMC Corporation makes no representations or warranties of any kind with respect to the information in this publication, and specifically disclaims implied warranties of merchantability or fitness for a particular purpose. Use, copying, and distribution of any EMC software described in this publication requires an applicable software license. For the most up-to-date listing of EMC product names, see EMC Corporation Trademarks on EMC.com. VMware is a registered trademark of VMware, Inc. All other trademarks used herein are the property of their respective owners. Part Number H10589 Working with the Cognos BI Server in Linux with the Greenplum Database 2
• 3.
  Executive summary The correctfunctionality of the Greenplum database with the Cognos BI Server is dependent on the configuration of an ODBC (Open Database Connectivity) driver using the Greenplum Connectivity Pack. This white paper walks the reader through the process of driver selection and installation, the configuration and validation of an ODBC connection to Greenplum, the creation of a Cognos data connection, and validation of that connection using Cognos Framework Manager and Query Studio. This white paper is based on examples from Cognos 10.1.1 (release pack 1), Red Hat Linux 5.5 and Greenplum 4.1.1 and Greenplum Connectivity pack greenplum- connectivity-4.1.1.0-build-4-RHEL5-x86_64. Audience This white paper is intended for customers, as well as, EMC field and support personnel who will be using the Cognos BI Server in the Linux environment with the Greenplum database. This white paper does not replace the Cognos documentation set supplied by IBM nor the Greenplum documentation set supplied by EMC. It is expected that the reader has basic knowledge of the Cognos BI Server, ODBC driver configuration in the Linux environment, and the Greenplum database. Organization of this paper This paper covers the following topics:  Overview of the Cognos BI system and components  How the Cognos BI Server integrates with relational database management systems  The installation, configuration, and verification of an ODBC connection using the Greenplum Connectivity Pack  Validation of the connectivity between the Cognos BI Server and the Greenplum database using the Cognos components Framework Manager and Query Studio Overview of the Cognos BI system and components The IBM Cognos BI server is implemented in a multi-tier architecture. For descriptive purposes, this architecture can be thought of as three tiers. Please note that Cognos 10 is a 32 bit application, therefore 32 bit ODBC drivers must be used. Tier 3 is the query database or data source. In this white paper the query database can be Greenplum either implemented on a computing appliance (Greenplum Data Computing Appliance) or in software only mode. Working with the Cognos BI Server in Linux with the Greenplum Database 3
• 4.
  Tier 2 containsthe Web server where the IBM Cognos BI gateway, dispatcher and content manager are hosted. The content store is a relational database that contains data that IBM Cognos needs to operate, such as report specifications, published models, and the packages that contain them. Tier 1 contains user interfaces including the Framework Manager modeling tool that drives query generation for IBM Cognos and the Cognos Connection user portal that includes administrative tools and reporting tools such as Query Studio. TIER 1 TIER 2 TIER 3 Framework Manager ODBC GPDB ODBC Cognos Web server Connection IBM Cognos BI Gateway Greenplum DCA Content or Software Store Installation How the Cognos BI Server integrates with relational database management systems Because Cognos supports many databases and those databases offer various levels of functionality, the Cognos BI Server must take into account which database it is sending SQL commands to in order to get optimal use out of that database. Cognos supports variable levels of SQL functionality by shipping individualized initialization files for each supported query database. The initialization file renders the generic Cognos SQL into the dialect of a particular supported database. The Cognos BI server resolves which database initialization file to load by interrogating the Working with the Cognos BI Server in Linux with the Greenplum Database 4
• 5.
  SQL_DBMS_NAME variable returnedfrom the SQLGetInfo call to the ODBC driver. All of this occurs automatically for the Cognos user. The installation, configuration, and verification of an ODBC connection using the Greenplum Connectivity Pack This section walks the reader through the steps required to select, install, configure and verify an ODBC connection to Greenplum. Install Required and Recommended Software The correct Greenplum Connectivity Pack for a particular combination of Greenplum and Cognos releases can be determined by referring to the IBM support site. Searching for the string “cognos 10.1.1 supported environments” in a Web search site, should direct the reader to the IBM support site. In the ODBC section of the Cognos release software environments page, locate the Greenplum database to be installed. A Greenplum Connectivity Pack version will be indicated for each supported environment. For example, for Cognos 10.1.1, Greenplum 4.1.1 is supported via Connectivity Pack 4.1.1 for Linux (x86). Greenplum Connectivity Packs can be downloaded from EMC’s PowerLink web site or from the Greenplum Community site. Please refer to the GPConnectUnix PDF in the installation pack for detailed installation instructions. Briefly, the installation of the GP connectivity tools consists of these steps: 1. Download the appropriate greenplum-connectivity-4.1.1.0-build-4-RHEL5-x86_64.bin installer package for RedHat Linux 64-bit. 2. Unzip the installer: unzip greenplum-connectivity-4.1.1.0-build-4-RHEL5-x86_64.bin.zip 3. Run the installer: /bin/bash greenplum-connectivity-4.1.1.0-build-4-RHEL5-x86_64.bin. Accept the license agreement and supply an absolute path for the tool installation. 4. As a convenience, a greenplum_connectivity_path.sh file is provided in the client tools installation directory following installation to set the environment variables GPHOME_CLIENTS, PATH, and LD_LIBRARY_PATH. The examples in this white paper specified the ODBC driver manager as unixodbc-2.2.12 and the ODBC driver as psqlodbc-08.04.0200. The Cognos BI server requires a 32-bit ODBC driver. The word size of the ODBC driver downloaded can be confirmed using the Linux “file” command. For example, -bash-3.2$ file <4.1.1-gp-conn-install-dir>/drivers/odbc/psqlodbc- 08.04.0200/unixodbc-2.2.12/psqlodbcw.so psqlodbcw.so: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), not stripped Working with the Cognos BI Server in Linux with the Greenplum Database 5
• 6.
  In order toverify that all the shared objects required by the ODBC driver are properly identified in the LD_LIBRARY_PATH, it is recommended that the user run the “ldd” command on the driver shared object. The ldd command prints the shared libraries required by each program or shared library specified on the command line. For example, ldd <4.1.1-gp-conn-install-dir>/drivers/odbc/psqlodbc-08.04.0200/unixodbc- 2.2.12/psqlodbcw.so linux-gate.so.1 => (0xffffe000) libssl.so.0.9.8 => /my-gpconn-dir/lib/libssl.so.0.9.8 (0xf7f22000) libpq.so.5 => /my-gpconn-dir/lib/libpq.so.5 (0xf7eeb000) libpthread.so.0 => /lib/libpthread.so.0 (0xf7ec0000) libodbcinst.so.1 => /my-gpconn-dir/drivers/odbc/psqlodbc-08.04.0200/unixodbc- 2.2.12/libodbcinst.so.1 (0xf7eab000) libodbc.so.1 => /my-gpconn-dir/drivers/odbc/psqlodbc-08.04.0200/unixodbc- 2.2.12/libodbc.so.1 (0xf7e29000) libc.so.6 => /lib/libc.so.6 (0xf7ce3000) libcrypto.so.0.9.8 => /my-gpconn-dir/lib/libcrypto.so.0.9.8 (0xf7b9c000) libdl.so.2 => /lib/libdl.so.2 (0xf7b98000) libkrb5.so.3 => /my-gpconn-dir/lib/libkrb5.so.3 (0xf7b17000) libcrypt.so.1 => /lib/libcrypt.so.1 (0xf7ae5000) libldap_r-2.3.so.0 => /my-gpconn-dir/lib/libldap_r-2.3.so.0 (0xf7a9f000)/lib/ld- linux.so.2 (0x00a0e000) libk5crypto.so.3 => /my-gpconn-dir/lib/libk5crypto.so.3 (0xf7a7b000) libcom_err.so.3 => /my-gpconn-dir/lib/libcom_err.so.3 (0xf7a75000) libkrb5support.so.0 => /my-gpconn-dir//lib/libkrb5support.so.0 (0xf7a6d000) libresolv.so.2 => /lib/libresolv.so.2 (0xf7a5a000) liblber-2.3.so.0 => /my-gpconn-dir/lib/liblber-2.3.so.0 (0xf7a4c000) Each of the shared objects should be found. If exceptions occur, the LD_LIBRARY_PATH environment file should be adjusted. It is recommended that the GP Client Tools from EMC be downloaded and installed. The client tools can be downloaded from EMC’s PowerLink web site or the Greenplum Community site. This tool will be used to verify connectivity between the Linux machine where the Cognos BI Server will run and the target Greenplum database. Please refer to the GPClientToolsUnix PDF in the Greenplum Database Client Tools for Unix installation pack for detailed installation instructions. Briefly, the installation of the GP client tools consists of these steps: 1. Download the appropriate greenplum-clients-4.1.1.0-build-4-RHEL5-x86_64.bin installer package for RedHat Linux. 2. Unzip the installer: unzip greenplum-clients-4.1.1.0-build-4-RHEL5-x86_64.bin.zip 3. Run the installer: /bin/bash greenplum-clients-4.1.1.0-build-4-RHEL5-x86_64.bin. Accept the license agreement and supply an absolute path for the tool installation. Working with the Cognos BI Server in Linux with the Greenplum Database 6
• 7.
  4. As a convenience, a greenplum_clients_path.sh file is provided in the client tools installation directory following installation to set the environment variables GPHOME_CLIENTS, PATH, and LD_LIBRARY_PATH. Verify Connectivity between the Cognos Linux Machine and Greenplum Before beginning the configuration of an ODBC connection for Cognos, it is recommended that the connectivity between the Linux machine where the BI Server will run and Greenplum be verified. If issues such as firewall restrictions exist, they will be exposed by this verification step. The psql command line tool included in the Greenplum Client Tools installation will be used to verify connectivity. At a Unix command prompt, simply invoke the psql command line tool supplying the Greenplum master database host name, port number, user name, and password. The psql command connection options are: Connection options: -h, --host=HOSTNAME database server host or socket directory (default: "local socket") -p, --port=PORT database server port (default: "5432") -U, --username=USERNAME database user name -d, --dbname=DBNAME database name A successful connection is followed by a prompt from psql that includes the database name. For example, to access the cognos_samples database, the following command would be issued supplying the correct host name, username, and password. -bash-3.2$ psql -h HOSTNAME -U USER -d cognos_samples -p 5432 psql (8.2.15) Type "help" for help. cognos_samples=# Some users may be inclined to confirm the connectivity between the Cognos BI host and the Greenplum database using the Linux-installed isql program. Caution should be exercised during this test because there is the possibility of a word-size mismatch between a 64-bit isql program and the 32-bit Greenplum Connectivity pack ODBC driver, resulting in a false-negative result to this connectivity test. It is recommended that the “-v” option be supplied when invoking isql in order to expose wrong ELF class errors. Configure an ODBC DSN for Greenplum The data source name (DSN) that is used to connect to Greenplum is specified in the odbc.ini file. This file may be placed wherever it is convenient. The environment variable ODBCINI will be used to inform Cognos where to find it. To get to the Greenplum database, the following are needed:  The database name Working with the Cognos BI Server in Linux with the Greenplum Database 7
• 8.
   The host name or IP address of the GPDB master server  The port number used by the GPDB, default 5432.  The username to log in to the master server  The password of the login user There are two sections of interest in the odbc.ini file, namely the ODBC Data Source section followed by one section for each DSN defined. These will be described by in-line comments below. # ODBC Data Sources lists the DSN’s to be defined [ODBC Data Sources] Greenplum=PostgreSQL driver for Greenplum # DSN for Greenplum points to cognos_samples database [Greenplum] Description = PostgreSQL driver for Greenplum # the absolute location for ODBC driver to be used Driver = /my-gpconn-install-dir/drivers/odbc/psqlodbc- 08.04.0200/unixodbc-2.2.12/psqlodbcw.so # tracing is may useful during testing but turned # after moving to production Trace = 0 TraceFile = /tmp/odbctraces_dbtm Debug = 0 DebugFile = /tmp/odbcdebug # the name of the target database Database = cognos_samples # the host name or IP address, user and password of the target data server Servername = xx.x.xx.xxx UserName = user-name Password = password # default port number for Greenplum Port = 5432 ReadOnly = No RowVersioning = No # recommended size MaxLongVarcharSize = 2048 DisallowPremature = No # provides some efficiency in query reuse UseServerSidePrepare = Yes ShowSystemTables = Yes ShowOidColumn = No FakeOidIndex = No # allows for cursor fetch of result sets avoids out of # memory errors in Cognos BI server useDeclareFetch = 1 Fetch = 4096 UpdatableCursors = Yes # required version Protocol = 7.4 # recommended sizes CacheSize = 75000 MaxVarcharSize = 1024 Working with the Cognos BI Server in Linux with the Greenplum Database 8
• 9.
  Validation of theconnectivity between the Cognos BI Server and the Greenplum database In order to validate the connectivity end-to-end between Cognos and Greenplum a Cognos data source connection will be created that will be used in small package created in Framework Manager and exercised in Cognos Connection Query Studio. Create a Cognos Data Connection From the Cognos Connection portal, launch IBM Cognos Administration. Select Configuration > Data Source Connections > *New Data Source. Enter a Data Source Name and Description and select the Next button. For a Type, pick ODBC from the pull down and select the Next button. For the ODBC data source, enter the ODBC DSN created above, in this example “Greenplum”. Supply the User ID and Password in the Sigons section. Working with the Cognos BI Server in Linux with the Greenplum Database 9
• 10.
  At the bottomof page, select Test the Connection. Verify connectivity to Greenplum through Cognos and ODBC by selecting the Test button. Working with the Cognos BI Server in Linux with the Greenplum Database 10
• 11.
  The next pageshould show the connection status as Succeeded. Complete the Cognos data connection by selecting Close twice, followed by Finish. Create Project in Framework Manager In Windows, start Framework Manager (Start -> Programs -> IBM Cognos -> IBM Cognos Framework Manager). From the Welcome page, click Create a new project. In the New Project page, specify a name and location for the project, for example Greenplum in this location, and click OK. In the Select Language page, click the design language for the project. Once the Metadata Wizard appears, select the Cognos Data Source created above, in this case Greenplum, and then select the Next button. The scope of Greenplum objects to be imported by the Wizard can be controlled in the Select Objects screen. Assuming the IBM Cognos Samples database has been loaded into Greenplum, select the branch table in Great Outdoors Sales (gosales) schema. Select the Next button to continue. Working with the Cognos BI Server in Linux with the Greenplum Database 11
• 12.
  It is importantnote that although Greenplum does not enforce referential integrity, users should include foreign key constraints during data migration since they are the source of information for the Metadata Wizard to build relationships between tables in Query Subjects. Select Import followed by Finish to complete the metadata import process. Create a Package In order to make the Query Subject just created available for reporting in the Cognos Connection, a Package must be created and published. In Framework Manager select Create under Packages. Working with the Cognos BI Server in Linux with the Greenplum Database 12
• 13.
  Give the Packagea name, in this case Greenplum and select the Next button. Select the Next button and include the Greenplum function set in the Create Package screen. Select the Finish button and specify the IBM Cognos 10 Content Store as publishing location in the Publish Wizard – Select Location Type. Select defaults for security and publish. Exit the Wizard by selecting the Finish button. Create a Report in Query Studio The final step in the end-to-end validation is to create a report in Query Studio. From the Cognos Connection portal, launch Query Studio. In the Insert Data menu select branch_code, address1, address2, and city from the branch table. The appearance of Working with the Cognos BI Server in Linux with the Greenplum Database 13
• 14.
  data demonstrates asuccessful end-to-end validation of the Cognos to Greenplum connectivity. Working with the Cognos BI Server in Linux with the Greenplum Database 14
• 15.
  Conclusion As stated atthe outset of this white paper, the correct functionality of the Greenplum database with the Cognos BI Server is dependent on the configuration of an ODBC driver using the Greenplum Connectivity Pack. This white paper walked the reader through the process of driver selection, installation, the configuration and validation of an ODBC connection to Greenplum, the creation of a Cognos data connection to a Greenplum database, and validation of that connection using Cognos Framework Manager and Query Studio. Working with the Cognos BI Server in Linux with the Greenplum Database 15