Tuesday, January 2, 2018

How to Design Effective Cognos Report Studio Templates

1 Context
There are many articles to address this topic. Instead of showing you the generic solution, this document is intended to provide a few tips to resolve a few practical problems, listed below
1) Separate report template from layout components
2) Define all styles in report template
3) Make title/header/footer dynamic based report description
4) Share the same report template for both excel and PDF
5) Make more detail report pages in report template with accurate size
 

2 Separate report template from layout components
The layout components mainly refers page header, page footer and page title. We want to make sure that these changes should be changed only once if needed. Therefore, we need to define layout components as many as possible, and then make report template to use these components as references. Report developers do not need to make report header and footer to point components any more.
 

3 Define all styles in report template
If we want to make report style for list and crosstab, we can change Global Class Extensions as well as define local Classes. If we change Global Class Extensions as proposed standard, then any reports applied this template will use the defined style. The problem is that we cannot change template once and reflect this change for all reports, as it doesn’t work the same way as layout component reference. The local Classes are very useful to standardize reports as well, in addition, this concept can be applied for active report development to reduce mht file size.



4 Make title/header/footer dynamic based report description
As report name appears on report for every pages including report title. However, sometimes you can not change report name once developed as the report name may be used in report schedule. The solution is to leverage report description. If report description will override report name.

Please note that report description is not saved in report specification, therefore, report description won’t be available when running report from report studio, and report name will be displayed. However, report description will be displayed for end users and scheduled output.
The script for report title for layout calculation is below
if (character_length(ReportDescription ()) = 0 ) then (ReportName ()) else (ReportDescription())



5 Share the same report template for both excel and PDF
As PDF and EXCEL output is different, using the same report footer and header is impossible. One of main problem is that the columns length is NOT based on report contents such as list and crosstab duo to the impact from report footer.  The idea is to make report header and footer to be selected based on the report output. For example, we can make footer as block as layout component reference. Two blocks within this block will be defined as EXCEL block and PDF block. Then we can use conditional rendering to proceed.
 

6 Make more detail report pages in report template with accurate size
As known, the size is very important to control, especially when charts and tables are combined on same page. We don’t want chart size to impact on table. The solution is to specify the exact size for both charts and tables, so that report will be generated as exact as expected when apply predefined template.

Friday, December 1, 2017

How to validate data integrity using Cognos report

Context

This document is intended to demonstrate a new method to handle data integrity with respect to data warehousing. There are some occasionsduring data ware house development, where data integrity cannot be enforced :
1) As known, Netezza database cannot enforce foreign key.
2) Data warehouse is not created by star scheme with foreign key enforcement duo to different reasons.
3) There are many different logics on the Framework manager side, such as make one or more columns with logic to be combined as adapter column to connect with conformed dimension.
It is very difficult to write SQL for validating database, because we need to mimic all mapping logic in framework manager and then proceed. In addition, when there are any changes in Framework, we need to change SQL accordingly. In other word, we need to maintain two sets of codes, which is very difficult to keep them in sync.

Solution & implementation

The idea is to take advantage of framework manager model, as all detail logic is already built there. we don’t need to write SQL script, let Framework manager write for us behind the scene.  Whether there is DMR model or relational model, we need to create report at business layers. Theoretically, we need to check all links about fact table. Please see sample below, we need to make sure that all data in PCF Performance Cube should be contained in Conformed Delq Status Dim.



The column on fact side Conformed Delq Status Adaptor is with logic below:
case [Business Layer].[PCF Performance Cube].[Realigned Delq Status] 
when '0 - Current' then 'NOT DELINQUENT'
when '1 - Class 1' then 'CLASS 0'
when '2 - Class 2' then 'CLASS 1'
when '3 - Class 3' then 'CLASS 2'
when '4 - Class 4' then 'CLASS 3'
when '5 - NPNA' then 'NPNA'
end

The exception report is built from this relationship:



 








Fact: 'PCF Performance Cube'
Dim: 'Conformed Delq Status Adaptor'
Exception: [Business Layer].[PCF Performance Cube].[Conformed Delq Status Adaptor]

Wednesday, November 1, 2017

How to use tuple without measure member in Crosstab with Cognos DMR model

A tuple is a combination of members from different dimensions. Normally, each tuple is a data cell in a result set, therefore a measure must be located in a tuple. However, a tuple may not be needed in Cognos DMR. This capability will present a very powerful solution for many cross table developments, as long as there is a measure in either column or row, regardless of whether it is nested or not. Furthermore,  we don’t even need a measure in either row or column if there is only one default measure used.

The following sample is intended to give an idea how this concept can be applied in development. 
The Cognos sample package great_outdoors_warehouse is used in this sample; the crosstab is to figure out the difference between planned revenue and revenue and between Telephone and Fax. Two not normal usages are used:
1) No measure is used in both tuples
 a.tuple([Telephone],[Camping Equipment])
 b.tuple([Fax],[Camping Equipment])
2) No measure is used in calcualtion for two tuples
[Telephone, Camping Equipment] - [Fax, Camping Equipment], which is same as
tuple([Telephone],[Camping Equipment]) - tuple([Fax],[Camping Equipment])

Sunday, October 1, 2017

how to write a effective SQL query to calculate based on dfferent rows

It is sometimes required to compare data from different rows when building ETL to create data mart.  Two SQL analytic functions are provided to dramatically reduce work effort.

Lag: Provides access to more than one row of a table at the same time without a self-join. The lag function provides access to a row at a physical offset before that position. If you do not specify the offset, the default is 1. The system returns the value in the optional default column, if the offset is beyond the scope of the window. If you do not specify the default, the value is null.

Lead: Provides access to more than one row of a table at the same time without a self-join. The lead function provides access to a row at a given physical offset beyond that position. If you do not specify the offset, the default is 1. The system returns the value in the optional default column, if the offset is beyond the scope of the window. If you do not specify the default, the value is null.

Two samples to calculate revenue are provided below:

Sample data is based on calendar year:

YEAR, MONTH,YTD, result
2017,01,$10,$10 (10-0)
2017,02,$20,$10  (20-10)
2017,03,$30,$10  (30-20)
2017,04,$40,$10  (40-30)

SELECT

      T.YEAR

      ,T.MONTH

      ,T.X

      ,T.YTD

      ,NVL(T.YTD,0)

- NVL(

LAG(T.YTD,1) OVER (partition by X, YEAR order by MONTH)

, 0) ) as result

FROM

      (

            SELECT

                  ,YEAR

                  ,MONTH

                  ,X

                  ,SUM(YTD)    AS YTD

            FROM   <TABLENAME>

            GROUP  BY YEAR,MONTH

      ) T  

 

Sample data is based on fiscal year:

YEAR, MONTH,FYTD, result
2016,11,$10,$10 (10-0)
2016,12,$20,$10 (20-10)
2017,01,$30,$10 (30-20)
2017,02,$40,$10  (40-30)
2017,03,$50,$10  (50-40)
2017,04,$60,$10  (60-50)

SELECT

      T.YEAR

      ,T.MONTH

      ,T.X

      ,T.YTD

      ,NVL(T.YTD,0)

- NVL(

LAG(T.YTD,1) OVER (partition by X, DT.FISCAL_YEAR

order by DT.FISCAL_MONTH)

, 0) ) as result

FROM

      (

            SELECT

                  ,YEAR

                  ,MONTH

                  ,X

                  ,SUM(YTD)    AS YTD

            FROM   <TABLENAME>

            GROUP  BY YEAR,MONTH

      ) T

LEFT JOIN (SELECT DISTINCT CALENDAR_YEAR , CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH FROM MPL_BI.MPL_DATE_DIM) DT ON (T.YEAR =DT.CALENDAR_YEAR AND T.MONTH =DT.CALENDAR_MONTH)

Friday, September 1, 2017

How to author Cognos complicated crosstab with multiple measures and different calculation based on DMR

1         Problem

This report below illustrates some special requests:
https://4.bp.blogspot.com/--YaAuJ3q800/WaXP0Ba-YHI/AAAAAAAAAmE/fICbKqYKcaYLDs3GUS33XF1Y7YQxm2UmACLcBGAs/s1600/1.png

1)     There are different measures located on rows and based on two group. The first row is total, while all others is a sub group;
2)     different columns are specified with three different granularities: a. two months, b) Month difference and c) all values are based on total, not sub group, which is different from two months.
3)     With respect to Month difference, there are different calculation, such as simple A-B and (A-B)/B.
4)     Some obvious override apply for the format for crosstab.
2         Analysis
This crosstab is not easy to build, as the granularity are different in both directions, columns and rows. One of solutions is to use crosstab override, which is approved very difficult. Another solution is to use singleton, but the problem is that each singleton in general need to a separated query. As it is so many cells to override, we need to have many queries to create. This solution is not practical either. The goal is look into a solution with one query and gets all data.
3         Solution
According to the goal with clear structure, we need to break crosstab into two different sub crosstabs, and break crosstab with more columns as needed, and then STITCH them together.
Layout:https://4.bp.blogspot.com/-CaD2S1oYU7w/WaXP0KJ_DSI/AAAAAAAAAmM/LpLjv0MqS5YoTZG3V2dPZ1tMHk-vGjZbQCLcBGAs/s1600/2.png



Single Query:
https://4.bp.blogspot.com/-NmSck0jcxlI/WaXP0HLYTjI/AAAAAAAAAmI/I4a5E1X5qt41VRDaOZYsbCJVnKwQ1AbygCLcBGAs/s1600/3.png

A few practical ideas are derived below:
1)     Use crosstab node is resolve different granularities for columns, such as break down and crosstab space as total
2)     Use different sub crosstabs to make crosstab handle different  granularities.
3)     Use different columns to calculate different granularities and then put it together with show/hidden to avoid crosstab override.
4)     As each data container (list, crosstab and chart) generates different SQLs, we can use the same query to perform multiple data sets. Please note that singleton is different from list, crosstab and chart, as singleton requests a separated Cognos Query.

How to create different filters (Home Page Filtered List, Public Filter, Private filter)

  1. Context

Three cases below are documented:
Home Page Filtered List - at profile level
Public Filter - at profile level
Private filter - at user level
  1. Home Page Filtered List - at profile level

2.1


2.2
2.3
2.4
2.5


2.6
2.7

2.8
  1. Public Filter - at profile level

Repeat 2.1-2.2 to create filter on object type
3.1
3.2


3.3
From the jump-to links, click Navigational Views.


3.4
Click Filtered List.


3.5
In the Associated Filters section click Associate.


3.6 Test object Risk issue




Sample of loss event public filters
  1. Private filter - at user level

4.1 Navigate to object type you need to add filter, in this case Loss Event


4.2 Click Advanced and define the filter as needed, and check result by click filter button


4.3 Click save to save the current filter as My Personal Filter 1


4.4 Log out and login again, you will see your personal filter. You can Change and remove it by simply clicking button respectively.

Tuesday, August 1, 2017

How to extract Datawarehouse Bus Matrix from Cognos Framework Manager Model


Data Warehouse Bus Matrix, shown below, is a key design tool representing the organization’s core business processes and associated dimensionality. It’s the architectural blueprint providing the top-down strategic perspective to ensure data in the DW/BI environment can be integrated across the enterprise. This design tool can be used at different level during the life cycle of project:

-       High level requirement, decide what dimension with what facts should be built

-       Communication between business requirement and technical design

-       Detail design to denote the relationship between columns from fact and dimension.

We can also some retrieve this information from Cognos framework manager model.  There are different ways to get this information. This document provides a very practical way to do it.
Step 1:  Open model.xml from FM model from XML notepad, then remove all nodes except relationships, then save it as model2.xml
 
Step 2:  Open saved model2.xml from Microsoft Excel with spreadsheet
 

Step 3:  Further remove all columns except 4 major columns as below
-       Remove all detail columns
-       Remove name space such as business layer
-       Make sure all dimension listed on left and facts on right side
-       Remove duplicated records
 

Step 4:  Make the spreadsheet as three columns below

Step 5:  Pivot table and filter whatever dimensions and facts, please note that you can use grand total to identify usage of dimension and sort them


Step 6:  Copy spreadsheet and paste as value and then make it is presentable as the first screenshot.