CODEWITHSUNDEEP
cognoscognos-10cognos-bi
need_java
need_java

Reputation: 127

How to access database behind Cognos Connection (10.2)

We are using Cognos Connection as a web interface to create/manage Cognos reports via a connection to an Oracle database. We have some reports that are saved to the file system (which I believe means the file system on the server in which Cognos is hosted) and other reports that are distributed via email.

We have unplanned (due to unforeseen circumstances) and planned (due to upgrades, maintenance, deployments, etc..) outages pretty frequently and this causes some Cognos reports to not run. For the reports saved on the file system, we have a view in our Oracle database that shows the last updated date for these reports and stores them in blob format (I'm not sure how they get from the Cognos file system to our Oracle database, but I'll have to figure that one out later). I've created a couple of views to check the last updated date based on the scheduled frequency of the reports (based on a static schedules table I created) and this will give us a quick view of which reports need to be manually ran and saved (due to not running during an outage).

That said, I'm having trouble figuring out how to get the status of reports that aren't saved in the file system and are just emailed to users. I've noticed that their is a run log/run history for each report and I can view the details of each day's log to see if there were issues; however, it's too cumbersome to check 50+ reports manually everyday. Is there a way that I can access the database behind Cognos Connection (where these logs are stored) in order to view this information? For instance, could I create a DB Link from our Oracle database to write queries on the objects behind Cognos? If not, is there a different way to get this information in some sort of automated or mass data pull format?

Any and all insight/guidance/tips are greatly appreciated.

Upvotes: 0

Views: 1143

Answers (1)

Corey
Corey

Reputation: 5239

There are several ways to capture this information. Cognos has an optional Audit extension that may or may not be configured in your environment. This tracks report and job execution data.

It's also possible to connect directly to the Cognos Configuration database, but it's not recommended. It puts unnecessary strain on the system, and you pretty much have to dig through the internal tables to figure out what data you really want. The audit package requires substantially less "sleuthing" to get the data you need.

Report history is also accessible through the API, but API's typically imply use of a programming language... which isn't used too often in most Cognos environments I've seen.

The first thing I do when setting up a new Cognos environment is creation of the aforementioned audit database and reporting packages. IBM published How to configure Audit Reporting in Cognos Analytics, as well as a detailed blog called Enable Auditing in Multitenant Environment of IBM Cognos 10.2 BI (should be relevant to newer versions too).

The key tables to look for are COGIPF_RUNREPORT and COGIPF_RUNJOB that come with the package.

Upvotes: 2

Related Questions