determinants in fm

Questions link

http://atozmcqs.blogspot.in/2014/01/cognos-multiple-choice-questions-and.html

http://www.atoztarget.com/2014/01/58-top-cognos-interview-questions-and.html

http://www.atoztarget.com/search?q=cognos

http://www.interview-blog.com/top-67-cognos-interview-questions-and-answers-pdf-download/

The tuple function serves to specify the specific context that a filter, sort order or another function (such as value() or rank()) will operate against. A tuple indicates which coordinates of the cube are in use for the operation being executed. Here are some examples of the tuple function being used... Within a Query Expression that filters: Simple use case: The tuple function serves to specify the specific context that a filter will operate against. In the case of a filter such as ?Top 10 SalesPeople for Q1 of 2012?, the context would likely be the measure (e.g. revenue) and the member 2012 Q1. Syntax: TopCount( children of salespeople, 10, tuple([Revenue], [2012 Q1]) ) If a dimension is not represented in the tuple function for a filter, then the default member of each dimension is used for the context. Within a Query Expression using the Rank function: Syntax: rank( TUPLE WITHIN SET ) Simple use case: User wants to see the rank of each Product Line for the year 2012. rank(currentMeasure DESC tuple [2012] WITHIN SET [Product Line]) NOTE: In this case, the context used by tuple is not placed in brackets. Historical Number 1019080

Control Page Breaks and Page Numbering

You can control page breaks and page numbering in a list, crosstab, table, or report page by choosing any of these options.

The options that are available depend on which object you have selected. All the options for all the objects are described in the following table.

Option	Description
Keep with header	Keeps all headers on the same page with the number of detail rows specified.
Keep with footer	Keeps all footers on the same page with the number of detail rows specified.
Keep with previous	Keeps the object with the specified number of preceding objects on the same page, if space permits.
Keep with next	Keeps the object with the specified number of subsequent objects on the same page, if space permits.
Reset page count	Resets the page count after a page break to the value specified.
Reset page number	Resets the page number after a page break to the value specified.
Repeat every page	If the report renders multiple pages, this object is repeated on every page.
Allow contents to break across pages	Allows contents to break across pages. In lists and crosstabs, controls whether a cell is broken across pages, which is useful when there is a lot of text.
Allow horizontal pagination	In PDF output, allows the columns of a list or crosstab to break across horizontal pages if they do not fit on a single page. Tip: In lists, you can select the Repeat every page option for list columns that show on every horizontal page. If the Allow horizontal pagination option is not selected, the size of the list or crosstab is scaled down when necessary so that it fits on a single page. Tip: The Horizontal Pagination sample report in the GO Sales (analysis) package includes horizontal pagination. For more information about The Sample Outdoors Company samples, see Sample Reports and Packages. If your report includes nested data frames such as a list within a list, horizontal pagination is supported on either the parent or child frame, but not both. If horizontal pagination is enabled on both the parent and child frame, it will be ignored on the child frame when the report runs. We recommend that you do not enable horizontal pagination on both the parent and child frames. Horizontal pagination is not supported for data container, such as a list or crosstab, that are nested in repeater tables. You can also specify page number options that use compound numbering schemes. For example, you can use the numbering scheme 1-1, 1-2, 2-1, 2-2, and so on. For more information, see Insert Page Numbers in a Report.
Enable horizontal page numbering	Increments page numbers of horizontal pages separately from the main page numbers when you select a page numbering style that includes horizontal pages. For example, if a page has three page breaks horizontally and you selected the page number style 1a, the horizontal pages are numbered 1a, 1b, and 1c. If you did not select a numbering style that includes horizontal pages, the horizontal pages are all numbered 1 for the first vertical page, 2 for the second vertical page, and so on. If this option is not selected and there are horizontal pages, all pages are numbered sequentially. For example, if a report has two vertical pages and three horizontal pages, the PDF pages are numbered from 1 to 6. Pages 1 to 3 are the three horizontal pages for the first vertical page and pages 4 to 6 are the three horizontal pages for the second vertical page.
Allow row contents to break across pages	In tables, allows the contents of a row to break across pages. For example, if a row contains four lines of text, the first two lines from the row appear on the first page, and the last two lines appear on the next page.
Repeat table rows on page break	In tables, if a row breaks across pages, repeats the rows that were previously rendered on each page. By default, table rows are repeated. Note: This option applies to saved reports only. In interactive HTML reports, table rows are always repeated even if this option is not selected.

You can also specify the style to use for page numbers.

Procedure

1. Click an object.
2. In the Properties pane, double-click the Pagination property.
3. Specify the page break and numbering options.

Considerations to Improve Report Accessibility

Creating accessible reports ensures access of information to all users, with all levels of ability.

 

For example, people with a visual impairment may use screen reading technology to access the information in a report.

The following are some design considerations for creating accessible reports:

• Avoid using visual cues, such as bold text or color, to convey important information.
• Avoid using pictures and OLE Objects in PDF documents, as these items are tagged as artifacts and ignored by the screen reader.
• Avoid using conditional formatting to convey important information.
• When selecting color palettes for report objects, choose patterns or shades of gray.
• Ensure that there is a table corresponding to chart types that are rendered as images because the screen reader ignores this information.
• Deliver reports in HTML format, which is the most supported output format for most screen readers.
• Ensure that the report has a title.
• Gain an understanding for screen reading technology.
• Avoid spelling and grammatical errors, as they cause the screen reading software to misinterpret the information.
• Avoid using features like calendar boxes and up and down selections on time controls. Instead use prompts such as check boxes, radio buttons, combo boxes, and multi-select boxes.
• Ensure that the target application is accessible when using embedded Web applications or drill-through paths.
• Avoid using large, complex list or crosstab reports.
  Displaying the information in multiple simple lists or crosstab reports is more manageable for assistive technology users.
• Add alternate text to images, charts, and other visual objects so that screen readers can provide context for them.
• When using tables, add summary text to provide context for the table content. If the top cells in a table behave as headers, designate these cells as headers so that screen readers can identify the relationships.

• Enable Accessible Report Outputs
  If you want to include accessibility features, such as alternate text, summary text, designated cell headers in tables and accessible conditional layouts, you must enable these accessibility features in the report output.
• Add Alternate Text to Images and Charts
  You can add alternate text for images, maps, and charts to make your reports accessible. When a screen reader encounters one of these objects, it reads the alternate text that you added to the object.
• Add Summary Text to Tables
  You can provide summary text for crosstabs, lists, repeater tables, and table objects. This text provides context for the entire object to make your reports accessible. When a screen reader encounters one of these objects in HTML report outputs, it reads the description that you added to the object.
• Designate Cells Headers in Tables
  You can specify whether specific table cells are table headers. This allows screen readers and speech browsers to identify the relationships between the cells in your tables.
• Example - Conditionally Show a List Below a Chart for an Accessible Report
  Charts are rendered as images in report outputs, such as HTML and PDF. As a result, they are difficult to navigate for visually impaired users and screen readers cannot convey the information shown in charts. To make your reports accessible, you can add a conditional layout that shows list or crosstab equivalents of the chart when the accessibility features are enabled for the report output

Cognos 10 Framework Manager Best Practices

1.0     Introduction

This document is primarily intended for Designers, Application Developers and Report Authors using Cognos 10 BI.

1.1      Purpose

This document is written for professional Cognos 10 BI developers to help them:

·         Design Framework Manager Models, which are easy to maintain and enhance.

2.0     Cognos Framework Manager Model

Framework Manager is a metadata modelling tool in Cognos in which data is sourced from one or more data sources. By Adding Multi-lingual capabilities, one can create a model which is a business representation of data available to the users for analysis.

2.1      Using a Four Layered Modelling approach.

2.1.1          Database Layer

• The database layer contains data source query subjects based directly on the objects found in the underlying database.
• Always try to use unmodified SQL for query definitions in this layer as modifying the SQL disables framework meta-data caching capabilities forcing Cognos to validate the query subject at run time which reduces the efficiency of the end report.
• The same applies to filters and calculations; avoid adding these to database layer query subjects.
• Joins, cardinality and determinants should be set at the database level but other than these no further modelling work should be done at the database layer.

2.1.2          Business Layer

• Start by organizing query items taken from the database layer of your model into logical query subjects.
•  Combine elements from multiple tables into single logical query subject which represent a single discreet business concept.
• Give query items appropriate names that reflect recognized business terminology.
• Set descriptions, tooltips and multi-language definitions along with output formats and usage properties.
• Define any required prompts, prompt-based calculations, calculations and filters. To ensure your model is as durable as possible base calculations and filters on items in the database layer of your model.
• Organize query subjects in a user friendly manner using folders where necessary to logically group similar query subjects.
• Avoid putting too many query subjects in a single folder as this can make finding items more difficult for users and remember to use tools such as ‘Reorder’ to ensure contents of folders and namespaces are presented in a logical manner.

Example:

2.1.3          Presentation Layer

• The sole purpose of the presentation layer is to make it easy for users to find the information they need. No modelling is carried out in the presentation layer; it should contain only shortcuts to items in the modelling layer along with folders and namespaces for organizational purposes.
• It can be useful to use the ‘Create Star Schema Grouping’ functionality when building the presentation layer. This is done by selecting all of the required Query Subjects in the modelling layer, right clicking on one of the selected items and choosing ‘Create Star Schema Grouping’. The result is a new namespace in the modelling layer, which contains shortcuts to the selected Query Subjects. The new namespace can then be moved to the presentation layer.

Example :

Example :

2.1.4          Dimensional Layer

• The Dimensional layer is used for presenting dimensionally modelled data to users; it is often mistaken as being the source used for building transformer cube models but these can and should be built off the presentation layer.
• As with the modelling layer, you should use the dimensional layer to add business context to the data from the database layer by renaming and adding appropriate descriptions and tooltips.
• To avoid confusion ensure query items which appear in both the dimensional and modelling layer have the same name, tooltip and description

2.1.5          Advantages of Four layered Modelling

·         Suitable for transactional databases.

·         Can be used separately for Report Studio/Query Studio and Analysis Studio.

·         Can segregate relational objects (Query Subjects) and dimensional Objects (Regular and Measure dimensions) properly.

·         Supports drill up/drill down functionality.

• Provides better scalability and supports new object inclusion

2.2      Defining Cardinality in FM

When importing from a relational data source, cardinality is detected based on a set of rules that you specify. The available options are:

·         Use primary and foreign keys.

·         Use matching query item names that represent uniquely indexed columns.

·         Use matching query item names.

The most common situation is to use primary and foreign keys as well as matching query items that represent uniquely indexed columns. The information is used to set some properties of query items as well as to generate relationships.

• Create relationships and ensure cardinality rules are appropriate. This is an extremely important component of the modeling process. Framework Manager uses the cardinality rules to assist the query engine in generating the appropriate SQL statements.  

Different types of cardinalities are:

§  One-to-one (1..1) - One-to-one (1..1) relationships occur when one instance of data in a query subject relates to exactly one instance of another. For example, each student has one student number.

§  One-to-many (1..n) or zero-to-many (0..n) relationships occur when one instance of data in a query subject relates to many instances of another. For example, each teacher has many students.

§  Many-to-many (1..n) - Many-to-many (n..n) Relationships occur when many instances of data in a query subject relate to many instances of another. For example, many students have many teachers.

·         Use 1..n or 0..1 for dimensionally aware queries

§  Identifies fact tables in star schemas

§  Identifies multi-fact queries and generated ‘stitched’ SQL statements.

·          Use 1..1 or 0..1 for default intersection queries (Non-stitched SQL)

·         Do not create fact-to-fact joins as these can create problems (such as query performance due to data volume, query performance with outer joins, different grains of detail). Fact tables should be separated by a dimension.

·         It’s not recommended to allow outer joins as it decreases the report performance

·         Many to Many relationships should be avoided as they generate stitched queries. Instead Star Schema grouping should be used.

·         Resolve loop joins or ambiguous relationships. The most common type of ambiguous relationships are where multiple valid relationships exist between two tables, reflexive relationships (table joins to itself). This can be resolved by creation of alias tables; however, it is not recommended to build deep hierarchies to resolve reflexive relationships. This should be accomplished by flattening out the table.

·         Set the SQL Generation type. (Minimized means that the generated SQL contains the minimum number of tables and joins required to obtain values for the selected query items.)

2.3      Using Dimensional Information

·         Use dimensional information to specify the relationship between levels in a multi-level dimension.

·         In order to define the behavior expected when querying at a one or more levels of time for example, dimensional information is used.

·         Levels are defined for years, quarters, months, and days.

·         A key is defined for each level and in this example, that key is sufficient to uniquely identify the level.

2.4      Avoiding Stich Queries

• Stitch queries are a (expected) single query which is broken down into two or more individual queries before being sent to the database.
• The results from the two (or more) queries are then joined on the Cognos application server which is in most cases, not very efficient.
• Stitch Queries occur when a model has multiple one to many relationships usually as a result of data coming from multiple fact tables which can only be joined through a conformed dimension. As an example, consider the model diagram shown below.

• The Sales fact and Inventory fact tables can only be joined through one of the three conformed dimensions (Time, Product and Staff). This will mean any report which takes data from both the Sales Fact and Inventory Fact tables would result in Cognos generating stitch queries.
•  This is the correct approach for this type of query but frequently occurs due to incorrectly set cardinality within the Framework model. It is essential to ensure cardinality is set correctly throughout your model to ensure unnecessary stitch queries which can cause report performance to be very poor

2.5      Handle Ambiguous Relationships

• There are two types of relationships that can provide inconsistent result sets if not handled by the modeler. The first occurs when there are multiple valid relationships. This typically occurs between facts and dimensions. In a fact table, a different date is present: invoice date, ship date, order date… all point to the date dimension. Combining multiple dates in a single query will no longer return results.
• Another issue occurs when handling recursive relationships. The classic example is the manager – employee relation. An employee has a manager. The manager is an employee and also has a manager that again is an employee.
• These situations can be handled by creating multiple model query subjects for every occurrence. You would however have to reset all the properties of every model query subject created leading to unnecessary work. A convenient solution to this problem is using shortcuts. There are two types:
• Regular Shortcut: reference to the source objects but inherits all properties including relationships
• Alias Shortcut: behaves independently of the source object, so different relationships can be set
• The creation of multiple alias shortcuts on a table that use different relationships will handle these ambiguous relationships graciously. Regular shortcuts will be used while creating the Presentation View.

·         Recursive relationship should be converted to fixed hierarchy using shortcuts

·         Avoid loops while creating relationships, by using shortcuts, as they behave unpredictably while reporting

2.6      Using Determinants

• A determinant is needed to identify levels of aggregation within the query subjects. This is a particularly useful feature when dealing with multi-fact, multi-grain queries.
• When you have a sales fact at day level and a target fact at month level, combining both facts in a single query would lead to incorrect results. The targets would be multiplied several times as they are stored at month level and not at day level.
• Determinants will change the default behavior of the query. Cognos will recognize the difference in grain and will write 2 queries that will be stitched together to return proper results at the proper grain.

·         The key element in performing multi-fact multi-grain queries is by using a conformed dimension shared between both fact tables.

·         When retrieving 2 measures from two different fact tables using a different granularity, Cognos can determine the correct aggregation when determinants are specified. A determinant will specify what set of columns will uniquely define a set of columns. Each level is specified identifying the key and attributes that belong to a level. The lowest level is marked unique.

·         This will enable the report developer to create a report showing revenue at week level versus month figures without double counting the lowest grain fact. Cognos uses the mechanism of stitch queries to perform these types of requests. A stitch query will perform a full outer join to break queries into multiple selects, one for each fact table and then stitch the data back together.

2.7      Using Conformed Dimensions

·         Conformed dimensions should be given the same name in each namespace where they appear. That’s the only way the users can tell that they are conformed.

·         If two fact tables have conformed dimensions, but different levels of granularity for that dimension, then for each fact table only the relevant levels should be included

2.8      Security

• Using the same framework, multiple package can be defined each containing one or more star schema’s. Granting or denying access to a package a very effective and easy way to implement a basic level of data security. However, each individual object at any level can be secured.

• Data security will restrict users to query data they are not allowed to. For example, a district manager will only be able to query data of his district. Row level security can be put in place in two different ways. It is possible to hard code the values for every group. However a more generic approach is to create embedded filters using security macro functions such as the LDAP username

2.9      Defining Usage Property in FM

• Usage property identifies the intended use for the data represented by each query item.
• During importing, the Usage property is set according to the type of data that the query items represent in the data source.
• You need to verify that this property is set correctly. For example, if you import a numeric column that participates in a relationship, the Usage property is set to identifier. You can change the property.
• For relational query items, the value of the Usage property depends on the type of database object the query item is based on

Usage Property	Database Object	Description
Identifier	key, index, date,datetime	Represents a column that is used to group or summarize the data in a Fact column with which it has a relationship. Also represents an indexed column. Also represents a column that is of the date or time type.
Fact	numeric, time interval	Represents a column that contains numeric data
Attribute	String	Represents a column that is neither an identifier not fact such has descriptions

2.10  Links & Segments

Linking and segmenting are recommended for large models. These techniques are also valuable when multiple FM developers are required.

·         Links are created to help organize work across large projects, to maintain consistency, and to reuse information.

For example, the project named Inventory contains the folder named Products. You can create a link from the Sales Products to Inventory Products. If any changes or additions are made to the Inventory Products folder, you will see them in the Sales Products folder.

A linked project is shared by other projects. It should not be created in a subdirectory within the master project directory.

·         A segment is a project within a main project. A segment is owned by its main project.

A project segment is a complete project and changes to that project impact all projects to which it is linked. If you want to open a segment as a separate project, it must be structured as a complete project. There must be a physical layer in each segment that contains a subset of the data source query subjects on which they are based. These data source query subjects provide access to the data and metadata and must be included in the appropriate segments.

2.11  Enforce Source Control

·         When starting project make sure appropriate precautions have been taken to protect the work. All files located within the model’’ project folders should be source controlled and checked in at least once a day

2.12   Efficient Processing

Determine if all data processing can be accomplished at the database level. Abstraction of data processing to the report application server level typically results in slower processing and higher resource consumption on the report server(s). A decision to perform local processing should be based on the following:

·         A clearly identifiable need for specific processing not available from the database such as cross database joins and unsupported SQL99 functions.

·         Anticipated data volume in the production environment

3.0     Packages and Validations

Create a Package for Each Functional Area

·         Create a separate package for each functional area. This ensures that the package stays as trim as possible and limits the demand for regression testing.

Validate the Model

·         During the development of the Data Model, the author should be testing their query subjects to ensure that the appropriate data is being returned. Always validate the project before publishing to the server. The tool requires that all errors be repaired prior to publication; however, warnings should be closely examined to ensure the future integrity of the model.

Model Modifications

·         Once the report authors begin development, modifications to the structure of the Framework Manager model may negatively impact the report authoring process.

Synchronize the Project

·         Frequently, changes are made to the underlying data sources that need to be propagated to the model. The preferred method of doing this is to synchronize the project. However, if the project is large and complex, this may be fairly time-consuming. You may choose to update individual query subjects, by selecting the update option under the tools menu.

Task Automation

·         If there is a need to perform identical tasks against multiple models, perform repetitive tasks or fully reconstitute a model, convert the log files to scripts to perform repetitive tasks or fully reconstitute a new model, save the transaction logs as scripts and execute the script as appropriate.

·