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?
