CognosAbout the TutorialIBM Cognos Business intelligence is a web based reporting and analytic tool. It is used toperform data aggregation and create user friendly detailed reports. IBM Cognos providesa wide range of features and can be considered as an enterprise software to provide flexiblereporting environment and can be used for large and medium enterprise.Cognos also provides you an option to export the report in XML or PDF format or you canview the reports in XML format.AudienceIBM Cognos provides a wide range of features and can be considered as an enterprisesoftware to provide flexible reporting environment and can be used for large andmedium enterprises. It meets the needs of Power Users, Analysts, Business Managersand Company Executives.Power users and analysts want to create ad-hoc reports and can create multiple views ofthe same data. Business Executives want to see summarize data in dashboard styles, crosstabs and visualizations. Cognos allows both the options for all set of users.PrerequisitesIBM Cognos Business Intelligence is an advanced topic. Even though the content has beenprepared keeping in mind the requirements of a beginner, the reader should be familiarwith the fundamentals of running and viewing reports or manage schedules, portal layouts,and other users' permissions before starting with this tutorial.Disclaimer & Copyright Copyright 2016 by Tutorials Point (I) Pvt. Ltd.All the content and graphics published in this e-book are the property of Tutorials Point (I)Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republishany contents or a part of contents of this e-book in any manner without written consent ofthe publisher.We strive to update the contents of our website and tutorials as timely and as precisely aspossible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt.Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of ourwebsite or its contents including this tutorial. If you discover any errors on our website orin this tutorial, please notify us at [email protected]

CognosTable of ContentsAbout the Tutorial . iAudience. iPrerequisites . iDisclaimer & Copyright . iTable of Contents . iiDATA WAREHOUSING . 11.Data Warehouse Overview . 2Features of a Data Warehouse . 3Architecture of Data Warehouse . 4Characteristics of a Data Warehouse . 5OLTP vs OLAP. 5Data Mart Vs Data Warehouse. 7Fact vs Dimension Table . 82.Data Warehouse Schemas. 9Star Schema . 9Snowflakes Schema . 10Fact Constellation Schema (Galaxy Schema) . 113.Data Warehouse – ETL & Reporting Tools . 12Extraction . 12Transform . 13Load . 13BI Reporting Tool . 13COGNOS BASICS . 144.Cognos – Introduction. 15Key Features of IBM Cognos. 15Cognos Versions . 17Cognos vs Other BI Reporting Tools . 18IBM Cognos vs SAP Business Objects . 195.Cognos Components and Services . 20Gateways . 20Application Tier Components . 21Content Manager . 216.Cognos – Connections . 22Connecting Different Data Sources . 22Creating a Data Source Connection in IBM Cognos . 23Data Source Security Setup . 27ii

Cognos7.Cognos – Packages . 28How to Create a Package? . 28Scheduling Reports in IBM Cognos . 298.Cognos – Framework Manager . 32What is a Framework Manager? . 32Metadata Modeling . 32IBM Cognos Framework Manager User Interface . 33Importing Metadata from a Relational Database . 349.Cognos Relationships in Metadata Model . 37Cardinality Concept . 37Relationship Notation in Metadata Model . 38Creating or Modifying the Relationships . 38Creating a Relationship Shortcut . 40Create a Query Subject. 40Edit SQL . 43QUERY STUDIO. 4510. Cognos – Query Studio . 4611. Cognos Ad-hoc Reports . 4712. Cognos Report Types. 5013. Cognos Creating a Report . 5114. Cognos Open an Existing Report . 5315. Cognos Add Data to a Report . 5416. Cognos Saving a Report . 5517. Cognos Run a Report . 57Run a Report in PDF, XML, and CSV format . 5818. Cognos Printing a Report. 59REPORT STUDIO . 6019. Cognos – Report Studio. 61How to Create a Report in Report Studio? . 61How to Open Report Studio? . 62iii

Cognos20. Cognos Report Templates . 63List Report . 63Crosstab . 63Chart . 64Map . 65Repeater . 6521. Cognos List Report . 6622. Cognos Crosstab Report . 69Formatting a Crosstab . 7123. Cognos Creating a Chart . 7324. Cognos Report Functions . 7625. Cognos Report Validation . 7826. Cognos Report Run with Options . 8027. Cognos Report Administration . 8228. Cognos Filters. 84Deleting a Filter . 8629. Cognos Custom Calculations . 87ANALYSIS STUDIO. 8930. Cognos – Analysis Studio . 90Interface . 9031. Cognos Create an Analysis . 9232. Cognos Save an Analysis . 9433. Cognos Open Existing Analysis . 95EVENT STUDIO . 9634. Cognos Event Studio . 9735. Cognos Notification Methods . 99iv

CognosData Warehousingv

1. Data Warehouse OverviewCognosA Data Warehouse consists of data from multiple heterogeneous data sources and isused for analytical reporting and decision making. Data Warehouse is a central place wheredata is stored from different data sources and applications.The term Data Warehouse was first invented by Bill Inmom in 1990. A Data Warehouse isalways kept separate from an Operational Database.The data in a DW system is loaded from operational transaction systems like – Sales Marketing HR SCM, etc.It may pass through operational data store or other transformations before it is loaded to theDW system for information processing.A Data Warehouse is used for reporting and analyzing of information and stores both historicaland current data. The data in DW system is used for Analytical reporting, which is later usedby Business Analysts, Sales Managers or Knowledge workers for

CognosIn the above image, you can see that the data is coming from multiple heterogeneousdata sources to a Data Warehouse. Common data sources for a data warehouse includes: Operational databases SAP and non-SAP Applications Flat Files (xls, csv, txt files)Data in data warehouse is accessed by BI (Business Intelligence) users for AnalyticalReporting, Data Mining and Analysis. This is used for decision making by Business Users,Sales Manager, Analysts to define future strategy.Features of a Data WarehouseIt is a central data repository where data is stored from one or more heterogeneous datasources. A DW system stores both current and historical data. Normally a DW system stores5-10 years of historical data. A DW system is always kept separate from an operationaltransaction system.The data in a DW system is used for different types of analytical reporting range fromQuarterly to Annual comparison.vii

CognosData Warehouse Vs Operational DatabaseThe differences between a Data Warehouse and Operational Database are as follows An Operational System is designed for known workloads and transactions likeupdating a user record, searching a record, etc. However, Data Warehousetransactions are more complex and present a general form of data.An Operational System contains the current data of an organization and Datawarehouse normally contains the historical data.An Operational Database supports parallel processing of multiple transactions.Concurrency control and recovery mechanisms are required to maintain consistencyof the database.An Operational Database query allows to read and modify operations (insert,delete and Update) while an OLAP query needs only read-only access of stored data(Select statement).Architecture of Data WarehouseData Warehousing involves data cleaning, data integration, and data consolidations. A DataWarehouse has a 3-layer architecture –Data Source LayerIt defines how the data comes to a Data Warehouse. It involves various data sources andoperational transaction systems, flat files, applications, etc.Integration LayerIt consists of Operational Data Store and Staging area. Staging area is used to perform datacleansing, data transformation and loading data from different sources to a data warehouse.As multiple data sources are available for extraction at different time zones, staging area isused to store the data and later to apply transformations on data.Presentation LayerThis is used to perform BI reporting by end users. The data in a DW system is accessed by BIusers and used for reporting and analysis.viii

CognosThe following illustration shows the common architecture of a Data Warehouse System.Characteristics of a Data WarehouseThe following are the key characteristics of a Data Warehouse: Subject Oriented: In a DW system, the data is categorized and stored by abusiness subject rathe