Cognos PowerPlay Transformer

Cognos PowerPlay Transformer is a tool to create multidimensional database, Powercube. 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.

The default Cognos PowerPlay transformer window is composed of the following windows:

1. 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.

2. 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.

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

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

5. User Classes - define the security.

6. Signons - datasource connection information.

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

Some of the problems faced during cube creations are:

Insufficient Memory

Increase the paging file size. However this requires re-start of the computer. So, before you build a cube on your local PC, make sure you have sufficient virtual memory (500-1000 MB).

The file system is full

Owing to the amount of data fetched from the database, the temporary space of the database where the data is temporarily stored becomes full. It is not possible for more cube processing to happen on the database and the cube is not created. The solution to this:

1. Check if the sqls fired are correct. Best option is before the iqds are included as data sources into the model, to fire the sqls separately, directly on the database and check if all the joins exist and are correct and if the right result is obtained.
2. Use query-optimization techniques to enhance the performance of the query through modifying the queries themselves or creating appropriate indexes on the tables used by the queries

The SQL explain facility is part of the SQL Compiler that can be used to capture information about the environment where the static or dynamic SQL statement is compiled. The information captured allows you to understand the structure and potential execution performance of SQL statements, including:

·         Sequence of operations to process the query

·         Cost information

·         Predicates and selectivity estimates

·         Statistics for all objects referenced in the SQL statement at the time of the Explain.

3. Ensure that the cube is run during off-peak hours. This is because, if other processes are running on the database, the primary and the secondary log files (into which queries are temporarily processed and stored) may become full and give the above error.
4. If conditions a, b and c are checked and still, the above error is obtained, then procure more temp space. Ask the DBA to either increase the number of secondary log files or increase their size.

Time-Consuming Build of Cube

If the cube has more than 8 measures, then new categories are searched for and created for each measure and then the records added. This is an extremely time consuming process and the cube can take upwards of 2 hours to completed build itself. If there is a database-resource intensive process that needs to run meanwhile, then this is not feasible.

These are the methods we can adopt to reduce the processing overhead

1. Incremental update of the cube - Instead of building the entire cube at a time, we can plan for the cube to ‘build incrementally’. However, this can be done only if there are no major structural changes to the old data i.e. the old hierarchical structure of the dimensions does not change and there are no updates on the measures. If this happens, then the cube has to be re-created again.  In incremental build of the cube, the old data in the cube remains unchanged and only the new records are added into the cube.

In PowerCube windowàPropertiesàProcessingàEnable the radio button ‘This cube is incrementally updated’

2. Use of Proper Optimization – There are 4 types of optimization methods which should be used depending on the way records are stored and updated in the database. The 4 types of settings are
1. Categories
2. Data Passes
3. Direct Create
4. Auto-Partition

The best way to arrive at the appropriate optimization technique is to limit the cube to only few records fetched at a time (excluding most of the categories, putting filters in the iqds). Using this, we can create a test cube in lesser time and then alternate between various optimization techniques to identify the best one.

More investigation should be done about excluding some data sources for measures at a time so that only limited and manageable amount of measures are processed at a time. One the cube is built, then the rest of the data sources are added. If incremental update of the cube is enabled, then the data sources present earlier shouldn’t be ideally processed and hence cube build should be faster.