Transformer version 8.x supports IBM Cognos Series 7 data sources as well as packages and reports that contain IBM Cognos 8 query items.
This section lists the supported data sources, summarizes the information you must specify for each data source, and identifies associated limitations.
Tip: You can also click the Help button, where available, for context-sensitive information about the parameters that you must specify.
Although you can add an unlimited number of data sources or columns to each model, you must perform any necessary joins between the various data files before you import the data into your Transformer model. You must also ensure that each data source contains sufficient information to provide the necessary context for any drill-down paths specified in the model.
You can import query items from relational or dimensionally modeled relational (DMR) packages and reports, and the associated filters and prompts, by choosing the Package or Report data source type and browsing and selecting from the available metadata. In relational packages and reports, measures appear as defined in Framework Manager. Note that
Transformer cannot view or override Framework Manager governor settings.
Query items that are calculated appear as regular query items. You should review the rollup rules in Transformer when you use these calculated items as measures to avoid incorrect rollup results. For example, a rollup rule of Sum should not be applied to a measure that uses a calculated item when the value is expressed as a percentage. For more information, see Rollup Functions.
Tip: Extra queries may appear when you import a report that contains prompt pages. These queries can be identified by the presence of query items named Use Value and Display Value. Avoid importing query items from these queries.
After import, you can combine the IBM Cognos 8 data with the data from other sources as required. Individual query items can be used as source columns in the Transformer model, and can be updated using the Modify Columns feature.
For information about using reports as a metadata source in Transformer, see Create a Model.
For information about modeling IBM Cognos relational and DMR data sources, see the Framework Manager User Guide.
When you access metadata from a dimensionally modeled relational package, you can import and leverage the dimensions, or import the query items or metadata that make up those dimensions. You can also import the measure metadata. Metadata from dimensionally modeled relational packages can be directly accessed in two ways:
Insert Data Source option
Using the Insert Data Source option on the Edit menu, you can select query items and measures from a dimensionally modeled relational package as though it were a relational package. In other words, you will not see the dimensional structure. Use this option when you want to import the measures or specific query items from the package, but not the dimensions.
Insert Dimension from Package option
Using the Insert Dimension from Package option on the Dimension Map, you can select the dimensions, hierarchies, or levels that you want to import onto the Transformer version 8.x Dimension Map. Use this option when you want to include a small subset of dimensions or you do not want to use the New Model wizard to import dimensions.
If you want to take advantage of Transformer's relative time functionality, do not import the date dimensions from dimensional packages. Instead, use the Insert Data Source option to import the appropriate date field to create your time dimension.
Transformer allows you to leverage metadata from other published OLAP packages. As a result, Transformer PowerCubes can be used as high speed data access cache methods for distributing smaller or focused areas of your business information.
Consider the size of the resulting cube when you use another OLAP package as a PowerCube data source. OLAP sources, such as Essbase, can include significant data that is not be appropriate for PowerCubes. However, taking a specific segment of data from these sources can be very useful, particularly if you intend to mix that data with other data sources for further reporting or analysis.
When you use OLAP sources to populate your Transformer models
Import the dimensions that you require.
SAP variable prompts are supported and should be used where necessary to limit the data to a specific segment of your data source. For more information, see Work with SAP BW Data Using a Package in Framework Manager.
Create the time dimension in the same way that you create fact queries.
Transformer does not support importing time dimensions from any OLAP source, including PowerCubes. To create the Transformer time dimension with relative time categories, import your time information from either an IBM Cognos 8 relational package or report source, or from a flat file exported from IBM Cognos 8 or the original OLAP vendor.
In Transformer, you add dimensions from OLAP packages directly from the Dimension Map. This is a useful way to begin creating conformed dimensions and, to some extent, to reusing portions of the published metadata from the source dimension.
Using the Insert Dimension from Package option on the Dimension Map, you can select the dimensions, hierarchies, or levels that you want to import from any OLAP package on to the Transformer Dimension Map.
You can use Transformer to import both dimensional and fact data from an SAP BW query source. To do so, the SAP BW query package must be in a specific format. The Transformer PowerCubes you create with these specifically constructed SAP query packages can be used as high speed data access cache methods for distributing smaller or focused areas of your business information.
There are three stages to importing an SAP BW query to access both dimensions and facts using IBM Cognos 8:
IBM Cognos 8 Business Viewpoint Studio helps to provide you with one version of the truth for dimensions used in an enterprise's performance management processes. Business Viewpoint Studio is a controlled, collaborative, workflow-oriented business process to manage both manual and automated changes to all data related to how enterprises analyze and manage their business. Business users are given the responsibility and authority to manage dimensions in their areas of domain responsibility. By using workflows, proposed changes and additions to dimensions are approved and validated before being distributed throughout the enterprise. You use the Business Viewpoint Client to subscribe to Business Viewpoint Studio master dimensional data from within Transformer.
Impromptu Query Definition (.iqd) files are generated from either IBM Cognos Impromptu or Framework Manager (as externalized query files). .iqd files are Transformer data sources that point to source databases specified in the cognos.ini file.
The Series 7 IQD Bridge component must be installed, and can be installed only on a supported IBM® Cognos® Series 7 version 7.4 platform. For more information, see Series 7 IQD Bridge.
When importing an IQD data source in Transformer, accept the default Isolation level or specify an alternative. For more information, see Isolation Levels for an IQD Data Source.
Tip: The Transformer version 8.x .ini file name and installation location have changed from the Transformer version 7.x file name and installation location. The Transformer preferences file name (previously the .ini file) is cogtr.xml, which is located in the installation_location\configuration directory. For information about accessing databases using the Series 7 IQD Bridge, see Connecting to an IQD Data Source.
Flat files are an excellent data source for achieving fast cube builds. Flat files are also recommended when you want to import OLAP fact data.
With Delimited-field text with column titles, input values are obtained from an ASCII text file with one record per line or row. The values in the first line represent column names.
When importing a flat file data source in Transformer, specify how the fields (column values) are delimited in the Field delimiter box, and either accept the default Character set or specify an alternative.
In Transformer version 8.x, you can specify Unicode as a valid character set. Using an IBM Cognos 8 report, you can define the fact query and the data you want to import for your PowerCube. You can then export the report to a .csv file that can in turn be used as the fact query data source in your Transformer model.
For more information, see Character Sets Used with Delimited Text Data Sources.
Flat files are an excellent data source for achieving fast cube builds. Flat files are also recommended when you want to import OLAP fact data.
With Delimited-field text, input values are obtained from an ASCII text file with one record per line.
When importing a flat file data source in Transformer, specify how the fields (column values) are delimited in the Field delimiter box, and either accept the default Character set or specify an alternative.
In Transformer version 8.x, you can specify Unicode as a valid character set. Using an IBM Cognos 8 report, you can define the fact query and the data you want to import for your PowerCube. You can then export the report to a .csv file that can in turn be used as the fact query data source in your Transformer model.
For more information, see Character Sets Used with Delimited Text Data Sources.
With an Access table, input values are obtained from a Microsoft Access file. Transformer uses the Microsoft ActiveX Data Objects (ADO) driver to access the data.
In Transformer, select a Table or range and a Character set, such as DOS Code Page or Windows ANSI.
With an Access query, the source table is described in a Microsoft Access Query (.mdb file). Transformer uses either the Microsoft ActiveX Data Objects (ADO) driver to access the data, or runs the SQL queries stored in the .mdb file to get the source columns from an ODBC-enabled server database.
Note: Password-protected files are not supported.
In Transformer, select a Table or range and a Character set, such as DOS Code Page or Windows ANSI.
With an Excel crosstab, input values are obtained from an Excel crosstab file.
Transformer supports both .xls and Excel 2007 .xlsx file formats. You must have Excel 2007 or the 2007 Office System Driver data connectivity components installed on your computer to select the .xlsx file format when browsing for a data source.
For more information, see Named Ranges.
With an Excel database, input values are obtained from a Microsoft Excel spreadsheet database file.
Transformer supports both .xls and Excel 2007 .xlsx file formats. You must have Excel 2007 or the 2007 Office System Driver data connectivity components installed on your computer to select the .xlsx file format when browsing for a data source.
In Transformer, select a range name from the Table or range box. For more information, see Named Ranges.
With a PowerHouse portable subfile, input values are obtained from a Cognos® PowerHouse® 4GL portable subfile.
In Transformer, specify the portable subfile dictionary (.psd) file or the data (.ps) file. Accept the default Character set or specify an alternative. For more information, see PowerHouse Data Source Parameters.
With Fixed-field text, input values are obtained from an ASCII text file with one record per line. Each field starts at the byte immediately following the preceding field; the width of each field occupies a specified number of bytes. Each row ends with a text line delimiter.
In Transformer, manually add columns to your data source by specifying the position and length, in bytes, of each column in the source file. Accept the default Character set or specify an alternative.
With Fixed field and record without CR LF, input values are obtained from an ASCII text file. Each field starts at the byte immediately following the preceding field; the width of each field occupies a specified number of bytes. The record end is not marked by a text line delimiter.
In Transformer, manually add columns to your data source by specifying the position and length, in bytes, of each column in the source file. Accept the default Character set or specify an alternative.