In this lesson, we will discuss Basic concepts in Cognos

IBM Cognos Business Intelligence is a web based reporting and analytic tool. It is used to perform data aggregation and create user friendly detailed reports. Reports can contain Graphs, Multiple Pages, Different Tabs and Interactive Prompts. These reports can be viewed on web browsers, or on hand held devices like tablets and smartphones.

Cognos also provides you an option to export the report in XML or PDF format or you can view the reports in XML format. You can also schedule the report to run in the background at specific time period so it saves the time to view the daily report as you don’t need to run the report every time.

IBM Cognos provides a wide range of features and can be considered as an enterprise software to provide flexible reporting environment and can be used for large and medium enterprises. It meets the need of Power Users, Analysts, Business Managers and Company Executives. Power users and analysts want to create adhoc reports and can create multiple views of the same data. Business Executives want to see summarize data in dashboard styles, cross tabs and visualizations. Cognos allows both the options for all set of users.

To llearn cognos training online please visit:cognos online classes

Key Features of IBM Cognos

Cognos BI reporting allows you to bring the data from multiple databases into a single set of reports. IBM Cognos provides wide range of features as compared to other BI tools in the market. You can create and schedule the reports and complex report can be designed easily in the Cognos BI Reporting Tool.

The Cognos BI Reporting Tool allows to create a report for a set of users like — Power users, Analysts, and Business Executives, etc. IBM Cognos can handle a large volume of data and is suitable for medium and large enterprises to fulfil BI needs.

3-Tier Architecture Cognos

Cognos BI is considered to be a 3-tier architecture layout. At the top, there is a Web Client or a Web Server. The 2nd tier consists of a Web Application Server. While the bottom tier consists of a Data layer.

These tiers are separated by firewalls and communication between these tiers happens using SOAP and HTTP protocols.

Tier-1 Web Clients

The web client allows BI users to access TM1 data and interact with data in any of the supported browsers. Tier 1 is responsible to manage the gateway and is used for encryption and decryption of passwords, extract information needed to submit a request to the BI server, authentication of server and to pass the request to Cognos BI dispatcher for processing.

Tier-2 Web Application Server

This tier hosts the Cognos BI server and its associated services. Application server contains Application Tier Components, Content Manager and Bootstrap service.

Cognos TM1 Web Application Server runs on Java based Apache Tomcat server. Using this tier, Microsoft Excel worksheets can be converted to TM1 Web sheets and also allows to export web sheets back to Excel and PDF format.

Tier-3 Data

This tier contains content and data sources. It contains TM1 Admin server and at least one TM1 server.

TM1 Admin server can be installed on any computer on your LAN and it must reside on same network as TM1 server. The version of TM1 server should be equal or most recent then the version of Cognos TM1 web.

Cognos Versions

In this section we will discuss the different versions of Cognos.

Cognos Query Version 7

7.4Inactive (as of Sept 30, 2012)Unavailable (since Sept 30, 2012)7.3Inactive (as of Jan 1, 2008)Unavailable (since June 30,2012)7.47.1Inactive (as of Dec 31, 2005)Unavailable (since Nov 30, 2011)7.3 or 7.47.0Inactive (as of Dec 31, 2004)Unavailable (since Nov 30, 2011)7.1 or 7.3

Cognos 8 BI

8.4.0Standard IBM Support(3), Inactive (as of Sept 30, 2012)Unavailable (since Sept 30, 2012)8.4.18.4 FCS (First Customer Ship)Inactive (as of Feb 27, 2009)Unavailable8.4.18.3Inactive (as of April 30, 2012)Unavailable (since April 30, 2013)8.4.18.2Inactive (as of March 31, 2010)Unavailable (since June 30, 2012)8.4.18.1.2 MR2Inactive (as of March 31, 2009)Unavailable (since Nov 30, 2011)8.4.18.1.2 MR1Inactive (as of March 31, 2009)Unavailable (since Nov 30, 2011)8.4.18.1.1Inactive (as of Dec 31, 2004)Unavailable (since Nov 30, 2011)8.4.1
Cognos tm1

9.4 MR1Inactive (as of Sept 30, 2012)Unavailable (since Sept 30, 2012)9.5.29.4Inactive (as of Sept 30, 2012)Unavailable (since Sept 30, 2012)9.5.29.1 SP4Inactive (as of Dec 31, 2010)Unavailable (since Nov 30, 2011)9.5.29.1 SP3Inactive (as of Dec 31, 2010)Unavailable (since Nov 30, 2011)9.5.2

And then there were different sub-versions of the — Cognos Business Intelligence 10, which were −

IBM Cognos Business Intelligence 10.1
IBM Cognos Business Intelligence 10.1.1
IBM Cognos Business Intelligence 10.2
IBM Cognos Business Intelligence 10.2.1
IBM Cognos Business Intelligence 10.2.2
IBM Cognos Business Intelligence 11.0.0

Cognos vs Other BI Reporting Tools

There are various other BI reporting tools in the market that are used in medium and large enterprise for analytics and reporting purpose. Some of them are described here along with its key features.

Cognos vs Microsoft BI

Following are the key features that are supported by both the tools −

Standard Reporting
Ad-hoc Reporting
Report output and Scheduling
Data Discovery and Visualization
Access Control and Security
Mobile Capabilities

Cognos can be considered as a robust solution which allows you to create a variety of reports like Cross tabs, Active reports (latest feature in Cognos 10), and other report structure. You can create user prompts, scheduling of report is easy and you can export and view reports in different formats. The Microsoft BI provides easy visualization of business data as well as Easy integration with Microsoft Excel.

IBM Cognos vs SAP Business Objects

SAP BO supports its own ETL tool SAP Data Services. IBM Cognos doesn’t support its own ETL tool. The IBM Cognos 8 doesn’t provide offline reporting features however it is there in SAP Business Objects reporting tools.

In Cognos the entire functionality is divided into multiple tools Query studio, Analysis studio, event studio etc. It is a tough task to learn all the tools. In SAP Business Objects, you have multiple tools like Web Intelligence for reporting, IDT for Universe Designer, Dashboard Designer so users feel that it is a tough task to manage and learn all the tools.

In IBM Cognos, data generated can be transformed in various formats (for instance, HTML, PDF, etc.) and can also be accessed from multiple locations (e-mail, mobile, office, etc.). IBM provides several planning capabilities such as forecasts, budgets, advance scenario modelling etc. Selection of BI tool depends on various factors like need of company, software version, features supported and the license cost.

Components and Services

There are various components in Cognos that communicate with each other using BI Bus and are known as Simple Object Access Protocol (SOAP) and supports WSDL. BI Bus in Cognos architecture is not a software component but consists of a set of protocols that allows communication between Cognos Services.

The processes enabled by the BI Bus protocol includes −

Messaging and dispatching
Log message processing
Database connection management
Microsoft .NET Framework interactions
Port usage
Request flow processing
Portal Pages

When you install Cognos 8 using the Installation wizard, you specify where to install each of these components −

Gateways

The Cognos 8 Web server tier contains one or more Cognos 8 gateways. The web communication in Cognos 8 is typically through gateways, which reside on one or more web servers. A gateway is an extension of a web server program that transfers information from the web server to another server. Web communication can also occur directly with a Cognos 8 dispatcher but this option is less common.

Cognos 8 supports several types of Web gateways, including −

CGI − The default gateway, CGI can be used for all supported Web servers. However, for enhanced performance or throughput, you may choose one of the other supported gateway types.
ISAPI − This can be used for the Microsoft Internet Information Services (IIS) Web server. It delivers faster performance for IIS.
apache_mod − You can use an apache_mod gateway with the Apache Web server.
Servlet − If your Web server infrastructure supports servlets or you are using an application server, you can use a servlet gateway.

Application Tier Components

This component consists of a dispatcher that is responsible to operate services and route requests. The dispatcher is a multithreaded application that uses one or more threads per request. The configuration changes are routinely communicated to all the running dispatchers. This dispatcher includes Cognos Application Firewall to provide security for Cognos 8.

The dispatcher can route requests to a local service, such as the report service, presentation service, job service, or monitor service. A dispatcher can also route requests to a specific dispatcher to run a given request. These requests can be routed to specific dispatchers based on load-balancing needs, or package or user group requirements.

Content Manager

Content Manager contains Access Manager, the primary security component of Cognos 8. Access Manager leverages your existing security providers for use with Cognos 8. It provides Cognos 8 with a consistent set of security capabilities and APIs, including user authentication, authorization, and encryption. It also provides support for the Cognos namespace.

Cognos — Connections

You can report interactive user reports in Cognos Studio on the top of various data sources by creating relational and OLAP connections in web administration interface which are later used for data modeling in Framework Manager known as packages. All the reports and dashboards that are created in Cognos Studio they are published to Cognos Connection and portal for distribution. The report studio can be used to run the complex report and to view the Business Intelligence information or this can also be accessed from different portals where they are published.

Cognos Connections are used to access reports, queries, analysis, and packages. They can also be used to create report shortcuts, URLs and pages and to organize entries and they can also be customized for other use.

Connecting Different Data Sources

A data source defines the physical connection to a database and different connection parameters like connection time out, location of database, etc. A data source connection contains credential and sign on information. You can create a new database connection or can also edit an existing data source connection.

You can also combine one or more data source connections and create packages and published them using Framework manager.

Dynamic Query Mode

The dynamic query mode is used to provide communication to data source using XMLA/Java connections. To connect to the Relation database, you can use type4 JDBC connection which converts JDBC calls into vendor specific format. It provides improved performance over type 2 drivers because there is no need to convert calls to ODBC or database API. Dynamic query mode in Cognos connection can support the following types of Relational databases −

Microsoft SQL Server
Oracle
IBM DB2
Teradata
Netezza

To support OLAP data sources, Java/XMLA connectivity provides optimized and enhanced MDX for different OLAP versions and technology. The Dynamic query mode in Cognos can be used with the following OLAP data sources −

SAP Business Information Warehouse (SAP BW)
Oracle Essbase
Microsoft Analysis Services
IBM Cognos TM1
IBM Cognos Real-time Monitoring

DB2 Data Sources

The DB2 connection type are used to connect to DB2 Windows, Unix and Linux, Db2 zOS, etc.

The common connection parameters used in DB2 data source includes −

Database Name
Timeouts
Signon
DB2 connect string
Collation Sequence

Creating a Data Source Connection in IBM Cognos

To create models in IBM Cognos Framework Manager, there is a need to create a data source connection. When defining the data source connection, you need to enter the connection parameters — location of database, timeout interval, Sign-on, etc.

In IBM Cognos Connection → click on the Launch IBM Cognos Administration

In the Configuration tab, click Data Source Connections. In this window, navigate to the New Data Source button.

Enter the unique connection name and description.

You can add a description related to the data source to uniquely identify the connection and click the next button.

Select the type of connection from the drop down list and click on the next button as shown in the following screenshot.

In the next screen that appears, enter the connection details as shown in the following screenshot.

You can use the Test connection to test the connectivity to the data source using connection parameters that you have defined. Click on the finish button once done.

Data Source Security Setup

Data Source Security can be defined using IBM Cognos authentication. As per the data source, different types of authentication can be configured in the Cognos connection −

No Authentication − This allows login to the data source without using any sign-on credentials. This type of connection doesn’t provide data source security in connection.

IBM Cognos Software Service Credential − In this type of a sign-on, you log in to the data source using a logon specified for the IBM Cognos Service and the user does not require a separate database sign-on. In a live environment, it is advisable to use individual database sign on.

External Name Space − It requires the same BI logon credentials that are used to authenticate the external authentication namespace. The user must be logged into the name space before logging in to the data source and it should be active.

All the data sources also support data source sign-on defined for everyone in the group or for individual users, group or roles. If the data source requires a data source sign-on, but you don’t have the access to a sign-on for this data source, you will be prompted to log on each time you access the data source.

IBM Cognos also supports security at cube level. If you are using cubes, security may be set at the cube level. For Microsoft Analysis Service, security is defined at the cube level roles.

Cognos — Framework Manager

What is a Framework Manager?

IBM Cognos Framework Manager is used to create business model of metadata derived from one or more data sources. It is a Windows based tool which is used to publish the business models to Cognos BI in the form of packages which can be used for analytical reporting and analysis.

Before you start a new project in Framework Manager, it is necessary that you go through BI reporting requirements that helps you to identify data strategies, metadata, report package delivery, etc. This helps you to identify which data sources are required in the Framework Manager to get the required data in the BI report. You should consider the following factors before starting a new project in the Framework manager.

Data Sources required to meet BI needs.
Types of DW system
Data refresh in Data Warehouse
BI Reporting- Daily, Weekly, or monthly.

Meta Data Modelling

A Metadata model is defined as the collection of database objects (tables, columns and relationship between objects) imported from the database. When you run the report, metadata published in BI Cognos portal generates a SQL statement according to the query.

The IBM Cognos Framework manager can hide the complexity of data in the data source and also alter the way how data is shown to users. It provides a view that is easy for BI users to understand and perform analysis and reporting.

IBM Cognos Framework Manager User Interface

The following screenshot shows the IBM Cognos BI Framework Manager User Interface.

Following are the various components of the above screenshot that are explained in detail for better understanding −

Project Viewer − This pane on left side allows you to access all the existing projects in a tree format.
Project Info − This is the center pane that is used to manage objects of an existing project. This has three tabs: Explorer, Diagram and Dimension.
Properties − This pane at the bottom is used to set the value of different properties of an object in a project.
Tools − This pane on the right side provides you various important useful tools. You can perform a search, or display an object and its dependent objects, changing project language, etc.To learn cognos training visit:cognos training online

Importing Metadata from a Relational Database

For importing Metadata from a Relational Database, you map the database objects to the Framework manager objects. In the Framework Manager, you can import all the objects or you can select particular objects like tables, columns, functions, stored procedures, views, etc.

Only user defined Stored procedures are supported.

Database ObjectsFramework Manager ObjectsColumnQuery ItemViewQuery SubjectSynonymQuery SubjectProcedureQuery SubjectTableQuery SubjectFunctionProject Function

To create a metadata model, run metadata wizard from the Action menu. Select a data source connection and click the Next button. Select the check boxes for the objects you want to import.

Specify how the import should handle duplicate object names. Choose either to import and create a unique name, or not to import. If you choose to create a unique name, the imported object appears with a number.

For example − When you see QuerySubject and QuerySubject1 in your project. Then click Import.

Import statistics including a list of objects that could not be imported and a count of objects that were imported are shown.

The next step is to click on the Finish button.

After importing Metadata, you must check the imported Metadata for the following areas −

Relationships and Cardinality
Determinants
Usage property for query items
Regular Aggregate property for query items

Importing Metadata from Cognos 8 Model

In the Framework Manager, you can also import metadata from an existing Cognos 8 Model. To import Metadata from the Cognos 8 model, go to Actions → Run Metadata wizard.

Click on the Cognos 8 Model and then the Next button. Navigate to the .cpf file from Cognos 8 Model and the click on Next.

Select the check boxes for objects you want to import and then click on Next and then on Finish.

Modeling Relational Metadata

Once you import the metadata, next is to validate the objects for reporting requirement. You can select the objects that appear in the report and test them. You can create two views of the Metadata Model −

Import View
Business View

The Import view shows you the metadata imported from the data source. To validate the data as per your BI reporting, you can perform the following steps −

Ensure that the relationships reflect the reporting requirements.
Optimize and customize the data retrieved by the query subjects.
Optimize and customize the data retrieved by dimensions. You may want to store dimensions in a separate dimensional view.
Handle support for multilingual metadata.
Control how data is used and formatted by checking query item properties.

Business view is used to provide the information in metadata. You can perform calculations, aggregations and apply filters in Business view and easily allow users to build the report. You can add business rules such as custom calculations and filters that define the information users can retrieve.

Organize the model by creating separate views for each user group that reflect the business concepts familiar to your users.

Cognos — Relationships in Metadata Model

Relationships are used to create queries on multiple objects in a metadata model. Relationships can be bidirectional and without creating relationship, objects are individual entities with no use in metadata model.

Each object in metadata model is connected using primary or foreign key in the data source. You can create or remove relationships in the metadata model to meet the business requirements.

There are different relationships which are possible, some of them are −

One to One − When an instance of one query subject is related to another instance. For example: Each customer has one customer id.
One to Many − This relationship occurs when one instance of query subject relates to multiple instances. For example: Each doctor has many patients.
Many to Many − This relationship occurs when many instances of a query subject relates to multiple instances. For example: Each patient has many doctors.

Cardinality Concept

It is defined as the number of related rows for each of the two query subjects. Cardinality is used in the following ways −

Loop Joins in Star schema
Optimized access to data source
Avoid double counting fact data

While using the Relational database as a data source, Cardinality can be defined considering the following rules −

Primary and Foreign keys
Match query item names represent uniquely indexed columns
Matching query item names

The most common ways to define Cardinality is by using the primary and foreign key. To view the key information that was imported, right click on the query subject → Edit Definition. You can import many to many relationships, optional relationships, and outer joins from the data source.

Relationship Notation in Metadata Model

In the Framework manager, a relation is represented by Merise notation. The first part of this notation represents the type of join for this relationship.

0..1 represents zero or one match
1..1 represents one to one match
0..n represents Zero or no matches
1..n represents One or more matches
1 − An inner join with all matching rows from both objects.
0 − An Outer join with all objects from both, including the items that don’t match.

Creating or Modifying the Relationships

To create a Relationship or to combine logically related objects which are not joined in metadata import. You can manually create relationship between objects or can automatically define relationship between objects based on selected criteria.

To create a Relationship, use CTRL key to select one or more query items, subjects or dimensions. Then go to Action Menu → Create Relationship.

If this is a valid Relationship, the Framework manager wants to create a shortcut to the relationship. You can then click on the OK button.

Once you create a relationship after the metadata import, you can also modify the relationship or Cardinality in the Framework manager.

To edit a Relationship, click a relationship and from Action menu → click Edit Definition.

From the Relationship Expression tab → Select Query items, Cardinalities and Operators.

To create an additional Join, go to the Relationship Expression tab → New Link and Define New Relationship.

To test this Relationship, go to Relationship SQL tab → rows to be returned → Test.

Click on OK button.

Creating a Relationship Shortcut

A Relationship shortcut is defined as the pointer to an existing relationship and to reuse the definition of an existing relationship. When you make any change to the source Relationship, they are automatically updated in shortcuts. Relationship shortcuts are also used to resolve ambiguous relationship between query subjects.

The Framework Manager asks whether you want to create a relationship shortcut whenever you create a relationship and both these conditions are true.

At least one end for the new relationship is a shortcut.
A relationship exists between the original objects.

Go to Action Menu → Create Relationship.

If this is a valid Relationship, Framework manager wants to create a shortcut to the relationship. Click YES. A list appears of all relationships in which one end is a model object and the other end is either another model object or a shortcut to another model object.

Click OK.

Create a Query Subject

A query subject is defined as a set of query items that have an inherent relationship. A query subject can be used to customize the data they retrieve using a Framework Manager.

The following are the query subject types in a Framework Manager −

Data Source Query Subject − These are based on the Relational metadata defined by the SQL statements and are automatically created for each table and view when you import metadata into model.
Note − The data source query subject references the data from only one data source at a time, but you can directly edit the SQL that defines the retrieve data to edit the query subject.
Model Query Subjects − They are not directly created from a data source but are based on the query items defined in other query subjects or dimensions. Using the model query subject, it allows you to create more abstract and business view of data source.
Stored Procedure Query Subjects − They are created when a Procedure is imported from a Relational data source. IBM Cognos Framework Manager only supports user defined Stored Procedures and system stored procedures are not supported.

How to create a Data Source Query Subject?

From Actions Menu → Create → Query Subject.

Enter the name of a new Query Subject.

Click on Data Source → OK to open new Query Subject wizard.

Follow the steps till the Finish button appears → Finish

Right click on Query Subject → Edit Definition. Click on the SQL tab → Available database objects box, drag objects to the SQL box.

You can also insert a data source reference, insert a macro, embed a calculation and embed a filter.

Select the actions from the list and click OK.

PurposeActionGranularity ControlDeterminants TabTo test query subjectTest TabTo view SQLQuery Information TabTo view system tables from data sourceShow System Objects check box

Edit SQL

When you edit any Relation database source, create or query a Relation database, then SQL is used in the background. You can use the following options −

Cognos SQL
Native SQL
Pass through SQL

To edit SQL of the model query subject, copy SQL from query Information tab and paste to the new data source query subject. It is possible to convert a model query subject to data source query subject.

Click Data Source query subject and Action menu → Edit Definition.
Click on SQL button, drag objects or type in SQL you want.
Click OK.

Change Type of SQL

You can select the type of SQL to be used when you define data source query subject. These factors should be considered while considering type of SQL −

SQL TypeAdvantageDisadvantageCognos SQL

Improved performance

Work on all supported database

Non Standard SQL not supportedNative SQL

Performance Optimized

Specific to Database

SQL doesn’t work on different database.

You can’t use SQL that data source doesn’t support for subqueries.

Pass Through SQLAny SQL supported by database

No option for Framework

Manager to optimize

performance automatically

Also note that it is not possible to change the type of SQL for query subjects based on the OLAP data sources.

To change SQL type, go to Query subject you want to change.

Go to Actions menu → Edit Definition and go to Query Information button.

Go to Options → SQL Settings tab.

To change the type of SQL, click on SQL Type List. Then, click OK.To more info visit:cognos controller training

What are the Basic concepts in cognos