Data Profile Your Cognos Content Store

By Paul Hausser, Envisn, Inc.

One of the most frequently asked questions of Cognos administrators is, “What databases are we using, and where and how are we using them?” Virtually every manager wants to know about the data that’s being used for management reporting, financial reporting, government reporting, etc. And as important as that question is, it’s often difficult or impossible to answer that question with any accuracy unless you have the tools to respond to those questions.

And if you did attempt to answer the question, how would you do it? Absent any direction, what would be the best way to try and present how data sources are actually used in your Cognos environment? We will try to explore answers to those questions in this blog article.

What might a data profile of your Content Store actually look like?

Well the first thing it would need to do is to identify all the databases. Following that, it would be important to know where they are sourced, the quality of the data, and after that where they are being used in the Cognos environment.

To identify all of the databases we have in our Cognos Content Store we will do an analysis of all data sources in the Content Store and get a list of them. In Figure 1 we will create a query for all of the databases.

Figure 2 shows a listing of all of the 58 databases used in our Cognos environment along with a summary of the total objects in our Content Store.

Now that we have answered the first question in terms of sizing the number of databases used in our Cognos environment, we can begin to look and where and how they are used. For this we want to select a single database. In this case we have chosen the Northwind database and in Figure 3 we are going to do a query and see how many objects are using this database.

Here in Figure 4 we see that 278 of our total objects are using the Northwind database.

Our next step (Figure 5) is to begin to drill down on the Northwind database and examine where some specific tables and items in the database are used. It this case we are selecting the table Customers and the item Company Name.

Here in Figure 6 we see the results along with a partial listing of each of the total 165 objects using Company Name.

The challenge of identifying where any database is used requires us to be able to look across every object in the entire Content Store and identify any and all links of any database-table-column. No small task, but it is doable, and once done it enables us to create a detailed data profile of the entire Cognos environment at any point in time. This means, for example, that we to know where every database, and all parts of it, are used. This capability also makes it easy to answer many questions arising from Sarbanes-Oxley (SOX) compliance or annual audits when necessary.

What this also means is that when we have this level of detailed information about our data sources, we also have the ability to know exactly which objects will be impacted if there are to be changes to any of the databases. This makes it easier to make changes in a planful way and not create any breaks in the data lineage within report or query objects.

Once you have the ability to get all of the database data wherever it’s used you then have the flexibility to create virtually any kind of a data profile you might ever want. It’s simply all of the data with whatever dimension is needed. This is very powerful because once you’ve unlocked the data in your Content Store you’ve created the Rosetta Store that enables you to use it in hundreds of different way for all the tasks that face you ask a Cognos manager, administrator, developer, etc. every day.

Summary

So there are essentially two basic questions here. What databases do we have and how do we use them. Ideally on the first question we want to know what databases we have, their source or provenance, and what is in them. The second question is how and where they are used. With the right tools answering these questions is a simple, straight forward task.

For the examples in this blog we have chosen Envisn’s NetVisn product to demonstrate the ability to data profile a Cognos Content Store.