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 you’re 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 comnformed “dimension” table. In a query where i nventory (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:

  1. 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.
  2. 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.

  1. 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 me at rbaker at ironsidegroup dot com .

7 replies
  1. Ralph says:

    Avineet,
    You can try to mock up a data table using some other table, or probably even calculations, but you will not be happy with it. There is really no substitute for a good date dimension table.

  2. Avineet says:

    Is there a possibility of creating a date dimension in Framewrok Manager, in the absence of a physical table at the db level?

  3. Ralph Baker says:

    I wouldn’t say that you “Can’t” create combined query subjects if you create your joins in the Business layer, just that it creates confusion and you end up mixing the functions of the layers. I’ve seen many cases where putting the joins in the business layer works fine, and also a few where it’s led to some ugly SQL. Obviously a lot depends on the underlying data structure. If your reports are generating efficient SQL, and your team is familiar and comfortable with the model, you may be okay with leaving it as is. Determinants should also be set in the layer in which you create your joins. If you’d like some specific help for your situation, please contact me at Ironside Group.

    As for role playing dimensions, they function as distinct tables, with separate joins, so I keep them in the data layer. The folder structure is up to you. It does not affect the operation in any way.

  4. Jaya Devulapalli says:

    I agree with you on creating the relationships/joins in the data layer. But in the organization I started working at, the joins have been made in the business layer. Can you give me some disadvantages of creating joins in the business layer in addition to not being able to create a combined query subject (combining 2 tables into 1 like Invoice Header and Detail)? In case of star schema groupings do we create them in the data layer as well under different name spaces? Also for role playing dimensions should the alias shortcuts be created in the data layer as well? do they need to be in a separate folder than the original table to work? Thanks much

  5. curtis says:

    I have read that Cognos “Object Security” should be applied at the presentation layer and your previous article mentioned “security filters” at the logical level. Which one is this correct?

    Also, what is the best way to implement that? For instance take the following example:

    The field Order_Amount is in the table Order which then is used in multiple Cognos packages. Wouldn’t i have to secure every namespace in which the Order_Amount field is used?

    Thanks,
    Curtis

Trackbacks & Pingbacks

  1. […] This post was mentioned on Twitter by hashcognos, Ironside Group, Inc.. Ironside Group, Inc. said: Best Practices in #Cognos 8 Framework Manager Model Design: Part 2 – Adv. Modeling Issues by @RalphRBaker http://ow.ly/2oAhw #ibmsoftware […]

Comments are closed.