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

These core tutorials will help you to learn the fundamentals of Cognos. 

For an in-depth understanding and practical experience, explore Cognos Online Training

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

For indepth understanding Cognos Training click on