Creating Data Sources For Different Database In Cognos

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_projectclick 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 namenext type Microsoft SQL server (OLIBP) nextserver 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 spacecreate select calculation name plane_revenuedefine 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 testclose 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 productdrag 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 hierarchyselect th member option

Select unique level

Select add levelrename 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 spacecreate select measure dimension expand order_details query subject sheet all the required measures and drop on measures and down