Questions

 1.Tell me about urself?

2. Tell me about your WORK ?

3. how you get task ? who assign you ?

4. what is the size of ur team?

5. what is d size of etl Team?

6. have u experienced in performance tuning?

7. what is difference between union and union all

8. Sql query to identify duplicates?

9.when running a report U are seeing null values in list ..how will backtrack ?

10.Practical scenario given for which the solution is Applying Grouping and total (asked in details explanations)

11.Getting wrong data in report.but wen run in sql it is giving correct data..how will u backtrack

12.Difference detail filter between summary filter?

13. what is the difference between render variable and style variable ?

14. Performance tuning in report level, fm level, db level.

15. how to improve performance using indexing

Interviewer from Hexaware

1. Tell me about yourself ?

2. What are the different types of securities we use?

3. In FM suppose query subject A and B are connected and B and C are connected, and I need to connect A and C in which way it can be achieved?

4. Do u have ETL KNOWLEDGE?

5. What are the different layers we create while creating a package?

6. Suppose we need a filter in all reports we create where shall we place the filter?

 6 a. for the above question I said we can place the filter in Business layer in FM as embedded filter. Then the question was why it can’t be placed in Database layer instead of Business layer

 7 When I run a report to fetch data it will take some 2-3 minutes. But If I draft a sql query and run it in SQL server it will take 30 seconds. Why is this so?

8 Have you involved in creating models or someone creates and u use it ?

9 Have you involved in Performance tuning of reports?

10 When we select a value in one prompt it should reflect in another prompt. How we achieve it?

11 What is a style variable ?

12 What is a conditional variable ? What are the types of conditional variables?

13 What is conditional block?

14 What is conditional styling ?

15 What are the sqls cognos generate and what is the difference between them?

16 What is Block variable?

17 What are the functions in Cognos. Name few?

18 What are the types of prompts/

19 What are types of outputs that are available in report studio?

20 Name some properties of query?

21 You have interaction with client.. suppose u see mismatch between what client said and business requirement ?what will u do ?

22 SQL – write query - Practical scenario of grouping employee – total salary

 23 SQL – write query- Joins – all data from Table A and B must be fetched and only the matching row from C must be fetched.

------------------------------------
1)   If you have a report with list and cross tab in the same page. How do yourestrict one user can see only the list and the other user can see both?
Ans:   Create a variable to check which user is logged in uning the session parameter (eg. account.defaultName) and then assign this variable to either conditional block or use it as render variable for the Cross tab.

2)  There is a prompt page having options for ascending and descending, based on the prompt value selected , the report page generates the list column value sorted in either ascending or descending:

Ans: 1. Select the List Report you want to display
         2. Goto Query Explorer, Click on Query 1, drag Data item into the Query 1.
         3. Drag & drop value promt. Add values (1,2,3)
         4. Give expression case ?Parameter?
                   when C1 then 1
                   when C2 then 2 else 3
                   end
        5.Select Data item, if you want rename & Sort to Ascending..
        6. Now open page 1, Drag & drop Data item to your report.
        7. Run the report....

3) what will happen internally in Cognos if we run a report in report studio?
Ans : Once the user runs the report, 
User Browser-->web server & gateway--> BI server/application server(Dispatcher, CM) -->
dispatcher handles queries from gateway--> content store --> user browser
1.The request is passed to the Web Server and gateways (CGI, ISAPI). The Cognos Gateway applies encryption to the information received, adds necessary environment variables and authentication.

2.The request will then be transferred to the Cognos BI Server Or application server. The BI Server will include the dispatcher and content Manager. The dispatcher used to handle the queries received from Gateways.

3.This request will now be passed to Content store. It will get all the metadata information and will be passed back to the user browser. 


4)How to create a report in which user should have a choice to select between List and Crosstab report types using prompt?

Ans : 1. Create the List and Cross tab reports with required data.
2. Drag and drop a value prompt to the prompt page.
3. Name the parameter as selection, then click on finish.
4. High light the value prompt then select the Static choice property as
Value Display
       > List
       > Crosstab

5. Go to the report page and drag and drop a Conditional block to the report page.
6. In the properties pane select the Block variable and assign a string variable.
7. Define the string variable as ParamValue(?selection?) with values as 1, 2.
8. Then select the current block as 1 then select the whole List report (Ancestor ->List),then drag and drop in the Conditional Block.
9. Similarly select the Conditional Block and select the Block varialbe and assign value 2.Then select the current Block as 2.
10. Select the Whole Crosstab report(Ancestor->Crosstab), then drag and drop the cross tab report into the conditional block.
11. Run the report then you will have the required report option in the prompt page to select to run the required report as per your requirement 
----------------------------
1.What are CSS classes?Cascading Style Sheet(CSS) Classes are used to format reports quickly and consistently.
Local Class, Global Class.


2.What is the use of creating Sections?

• To show grouped information in separate report objects.

• When we run the report, separate sections appear for each value.

3.How do you reuse objects within the same report?

• Using Layout Component reference.

4.What is the use of View Page Structure?

• View the entire contents of a report page in a tree structure.
• Quickly move objects from one area of a page to another.
• Modify object properties.

 5.What are the types of variables?

• Boolean variable – use this when there are only two possible outcomes, where the values will be Yes or No.
• String variable – use this when there is more than one outcome, based on string values we specify
• Language Variable- use this when the values are different languages.

6.What is the use of Conditional Formatting?

• Used to change the display of a report based on conditions we specify.

Eg- Identify sales > 50,000 Rs

7.For which variables should we define condition?

• Boolean and String.

 8.How do we see the structure of a report?

• View Page Structure.

9.Why should we Unlock cells?

• To manipulate an object’s contents.
• Once they are unlocked, we can change the text and add objects inside existing objects.

10.How do we force page breaks in reports?

• Using Page sets.

11. Context filters are also called as ________ . 

Slicer Filters : they are used to quickly focus report on a particular view of the data.
they can be used as a prompts when the report is run.
it only removes the filtered data from the report.

-----------------------------------

16. Repeater and Repeater Table?

Repeater- repeat items across a single row without a particular structure.

Repeater Tables-repeat items in a table structure, such as mailing label information.

17. Parent and Peer data items?

Sales Territory is the Peer of Product Line.

Product line____

Sales Territory

Sales Territory is the Child of Product Line.

Product line____ Sales Territory

Sales Territory

18. Crosstab Node and Node members? Moving of Node & Member?

• When we add items to crosstabs, we create crosstab nodes and crosstab node members.
• The row and column edges of a crosstab are composed of sets of crosstab nodes.
• A crosstab node contains one crosstab node member, as well as any cross tab node members nested under it.
• Each crosstab node member refers to a data item.

19. What are Baselines and Markers?

Baselines are horizontal or vertical lines that cut through the chart to indicate divisions within the charted data.

Markers are notations along the axis of a chart that designate a point on the scale to help users analyze and understand the data.

20. What is Dashboard?

An interactive report which can contain different types of reports like gauge chart,column chart ,crosstab.

21. What is the use of a Filter?

A filter is used to narrow our focus and report on specific data.

22. What is the difference between Detail and Summary Filter?

Detail Filter-filter that applies to rows.

Summary Filter tab-filter that applies to groups.

23. What is the difference between Before and After Auto Aggregation?

Before Auto Aggregation – to filter on individual records.

After Auto Aggregation – to filter on summarized data.

24. How to create a filter when we don’t know the values in the data source or when we don’t know how the particular value?

Select Multiple Values

25. What is prompt?

Prompts ask the user to provide the value for the parameter.

26. What is parameter?

Parameters are placeholders that require a value to determine what data to report on.

27. Three ways to prompt a user for report specifications?

• Create a parameter for an item on the report.
• Add a prompt page to the report containing one or more prompt items-Build Prompt Page button on tool bar.
• Add a prompt item to a report.

28. Which are the types of prompt?

1. Prompt Button
2. Text Box Prompt
3. Value Prompt
4. Search & Select Prompt
5. Date Prompt
6. Time Prompt
7. Date & Time Prompt
8. Interval Prompt
9. Tree Prompt
10. Generated Prompt

29. What is a Generated Prompt?

Acts as a placeholder. It will display a suitable prompt type based on the nature of the query item on the generated report.

This prompt should be used when you are not sure of which type of prompt best suits for requirement.
For Example - If you add date and time value as the filter item in a generated Prompt, the prompt will automatically choose the 'Date and Time Prompt' at the run time.

30. Which prompt to use when there are multiple items to choose from but users are not completely familiar with the data values?

Select & Search Prompt

------------------

What are active reports in Cognos 10 BI ?

These are the disconnected and interactive reports developed for the employees, buisness parterners and business managers who are not connected to intranet and need anlaytical information for quick and better decision and actions.

IBM Cognos Active Report produces reports that are an extension of existing IBM Cognos Report Studio values and the IBM Cognos Platform. Users continue to benefit from the enterprise value of one version of the truth, and new interactive control types in IBM Cognos Report Studio serve as building blocks for creating active reports.You use active report controls to create the layout of an active report and to filter and sort data in the report.

------------------

Report Studio Question

Q: 1 In Report Studio, Based on the crosstab below, which of the following

is true?

A: Sales territory is a parent to Quality and Revenue.

B: Quality and Revenue have a parent-child relationship.

C: Order method is a peer of Retailer type.

D: Sales territory is a peer of Order method.

Correct:A

Q: 2 In Report Studio, what is true about a crosstab node?

A: It contains at least one nested crosstab node.

B: It contains at least one crosstab node member.

C: It contains more than one nested crosstab node.

D: It must contain more than one crosstab node member.

Correct:B

Q: 3 The sales department require a report that numerically compares the gross

profit generated for all products lines in each territory. In Report Studio,

which is the most appropriate report type?

A: List

B: Chart

C: Crosstab

D: Repeater

Correct:C

Q: 4 An author wants to apply formatting to a list report, In report Studio, the

author clicks a column, Clicks select Ancestor, and then clicks list Columns.

In this situation, which of the following is true?

A: Formatting all columns will also format aggregate rows in the list.

B: Formatting all columns overrides formatting foe an existing formatted column

in the list.

C: Formatting all columns affects all subsequent objects added to the report

page.

D: Formatting all columns ensures any new columns added to the list will have

the same Formatting.

Correct:D

Q: 5 In Report Studio what is the baseline?

A: A value in a list report that other values are compared against.

B: A border in a crosstab that separates measures from aggregate totals.

C: A constant value drawn on a chart to indicate major divisions in the data.

D: A break point that lets you keep part of a report stationary while you

scroll.

Corrcet:C

Q: 6 In Report Studio, an author wants to change the location of the legend in

the chart report. What property will accomplish this?

A: Auto Truncation

B: Axis Assignment

C: Absolute Position

D: Relative Alignment

Correct:C

Q: 7 The following report was created in Report Studio using a relational

package and shows revenue by sales rep, city and country. What will the

report show if the author creates a detail filter for Revenue > 500,000

and applies the filter AFTER auto aggregation?

A: Only reprs who generated total revenue greater than 500,000.

B: Only cities that generated total revenue greater than 500,000.

C: Only countries generated total revenue greater than 500,000.

D: Only reps who had individual sales transactions of greater than 500,000.

Correct:A

Q: 8 In Report Studio, an author is using a relationally modeled package

and wants to filter a report to only include three specific products. If the

author is not sure how to spell the product names. What is the fastest way

to add the product names to the filter expression?

A: Run the report with all product names and copy the product names.

B: Use the select multiple values feature to search for the product names.

C: Add the product names from the source tab in the Available components pane.

D: Add the product names from the data Items tab in the available components

pane.

Correct:B

Q: 9 An Author wants to create a filter on this report to only show Product line

totals larger than 1 billion.What calculation is used to create this filter?

A: Detail filter as: Total ([Revenue] for Product line)>1000000000

Application =After auto aggregation

B: Detail filter as: Total ([Revenue] for Product line)>1000000000

Scope = Product line

C: Summary filter as:[Revenue]>1000000000

Scope = Product line

D: Summary filter as:([Revenue] for Product line)>1000000000

Application =After auto aggregation

Correct:C

Q: 10 In Report Studio, which chart lets the report author plot a third measure?

A: Area

B: Bubble

C: Scatter

D: Progressive column

Correct:B

-------------------------------

COGNOS Certification Questions

1. What is the reason for creating a database Signon in Cognos 8?

2. How to move an entire Cognos 8 installation from one environment to another

3.Predefined roles in Cognos 8?

4. How to upgrade Reportnet report specifications and perform consistency check in Cognos Connection?

5. In Cognos Connection, what are the portlets used for Content handling ?

6. What might be a reason for copying a user profile?

7. What is a shortcut and what happens when the target is renamed?

8. What are the IBM Cognos 8 user permissions?

9. Mail server configuration?

10. How to set up email report distribution in Cognos?

11. What might be a reason for creating a report view by an administrator

12. What is the scope of a package based drill through?

13. How do the components in IBM Cognos 8 architecture communicate?

14. What are the architecture tiers in IBM Cognos 8?

15. IBM Cognos 8 services overview?

17. What are the supported application servers by Cognos 8

18. What is the definition of the Application tier?

19. How to ensure failover when configuring Content Manager?

20. Cognos Application Firewall configuration to access a report on a remote computer?

22. What are gateways in the Cognos Webserver tier?

23. How to disable anonymous access in Cognos 8?

24. Why content manager services must be started first in the distributed installation?

25. How does load balancing work in Cognos 8?

28. IBM Cognos 8 environment tuning and optimization tips?

30. Audit logging and audit reports in Cognos 8?

31. What logging levels are available in IBM Cognos 8?

34. What are Cognos 8 system metrics types?

41. Supported data sources in IBM Cognos 8?

51. Concurrent report query execution?

52. Integration with an existing web portal?

53. Definition and role of a cognos 8 dispatcher?

54. Predefined roles in Cognos 8?

55. How to schedule reports in Cognos 8?

Cognos BI Report Authoring Questions with Answers some more

1. What is Cognos Connection?

Web page that provides single access point to all corporate data available in C8 BI.

2. How to change the appearance of portal page?

*In the top right corner, click TOOLS, and select MY PREFERENCES.

3. What is report?

Specification that says which information to include in a report.

4. What will you do to run a report in different Format and Language?

Go to Cognos Connection.

From the list of reports, Click Run with Options.

5.How many schedules can be associated with a report or a JOB?

ONE

6.What will you do for multiple shedules for the same report?

Create multiple report views and create a schedule for each report.

7. What is a JOB?

Identifies a collection of reports, report views and other jobs.

8. Which are the ways in whch the steps in a JOB can be executed?

*all at once

*in sequence

9. Where will you view the Run History of a report?

Schedule Management Tool

10. Where to RENAME the report?

Go to Cognos Connection, Click on Actions -> More -> Set Properties ->Name

11. How will we create a report shortcut?

Under Reports, Click MORE and then click on CREATE A SHORTCUT TO THIS ENTRY.

12. What is Report Studio?

*A web based report authoring tool.

* Lets you create BI reports that analyze corporate data according to specific information needs.

* lets you format, present, and distribute your corporate data using many different methods.

13.How many tabs are there in the Insertable Object Pane? What is the difference?

3 tabs

1. Source data contained in the package. We can add any of these data to our report.

2. Data items that are currently in the report

3. Tool box objects

14. Example of Level Spanning?

Level span by city- When the city name changes, Country name repeats.

15. Two ways of grouping the data items?

Click on Group/Ungroup on the tool bar.

Click on Advanced Grouping to use the Grouping and Sorting dialog box.

Cognos HotFile

            A HotFile is a flat sequential file. It acts as a separate local data table that can be added to your
catalog or used in a report as if it were a database table. Both the data and the definitions are
stored in the HotFile.


           Once created, a HotFile is independent of the database columns from which it was created, and
you can use it in any report or catalog. You can join a HotFile to database tables, reports, or other
HotFiles.

Limitations of HotFiles

Below are some limitations surrounding the use of HotFiles:

• It exist on the client side.
• They are not indexed, and thats why, joins may takes longer execution time. Also,Impromptu must use Sort/Merge and sequential scan to filter.
• HotFile size is limited. However, it should not be used to store very large query results as they could slow computer performance.

What are Dimensions?

"A dimension is a structure that categorizes data in order to enable

 users to answer business questions." 

Commonly used dimensions are customers, products, and time. For example, each sales channel of a clothing retailer might gather and store data regarding sales and reclamations of their Cloth assortment. The retail chain management can build a data warehouse to analyze the sales of its products across all stores over time and help answer questions such as:

1. What is the effect of promoting one product on the sale of a related product that is not promoted?
2. What are the sales of a product before and after a promotion?
3. How does a promotion affect the various distribution channels?

The data in the retailer's data warehouse system has two important components: dimensions and facts. 

The dimensions are products, customers, promotions, channels, and time. 

One approach for identifying your dimensions is to review your reference tables, such as a product table that contains everything about a product, or a promotion table containing all information about promotions. 

The facts are sales (units sold) and profits. 

A data warehouse contains facts about the sales of each product at on a daily basis.

A typical relational implementation for such a data warehouse is a Star Schema. The fact information is stored in the so-calledfact table, whereas the dimensional information is stored in the so-called dimension tables. In our example, each sales transaction record is uniquely defined as for each customer, for each product, for each sales channel, for each promotion, and for each day (time).

In Oracle9i, the dimensional information itself is stored in a dimension table. In addition, the database object dimension helps to organize and group dimensional information into hierarchies. This represents natural 1:n relationships between columns or column groups (the levels of a hierarchy) that cannot be represented with constraint conditions. Going up a level in the hierarchy is called rolling up the data and going down a level in the hierarchy is called drilling down the data. In the retailer example:

• Within the time dimension, months roll up to quarters, quarters roll up to years, and years roll up to all years.
• Within the product dimension, products roll up to subcategories, subcategories roll up to categories, and categories roll up to all products.
• Within the customer dimension, customers roll up to city. Then cities rolls up to state. Then states roll up tocountry. Then countries roll up to subregion. Finally, subregions roll up to region, as shown in Figure.

Fig- Sample Rollup for a Customer Dimension

Data analysis typically starts at higher levels in the dimensional hierarchy and gradually drills down if the situation warrants such analysis.

Dimensions do not have to be defined, but spending time creating them can yield significant benefits, because they help query rewrite perform more complex types of rewrite. They are mandatory if you use the Summary Advisor (a GUI tool for materialized view management) to recommend which materialized views to create, drop, or retain.

Data Warehouse Architectures

Data warehouses and their architectures vary depending upon the specifics of an organization's situation. Three common architectures are:

• Data Warehouse Architecture (Basic)
• Data Warehouse Architecture (with a Staging Area)
• Data Warehouse Architecture (with a Staging Area and Data Marts)

Data Warehouse Architecture (Basic)

Figure 1-2 shows a simple architecture for a data warehouse. End users directly access data derived from several source systems through the data warehouse.

Figure 1-2 Architecture of a Data Warehouse

In Figure 1-2, the metadata and raw data of a traditional OLTP system is present, as is an additional type of data, summary data. Summaries are very valuable in data warehouses because they pre-compute long operations in advance. For example, a typical data warehouse query is to retrieve something like August sales. A summary in Oracle is called a materialized view.

Data Warehouse Architecture (with a Staging Area)

In Figure 1-2, you need to clean and process your operational data before putting it into the warehouse. You can do this programmatically, although most data warehouses use a staging area instead. A staging area simplifies building summaries and general warehouse management. Figure 1-3 illustrates this typical architecture.

Figure 1-3 Architecture of a Data Warehouse with a Staging Area

Data Warehouse Architecture (with a Staging Area and Data Marts)

Although the architecture in Figure 1-3 is quite common, you may want to customize your warehouse's architecture for different groups within your organization. You can do this by adding data marts, which are systems designed for a particular line of business. Figure 1-4 illustrates an example where purchasing, sales, and inventories are separated. In this example, a financial analyst might want to analyze historical data for purchases and sales.

Figure 1-4 Architecture of a Data Warehouse with a Staging Area and Data Marts

 

-------

Cognos 8 Report Studio - Rotate Text

Introduction

If you want to Rotate some text in Cognos 8 Report Studio . i found very helpful data and here i am sharing with you. At the end of steps by step guide you will get the report with text rotated in 90 degrees and 270 degrees.

Steps by Step guide: 

1. Click on the Report Studio link from cognos connection and open Report Studio with Go Sales and Retailer package. 
2. Select a Blank Report from file menu or an icon. 
3. From the Toolbox, insert a Table with 3 columns and 1 row. 
4. Insert a List with Model Data Items in the 2nd column of Table on Layout 
5. Insert a HTML Item in to the 1st column of Table from tool box. 
6. Similarly insert a second HTML Item in to the 3rd column of the Table. 
7. Right-click on the first HTML Item and select Edit. 
8. Copy-Paste the below HTML code 

<html> 
<head> 
<script language=javascript>
</script>
</head> 
<body> 
<div id="mydiv" style="height:333; width:333;filter='progid:DXImageTransform.Microsoft.BasicImage(rotation=3)'">

<center>

 <font size=11> Cognos 8 BI</font>

</center>

 </div>

</body> 
</html> 

9. Change the Value of rotation=3 by rotation=1 for 90 degree shift. 
10. Save and run the report. 

after running the report u will get "Cognos 8 BI" rotated at 90 and 270 degrees.