Translate

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.



No comments: