Thursday, June 1, 2017

How to troubleshoot data issue when migrating SAS reports to Cognos Reports

The most important task for migration is to make sure that report data matches between SAS report and Cognos report.  General speaking, SAS report is generated from series of SQL transformations, while Cognos report is created by FM model based on data warehouse. The bottom line is that both report comes from the same data source.
Source data -->SAS report
Source data --> data warehouse --> Framework Manager  -->Cognos Report
In many cases, we need to dig into the data source. the troubleshooting approach is listed as follows:
1) Choose a single element with problem, instead of using the whole report
2) Get SQL from SAS side to match SQL result

3) Get SQL from Cognos side to match SQL result
4) Identify problem based on 2) and 3)

Monday, May 1, 2017

How to develop light weighted ETL using Cognos Framework Manager


(This is an idea shared from my co-worker Ivan Li)
There are many tools to develop ETL. This document shares a new idea to develop light weighted ETL with Cognos Framework manager. This solution could be very useful for following situations:

1)    You want to quickly develop a prototype to transform data.
2)    You don’t use traditional DW surrogate key in fact table, instead, you want to simply use natural business key to connect  dimensions and facts.
3)    Each table has many columns.

The major advantage of FM solution is to allow modellers to focus on business logic instead of detail coding. In addition, it will give us a good overview in term of logic. It refers to follows:

1)    What source tables are needed?
2)    What relationship should be built between these tables, such as left join?
3)    What filters are needed?
4)    How many steps you want to transform the data?
5)    What columns should be exist in target table?

A sample overall picture looks like follows, same as traditional FM.


You can also make query with complicated logic as query subject.






SQL statement can be obtained by test target table with Query information tab

when generate SQL script in FM, make sure to fix the more-than-30-character issue, delete the FM generated column such as c122.

 Create Table by simply add WHERE 1>2






Populate Data with the same SQL script


You can make SQL script with good format with different SQL format tool.

 

Saturday, April 1, 2017

how to model dimensions with cognos DMR in real life

Based on the design principle from Kimball, there are many clear defined guideline for different facts (transactional, periodic snapshot and accumulative snapshot) and dimensions (regular, fact, junk, etc.). The key concept is to use surrogate key to make a clear star and slow flake dimension, regardless of whether it is slow change dimension type 1 or type 2. However, this approach may not be used everywhere with data warehouse project. It have been many variations implemented in real life to reduce cost and complexity. In other word, we can establish dimension model (Cognos DMR) without ETL and STAR SCHEMA in database. A few approaches are listed in this document.

1.    Standard approach
Description:
-       Create physical table for each dimension
-       Use surrogate key in dimension (SCD1 and SCD2)
-       Star schema
-       DMR will be established based on star schema

Advantages:
-       Conformed dimensions can be easily established.
-       Superior performance  

Disadvantages:
-       Big effort, need to develop serious ETL.
-       Need to handle late arrived dimensions

 2.    Standard approach but without surrogate key
Description:
-       Create physical table for each dimension, whether it is static or dynamic.
-       The relationship between fact and dimension is established by business key, in many cases, you need to create a business key adaptor to establish relationship.
-       Star schema
-       DMR will be established based on star schema

Advantages:
-       Conformed dimensions can be easily established.
-       Much less effort to create data mart

Disadvantages:
-       You may still need to handle late arrived dimensions
-       Cannot  or very difficult to support slow change dimension type 2


3.    Create dimension based on multiple fact tables
Description:
-       NO physical table for dimension is created, Instead, we can use the query  to define dimension, such as
select distinct Column1, Column2, column3 ... from fact1
UNION
select distinct Column1, Column2, column3 ... from fact2
-       The relationship between fact and dimension is established by business key, such as connect Fact 1 to this dimension, and Fact 2 to this dimension
-       Star schema
-       DMR will be established based on star schema

Advantages:
-       Conformed dimensions can be established, as both Fact 1 and Fact 2 shares the same dimension.
-       no physical dimension and ETL are needed for this dimension.
-       Do not need to handle late arrived dimensions, as the data integrity is automatically enforced.

Disadvantages:
-       Cannot  support slow change dimension type 2
-       Could result in a bad performance

 4.    Create dimension based on single fact table with hierarchy
Description:
-       NO physical table for dimension is created
-       NO query for dimension is created, and therefore no separated query subject is created. Instead , create additional columns for hierarchy on business layer.
-       NO relationship between fact and dimension is needed, as dimension is directly come from fact table
-       NO Star schema
-       DM layer will be established based on fact table

Advantages:
-       no physical dimension and ETL are needed for this dimension.
-       Do not need to handle late arrived dimensions, as the data integrity is automatically enforced.

Disadvantages:
-       Cannot  support slow change dimension type 2
-       Could be result in a bad performance, as dimension will be extracted from fact on the fly
-       Conformed dimensions can NOT established

5.    Create dimension based on single fact table with NO hierarchy
Description:
-       NO physical table for dimension is created.
-       NO query for dimension is created.
-       NO query subject in both data tier and business tier is created.
-       NO relationship between fact and dimension is needed, as dimension is directly come from fact table
-       NO Star schema
-       dimension will be established by simply dragging one attribute to DM layer

Advantages:
-       no physical dimension and ETL are needed for this dimension.
-       Do not need to handle late arrived dimensions, as the data integrity is automatically enforced.

Disadvantages:
-       Cannot  support slow change dimension type 2
-       Could result in a bad performance, as dimension will be extracted from fact on the fly
-       Conformed dimensions can NOT established
-       No hierarchy can be used.

How to enable custom report by business users


  1. Context

Power user can be enabled to create and publish report as needed
  1. Basic setup

2.1 Created a folder in Cognos to host all custom developed report in Cognos server
2.2 Added a folder in OpenPages to report menu in OPX back end


2.3 Configure user access to report Studios
2.4 Enable permission to add Report to Reporting Menu


  1. Create new report under My folder and copy it to GOR Admin reports

3.1


3.2


3.3


3.4


3.5


  1. Publish/Share report

4.1


4.2


4.3
4.4



Wednesday, March 1, 2017

How to model a effective date dimension in DMR with alias shortcut


This document is a practical extension of concept described by my ex-post. (How to use “Alias Shortcut” for role playing dimensions in Framework Manager)  The goal is try to figure out a most effective way to design date dimension in DMR. The date dimension must satisfy following criteria:
1)    There must be only one physical date dimension. Once changed, the date dimensions at business layer will reflect the change accordingly.

2)    There must be only one date dimension at business layer. Once additional columns of date dimension changed, all date dimensions should be changed accordingly.
3)    Date dimension at date dimensional layer should be user friendly.

 
1.    Approach in detail

Database Layer:
-       Date dimension  (from physical table)

Business Layer:
-       Date1 dimension  (model from database layer and added all additional columns)
-       Date2 dimension  - alias shortcut to Date1, Please note any change of Date1 will be reflected in Date2.
-       Date3 dimension  - alias shortcut to Date1 , Please note any change of Date1 will be reflected in Date2.

Relationship
      FactTable ß----------à Date1
      FactTable ß----------à Date2
      FactTable ß----------à Date3

DM Layer:
-       Date1 dimension   --  mode dimension from Date1 from business layer
-       Date2 dimension  --  mode dimension from Date2 from business layer
-       Date3 dimension  --  mode dimension from Date3 from business layer
-       FactTable Measures

(It is impossible to define a single date dimension at DM layer and make other date dimension refer to the first date dimension. If there is any change for date 1 dimension at DM layer, you need to change Date 2 and Date 3 accordingly by manual.)

Presentation  Layer:
-       Date A dimension   --  Shortcut to Date1 dimension from DM layer
-       Date B dimension  --  Shortcut to Date2 dimension from DM layer
-       Date C dimension  --  Shortcut to Date3 dimension from DM layer
-       FactTable Measures
Date A, Date B and Date C is user friendly name.

 
2.    Sample implementation
The some screenshots below demonstrates the idea described above.



 
3.    Note
Cognos DOES NOT support reference shortcut to point to alias shortcut.