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.

DataSource Connection

DataSouce can be defined at 2 places.

a) Cognos Connection
b) Framework Manager

It actually has 2 parts as well. Datasource which is kind of physical connection to the database and second is the datasouce connection which contains the credentials to conenct to the database.

If you see any datasource at the cognos connection,you can actually click on it and you will find the datasource connection(s) under it.You may have multiple datasouce connection for a datasource. When user will run report, he will be prompted to select which datasource connection he wants to use for report.Signon is kind of reusable thing,while defining datasouce connection,you can say that make it signon as well, if you do that then any other person can also use that signon and create many more datasources.He would just need to copy and paste singon from one place to other place(under the new datasource). This work perfactly when you have single ID/PSWD for multiple databases, you won't need that administrator( person knowing ID/PSWD) to enter ID/PSWD for you again and again for each databases.


At Framework manager you can not just right click the Data sources Folder and create a new datasources.You will need to run MetaSata Wizard to get to that place from where either you can bring a existing datasource into your model or create a new datasouce.You just click on namespace and then goto to ACTION menu and click MetaData Wizard. From here procedure is same as you create a datasource from Cognos Connection.

To be continued.......

Degenerated,Confirmed and Junk Dimension

Degenerated Dimensions- In simple terms this is actualy is not a dimension because in dimension modelling we do not have a separate dimension for this. Its bit confusing right? Let us understand it in more detail - We have seen in lof of examples of degenerate dimension on web where we get example of Invoice number,Line number in a bill. We will take one most simple example. We have a fact table where in we store the results of students, I also need rank of students in the fact table and I do not want rank to appear as 1,2,3 but I want rank to appear as FIRST ,SECOND ,THIRD. Now here one thing you can do is to add 1 column that will store values like 1,2,3 and create a dimension rank dimension and store following values there

Rank Rank Descr
-----------------
1 First
2 Second
3 Third

Now you can connect that fact table with Rank Dimension ( just think this example objectively, because there are lot of ways to handle this at report level) to get the Rank Descr.

But if you see we can avoid this join if we can directly put FIRST ,SECOND,THIRD values in fact itself becuase we don't have anything thing stored in rank dimension. So this way a dimension will be killed and will become a part of a fact table. So this particular FIRST,SECOND,THIRD part will be called DEGENERATED DEMENSION in the fact table.

Confirmed Dimesnion - We have seen lot of definitions of Conformed dimensions, here we will see what is it through Cognos eye. Whenever we create a model using framework manager,it is not necessary that one star schema would satisfy your needs, we might need more then one star schema groupings ( I will have separate blog on what is star schema grouping).Different star schemas uses different fact tables but they might use some dimensions which are common across different different star schemas. These common dimensions are called Conformed Dimensions, typically you can say Time Dimension is a conformed dimension.In a product related information model Product dimension may work like a confoemed dimension.

Junk Dimension - While doing modelling there are lot of things which do not classify as facts, so should we make dimensions out of them ? If we start doing this we will end up making lot of small small dimensions and will crowd our modeliing space.So we have to decide whether making separate dimension makes sense for something.Suppose there are 5 things we are not going to make separate dimension for, we will put all these details into one dimensions and call that dimension a junk dimension.Mainly junk dimesnion contains flags like how customer came to purchase something buy car or bike, he is living in rented home or having his own home,customer placed order via web,phone or by any other means etc. Now you can see by these details that theses kind of details do not deserve to have there own dimension.

Bookmarks in Cognos

You can see here four columns have been added to the list , one column variable descr have been converted into a section. Unlock report and click Variable descr column and go to its properties

>Drill Through Definitions > Bookmark Tab

change SourceType value to 'DataItem Value' , Click OK.

Add a book mark just adjscant to variable descr and go to bookmarks properties.

Change source Type property to 'DataItem Value' and DataItemValue property to Varialbe Descr.

Now when you run this report this bookmark will have value of Variable descr value.

Suppose one of the value of Variable descr is 'India' , so now put a text item at the top of the report and go to its drill throug definition and go to bookmark tab and set sourcetype='text' and text=India, now you can run the report and get the output in pdf. In pdf when you click on that text item , it will directly take you to the palce where variable descr is having its value equal to India.