Blog Archive

Theme images by Storman. Powered by Blogger.


Saturday, August 20, 2016

Latest Cognos Framework Manager Interview Questions.

Latest Cognos Framework Manager Interview Questions. Latest Cognos FM Interview Questions With Answers. Most Frequently Asked Cognos Framework Manager Interview Questions And Answers.


What Are Different Levels Of security In FM ?

Data security: You create a security filter and apply it to a specific query subject. The filter controls the data that is shown to your users when they set up their reports.

Object  security: You secure an object directly by allowing users access to the object, denying users access to The object, or keeping it hidden from all users.

Package security: You apply security to a package and identify who has access to that package

Which object you can import in FM?

Stored procedure, Views, Tables, Materialized views.

Cardinality:

Relationships exist between two query subjects. The cardinality of a relationship is the number of related rows for each of the two query subjects.

IBM Cognos 8 uses the cardinality of a relationship in the following ways:

to avoid double-counting fact data
to support loop joins that are common in star schema models
to optimize access to the underlying data source system
to identify query subjects that behave as facts or dimensions
Notations:

.1 (zero or one match)
.1 (exactly one match)
.n (zero or more matches)
.n (one or more matches)
The first part of the notation specifies the type of join for this relationship:

an inner join (1)
An inner join shows all matching rows from both objects.

an outer join (0)


How to specify outer join in FM?

Specify the cardinality as 0:nParameter maps in framework manager.

Parameter maps are similar to data source look-up tables. Each parameter map has two columns, one for the key and one for the value that the key represents. All parameter map keys must be unique so that Framework Manager can consistently retrieve the correct value.

Here is an example to explain the use of parameter maps.

An international company stores its product information in English and French. With the use of a parameter map and macros, employees can retrieve data that matches the information they require. Create a Language_lookup parameter map that contains the following:

Key    Value

en      EN

fr        FR

When you examine the SQL for the Product Line query subject, you see the following:

Select PRODUCT_LINE.PRODUCT_LINE_CODE,

#’PRODUCT_LINE.PRODUCT_LINE_’ + $Language_lookup{$runLocale}#

as Product_Line from [gosales].PRODUCT_LINE PRODUCT_LINE

The runLocale macro returns a locale setting that is used by the Language_lookup macro to specify a language value.

So, when an employee wants to view the product information in English, the parameter map key would be ‘en’ & vice-versa

Macros in cognos :

The Cognos engine understands the presence of a macro as it is written within a pair of hashes (#). It executes the macros first and puts the result back into report specification like a literal string replacement. We can use this to alter data items, filters, and slicers at run time.

Add data level security using CSVIdentityMap macro

A report shows the employee names by region and country. We need to implement data security in this report such that a user can see the records only for the country he belongs to. There are already User Groups defined on the Cognos server (in the directory) and users are made members of appropriate groups.

Drag the appropriate columns (Region, Country, and Employee name) on to the report from Employee by Region query subject
Go to Query Explorer and drag a new detail filter.
Define the filter as:
[Country] in (#CSVIdentityNameList(‘,’)#)

Here we are using a macro function called CSVIdentityNameList. This function returns a list of groups and roles that the user belongs to, along with the user’s account name



Regular Dimension

A regular dimension contains descriptive and business key information and organizes the Information in a hierarchy, from the highest level of granularity to the lowest. It usually has multiple levels and each level requires a key and a caption. If you do not have a single key for your level, it is Recommended that you create one in a calculation.

businessKey
Represents the key for the level. The level can be defined as unique if the business key of the level is sufficient to identify each set of data for a level.



Filters in framework manager are

Standalone filters
Embedded filters
You can add a standalone filter to one or more dimensions or query subjects to limit the data that the Query retrieves when the filtered dimension or query subject is used in a report, or you can include it in a package to make it available to your users. By moving a standalone filter or a shortcut to it into a folder, you can better organize the model objects.

Use an embedded filter when you want to use a filter with only one dimension or query subject. You can create an embedded filter when modifying a dimension, relational data source query subject, or model query subject. If you start with an embedded filter, you can later convert it into a standalone expression that you can apply to other dimensions or query subjects.



What is a usage Property in Framework Manager?

Usage property identifies the intended use for the data represented by each query item and it determines the aggregation rules of query items and calculations.

The different usage property settings are Identifier, Fact, Attribute, and Unknown.



What is the difference between Model and a Package?

A model is a logical view of the enterprise, where in a package is a subset of the model. This helps in reducing redundant copies of the database objects in several places.

For example, you can create one single model for the entire warehouse and choose to create multiple packages for each subject area which specific focus.



Define Name Space?

In security, a collection of user accounts and user groups from an authentication provider.

A  Name Space is a container, which holds the Query Subjects. A Name Space uniquely represents the Query Subjects



Explain about Data Source Query Subject?

Data Source query subjects contain SQL statements the directly reference data in a single data source. Frame work manager automatically creates a data source query subject for each table and view that you import into model.



When the default data source query subject is created?

During the initial metadata import operation, a default data source query subject is created for each object you select (for example table) and creates the default relationships between query subjects for you automatically.



What is meant by Governors?

To apply privileges and restrictions for a user class. Governor settings are used to set restrictions on queries by user class, such as

Sorting on non-indexed columns
Outer joins
Suppress duplicates
Cross-product queries
Retrieval of large text items
Number of records
Number of tables
Query execution time


What are the sources to create new Query subjects?

A new Query Subject can be created from the following sources.

Model (Query subjects & Query Items)
Data Sources (Tables and Columns)
Stored Procedure


What is a Loop?

A Loop is a closed path which is resulted due to joints.

A Loop causes performance degradation of query execution and wrong data will be displayed in the report.

A Loop can be resolved by creating the short cuts (Alias).



Determinants

The Situation

Let’s use the example of a date dimension table with day level grain. If all the fact tables join at the day level, the most detailed level, then you do not need determinants.  But as many of us know from experience, this is not always the case. Fact table are often aggregated or stored at different levels of granularity from a number of reasons.

The Problem

The trouble arises when you wish to join to the dimension table at a level that is not the lowest level. Consider a monthly forecast fact table which is at the month level of detail (1 row per month). A join to the month_id (e.g. 2009-12) would return 28 to 31 records (depending on the month) from the date dimension, and throw off the calculations. Determinants solve this problem.



In FM

The first three levels, Year, Quarter, Month, should be set to “group-by” as they do not define a unique row within the table and Framework Manager needs to be made aware that the values will need to be “Grouped” to this level. In other words, the SQL needs to “group by” a column or columns in order to uniquely identify a row for that level of detail (such as Month or Year).  The Day level (often called the leaf level) should be set to “Uniquely Identified”, as it does uniquely identify any row within the dimensional table



Stitch Query

A Manager asks a Report Developer to provide a report showing a comparison between Actual Sales and Forecast Sales for any years we have data.



But these measures come from two completely separate facts: the Forecast Fact and Sales Fact

To achieve this Cognos uses a conformed dimension(s) (dimensions that occur in both facts) as a common data point. Since the Manager has requested the attribute Year as the granularity, we will use the dimension which Year is a part of: Time. The example below shows how the Time dimension for each fact is used to bridge the two subject areas:

Cognos is performing a full outer join between the queries. This can also be done using measures from multiple facts. While seemingly simple to the user, behind the scenes Cognos does the heavy lifting and gets surgical with stitch queries.



COALESCE function:

The COALESCE function evaluates its parameters in order and returns the first one that is not NULL. The result is NULL if, and only if, all the arguments are NULL.

COALESCE(Body.Salary, 0)

Above example returns the value of the Salary field in the message if it exists, or 0 (zero) if that field does not exist.



Junk Dimension

A “junk” dimension is a collection of random transactional codes, flags and/or text attributes that are unrelated to any particular dimension.

e.g. Origin code (that indicates whether the trade was  initiated with a phone call or via the Web)



What is loop in framework manager ?

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



What are the types of SQL?

SQL is the industry language for creating, updating and querying relational data base management system. Types of SQL.

Cognos SQL
Native SQL
Pass-through SQL.


Define Cognos SQL?

By default Cognos Frame work manager uses Cognos SQL to create and edit Query subjects.

Advantages:

Can contain metadata from multiple data sources.
Have fewer database restrictions
Interact more effectively with Cognos applications.
Disadvantages:

You can not enter nonstandard SQL.



 Define Native SQL?

Native SQL is the SQL, the data source uses, such as Oracle SQL, but you cannot uses Native SQL in a query subject that references more than one data source in the project.

Advantages:

Performance is optimized across all related query subjects.
You can use SQL that is specific to your database.
Disadvantages:

You cannot use SQL that the data source does not support for sub queries.
The query subject may not work on different database type.


 Define Pass-Through SQL?

Pass-Through SQL lets you use native SQL without any of the restrictions the data source imposes on sub queries.

Advantages:

You can enter any SQL supported by the database.
Disadvantages:

There is no opportunity for Frame work manager to automatically optimize performance. The SQL may not work on a different data source.


What are Query Processing Types?

There are two types of query processing.

Limited Local: The database server does as much of the SQL processing and Execution as possible. However, some reports or report sections use local SQL processing.
Database only: The database server does all the SQL processing and execution with the exception of tasks not supported by the database. An error appears if any reports of report sections require local SQL processing.

Read more at http://blog.preceptorstraining.com/framework-manager-interview-questions/#SPpDoyhIwGiFRp5Q.99

0 on: "Latest Cognos Framework Manager Interview Questions."