Bridge table

An example of a Bridge table would be relationship table between student_master and courses_master. A Student may participate in multiple courses and obviously in a course there will be multiple students. In this scenario to maintain relationships between student_master and courses_master we need another table which may be called Student_courses. This table will have all relationships between Students and Courses.A bridge table is a table that only contains the keys between the two tables in a many-to-many relationship. These tables typically consist of mostly key fields the primary keys of the two tables needing the many to many relationships. Sometimes there are more data elements but often there are only two columns both foreign keys.

A table which maintained to define the relationship between two different tables is known as bridge tables. For example say there are two tables one table with employee information (an employee table) and other with deparment details. Now a bridge table coming to picture to maintain the relationship between an employee with his/her department. which contains informations like which employee belongs to which department. Here is a example to understand More precisely ,this solution will help when one employee is working for different departments at the same point of time.

Employee table--------------------

EMP_CD EMP_name DOJ
1001 MR.X 12-06-2006
1002 Ms.Y 24-11-2004
1003 MS.K 25-02-2004

Department table -----------------------

DEPT_CD DEPT_NAME SECTOR_CD MANAGER_NM
IT_DEV S/W Developer SDV MR.XL
MNGNT HR Management HRD MS.DS


EMP_DEPT (Bridge Table)---------------------------------
EMP_CD DEPT_CD
1001 MNGNT
1002 IT_DEV
1003 IT_DEV

Create Data Source and Data Source Connections

Create Data Source and Data Source Connections

The DB2 database name that you type must use uppercase letters. Also, in Framework Manager, the schema names that you type for the DB2 data sources must use uppercase letters.

Here we are creating a Data Source now -

1) Clicked on pink color icon
2) A screen comes, give datasource name and clik next , following screen appears
3) Select the database that you want to connect to and click next , following screen will appear.
You just need to give database name and ID and Password then you can test the connection.
Connection string will get automatically build by the cognos.

Here you can click on finish button and your datasouce is ready to be used in FM/Report Studio.

Framework Manager Determinants

Let us understand the determinants in simplest terms.

Company CEO Empl
-----------------------------------------------------
Ennov Vijay Vishwas
Ennov Vijay Mritunjay
Ennov Vijay Farrukh


Company Revenue
---------------------------------------
Ennov 500000
Carrot 400000

When above 2 tables are joined on Company field and you bring following items in the query Company,CEO, Revenue. The the following will be the output

Company CEO Revenue
-------------------------------------------------
Ennov Vijay 500000
Ennov Vijay 500000
Ennov Vijay 500000

You got three rows because Ennova had three employees, here cognos was not able to understand that granulirity of both these tables was different. You have to defined it explicitely and end product of this is called DETERMINANTS.

Here in the first table you have to define 2 determinants CEO and Company and and check the check box of group by. Now when you run the query in cognos you will find that repeation is not there.

Thanks
Vishwas

Drill Level Number

You can find the level numbers dynamically.This is not something that is there in documentations
and even you if ask Cognos about it they would reply that this is not possible dynamically.

But this is very much much possible. You will have to apply your thinking and think in terms of what happens when you click on drill down link, you will get the answer.

Thanks
Vishwas

PI

Performance improvement Ideas for a Report.

1) Only those data items which are required.

2) use a single query for crosstabs and make crosstabs have similar dataitems placed, it will help cognos generate a single query.

3) Use case when condition in filter rather then in dataitem and then use that dataitem in the filter.

4) use FM for fitering data

5) do not use BETWEEN
6) If you are doing inner join b/w 2 quries then better write your own quries.

7) make sure Minimized sql is created by the FM and proper joins are there.

8) Sometime you create a single query and use it in second query. In second qeury you put a filter on the first query. This is will definately slow the whole process. Instead of putting a filter on second query , do something a put a fitler in first query itself so that limite data is provided to second query.

9)have the indexes placed on the dataitmes you have filter on.

10)Formatting funtions also takes time.

11) Dont do time based calculations on run time.