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

After importing the metadata sources, the next step is to build hierarchies and levels in the Dimension Map. Steps involved in building hierarchies are as follows:

1. Right-click inside Dimension Map window and click on “Insert Dimension”.

2. Give the name as “Customer” and click OK.

3. Right-click inside “Customer” hierarchy and click on “Insert Level”. Give the Level name as “Customer Country” and click on “Add.

4. Select the Association role as “Source” and click on “More >>>”. From the list of columns, select “CST_CNTRY” and click OK.

5. Now, you will see the level “Customer Country” under “Customer” hierarchy.

6. Create another level with the name “Customer City” and associate “CST_CITY” to it. 

7. Create one more level with the name “Customer Name” and associate following columns to it. Please note that, Customer Name is the lowest level in Customer table and in order to establish a scope relationship with Sales Fact, it must be specified as “Unique” under Categories.

Source and Label

In Cognos Transformer, while building levels in dimensional hierarchy, we need to specify Source and Label for some of the levels.

Source is usually a key or Identifier which can uniquely identify the column description. Label is usually the actual descriptive column that will be used to display in the report output. In other words, Source is something similar to Use Value and Label is similar to Display Value in Report Studio prompt.

While defining Customer Country and Customer City, we did not need to specify a combination of Source and Label because, no identifiers are present in the table to uniquely identify those column values.

Follow the above steps and build a hierarchy for Product dimension as follows:

The Dimension Map will be appeared as follows after building a “Product” hierarchy

Now, there is a hidden dimension that can be created by using a date column available in the FACT_SALES table. This is called “Time” dimension. Building a hierarchy and levels for dimension is not something similar to the process what we follow to build other dimensions. Cognos Transformer has a built in option which can automatically build a hierarchy and levels if we just locate a date column from the source. Here are the steps to build:

1. Right-click on Dimension Map and click on “Date Wizard”

2. Give the name as “Time” and click on Next.

3. Select DT_SALE column from the available list and click Next.

4. Select the options as per your business requirement.

Continue >>>