Building Cognos Transformer Model with (.CSV) as Data Source

Let’s assume that the source data is present in the form of CSV files and we need to build a Transformer model based on it. In this example, there are ‘3’ files present in the system: DIM_CUSTOMER, DIM_PRODUCT and FACT_SALES and the structure of these files are as follows:

DIM_CUSTOMER

CUSTOMER_ID	CST_CD	CUST_FRST_NM	CST_LAST_NM	CST_CITY	CST_CNTRY	CUSTOMER_TYPE	CST_DOB
2001	C21	Virat	Kohli	Mumbai	IND	A	4/6/1974
2002	C22	Suresh	Raina	Chicago	US	B	5/7/1964
2003	C23	Yuvraj	Prasad	Bangalore	IND	C	4/6/1974
2004	C24	Mike	Clarke	New York	US	A	5/7/1964

DIM_PRODUCT

PROD_ID	PROD_CD	PROD_NM	PROD_TYPE	PROD_CAT
210	P21	Acer-7000	Acer	Laptop
220	P22	Samsung Galaxy - 5	Samsung	Cell
230	P23	Eye Liner	Cosmetics	Beauty
240	P24	iPhone - 5	Apple	Cell

FACT_SALES

CUSTOMER_ID	PRODUCT_ID	DT_SALE	UNIT_COST	UNIT_PRICE	QTY
2001	210	1/1/2013	100	100	10
2001	220	2/1/2013	100.3332	200.22	20
2001	230	3/1/2013	10.688	12.111	30
2001	240	4/1/2013	11.01	12.45	20

The first step in building a Transformer model is to import the structures of these ‘3’ tables into the modeling environment. The steps involved in importing are as follows:

1. Open Cognos Transformer and click on Create a new model

2. Give the model name as “CSV Transformer Model” and click on Next.

 

3. Specify the data source name as “Customer” and select data source type as “Delimited-field text with column titles”

4. Specify the path of “Customer” CSV file and click on Next.

5. Disable “Run Auto Design” option and click on Finish.

6. You can now see “Customer” table imported under Data Sources.

 

7. Right-click inside “Data Sources” window and click on “Insert Data Source”.

8. Give the data source name as “Product” and select the data source type as “Delimited-field text with column titles” and click on Next.

9. Specify the path of “Product” CSV file and click on Next.

10. Disable “Run Auto Design” and Click on Finish.

11. Similarly, import “Sales Fact” as well under “Data Sources” window.

Above data model consists of two dimensions (Customer and Product) and one fact (Sales Fact). Please note that, all these ‘3’ metadata tables are isolated and there are no relationships exist in between them. In order to build a meaningful model, these ‘3’ metadata tables should be associated with each other and it can happen through match column names for Primary and Foreign keys between dimension and fact tables. And the cardinality applies in between dimension and fact is 1:1 à 1:N.

In this case, both “Customer” dimension and “Sales Fact” can be associated through a matching column “CUSTOMER_ID” however, “Product“ dimension can’t be associated because of mismatch in the column name. We first need to correct it before proceeding with building hierarchies.  The best practice is to change the column name in dimension table instead of fact table using following steps.

a. Right-click on “PROD_ID” column and click on Properties.

b. Change the Column name to “PRODUCT_ID” to match with the column name in “Sales Fact” table.

Continue >>>