By Rick Ryan, Envisn, Inc.
In the first couple of blogs in this subject series we covered the major elements of the Cognos metadata universe and how to harness them for use. And one of our goals was to be able to access metadata from any of the four major groups and be able to link when appropriate with data from any other group. This is possible because we have all of the metadata from the groups in an XML database which is a virtual image of the Cognos Content Store.
In this real world example we’ll go deep into the detail of what’s possible with the Cognos metadata.
It’s not unusual for joins to be created at the business level in Framework Manager. And, though not recommended, it is sometimes done. However, when this is done it can be difficult to determine which tables and columns Cognos will use for these joins. One reason to know this is to properly index the database for performance where this may be helpful.
Having the full data lineage enables us to determine if relational data source indexes are optimized for a Framework Manager model. This is especially true when the FM relationships are defined at a business level in the model as with this example below.
Here we document a join between query subjects at the FM business level which is defined using model item references:
Budget <- - -> Key Master
[Budget BV].[Budget Master glba_budact_mstr glg_budg_dtl].[Ledger]
= [Budget BV].[Organization Key Master k_key_mstr].[Ledger] AND
[Budget BV].[Budget Master glba_budact_mstr glg_budg_dtl].[Budget Organization Key]
= [Budget BV].[Organization Key Master k_key_mstr].[Organization Key]
We’re now able to transform this business level expression of the relationship to a database expression using the calculated lineage information for this model. This is in the form of [DATABASE.TABLE.COLUMN] and will show database columns which may benefit from an index:
[ONESOLUTION FINANCE.GLBA_BUDACT_MSTR.GLBA_GR]
= [ONESOLUTION FINANCE.GLK_KEY_MSTR.GLK_GR] AND
[ONESOLUTION FINANCE.GLBA_BUDACT_MSTR.GLBA_KEY]
= [ONESOLUTION FINANCE.GLK_KEY_MSTR.GLK_KEY]
It’s probably important here to share with you how this was done. Since we have all of the metadata available in an XML database, we can easily translate the FM model descriptions into what they represent in terms of databases, tables and columns. And equally important, we can link any and all of the Cognos metadata with any other parts as needed.
Another metadata use case example is where a legacy database named Northwind is to be removed from the active category and we want to know if any Content Store objects are still using it.
Here we’ll do here is a quick search of the entire Content Store and see if any objects are using this database.
An obvious question is how far can you go with this? From our experience there don’t seem to be any limits on what you can do with the metadata once you have it all and know how to use it. The secret is to atomize the data and put it in a repository that enables its use without the requirements that a traditional database requires and then get creative. The reality is that you really can answer any question you would ever need to.
Next: Part IV: Unmasking Cognos Security