Unleashing the Power of Data Servers in Cognos Analytics

Way back in version 11.0, IBM introduced a new type of data source connection in Cognos Analytics called a Data Server, but if you're not setting up data source connections all the time or using Data Modules, you may not understand how they differ from the traditional Data Source connections. Let's take a look at how they compare to Data Source connections, how you set them up, and what makes them more useful than a standard Data Source connection.

Data Source vs. Data Server

Data Sources and Data Servers are both pointers to a physical database or other source of data. They contain configuration information including the host, port, database name, any connection parameters, and can include login information.

However, there are a few key differences between these two components. Firstly, Data Sources are created using the older Administration Console, while Data Servers are set up using the Manage Panel interface in the main portal. Additionally, Data Sources offer various options for establishing connections to databases, including ODBC or native drivers like SQL Server or Oracle, with JDBC being an optional choice. On the other hand, a Data Server requires a JDBC connection to function, which you may also be familiar with from using Dynamic Query Mode (DQM) with Framework Manager. This is because JDBC provides a standard API that enables Cognos's Java-based back end to access various databases. Furthermore, JDBC connections use a binary format for communication, which is more secure and efficient than other protocols like ODBC. By eliminating intermediate layers, JDBC connections reduce latency, making them essential for large-scale data access and analytics. Finally, Data Server connections allow you pre-load metadata information about the data source and schema where it's connected. Cognos will gather information about the table and pull some sample data to gain a better understanding to help with the creation of join relationships and data types.

Basically, you can think of a Data Server as a newer, more capable version of a Data Source. In fact, Data Servers even show up in the Administration Console under Data Sources. They hold all the same functionality but are also specifically designed for the more modern Cognos studios like Data Modules, Dashboards, and Explorations.

Data Server Connection Setup

Let's step through setting up a new Data Server connection.

Note: The screenshots below are from Cognos 11.2.4. If you're on an earlier version, the UI may look a little different, but the steps are the same.

1. Open the Manage panel --> Data Server Connections. Data Sources are set up in the Administration Console, but Data Servers are created using the newer Manage panel administration.

2. Click on the Add Data Server button:

3. Provide a name and a connection type:

4. Provide the JDBC URL to your data source. Different information is required depending on the type of connection. In this case for SQL Server, I need to include host, port, and database name. Also note the Driver Class name. This driver must be included in a jar file in the "drivers" folder on your Cognos application servers. You should be able to get the jar file from your database application or download it from the web. Cognos services will have to be restarted for it to pick up a new file if one is needed.

5. Optionally, add some Cognos-specific connection properties or isolation level:

6. Choose an Authentication type:

a. Connect anonymously - No login information required by the database. This is rarely used due to the lack of security.

b. Prompt for the user ID and password - This will ask the user to log in to the database, including anyone executing a report or dashboard based on this connection.

c. Use an external namespace - Pass the authentication information for the logged in user to the database. This is a great, secure option if your database uses the same authentication as Cognos. It ensures that any security set up in the database is automatically included.

d. Use signon - Store the user ID and password used to connect to the database within Cognos. This is most often used if you have a service account login to the database.

In this case, I'll select the "Use signon" option and provide a user ID and password. Click the Test connection button at the bottom to make sure it connects.

Data Server Connection Troubleshooting

Hopefully, your connection tests successfully and you're ready to start making a Data Module. However, if it does not connect, here are a few troubleshoots steps to go through:

1. Verify Connection Details: Check that the connection details entered in the Cognos configuration match the details of the database server. This includes the hostname, port number, database name, username, and password.

2. Test the Connection Outside of Cognos: Try connecting to the database using a tool outside of Cognos, such as SQL Developer or DBVisualizer. This can help determine if the issue is specific to Cognos or if it is a general database connectivity issue.

3. Check Network Configuration: Make sure that the database server is accessible from the Cognos server. Check that the firewall is configured to allow traffic on the specified port number.

4. Check Database Configuration: Check that the database server is running and that the database is accepting connections. Verify that the user has the appropriate permissions to access the database.

5. Review Error Messages: Check the Cognos logs for any error messages related to the database connection. The error message may provide clues to the root cause of the issue.

6. Verify JDBC Driver: Ensure that the appropriate JDBC driver is installed on the Cognos server and configured correctly.

Loading Assets

Once the Data Server connection is set up and tests successfully, you have to load Assets before it can be used. This is a new step that differs from Data Source connections.

Navigate into your Data Server connection, click the three dots action menu at the end of the connection name. Then, select Assets from the menu.

Note: In older versions of Cognos this was under the Schemas tab in the properties of the connection.

This brings up the list of schemas in the database. You can load it here by clicking the Load link next to the schema(s) that you plan to use.

You can also look at the options available before loading. This is particularly important if you have a very large number of tables in the schema.

By default, Cognos will try to get the primary and foreign keys along with 1,000 rows of sample data from each table. If you have 100's of tables in the schema, this can put quite a load on the database and take quite a while. You can select a smaller number of rows or unselect the option to retrieve sample data. You will lose some functionality around Cognos intelligently determining data types for the fields and some of the context for the AI Assistant.

You also have the option to select only specific tables if you're not going to use them all. Again, this can be particularly important if you have a large number of database tables, many that you will not be using.

Once the schemas are loaded, you can start to use them in a Data Module. Keep in mind that if you later add a new table to the schema, you will need to come in here again to reload the tables before it can be used in a Data Module.

Conclusion

Data Servers in Cognos Analytics offer significant advantages over traditional Data Sources when it comes to setting up connections to physical databases or other data sources. They are set up using the Manage Panel interface, require a JDBC connection to function, and are specifically designed for modern Cognos studios like Data Modules, Dashboards, and Explorations. Additionally, Data Servers enable you to pre-load metadata information about the data source and schema where it's connected, which helps with creating join relationships and data types. By following the steps outlined in this article, you should be able to set up a new Data Server connection, troubleshoot any issues, and load assets to start using it. Overall, if you're looking for a more capable and modern way of connecting to data sources in Cognos Analytics, a Data Server connection is the way to go.

Next Steps

We hope you found this article to be helpful and informative. If you have any additional questions or comments, contact us today. Be sure to also subscribe to our newsletter for more PMsquare articles, updates, and insights delivered directly to your inbox.