Time Dimension Design from Cognos Perspective

Cognos do provide tecgniques like determinants at FM level to avoid double counting, but we cognos guys should make sure we don't reach to that level if possible. The best is to flatten the hierarchy, most of the time we face this issue in time dimension but if we can flatten by not having surrogate key for month level and only have it for day level, we will be good. Directly join month level fact with this day level surrogate key...

Cognos FM best practices

We all know that implementing best practices at FM  is very important as all reports/cubes/metric designer are dependent on it.

We may not follow best practice at report studio or analysis studio level as these things don't have any other objects which are dependent on them.

But at cube and at FM and at metric designer it is imperative to follow best practice.

Let us discuss PRACTICALY best practice at FM level.

Create following layers

Data layer - vanilla database table objects , no joins at this layer. Define the query items property here so that following layers can inherit that.

Business layer - crate joins and determinants and give business names to query items.


Dimension leyer - if you are doing a DMR.

Presentation layer or short cut layer -  better you call it a short cut layer so that you know you have to use short cut here , you might want crate star schema groupings here if required.

If there is a confirmed dimension that lies in 2 star schema grouping better you make join again at presentation layer b/w short cuts so that cognos can do stitching .






Thank You,
Vishwas Gupta

Stitched query, star schema grouping and cognos FM

Many a times instead of using cognos own automatic functionalities , seen people using star schema groupings.

Start schema groupings are mainly used when you want to present clean picture of query subjects to the user so that they don't mix business subjects in the report.

It should not be used when you are having multiple facts in design as multiple facts will be taken care automatically when cardenilities are set 1-n between facts and dimensions. FM will automatically resolve multiple paths via stitched queries.



Thank You,
Vishwas Gupta

Determinants and FM layers

Determinants in framework manager has to be defined in the layer where you have joins defined.

If you have defined joins at business layer and defined determinants at dimension layer then nothing will work !!



Thank You,
Vishwas Gupta