Creating data sources for different database in cognos

Blog Author


Published Date

21st September, 2018




  • Share this blog :

 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


About Author


Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills .

Related Blogs

Write For Us