Sunday, April 1, 2018

How to change member caption without changing ETL and reports in DMR model

1 Context
This document is intended to demonstrate an idea to handle caption without changing ETL and reports. Sometimes, there is requirement to change name, such as brand, product on all reports. There are many different ways to proceed, such as
1) Change ETL to change name across the board. This method will request review for Framework manager and all reports, especially where the individual member is directly used in report.
2) Change Report across the board. This method will have huge effort.

2 Solution & implementation
The idea is to take advantage of framework manager model, but not to make any changes for ETL and reports. As known, each member will have default roles _BusinessKey, _MemberCaption, _MemberDescription

Also, please note that caption will be used in all reports by default. We can assign default column to _BusinessKey, and create new column for caption. Doing this way, all reports will still work as the report specification uses _businessKey, and therefore the caption change will NOT have impact on report specification. But it will have change report result when executing.

Two samples are listed below
1)
Before

 After


2)
Before






After

Thursday, February 1, 2018

How to create a report running current month by default or date range if specified

This document is intended to share two solution tips, which can applied for many difference cases.
Given that we need to create a report to satisfy requirements:
1) This report can be scheduled without any specifying parameter, by default, it will execute with current month.
2) This report can specify data range if needed.
Based on this requirement, we need to create a prompt page as below









If user select specify data range, then start date and end date will appear for users to spcifiy.

Create a fake value prompt to avoid refreshing report
When a user change the option from current month to specify date range, the start date and end date will be appear.  Obviously, you need to make prompt to set up as auto prompt.

 

Doing so will make report submit right away and move to real report page. To avoid this action, we need to make a faked value prompt to take  value from the selection.  We need to also make this faked value as NOT visible




Certainly, we need to make date range conditional with render variable

 

Make filter optional to allow user and Cognos schedule to run report against current month

Make default moth is used when date range is not specified. Cognos can only support  case 1 (IF
THEN) and  case 2 (CASE WHEN), doesn’t support the 3rd case.  Make sure this filter is OPTIONAL.

 
Case 1: filter works, as Cognos send SQL after applying the filter, in this case, ( [Sales (query)].[Time].[Date] BETWEEN _first_of_month(_add_months (current_date, -1 ))

if ( ?p_selected? = 'CURRENT'  ) THEN     
 ( [Sales (query)].[Time].[Date] BETWEEN _first_of_month(_add_months (current_date, -1 )) AND _last_of_month (_add_months (current_date, -1 )))
ELSE ( [Sales (query)].[Time].[Date]   BETWEEN ?Report Start Date? AND ?Report End Date? )


Case 2: Filter works , same reason as above

CASE WHEN ( ?p_selected? = 'CURRENT'  ) THEN     
 ( [Sales (query)].[Time].[Date] BETWEEN _first_of_month(_add_months (current_date, -1 )) AND _last_of_month (_add_months (current_date, -1 )))
ELSE ( [Sales (query)].[Time].[Date]   BETWEEN ?Report Start Date? AND ?Report End Date? )
END


Case3 filter doesn’t work, as Cognos doesn’t know how to handle another two filters. In this case, the filter WON’T apply at all. all data will be retrieved.

(
 ( ?p_selected? = 'CURRENT'  ) 
 AND
  ( [Sales (query)].[Time].[Date] BETWEEN _first_of_month(_add_months (current_date, -1 )) AND _last_of_month (_add_months (current_date, -1 )))
)
OR
(
 ( ?p_selected? <> 'CURRENT'  ) 
 AND
 ( [Sales (query)].[Time].[Date]   BETWEEN ?Report Start Date? AND ?Report End Date? )
)

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.