Cognos FM Interview Questions and Answers

                                                   Framework Manager Questions

1.     What is Loop in FM

     Loop is a bottleneck in FM and we should avoid it using Alias or shortcut.

Loop: loop is a closed path (relation) that exists among 3 (or) more tables. For example, if we have '3' tables T1, T2, T3 then, a loop exists among these tables only when we create joins in the following fashion:

         Loop:          T1 ---> T2 ---> T3 ---> T1

To resolve the above problem, we have to create a shortcut (or) Alias to the Table T1.

         No Loop:      T1 ---> T2 ---> T3 ---> Alias (or) Shortcut of T1

2.    Determinants

Determinants are a feature of Cognos 8 designed to provide control over granularity.  Determinants are used to avoid double counting. Determinants are most closely related to the concept of keys and indexes in the database. There is no concept of hierarchy in determinants, although the order in which they are specified does govern the order in which they are evaluated.

          When do I need to use Determinants? Joins at multiple levels of granularity on a single query subject
         A classic example of this is the Time dimension in Go Data Warehouse. There are joins to the    

        Time dimension on the day key and on the month key.

        This is mainly an issue when doing multi-fact querying and you need to prevent double counting.

             Example 1: Time Dimension (Unique foreign keys)

Year Key	Month Key	Month Name	Day Key	Day Name
2005	200501	Jan 05	20050101	Sunday, Jan 1, 2005
2005	200502	Jan 05	20050102	Monday, Jan 2, 2005

            It is possible to define three determinants for this data set, two non-unique determinants (Year and  

            Month) and one unique determinant (Day).

          

          Interpretation:

Day key is the unique key of the table therefore you can associate all the columns in the table to this key. Because it is a unique key, we check the Uniquely Identified box and do not check the Group By. Group By is a property that we do not need when data is unique.

 Month key is also a key but is not unique, so the Uniquely Identified box is not checked for this    determinant. However, the Month Key is all that is needed to identify a month in the data. If you wanted to query month from this time table you would write a query that used select distinct syntax and grouped by Month key since the values are repeated, this is why we check the Group By box.

Similar logic applied to the Year determinant.

3.    Star and Snow Flake schema

Star Schema: It has single fact table connected to dimension tables like a star. In star schema only one join establishes the relationship between the fact table and any one of the dimension tables. A star schema has one fact table and is associated with numerous dimensions table and depicts a star.

Snowflake Schema: It is an extension of the star schema. In snowflake schema, very large dimension tables are normalized into multiple tables. It is used when a dimensional table becomes very big. In snow flake schema since there is relationship between the dimensions Tables it has to do many joins to fetch the data. Every dimension table is associated with sub dimension table.
The main difference between star schema and snowflake schema is that the star schema is highly denormalized and the snowflake schema is normalized. So the data access latency is less in star schema in comparison to snowflake schema. As the star schema is denormalized, the size of the data warehouse will be larger than that of snowflake schema.

Performance wise, star schema is good. But if memory utilization is a major concern, then snow flake schema is better than star schema.

A dimension table will not have parent table in star schema, whereas snow flake schemas have one or more parent tables.

The dimensional table itself consists of hierarchies of dimensions in star schema, whereas hierarchies are split into different tables in snow flake schema. The drilling down data from top most hierarchies to the lowermost hierarchies can be done.

4.    Normalization and De – Normalization

Normalization is the process of reducing data redundancy and maintains data integrity. This is performed by creating relationships among tables through primary and foreign keys. Normalization procedure includes 1NF, 2NF, 3NF, BCNF, and then the data is normalized.

Denomalization on the contrary is the process of adding redundant data to speed up complex queries involving multiple table JOINS. One might just go to a lower form of Normalization to achieve Denormalization and better performance. Data is included in one table from another in order to eliminate the second table which reduces the number of JOINS in a query and thus achieves performance.

         

          Steps of normalization:

First Normal form
Entities of the table must have unique identifier or entity key.
Second Normal Form
All the attributes of the table must depend on the entity key for that entity.
Third Normal Form
All attributes that are not part of the key must not depend on any other non-key attributes.

5.    Governor settings.

Governors setting is used to reduce system resource requirements and improve performance. You set

governors before you create packages to ensure the metadata in the package contains the specified

limits. All packages that are subsequently published will use the new settings.

There are a number of governors that you can set. Governor settings in the model can be

overridden by governor settings in Report Studio.

1. Report Table Limits

You can control the number of tables that a user can Retrieve in a query or report

          2.Data Retrieval Limits

You can control the number of rows that are returned in a query or report

          3. Query Execution Time Limits

You can limit the time that a query can take

          4. Large Text Items Limit

You can control the character length of BLOBS (binary large Objects) that a user can retrieve in a

query or report

5.    Allow Enhanced Model Portability at Run Time

    You can use a separate but structurally similar data source at run time. The database schema must be   

    the same between the two data sources.

6.    Allow Usage of Local Cache Select this governor to specify that all reports based on

this model should use cached data.

7.    Outer Joins

You can control whether outer joins can be used in your query or report.

8.Cross-Product Joins

 You can control whether cross-product joins can be used in your query or report.

9.Use With Clause When Generating SQL

You can choose to use the With clause with Cognos SQL if your data source supports the With

clause.

8.    Star Schema groupings

           When you create a star grouping, a new namespace is automatically created for you and all the     

           dimensions in that namespaces are shortcuts to the original dimensions. 

           This way you can have a dimension with all its calculated items, etc. and use it in many different star    

           groupings. That is the whole purpose is to have conformed dimensions being used with multiple facts. 

9.    Alias & Shortcut

           Alias cannot be refreshed when base query updated, but short cut will be refreshed.

A shortcut is a pointer to another entry such as a report, report view, folder, job, agent, page, or URL. You can use shortcuts to organize information that you use regularly. For example, if you frequently use a report in Public Folders, you can create a shortcut in My Folders.

Alternatively, you could create shortcuts as the aliases to the
data source query subject and create your relationships to the shortcuts. To create an alias shortcut, right-click on the query subject it will reference and choose Create Alias Shortcut from the context menu.

a shortcut is always a pointer to another object to which our shortcut points to.

But in contrast, an Alias (shortcut) is a second copy of the original object and, since this is a just copy and not the actual object, any change made in the original object is not reflected in this one.

10. Stitched Query

When we have multi facts objects involved in our report, Cognos will generate two separate queries with each Fact table and combine them with FULL OUTJOIN. These queries are called Stitched queries.

          If you are trying to model a full outer join involving two fact tables related through a common

          Dimension table, note that Cognos 8 will handle such situations using a stitch query.

          A query that uses multiple facts from different underlying tables is split into separate queries for

          each underlying fact table. Each single fact query refers to its respective fact table as well as to the

          Dimensional tables related to that fact table. Another query is used to merge these individual queries

          into one result set. This latter operation is generally referred to as a stitched query. You know that

          you have a stitched query when you see coalesce and a full outer join.

11. Cardinality

12. Security

                 Package

                 Object

                 Data

                 Row Level

13. Parameter maps

14. Session parameters

15. What is DMR models.

       

16. Conformed dimensions

       Confirmed dimensions are the dimensions which can be used in multiple star schemas. The dimensions   

       which is used more than one fact table is called conformed dimensions. the dimensions which are used   

       across multiple data marts are called as conformed dimensions. Dimesnsions, which are reusable.

17. Multi fact multi grain

      Multiple-fact, multiple-grain queries in relational data sources occur when a table containing dimensional     

      data is joined to multiple fact tables on different key columns. 

      A dimensional query subject typically has distinct groups, or levels, of attribute data with keys that     

      repeat. The IBM® Cognos® studios automatically aggregate to the lowest common level of granularity 

      present in the report. The potential for double-counting arises when creating totals on columns that 

     contain repeated data. When the level of granularity of the data is modeled correctly, double-counting can 

     be avoided. 

    Note: You can report data at a level of granularity below the lowest common level. This causes the data of    

    higher granularity to repeat, but the totals will not be affected if determinants are correctly applied.

18.  what is dynamic cube? 

19. . What is the usage propperity?