COGNOS Transformer tutorial

Cognos PowerPlay Transformer - Models and Cubes

PowerPlay Transformer is a Cognos component used to define OLAP cubes structure and data (in PowerPlay models) and build the multidimensional analysis cubes called PowerCubes.
A PowerCube contains calculated, summarized data organized into dimensions and measures and can be viewed and analysed in the PowerPlay Web Explorer, PowerPlay client for Windows, PowerPlay for Excel or Cognos 8 BI. PowerCubes provide secure and fast data access.
Keep in mind that Cognos cubes are static and building a PowerCube is a repetitive process (cubes may be refreshed every day, every month or even once a year).
The PowerCubes can be accessed and used both on-line (PowerPlay Web & Cognos 8) and off-line (PowerPlay Client). 

Main PowerPlay Transformer features:

• Advanced modelling capabilities
• Leverages aggregate and fact tables
• Intergation of the query data into the model
• The models are defined by dimension structures and measures which can be easily customized
• No custom programming required
• Time periods are handled in a specific way which makes data delivery easy
• High flexibilty and portability

PowerPlay cubes modeling and development process

The application development process in Cognos PowerPlay Transformer usually contains the following steps:

• Gather business users requirements
• Create a Model Plan which will include design and data requirements for the model and decide on the cube distribution methodology
• Select measures
• Plan dimensions and levels
• Design and develop the PowerPlay model in Transformer
• Create PowerPlay cubes and distribute them to the end users
• Analyze and explore the cube data using PowerPlay

A Transformer Model can be also created using a wizard with AutoDesign function or manually. We strongly recommend to avoid the AutoDesign function as in most cases it does not provide the results expected. 

PowerPlay Transformer application

The default Cognos PowerPlay transformer window is composed of the following windows depicted on the the screenshots below. 

Building a PowerPlay model involves designating columns in the source files as measures and dimensions&levels.

Data Sources - a list of all connected data sources. Each source can be expanded and it lists all available columns. Users can browse the source data by using Data Source Viewer. Columns from a data source window can be dragged & dropped to the dimensions and measures windows accordingly.

Dimension Map manages the dimensions and levels hierarchy in the data model. It allows creating new dimensions and modifying existing using an user-friendly graphical interface. Dimensions and underlaying Levels are created by moving columns from the Data Sources window to the corresponding dimension column.

Measures - drag & drop columns to manage number figures (facts) in a PowerPlay model.

PowerCubes - you can specify here a list of cubes created by the model.

User Classes - define the security.

Signons - datasource connection information. 

Cognos Powerplay Transformer model for the palm nursery business scenario:

Cognos PowerPlay Transformer Guide - data sources

PowerPlay Transformer is supplied with the source data through the data definitions managed in a Data Sources window.
Source data typically comes from a data warehouse, sometimes from production ERP system and in many cases includes multiple sources in order to deliver information required by the model.

Data sources recommendations

Column names in a data source must be unique. If a model contains multiple data sources, then the columns can be duplicated. Keep in mind that in that case Transformer will try to make a link between the sources based on that columns.

Every dimension requires a column on a lowest level of the dimensions hierarchy (usually an ID column is at the bottom)

At least one measure must be derived directly from a source column. Other measures can be calculated.

A good practice is to keep column names in the model consistent with the data source column names.

Another good practice is to preview source data with the Data source viewer.

Avoid changing or switching columns in the source data

PowerPlay Transformer uses the following data types to populate dimensions, levels, categories and measures:

Date - a special type which defines time periods and can be linked directly to the time dimension which is automatically managed by Transformer. Usually it is a native database date format, sometimes text in YYYYMMDD format.

Numeric - used mainly by measures

Text - defines levels and dimensions

Cognos Powerplay Transformer - multiple data sources in a model with the data source definition:

The data source properties window also allows users to choose the datasource type, point to the right file or database server and set up a number of processing options, like: define timing, uniqueness verification, specify number separators and indicate current period in the time dimension.
We will get into more details on that options later on in this tutorial.

Supported Data Sources by Cognos Transformer

Impromptu query definition files (.iqd) - which can query local or server-based databases

Relational sources - including Oracle, SQL, IBM, Teradata, Sybase, and any other ODBC source

Dimensional sources - Cognos OLAP, SAP BW, Microsoft SQL Server Analysis Services, Essbase, Oracle 10G, IBM DB2 CubeViews

ERP systems - SAP, PeopleSoft and Siebel

XML, Java beans, JDBC, LDAP, WSDL

Excel files, Access files and csv or text files

Legacy and Mainframe systems - like VSAM, IMS, IDMS, Cobol Copybooks

Content management data - FileNet, Documentum, and OpenSoft 

Cognos PowerPlay Transformer Guide - Time dimension

In this lesson we will create the time dimension and time levels in our sample Palm Nursery model. We will group the data by year in the highest hierarchy level and the most detailed level in the time dimension will be a week. The time source data is in the DT column in the model.

Time dimension

Time dimension is a special type of dimension. Cognos provides an automatic support for handling time dimensions which is working very well.


Go to Tools -> Date wizard (or use a date wizard icon located in the toolbar) and follow the wizard steps (values entered in our example in blue):

1. Provide name of the dimension - TIME

2. Choose Date Source Type ( Column ) and point the coumn name which contains date values ( DT ).

3. Do you want years in the dimension? Yes

4. Choose the kind of years between Calendar years (355 or 356 days) and Lunar years of 52 weeks

5. Do you want quarter-years in the time dimension? Yes

7. Do you want months in the time dimension? Yes

9. Do you want months in the time dimension? Yes

10. Do you want days in the time dimension? No

11. Choose the first day of the week. Default is Sunday, we will choose Monday

12. How to treat a week which spans a month? Split between the two

13. Enter ther first day of a particular year (yyyymmdd) - year value in this setting is not important. This options allows users for instance to report fiscal years starting from 1 March. We leave the default 20070101

14. Do you want to generate categories? If not needed, we suggest to generate them based on the data and do not generate a category for each day. No 

We can see a new time dimension in the Dimension Map of the model:

The time dimension levels in the PowerCube are depicted below:

Cognos PowerPlay Transformer Guide - Create Customer dimensions

In this lesson we will create two dimensions: Customers and Customer Region. Each of the dimensions will have its own levels and categories and a single drill-down path.

The Customers dimension will have the following hierarchy:
Customer Segment -> Customer Group -> Customer Name -> Customer ID

And the Customer_Region dimension will have the following hierarchy:
Customer Region -> Customer Country -> Customer ID


It is a good practice to have a look at source data in the Data Source viewer and check if all the columns are displayed correctly before starting building the dimensions.

Follow the steps below to add a new dimension:

1. Drag & Drop CUST_SEGMENT column to the Dimension Map next to the TIME dimension. This will create a new dimension called CUST_SEGMENT. This is not the name we want so double-click on the header and change its name to CUSTOMERS.

2. Now move the other customer Levels to the dimension map and place them underneath CUST_SEGMENT. Those are CUST_GROUP, CUST_NAME and CUST_ID

3. We will use CUST_ID as a key for the customers dimension. Go to the Customer ID column property and tick the Unique checkbox to indicate uniqueness of the categories.

Customers dimension with the source data preview:

Our source data contains also Countries which we want to see in the cube.
But at this stage we don't want to have Customers grouped by Region, Country and then by Segment becuase it would be not intuitive for the end users. We will create two separate dimensions for customers instead, to be able to analyze the data in a more user-friendly way.

We also make the names more user-friendly by clicking on each of the levels and changing the Level Name 

Customer regions dimension:

PowerPlay Transformer Guide - Products dimension

In this lesson we will create two additional dimensions: Product and Product Segment.
The dimensions will have the following hierarchy:

PRODUCTS: Product Group -> Hardiness Zone -> Product Name (in English and in Latin) -> Product ID

PRODUCT_SEGMENT: Business Line -> Product Age

Each of the dimensions will have its own levels and categories and a drill-down path.

The products dimensions creation process 

1. Drag & Drop PROD_GROUP, PROD_ZONE, PROD_NAME_ENGLISH, PROD_ID columns to the Dimension Map next to the CUSTOMERS dimension.

2. Rename the dimension to PRODUCTS.

3. Go to the Product ID column property and tick the Unique checkbox to indicate uniqueness of these categories.

4. Do the same for the PRODUCT_SEGMENT dimension. Include the PROD_TYPE and PROD_AGE columns from the fact table. 

The Dimensions Map after adding the products dimensions looks like depicted below:

Define measures in the PowerPlay Transformer Model

After having all the dimension defined, we can proceed and create measures. When all dimensions and measures are set up, we will generate PowerCubes and open it in PowerPlay.
Measures are numerical figures and come from the fact table.

To add measures to the model, follow the instructions below:

1.Drag & Drop PRICE, QUANTITY and REVENUE columns from Data sources to the Measures window.

2. Double-click on the REVENUE measure and rename it to Gross Revenue . Then go to a Format tab to change the default number format to theŁ#,##0 format with 2 Decimal places

3. Rename PRICE to Unit Price and apply the same format as for Gross Revenue.
We also do not want to summarize the Unit Price measure because it does not make any business sense. To change the grouping calculation, go toRollup tab and choose Average and set Regular Weight to Gross Revenue .

4. Set the #,##0 with 0 decimal places format for Quantity.

5. From the Data Sources window go to Facts and select DT. In the General tab change the Data class to Date and in the Time tab select the following Date input format: DMY 

Measure properties for Unit price :

Define an output PowerCube

1. Go to the PowerCubes window and press Insert key

2. Put a name for the PowerCube: www.Cognos-BI.info Sales Analysis

3. Put the PowerCube file name: cognos-bi_analysis.pyi

4. Create the PowerCube by clicking Run -> create PowerCubes and the model will become ready for exploration and analysis

The final model structure is shown below:

The Cognos OLAP cube created from the model illustrated above:

Measures allocation

Measures Allocation is a techique that allows comparing data in a report even though it is grouped on a different level of detail.
Usually it is required to perform allocation when part of the data is available only on a higher detail level than other data.

In real-life, the measures that usually need to be allocated are fixed costs, forecasts, budget plan, discounts, rebates, customer bonus. This is because in most cases those figures are calculated on a monthly (for example COSTS), quarterly (CUSTOMER BONUS) or yearly (BUDGET) basis and very often the SALES reports are generated on a daily basis.

It is also very important to remember that in some business cases allocation is logically inappropriate and may not make any sense.

There are two ways of implementing allocation:

Fixed allocation - which means that there is a constant value allocated to all levels in a dimension. Users may filter the report, drill up, down, slice and dice and the figure will remain constant. It may be sometimes confusing to the end users and before putting such a figure it is necessary to make clear to the end users that the number is constantly allocated and may not act as expected when analyzing data (especially when calculating summaries).

Dynamic allocation (weighted, proportional) - values are allocated using subtotals of an another measure. First a percent of total of a allocation base measure is calculated and based on that figure, the corresponding values for allocated measure are calculated. The weighted type of allocation is often used in real-life datawarehouse environments.

Measures allocation real-life example in Cognos

In the following example we will analyze revenue, constant costs and variable costs. Our goal is to be able to compare revenue to fixed and variable costs in all the time dimension levels available.

We will analyze the following measures from our tutorial business scenario:

Gross revenue - the source data stores sales records on a daily basis. It will be handled automatically be cognos.

Fixed costs - calculated per the whole year. The measure will be allocated.

Variable costs - monthly detail level in a source data. This measure will be also allocated.


We will perform a constant value allocation to the FIXED COSTS in a YEAR dimension and dynamic allocation to the VARIABLE COSTS in a MONTH dimension (the allocation key is QUANTITY).

To allocate a measure in PowerPlay Transformer go to a dimension level properties to the allocation tab and choose the corresponding option.

Fixed measure allocation:

Measure allocation by QUANTITY:

We see below that fixed costs have been allocated in a constant fashion:

Variable costs allocated dynamically by quantity:

Categories: Cognos Transformer , Cognos Tutorial