Report Studio QA-2

Q. What is Condition Explorer in Report Studio?

Ø  Condition Explorer allows you to manage the variables that are being used in the report.

Q. What is a Page set in Cognos Report Studio?

Ø  The set of pages to render according to a grouping structure.

Q. What are classes in Cognos Report Studio?

Ø  Classes provide a default style to be applied to the objects.

Q. How you do “Conditional Highlighting” on a report in Report Studio?

Ø  The steps to add conditional highlighting to a report are listed below:

a)     Select the list column body for which you want to set conditional highlighting.

b)    From the Style toolbar, click Conditional Styles.

c)     In the New Conditional Style list, select Advanced Conditional Style.

d)    Type a name for the style. Click on New Advanced Condition.

e)     In the Expression Definition pane, specify terms for the expression.

f)     Select a style from the Style list. Edit to define a custom style.

g)    Click Ok and run the report.

  Q. How do you convert a list to cross-tab in Cognos Report Studio?

Ø  To convert a list to a cross-tab, follow the listed steps:

            1. Click the columns that you want to appear as columns or nested columns in the cross-tab.

            2. From the structure menu, click “Pivot List to Crosstab”.

Q. How can you hide an object in Report Studio?

Ø  Set its “Box Type” property to “None”.

Q. How do you hide the rows that have null values?

Ø  You can hide the rows having null values by applying conditional formatting as when you find null values in rows make the box type none in the properties.

Q. How can you hide a column in Report Studio?

Ø  For Crosstab reports –

a)     Group on the column and create aggregation to have the column total in the bottom row.

b)    Click the Unlock button on Report studio toolbar.

c)     Now select the “text item” and delete it.

d)    Select the empty crosstab node after deleting the text. Change its padding to 0 pixels and font to 0.001 pt size.

Q. How can you Specify what appears when no data is available in a report?

Ø  The steps  are as listed below:

a)     Select the data container in the report.

b)    From the properties pane, set the “No Data Contents” property to Yes.

c)     No data contents tab is displayed, which can be used to drop “Text item” or “Layout Calculation” to be displayed in case of no data in report.

Cognos Report Studio QA

Q. What are the different types of reports in report Studio?

Ø  The different types of reports available in Report Studio are listed as:

            1. List

            2. Crosstab

            3. Chart

            4. Map

            5. Repeater

Q. What are Prompts in Cognos Report Studio? List the different type of prompts?

Ø  Prompts act as questions that help users to customize the information in a report.

Different type of prompts can be listed as:

a)    Text Box Prompt – Retrieves data based on a value that user’s type.

b)    Value Prompt – Retrieves data based on values that user’s select from a list.

c)    Search & Select Prompt – Retrieves values based on search criteria that users specify.

d)    Date Prompt – Retrieves data based on a date that users select.

e)    Time Prompt – Retrieves data based on a time that users select.

f)     Date & Time Prompt – Retrieves data based on a date & time that users select.

g)    Interval Prompt – Retrieves data based on a time interval that users specify.

h)    Tree Prompt – Retrieves data based on values that users select from a list (Returns a set of members from hierarchy). It is used when working with dimensional data.

i)      Generated Prompt – Selects a prompt control based on the data type of the data item.

Q. What are Cascading prompts?

Ø  Using cascading prompts, the values in one prompt are driven by what is selected in another prompt.

Q. What are the different types of filters available in Report Studio?

Ø  Report Studio – Report studio provides three types of filtering:

a)     Detail filter – Detail filters are used to filter detail level values within a report. They work at the lowest level of granularity in the selected objects.

Example: We can apply a detail filter on “Sales Month” to extract all transactions which occurred within the specified month.

b)    Summary filter – Summary filters are used to filter summary level values within a report.

Example: We can apply a summary filter to get the highest sold product in a sales region.

We would need to group the “Region” and “Product” fields beforehand.

c)     Slicers – Slicers are used with dimensional models. A slicer filter affects the cell value, not the row or column edges. Slicers are useful if you want some data in a report to be hidden and other data to be visible.

            Example: We can apply a slicer if we want a report displaying all “Product” names but sales data only                    for a few and not all products.  

Q. What are the different types of variables in Report Studio?

Ø  The different variables are :

a)     Boolean Variable

b)    String Variable

c)     Report Language Variable

Q. What are classes in Report Studio?

Ø  Classes in Report Studio provide a default style to be applied to the objects.

Q. What is Condition Explorer in Report Studio?

Ø  Condition Explorer allows you to manage the variables that are being used in the report.

Q. What’s the difference between Drill-through report & Master-detail report?

Ø  Drill-through Report – Links 2 separate reports containing related information. (Target & Source report). Steps to create are listed below:

a)     Create a target report with detail filter that uses the parameter on which you want to filter when drilling through from the source report. Save the report.

b)    Create a source report that contains the item you want to use for drill-through.

c)     Select the item to be used as the drill-through and click on Drill-Through definitions button on the standard toolbar.

d)    Click new drill through definition button. Select the target report and fill the required options.

e)     Click Edit to display the parameters. Select method as “Pass Data item value”. Select value from the source report.

f)     Click Ok and then run the report.

Ø  Master Detail Report – Links information between two data containers (eg. List, crosstab, chart) within a report. Using master-detail, you can use a single report to display information from two reports. Steps to create are listed below:

a)     Create a new report having two data containers E.g. Crosstab and Chart. Insert data items into both containers so that 2 queries are created E.g. Query1 and Query2.

b)    The 2 queries should have at-least 1 Common data-item.

c)     Click “Unlock” icon from Toolbar to unlock page objects. Select 2^nd container object, and drag it into the 1^st container where you want to place it.

d)    Click embedded container object. Click Master Detail Relationships from Data menu. Create “New Link” between the common data-item.

e)     Click Ok and run the report.

Q. How do you set security for reports?

Ø  You can set security from the report properties in Cognos Connection. You can add users / user classes with read/write/execute/traverse/set policy level access.

   Cognos Connection à Report Properties à Permissions

Q. What is a “Layout Component Reference” in Report Studio?

Ø  “Layout Component Reference” is an object in Report Studio which is used to give a reference to another object in the report. It helps to reuse an object instead of creating it again.

Example – It can be used to include a Company Logo on all pages of a report.

Steps:

a)     Create 2 page report; List on first page and Crosstab on second.

b)    Add Company logo on first page and give it a unique name.

c)     On second page, add “Layout Component Reference” object where you want the same logo to be displayed.

d)    Check on “This Report” option and select the “Company Logo” object.

Cognos Transformer

Cognos PowerPlay Transformer is a tool to create multidimensional database, Powercube. A PowerCube contains calculated, summarized data organized into dimensions and measures and can be viewed and analysed in the PowerPlay Web Explorer, PowerPlay client for Windows, PowerPlay for Excel or Cognos 8 BI. PowerCubes provide secure and fast data access.

The default Cognos PowerPlay Transformer window is composed of the following windows:

1. Data Sources - a list of all connected data sources. Each source can be expanded and it lists all available columns. Users can browse the source data by using Data Source Viewer. Columns from a data source window can be dragged & dropped to the dimensions and measures windows accordingly.

2. Dimension Map - manages the dimensions and levels hierarchy in the data model. It allows creating new dimensions and modifying existing using an user-friendly graphical interface. Dimensions and underlaying Levels are created by moving columns from the Data Sources window to the corresponding dimension column.

3. Measures - drag & drop columns to manage number figures (facts) in a PowerPlay model.

4. PowerCubes - you can specify here a list of cubes created by the model.

5. User Classes - define the security.

6. Signons - datasource connection information.

Building a PowerPlay model involves designating columns in the source files as measures and dimensions.

Some of the problems faced during cube creations are:

Insufficient Memory

Increase the paging file size. However this requires re-start of the computer. So, before you build a cube on your local PC, make sure you have sufficient virtual memory (500-1000 MB).

THE FILE SYSTEM IS FULL

Owing to the amount of data fetched from the database, the temporary space of the database where the data is temporarily stored becomes full. It is not possible for more cube processing to happen on the database and the cube is not created. The solution to this:

a.                  Check if the sqls fired are correct. Best option is before the iqds are included as data sources into the model, to fire the sqls separately, directly on the database and check if all the joins exist and are correct and if the right result is obtained.

b.                  Use query-optimization techniques to enhance the performance of the query through modifying the queries themselves or creating appropriate indexes on the tables used by the queries

The SQL explain facility is part of the SQL Compiler that can be used to capture information about the environment where the static or dynamic SQL statement is compiled. The information captured allows you to understand the structure and potential execution performance of SQL statements, including:

·         Sequence of operations to process the query

·         Cost information

·         Predicates and selectivity estimates

·         Statistics for all objects referenced in the SQL statement at the time of the Explain.

c.                   Ensure that the cube is run during off-peak hours. This is because, if other processes are running on the database, the primary and the secondary log files (into which queries are temporarily processed and stored) may become full and give the above error.

d.                  If conditions a, b and c are checked and still, the above error is obtained, then procure more temp space. Ask the DBA to either increase the number of secondary log files or increase their size.

Time-Consuming Build of Cube

If the cube has more than 8 measures, then new categories are searched for and created for each measure and then the records added. This is an extremely time consuming process and the cube can take upwards of 2 hours to completed build itself. If there is a database-resource intensive process that needs to run meanwhile, then this is not feasible.

These are the methods we can adopt to reduce the processing overhead

1.                   Incremental update of the cube - Instead of building the entire cube at a time, we can plan for the cube to ‘build incrementally’. However, this can be done only if there are no major structural changes to the old data i.e. the old hierarchical structure of the dimensions does not change and there are no updates on the measures. If this happens, then the cube has to be re-created again.  In incremental build of the cube, the old data in the cube remains unchanged and only the new records are added into the cube.

In PowerCube windowàPropertiesàProcessingàEnable the radio button ‘This cube is incrementally updated’

2.                  Use of Proper Optimization – There are 4 types of optimization methods which should be used depending on the way records are stored and updated in the database. The 4 types of settings are

a.                  Categories

b.                  Data Passes

c.                   Direct Create

d.                  Auto-Partition

The best way to arrive at the appropriate optimization technique is to limit the cube to only few records fetched at a time (excluding most of the categories, putting filters in the iqds). Using this, we can create a test cube in lesser time and then alternate between various optimization techniques to identify the best one.

More investigation should be done about excluding some data sources for measures at a time so that only limited and manageable amount of measures are processed at a time. One the cube is built, then the rest of the data sources are added. If incremental update of the cube is enabled, then the data sources present earlier shouldn’t be ideally processed and hence cube build should be faster.

Framework Manager

Q. What are the components/objects in a FM project?

Ø  The components of a Framework Manager project can be listed as:

       Project à Model à Namespace à Query Subject à Query Item

Q. What are the components of Framework Manager screen?

Ø  The different components can be listed as below:

a)     Project Viewer – shows project objects in a hierarchical view. Used to view, modify and create objects.

b)    Explorer Tab – shows the contents of a project like any file system. Used to view, create and modify objects and relationships.

c)     Diagram Tab – shows the relationships between objects in a project. Used to view, create and modify objects and relationships. Also used to change diagram settings, “Capture Screen” and “Set Focal Point”.

d)    Dimension Map – Used to view, create and modify hierarchies and levels for the dimension selected in Project viewer.

e)     Properties Pane – shows the properties of the object that is selected in the project viewer.

f)     Tools Pane – The different tabs in tools pane are listed below:

                                                                                                 a)        Summary Tab

                                                                                                  b)        Search Tab

                                                                                                 c)        Dependencies Tab

Q. What is the extension of FM project file? What all files constitute a FM project?

Ø  The extension of a FM project file is .cpf.

Below is a list of all files that constitute a project:

a)     ProjectName.cpf – Project File (references the .xml files)

b)    IDLog.xml – used to track objects for models

c)     Log.xml – list of all modifications made to the model

d)    Model.xml – actual model data

e)     Preferences.xml –

f)     Persistent.txt – Diagram information

Q. What are the different types of Query Subjects?

Ø  The different types of query subjects can be listed as below:

a)     Data Source Query Subject.

b)    Model Query Subject.

c)     Stored Procedure Query Subject.

Q. What are the different types of Stored Procedures in Framework Manager?

Ø  Framework Manager supports only user-defined stored procedures. System stored procedures are not supported.

The different types of Stored Procedures in Framework Manager are:

1.       Data Query Stored Procedure – Issues a read-only transaction.

2.       Data Modification Stored Procedure – Writes a record to the data source.

Q. How to build a model and publish a package?

Ø  Steps to build a model and package:

1.     Open Framework Manager and click on “Create a new Project”.

2.     Select language and select data source.

3.     Select the tables, views, procedures etc. to import.

4.     Create relationship and click on import.

5.     Right click on the package and select to create a package option.

6.     Choose objects and select function sets.

7.     Select location and click on publish.

Q. How do you create IQD in Framework Manager?

Ø  We can create IQD by setting the externalize property and select property to IQD.

         Steps to follow:

          1. Select the Query subjects and go to Properties.

          2. Select externalize method option.

          3. Select property to IQD.

          4. Save the model.

          5. While publishing, specify the location for IQD files.

          6. You can find your IQD’s in the specified location after publishing the package.

Q. 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

Q. What is difference between Shortcut and Alias Shortcut?

Ø  Shortcut is a pointer to an object.

   While a regular shortcut is a simple reference to the target object; Alias shortcut behaves as a copy of the original object with completely independent behavior.