Composite Server

COGNOS(R) 8 C O G N O S 8 A N D C O M PO S I T E (TM) I N FO R M A T I O N S E R VE R GETTING STARTED

Cognos 8 and Composite Information Server (TM) Getting Started 08-1-2005 Cognos 8 8.1 Type the text for the HTML TOC entry Type the text for the HTML TOC entry Type the text for the HTML TOC entry GETTING STARTED

THE NEXT LEVEL OF PERFORMANCE

TM

Product Information This document applies to Cognos 8 Version 8.1 and may also apply to subsequent releases. To check for newer versions of this document, visit the Cognos support Web site (http://support.cognos.com).

Copyright Copyright (C) 2005 Cognos Incorporated. Portions of Cognos(R) software products are protected by one or more of the following U.S. Patents: 6,609,123 B1; 6,611,838 B1; 6,662,188 B1; 6,728,697 B2; 6,741,982 B2; 6,763,520 B1; 6,768,995 B2; 6,782,378 B2; 6,847,973 B2; 6,907,428 B2; 6,853,375 B2. Cognos and the Cognos logo are trademarks of Cognos Incorporated in the United States and/or other countries. All other names are trademarks or registered trademarks of their respective companies. Composite is a trademark of Composite Software, Inc. While every attempt has been made to ensure that the information in this document is accurate and complete, some typographical errors or technical inaccuracies may exist. Cognos does not accept responsibility for any kind of loss resulting from the use of information contained in this document. This document shows the publication date. The information contained in this document is subject to change without notice. Any improvements or changes to either the product or the document will be documented in subsequent editions. U.S. Government Restricted Rights. The software and accompanying materials are provided with Restricted Rights. Use, duplication, or disclosure by the Government is subject to the restrictions in subparagraph (C)(1)(ii) of the Rights in Technical Data and Computer Software clause at DFARS 252.227-7013, or subparagraphs (C) (1) and (2) of the Commercial Computer Software - Restricted Rights at 48CFR52.227-19, as applicable. The Contractor is Cognos Corporation, 15 Wayside Road, Burlington, MA 01803. This software/documentation contains proprietary information of Cognos Incorporated. All rights are reserved. Reverse engineering of this software is prohibited. No part of this software/documentation may be copied, photocopied, reproduced, stored in a retrieval system, transmitted in any form or by any means, or translated into another language without the prior written consent of Cognos Incorporated.

Table of Contents Introduction 5 Chapter 1: Cognos 8 and Composite Information Server 7 Workflow 7 Chapter 2: Installing and Configuring Composite Information Server 9 Composite Server and Cognos 8 Installation Options 10 Install the Composite ODBC Driver and Driver Manager (UNIX and Linux only) 10 Start Composite Studio 11 Create Users in the Composite Domain 11 Configuring Composite Information Server 11 Change the Case Sensitivity Policy 12 Change the Trailing Spaces Policy 12 Enable Logging for Debugging 12 Change the Location of the Temporary File Folder 12 Data Access Considerations 13 Uninstall Composite 13 Chapter 3: Accessing Composite Information Server with Framework Manager and Cognos 8 15 Add a Composite Data Source in Composite Studio 15 Securing Data and Metadata with Cognos 8 and Composite Information Server 16 Simplifying the Metadata in Composite Studio Before Publishing to Cognos 8 16 Create a View 17 Create a Composite Data Service 17 Publish the Data Source 17 Add an ODBC DSN to your Installation 17 Create a Data Source Connection in Cognos 8 19 Import the Metadata into Framework Manager 19 Enhancing Your Metadata Using Framework Manager 20 Set the Usage Properties 20 Specify Dimension Information 20 Verify Relationships and Create Joins 20 Create the Star Schema Grouping 21 Chapter 4: Tuning Composite Information Server 23 Persistent Caching 23 File-based Caching 23 Database Caching 24 Optimize a Query 24 Change Connection Pool Properties 25 Change the Metadata Cache Size 25 Chapter 5: Troubleshooting 27 Data Sources Appear Empty After Importing into Framework Manager 27 Result Set Not Shown for Functions and Stored Procedures 27 Web API Error When Trying to Open Composite Studio 27 Metadata Synchronization Problems Between Composite Information Server and Framework Manager 28 Supported SQL Syntax 28 Index 29

Getting Started 3

4 Cognos 8 and Composite Information Server(TM)

Introduction This document gives instructions for installing, configuring, and using Composite Information Server with Cognos 8 and Framework Manager. Cognos 8 is a Web product for creating and managing ad hoc and managed reports. Composite Information Server is a server product that helps business users query different and distributed information sources. Composite Information Server creates a unified interface to business information. For more information about using Cognos 8, visit the Cognos support Web site (http://support.cognos.com). For more information about using Composite Information Server, visit the Composite software Web site (http://www.compositesw.com). The following documents contain related information, and may be referred to in this document. Document

Description

Cognos 8 Administration and Security Guide

Managing servers, security, reports, and Portal Services; setting up Cognos samples; and customizing Cognos 8

Framework Manager User Guide

Creating and publishing models using Framework Manager

Cognos 8 Installation and Configuration Guide

Installing, upgrading, configuring, and testing Cognos 8, changing application servers, and setting up samples

Composite Installation and Administration Installing Composite Information Server Guide components and managing security and performance Note: This guide is installed with the product, and is also available at the root level of the CD. Composite Getting Started Guide

Teaching new users how to use Composite Information Server

Composite User’s Guide

Using Composite Studio to model, publish, and manage resources

Composite Reference Manual

Describing which SQL functions, operators, and keywords are supported by Composite Information Server

This document is available as an online book. Our documentation includes user guides, tutorial guides, reference books, and other materials to meet the needs of our varied audience.

Books for Printing You can print selected pages, a section, or the whole book. Cognos grants you a non-exclusive, non-transferable license to use, copy, and reproduce the copyright materials, in printed or electronic format, solely for the purpose of providing internal training on, operating, and maintaining the Cognos software.

Getting Started 5

Introduction

6 Cognos 8 and Composite Information Server(TM)

Chapter 1: Cognos 8 and Composite Information Server Use Cognos 8 together with Composite Information Server • to provide access to additional data sources, such as LDAP, JDBC, Open XML, and WSDL • to improve performance when querying data from different data sources Use Composite Information Server to create a view of the database that is optimized for Cognos 8. Use Framework Manager to model the database view and create a single business view. Cognos 8 components, including Framework Manager, use an ODBC interface to access a Composite data service. Composite Server accesses the data sources through Java Database Connectivity (JDBC), a Java API, ODBC, the OS File System, and SOAP. Cognos 8 and Composite Components

Cognos 8

Framework Manager

Composite ODBC driver and driver manager Composite Server

JDBC

ODBC Excel

Composite Studio

Relational data source

SOAP Web services

Flat files, XML

OS File System

For information about the structure of Composite Information Server, see the Composite User’s Guide. For information about the Cognos 8 components, see the Cognos 8 Installation and Configuration Guide.

Workflow Working with Composite Information Server and Cognos 8 requires that you perform tasks in sequence. In the following diagram, the workflow is divided into three parts: • setting up the environment The first part of the workflow involves setting up your environment, including installing and configuring the appropriate software and drivers. Depending on the environment, some tasks are optional. • creating a JDBC data source

Getting Started 7

Chapter 1: Cognos 8 and Composite Information Server

•

The second part of the workflow includes accessing and simplifying the metadata using Composite Information Server. accessing metadata using Cognos 8 The third part of the workflow involves preparing metadata for reporting in Cognos 8.

Optionally, after you begin working with Composite Information Server and Cognos 8, you may decide to tune Composite Information Server to better suit your reporting environment (p. 23).

Workflow Setting up the environment

Creating a data source

Accessing metadata using Cognos 8

Install Composite Information Server (p. 9)

Add a data source (p. 15)

Create a data source connection in Cognos 8 (p. 19)

Install JDBC drivers for DB2 and Microsoft SQL Server data sources and restart CIS

Secure the metadata Simplify the (p. 16) metadata (p. 21)

Import the metadata into Framework Manager (p. 19)

Start Composite Studio (p. 11)

Create users in the Composite domain (p. 11)

Configure Composite Information Server (p. 11)

optional

Simplify the metadata (p. 16)

Create a view (p. 17)

Create a Composite data service (p. 17)

Publish metadata (p. 17)

Create an ODBC system data source (p. 17)

8 Cognos 8 and Composite Information Server(TM)

Enhance the metadata (p. 20)

Chapter 2: Installing and Configuring Composite Information Server Install Composite Information Server in your Cognos 8 environment to enhance performance when querying heterogeneous data sources. You can install Composite Server on a server running Microsoft Windows, Red Hat Enterprise Linux, Sun Solaris, or HP-UX. To view a complete list of environments currently supported by Cognos products, visit the Cognos support Web site (http://support.cognos.com). Some post-installation tasks are required to ensure that Composite Information Server works with Cognos 8. The following table lists the default ports for Composite Information Server. Port

Description

9400

Composite Studio requests to Composite Server

9401

JDBC and ODBC connections

9403

MySQL

If port 9400 is not available, Composite Information Server attempts to find other available ports by using increments of 10. Ensure that Cognos 8, including Framework Manager is installed, configured, and running. For more information, see the Cognos 8 Installation and Configuration Guide. Before you install, ensure that you have administrator privileges for the computer you are installing on. The name of the directory where you install Composite Information Server must not contains spaces. Use the following checklist to guide you through the installation and configuration process: ❑ Ensure that your computer meets the working environment required to install Composite Information Server. For more information, see the Composite Installation and Administration Guide. ❑ Choose a Composite Server and Cognos 8 installation option.

❑ Install Composite Studio on a Windows computer. For instructions, see the Composite Installation and Administration Guide. ❑ Install Composite Server. For information, see the Composite Installation and Administration Guide. Note: You can also install multiple instances of Composite Server on the same computer. ❑ Install the Composite ODBC driver on each Framework Manager and Cognos 8 report server computer. Because Cognos 8 uses the ODBC driver to access Composite Information Server data sources, clear the JDBC driver check box. For information about installing on Windows, see the Composite Installation and Administration Guide. For more information about installing on UNIX or Linux, see "Install the Composite ODBC Driver and Driver Manager (UNIX and Linux only)" (p. 10). ❑ Install JDBC Drivers for DB2 and Microsoft SQL Server Data Sources (optional). Getting Started 9

Chapter 2: Installing and Configuring Composite Information Server For instructions, see the Composite Installation and Administration Guide.

❑ Start Composite Studio. ❑ Create users in the Composite domain. ❑ Configure Composite Information Server. The Composite Server and the CompositeServerRepository (MySQL service) services automatically start when the installation is finished. If you experience problems during the installation, check the logs available in the logs folder in the Composite_installation_location directory. Otherwise, you can start using Composite Studio to access metadata (p. 15).

Composite Server and Cognos 8 Installation Options Before using Composite Information Server, you must decide how to install it in your existing Cognos 8 environment. You can install Composite Server and Cognos 8 on one computer, or on separate computers. The best distribution option depends on your reporting requirements, resources, and preferences. Configuration requirements differ depending on whether you install all components on one or multiple computers. For more information about Composite Information Server components, see the Composite User’s Guide. Composite Server and the Cognos 8 report server can also be installed on a UNIX or Linux computer. No matter which Cognos 8 installation option you choose, you can install Framework Manager and Composite Studio on a computer separate from the one where you have Cognos 8 report server and Composite Server. You must install the Composite ODBC driver on each computer running either the Cognos 8 report server or Framework Manager. You must also install the Windows ODBC driver on the same computer as Framework Manager.

Install Cognos 8 and Composite Server on One Computer You can install Composite Server on a computer where Cognos 8 is already installed. Choose this scenario for proof of concept or demonstration environments where the user load is small. Although you can install Composite Server on the same computer as Cognos 8, check your available system resources and user load. For information about disk space and memory requirements for Composite Information Server, see the Composite Installation and Administration Guide. For similar information about Cognos 8, see the Cognos 8 Installation and Configuration Guide. When deciding whether to install both Composite Server and Cognos 8 report server components on the same computer, consider that Composite Server and Cognos 8 each run in a separate instance of Tomcat, each uses a Java Virtual Machine (JVM) - which consumes resources at startup - and each uses its own logging facility.

Install Cognos 8 and Composite Server on Separate Computers In this option, you may have one or more Cognos 8 services accessing a single instance of Composite Server. In most environments, install Composite Server on a computer separate from Cognos 8 for better performance and availability. Installing one instance of Composite Server ensures that there is only one Composite modeling repository and that data is consistent to all Cognos 8 services.

Install the Composite ODBC Driver and Driver Manager (UNIX and Linux only) Because Cognos 8 uses the Composite ODBC driver to access Composite Information Server data sources, you must install the ODBC driver and driver manager on each instance of Cognos 8 report server installed on UNIX or Linux. 10 Cognos 8 and Composite Information Server(TM)

Chapter 2: Installing and Configuring Composite Information Server The driver manager routes all Cognos 8 requests to the appropriate ODBC driver to access the data sources. When you add an ODBC DSN using the driverConfig utility (p. 18), you are identifying an ODBC driver to the driver manager. The driver manager then knows that the data source associated with this DSN is accessed through a particular ODBC driver. For instructions about installing the Composite ODBC Driver and driver manager, see the Composite Installation and Administration Guide. After this installation, you can start Composite Studio and connect to the Composite Server.

Start Composite Studio Before using Composite Studio, you must connect to Composite Server. Ensure that you know the host name or IP address of the computer where Composite Server is installed. For instructions about starting Composite Studio, see the Composite Installation and Administration Guide. After you successfully start Composite Studio and connect to Composite Server, you must create users in the Composite domain.

Create Users in the Composite Domain You add users to the Composite domain • to enable multiuser modeling in Composite Studio • to limit access to Composite resources, such as data sources and views By default, it is assumed that immediately after installation, the first person to start Composite Studio is an administrator. For instructions about adding users to the Composite domain, see the Composite Installation and Administration Guide. After you add users, you can grant them privileges to Composite resources.

Configuring Composite Information Server When you install Composite Information Server, default configuration settings are set. If you have any reason not to use these default values, you can change them to meet the needs of your reporting environment. Here are a few examples of the common configuration tasks that you may want to perform: • change the case sensitivity policy • change the trailing spaces policy • enable output for logging (debug output enabled) • change the location of the temporary file folder • enable caching views When you turn on caching, you can choose from file-based caching or database managed caching. When you choose file-based caching, the file and the file location are not encrypted. Therefore, you may want to choose database managed caching (p. 24). • data access considerations After you configure Composite Information Server to meet your requirements, you can tune it for performance (p. 23).

Getting Started 11

Chapter 2: Installing and Configuring Composite Information Server

Change the Case Sensitivity Policy Composite Information Server uses a case insensitive string comparison by default. The SQL specification encourages the use of case sensitive string comparisons. Depending on the database that you are using, you may want to change the way Composite Information Server compares strings. Performance may be affected if you change the default Composite Information Server setting. For more information, see the Composite Installation and Administration Guide. Important: Ensure that the case sensitivity policy of Composite Information Server matches the policy of your database. Otherwise, you may experience performance issues.

Steps 1. Start Composite Studio. 2. From the Administration menu, click Configuration. For information about the Configuration window and how to change settings, see the Composite Installation and Administration Guide. 3. In the configuration tree, under Server, click Query Engine, Configuration, and then Case Sensitive. 4. Change the setting as required.

Change the Trailing Spaces Policy By default, Composite Information Server ignores trailing spaces when performing string comparisons. Depending on the database that you are using, you may want to change the way Composite compares strings. Performance may be affected if you change the default Composite Information Server setting. For more information, see the Composite Installation and Administration Guide.

Steps 1. Start Composite Studio. 2. From the Administration menu, click Configuration. For information about the Configuration window and how to change settings, see the Composite Installation and Administration Guide. 3. In the configuration tree, under Server, click Query Engine, Configuration, and then Ignore Trailing Spaces. 4. Change the setting as required.

Enable Logging for Debugging By default, debug output is not enabled. If you require assistance in resolving issues, you can set the Debug Output Enabled property to true. Because enabling this property can produce large files, enable the output only for a specified period and then disable it to prevent performance from degrading.

Steps 1. Start Composite Studio. 2. From the Administration menu, click Configuration. For information about the Configuration window and how to change settings, see the Composite Installation and Administration Guide. 3. In the configuration tree, under Server, click Logging and then click Debug Output Enabled. 4. Change the setting as required.

Change the Location of the Temporary File Folder You can change the location of the temporary file folder if you require encryption or other security for query results and cached data. The temporary file location is not encrypted.

12 Cognos 8 and Composite Information Server(TM)

Chapter 2: Installing and Configuring Composite Information Server The amount of disk space that the location uses depends on the query characteristics and is used dynamically. It is used to keep temporary files, handle large queries, and handle heavy load. We recommend that you have several gigabytes allocated and that you monitor the available free disk space.

Steps 1. Start Composite Studio. 2. From the Administration menu, click Configuration. For information about the Configuration window and how to change settings, see the Composite Installation and Administration Guide. 3. In the configuration tree, under Server, click Configuration and then click Temp Directory. 4. Change the setting as required.

Data Access Considerations Composite Information Server handles different data source behaviors. Generally, you should try to match Composite Information Server settings with the underlying data source to avoid performance issues or data integrity problems.

Data Source Settings For best performance, we recommend that you ensure the following server settings are set to true. • Disable Case Sensitivity Correction • Disable Ignore Trailing Space Correction • Push If Case Sensitivity Mismatch • Push If Ignore Trailing Space Mismatch

Collation Sequences When querying heterogeneous data sources, ensure that the collation sequence for each data source that you create in Composite Information Server is the same. For example, they all must be either case sensitive or case insensitive. Depending on the data sources involved, collation sequences for different database sources in a query can cause errors or inaccurate query results. For information about collation sequences, see your database vendor documentation.

Uninstall Composite If you no longer require Composite Information Server or if you are upgrading, uninstall all Composite Information Server components. Before you uninstall, ensure that you back up data. For information, see the Composite Installation and Administration Guide. For instructions about uninstalling, see the Composite Installation and Administration Guide.

Getting Started 13

Chapter 2: Installing and Configuring Composite Information Server

14 Cognos 8 and Composite Information Server(TM)

Chapter 3: Accessing Composite Information Server with Framework Manager and Cognos 8 Before you can create models in Framework Manager and run reports in your portal, you must make the data source available to Framework Manager. Composite Information Server makes additional data sources available to Cognos 8 and improves performance when using heterogeneous data sources. Framework Manager sees Composite data sources as collections of views that do not contain index or key information. To make the data source available in Cognos 8, follow these steps: ❑ Add a Composite data source in Composite Studio (p. 15).

❑ ❑ ❑ ❑ ❑ ❑ ❑ ❑ ❑

Secure metadata (p. 16) (optional). Simplify the metadata (p. 16) (optional). Create a view (p. 17) (optional). Create a Composite data service (p. 17). Publish the data source (p. 17). Add an ODBC DSN to your installation (p. 17). Create a data source connection in Cognos 8 (p. 19). Import the metadata into Framework Manager (p. 19). Enhance your metadata using Framework Manager (p. 20).

After the metadata is available in Framework Manager, you can model the metadata to suit your business needs. For more information about modeling metadata using Framework Manager, see the Framework Manager User Guide.

Add a Composite Data Source in Composite Studio Before you can model your metadata, you must create a link between your modeling tool and your database. You do this by adding a data source. In Composite Information Server, a data source is the Composite representation of the external, physical data source that is available to the Composite Server. The data source is exposed by publishing it to a Composite data service. In Cognos 8, a data source is a named set of connections to a physical database. Cognos 8 views Composite connections as data sources. The act of adding a data source to the Composite modeling environment is known as introspection. You must always supply data source authentication when adding a data source. There are two ways to add a data source. You can add a data source manually and supply all required information or you can have Composite Studio search your network for data sources. You supply the IP connection, and Composite Studio scans all commonly used ports and returns a list of all discovered databases. Note that you cannot use spaces when naming data sources. We recommend that you use underscores instead. Tip: If you added a new table to the data source and want it to appear in Composite Studio, right-click the object and click Add / Remove Resources. For instructions about adding data sources in Composite Studio, see the Composite User’s Guide. Getting Started 15

Chapter 3: Accessing Composite Information Server with Framework Manager and Cognos 8

Securing Data and Metadata with Cognos 8 and Composite Information Server When working with Cognos 8 and Composite Information Server, you can specify security at different levels. You must always supply data source authentication when adding a data source. By default, immediately after installation, the first person to start Composite Studio is an administrator. This may be sufficient for your needs because metadata and data security can be specified in Framework Manager. You can limit access by using Cognos 8 to secure a DSN that was created using the administrator userID and password. You must create an ODBC DSN to provide all users connectivity to the Composite Information Server data source through an ODBC driver. These data sources are local to a computer. All users with the appropriate privileges can access an ODBC DSN. The data source name must use fewer than 32 characters. If you add users to the Composite domain, consider the following: • You can configure a DSN to allow specification of a user ID and password in a signon from Cognos 8. • Access privileges are not inherited. A user must have read and select privileges to a Composite data service if you are using it for modeling or reporting. For more information, see the Composite User’s Guide.

Simplifying the Metadata in Composite Studio Before Publishing to Cognos 8 You may want to simplify your metadata before making it available in Cognos 8. This can make modeling easier in Framework Manager. In some situations, we recommend that you model your metadata using Composite Studio before publishing to a Composite data service. For example, model in Composite Studio in the following situations: • You have heterogeneous data sources that you must combine. • You want to cache the data. • The data is always modeled the same way, and you want to ensure that it is processed by Composite Information Server. Examples of this include adding calculations and filters, data that is aggregated to which the details will never be exposed, and creating complex views. • You are publishing stored procedures. Some cursor outputs cannot be detected automatically. You may have to design the cursor manually (p. 27). • Your data contains binary large objects (BLOBs). BLOB data types are not supported. You can remove them from the table or, if they are less than 32K, you can convert them to SQL_VARBINARY data types. If you convert them to SQL_VARBINARY data types and they are more than 32K, they are truncated. • You must resolve SQL traps. • Your data structure can be collapsed but it can be done in either Composite Information Server or Framework Manager. Examples of this include collapsing master/detail facts and collapsing or denormalizing tables with hierarchical relationships or snowflaked dimensions. This data must be collapsed either by creating a Composite Information Server view or a Framework Manager query subject. For more information about metadata modeling using Composite, see the Composite User’s Guide.

16 Cognos 8 and Composite Information Server(TM)

Chapter 3: Accessing Composite Information Server with Framework Manager and Cognos 8

Create a View If you want to access and integrate data from multiple data sources, you can create a view. The view is then published to a Composite data service, making it available to Cognos 8. If the data that you want is available from individual tables from a single data source, you can publish the database tables directly. For more information, see the Composite User’s Guide. Note that you cannot use spaces when naming views. We recommend that you use underscores instead. Because index and key information is not published as part of a Composite view, joins must be explicitly defined because they are usually between tables in data sources that do not have implicit foreign key relationships. A criteria that can be used to detect joins is the Name field. It is more difficult to detect usage properties because of the absence of index or key information on views. Index and key information is used with data type information to set usage properties automatically. You must specify dimension information if a hierarchy relates to more than one query subject at different levels. Dimension information must be available to ensure that rollups are calculated properly. We recommend that you simplify your metadata (p. 16) before making it available to Cognos 8. For instructions about creating views in Composite Studio, see the Composite User’s Guide.

Create a Composite Data Service A Composite data service represents tabular data and procedures that were published as a relational schema or as hierarchical XML data. You must create a catalog and a schema before Framework Manager can import views that were published in Composite Information Server. For instructions about creating a Composite data service using Composite Studio, see the Composite User’s Guide.

Publish the Data Source Publishing a data source to a Composite data service makes it available to Framework Manager. Object references that are published to a Composite data service are exposed to Cognos 8 using ODBC. They appear in Framework Manager as views. Since published Composite data services are actually references to the published Composite objects, any change in the Composite data source is automatically reflected in the published Composite data service. Tip: To ensure that the updates are shown in Framework Manager, right-click the query subject and click Update Query Subject, or select Project Synchronization from the menu to update the model. For more information, see the Framework Manager User Guide. You must have read and select privileges to a Composite data service if you are using it for modeling. Access privileges are not inherited. For information about access privileges, see the Composite User’s Guide. Views must be published into the schema level of a Composite data service. For instructions about publishing tables and views to a Composite data service, see the Composite User’s Guide.

Add an ODBC DSN to your Installation You use ODBC to access the Composite data source from Cognos 8.

Getting Started 17

Chapter 3: Accessing Composite Information Server with Framework Manager and Cognos 8 On Windows, use the ODBC Data Source Administrator to add a system data source to your computer. On UNIX or Linux, use the Composite Information Server command line utility named driverConfig to add an ODBC DSN. The data source associates a particular ODBC driver with the data you want to access through that driver. Before you can create an ODBC DSN, you must have the appropriate permissions for the Composite configuration files and libraries.

Steps for Windows 1. Click Start, Settings, and Control Panel. 2. Double-click Administrative Tools, and then double-click Data Sources (ODBC). The ODBC Data Source Administrator dialog box appears. 3. Click the System DSN tab. Although Composite data sources can be accessed using a User DSN, you must create a System DSN for use with Cognos 8. 4. Click Add, click Composite, and then click Finish. The Composite Software ODBC Driver Configuration dialog box appears. 5. Click Help for detailed information about the System DSN tab and adding a data source. 6. Enter the appropriate information, considering the following: • You cannot have a System DSN and a User DSN with the same name. • By default, Composite Server uses port 9401 and the composite domain. If Composite Server did not encounter conflicts with these settings during the installation, use these values for the Port and Domain settings. • By default, the Composite host value is set to localhost. • You must create a system data source name to provide all users connectivity to the Composite Information Server data source through an ODBC driver. These data sources are local to a computer. This means that all users with the appropriate privileges can access a system DSN. The data source name must have fewer than 32 characters. Tip: Click Refresh for the schema name to be automatically located.

Steps for UNIX or Linux 1. On the computer where the Composite ODBC driver is installed, go to the directory $COMPOSITE_HOME/apps/odbc/bin. Before you can create an ODBC DSN, you must have the appropriate permissions for the Composite configuration files and libraries. 2. Run the following command: ./driverConfig The Main Menu for the driverConfig utility appears. 3. At the prompt, type 3 and press Enter. 4. At the prompt, type 1 and press Enter. The Create DSN menu appears. 5. At the DSN name prompt, type the DSN name for your data source and press Enter. 6. At the driver prompt, press Enter to accept the default driver name. 7. At the host prompt, type the IP address of the computer where Composite Server resides and press Enter. 8. At the port prompt, type the port number and press Enter. The default port number is 9401. 9. At the username prompt, type the username for this DSN and press Enter. 10. At the password prompt, type the password and press Enter. 11. At the domain prompt, type the domain name and press Enter. The default domain name is composite. 12. At the datasource prompt, type the name of your data source and press Enter. 13. At the catalog prompt, type the name of the catalog and press Enter.

18 Cognos 8 and Composite Information Server(TM)

Chapter 3: Accessing Composite Information Server with Framework Manager and Cognos 8 14. At the y/n prompt, type y and press Enter. 15. Repeat steps 3 to 14 for each ODBC DSN that you want to add. 16. At the main menu, type 0 and enter the appropriate information, considering the following: • By default, Composite Server uses port 9401 and the composite domain. If Composite Server did not encounter conflicts with these settings during the installation, use these values for the Port and Domain settings. • You must create an ODBC DSN to provide all users connectivity to the Composite Information Server data source through an ODBC driver. These data sources are local to a computer. Ensure that all UNIX or Linux accounts have the appropriate rights set. The data source name must have fewer than 32 characters. • The Cognos 8 daemon must have Read and Execute permissions for the Composite configuration files and library files to run with the ODBC driver. 17. Press Enter to exit.

Create a Data Source Connection in Cognos 8 Before you can create models in Framework Manager, you must define the data source connection. A data source connection supplies the parameters that Cognos 8 needs to connect to the database, such as the location of the database and the timeout duration. A connection can also include credential information and signons.

Steps 1. Log on to Cognos Connection as an administrator. 2. On the portal toolbar, click Directory. 3. Click the Data Sources tab. Tip: To remove a data source, select the check box for the data source and click the delete button. 4. Click the new data source button. 5. In the name and description page, type a unique name for the data source and, if you want, a description and screen tip. Select the folder where you want to save it, and click Next. 6. In the connection page, click Composite (ODBC) and then click Next. The connection string page for the selected database appears. 7. Specify any parameters that make up the database connection string, specify the timeout, and select whether to create a signon. The ODBC data source parameter is the ODBC DSN that you added earlier. You do not need to specify the ODBC connect string and the collation sequence. We recommend that you open the database asynchronously. If you specified signon information when you created your system data source, you do not need to specify it here. 8. If you want to test whether parameters are correct, click Test. If prompted, type a user ID and password or select a signon, and click OK. 9. Click Finish. The data source appears as an entry in the Directory tool in the portal, and can be selected when using the Import wizard in Framework Manager. For more information about creating data source connections in Cognos 8, see the Cognos 8 Administration and Security Guide.

Import the Metadata into Framework Manager Framework Manager can use the metadata from external data sources to build a project. You can import metadata into a new project or an existing project.

Getting Started 19

Chapter 3: Accessing Composite Information Server with Framework Manager and Cognos 8 All objects that are published by Composite Information Server appear as views in Framework Manager. The index and key information for these views is not published by Composite Information Server. For this reason, you cannot import joins and do not need to specify criteria in the Generate Relationships dialog box. Although you can generate relationships using query item names, we do not recommend this because there is insufficient metadata to generate cardinality correctly. After importing the metadata, all numeric codes appear as measures. This is because the rules that Framework Manager uses to determine the Usage property are based on numeric fields having keys or indexes as identifiers. Numeric fields without keys or indexes are treated as facts. For instructions about importing metadata into Framework Manager, see the Framework Manager User Guide.

Enhancing Your Metadata Using Framework Manager Framework Manager sees Composite data sources as collections of views that do not contain key or index information. After the Composite data source is available in Framework Manager, examine the metadata carefully to ensure that it is ready to be modeled. Do the following: • Set the usage properties (p. 20). • Specify dimensional information (p. 20). • Verify the relationships and create joins (p. 20). • Create the star schema grouping (p. 21). Tip: If you want to add a column from a table in the data source, go back to Composite Studio and add the column and then save the data source. To see the change in Framework Manager, right-click the query subject and click Update Query Subject.

Set the Usage Properties You must set the usage properties for each query item. The usage property identifies the intended use for the data represented by each query item. Because the key and index information is unknown, the usage properties may be set incorrectly. For example, all numeric and date codes without key and index information appear as facts. All character data types are set as attributes. For instructions about setting usage properties and information about the rules governing them, see the Framework Manager User Guide.

Specify Dimension Information Levels define how you roll up data. For example, days roll up to months and months roll up to years. Under normal circumstances, keys and indexes are used to determine the lowest level. In the absence of index and key information, it is important to use dimension information to ensure that rollups are performed correctly. For instructions about specifying dimension information, see the Framework Manager User Guide.

Verify Relationships and Create Joins You should verify the relationships and create joins accordingly. A relationship defines the connection between two query subjects. Without relationships, query subjects are isolated pieces of information. Under normal circumstances, keys, indexes, and names can be used to detect relationships. In the absence of key and index information, only names can be used. For instructions about verifying relationships and creating joins, see the Framework Manager User Guide.

20 Cognos 8 and Composite Information Server(TM)

Chapter 3: Accessing Composite Information Server with Framework Manager and Cognos 8

Create the Star Schema Grouping A dimensional model often uses a star schema design. One common form of a star is quantitative, where transactional data is contained in a central fact table. Related dimension tables radiate out from the fact table. For instructions about creating star schema groupings, see the Framework Manager User Guide.

Getting Started 21

Chapter 3: Accessing Composite Information Server with Framework Manager and Cognos 8

22 Cognos 8 and Composite Information Server(TM)

Chapter 4: Tuning Composite Information Server After Composite Information Server is installed and configured, you may decide to make changes to some of the Composite settings to better suit your reporting environment. For example, you may want to enable persistent caching, optimize your queries, change the database connection properties, or change the size of the metadata cache.

Persistent Caching Composite Information Server can be configured to enable persistent caching. With persistent caching, the query result set is cached to improve performance or lighten the load on an underlying data source. The cache persists even after the session ends. Composite Server optimizes the running of queries by having the underlying data source do the query computations. You may want to cache data if • the query is complex For example, you may want to use caching to eliminate sub-trees in the query structure. • the query takes a long time to run • the same data is being queried repeatedly • the data source is not always available • the data changes significantly during peak periods Caching at a specific time means that users can see a consistent view of data when it is changing rapidly. By default, caching is not enabled. If you enable caching, you can cache the data to a local file or to a database. If you cache the data to a file, the file and the directory where the file is located are not encrypted. Therefore, you may want to consider other methods to secure the folder where the cache file is located. Only views can be cached. Stored procedures and tables taken directly from the data source cannot be cached unless they are wrapped in a view. Composite Information Server can generate a persistent cache for views that can be refreshed manually from Composite Studio or on a scheduled basis. A user may not be able to identify whether the cached data is current. If the cached data is not current, the query results retrieved from the cache can be meaningless or misleading. Therefore, we recommend that you include a timestamp, such as CURRENT_TIMESTAMP, in the Composite view to indicate when the cache data was last refreshed. If you want to cache an entire data source, create views for each table and apply caching to each view. You can then publish these views to a Composite data service, making them available to Cognos 8. For instructions about enabling caching in Composite Studio, see the Composite User’s Guide.

File-based Caching Depending on your usage, file-based caching is typically sufficient. With file-based caching, the query result set is saved in a local file. The file does not require an administrator to manage it because it is managed automatically on the Composite server. If a cache refresh is unable to complete, the data is rolled back.

Getting Started 23

Chapter 4: Tuning Composite Information Server Local caching is not recommended for queries with large result sets. When the query makes a call to the cached data, the file-based cache must scan the data and read every row. If the cache is large, this may detract from the performance enhancements that are typical when using cached data. The cached data in system files cannot be shared by multiple Composite server installations. You can customize the location of the cached file. The directory where the system file is located is not encrypted. To secure the cached data, use folder permissions or an encryption file system. For more information, see the Microsoft Windows help.

Database Caching Database caching is recommended if your SQL query makes selections against the cached view or when result sets are large. A database cache can contain indexing, which speeds up data selections. If the cached data is used in a join, the query is processed by the original data source, resulting in improved performance. If you store a cached view in a database, you must manually create the database table to match the column names and types of the view. Any available data source with permissions can be a cache container. The account used to update the database table must have insert, update, and delete privileges. The cache should be optimized by your database administrator. If you want to cache an entire data source, you must create views for each table and apply caching to each view. If the data in a table is volatile, you can schedule automatic or manual updates to refresh the cached data so that users see a consistent view of data.

Optimize a Query A query causes data to be fetched from the appropriate data sources. Letting the underlying data source process as much of the query as possible minimizes the amount of data returned and improves performance and speed of data retrieval. Composite Server examines the query and optimizes the relationships in the data source before running it. These optimizations can be viewed in the query plan. Understanding how a query is processed is crucial to writing good queries. This becomes especially difficult when using queries that span multiple data sources. You may face some of the following issues: • network latency • limited capabilities of disparate data sources, such as the fact that .csv files do not support joins • limited access to data sources, which is dependent on the driver capabilities • inability to monitor fluctuations in the amount of available data Composite Information Server applies rule-based optimizations automatically, requiring no user input. This reduces the number of rows fetched from the data source, which reduces the amount of work done by Composite Server and returns the result set as quickly as possible. Cost-based optimizations analyze the join algorithms to explore the nature of the data. These statistics are used to develop the best possible query plan. For instructions about optimizing your queries, see the Composite User’s Guide.

Query Plans Query plans are generated when any query runs. Query plans are relationship-based and not flow-based. This means that views can be used to represent business processes, but when the query is run, the view is flattened. The relationships are examined and, using the rules of the underlying data source, an optimized query plan is reassembled before accessing the data source.

24 Cognos 8 and Composite Information Server(TM)

Chapter 4: Tuning Composite Information Server When two queries have the exact same signature, Composite Information Server attempts to reuse the previous query plan, thereby improving performance and decreasing the time to process the query.

Change Connection Pool Properties When you add a data source, you can change the properties of its connection pool. A connection pool is a set of database connections that are available for an application to use. There is one connection pool for each data source, and the pool is created on demand. Before running a command, a connection to a database must be established. Sometimes creating and removing the connection is more costly than running the command. For this reason, connection pools are created to maintain connections. After a connection is created, it is placed in the connection pool for future use.If all the connections in the pool are being used, new connections are automatically created and made available through the pool. Each data source always has configurable connections open (the connection pool minimum size), a maximum number of connections that is allowed (the connection pool maximum size), and a timeout period (the connection pool timeout). Connection pools are never removed. Tip: To release a connection, you must stop and restart the Composite server. For information about connection pool properties, see Adding Data Sources in the Composite User’s Guide.

Change the Metadata Cache Size Composite Information Server uses a metadata repository to process query requests. To enhance performance, it maintains a dynamic cache of metadata information during run time. The default size of the cache is sufficient for most installations, but you can change the size of this cache if necessary. We recommend that you set the cache size to approximately the same size as the ALL_RESOURCES system table in Composite Information Server. Tip: To find the size, introspect all data sources that you connect to with Composite Information Server and run a count query.

Getting Started 25

Chapter 4: Tuning Composite Information Server

26 Cognos 8 and Composite Information Server(TM)

Chapter 5: Troubleshooting Use this troubleshooting information as a resource to help you solve specific problems that you may encounter during or after the installation of Composite components. Problems are characterized by their symptoms. Each symptom can be traced to one or more causes by using specific troubleshooting tools and techniques. After being identified, each problem can be fixed by implementing a series of actions. Composite Information Server provides log files about installation, system events, and user activities. These log files are located in the Composite_installation_location\logs folder. For more information, see the Composite Installation and Administration Guide.

Data Sources Appear Empty After Importing into Framework Manager After importing metadata from a Composite data source, the data source appears empty in Framework Manager. In Composite Information Server, when you publish the data source to a Composite data service, you must publish at the schema level of the Composite data service. Otherwise, the data source appears empty in Framework Manager. If the data source appears empty, go back to Composite Studio and republish the data source to the schema level of a Composite data service. You must reimport the metadata into the Framework Manager project.

Result Set Not Shown for Functions and Stored Procedures Composite Studio exposes functions and stored procedures as views. Initially, the result set of these views contains only the return value from the procedure or view. To see the full result set in Framework Manager, the cursor outputs must be identified. In some cases, the cursor outputs for functions and stored procedures cannot be detected automatically. If the result set does not appear when you run a function or stored procedure, you can use the Design By Example function in Composite Studio to design a cursor that defines the result set that the user wants to see. For information about designing a cursor using Design By Example, see the Composite User’s Guide.

Web API Error When Trying to Open Composite Studio You try to open Composite Studio and see a Web API Error. The Composite server appears to be started. On startup, the Composite server requires a few minutes to initialize before you can use it.

Getting Started 27

Chapter 5: Troubleshooting

Metadata Synchronization Problems Between Composite Information Server and Framework Manager If changes are made to a Composite data source while you are connected, Framework Manager is not automatically updated. Therefore, if you try to model data in Framework Manager that no longer exists or was renamed, you get errors. If the existing Composite views were changed, you can update the query subject in Framework Manager to refresh the data. If new views or tables were added, you must reimport the metadata into your Framework Manager project to refresh the data.

Supported SQL Syntax If you attempt to use SQL syntax that is not currently supported by Composite Information Server, error messages appear. For information about the SQL syntax currently supported by Composite Information Server, see the Composite Reference Manual.

28 Cognos 8 and Composite Information Server(TM)

Index A adding a system DSN (Windows), 17 an ODBC DSN (UNIX or Linux), 17

B BLOB data types, 16

C cache metadata, 25 caching data database managed, 23 file-based, 23 case sensitivity configuration settings, 12 Cognos 8 accessing Composite data sources, 17 creating a data source connection, 19 using with Composite Information Server, 7 Composite data services creating, 17 Composite domain creating users in, 11 Composite Information Server accessing, 15 configuring, 11 persistent caching, 23 query optimizations, 24 securing metadata, 16 tuning and performance, 23 using with Cognos 8, 7 Composite Server installing on one computer with Cognos 8, 10 installing on separate computers, 10 Composite Studio adding data sources, 15 creating views, 17 data services, 17 introspecting, 15 modeling using functions, 16 modeling using stored procedures, 16 publishing data sources, 17 simplifying metadata, 16 starting, 11 troubleshooting, 27 Composite views index and key information, 17 configuration settings, 11 case sensitivity, 12 debug output, 12

configuration settings (cont'd) temporary files location, 12 trailing spaces, 12 configuring case sensitivity, 12 Composite Information Server, 11 connection pool properties, 25 temporary file location, 12 trailing spaces, 12 connection pool properties, 25 copyright, 2 cost-based optimizations, 24

D data access, 13 data access considerations collation sequence, 13 data source settings, 13 data source connections creating in Cognos 8, 19 data sources adding in Composite Studio, 15 making available in Cognos 8, 15 publishing in Composite Studio, 17 troubleshooting, 27 database caching, 23 database security creating users in the Composite domain, 11 debug output configuration setting, 12 configuring, 12 default ports, 9 dimensional information specifying in Framework Manager, 20 dimensional models in Framework Manager, 21 document version, 2

F file-based caching, 23 Framework Manager creating joins, 20 creating star schema groupings, 21 enhancing metadata, 20 generating relationships, 19 importing metadata, 19 setting usage properties, 20 specifying dimensional information, 20 verifying relationships, 20 working with dimensional models, 21 functions modeling in Composite Studio, 16

Getting Started 29

Index functions (cont'd) troubleshooting, 27

I index and key information Composite views, 17 installation options, 10 installing Cognos 8 and Composite Server on one computer, 10 installing Cognos 8 and Composite Server on separate computers, 10 installing checklist, 9 Cognos 8 and Composite Server on one computer, 10 Cognos 8 and Composite Server on separate computers, 10 introspecting Composite Studio, 15

S security creating users in the Composite domain, 11 metadata, 16 simplifying metadata publishing to Cognos 8, 16 SQL supported syntax, 28 star schema groupings creating in Framework Manager, 21 starting Composite Studio, 11 stored procedures modeling in Composite Studio, 16 troubleshooting, 27 system DSN adding, 17

T J

latency of data, 23 logging configuring, 12 output, 12

temporary file location configuring, 12 trailing spaces configuration setting, 12 troubleshooting, 27 empty data sources, 27 functions and stored procedures, 27 metadata synchronization, 28 SQL syntax, 28 Web API error, 27

M

U

metadata configuring the cache, 25 enhancing in Framework Manager, 20 importing in Framework Manager, 19 security, 16 synchronization issues, 28

uinstalling Composite Information Server, 13 usage properties setting in Framework Manager, 20 using Cognos 8 to secure metadata, 16

O

V

ODBC DSN adding on UNIX or Linux, 17

P

version document, 2 views creating in Composite Studio, 17

persistent caching Composite Information Server, 23

W

joins creating in Framework Manager, 20

L

Q query optimizations Composite Information Server, 24 query plans, 24

R relationships verifying in Framework Manager, 20 rule-based optimizations, 24

30 Cognos 8 and Composite Information Server(TM)

Web API error troubleshooting, 27 workflow Composite Information Server and Cognos 8, 7 diagram, 8