Cognos Analytics Data Servers

Joe Schmoe asks a fairly common question about Cognos Analytics data servers in the comments to my Framework Manager vs Data Modules article:

In Cognos Analytics 11.1.5, I don’t see a way to use a data source to feed a data module. It looks like in order to use data from a database, you need to use a package — which means you need to use Framework Manager anyway. Am I missing something?

Let’s take a look at how data servers work in Cognos Analytics to (eventually) answer Mr. Schmoe’s question.

What is a data server?

A data server is simply a connection to a database that has been defined within Cognos Analytics. This definition contains the database url, connection parameters, username and password necessary for Cognos to authenticate with, issue queries to and receive data from the desired database. A data server can be re-used by an infinite number of data modules to provide self-service users and report authors controlled access to desired data while maintaining high quality row-level security. Longtime Cognoids will no doubt say ‘Ryan, that sounds exactly like what we used to call a data source!’ Yes. Yes it does.

Cognos Analytics data server vs Cognos 10 data source

Cognos Analytics data servers and Cognos 10 data sources are fundamentally the same while having some differences in where and how they are configured. If you’re familiar with Cognos 10 you can breath a sigh of relief as your existing knowledge is almost 100% applicable. You just need to learn a slightly new UI.

Cognos Analytics data server

• Introduced in Cognos Analytics
• Provides a definition which Cognos uses to connect to a database
• Configured via the manage menu
• Requires JDBC drivers and uses exclusively JDBC connectivity
• Uses Dynamic Query Mode exclusively
• One data server can be used by infinite data modules
• Source for data modules only – no Framework Manager [Editors note: Commentor Jerzy points out below that you can use data servers as a source for FM for DQM models only. I didn’t know that! Thanks Jerzy!]

Cognos 10 data source

• Introduced in Cognos 8? 7? Reportnet? I was in college, it’s all very hazy
• Provides a definition which Cognos uses to connect to a database
• Configured via legacy administration console
• Uses a wide variety of connection types including JDBC, ODBC and others
• Uses Dynamic Query Mode or Classic Query Mode
• One data source can be used by infinite Framework Manager packages
• Source for Framework Manager only – no data modules (we’ll cover a simple workaround later in this article)

As you can see, data servers and data sources serve the same function within Cognos – defining a reusable database connection for Cognos – and have many of the same features. They differ primarily in the types of databases to which they can connect, the connectivity standard they use and how they are configured within Cognos.

Configuring data servers

Configuring a data server is easy and is – for the most part – a one time, ‘set it and forget it’ task most frequently done by Cognos administrators. Like everything in Cognos you have a few options for how to proceed. Before we dig into that though, let’s take a look at the info we need regardless of how we’re configuring data servers.

Data server configuration checklist

1. Identify which type of database you want to connect to
2. Make sure you have permission to connect to it!
3. Check to make sure it’s supported by Cognos
4. Check to see if there are any special considerations for that database
5. Acquire the relevant JDBC driver and install it in the ‘drivers’ folder of the Cognos install directory
6. Ask your DBA for the following information:
   • URL for connecting to the database
   • Database name
   • Schema name
   • Username for authentication
   • Password for authentication

Configuring a data server from scratch

Now that we have our ducks in a row we can build the data server. This is incredibly easy, though you might not know it from reading these instructions. Let me break it down for you:

1. Click the manage icon then select ‘Data server connections’
2. Click the plus button in the upper right of the data server connections window
3. Select your database type from the list that appears
4. The data server configuration window will automatically open for you to input your connection url from above.
5. In authentication method, select ‘use the following signon’ and input your username and password
6. Click ‘Test’ and ensure Cognos can connect to your database
7. Click on the ‘Schemas’ tab and set the following:
   • In ‘Load options’, choose whether or not you want Cognos to retrieve statistical samples from the database. I generally leave this alone. Turning this off will disable certain advanced features of Cognos – like auto joins in data modules
   • In ‘Tables’, select which tables you want to expose via the data server. I highly recommend you select only the tables which you need. Allowing Cognos to profile your entire production database during work hours is an extremely bad idea…
8. Click ‘Save.’ Your data server is ready for use in data modules!

Converting Cognos 10 data sources to Cognos Analytics data servers

While it’s true that a data source configured in Cognos 10 cannot directly feed a data module, you can easily convert a data source to a data server provided the administrator has configured a jdbc connection for the data source. To do so follow these steps:

1. Click the manage icon then ‘Administration Console’
2. Click ‘Configuration’ tab and ‘Data Source Connections’
3. Click the ‘set Properties’ icon for the data source you wish to convert
4. Check the ‘Connection’ tab
5. Check the box next to ‘Allow web-based modeling’
6. Click ‘OK’
7. Exit the administration console and open ‘Data Servers’ in the ‘Manage’ menu. You should see your data source list as a data server.

Using data servers in Cognos analytics

By following the process outlined above we can easily use data servers to get our data into data modules and then into reports, dashboards or the new exploration feature without ever needing to reference Framework Manager. I hope this satisfies Mr. Schmoe and as always, contact me if you have any questions!

---

Read on to level up your Cognos skills!

• Cognos Union Queries in Reports
• Cognos Relative Dates in 11.2
• The 2021 Gartner BI Magic Quadrant is Broken for Cognos Analytics
• Data Modeling for Success: BACon 2020
• Cognos Analytics 11.1.6 What’s New