IBM Cognos BI Course Details

Welcome to the Cognos Tutorials by Cognos Experts. The intent of these tutorials is to provide in depth understanding of Cognos Tools.

Introduction to IBM COGNOS

Data Base:

It is a collection of inter related data (or) it is a collection of objects

 

OLTP DATABASE: (original transactional processing)

It is used to perform day to day transaction (or) it is used to load day to day transaction data.

 

OLAP DATABASE: (online analytical processing)

It is used to perform analysis by maintaining all the historic data

 

DATA WARE HOUSE TOOLS:

Data ware house tools are categorized into two types

  1. ETL tools
  2. Reporting tools (or) OLAP tools(or) BI tools

ETL tools:

Used to perform extraction transformation and loading

The following are the Etl tools available in the mark etl

  1. Informatica 2. Data storage 3. Abinitio 4. ODI (oracle data integrator)
  2. BODS (business objects data services) 6. SSIS (sequel server integrator services)

Reporting tools:

  • Used to generate reports to perform analysis
  • The following are the reporting tools
  1. IBM cognos
  2. SAP BO (business objects)
  3. Plicro strategy
  4. OBIEE (oracle business intelligence)
  5. Hyperlan
  6. Qlik view

 

Fathers of data ware house:

  1. Raiph Kim ball
  2. WH inmon
  • According to Ralph Kimball, a data ware house is also a database specify designs for analysis per pase but not for running the business
  • According in to W.H inmon a data ware house is a time variant, non volatile subject oriented and integrated database

 

Naming conventions for synonyms for data ware house: 

  1. DSS : decision supporting system
  2. Historical database
  3. OLAP database
  4. BIS (business intelligence system)

 

Data ware housing: (imp)  

It is a process of

  • Constructing the data ware house (or) building the data ware house (or) designing the DWH schema (database modeling (or) data base architects.
  • ETL (ETL developers)

 

Screenshot_1

 

Data Acquisition

It is a process of ETL

 

 

Architecture of ETL:           

 

Screenshot_2


Pre request installing & Roles in cognos BI

Roles on cognos side:

 

1.  Cognos architect role:

  • Meta data modeling as per the report requirement
  • Creating projects, creating models, creating packages

 

2.  Cognos administration roles: 

  • Installations and configurations, creating users, assigning permissions and privileges to users and taking the backups, tuning the servers, providing security to the objects.

 

 3.  Cognos report developer:

  • Developing the report by going through RRT (report requirement template)

 

RRT:  (report requirement template)

It is a document which contains all the required information to develop a report application

  • Cognos is a reporting tool (or) BI tool (or) OLAP tool
  • It is a product of IBH
  • It is a client server technology
  • It is a GUI based tool (graphical user interface)
  • IBM cognos BI is a completely web based dynamic business intelligence reporting solution from IBM

 

Pre request installing cognos:

  1. Operating system – windows xp professional
  2. Database – oracle, windows SQL server
  3. Web server – IIS
  4. Cognos software

 

  • Where we install cognos the following two types of components
  1. Windows based component
  2. Web based component

Windows based component: 

  1. Frame work manager
  2. Map manager

Web based manager:

  1. Cognos connection
  2. Report connection
  3. Query studio
  4. Analysis studio
  5. Event studio

 

Query subject:

  • In cognos terminally a table is called as query subject
  • By default every query subject is associated with a query with feature the actual data from database at the time of execution
  • Query subjects are two types

 

  1. Dimensional query subject
  2. Fact query subject

 

Dimensional query subject:

A query subject which contains any identifiers and attributes without any measures is called a dimensional it is symbolicallyScreenshot_3

Fact query subject:

A query subject which contains measures along with dimensions is called a fact query subject.

It is symbolically represented withScreenshot_1

 

Query items:

Columns are called as query items in cognos query items are called categorized in to 3 types

  1. Identifiers
  2. Attributes
  3. Measures

Identifiers:

Key, columns, data columns

 

Attributes:

Descriptive columns

 

Measures:

Key performance indicators

Note:    The default aggregation for measure is “sum”

Eg:    at database level; customer (table)

At frame work manager level: customer is called as query subject

 

Customer:

Custno, cutname, cust_age, cust_add, mail_id, Qty, rename

(Query items)

Identifier: cust no

Attributes: cust names, cust_age, cust_address, mail_id

Measures: Qty rename.

 

Package:

A package is created by copying all the objects required from the project

  • Provide the security to the project before publishing in to content store( database)

 

Reporting Studio in IBM Cognos

Report studio or author advanced reports:

  • It is a web based component used to create professional reports or more advanced reports or complete reports
  • Use browser to access report studio
  • The following types of reports can be created by using report studio
    • List reports
    • Cross tab reports
    • Chart report s
    • Repeater table reports
    • Dash boards
    • Active reports
    • Balance reports to create dashboards
    • Map reports ……etc.
  • Folders are used to organize the reports
  • There are two types of folders in cognos
    • Public folder (default)
    • My folder
  • A report which is saved in a public folder can be accessed by any folder
  • A report which is saved in my folder is specific to that particular user

 

Components of reports studios GUI:

Inter table objects pane:

It contains the following there tab

  1. Source tab (default) : to display package items
  2. Data items tab : to display query processed items
  3. Tool Box : to display the predefine objects of report studio GUI

Properties pane:

It is used to set the required properties of the selected item in the work area

It displays the properties of selected item in the work area

Work area:

It is used to create report applications

By default work area contains a report package

Report package contains

  1. Page wader: use page wader to display the titles, logos
  2. Page footer: use page footer to display page number date, time
  3. Page body : use page body to create report

Explorer bar:

It is a vertical bar between insert able objects pane and work area

It contains the following 3 types of explorer

  1. Page explorer : to explore different pages of a report like report page, prompt page
  2. Quarry explorer: it is used to explore the quarries of a report
    1. Use quarry explorer to use set operations like union, join minus, intersection, etc
  3. Condition explorer: it is used to perform conditional formatting

Ancestor button:

It is used to display the data in the form of rows and columns (tabular format)

List report contains:

  1. List header: display column titles
  2. List body : display actual data items

EX: create list report with order year, order month, quantity and revenue

  • Open the browser IE type the following URL http:// localhost/ibm cognos
  • From welcome page select author advanced reports (report studios)
  • Select the package go sets and relevance
  • Select the create name arrrowselect list arrrow click on ok
  • Expand orders query subject drag order year and quantity and revenue and drop on list report in the work

 

Saving a report:  

  • From file menu click on save
  • Click on create name folder
  • Enter the required name arrrowok arrrow double click on the folder
  • Enter the name under the folder arrrow click on save
  • Report created

 

Executing a report by using report studio:

From menu bar, select run menu click on run report time 

 

Aligning the report:

  • Use horizontal alignment and vertical alignment properties to align a report
  • Select page body
  • From properties pane select horizontal alignment arrrow enter
  • From vertical alignmentarrrowmiddle

 

Eliminating duplicate values:

  • Use group option to eliminate duplicates

Note:

Duplicates are because of one too many relationships between the items in the report

  • If there is n items which are having one too many relationship then ‘n-1’ items from top will display duplicates

 

Procedure to eliminate duplicates:

  • Select the order year data item in the list report
  • From structure menu select group/ungroup

Note:

Displaying all records in the single page

  • Select run menu select run option
  • Set rows per page ‘0’ arrrow click on ok

 

Setting colors and fonts:

  • Select order year column title cell in the list report
  • From properties window select ancestor Screenshot_3 button
  • Select list columns titles style
  • From properties pane, select font property arrrow click on browse
  • Select required font size, style, family, weight
  • Select for ground color arrrow select required color arrrow ok
  • Click on apply arrrow click on ok

 

Calculating group totals:

Use aggregate functions to perform group calculations

  • Select quantity and revenue in the list report
  • From data menu summaries arrrow “total”

 

Creating sections:

  • Select order year dsa item in the list report
  • From structure menu arrrow  select ‘section’

 

Setting page break:

  • Select order year section in the report
  • From structure menu arrrow select ‘set page break’

 

Merging data cells

  •  Cognos allows the user to combine multiple data cells in to a single data cell
  • Use ‘ block object’ to merge data cells

Eg:

Create a list report with order year, order method, quantity and revenue

  • From tool bar click on ‘unlock’Screenshot_4
  • From insert able object pane arrrow select tool box
  • Drag block objectarrrow drop on order year data item in the
  • Select order method data item, drag and drop the block
  • Select order method data cells from tool bar click on delete
  • Select order year column title from tool bar click on delete
  • Select order year column title from tool bar arrrow delete
  • From tool box, drag a text item, drop on the title column call
  • Enter column name as order_info arrrow ok

 

Data format:

  • Congnos allows t format dates, numbers, currency, time, text_etc
  • Use data format property to format the data

EX:

Create list report with order date, order method, qnty, and grass profit

  • Select order date data item in the list report
  • From properties pane, select data format property
  • Click on browse arrrow format type arrrow date
  • Set the following properties
  • Data style –arrrow full
  • Data ordering arrrowD.M.Y arrrow ok

 

Report filters:

  • A filter which is created at the time of generating the report is called report filter
  • A report filter is specific to that particular report
  • Report filters are two types
    • Detail filters
    • Summary filters

 

Detail filters:

  • Detail filter can be created for attributes as well as measures
  • In case of measures like qty and revenue, detail filters are categorized in to 2 type
  • Before auto aggregation
  • After auto aggregation

“A filter which is applied for each and every detail record that is called a detail filter “

  • Detail filters are two types
    • Static filter 

 

Static filter:

A filter which is called with a static value that can’t be changed at run time it is called a static filter

Syntax:

Quarry item operator operand

Ex: 1 – year = 2009

EX: 2 – country in (‘India’, ‘USA’, ‘UR’)

EX: 3- year not in (2005, 2006)

 

Static filter for attributes:

Create a list report with list report quty, and revenue group on order year total in qty and revenue

  • From data menu, select arrrow filters-arrrow edit filters
  • Click on add Screenshot_5 arrrow advanced arrrow ok
  • From order quantity subject drag order year drop on expression definition window
  • Type= from tool bar select single value, select the required year click on insert (=2006)
  • Click on validate symbolarrrow click on ok
  • Click on add filter arrrow advanced –arrrow ok
  • Define the following condition

 

Detail filter for measure:

  1. Before auto aggregation :

It is a filter condition it is apply before aggregating the detail records than it is called a filter before auto aggregation

Report:

Order year. Order month qty

Year Month Day Qty
20041112
1215
1309

 

Data base level:

Filter: qty > 10 (before auto aggregation)

Eg:

Create a list report with product type, predict line, qty

  • Group on product line total on qty
  • From data menu select filters arrrowedit filters click on arrrow add
  • Select advanced arrrow ok
  • Define following condition
  • Qty arrrow 75 (drag qty from order quarry subject)
  • Click on ok arrrow before auto aggregationarrrow ok

 

Filter after auto aggregation:

If a filter condition is applied after aggregating the detail records then it is called a filter auto aggregation

Eg:  create a list report product type, qty, group of product line, total of qty

  • From data menu arrrow select filters arrrow edit filters
  • Click on add select arrrow advanced arrrow ok
  • Define the following condition
  • Qty > 100000 ( drag the qty from orders)
  • Click on ok arrrowselect after auto aggregation
  • Run the report

 

Summery filter:

  • If a filter condition is applied for group summaries then it is called summery filter
  • Use summery filters to filter the data based on group summaries

Note:

To create summery filter first create groups

Eg: create a list report product line, product type, Qty group on product line, qty

  • From data menu select filtersarrrow edit filtersarrrow summery filters arrrow add arrrow advanced arrrow ok
  • Available components drag qty drop on expression definition window
  • Type qty>300000arrrow ok
  • From scope click on browse arrrow product line arrrow okarrrow ok 

 

Parameterized filters or dynamic filters: or prompt:

If a filter condition is created with a parameter then it is called a parameterized filter

  • Parameterized filter allows the user either to select the values (S) or to enter the value (S) at run time
  • In case of parameterized filter, cognos server creates a prompt page at the time of execution to select the value or to enter the value

Syntax to create parameterized filter:

Query item operator? Parameter?

Eg: year =? Param_year? (name, param_year is parameter name which is user defined )

Create a list report order year, order name, qty, and revenue. Group on order year, total on qty and revenue

  • From data menuarrrow select filtersarrrow edit filtersarrrow add
  • Click on advanced arrrowok
  • Define following condition
  • Order year =? Param_order year?
  • Drag order year from orders query subject. Drop on expression definition window –> ok–> optional arrrow ok
  • Run the report
  • Single value means arrrow ‘=’ operator
  • Multiple values means arrrow ‘in’ operator

Eg:

Create a list report with country, product line, order method, qty, and gross profit

  • Group on country, product line total on qty and gross profit
  • From data menu arrrow filterarrrow edit filtersarrrow addarrrow advanced
  • Click on ok
  • Define following condition
  • Country =? Param_country?
  • Click on ok arrrow ok

Note:

Cognos creates a value prompt if the parameterized

  • If creates a text box prompt for numeric items
  • It creates in_range prompt for data items

 

Build prompt page:

It is used to create a prompt page with the selected items in the report

  • By default it creates parameterized filters for the selected items in the report

Eg: Create a list report with country, product line, selected items in the report

  • By defaults it creates parameterized filters for the selected items in the report

Eg: Create a list report with country, product line, order method, qty and revenue

Group on country, product line, total on Qty and revenue

  • Select country product line, data items in the list report
  • From tools menu arrrow select “build prompt page”
  • Apply filters

 

Cascading prompts:  

A prompt which takes the values from author prompt is called a cascading prompt

There are two types of cascading [prompts

  1. Single select, auto submit cascading prompt
  2. Multi selection

Note:    Auto submit works any for single server in case of multi selection, create ’re prompt button’

Ex:

Create a list report with product line, product type, product name, qty, revenue.

  • Group on product line, product type, and total on qty and revenue
  • Select product, product type, product name by using edit button
  • Select product line prompt from properties setarrrow multi selectarrrow no
  • Auto submit arrrow yes, cascading sourcearrrow name
  • Select product type prompt set the following properties
  • Multi select arrrow no, auto submit arrrow yes cascade sourcearrrow click on browsearrrow product line arrrowok
  • Select product name prompt arrrowmulti select arrrow yes
  • Auto submit arrrow no, cascade type arrrow product typearrrow ok

 

Multi selection:

Select product line prompt set the following properties multi select arrrow yes, auto submitarrrow yes, cascade source re prompt line

  • Select product type arrrow multi select arrrow yes
  • Auto submit arrrow no arrrow cascade source-arrrow product type
  • Select product name arrrow multi select arrrow yes
  • Auto submit arrrow no arrrow cascade source arrrow product type

 

Creating a re prompt button:

  • From test box drag prompt button drag beside product line prompt page
  • Select next from properties arrrow select type arrrow re prompt
  • Similarly create a re prompt button for product types prompt

 

Creating customized prompt page with a value prompt:

Value prompt:

It allows the user to select the values at the run time

  • In case of value prompt, cognos creates a prompt query which will fetch all the district values from database and displays in the value prompt
  • Value prompt is very user friendly
  • Value prompt allows the user to create radio buttons, drop down list, check box group, to add user option etc

Create a value prompt for order year by creating the prompt page manually

  • Create a list report with order year, order method, qty and gross profit
  • Group on order year total on qty and revenue
  • Select page explorer bar–> select prompt pages
  • From insert able objects pane drag a page drop on prompt page window
  • Double click on prompt page top open
  • From tool box drag a value prompt drop on prompt page
  • Create a new parameterarrrow enter the name new “param_year”
  • Click on next arrrowfrom package item arrrow order year arrrow ok
  • Operator = arrrow next arrrow
  • From tool box drag a text item drop just before value prompt in the prompt page
  • Enter the text as order yeararrrow ok

 

Creating radio buttons or check boxes group in a value prompt: 

  • Select value prompt from properties pane select “select ui” propertyarrrow radio button group

 

Displaying images: (logo)

  • Use image object to download the images to run time
  • From tool box drag image object drop on the title area
  • Select image object from properties select arrrow name arrrow”company name”
  • From properties, select the “url” property arrrow server URL (image URL path)arrrowok

 

Re creating an already existing download image:

  • Use layout component reference object to refer an existing object
  • Open report page from tool box, drag layout component reference object, drop in the title area
  • Select image arrrow ok

 

Value prompt with user options:

User “static” choice property to add user options to a value prompt

Eg: create a list report with order year, order method and qty, group on order total on qty

  • Add a prompt page to the report
  • Open the prompt page from tool box drag a value prompt
  • Create a parameter (param_year) and click on next
  • Package item (order_year)
  • Operator (=) arrrow click on next and finish
  • Drag the text item from toll box and next select order year Screenshot_6
  • Create radio button to the data body, select “UI” property and select radio ”button group”
  • Select value prompt from properties select “static choices” and click on browse then click on add
  • Use arrrowall, display arrrow all, arrrow ok
  • From page explorer select report page
  • From data menu select filters and edit filters
  • Double click on the filters
  • Define the following expression in the expression definition window

If (? Param_year? /=’all’) than ([do sales_goretailers],[orders],[order year]

Else

[go sales_go retails].[orders].[order_year] = ?param_year?]

  • Click on ok
  • Run the report

 

Text box prompt:

It allows the user to enter the value at run time

  • In case of text box cognos does not create a prompt query

Eg: Create a list report order year, order method, qty, revenue group on order year total on qty and revenue

 

Add a prompt page:

  • Open a prompt page from tool box drag a text box prompt drag on prompt page
  • Create a name parameter (param_year) arrrow select order_year.
  • Operator =arrrow finish
  • From tool box drag a text item, drop before text box prompt
  • Enter the order yeararrrow ok

Eg: Create a prompt page with 2 prompts (1 for order year, and one for country) and display the prompts 1 bellow another with messages

  • Create text box prompt for order year and value prompt for country

Procedure:

  • Create a list report with order year, country, qty, revenue group on order year total on qty and revenue

 

Add a prompt page to the report:

  • Open the prompt page
  • From tool box drag a table object, drop on prompt page
  • Drag a text item drop in (1,1) cells
  • Enter order year : arrrow click on ok
  • From tool box, drag text box prompt drop on (1,2) cell
  • Enter the parameter name (param_year)
  • Package item arrrow order year
  • Operator arrrow in arrrow click on finish
  • Drag a text item drop on (2,1) cell enter the text as select the countryarrrow ok
  • From tool box drag a value prompt drop in (2,2) cell
  • Enter the parameter (param_country)arrrow next arrrow
  • Package item arrrowcountry
  • Operatorarrrowin
  • Parameter arrrow param_country arrrownext arrrow finish

 

Add check box group in the value prompt:  

  • Select the value prompà properties arrrow select UI arrrow check group arrrow ok
  • Display the value in red color
  • Use font properly to select color

 

Select and search prompt:

It allows the order to search for the required values to select the values at run time

Eg:

Create a list report with country, product line, order method, qty, and gross profit

  • Create a page and open the prompt page
  • Enter the parameter arrrow param_countryarrrow nextarrrow
  • Package item arrrow country
  • Operator arrrow in arrrow next arrrow finish
  • Drag a text item just before prompt page and enter the name “enter the word to search for”
  • Run the report

 

Inline prompt:

A prompt which is created in the report page itself is called inline prompt

Eg: Create a list report with order year, order method, qty, and gross profit group on order year total on quantity and gross profit

  • From tool box drag a value prompt drop bellow prompt in the report page
  • Enter parameter arrrow param_year
  • Package item arrrow year
  • Drag text item enter the value sheet order year
  • Select UIarrrow check box group for promp

 

Cascading prompts:    

Create a report cascading prompts build prompt page option (use customized prompt page)

Ex: Create a list report with product line, product type, qty, revenue, product name

Group on product time, product type, total on qty on qty and revenue

  • Add a prompt page to the report and open the prompt page
  • From tool box drag a table object drop on prompt page
  • Enter number of columns 2 number of rows 3
  • Select show borders arrrow ok
  • Drag a text item drop in (1,1) cell
  • Enter the text name arrrow select the product line arrrow ok
  • Drag a value prompt, drop in (1,2) cell
  • Parameter name arrrowpram_product line arrrow next
  • Package item arrrow product line
  • Operator =arrrow next arrrow finish
  • Drag a text item drop in (2,1) cell
  • “ select product type “ (text name)arrrow ok
  • Drag a value prompt drop in (2,2) cell
  • Enter the parameter arrrow param_product type arrrow next
  • Package item arrrow product type
  • Operator arrrow = arrrow next –arrrow nextarrrow
  • Cascade source arrrow param_product line arrrow finish
  • Drag a text item, drop in (3,1) cell
  • Enter “select product name” arrrow ok
  • Drag a value prompt, drop in (3,2) cell
  • Enter the parameter arrrow param_product name arrrow next
  • Package item arrrow product name
  • Operator arrrow in arrrownext arrrow next
  • Cascade source arrrow param_product type arrrow finish
  • Select product line prompt arrrow from propertyarrrowauto submitarrrowyes
  • Select product type promptarrrow from property arrrow auto submitarrrow yes

 

Conditional formatting

It is a process of formatting the report based on given condition or expression

  • Conditional formatting provides the user quick way of analysis
  • Use condition explorer to perform conditional formatting
  • Condition explorer contains the predefined variables which are used to perform conditional formatting
  • There are three types of variables in cognos
    • String variable
    • Boolean variable
    • Report language variable

 

Variable:  

It is a temporary memory location used to hold the nature values of an expression temporarily during execution

String variable:

 

Conditional formatting – string variable:

Use string variable to hold the return values(s) of an expression which is defined with user defined values temporarily at the time of execution

Ex:

If (order year = 2004) then (1)

Else

If (order year = 2005) then (2)

Else

If (order year =2006) then (3)

 

String variable holds the value wither 1 or 2 or 3 at here, (1, 2, 3) are user defined values

Here (1, 2, 3) are user defined values.

Ex: Create a list report with order_year, order_method, qty group on order year, total on qty.

  • Select condition explorer from explorer bar.
  • Select variables from insert able object pane drag string variable, drop on variables window.
  • Define the following expression in expression definition wind
  • If ([query]. [Order year]=2004) then (1)
  • else
  • If ([query]. [Order year]=2005) then (2)
  • else
  • If ([query]. [Order year]=2006) then (3)
  • From variables arrrow select string1 arrrow properties arrrow name arrrow string_year
  • From values window click on add Screenshot_8
  • Values = 1 arrrow ok
  • Similarly add the values 2 and 3
  • Select page explorer arrrow select report page1
  • Select order year in the list report.
  • From ansister select list columns body style.
  • From properties pane arrrow select style variable arrrow click on browsearrrowfrom drop down list arrrowselect string_year.
  • Select condition explorer, select the value1 from string_year.
  • Set the required color and font by using font properties.
  • Select condition explorer, select the value2 from string_year.
  • Set the required color and font by using font property.
  • Select condition explorer, select the value3 from string_year.
  • Set the required color and font by using font property.
  • Double click on explorer bar to turn off.
  • Run the report.

Conditional formatting – Boolean variables:

Boolean variable is used to hold the return values of a Boolean expression temporarily at the time of execution.

  • It can hold the values either ‘yes’ or ‘no’.

Ex for Boolean expression:

  • Qty > 10000

Ex: Create a list report to display the records which are having more than 1 lakh in green color and display the records which are having Qty below 1 lakh in red color.

Procedure:

  • Create a list report with order year, order method, qty group on order year total on qty.
  • Select condition explorer, select variables.
  • Drag Boolean variable drop on variable window.
  • Define the following expression.
  • [Query1]. [Query] > 100000
  • Click on validatearrrow click on ok
  • Open report page from page explorer
  • Select page1
  • Select qty data item in the list report.
  • From properties pane, select style variable
  • Click on browse from dropdown list arrrow Boolean 1 arrrow ok
  • Select condition explorer, select the value ‘Yes’
  • Set the required green color by using font property.
  • From condition explorer, select the value ‘No’
  • Set red color by using font property.
  • Double click on explorer bar to turn off
  • Run the report.

 

Conditional formatting – Report language variable:

Use report language variable to hold the language names temporarily during execution.

  • Display the report in red color when report is executed in Chinese language. Display the report in green color when report is executed in Dutch language. Display the report in maroon color when report is executed in English language (default)
  • Create a list report with country, product line, order method, qty and revenue
  • Group on country and product line total on qty and revenue.
  • Select condition explorer —arrrowselect variables.
  • Drag report language variable drop on variables window.
  • Select the languages Chinese and Dutch and English.
  • Open report page from page explorer.
  • Select any item in the list.
  • From assister arrrowselect list.
  • From properties arrrow select style variable.
  • Click on browse arrrow from variables arrrow report languages.
  • From condition explorer select the value ‘Chinese’.
  • Set the required color and font by using font property.
  • Similarly set the color and font for the remaining languages.
  • Double click on explorer bar to turn off.

 

Executing a report the required language:

  • From run menu arrrow run options
  • Language arrrowChinesearrrow ok
  • Run the report.

 

Drill through:

It is a process of navigating from one report to another report (or) navigating from one place to another place in the report by creating ‘hyperlinks’.

  • Use drill through property to create hyperlinks.
  • Which allows to perform drill through

Navigating from report to another report (or) Navigating from master to detail:

Master report(or)summary report

 

Screenshot_15

 

Procedure:

Master report:

Create a list report with country, qty, revenue save the report with the name Couuntry_Master.

 

Detail report:

  • Create a list report with country, city, staff name, product line, qty and revenue. Group on country, city, staff name.
  • Total on qty and revenue.
  • From data menu arrrow select filters arrrowedit filters
  • Select advanced arrrow click on ok
  • Add the following condition.
  • Country =? Param_country?
  • Click onarrrow ok
  • Save the report with the name country_details.

 

Creating hyperlinks for country data item in the master report:

  • Open country_master report.
  • From file menuarrrow open arrrowcountry_master arrrowopen
  • Select country data item in the list report.
  • From properties pane arrrow select arrrow drill through definition.
  • Click on browse arrrow click on add Screenshot_8
  • From report click on browse arrrowCountry _Details.
  • Click on open
  • From parameter click on edit (symbol)
  • Enter the following information.
  • Name – Param – country
  • Type – string
  • Required – Screenshot_2 Yes
  • Multi select – none
  • Method – data item value
  • Value – country
  • Property to pass arrrow default
  • Click on ok arrrowok
  • Save the report and execute the report.

 

Staff – details Report:

  • Create a list report with staff name, city, address, work phone, fax and email.

Add a parameterized filter for staff name with the following condition.

Staff name? Param staff name?

  • Save the report with the name staff.details

 

Creating hyperlinks for staff name in country details report:

  • Open country – details report.
  • Select staff name data item in the country – details list report.
  • Right click arrrow drill through definition.
  • Click on add Screenshot_8
  • Report arrrow staff – details arrrow open
  • Click on edit (symbol)
  • Method – pass data item value.
  • Value – staff name
  • Click on ok arrrowok
  • Save the report and execute the report.

 

Creating hyperlink for text item:

  • Open country – detail report.
  • From tool box, drag text item drop in the title area.
  • Enter the name as ‘Back’
  • Click on ok
  • Select text item back arrrowright click drill through definition arrrow click on add.
  • From report arrrow country – master arrrow open arrrow ok
  • Save the report.

 

Book Marks:

It is used to navigate from one place to another place within the report.

  • Create a list report with country qty and revenue.
  • From toolbox, drag a list object drop below first in the report page.
  • Drag country, city, product line, order method, qty and revenue drop on second list (below)
  • Group on country, city, product line total on qty and revenue in the second list.
  • Select country item in the second list from “structure” menu click on “section”.

 

Note: Unlock the report to place a book marks in the report page.

  • From tool bar click on unlock.
  • From tool box, drag a book mark drop beside country section in the report page.
  • Select book mark from properties arrrow source typearrrow data item value.
  • Data item valuearrrow country
  • Select country data item in the first list.
  • Right click, select drill through definition.
  • Click on add Screenshot_8
  • Select book mark
  • Source type arrrow data item value.
  • Data item arrrow countryarrrow ok
  • From tool box drag a book mark drop in the title area.
  • From tool box drag a text item, drop beside country book mark.
  • Enter the text as ‘Top’ arrrow ok arrrow create hyperlink
  • Select title book mark (Top) from properties source type ‘Text’
  • Label arrrowTop arrrow ok arrrow “Create hyperlink” for top text item.
  • Save the report with the name book mark – report.

 

Executing the report in the background by using IBM Cognos connection:

  • From welcome page select “My home”
  • Browse to location of the report.
  • Select gosales and retails arrrow batch 8am – reportsarrrow book mark report.
  • From condition select run with options Screenshot_9 for book mark report.
  • Format arrrow HTML
  • From delivery arrrowselect save the report.
  • Click on run arrrow ok
  • Select refresh button to refresh IBM Cognos connection page to view the output.
  • From action arrrow select “View the output versions”
  • Select the HTML

 

Master detail relationship:

It is used to create a relationship between master data and detail data based on common item.

  • There should be a common item between master layout and detail layout to create master detail relationship.
  • Master detail relationship is used to filter the detail data based on master.

 

Note: Select any item in the detail layout to enable master detail relationship option.

EX: Create a list report with order year, qty and revenue from tool box drag a list object drop beside revenue in the first list report.

  • Drag order year, country, product line, qty and revenue drop in the Inner list (detail list)
  • Group on order year, country in the inner list.
  • Total a qty and revenue.
  • Select order year in the inner list.
  • From data menu arrrow select ‘Master detail relation ships’
  • Select ‘New link’ [New link]
  • Select order year from query 1 and query 2
  • Since order year is common item in this query
  • Click on ok arrrow run the report.

 

Report level calculations (or) report calculations:

  • Report studio allows the user to device new calculations objects from already existing objects at the time generating the report.
  • These calculations are called as report level calculations
  • Report calculations are non reusable
  • There are 2 types of report calculations
  1. Query calculations
  2. Layout calculations

 

Query calculations:

If a calculation is derived by defining an expression at query level is called a query calculation.

  • Query calculation object will be display separate column in the report.
  • Use already existing item data source to device query calculation objects.
  • Use query calculation object from tool box to perform query calculations.

Ex: Derive a new query calculation with the name plane – revenue by multiplying qty and unit sale price.

  • Create a list report with order year, order method, qty and unit sale price.
  • From tool box drag query calculation object drop beside unit sale price in the list report.

Name arrrow plane – revenue arrrow ok

Define the following expression in the expression definition window.

[Go sales_go retailers]. [Orders]. [Quantity] * [go sales_go retails]. [Orders]. [Unit sale price]

Click on valid date arrrow click on ok

 

Calculating plane – revenue in terms of rupees:

  • Drag a query calculation object drop beside plane_revenue in the list report.
  • Name arrrow plane_rev_in_rs arrrow ok
  • Select data items symbol.
  • Drag plane_revenue drop on expression definition.
  • Define following definition.
  • [plane_revenue] * 60

Converting dollar $ into Indian rupees Screenshot_10:

  • Use data format property to format currency.
  • Select plane_rev_rs in the list report.
  • From properties pane select data formats.
  • Click on browse.
  • Format type arrrow currency
  • Set the following properties
  • Currency –> RP(INR) – India rupee
  • Currency symbol arrrow Rs
  • Currency symbol position arrrowbeginning
  • No of decimal places arrrow 0

 

Err: Derive a new query calculation object with the name staff_name by concatenating first name and last name.

  • Don’t display first name and last name in the report.
  • Create a list report with order year, order method, revenue.
  • From toolbox drag query calculation object drop in between order method and revenue.
  • Name arrrow staff – namearrrow ok
  • Define the following expression.
  • First name (double click) ll “ll last name.
  • ll arrrow means (concat operator)
  • Select concat operator from functions tab, operator’s folder.

 

Ex: Derive new calculation objects with the names qty_in_2004, qty_in_2005 and variance country wise and display the countries (or) highlight the countries which are having the variances less than ‘0’

  • Create a list report with only country from tool box drag a query calculation object ——————
  • Name arrrow qty_in_2004 arrrow ok
  • Define following the expression.
  • If ([order year] = 2004) then ([Quantity])

else

(0)

  • Click on validate arrrow click on ok
  • Similarly calculate qty_in_2005
  • By defining following expression
  • If ([order year] = 2005) then ([Quantity])

else

(0)

  • Click on validate –arrrow click on ok
  • Drag query calculation object drop beside qty_in_2005 in the list report.
  • Name arrrow variance arrrow ok
  • Define the following expression.
  • [Qty_in_2004] – [Qty_in_2005]
  • Qty 2004, Qty 2005 drag from data item tab.

 

Highlighting countries which are having variance Less than ‘0’:

  • Select conditional explorer arrrow variables.
  • Drag Boolean variable drop on variable window.
  • Define the following expression.
  • [Quantity].[Variance] < 0 arrrow ok
  • From page explorer select report page.
  • Select variance in the list report.
  • From ansister arrrow list columns body style.
  • From property arrrow style variable arrrow click on browse.
  • Variable –arrrow Boolean 1 arrrowok
  • From conditional explorer select the value ‘Yes’
  • Set the red color by using font property.
  • Turn off explorer bar by double click explorer bar
  • Run the report.

 

Ex: Display top 5 countries revenue wise

  • Create a list report with country, revenue from toolbox drag a query calculation object. Drop beside revenue in the report.
  • Enter the name ‘Rankarrrow ok
  • Select functions tab expand ‘summaries’ functions
  • Drag rank function drop on expression definition window.
  • Drag the revenue drop on expression window beside rank function.
  • Rank (revenue)
  • From data menu select filters, edit filters.
  • Click on add Screenshot_8
  • Select advancedarrrow ok
  • Select data items tab drag the rank drop on expression window.
  • [Rank] < 6
  • Validate arrrow ok
  • Select ‘after auto aggregation’ arrrow ok

 

Layout calculations:

It is a process of calculating the layouts like displaying current dates as title, current time as title at run time.

  • Use layout calculation object to perform layout calculations.

Ex 1: Display the selected country name as a title at run time.

  • Create a list report with country, product line, order method, qty and revenue.
  • Click on qty and revenue.
  • Add a parameterized filter for country with the following condition.
  • Country =? Param-country?
  • From toolbox drag layout calculation object, drop in the title area.
  • Select parameters tab drag the parameter (param_country) drop on expression definition window.
  • Param display value (param_country) arrrow ok

 

Displaying report execution data (current data) as a title:

  • From toolbox drag layout calculation object, drop in the title area.
  • Select functions tab, expand report functions.
  • Drag the function “as of date” drop on expression definition window arrrow ok

 

Displaying both countries, current data one below in title area in runtime:

  • From toolbox drag a table object drop in title.
  • Enter 2/2 columns (2) rows (2) arrrowDrag a text item “country name”
  • Drag a text item (2,1) cell “current date”
  • Drag a layout calculation drop in (1,2) cell
  • Select param_country, drag the expression definition windows.
  • Drag a layout calculation drop ou (2,2) cell
  • Drag “as of date” function drop on expression definition window.

 

Crosstab Report:

It is a best report for compare to analysis crosstab report displays the data in the form of rows and columns and measures.

Every crosstab report contains

  • Row Zone
  • Column Zone
  • Measure Zone

Note: Intersection of row and column is measure symbolical representation of a cross tab

 

Screenshot_16

 

 

Types of crosstab:

1.    Basic cross tab (or) simple crosstab

 

Screenshot_17

 

2.    Nested cross tab

  1. Nested on rows
  2. Nested on columns

 

    1. Nested on rows

     

 

Screenshot_18

 

  1.  Nested on columns

Screenshot_19

 

3.     Union crosstab (or) Discontinuous crosstab

  1. Union on rows
  2. Union on columns

 

  1. Union on rows

Screenshot_20

 

b.  Union on columns

Screenshot_21

 

4.   Asymmetric crosstab

Combination of both nested and union

Screenshot_22

 

Screenshot_23

 

Ex: Create a simple crosstab report writer order flag on rows, Order method on columns and sequence on measures.

  • Open report studio with go sets and retailers package
  • Then select crosstab arrrow ok
  • Drag order year, drop on columns
  • Drag order method, drop on columns
  • Drag columns, drop on measures
  • Select the measure zone, select the aggregate function total.
  • Run the report.

Note: Displaying the grand total just below columns heading in cross tab.

  • Select total in a cross tab drag and drop below revenue (where the big line is blinking)

 

Nested crosstab:

  • Create a crosstab with order year, order month on rows, order method on columns, grass profit on measures.
  • From file menu select new select crosstab arrrow ok
  • Drag order year, drop on rows.
  • Drag order month, drop beside order year on rows.
  • Drag order method, drop on columns.
  • Drag revenue, drop on measures.
  • Select measures zone and select aggregate function total

 

Creating a space between 2 data items in a crosstab:

  • From toolbox drag a crosstab space object, drop in between order year, order month items in the crosstab.
  • Use different filters, prompt pages (name / yourself)

 

Union crosstab:

  • Create a new crosstab report drag order year, drop on rows.
  • Drag order month, drop below order year on rows.
  • Drag product line, drop on columns.
  • Drag revenue, drop on measures.
  • Create a space between order year and order month.

 

Chart Reports:

  • Use chart reports to display the data in the form of graph.
  • Chart allows the user to represents the data in a pictorial way or a graphical way.
  • The following of types charts.
  1. Column chart
  2. Bar chart
  3. Line chart
  4. Pie chart
  5. Gauge chart ….etc
  • Column chart and pie chart are most frequently used charts in real time.
  • Create a column chart with order year on categories (X-axis), order method on series and requires on measures (Y-axis)
  • From file menu select new select chart arrrow column , chart arrrowok
  • Drag order year drop on categories (X-axis).
  • Drag order method drop on measure (Y-axis)
  • Run the report.

 

Hiding the values in a column chart:

  • Select column chart from properties select tooltip property arrrow hide, yes means(show)

 

To increase (or) decrease a chart:

  • Select chart report from property, select size and overflow arrrow click on browse. Height 6 in, width 5 in. arrrow ok

Pie chart:

  • From file menu select new arrrow chart arrrowok
  • Select pie donut arrrow select the chart arrrow ok
  • Drag order year, drop on categories (pies)
  • Drag order month, drop on series (pies slices)
  • Drag revenue, drop on default measure

 

Displaying the measure value per each and every pie outside with lines:

Select pie chart from properties.select values arrrow outside slices with lines

 

Displaying the values internal of percentage:

Select pie chart from properties, select value representation arrrow present

 

Sharing the same information between multiple layouts:

(List, crosstab, chart):

Sharing the information between the layouts improves the performance.

  • Create a list report with order year, order method send qty.
  • From toolbar drag a crosstab, drop list layout in the report page.
  • Select anywhere in the crosstab.
  • From properties select arrrow query arrrow Query
  • Select data items tab (Icon)
  • Drag order year from query, drop on crosstab rows.
  • Drag order method, drop on measures in crosstab.
  • From tool box drag a chart drop below crosstab in the report page.
  • Select chart from properties select.
  • Query arrrow query 1
  • Select data items tab (Icon)
  • Drag order year from query 1 drop on categories.
  • Drag order method, drop on series.
  • Drag qty drop on default measures in pie chart.

 

Conditional Blocks:

  • Use conditional blocks the required layout based in the condition satisfied.
  • Use block variable incase of conditional blocks.

Ex: Create a list report with order year, order method, and qty. group on order year total on qty.

  • From tool box drag a crosstab drop below list.
  • Drag order year drop on rows.
  • Drag product line drop on columns.
  • Drag grass profit drop on measure.
  • From tool box drag a chart drop below crosstab
  • Select column chart click on ok.
  • Drag order year drop on categories.
  • Drag order month drop on series.
  • Drag revenue drop on measures.

 

Creating a value prompt with user defined values:

(List, crosstab, chart):

  • From page explorer select prompt page.
  • Drag a page drop on prompt pages.
  • Double click on prompt page.
  • From tool box drag a value prompt drop on prompt page.
  • Enter the parameter arrrow param_layout arrrow next.
  • Click on finish.
  • Drag text item drop before value prompt.
  • Enter the text as “select the layout” arrrow ok

 

Create radio buttons in the value prompt:

  • Select value prompt from properties select UI arrrow radio button group.

 

Adding the user options to a value prompt:

  • Select value prompt from properties
  • Static choices arrrow click on add Screenshot_8
  • Use arrrow list
  • Display arrrow list.arrrow ok
  • Click on add
  • Use arrrow crosstab
  • Displayarrrowcrosstab arrrow ok
  • Click on add
  • Use arrrow chart
  • Display arrrow chart arrrow ok arrrow ok

 

Creating string variable:

  • Select condition explorer select variables.
  • Drag string variable drop on variables window.
  • Define the following expression in expression definition window.
  • If (param display value (“param_layout”)=’list’) then (‘A’)

Else

If (param display value (“param_layout”) =’crosstab’) then (‘B’)

Else

If (param display value (“param_layout”) =’chart’) then (‘C’)

  • Click on validate arrrow ok
  • From values window click add Screenshot_8

Enter A arrrow ok

  • Similarly add B and C arrrow ok
  • From page explorer select report page
  • From tool box drag “conditional blocks” object drop on empty space in the report page.
  • Select conditional block in the report page.
  • From properties select block variable property
  • Click on browse arrrow variable arrrow string 1 arrrow ok
  • From properties select “current block” arrrow A
  • Select order year in the list from assister select list
  • Drag the list layout, drop in the conditional block.
  • Select conditional block from propertiesarrrow
  • Current block arrrowB
  • Select any item in the crosstab from assister select crosstab.
  • Drag the crosstab drop in the conditional block.
  • Similarly de for chart layout.
  • Save the report.
  • Run the report.

Query Studio in IBM Cognos

It is a web based component used to create simple reports (or) Adhoc reports (or) self service reports (or) Instant reports.

  • It is end users (client) studio.
  • The full query is types of reports can be generated by using query studio.
  1. List report
  2. Crosstab report
  3. Chart report.

 

Ex: Create a simple list report with order year, order month, qty and revenue by using query studio.

  • Open the IE browser to access the query studio.
  • Enter the following URL http://IBM Cognos
  • From welcome page select “Query my data” (query studio)
  • Select the package go sales and retailers.
  • Drag order year, order month, qty
  • Drop on work area.

Eliminating duplicates:

  • Use group options to eliminate duplicates and to display group totals.
  • Select order year data item in the list report.
  • From menu arrrowchange layout arrrow select group

Ungroup:

It is used to restore the duplicates.

  • Select order yeararrrow ungroup

 

Collapse group:

It is used to hide (or) collapse the details of a group.

  • Select order year in the list report.
  • From change layout menu –> select collapse group.

 

Expand group:

To restore the details of a group

  • Select order year arrrowfrom change layout menu expand group.

 

Create sections:

It is used to divide a report into number of sections.

  • Select order year in the list report.
  • From change layout menu select create sections/

Set page breaks:

To create page breaks for each and every section to display single page.

  • Select order year apply set page breaks.

 

Set web page size:

It is used to set a number of rows per each and every page.

 

Edit title area:

To specifies the title name to the report.

 

Change font styles:

It is used to set the required color and fonts.

 

Pivot: It is used to connect a list into crosstab.

  • Create a list report with order year, order method, qty, and revenue.
  • Select order year from change layout menu.
  • Select pivot

 

Swap rows and columns:

It is used to convert rows into columns and columns into rows in a crosstab.

Chart:

It is an option used to convert a list (or) crosstab into a chart report.

Define conditional styles: It is used to perform simple conditional formatting.

  • Create a list report order year, order method, revenue
  • Select revenue
  • From change layout menu arrrow select define conditional styles.
  • New valuearrrow 1000000 arrrow insert
  • Stylearrrowexcellent
  • New value arrrow500000 arrrowinsert
  • Stylearrrow average
  • New value arrrow 50000 arrrow insert
  • Style arrrow poor

 

Template:

It is a predefined structure that can be reused for multiple reports.

  • Use report studio to create templates.

 

Creating a template:

  • Open report studio with gosals and vectors package.
  • From file menu select new, select template.
  • Double click to edit text ABC limited.
  • Subtitle arrrow India
  • Select page explorer arrrow click on ‘classes’
  • From global class extension arrrow list column body cell.
  • Set the required color, font by using font property.
  • Select list column title call arrrow set the required template file menu, click on save under your folder with the required name.

 

Applying template:

  • Open query studio with gosels and retailers.
  • Create a list report with country, product link, and qty and revenue order method.
  • From change layout menu select apply template
  • Apply a template
  • Select Template –> browses your required template.
  • And select the template –> ok

 

Define custom groups:

It is used to compare one element in the attribute with remaining elements in the attribute.

Ex:

  • Create a list report with order method, revenue.
  • Select order method
  • From edit data menu select define custom groups.
  • Enter the new group name arrrowemail_group arrrow add from available values arrrowemail arrrow add.
  • Create another group arrrow others_group arrrowadd
  • From available value arrrow select all values arrrow add
  • Click on ok
  • Select order method data cell arrrow click on delete.
  • Select revenue –arrrow click on ‘calculate’ option
  • Operation type arrrow percentage
  • Operation –arrrow % of total
  • Report type arrrow revenue
  • % of total

Schemas and its types in Cognos

Schemas and its types :

Schema:

A schema is nothing but a collection of objects

  • Designing a dWH is nothing but designing the schema
  • A data ware house tables are contain dimensional tables and fact tables
  • Database models follows dimensional modeling to design dWH schema
  • Dimensional modeling is a process of creating dimensional tables and fact tables with proper relation

 

Types of schemas:

  1. Star schema
  2. Snow flake schema
  3. Galaxy schema (or)integrated schema (or) constellation schema

 

STAR SCHEMA:

A star schema design is a data base design where the centrally located table is called fact table with surrounded by number of dimensional tables

  • Since the database design looks like star, it is called star schema

 

 

Format of star schema:

Screenshot_11

 

 

Dimensional tables:

  • A dimensional table contains detail information (or) descriptive information
  • Dimensions are used to describe the facts.

Fact table: 

  • A fact table contains all the key columns (PK, FK) and measures of the particular business organizations.
  • Measures are also called as facts (or) KPI’s (key performance indications)
  • fact should be a numeric but every numeric near not be a fact.

 

 

Sales schema

 

Screenshot_12

 

 

Snow flake schema:

 

Screenshot_13

 

 

 

Difference between the schema and snow flake schema:

 

Star schemaSnow flake schema
It is de normalizedRetrieval up data is faster

redundancy is there

 it is normalizedRetrieval up data is slower due to more number of icons

redundancy can be reduced

 

 

Integrated schema:

 

Screenshot_14

 

Phages of Data Acquisition in IBM Cognos

Phages in data acquisition:

  1. Data extraction
  2. Data transformation
  3. Data loading

 

Data extraction:

From various types of sources like RDBMS systems (oracle, SQL, server, tera data, sy base etc), files (txt, xml) and ERP systems like (SAP, people soft … etc)

 

Data transformation:

It is a process of transforming the data from one format to another format (client required format) by applying business lagiees

  • Data transformation is takes place in the staging area
  • Staging area is nothing but a temporary memory area where the transformation activity takes place
  • The following are transformation activity takes place in the staging area
  1. Data cleaning
  2. Data subbing
  3. Data merging
  4. Data aggregating

 

Data cleaning:

It is a process of converting inconsistent data (means UN uniform data ) in to consistent data (uniform data ) and removing un wanted data.

Customer (source) 

Cust no      cust f name               cust l name                   amt                        loc

1                           bill                         gates                           50000                  Newyork

2                           bill                         Clinton                       70000                  boston

3                           nara                      chandrababu            40000                  hyderabad

 

Customer target

Cust no                cust name                      amt        tax          loc

1                           billgates                            5000     500       Newyork

2                           billClinton                        7000    700        boston

3                          narachandrababu           4000     400       hyd

 

Screenshot_3

 

 

Staining area :

  1. Cust name= concat (Init cap (Cust name).’ ‘ , init cap(Cust name)
  2. Tax = actual

Data merging:

It is a process of combining multiple sources into a single target (single output) (or) it is a process combining multiple input flows into a single output flow there are two types of data merging

  1. union
  2. join

 

Union:

It is a process of combining multiple sources which are having same structure (number of columns should be same and the data types of the corresponding columns should be same)

  • sources can be homogeneous (same type of sources) or heterogeneous (default types of sources)

 

 

Screenshot_5

 

Join:

To perform join, there should be at least one common column between the sources, sources can be homogeneous (or) hetero genius.

Ex: source 1

 

 

Screenshot_6

 

Data aggregation:

  It is a process of converting the details data in to summary data by applying group functions like sum, max, min, avg, count.

  • It s a process of performing group calculations like sum, max min, avg , etc…

 

Screenshot_7

 

Data loading:

It is a process of populating (or) dumping the source data (OLTP) into the target system (DWH) there are 2 types of data loading

  1. initial load (or) full load
  2. incremental load (or) delta load

 

Initial load:

Leading the data for the first time from source to target is called initial load (or) full load

 

Incremental load:

It is a process of inserting new records and updating existing records when coming with new values

 

Data mart:

It is a subset of data ware house (or) it is a subject oriented database to support middle level management (or) it is a high

 

Screenshot_8

 

There are two approaches to design the data mart

  • top-down approach (W.H inman):

 

Screenshot_9

 

  • bottom-up approach(Ralph Kimball):

 

Screenshot_10

 

  • based on the design, data marts are categorized into two types
  1. dependant data mart
  2. independent data mart
  • Data mart in to down approach is dependent. Because designing of these data marts depends upon design of enterprise data mart
  • Data mart bottom approach is independent. Because designing of these data mart doesn’t depend upon enterprise data mart.

 

project Creation & Execution in Cognos Framework

 

Framework manager workflow diagram:

Slide15

 

 

Procedure to create a project:

Start the following services to work with cognos

IBM cognos

Startarrrow runarrrowservices.Mscarrrowok

Startarrrow programsarrrowIBM cognos 10arrrowIBM cognos framework managerarrrowselect create a name projectarrrowproject name arrrow locationarrrowokarrrowEnglisharrrowselect data sourcesarrrownextarrrowgo toarrrowtable’sarrrownextarrrowselectarrrowuse primary as foreign key  ( to import the tables with relations)arrrowclick on importarrrowfinish

 

Creating a project:

From project viewer panel, select packages, right click, select create, select package, name, next, select all the required objects from the modelarrrownextarrrowselect including function setsarrrowfinisharrrowyesarrrownextarrrowadd usersarrrownextarrrowpublisharrrowfinish.

 

Creating a report by using report studio:

  • Open the Browser interment explorer enter the following URL :

http:/localhost /cognos10

  • From welcome page, select author advanced report (report studio)
  • Select the package required ( B8AM sales package)
  • Select create name arrrowselect list arrrowclick on ok
  • Expand country query subject from lift can drag country code and drop on country code.

 

Executing a report: 

  • From run menu, click on run report

 

Publishing package:

  • Open framework manager with B8AM_sales_package
  • From project viewer pan. Expand packagearrrowselect the package and publish and right click on publish packagearrrow nextarrrownextarrrowpublisharrrowyesarrrowFinish

Procedure to select report studio package by using report studio:

  • Open report studio from file menu select name from package on browse(dropdown) select 8AM sales packagearrrow okarrrow select listarrrowok

 

Fitters and its types in Cognos

 Fitter:

It is nothing but a condition to restrict the data to be displayed on the report

There are two types of filters

  1. Model filter
  2. Report filter

Model filter:

If a filter is created at the time of designing model then it is called a model filter

Model filters are reusable

Report filter:

A filter which is created at the time of generating the report is called a report filter

Report filter is specific to that particular report only

  • Again model filters are two types
  1. Stand alone filter
  2. Embedded filter

 

Stand alone filter:

  • It is created at name space level it is available as a separate object in the model
  • It can be applied for any query subject in the model
  • Stand alone filters are two types
  1. Static filter
  2. Dynamic filter (or) parameterized filter (or) prompt

 

Static filter:

If a filter condition is created with a static value that can’t be changed at run time than it is called a static filter

 

Syntax to create static filter:

Query time (column) operator operand.

EX: year = 2010

EX: country in (‘India, ‘USA’)

EX: year is not in (2009, 2011)

EX: create a static filter for year 2008 at model filter

Procedure: open framework, manager with replanted project

  • From project viewer pan, select the namespace (model) right click –> create–>  filter–> create name year_2006–> expand product

 

Testing a filter:

  • Select product_fore caste query subject, right click, select edit definition
  • Select filter stop drag the filter year_2006, drop on filters window
  • Select test tab click on test sample

Parameterized filter:

If a filter condition is created with a parameter then it is called a parameterized filter (or) dynamic filter (or) prompts

It allows the user either select the value(s) (or) to enter the time of execution

Syntax to create parameterized filter:

Query item operator, Parameter?

EX: year =? param_year?

Country in? param_country?

Create a parameterize filter for country

  • Right click on the name space arrrowcreatearrrow filter arrrowname country (?) expand country_multilingual arrrow drag on the country on expression definition window arrrowok

 

Procedure  to create a folder under the namespace:

  • Right click on the “namespace” arrrow create folder
  • Folder namearrrow filterarrrow next
  • Select the required objects (filter) and click on finish

 

Embedded filter:

  • If a filter is created at query subject level than it is called an embedded filter
  • The scope of this filter is for the particular query subject

 

Procedure to create embedded filter:

Select the query subject country_mnultilingual –> rightclickarrrow select edit definitionarrrow select filter tabarrrow click on addarrrow filter  name  language_enarrrowexpression definition = ’EN’arrrowokarrrow select test tabarrrow click test sample arrrow ok

 

Creating data sources for different database in cognos

 Creating data sources for different database: 

  1. Oracle:
  • Open the browser internet explorer enter the following URL
  • http:/local host/cognos 10
  • from welcome page select administrator IBM cognos content
  • select configuration arrrow select near data source(database symbol)
  • create namearrrow next arrrow type oraclearrrow next arrrowsql net connect string ‘ORCL’arrrow user idarrrow password arrrow select test the connection
  • Click on test arrrow close arrrownextarrrow finish
  • Create a name project with the name by B8M_scott_project and import the tables from scott database
  • Open framework manager arrrow select create a name project arrrow
  • Enter project name B8m_scoot_projectarrrowclick on ok arrrowselect Englisharrrow select datasource arrrow next arrrow select B8m_oracle_datasource arrrow click on next arrrowselect scottarrrow expand tablesarrrow select EMP,Deptarrrow nextarrrow uncheck user primary foreign keysarrrow click on importarrrow finish

Procedure to create a relationship between the queries

 

Subject at model level by using FM:

Note:

There should be at least one common item between the query subjects to create relationship

  • From project viewer pan, expand the namespace
  • Select the query subjects between them relation to be created by using control button
  • Right click any selected query subjectsarrrow createarrrow relationship
  • Select dept no from both sides (since dept no is the common item in emp and dept tables)
  • Dept side 1.1 and emp side 1.n arrrow than click on ok

Creating a name data source for sql server:

  • Open the browser internet explorer enter the following URL
  • http:/local host/ IBM cognos
  • from welcome page IBM cognos content–> select configuration
  • select name data source arrrow namearrrownext arrrow typearrrow Microsoft SQL server (OLIBP)arrrow nextarrrowserver name local host arrrow database name (go sal)
  • password Screenshot_1 username_saarrrow user id_saarrrow testarrrow finish

 

Model calculations:

  • FM allows the users to define name calculation objects from already existing objects.
  • Model calculations are reusable

EX: derive a name calculation object with the name plane_name by multiplying quality and unit sal price

  • Right click on name spacearrrowcreatearrrow select calculationarrrow name plane_revenuearrrowdefine the following expression the expression defines windows
  • (quantity+until sale price) quantity and unit sal price available in order_details

EX: 2: derive a name calculation object in the name staff_full name by calculating by first name and last name

  • Right click on the name space arrrow create arrrow select calculation arrrow name stoff arrrow full name arrrowdefine the following expression

 

Embedded calculation:

It is a process of enhancing the query subject by adding the name query items

Ex: add a name query item with the name plane_ revenue to the query subject order_details

  • Right click on order_details query subject arrrow select edit definition
  • Select the default query arrrow define the following query in SQL windows as shown bellow
  • Select the query subject column drag and drop SQL windows

 

Deriving name query subject by merging multiple query subjects: 

Note: there should be at least one common column between the query subjects to merge

EX: merge the query subjects product and product_forecast to derive product_merge

From name space , select the both the query subjects by using ctrl button right click on any selected query subject arrrow select merge in name query subjectarrrow yesarrrow right click rename arrrowenter product_mergearrrow enter.

 

Creating name query subjects:

There are three types of name queries

  1. Model (query subject and query item)
  2. Data source(tables and columns)
  3. Stored procedure

 

Creating a new query subject by using model as a source:

  • Right click on the name spacearrrow createarrrow query subject
  • Enter the name country _model_query subjectarrrow model arrrow ok
  • Drg country , flag image from country and drop in query item window and required columns arrrow drag all three query subjects column arrrow ok

 

Creating a data source for oracle:

  • Open the browser internet explorer enter the following URL
  • http:/local host/ IBM cognos/
  • select configurationarrrow select near data source
  • enter the name oracle_data sourcearrrow next
  • type arrrow oracle arrrow nextarrrow orclarrrow user idarrrow passwordScreenshot_2
  • enter user id scott password tiger and conform password tiger
  • click test the configuration arrrow testarrrowclosearrrow next arrrow finish

EX: create a name project with the name scott_project and import tables emp and dept with out relation

  • create file temp

ex:  creating a name query subject by using data source as a source

  • right click on the name spacearrrow create arrrow query subject
  • name product_data source_QSarrrow datasource(table and columns)
  • click on okarrrow selects go salesarrrow nextarrrow expand go sal
  • expand tablesarrrow productarrrowdrag the columnsarrrow product_forcast
  • drag the columns in the inclided columns window
  • click on next arrrow next arrrow nextarrrow click on test sample arrrowclick on finish

 

DMR model (dimensionally modeled relations):

  • use dmr models to perform multidimensional analysis

 

Multidimensional analysis:

It is a process of viewing the data at different levels of details

  • use the following features to perform multidimensional analysis

 

Drill up:

To navigate from lowest level to highest level

 

Drill down:

To navigate from highest level to lowest level

 

Creating DMR model:

Creating DMR model is nothing but creating regular dimensions and measure dimensions

  • the source for creating DMR model related url database meta data
  • a DMR model is made up of regular dimensions and measure dimensions

 

Regular dimensions:

  • A regular dimension is made up of hierarchies
  • A hierarchy is made up of levels
  • Each and every level should be assigned with the following rules
  1. _business key
  2. _member caption
  3. _member description

Note:

Business key and caption are mandatory but description is optional

 

Note:

A regular dimension can have any number of hierarchies

A hierarchy can have any number of levels

 

Measure dimension:

It is created with all the measures (facts or KPI’s) required to perform multi dimensional analysis (drill up and drill down)

 

Scope relationship:

It is a relation between regular dimension and measure dimension

  • By default cognos creates scope relation as soon as regular dimension and measure dimension are created

 

Procedure to create regular dimension:

  • Open frame work manager with the required project
  • Right click on the namespace (model), create arrrowregular dimension
  • From hierarchies window, select add hierarchy arrrow select renamearrrow enter the name “ product hierarchy”arrrowclick on enter
  • Right click on name hierarchy (all) select rename enter the name “produce(call)”arrrowenter
  • Select add levelarrrow right click on name level sheet rename arrrow enter the new name “product_line” arrrow enter
  • Expand product _line query subject from the name space
  • Drag product_line_code, drop on product_line level hierarchy
  • Select business key, drag product_line.ex,drop on product line level hierarchyarrrowselect th member option
  • SelectScreenshot_2 unique level
  • Select add levelarrrowrename new level as “product _type”
  • Click on okarrrow right click on regular dimension select rename “product_regular_dimension”

 

Produce to create measure dimension:

  • Right click on name spacearrrowcreate select measure dimension expand order_details query subject sheet all the required measures and drop on measures and down
  • Expand invent_level drag carrying_inventory and drop on measures window arrrow click on ok
  • Rename the measure dimension as sales_measure_dimension
  • Click on diagram tab

 

Dimensions & OLAP Types in Cognos

Conformed Dimensions

It is a dimension which is shared by multiple fact tables

Ex: customer dimension, time dimension

Fact Constellation

It is a process of connecting multiple fact tables with PK-FK relationship

Slowly changing dimension (SCD)

A slowly changing is a dimensions which captures the changes occurs  over a period of time

  • there are three types of SCD’s
  1. SCD type1
  2. SCD type 2
  3. SCD type3

SCD type 1

It contain only current data

SCD type 2

It maintain couple history and current data

SCD type 3

It maintain partial history (or) recent history and current data

Surrogate key

It is a system generated key which access primary key in the dWH tools maintains the uniqueness.

OLAP and types of OLAP

OLAP

It is a interface (or) a gate way between the user and data source (dWH)

(or)

It is a set of specifications with allows the client application (report application) to query the data from data source and displays the data and to report

Types of OLAP

  1. DOLAP (desk top)
  2. ROLAP (relational)
  3. MOLAP(multi dimensional )
  4. HOLAP(hybrid)

DOLAP

It is a OLAP used to query the data from desktop database like Dbase, fox, ms access, ckpper. While generating the report online

ROLAP

It is used to query the data from multidimensional source like cube and DMR model (dimensional relational model) to perform multidimensional analysis.

Multi dimensional analysis

It is a process of viewing the data at different levels of details

  • Use the following features to perform multidimensional analysis
  1. Drill up:

Navigating from lowest level to highest level

  1. Drill down:

Navigating from highest level to lowest level

HOLAP

It is a combination of both ROLAP and HOLAP it can query the data from the relational sources as well as multi dimensional sources like cognas.

Phases in dimensional modeling

  1. Conceptual modeling
  2. Logical modeling
  3. Physical modeling

Conceptual modeling

In conceptual modeling face, database models conform or decide the dimensional tables and fact tables required for a particular data ware house design.

Logical modeling

In logical modeling database modelers start creating relation between the tables logically

Physical modeling

In physical modeling database modelers creates dimensional tables and fact with relations physically in the database


Last modified: Wednesday, 10 August 2016, 12:34 AM