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:
    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