August 21, 2013, by Paul Hausser, Envisn, Inc.
People new to administering or managing Cognos BI often assume that a lot of the information they need to manage their Cognos environment and Content Stores is readily available. Basic information about key attributes such as size, what databases are used, failure rates, dependencies, etc. It’s disappointing to discover that there is not much of that information available right out of the box. Where it does exist at all, the information may be limited to a single object like a report or query.
The Challenge
If you really want to understand Cognos BI in all its dimensions and complexity you basically have two options: Create it yourself or look for a third party application to provide it.
To do this you need to capture the properties or classes and parameters of every object in the Content Store and then be able to reassemble these in multiple ways depending on what you need to know at any given point in time. Plus, you also need information on users and their security profile since this is a key dimension within Cognos.
To take a simple example of a single Cognos Report:
Report Properties & Parameters:
a. Specification
b. Package
c. Model
d. Data source
e. Data lineage
f. Database usage
g. Report views
h. Schedule
i. Jobs
And this is only a partial listing of properties (classes) and none of the multiple levels of sub categories.
But it goes beyond the detail and dimensions of each object in the Content Store. You also need to know the relationship of each object’s dimensions to those of every other object. Why? Because so many of the questions that an administrator or author/developer needs to answer relate to dependencies. What reports are used by package XYZ? What objects will be impacted if we make major changes to the Customer table in the database?
Security inheritance complicates this even further. Determining how an object’s security or access profile is set can be difficult or impossible within Cognos itself. And of course you have the problem of how to store all of this data. And since this data is mostly hierarchical rather than relational, a SQL database may not be the most appropriate one to use.
The Payoff
The number of permutations for object properties and sub-attributes that Content Store objects can possess is large. Cognos 10.2.1, for example, consist of 630 classes and 479 properties. Each class contains a subset of those properties which can also be class members or inherited from ancestor classes. This is a ton of data to deal with so there is a lot of work to be done defining and organizing the metadata and then coming up with rules to be able to actually use it successfully. But when this is one the results are extremely powerful. You’re able to answer any question about your Cognos BI environment instantly and able to manage routine tasks around making changes, version control and promoting content between environments quickly and easily.
And in the area of security the payoff in getting useable data can be huge. So if you need to know the members, memberships and expanded members of a group or role it’s easy to get that in a clear, simple format.
Is it practical for the average Cognos administrator to try to do this on their own? No. It would take far too long and the rules that have to be developed to turn this mélange into useable information can be complicated.
Some other examples of hidden Cognos metadata that can be easily retrieved include:
- All objects with model item dependencies
- List of reports with descriptions and query names
- List of package data sources, tables & columns and if used in any reports
- List of package data sources & tables
- List of package data sources & tables and if used in any reports
- List of all objects currently in the Recycle Bin
- List of all reporting objects with drill-though targets
- Find object information using store ID
- Update query to change recycle bin objects to restore to different location
- List all databases, tables & columns found in models
- List of all reports & owner with embedded SQL (non Cognos SQL)
- Virtually any question about any Cognos metadata you can come up with. Plus, be able to filter or group it to get exactly what you need.
The real takeaway here is that you need this kind of Cognos metadata to meet the challenge of managing your Cognos BI environment effectively. And if you’re planning on implementing best practices in Cognos it’s essential to have this information.
All of the capabilities discussed here are available in our NetVisn product. It was built the hard way, from the ground up using what we learned in the use of Cognos BI and developing the metadata rules along the way.
© 2013 – Envisn, Inc., Tools for Cognos Administrators, All rights reserved.
