Cognos Report Studio Some terminologies and Logics

Cognos Report Studio Some terminologies and Logics

Query : it shows what data to display in report. In Cognos you can create or modify queries with the help of Query Explorer. We can change the order in which items are queried from the database. or we can improve performance by changing query properties to allow the report server to execute queries concurrently where possible. By default queries run sequentially.

Dimensional queries are designed to enable multiple-fact querying. 

(Multiple-fact, multiple-grain queries in relational data sources occur when a table containing dimensional data is joined to multiple fact tables on different key columns.)

NOTE : A query subject with cardinality of 1:1 or 0:1 behaves as a dimension.

Single Fact-Query 

A query on a star schema group results in a single fact query.

  

A query on multiple facts and conformed dimensions respects the cardinality between each fact table and its dimensions and writes SQL to return all the rows from each fact table.  

SQL

The SQL generated by IBM Cognos software, known as a stitched query, is often misunderstood. A stitched query uses multiple subqueries, one for each star, brought together by a Full outer join on the common keys. The goal is to preserve all dimensional members occurring on either side of the query. 

What Is the Coalesce Statement?

A coalesce statement is simply an efficient means of dealing with query items from conformed dimensions. It is used to accept the first non-null value returned from either query subject. This statement allows a full list of keys with no repetitions when doing a full outer join.

Why Is There a Full Outer Join in stitched query?

A full outer join is necessary to ensure that all the data from each fact table is retrieved. An inner join selects all rows from both tables as long as there is a match between the columns in both tables. A right outer join returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.. A left outer join all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. A full outer join returns all rows from the left table (table1) and from the right table (table2).