Governors in framework manager

Hi Guys ,

Below is screenshot of governor setting in framework .Please feel free to add your comments on each settings .I will add my notes each time i come across any special case .

Cardinalities in Cognos framework

Hi Guys,

Below is note on cardinalities .I have taken the content from IBM cognos helpguide .You can find the same in cognos framework userguide .Just noting down point for my reference .

taken from IBM

IBM helpguide Link

Cardinality in Generated Queries

IBM Cognos 8 supports both minimum-maximum cardinality and optional cardinality.

In 0:1, 0 is the minimum cardinality, 1 is the maximum cardinality.

In 1:n , 1 is the minimum cardinality, n is the maximum cardinality.

A relationship with cardinality specified as 1:1 to 1:n is commonly referred to as 1 to n when focusing on the maximum cardinalities.

Note --- so 0:1 to 1:n is also read as 1 to n

A minimum cardinality of 0 indicates that the relationship is optional. You specify a minimum cardinality of 0 if you want the query to retain the information on the other side of the relationship in the absence of a match. For example, a relationship between customer and actual sales may be specified as1:1 to 0:n. This indicates that reports will show the requested customer information even though there may not be any sales data present.

Therefore a 1 to n relationship can also be specified as:

• 0:1 to 0:n
• 0:1 to 1:n
• 1:1 to 0:n
• 1:1 to 1:n

Use the Relationship impact statement in the Relationship Definition dialog box to help you understand cardinality. For example, Sales Staff (1:1) is joined to Orders (0:n).

It is important to ensure that the cardinality is correctly captured in the model because it determines the detection of fact query subjects and it is used to avoid double-counting factual data.

When generating queries, IBM Cognos 8 follows these basic rules to apply cardinality:

• Cardinality is applied in the context of a query.
• 1 to n cardinality implies fact data on the n side and implies dimension data on the 1 side.
• A query subject may behave as a fact query subject or as a dimensional query subject, depending on the relationships that are required to answer a particular query.
  
  
  
  
  Possible end labels are
  • 0..1 (zero or one match)
  • 1..1 (exactly one match)
  • 0..n (zero or more matches)
  • 1..n (one or more matches)
  The first part of the notation specifies the type of join for this relationship:
  • an inner join (1)
    An inner join shows all matching rows from both objects.
  • an outer join (0)
    An outer join shows everything from both objects, including the items that do not match. An outer join can be qualified as full, left, or right. Left and right outer joins take everything from the left or right side of the relationship respectively and only what matches from the other side.
    
    Example
    
    Cardinalities are set according to reporting needs and are not necessarily based on data .Consider below example .Which will require a full outer join.
    
    Consider example of sold date and product .there might be dates when no product were sold and there can be product which were never sold .so the cardinality shoud be 0:n :0:n (Full outer join)
    
    but in actual case we want to see only products that were sold so we have 1:1 to 1:n we can also model it to outer join 0:n to 1:n base on requirement 

Related Links in drill down/drill up .or drill through definations

Hi Guys ,

I am trying to make you aware of one feature of cognos which i noticed recently but was there from begining in case you have not noticed it .It a fun to try.

First thing to notice is drill through defination on home page .It allows you to create drill defination which will be available only when certain item from model is available in report .Like drill through defination whose scope is product type .

It allows you to make use of dynamic filtering for drill through defination .When you click on any intersection in crosstab .The values from intersection will be used to dynamically filter the drilled report .

Scope in Framework

Hi Guys,

Scope allows you to set till what level a measure is avaiable for a dimension

Shortcut and Alias shortcut explained

Hi Guys

Below is a explaination on alias shortcut and shortcut

I  have created shortcut (reference ) to employee history created join between employee history and shortcut and published the model

Now what is interesting in this .

Notice the behavior of the generated sql and you will neve has issue of differentiating between Shortcut and alias .

See its picking values from only one table Position Department .as if the shortcut is only for our understanding

select "POSITION_DEPARTMENT"."POSITION_CODE" "POSITION_CODE", "POSITION_DEPARTMENT"."DEPARTMENT_CODE" "DEPARTMENT_CODE"
 from "GOSALESHR"."POSITION_DEPARTMENT" "POSITION_DEPARTMENT"

Now Alias Shortcut 

Look at the sql generated 

select "Alias_to_POSITION_DEPARTMENT"."POSITION_CODE" "POSITION_CODE", "POSITION_DEPARTMENT"."DEPARTMENT_CODE" "DEPARTMENT_CODE"
 from "GOSALESHR"."POSITION_DEPARTMENT" "Alias_to_POSITION_DEPARTMENT", "GOSALESHR"."POSITION_DEPARTMENT" "POSITION_DEPARTMENT", "GOSALESHR"."EMPLOYEE_HISTORY" "EMPLOYEE_HISTORY"
 where "Alias_to_POSITION_DEPARTMENT"."POSITION_CODE"="EMPLOYEE_HISTORY"."POSITION_CODE" and "EMPLOYEE_HISTORY"."POSITION_CODE"="POSITION_DEPARTMENT"."POSITION_CODE"


Comment 

It treats the alias shortcut as a separate new table .Its same as creating alias in oracle statement

Similar to below oracle statement 

select * from emp a , dept b
where a .dept_id =b.dept_id

here a , b are alias to emp and dept table respectively .

Difference between Model query subject and Alias 

The key difference between model objects and shortcuts is that model objects give you the freedom to include or exclude items and to rename them. You may choose to use model objects instead of shortcuts if you need to limit the query items included or to change the names of items.


IBM Link stating difference

Model Query subjects 

If you create a new model query subject on Position department .and take postion code from model query subject and department code from original Position department you will see that cognos can recognise its the same table and below is its query .It works like shortcut(reference) 

select "POSITION_DEPARTMENT"."POSITION_CODE" "POSITION_CODE", "POSITION_DEPARTMENT"."DEPARTMENT_CODE" "DEPARTMENT_CODE" from "GOSALESHR"."POSITION_DEPARTMENT" "POSITION_DEPARTMENT"

However as soon as you create relationship to this model query subject it behave as Alias shortcut 

select "New_Query_Subject"."POSITION_CODE" "POSITION_CODE", "POSITION_DEPARTMENT"."DEPARTMENT_CODE" "DEPARTMENT_CODE" from "GOSALESHR"."POSITION_DEPARTMENT" "New_Query_Subject", "GOSALESHR"."POSITION_DEPARTMENT" "POSITION_DEPARTMENT", "GOSALESHR"."EMPLOYEE_HISTORY" "EMPLOYEE_HISTORY" where "New_Query_Subject"."POSITION_CODE"="EMPLOYEE_HISTORY"."POSITION_CODE" and "POSITION_DEPARTMENT"."POSITION_CODE"="EMPLOYEE_HISTORY"."POSITION_CODE"


Very Important note -

Alias shortcut as name suggest is a shortcut any changes that you make to original will be reflected like change of column name will change column name in alias shortcut not the case with model query subject

2) When you create a relationship to model query subject it will ask whether you want to replicate all the original relationships .Like suppose you create model query subject of product it will ask if you want all product relationship to be assigned to model query subject .So if you say yes the model query subject will behave just as original product with your new relationship . This is not the case with alias shortcut


I hope this is one of the best explanation available out there .

Exporting Cognos package

Hi Guys,

Below is how to move reports from one server to another ,

http://www-01.ibm.com/support/docview.wss?uid=swg21382301


A) Moving the reports manually

Open the report in Server1's Report Studio
Select Tools > Copy Report specs to clipboard
Open notepad and paste the output into the workspace
Save the file
Launch Server2's Report Studio
Select Tools > Open Report specs from clipboard

Be careful here 

Prompts in Cognos

Hi Guys,

Below are some prompts in cognos .Nothing complex .I am pasting it as these are the prompts that we dont use much often .So just as a reference

List of prompts available in Cognos

Interval Prompt

Range option selected from properties

Text Box prompt

Date prompt 

Use parameter info

If you set this property to YES. then the queries with this property set to YES ,. are first executed then the queries with no parameter in them are prepared later .this will help you if its takes long time for the first prompt page to load which may be because the report has a heavy query in it.Know understand how this helps 

IBM Extract 

When you set this property what you are simply doing it suggesting to Rsvp that it only include those queries you have tagged for the first part of the conversation. Doing this can speed up the time to first prompt page cycle as you are deferring the prepation of queries unrelated to prompting until the later phase