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