IBM COGNOS INTERVIEW QUESTIONS AND ASWERS

Various components in Cognos tool and their usage/functions

ACTIVITY	COMPONENT
Publishing, managing, and viewing content or administrative activities	IBM Cognos Connection
Interactive dashboards IBM Cognos	Business Insight
Simple reporting and data exploration IBMCognos with dashboards	Business Insight Advanced
Ad hoc querying IBM Cognos	Query Studio
Managed reporting IBM Cognos	Report Studio
Event management and alerting IBM Cognos	Event Studio
Score carding and metrics IBM Cognos	Metric Studio
Analyzing your business IBM Cognos	Analysis Studio
Working with IBM Cognos BI content in Microsoft Office	IBM Cognos for Microsoft Office

Why model is created and published in FM as the data is already available in Warehouse?

Because stored data is typically designed for storage and not for reporting, a data modeler uses

Framework Manager to create metadata models--model structures, adds to, and manages data

in ways that make sense to business users

Difference between Group By and Order BY?

group by: This clause is used to group the records of a table based on their common properties and then we apply group functions such as sum(), max(), min(), count() on the groups.

order by: This clause is used to sort ( either ascending or descending ) the records based on a field or column of the table

Advantages of grouping:

We can apply group functions such as sum(), max(), min(), count()  on  groups  to find total salary being drawn by each and every group, we can find maximum salary for each group, we can find number of employees have been in each and every group, etc..

For example let us try to find total salary being drawn by each and every departments..

Do's and Dont's:

1) All the columns that have been written after "select" clause must appear in group by clause

Ex:  select department, job, sum (sal) from employee group by department;

the above query is wrong, because the column "job" was not included after group by clause. the correct is...

       Select department, job, sum(sal)  from employee group by department,job;

2) Whatever the column we use in group function that needs not to appear in group by clause. In the above   query the column "sal"  has been used in sum( ) function. Here, the column "sal" is not necessary to be appeared in the group by clause.

EXPLANATION ( order by )

order by clause can only be used to sort the records based on values of a column and we cant apply any group functions. There are two types of sortings, 1) Ascending  2) Descending

Ascending: This is the default order of order by clause. This method arranges elements in A to Z(0-9) order

select * from employee order by ename;

Descending : This method arranges elements in Z to A (9 to 0) order.

select * from employee order by ename desc;

Difference between Group and Role in Cognos for Users?

The only technical difference between a group and a role is this: A role can contain users, groups, and other roles while a group can contain only users and other groups. Other than that difference, groups and roles can behave the same. To keep life easier, we suggest you follow this rule of thumb: Use groups to define what Cognos 10 content a set of users can access; use roles to define what capabilities a set of users has. By following this rule, you can use groups and roles to set default functionality. As an example, if you are in the Sales Managers group and you have not been assigned a role, you cannot perform any actions in that group, because roles define what you can do. While in the Sales Manager group, if you are also assigned the Consumers role, you can do anything allowed by the Consumers role for any object for which the Sales Managers group has access permissions.

Once security has been applied and you have been assigned a role but not a group, you can perform the actions permitted by the role and will have limited access to the application. If you have been assigned the Authors role, for example, but you have not been placed in a group, you will be unable to access a reporting source and, even if you could, you would be unable to save the report to a global folder. You could save reports to My Folder. But because no one else can see that folder, the organization would not benefit from reports authored by you.

NOTE   Membership in groups or roles is optional; however, you should use them. Otherwise, administering permissions and capabilities for each user quickly gets out of hand and maintenance becomes a nightmare

Variables and conditional functionality in Cognos report studio

Conditional formatting:

·        Conditional formatting and Conditional Style are same. By defining either string/Boolean variable, we assign this to the Style Variable under properties section. Used for applying color/size and overflow etc.

·        Conditional formatting will change some format based on the data being checked.
Conditional style is ONE WAY to use conditional formatting - result being that STYLE is changed (rather than text)

Conditional Block:

·        We define a variable (string/Boolean variable) and assign the same to block variable under properties section.
To render an object (table/list/crosstab/repeater/prompt buttons and so on...) we use conditional block and block variable.
For rendering pages (report or prompt pages), we define a variable (string/Boolean variable) and assign the same to Render Variable under properties section.

·        Render variable can be set on more than just pages... Graphs, Crosstabs, Lists, etc... can also be conditionally rendered

·        Conditional block checks a condition of the data to decide whether or not to render an entire block.
Render variable checks some data to decide whether an object should show up (render).

Difference between report Studio and Query Studio?

Query studio is lightweight ad hoc web report writer that allows semi-power users to build ad-hoc reports
Users can sort/filter change font size, do some grouping and aggregation... and save their reports
Report studio which is the enterprise report development tool which can develop pixel perfect reports that have a high degree of interactivity complex condition/conditional layout processing.Report studio can edit query studio reports as well. So sometimes users save their Query studio reports but ask for developers to tweak them to get desired appearance/functionality

Both applications generate XML files that are stored in the Cognos Repository.Query studio is a subset (in feature) of Report Studio. It is mainly used for Ad-hoc queries.

What is the difference between parameter and variable in report studio

Parameters generally act as a filter to a report. Depending on the user selection the report is been filtered and is provided to the user. They can be used to display what value user has selected or what data the user wants to see.

Whereas Variables are used to conditionally format or conditionally hide/show the object in the report. They can’t be used to filter the data in the report.

What are content store and content managers in Cognos?
Content Store: It is Cognos Database.
Content Manager: It is the application which controls the Content Store through jdbc connections to CS.

What is Scrubbing at Project level?

According to the report requirements derive the new Items with the help of existing Query Items. This process is known as Scrubbing at Project level.

This is the most used join in the SQL. This join returns only those records/rows that match/exists in both the database tables.

Whar are the differences between inner join and equi join?

Inner Join Example

SELECT * FROM tblEmp JOIN tblDept

ON tblEmp.DeptID = tblDept.DeptID;

Equi Join

Equi join is a special type of join in which we use only equality operator. Hence, when you make a query for join using equality operator then that join query comes under Equi join.

Equi Join Example

SELECT * FROM tblEmp JOIN tblDept

ON tblEmp.DeptID = tblDept.DeptID;

--Using Clause is not supported by SQL Server

--Oracle and MySQL Query

SELECT * FROM tblEmp INNER JOIN tblDept USING(DeptID)

Note

1. Inner join can have equality (=) and other operators (like <,>,<>) in the join condition.

2. Equi join only have equality (=) operator in the join condition.

3. Equi join can be an Inner join, Left Outer join, Right Outer join

4. The USING clause is not supported by SQL Server and Sybase. This clause is supported by Oracle and MySQL.

what is reprompt?

If u want to select multi items and we dont need finish button we use reprompt button. In Cascading Prompt we have two things -: 1.) Single Select Auto Submit. 2.) Multi Select and Reprompt Button.

Where do you edit cognos sql in report studio? 

1. Pause the pointer over the query explorer button and click the query you want. 2. In the Properties pane, double-click the Generated SQL/MDX property. 3. Click Convert. 4. Make the changes you want. If you are working with MDX, you must be aware of the MDX syntax that Report Studio supports. 5. Click Validate to check for errors. 6. Click OK

What is snapshot ? 

A SnapShot is a Permanent Local Copy Of The Report Data a Sanpshot is Static Data Source it is saved with .imr File it is suitable for Disconnected Network



Can we use 2 packages in report studio at a time is there any alternative for this ?
how do you configure link between fwm and database (source).if you created fwm where it will be? and if you create reports where it is stored? 2.what is spic shot in fwm? 

1. To create link between FWM and DB(Source) Open FM-->File-->New Project-->it will ask you to select DATA Sources..we can select new DS to create or existing to connect.If you select 'new' it will ask for type of ds (Oracle,sybase) and click next then give Connection string and userid/pw .then test for connection.if the connection is succeeded.connection is established between model and DS. 2. Once model is created..it will be stored under projects in location where C8 is installed. (eg) if your modelname is 'sample' then it will be stored as 'sample.cpf'..C:\Program files\C8\samples.. 3. I you create reports..it will be stored as specification in .xml file.


How will you migrate your reports to QA environment?

1. Go to Conos connection 2. Then click on Tools. Select Content administration from the drop down menu. 3. Click on 'new export' icon in content administration page. 4. Specify a name and follow the steps


Query Processing types:

The query processing property for data sources determines whether SQL processing is performed by the database server or if it is processed locally. For relational metadata, you can improve performance by selecting the right type of query processing.

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. An error appears if any reports or report sections require local SQL processing.

Although the database server can usually run the SQL and run reports much faster, local processing is sometimes necessary. For example, choose limited local processing if you want to create cross database joins, or if you want your users to use unsupported SQL99 functions.

Some complex queries require limited local processing, such as a query that must generate an At clause to avoid double-counting. In this case, the query automatically uses limited local processing even if the package was published with database only processing.

BEST PRACTICES: 

Please find the below explanations and methods of usage of some of the properties we have been using as "Best Practices" while designing the reports.

Before going in to the details we need to make sure that whatever properties we have been setting here at report studio level will bypass the settings that have been made at Framework and DB level(*some instance may differ)

So we need to work Closely with Admin and Modeler(FM level) while setting these properties at Report level along with that we need to consider the underlying warehouse/Data Mart which may be from Heterogeneous or homogeneous data sources

I have only taken some of the critical properties which have an overall impact and should be considered for usage only after analyzing the complete cycle of query generation through Cognos Query engine.

Properties consider for the discussion:

1. Use SQL parameters
2. Use Local Cache
3. Execution Method
4. Processing

Execution Method:

By default all queries within a single report are run one by one. It is possible to run either prompt or data queries concurrently. The report server uses the concept of helpers to manage how many queries can be executed concurrently within the report server. Setting this to 10 for example means that an additional 10 queries may be executed concurrently for the entire report server instance.

The report server advanced property RSVP.CONCURRENTQUERY.NUMHELPERSPERPROCESS is used to set the number of helpers available within the server. The default is zero. If this is not set then no concurrent queries may be run.

You must also configure how many helpers are available to each report using the report server advanced property RSVP.CONCURRENTQUERY.MAXNUMHELPERSPERREPORT. The default is one which allows any report to execute 1 query at a time - i.e. you must set this to at least 2 to allow parallel queries to run.

Important Note:

For interactive reports(On Demand Reports in our case), if concurrent query execution is enabled, some queries may be unnecessarily executed because the results are not used. For example, all the queries for a multi-page report may execute with at least one query on each page, but the user may view only the first page. If you do not want to use resources for results that are not used in interactive reports, disable this parameter. Authored prompt pages are not interactive output and are not affected by this setting.

Hence this property is best suited for batch execution

The default value for this parameter is false, meaning disabled.

So before setting this property make sure everything at Cognos Admin level is configured or else this will be an overhead to the query engine

Processing:

The query processing property for data sources determines whether SQL processing is performed by the database server or if it is processed locally. For relational metadata, you can improve performance by selecting the right type of query processing.

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. An error displays if any reports or report sections require local SQL processing.

Instances or cases when that encourages us to use the Processing to Limited

1. When you are running the report or individual queries you may end up in the following errors

• UDA-SQL-0458 PREPARE failed because the query requires local processing of the data. The option to allow local processing has not been enabled.
• UDA-SQL-0475 The OLAP function 'sum' contains a clause (AT or PREFILTER) that is not supported by the database. This operation requires local processing of the data.

These errors notify us to set the query processing types to Limited to Local so that we can have the query engine to take care of some of the actions.

2. Using functions or operations which are not supported by the data source

3. Joining queries from separate databases. For example, choose limited local processing if you want to create cross database joins, or if you want your users to use unsupported SQL functions.

4. Some complex queries require limited local processing, such as a query that must generate an At clause to avoid double-counting.(Dimensional/Cube reporting)

Impact of Local Processing:

1. Performing operations at the Cognos server means that you no longer have database indexes or  materialization of views etc to help speed up the query processing.
2. In order to perform most operations with local processing you will have to retrieve a larger, more detailed, result set from the data source. Fetching large result sets will slow things down but it is the processing of these large result sets at the Cognos server which will really impact performance.

Please find the document for concurrent query execution example in the attachment. 

Use SQL Parameters:

• By default, Query Engine will attempt to utilize the support of parameterized SQL and data type conversion (CAST) if they are supported.
•  If the required type conversion is not supported this operation will have to be performed locally on the Cognos BI servers. If the database vendor does not support parameter markers Query Engine will automatically generate literal values.
• A vendor may support parameter markers and the data type conversions but internally plan the query in a manner which is less optimal than one where literal values are used instead.
• Adding parameters (prompts) in query items and filters may cause a query to execute more slowly depending on the capabilities of a database vendor.
• As per my Previous experience this property is very much useful when dealing with SAP BW Data sources.

Use Local Cache:

When you run a report, the query request is sent to the database and the result set is returned. After the initial report execution, you may decide to make changes to the report. Often, the report can be created without querying the database again. To take advantage of this, you should turn the query reuse feature on.

When query reuse is turned on and you run a report for the first time, the query is stored in the cache of your current session and reused the next time you run the report. The queries are kept in the cache for each user. The cache is cleared when the report consumer exits the reporting tool and returns to the portal or when the report server times out the session, typically after five minutes of inactivity.

The first time the report is run and the cache is created, the response time may be slightly negatively impacted. The performance improvement is realized by the report consumer on each subsequent report execution, when the response time is improved by as much as 80%. This performance improvement occurs because the report does not have to re-query the database. In addition to this, reduced queries to the database yields improved overall system performance, which positively impacts all users.

Important points to be noted:

1. This is very much useful in reports using Master detail relationship as the master query is executed once and the detailed queries can take advantage of the local cache
2. If most report consumers run reports interactively but run them only once, you may not experience a high level of performance improvement by caching data.
   Note: Regardless of the query reuse settings, reports that are run in batch do not cache data.
3. The size of the cache may impact scalability. For example, if a report has a large result set, the cache will also be large. This should be taken into account when sizing and configuring your server environment.
4. By default, with the dynamic query mode, all data sources use a local cache. The dynamic query mode offers improved caching for OLAP over relational, SAP BW, and Oracle Essbase data sources. When the dynamic query mode is enabled on a package, the cache is built on demand as users build and run reports, perform analyses, execute reports in batch mode, and so on.
5. Also please check on the session caching along with this for any further issues.

 

COGNOS 10 INTERVIEW QUESTIONS

Hello every one please do prepare the below questions before appearing any COGNOS interview 

There is a probability of 70% questions coming from the below 

Will post the answers for each question soon .Also please contribute for addition of new questions under each category as comments to this post

Will also post different scenario based questions and transformer related questions soon

Frame work manager interview questions

1. What is a Model?
2. What is a Project?
3. What is a Package?
4. Tell me the cycle from project creation to publishing the model in FM
5. What is query subject and Query item
6. What are the types of Query subjects in Cognos and Explain
7. Explain different layers in FM
8. Types of model's you have used in your FM(dimensional,relational)
9. What is Cardinality?
10. What are joins and types of joins
11. Cardinality for different types of joins
12. What are loops in FM model and how to resolve loops
13. types of shortcuts(reference and alias)
14. what are filters in FM(STAND ALONE AND EMBEDDED)
15. types of security(package ,object, data, row level)
16. governor settings in FM
17. Determinants and what is its significance
18. What are usage properties
19. What is stitched query
20. What are Session parameters
21. Explain about Parameter maps
22. What are Macros
23. What is Model Advisor
24. What are Scope relations in dimensional model
25. Explain Dimensional functions such as set,slice,filter
26. Explain Dimensional functions lead lag parent child
27. Explain Dynamic query mode
28. Explain Model design accelerator
29. Difference between CQM  vs DQM
30. New functionalities added to FM in Cognos 10 from Cognos 8
31. Multiuser models (linking segmenting branching etc.)
32. Types of Schemas used in your project
33. Tools tab properties like show object dependencies, model reports etc.
34. Differences between cubes(OLAP) and DMR
35. Scope relationship in DMR
36. What is MUN and tree prompt
37. What is drill up drill down slice and dice
38. What are the types of hierarchies? In dimensional model
39. What are query processing types(limited to local limited to DB)

Report studio interview questions

1. Explain Complex reports you have worked on
2. What is drill through and how many types you can achieve(pass by dataitem,pass by parameter)
3. What is master detail why is it used
4. Difference between master detail and drill through
5. Types of filters in Report studio and explain(detail,summary)
6. What is before aggregation and after aggregation?
7. What is aggregation and rollup aggregation?
8. What are conditional blocks?
9. Various types of variables in report studio
10. Explain the components like style variable render variable block variable
11. What is text source variable
12. What is repeater table and why are repeaters used
13. What is singleton and what is its significance
14. What is conditional formatting how you implemented it in your project?
15. Which chart you can plot two measures (bubble)
16. Types of prompts
17. Can you convert crosstab to list and vice versa
18. How can you increase performance of the report
19. What is cascaded prompt
20. What is re prompt and auto submit
21. Explain Display value vs use values and its significance
22. What are static choices in prompts and why are they used
23. What are page breaks and page sets in report
24. What is group span
25. What is difference between group by and order by
26. How can you display two measures in a cross tab report
27. what are the ways in which can you establish a relation between queries in your report(join union)
28. what is union of two queries how can you achieve
29. what is MDX,SQL
30. what is Bursting have you implemented it in your project
31. difference between bursting and scheduling
32. what is CAMID
33. How can you limit rows in report where will you change the property
34. What are active reports
35. What are the active report components you have used
36. What is a variable and event in active report
37. Ancestor button in report studio
38. What is box type property and why it is used
39. What are data containers
40. What is data format how did you use this in your reports
41. Row level security?
42. What are query processing types(limited to local limited to DB)
43. What is source type property
44. Snapshot  and report view differences
45. What is hyperlink and why it is used in reports
46. how to get the all values from a prompt using 'ALL'
47. Can u drill through report from report studio to analysis studio and vice versa
48. Can u Edit Cognos SQL in Report and how
49. Difference between count and running count
50. How to test reports
51. What is layout calculation and its significance
52. How can I change reports to another package
53. Can we use 2 packages in report studio at a time is there any alternative for this
54. what is the difference between group and association
55. Can you apply grouping on crosstab?
56. How will you migrate your reports to QA environment?
57. diff b/w layout calculation and query calculation
58. What is meant by Thumbnails on Cognos?
59. What is level span? How it is used in cognos? What is the main advantage of level span. 

SQL level interview questions for Cognos

1. What are Cognos SQL, native SQL, and Pass through SQL?
2. What is primary key and foreign key
3. What is a sid (surrogate key)?
4. What are types of joins
5. What is sub query and types of sub-quires
6. What is difference between join and sub query
7. What is difference between Where and Having
8. What is difference between group by and order by
9. What is the use of With clause
10. Real time examples for all types of joins
11. Difference between equi join and inner join
12. What is difference between union and union all
13. What is difference between union and join
14. How will you remove duplicates from a table
15. What is self-join and real time significance with example
16. How do you get the latest updated record from the table which is not having any timestamp
17. Nth highest salary of an employee from top and from bottom
18. What is difference b between MDX and SQL
19. What is an index and how many types of indexes you know and significance of indexing
20. what is the difference between group and association

Warehouse level Cognos interview questions

1. Types of schemas
2. What is OLAP and What are the types of OLAPs?
3. What is OLTP and difference between OLAP and OLTP
4. What is relational model and Dimensional model
5. What are facts and dimensions
6. What are regular dimensions and measure dimensions
7. Types of FACT’s
8. Types of Dimensions
9. What best schema star or snow flake and why
10. What is junk dimension,factless fact
11. What is role playing dimension give an example
12. What is Data warehouse and its significance
13. What is the difference between database and data warehouse
14. What is Data mart
15. What is metadata?
16. What is materialized view?
17. What is schema?

Very Important questions and Miscellaneous questions asked in every interview

1. What is Cognos Architecture
2. What is content Store and Content manager
3. How can you increase performance of report at Report studio level
4. What is ETL and what is the ETL tool used in your project
5. What is complete process going on in background  when you run report
6. How can you enhance the performance of a report?(at all levels)
7. How can you tune performance of report at FM level?
8. Best practices of FM
9. Best practices to be followed while designing the report
10. Difference between Business insight and Business insight Advanced
11. Difference between Query studio and Report studio
12. What is metric studio and explain its significance
13. What is analysis studio and what is context filter in analysis Studio.
14. Difference between Group and Role in Cognos for Users