Datasources and Agents

One of the most difficult concepts to master when transitioning to the Cognos Now! modeling environment is understanding the differences and appropriate uses for agents, datastreams, views, cubes, and lookup tables. I will try to discuss all of these objects eventually, but I thought that I would focus on the fundamentals of datasources and agents in particular today.

Setting up the data modeling environment starts with the configuration of an agent. An agent is an object that Cognos Now! uses to connect to an external datasource and extract transactional data. Agents are usually based on jdbc connections to large databases such as Oracle or SQL Server, but they can also be used to connect to files stored on the Cognos Now appliance or other datasources such as Salesforce.com. I will demonstrate the creation of a JDBC agent for an Oracle 9i database here, and discuss other types of agents at a later date.

To create a new agent, you need to define the agent in both the Workbench and within the Appliance Administration console. 

To create an agent in the workbench, select Activities > Create New > Agent. Then select the type. One of the most common agent types is JDBC. JDBC agents can connect to any JDBC standard database, such as Oracle, MS Sql Server, Postgresql, Mysql, etc.  First select "JDBC" as the agent type, then assign a name and set the "Save in" location. It is best to save agents at the root level however, not in a folder. After the agent is created, reopen it for editing and set the database type, then set the type of jdbc connection (I am using Datasource-based for Oracle), and set the JNDI Name as the same as the Agent name for simplicity. It is not nececessary to set username or password here.

Here you can see what the properties screen looks like for a typical agent that connects to an Oracle database.

Note that the agent is configured as "datasource based." This means that the Appliance Administration console is being used to define the datasource details. The agent definition in the workbench only needs to define the name and type of the agent. All agents should be setup in this way, per Cognos Now! customer support. 

The next step is to setup the definition of the datasource in the appliance administration console. Navigate to http://cognos.SERVER_URL.com/rAA and login using the "Admin" account for your system. Once you login, select "Cognos Now! Configuration" then "Manage Data Sources." Select "Add Data Source" to create the new source. You will need to provide a JNDI Name value - this should match the name of the agent you created previously in the workbench. You also need to provide the database connection url, which should look something like this:

oracle:thin:DATABASE_USER/DATABASE_PASSWORD@SERVERNAME:PORT:INSTANCE

You can usually get this information from the tnsnames.ora file provided by your dba for database connectivity. If you don't have it, call your administrator and ask what the jdbc connection string should be for your database. 

Next you need to provide a database driver classname. This will be associated with the jdbc driver that you will copy to your Cognos Now server. A typical classname for an Oracle jdbc driver looks like this:

oracle.jdbc.driver.OracleDriver

This classname will change based upon the type of database you are connecting to and the jdbc driver you are using. 

Additionally, you will have to enter the database username and password for the system you are connecting to. Frequently a custom Cognos schema and database user is created to allow tight control over what information is available for reporting purposes. Finally, you should also set the maximum pool size to control the maximum number of database connections allowed (20 is a good number to start with).

The last step is placing a copy of the appropriate jdbc driver on the Cognos Now! server. To accomplish this you will need to obtain the correct jdbc driver for the system you are connecting to first. You can retrieve jdbc drivers for the Oracle database from the Oracle site. Most other database applications publish jdbc drivers that you can download for free via the web. Also, the Cognos Now! machine should ship with some jdbc drivers already setup. 

After you have retrieved an appropriate jdbc driver, you need to place a copy of it on the Cognos Now! appliance. To do this you can use the "Upload JDBC Driver" interface in the Cognos Now admin console. Select "Cognos Now Configuration" then "Upload JDBC Driver" then use the browse command to select the appropriate jdbc driver file.

If you have completed all the steps correctly, you should be able to setup a datastream and start extracting real time transactional data from your datasource for your reports and dashboards. Stay tuned for design tips and tricks on how to best accomplish this.