Creating data sources for different database:
- 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
select near data source(database symbol)
- create name
next
type oracle
next
sql net connect string ‘ORCL’
user id
password
select test the connection
- Click on test
close
next
finish
- Create a name project with the name by B8M_scott_project and import the tables from scott database
- Open framework manager
select create a name project
- Enter project name B8m_scoot_project
click on ok
select English
select datasource
next
select B8m_oracle_datasource
click on next
select scott
expand tables
select EMP,Dept
next
uncheck user primary foreign keys
click on import
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 subjects
create
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
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
name
next
type
Microsoft SQL server (OLIBP)
next
server name local host
database name (go sal)
- password
username_sa
user id_sa
test
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 space
create
select calculation
name plane_revenue
define 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
create
select calculation
name stoff
full name
define 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
select edit definition
- Select the default query
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 select merge in name query subject
yes
right click rename
enter product_merge
enter.
Creating name query subjects:
There are three types of name queries
- Model (query subject and query item)
- Data source(tables and columns)
- Stored procedure
Creating a new query subject by using model as a source:
- Right click on the name space
create
query subject
- Enter the name country _model_query subject
model
ok
- Drg country , flag image from country and drop in query item window and required columns
drag all three query subjects column
ok
Creating a data source for oracle:
- Open the browser internet explorer enter the following URL
- http:/local host/ IBM cognos/
- select configuration
select near data source
- enter the name oracle_data source
next
- type
oracle
next
orcl
user id
password
- enter user id scott password tiger and conform password tiger
- click test the configuration
test
close
next
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 space
create
query subject
- name product_data source_QS
datasource(table and columns)
- click on ok
selects go sales
next
expand go sal
- expand tables
product
drag the columns
product_forcast
- drag the columns in the inclided columns window
- click on next
next
next
click on test sample
click 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
- _business key
- _member caption
- _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
regular dimension
- From hierarchies window, select add hierarchy
select rename
enter the name “ product hierarchy”
click on enter
- Right click on name hierarchy (all) select rename enter the name “produce(call)”
enter
- Select add level
right click on name level sheet rename
enter the new name “product_line”
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 hierarchy
select th member option
- Select
unique level
- Select add level
rename new level as “product _type”
- Click on ok
right click on regular dimension select rename “product_regular_dimension”
Produce to create measure dimension:
- Right click on name space
create 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
click on ok
- Rename the measure dimension as sales_measure_dimension
- Click on diagram tab
For indepth understanding Cognos Training click on
- Cognos Tutorials
- Introduction to IBM COGNOS
- Dimensions & OLAP Types in Cognos
- Fitters and its types in Cognos
- Schemas and its types in Cognos