Happy Holidays!!!

As the year comes to an end, wanted to take a moment to thank you all for keeping this blog going.

When I started this, didn't think that it would last for more than a few months or I would even have enough data to write about for more than 10 articles..It is with your continued interest and insights that I have been able to keep this going.

Wishing you all a Happy and Prosperous New Year...Happy Holidays!!!

Cheers,
Zephyr
 

Calculations in Dimensional Query Subjects

A long time back while working on a relational ad-hoc package request I ran into an issue. I have been unable to explain the cause for it and the solution involved a work-aound that I am unhappy with. I am posting it here hoping that someone might be able to offer a better solution or even validate my reasoning on the issue.

Let us assume the ad-hoc package involves the query subjects - Product, Revenue and Forecast. Product behaves like a dimension due to being on the 1 side of the 1-N relationship to the other 2 query subjects. I have created a calculation say Calc A in the Product Query subject. The package also has a stand-alone calculation say Calc B defined as Revenue / calc A.

In Query Studio, when you drag Product and Calc B the data is off. The SQL generated by Cognos involves a min on Calc A even if aggregates are set to Total or Sum.Including the function Total inside of Calc A also doesn't help. No determinants are involved as the fact tables are at the same granularity and join to the dimensions at the same granularity.

My assumption on this issue now is that Cognos treats Product as a Dimension and hence includes a Min on the dimensional data item.

My work-around to the above issue was to create a shortcut to Product and join it to Product with a cardinality to make this behave like a fact. This has the downside of the table being queried twice and involves a stitched query when calc B is pulled into the query. Since the Dimension table in my case was not huge there wasn't much performance impact.

I will otherwise have to manipulate cardinalities to make Cognos treat Product as a fact which is not advisable as it impacts other queries and still will not remove the stitched query. Any thoughts on this?
  

Column Pagination

This tip is something probably everyone is aware of. When dealing with wide reports, you can split the report horizontally across pages. You can set which columns need to appear across each of those horizontal pages using the pagination property of the List Column.
 

Moving Aggregate Calculations on Dimensional Model

Here's a requirement to use moving aggregate calculations against a dimensional model and the solution to the same. The requirement is based on dates and hence may not be applicable to all scenarios.

Requirement: Display Quantity Sold, Revenue and Margin which is calculated as Revenue / Quantity for each year in a crosstab. Show the Margin Differential or moving-difference Calculation which is the (current year quantity - previous year quantity) / Margin of previous year.

Solution:

Step 1: Create a crosstab report with Years, Quantity Sold, Revenue dragged into it.

Step 2: Create a calculation to get Previous Year - lag(currentmember([Great Outdoors].[Years].[Years]),1). Let us call this as Prev Year.

Step 3: Create a Calculation called Quantity Diff - [Quantity Sold] - aggregate([Quantity Sold] with set [Prev Year])

Step 4: Create the Margin Diff Calc as [Qty Diff] / aggregate([Margin] within set [Prev Year]).

User Comments [Paul]: You could use the periodsToDate function for a running - aggregate([Quantity old] within set periodsToDate([All Member],currentMember([Great Outdoors].[Years].[Years])))
 

Creating Effective Dashboards

Here's a look at how to create effective Dashboards using Portal feature of Cognos.

Requirement: Create a dashboard showing Product Name, Sales in a list and chart report as tabbed reports with a prompt on Product ID.

Solution:

Step 1: Create folders - Dashboard Reports, Dashboard Portals.

 



Step 2: Create the prompt report under the Dashboard Reports folder. Let us name this as Dashboard - Prompt. Create an optional Product value prompt.

 









Step 3: Create a list report and a chart report under the Dashboard Reports folder. Let us name this as Dashboard - List, Dashboard - Chart. Include optional Produt filters in both the reports.

Step 4: Create a Dashboard Portal page for each of the reports under the Dashboard Portal folder. Name these as Dashboard - List Portal Page and Dashboard - Chart Portal Page. Set the content to Cognos Viewer.

 


Step 5: Edit the Portal Pages and set the Entry to the Dashboard Reports.Hide the title bars in the portal pages.

Step 6: Set the Portlet Communication options under Report Properties for each of the Dasboard portal pages. Provide the channel name for this property. All portlets sharing the same channel name will then be able to communicate with each other and can pass parameter values between them.

 

Step 7: Create the main Portal page (Dashboard) that integrates the prompt report and the reports portal pages.Set the content to use Cognos Viewer for the prompt section and multipage object for the tabbed report output.

Step 8: For the prompt report section, set the Portlet Communication options under Report Properties.