Identifying nth Weekday of a month in reports

Requirement: Identify the 2nd Thursday of the current month.

Solution:

Create data items ToDate, 1stDateofMonth, 1stDayofMonth, Nth, Day , AddDays, NthDay

ToDate - current_date
 
1stDateofMonth - _first_of_month(ToDate)
 
1stDayofMonth - _day_of_week(1stDateofMonth ,1) // Assuming Monday is Day 1
 
Nth - 2 // The nth value of the weekday required, in our case we require 2nd Thursday
 
Day - 4 // Assuming Monday is Day 1, then Thursday is Day 4
 
AddDays - case when [Day] >= [1stDayofMonth]
then [Day] - [1stDayofMonth]
else
([Day] + [1stDayofMonth]) -1
end
 
NthDay - _add_days([1stDateofMonth],(([Nth]-1) * 7 ) + [AddDay])
 
NthDay returns the 2nd Thursday of the month.
 

Required a Property for Relational querying !!!

I so wish there was a Property or setting for Relational Querying vs Dimensional Querying. We are dealing with a model that is ER modeled and is based on an OLTP system. We need to build an ad-hoc model based on this. And as you know this model is not supported by Cognos. So don't you think if there was a setting in FM that instructed Cognos to generate ER based queries rather than identifying facts and dimensions based on cardinalities that would make life so much simpler.

I mean, just think if there are 3 tables A, B, C and they are joined to each other, you would expect a query that relates the 3 tables based on the joins. I do not want Cognos to identify if A/B/C is a fact / dimensions and break-up my query.

I know you would ask me to move to a dimensional model but in our case it doesn't even make sense. The OLTP system doesn't even deal with millions of rows nor do we have an ETL system. SO moving to a dimensional model would mean a lot of expenditure in terms of architecting, getting an ETL tool, designing and developing ETL workflows, time, resources involved and so on...Just wish there was a setting..
 

Practical Usage for Sectioning

Most often you would have used Sectioning for formatting reasons. Here's how you could use Sectioning, Page-break concepts to make a single list report work like 2 lists.


Requirement: Display Data for Current Year and Previous Year as 2 separate lists on 2 separate pages.

Normally you would have used 2 separate queries for the 2 list reports. Using Sectioning and Page-Break you can use a single query to achieve this. You can use this method when you need to display multiple lists that differ in terms of filters alone.

Solution:

Step 1: Drag Product, Sales, Year in to the report

Step 2: Create a filter to filter the data for current and previous year.

Step 3: Create a data item called Section Title.

case when [YEAR] = extract(year from current_date)
then 'Current Year'
else 'Previous Year'
end

Step 4: Section on the Section Title data item



Step 5: Create a page break on Section Title data item.






 

Limited Cache, Query Re-Use Capabilities

I have always had a major complaint with Cognos and that is with regards to caching and query re-use. I am not sure if this is the case with other BI tools as well. And it is really frustrating when you have to explain this to the users.

For instance you are asked to implement dynamic sorting on your report that includes a number of columns and retrieves a huge amount of data. Now with Dynamic sorting each sort fires a query against the DB which is performance impacting. And when you explain this to the users their query is "Why doesn't Cognos just retrieve the data and push it to the client side, so that each sort would not hit the DB?" Good question, but that is not for me to answer.

Likewise imagine a scenario where you have to display 3 Lists in your report one each for YTD, QTD and WTD. Ideally what I would like to do is to get the YTD data and QTD, WTD would all be a subset of the YTD data. So if the YTD data can be retrieved and cached and this data re-used for the other queries that would be great. But unfortunately that is not the case with Cognos. When I create a query object and point other query objects to this the base query still gets executed multiple times.

These are all required features in Cognos.

The only cache and query re-use capabilities Cognos offers is when you have multiple lists all pointing to the same query and having the same order by, group by and columns which is ever rarely the case.
 

Bursting Explained - Part III

Links to Part I, Part II.

Scenario 3: Separate Burst Group, Burst Recipient and Report Queries.

In this case all 3 queries are separate.

Example: Burst a Sales report based on Sales Regions that has 2 lists in them and make all the sales reports available to all users through Cognos Connection Directories.

BurstGroupQuery (Master Burst Group Query) - Sales Region

ListQuery1 (Detail List Query 1) - Year, Sales Region, Revenue

ListQuery2 (Detail List Query 2) - Year, Sales Region, Sales Target




RecipientQuery (Detail Recipient Query) - Sales Region, Recipient

Create Master-Detail Relationships between Master Burst Group query and Detail List Queries. Create Master-Detail Relationship between Master Burst Group query and Detail Recipient Query.



In this example the master burst group query is run once, the data set is retrieved, for each record retrieved the recipient query is run once and the data set is retrieved. Again, for the master query the detail list queries are run once each for each record and data is retrieved.



In the above case:

Master Query - Run Once, 100 records retrieved.

Recipient Query - Run 100 times and 1 record retrieved each time.

Report Queries - Run 100 times * 2 report queries
 

Bursting Explained - Part II

Link to Part I, Part III.

Scenario 2: A single Burst Group and Burst Recipient Query but multiple report queries.

In this case the report has multiple queries. The burst group and burst recipient information is fed by a single query.

Example: Burst a Sales report that has 2 lists based on Sales Regions and make the report available to users through Cognos Connection Directories.




RecipientQuery (Master Query) - Sales Region, Recipient

ListQuery1 (List Query 1) - Year, Sales Region, Revenue

ListQuery2 (List Query 2) - Year, Sales Region, Sales Target

You would need to create master-detail relationships between the Master Query and the 2 List queries. The Burst Group and Recipient query would be the master query.





In the above example, since the burst group and burst recipient is sourced from a single query, this query is run once before the start of bursting process. Then for each record retrieved by the burst query the detail queries are fired once each.

Say we have 100 sales regions. The master query is fired once and the 100 records are retrieved. Then for each Sales region the 2 detail queries are fired. So you would see a total of 2 * 100 queries fired against the DB in sequence.

It is in the above scenario that you would see the entire burst process taking time to complete. Assume each report output is generated in 1 minute. The entire burst process in the above example is completed in 100 minutes and that is like more than an hour.


 

Bursting Explained - Part I

I have been asked by so many users what happens behind the scenes when Cognos bursts a report? Why do burst reports take longer to complete? etc. And I have explained about master-detail relationships and its impact on Burst reports in an earlier post. In this post and the next couple of posts I am going to try and cover the various burst scenarios and how many SQLs Cognos fires against the backend in each scenario.

Bursting is made up of 3 components:

The Burst Query or Report Query: The report query that needs to be burst. The report could be a single query report or multi-query report. In case of a multi-query report, each data container in the report needs to have a master-detail relationship set up with the burst group query.



The Burst Group Query: The query that decides the burst group. For each record in the burst group query the report is sliced.

Example: A report needs to be burst for each Sales Regions. In this case the burst group would be the Sales Region. The report is sliced for each Sales Region record generated by the burst group query.

The Burst Recipient Query: The query that provides the recipient information.



Scenario 1: A single Burst Query, Burst Group Query, Burst Recipient Query.

In this case the list in the report, the burst group and the burst recipient information is fed by a single query.

Example: Burst a Sales report based on Sales Regions to be made available to users through Cognos Connection Directory.

Query 1 Data Items: Year, Sales Region, Sales, Recipient.

Recipient - CAMID('Everyone')

In the above Example, since burst group, burst recipient and list report are sourced from a single query, Cognos runs this query once, retrieves the entire data set and then slices the report output for the various sales regions. Hence only 1 query is fired by Cognos in this case.



Links to Part II, Part III