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 

Framework Issues

In Go sales model order detail and order header are combined to make Sales model query subject .where sales acts as a fact .You will notice that the order header does not have any fact data alone .but when combined with order detail it becomes a fact .

By making it sales fact the model is simplified .The model represents a star schema and is much easier to track. Its difficult in actual scenario to find such a group of tables but worth looking for .

Similary in 1 to n relationship either represent fact or hierarchies are represented.Consider the case of product line , product type ,product they follow 1 to n in a line .

In the screenshot below notice that if you drag item from product brand and product type there are two path for the query this causes ambiguity . So it is solved by removing the join between product brand and sales target .In the other diagram 2 you will notice a new query item products is created which is joined to on product type code to sales target and product brand code to product brand .This new object product is combination of product line ,product type ,product .So actually we are limiting the join of sales target with product only based on product type and the direct join based on product brand does not exists .

The loop is solved in below screenshot.Notice that Products on (right hand corner ) is different from Product at center

Diagram 2




Now notice that the products that is there is a model query subject .so whats the idea behind this??

Above issue is solved below diagram 

Scenario if you drag sales and branch there are two paths that exists that is taken care below

Notice that the sales target that is connected to product type is actually a model query subject that is joined to product type base on product type code.

So its a good place to understand the difference between using model query subjects and creating join to them v/s using a shortcut

As per IBM cognos 10 Help file

1) Shortcut are easy to maintain and are automatically updated if the table structure changes
2) Shortcut are exact replicas and do not allow addition of columns whereas if you create model query subjects you can add or remove columns from query subjects.
3) Creating shortcut does not create a copy of the joins.
4)However if you directly create a copy of query subject it will create a copy of all the joins of that query subject(In our example we have created a new model query subject and not a copy of query subject)
.Its gives just for knowledge purpose.

Point to Ponder -- How will the join form if we take data from sales target and product type .Will the join differ based on among the two which sales target we choose ??

Even in case of shortcut how will the join that is formed decided on?? It depends on where you pick the data from which sales fact


The product forecast and sales also forms a loop we will look below 

As you can see below the retailer type is made by combining retailer and retailer type .Its actually a model query subject,So by creating a join with model query subject we have solved the loop by ensuring that whatever way cognos decides to choose for the loop the joins remain same .Better explained below with products example 

Notice that products is a combination of product type,product line and product .Its between sales target and sales .Now this is a loop as you can also travel from produt type , product and sales.(product line is hidden in diagram dont worry) .So actual any path it takes the join remain the same and so the outputwill be same and hence the loop is solved .

By creating products between sales target and product forecast we have ensured that the joins remain the same for both paths of the loop.

Please refer this diagram for my question 

Using Stored procedure in Cognos with Oracle

Hi Guys ,

Below is a stored procedure in oracle that i am using in cognos to return the output.Its a simple procedure to know basic of how to use procedure in oracle

CREATE OR REPLACE
PROCEDURE get_emp_rs (prod_line IN varchar2 ,
p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
SELECT product_line_code
FROM sls_product_line_lookup
WHERE product_line_en = prod_line;

END get_emp_rs;

Note - Though the procedure is returning the out parameter ,There is no need to define out parameter in framework while creating query subject from stored procedure .Cognos automatically detects the out parameter .


I have supplied the value camping equipement so that some output is returned .Usually this value will be passed from the prompts . Like a value prompt parameter .

Note -Here in oracle you will have to use a cursor to return the data not like ms sql where we just write a select statement .Notice that i have kept the curson open have not closed it .