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.

·