Cognos TM1 can replace Cognos Powerplay

We always thought the Congos Powerplay is the best tool available for OLAP analysis. But it is going to be changed now.

Cognos TM1 is much much better in OLAP analysis.

 

OLAP is actually a concept it has nothing to do with databases and queries all those technical things. It is just a concept which says that user should be able to do analysis on multi dimensional way and on pre-summarized data. Thats it.

 

Cognos Powerplay has lot of restriction like measure is bounded with the dimensions. Once you create a dimension you have to use the dimension in the same manner , you can not use the same dimension with few changes.Any changes you need in dimension you have to create entirry new dimension.

 

In Cognos TM1 all these constraints are not there. Measure is treated as dimensino and you can create  subset of the dimension.

It is so flexible. Dont be suprised if in coming times you see Powerplay is replaced by Cognos TM1.

 

Yours

Vishwas

Cross Product Confusion

The following property that we have , it doesn't mean that we will have cross join only after making this property 'Allow'.

By default its value is 'Deny".

We can always have cross-joins between the queries or we can make some kind of arrangements between the qureries and have cross-join and get our job done. This property stands for stopping any kind of cross-join between DATABASE TABLES.

Thanks

Vishwas

DRAFT:- FM Preparation

Following points must be taken into consideration before creating FM model

1) What kind of reports you have. Dimensional or Relational i.e are you going to have drillup/down on reports (dimensional) or you have simple reports(relational). You might have a case where few reports are dimensional and few are relational , in this case you have to decide which table is going dimensional and which table going relational.

2) What kind of filters your reports is going to have.Are there common filters.?If there are common filters then you should create them in report itself.

3) Do you have any report requiring 2 facts join. If you have then you have to test you FM for that to make sure you FM is creating stitched query or outerjoin query. If FM is not creating stitched query then you will not be able to get the right kind of results. If ther is lot of confusion among different tables joins then make separte start schema grouping for that particular report requirments so that when those 2 fact table are taken in the query, no other table affect that.

4) Ask client if he has plan to use model in query studio. If he says "YES" then , : ) your job is doubled. You have to make sure from the beginning that your model is satisfying needs of report authors(Report Studio) as well as Adhoc reports(Query Studio).If you can afford to make 2 model , one for query studio and one for Report Studio , do that if your model is big and complex.

5) All the joins should be defined at the Data Layer. Never do anything at the Data Layer other then making joins between the tables if they have not been imported from the database or not created by FM automatically while importing tables from the database.

6) Never get confused by different 'Layers' of the FM. Layers are nothing but they are like 'Folders' of your desktop. It all depends upon you , how you arrange your objects. Same requirement may be fulfilled by creating 10 layers or 2 layers. There is no fix rule.But FM is a bit complex and all your reports depend upon the FM so we create differnt layers for each thing that we do like creating business names, creating dimensions, short-cuts for report author presentation just to makre sure if somebody else takes over he is clear.

7) Please read carefully what is a namespace and what are other objects of  FM. Namespace is very different thing then other objects. Also take care when you copy and paste objects.And take very much care while building new query subjects from below layer, be mindful of what are you doing. Becuase any error and your entire day will be gone.

8) You have to setup row level security at the FM level. And if model is going to be used in query studio as well you have to alter the defintion of all those facts so that if anything is used from those facts row level security is applied. But at the same time you have to also make sure it should not hamper the report studio reports. So to achieve that make sure prompt name ( ?xyz? ) is same in FM query subject(Fact) as well in report studio.

..to be continued...

Conditional Layouts

I didn't know about conditonal layouts until somebody asked me about it. I was not able to answer him and that was frustrating.

I know we work in technology field and there is every possibility that your work may not match with others work even if you both are working in say Cognos. Your insights and knowledge may be different from others who work in Cognos.

I wanted to know about conditional layouts but didn't find anything on net. I had to dig in report studio itself and find what it is.

When I came to know what it is , first word I uttered was "ABSURD !!!" .This is nothing but rendering !!!!  We do more complex things and sometime we miss simple and easy things ,it also depends upon what kind of requirements your clients is presenting.If you keep working in XYZ area in Cognos , even if you have 10 years of experince you won't be able to answer the questions related to ABC area asked by 1 year experienced person in ABC.

But thats what I also like about technology ,everybody is having something to teach other person !!!!!

So now come back to Conditional layouts. These are the report layouts based on some condition.

For example client may run report in HTML or Excel or PDF. But if client doesn't want to include some columns in PDF version and add few more column in Excel version that what would you do ???  This is where conditional layouts are used.

Thanks

Vishwas

Guessing Granularity ???

Understanding this term is easy. One liner like "Table can provide up to which level of detail is known as its granularity" can make anybody understand what is it. But its easiness stops here.

Why am I saying 'its easiness stops here" ?? if you want to get a report coming out of 2 tables with different granularity then you will find the reason.

If number of tables increasing in your query having different granularity then you are just heading for some tough times if you don't know how to handle granularity.

If you are just writing queries manually then you can handle granularity issues easily but when queries is being created by cognos FM then your result would be wrong if you have not handled granularity carefully. You would start getting double counting of fact data and all those kind of issues that you don't want to see.

When I am talking about granularity then I am talking about facts and not dimensions.If you have a requirement where you need to join 2 facts. How do you join 2 facts with altogether different granularities ?

If somebody thinks that he can directly join 2 facts on common keys then he is planning for disaster.This is not the best idea and no body suggest it. There is one more reason you should not do it,because you can not keep customizing your model dedicated to a requirment.Anything that we do in the model we should try that we are generalizing it for other future requirements as well.

Getting a report out of 2 facts with different granularity means helping 2 people talk having different mother tongue.So what we do here, we have to bring a person who knows both the mother tongues,right ? Yes that is what we will have to do with these 2 facts. We will have to bring a commong dimension between these 2 facts.We also say these common dimension, conformed dimension. I hope you got the idea how to join 2 facts.
Thanks
Vishwas

FM making you go MAD ???

I have created a prompt for query studio,this prompt is basically made for inforcing row level security. User can select using which role he wants to run report. For that you have put filters in the corresponding FACT table so that whenever something is dragged from the FACT table and query studio report is run.. user is asked to select the ROLE.

But when you run the report , you see all the roles of ROLE TABLE are coming in the report and not those roles that are assigned to you.

ID	Role
John	Manager
John	Executive
David	Analyst

So when John is running report , he is saying all the roles in the prompt , Manager, Executive and Analyst. But this is wrong.

But why this is happening ???

So to correct this you go and put this filter exclusively in the ROLE TABLE query subject, you republish the package. But still you get the same kind of result. You would go made surely.. becuase the same thing happened with me  :(

You do one thing , you go one layer down and put the same filter in ROLE TABLE query subject. Re-publish the package and now run the report. Now you would see only Manager and Executive roles appearing for John.

Why Why Why ???  I also don't know .. This is weird!! This is absurd !!

Only thing I can guess is upper layer was inforcing the row level security so below layer should bring all the things which are required

Thanks

Vishwas

Why the HELL my DRILL is not Working ??

Drill up and drill down works, but in your case if it is not working then check following things.

 

1) Data Menu > Drill Behaviour > Enable Drill up and Down check box is checked !!!!

2) You have not played with the dataitem on which you have to drill because DRILLING is very sensitive and it is totally handled by Framework Manager.If you do something in that dataitem like doing some calculation or using CASE_WHEN , drilling behaviour will be gone.

 

Even after having above 2 points ok,still the DRILL is not working ???

That means your query is very complex and Cognos is forgetting that it has to drill on that particular dataitem.

 

Do one thing go in the query and create a Member Set on that data item and define advanced drill behaviour on that dataitem so as to make cognos remember its drill property after the report is run.

 

Thanks

Vishwas

Preparing FM

Following points must be taken into consideration before creating FM model

1) What kind of reports you have. Dimensional or Relational i.e are you going to have drillup/down on reports (dimensional) or you have simple reports(relational). You might have a case where few reports are dimensional and few are relational , in this case you have to decide which table is going dimensional and which table going relational.

2) What kind of filters your reports is going to have.Are there common filters.?If there are common filters then you should create them in report itself.

3) Do you have any report requiring 2 facts join ? Getting the answer of this question if very importatnt.
4) Ask client if he has plan to use model in query studio. If he says "YES" then , : ) your job is doubled. You have to make sure from the beginning that your model is satisfying needs of report authors(Report Studio) as well as Adhoc reports(Query Studio).If you can afford to make 2 model , one for query studio and one for Report Studio , do that if your model is big and complex.

5) All the joins should be defined at the Data Layer. Never do anything at the Data Layer other then making joins between the tables if they have not been imported from the database or not created by FM automatically while importing tables from the database.

6) Please read carefully what is a namespace and what are other objects of  FM. Namespace is very different thing then other objects. Also take care when you copy and paste objects.And take very much care while building new query subjects from below layer, be mindful of what are you doing. Becuase any error and your entire day will be gone.

8) You have to setup row level security at the FM level. And if model is going to be used in query studio as well you have to alter the defintion of all those facts so that if anything is used from those facts row level security is applied. But at the same time you have to also make sure it should not hamper the report studio reports. So to achieve that make sure prompt name ( ?xyz? ) is same in FM query subject(Fact) as well in report studio.

..to be continued...

Calculated Measures

Cognos developers are sometimes confused about calculated measures. The one thing they understand is that, it is called a calculated measure because we put somekind of calculation into it. There is one more meaning of the calculated measure which helps in using it in more better manner,Calculated measure calculates itself depending upon where it is placed in Lists/Crosstabs. You can say Calculated Measure is more dynamic in nature than other measures. This definition gave me clear understanding of it and helped me use it in better way in reports. I hope it helps you as well.

Disable drill down at particular level

You want to allow drill up on that level you need to do following

goto

1) Data - Drill Behaviour - select that particular level, both in drill up and drill down section.
Goto Advanced tab and for that particular level ; Define drill up behaviour as "Depth based Expression" and
Drill down behaviour as "Preserve".
This will enable drill up and disable drill down for that particular level.
2) Data - Drill Behaviour - Select that particular level and set the depth based expression to for example 2, that column or hierarchy will drill only till level 2.

------------------------------------

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.