Using Cognos Transformer to Convert a Single Table (or Query) Source into a Data Cube

1. Start Transformer from the Start button menu
2. Choose File> New>  

· This will start up the New Model Wizard

3.  Click “Next” button
4. Type in you model name (don’t include security for our work). Click “Next” button
5.  Type in a name for your source. Choose data source type from drop down (e.g. Delimited-field text with column headings , or Access Query or Access Table, …). Click Next
6. Click the “Browse” button to find the database file. Find the file, then click the “Open” button. (it appears that you need to have the DB be not read-only, so you cannot directly use a DB on a CD-R)
7. If the “Table or Range” is not the one that you want, use the drop down to choose the appropriate table or query. Click the “Next” button
8. For the most control, unclick the “Run AutoDesign” check box. Click the “Finish” button.
9. In the “Data Sources” pane, click on the plus sign to show attributes
10. Drag measures (facts) into the Measures pane
11. For each dimension hierarchy:

· Drag the highest level in the hierarchy (most summarized) to the gray bar at the top of the “Dimension Map” pane (“dimension line”)

· Drag other attributes in the hierarchy, in order from highest level of summarization.

o Dimensions hierarchies should be true hierarchies. Values for the lower level should not be split among different values for the higher level.

o This may mean that you are creating more than one hierarchy for a given dimension. In Cognos, each of these will be considered a “dimension”. In some cases the “hierarchy” may only be one level deep.

· NOTE – the first two hierarchies are the ones that will show up by default when the cube is opened. The order can be changed by dragging the Dimension Line items around.

12. Choose File> Save>
• Specify the file name and location (the type should be Model Files and the filename extension .pyi – which should be the default)
13.  You can double-click on the names on the dimension line to rename to more user friendly names. You can do likewise for any other attributes in the Dimension Map (change the “Level Name”)
14. Choose Run > Generate Categories>
• With big data this will take some time
• If warned about too many descendents, click ok. This is a user friendliness issue (and possibly a performance issue). You might consider changing the model if possible and re-running.
15.  When you are ready to create a PowerCube (Cognos lingo for Data Cube), choose Run > CreatePowerCubes

· With big data, this will take some time

16. To view the cube created, choose Tools> PowerPlay > (though with a less friendly interface than you are used to).

· To save as a stand-alone file, from the viewed data, choose File > Save As >, then specify name, location, and for type indicate PowerPlay Cube (.mdc)

17. The result will be able to be opened on a computer with Cognos PowerPlay installed. You will not be able to put them up on the WWW server since you do not have access to it.

This is designed as a quick reference style tutorial. Data Cubes can be created from multiple sources, can have alternative drill-down paths and a time dimension can be automatically generated (I haven’t done these yet). For more complete documentation, look under Start > All Programs >Cognos > Documentation > PowerPlay > Discovering Transformer for a pdf document.