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.