Best Practices in Cognos 8 Framework Manager Model Design: Part 2 – Advanced Modeling Issues

August 1, 2010/by Ironside Group

Editor’s Note: This blog was originally posted on August 1, 2010 and has been reviewed and updated on 10/5/2020 with Cognos Analytics 11.1.7. We find that these best practice tips about metadata modeling are still very useful to build solid data foundation for reports, especially cardinality settings and stich query controls as they are critical modeling techniques. These procedures are also applicable to Cognos new web based metadata modeling component – the Data Module, which is greatly adopted by self-service authors and when Cognos is hosted in cloud (no need to install Framework Manger tool).

Last month’s article described the overall approach for best practice model design using the 4 layer architecture. In this month’s article, I’ll describe some advanced issues which arise during model design, and how best to deal with them. Given the limitless and complex situations that can come up during modeling, these techniques may or may not apply to your specific situation.

Data Sources

One area which many modelers neglect is the data sources. A key part of the data source is the Content Manager Data Source, which references a data source configured via the web interface in the Administration area of Cognos Connection. You should have as few of these as possible, ideally only 1 per database, unless there are very specific reasons (such as security). Even so, many security or logon issues can be addressed via multiple connections or signons within a data source.

Framework Manager will automatically create multiple data sources, (one per schema, for example), but they should reference the same Content Manager Data Source. The reason minimizing these is because multiple Content Manager data sources will open separate connections to the database and force Cognos to perform simple joins within the reporting engine instead of in the database where they belong.

Relationships and Cardinality

The area which causes much confusion and difficulties with modeling is relationships and cardinality. There are a number of documents which discuss the handling of data modeling traps, such as the Guidelines for Modeling Metadata document included in the documentation, and so I will not duplicate those discussions here, instead addressing other topics.

A common concern is when to alias a table in the data layer. In general, common dimension tables used across multiple fact tables, or contexts, should not be aliased. This is important in creating conformed dimensional reports. For example, a parts dimension is the same when used for reporting against sales or inventory fact tables. When a table has separate usages, or meanings, it should be aliased. For example, a general purpose address table, which contains branch, customer and employee addresses. These are often identified by having multiple join paths or filters which can signal different usage.

Another situation to use aliases is when there are multiple usages of a dimension table. The most common occurrence of this is the general-purpose date dimension table. Applications such as health and insurance can have many different joins to a date dimension, each of which has a different context. This can be complicated to track, because one usage, such as Admission Date must apply in a conformed manner to many fact tables, while a different usage (Treatment Date) must also be conformed across many of the same dimension tables. Obviously a meaningful naming convention is required!

If you do decide that your logical query subject requires a different alias, make sure to make liberal use of the Remap to New Source option, available by right-clicking on the query subject.

Controlling Stitch Queries

One frequent point of confusion, is how the model causes reports to create stitch (or multiple) queries. To resolve this requires a firm understanding of the cardinality in relationships. Also realize that stitch queries are sometimes best way of issuing a query.

Report studio will generate simple, consistent queries when there is consistent progression from “1 to many” relationships. This is a smooth progression from dimensions to facts. It can easily generate facts with correct aggregations with this simple scenario. Remember that Cognos will treat the query subject at the end of one or series of 1:N relationships as fact tables.

Similarly, it also works well in the case where multiple “dimensions” converge on a single fact table, as shown below. An easy visual check is to make sure that the 1:N relationships proceed in the same direction, with the ultimate fact table serving as the “Many” end-point of several dimensions.

The stitch issue occurs when there is more than one “Many” side along the same dimension path, as shown below.

A query which utilizes both of the rightmost, or “fact” tables will result in a stitch query as the only relationship is via a conformed “dimension” table. In a query where inventory (fact) and sales (fact) are compared at a product (dimension) level, a stitch query is the correct approach. However, if this is an unintentional or poorly modeled relationship, incorrect or inefficient queries will be generated. Understanding the implications of cardinality in relationships is the best defense against this.

Naming Conventions and Nomenclature

Another area of confusion is naming conventions and nomenclature, and areas surrounding these subjects. The best practice is to have the query subject and element names all carefully thought out and implemented before developing the model, but that doesn’t always happen.

First, let’s describe how Framework Manager and report studio considers element names. Each element name is uniquely identified within the package by a 3 part name: The element itself, the parent query subject (or shortcut) name, and the parent namespace to the query subject. Element names must therefore be unique within the query subject, and similarly for query subjects within the namespace. However, the namespace name must be unique within the entire model. So you can have multiple references to element “Part Name” within query subject “Part Master”, but the namespace, such as “Purchasing” or “Inventory” can only occur once within the model, making the fully qualified name unique. Notice that folder names do not come into play when defining names. They are used only for organizing other items.

Managing Name Changes

The result of all this, is that names within the model are very important, especially once report writing begins, because they are difficult to change without invalidating existing reports.

So, in order of preference, maintain consistent names across models by:

Get it right the first time. If multiple areas of the organization have a stake in naming conventions, involve all organizations early in the design process, even if the first stages do not apply to some of them.
Identify situations where a renamed item is used. Here the option “Find Report Dependencies” is invaluable in identifying reports where an item is used. Keep in mind, though, that if your model references a development server, it will not identify reports or queries which exist only in your production environment. Therefore it may not adequately identify issues in every environment. Advanced audit report and SDK script can help identify report<->package dependencies information.
Move the renamed item to a “deprecated” query subject folder, and create a similar element with the new name. While this relies on a certain amount of end-user training, to instruct report and query writers not to use objects within a folder named “DO NOT USE” or similar, it will ensure that existing reports will still run correctly, while using the new, correct name moving forward.

This article addresses some of the most common advanced issues around model design, but certainly not all of them. With modeling being part science and part art, it’s not possible to address all possible situations. If you have a specific modeling situation you would like me to address, please email us at GetInsights@IronsideGroup.com.