IBM Cognos Transformer
Version 10.1.1
User Guide
Note
Before using this information and the product it supports, read the information in “Notices” on page 377.
Product Information
This document applies to IBM Cognos Business Intelligence Version 10.1.1 and may also apply to subsequent
releases. To check for newer versions of this document, visit the IBM Cognos Information Centers
(http://publib.boulder.ibm.com/infocenter/cogic/v1r0m0/index.jsp).
Licensed Materials - Property of IBM
© Copyright IBM Corporation 2007, 2011.
US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract
with IBM Corp.
Contents
Introduction .................................xiii
Chapter 1. What's New? .............................1
New Features in Version 10.1.1 .............................1
New Features in Version 10.1.0 .............................1
Publishing Cube Groups ..............................1
Deprecated Features in Version 10.1.0 ...........................1
Removed Features in Version 10.1.0............................1
PowerCube Connection (PCConn) Utility .........................1
Framework Manager Externalized Queries ........................2
Chapter 2. Planning Your Model .........................3
Dimensional Modeling Workflow ............................3
Analyzing Your Requirements and Source Data .......................4
Preprocessing Your Data ..............................4
Building a Prototype................................6
Refining Your Model ...............................8
Diagnose and Resolve Any Design Problems .......................9
Upgrade an IBM Cognos Series 7 Model..........................11
Chapter 3. Data Sources for Your Model .....................13
Techniques for Designing Data Sources ..........................13
Data Source Types .................................13
IBM Cognos Package or Report ............................14
Business Viewpoint Studio .............................18
IBM InfoSphere Warehouse Cubing Services .......................18
TM1 Cubes ..................................19
Impromptu Query Definition File ...........................19
Delimited-field Text with Column Titles .........................19
Delimited-field Text ...............................19
Access Table ..................................20
Access Query ..................................20
Excel Crosstab .................................20
Excel Database .................................20
PowerHouse Portable Subfile ............................21
Fixed-field Text .................................21
Fixed Field and Record Without CR LF .........................21
Designing Successful IBM Cognos PowerCubes .......................21
Conformed Dimensions ..............................21
IBM Cognos Business Keys .............................22
Member Unique Names ..............................22
Resolving Uniqueness Problems in Your Data Source ....................23
Creating a Model .................................24
Creating a Model Using an IBM Cognos Package or Report ..................25
Creating a Model Using an SAP BW Package .......................27
Creating a Model Using Other Data Sources .......................28
Edit Existing Prompts in IBM Cognos Reports and Packages ...................29
Generate a File of Prompt Values for Use in the Command Line.................31
Remove Obsolete Prompts .............................31
Specifying a Segmenting Prompt for an SAP BW Query ....................32
Change a Data Source Type ..............................33
Create Dimensions from the Dimension Map Using OLAP and DMR Packages .............33
Adding an IBM Cognos Data Source to an Existing Model ...................34
Add Additional Queries to Existing IBM Cognos Data Sources .................35
Adding Other Data Sources to an Existing Model.......................36
© Copyright IBM Corp. 2007, 2011 iii
Synchronizing Columns with Your Data Source .......................37
Synchronizing Columns with Text Data Files or IQDs ....................38
Connecting to an IQD Data Source ...........................39
Isolation Levels for an IQD Data Source .........................39
Example - Using an .iqd File to Access a UNIX Data Source ..................40
Modify the SQL Query in an IQD Data Source .......................40
Character Sets Used with Delimited Text Data Sources .....................41
Named Ranges ..................................41
Example - Specifying Named Ranges for a Multipage Crosstab .................41
Example - Specifying a Named Range for a Spreadsheet Database ................42
PowerHouse Data Source Parameters...........................43
Using Multiple Data Sources in Your Model ........................43
Data Source Scope ................................44
Control When the Source Data Is Read .........................46
Defining Columns .................................47
Troubleshooting Issues Related to Column Names .....................48
Troubleshooting Issues Related to Date Columns ......................48
Define Columns in a Fixed-field Text Data Source .....................49
Scale Input Values ................................50
Set the Level of Detail for Dates ...........................51
Specify Monthly or Quarterly Time Arrays ........................51
Modify Date Categories When Spanning Two Centuries ...................54
Signons.....................................54
Creating a Data Source Signon ............................55
Creating an IBM Cognos Signon ...........................56
Chapter 4. Structuring Your Data Into Dimensions .................57
AutoDesign ...................................57
Creating a New Dimension ..............................58
Creating Dimensions in the Dimension Map Using Relational Data Sources .............58
Creating Dimensions from the Dimension Map Using OLAP and DMR Packages ...........58
Define a Calculated Column ..............................59
Example - Using a Calculated Column to Add an Exception Dimension ..............60
Example - Using a Calculated Column to Support Allocated Measures ..............60
Adding Levels and Categories to a Dimension........................61
Add Source Levels to a Dimension ..........................61
Add Manual Levels to a Dimension ..........................62
Create Categories Manually .............................63
Creating Calculated Categories ............................63
Order Categories Within Levels............................66
Order Categories Using a Global Preference Setting .....................67
Create Unbalanced Level Hierarchies Within a Dimension...................67
Drill-down Paths .................................68
Create an Alternate Drill-down Path ...........................68
Define a Scenario Dimension and a Cube Opening Level ....................69
Setting Up the Time Dimension.............................70
Creating the Time Dimension ............................71
Format Date Values................................73
Set up Fiscal Years, Quarters, and Months ........................74
Set up Calendar and Fiscal Years Within a Single Time Dimension ................74
Specify How Weeks Split When Spanning a Higher-level Time Period ...............75
Set up Lunar Time Periods .............................76
Specify How Extra Weeks Get Added to Lunar Years ....................77
Limit the Range of Dates Included in the Model ......................77
Set up a Custom Time Dimension ...........................78
Setting Up Relative Time Categories ...........................79
Set the Current Period Automatically ..........................80
Set the Current Period Manually ...........................81
Track Changes in a Measure over a Specific Time Period ...................81
Track Changes in a Measure for a Period-to-Date ......................82
Track Changes in a Measure over Several Time Periods....................83
iv IBM Cognos Transformer Version 10.1.1: User Guide
Track Changes in a Measure in Future Time Periods .....................84
Track Monthly Performance Measures in Different Time Periods .................85
Setting up Special Categories .............................85
Create a New Special Category ............................85
Create Special Category Levels ............................86
Disable Rollup on Special Categories ..........................87
Resolving the Parentage of Orphan Categories........................88
Resolve Invalid Date Errors ..............................88
Adjust the Date Range to Encompass Early and Late Dates ...................89
Set up a Manual Level for Unknown Categories .......................89
Prevent New Categories from Being Added to a Dimension ...................90
Verifying Your Model ................................90
Generating Categories from a Specified Data Source .....................91
Chapter 5. Modeling Your Key Performance Measures ...............93
Add a Regular Measure ...............................94
Define a Measure Folder ...............................95
Define a Measure that Counts Categories .........................95
Define a Calculated Measure .............................97
Define When Measures are Calculated ..........................97
Specify How Missing Values Appear ...........................98
Reverse the Sign of Measure Values in Financial Models ....................99
Cognos Transformer Functions .............................99
Controlling How Measures Roll Up ...........................100
Set a Regular Rollup Function for Measures .......................101
Create Cubes with External Rollups ..........................102
Set a Time State Rollup Function for Measures ......................103
Ignore Null and Missing Values in Specified Time State Rollups ................105
Set Regular and Time State Rollup Together .......................106
Consolidation ..................................107
Example - Consolidating Data Using Duplicates Rollup (Sum) .................107
Set a Duplicates Rollup Function for Measures ......................108
Allocating Measures ................................109
Show the Scope for a Measure............................110
Set Dimension Allocation .............................111
Set Level Allocation ...............................112
Set Category Allocation ..............................113
Suppress Allocation ...............................114
Setting Up Currency Conversion ............................115
Enabling Conversion Using a Base Currency Table .....................116
Enable Conversion Using a Euro Table .........................118
Update a Currency Table Manually ..........................121
Chapter 6. Creating PowerCubes ........................123
Create a Single PowerCube..............................123
Create a Cube Group................................125
Defining a Time-based Partitioned Cube .........................125
Advantages of a Time-based Partitioned Cube ......................126
Disadvantages of a Time-based Partitioned Cube .....................126
Create a Time-based Partitioned Cube Group .......................128
Processing Issues Related to Time-based Partitioned Cubes ..................128
Customizing a Time-based Partitioned Cube .......................131
Exclude Measures from a Cube ............................131
Omit Dimensions from a Cube ............................132
Customizing Cube Content with Views..........................132
Create a Dimension View .............................133
Omit Categories Using Suppress ...........................134
Omit Categories Using Cloak ............................135
Omit Categories Using Exclude ...........................136
Omit Descendant Categories Using Summarize ......................137
Contents v
Omit Categories Using Apex ............................138
Setting Up Drill-through Targets ............................139
Drill Through in IBM Cognos BI ...........................139
Drill Through in IBM Cognos Series 7 .........................140
Use Alternate Data Sources for Cube Creation .......................143
Build a Subset of Your Cube Data for Testing Purposes ....................143
Update the PowerCube Metadata............................144
Check Cube Status ................................144
Chapter 7. Adding Security ..........................147
PowerCubes with IBM Cognos Series 7 Security ......................147
Choosing the Type of Security to be Applied ........................147
IBM Cognos Security Objects .............................148
Create Member-based Security ............................149
Update Model Security ..............................151
Union of Custom Views ...............................151
Example - Two Custom Views, Each with an Apex in the Same Dimension .............152
Example - Apexed Custom View Cloaked by Another Custom View in the Same Dimension .......153
Example - Apexed Custom View Excluded by Another Custom View in the Same Dimension ......153
Example - Union of Two Apexed Custom Views in Different Dimensions .............154
Example - Union of Two Multi-dimensional Apexed Custom Views ...............154
Example - Union of Excluded Categories in Two Dimensions .................155
Examples - Unions of Excluded Categories in the Same Dimension ...............155
Example - Union of Excluded and Cloaked Categories in the Same Dimension ...........156
Example - Union of a Custom Views with Omitted Dimensions ................156
Create Password-protected Cubes ...........................157
Combining Custom Views with Dimension Views ......................157
Block Total Values for Parent Categories with Excluded Children .................158
Upgrade an IBM Cognos Series 7 Secured PowerCube ....................158
Chapter 8. PowerCubes in Production Environments ...............161
Managing Languages and Locales ...........................161
Publishing PowerCubes ...............................162
Publishing a PowerCube Using the Wizard .......................163
Publishing a PowerCube Using Settings Stored in the Model .................165
Creating a Publish Specification to Publish One PowerCube ..................166
Creating a Publish Specification to Publish All PowerCubes in the Model .............167
Publishing a Cube Group .............................168
PowerCube Load Balancing .............................168
Maintaining Models and Cubes ............................169
Source Data Updates ...............................169
Model Updates .................................169
Cube Updates .................................170
Automatic Creation of Multifile Cubes .........................170
Recovering a Failed Model .............................171
Matching Model and Source Columns .........................172
Move Categories When Source Data Changes ......................173
Cleaning up Your Models and Cubes .........................174
Modifying a PowerCube..............................179
Update Cubes Incrementally ............................179
Choosing a Partitioning Strategy ...........................180
Updating Published PowerCubes and PowerCube Connections..................186
Copying and Activating a Newer Version of a Published PowerCube...............187
Activating a Published PowerCube Using pcactivate ....................189
Chapter 9. Guidelines for Optimizing Production Environments...........191
Building PowerCubes in UNIX or Linux .........................191
Guidelines for Model Design, Cube Creation, and System Maintenance Process ...........192
Setting Up Data Sources for UNIX and Linux Cubes ....................192
Using IBM Cognos Reports to Create a Data Source ....................193
vi IBM Cognos Transformer Version 10.1.1: User Guide
Adding Security to a UNIX or Linux Cube .......................194
Controlling Processing with Preference Settings or Environment Variables ..............194
Preferences ..................................195
Environment Variables ..............................200
Addressing Common UNIX and Linux Processing Problems...................200
Checking UNIX and Linux Job Status .........................200
Scheduling Batch UNIX and Linux Production Jobs ....................201
Improving Performance in a UNIX Production Environment .................201
Performing Incremental Updates on UNIX and Linux ....................201
Example - Updating a Model Using an MDL Script.....................201
Example - Restarting a Failed Process from a Checkpoint File .................202
Example - Restarting a Failed Process from the Beginning ..................202
Reducing Build Times for Large PowerCubes ........................202
Multiprocessing with Cognos Transformer Server .....................203
Memory Allocation ...............................204
Reconfiguration of the Hard Drive ..........................204
Allocating Sufficient Space for the Temporary Files .....................205
Optimization of the Operating Environment .......................205
Redistributing Files ...............................206
Optimization of Gateway Settings for IBM Cognos Series 7 IQDs ................207
Keeping Model and Cube Sizes Within Practical Limits ...................207
Using the Log File to Analyze Processing Bottlenecks ....................208
Appendix A. Command Line Options ......................209
Command Line Syntax ...............................210
Command Line Options ...............................211
-a option ...................................213
-c option ...................................214
-d option ...................................214
-e option ...................................217
-f option ...................................217
-g option ...................................225
-h option ...................................226
-i option ...................................226
-j option ...................................226
-k option ...................................227
-l option ...................................228
-m option ..................................229
-n option ...................................229
-nologo option .................................230
-o option ...................................230
-ox option ..................................231
-p option ...................................231
-r option ...................................231
-s option ...................................232
-t option ...................................232
-u option ...................................233
-v option ...................................233
-x option ...................................234
-y options ..................................234
Backward Compatibility ...............................236
Sample Windows Preference Files for IBM Cognos Series 7 ..................236
Sample UNIX and Linux Preference Files for IBM Cognos Series 7 ...............237
Appendix B. Troubleshooting .........................239
Accessing Error Message Help ............................239
Known Issues When Modeling in IBM Cognos Transformer ...................239
BAPI Error Occurs After the Prompt Specification File Edited Manually ..............239
Unable to Access an IQD Data Source Using a Sybase Database Connection ............239
Unable to Use an IQD Created in Framework Manager That Contains an Oracle Stored Procedure .....240
Contents vii
Preventing Errors When Model Calculations Use Double Quotation Marks .............240
Framework Manager and Transformer May Display Different Locale Session Parameters for Some Languages 241
Regular Columns Cannot Be Converted to Calculated Columns and Vice Versa ...........241
Transformer Takes a Long Time to Retrieve Data from an SAP-based Data Source ..........241
Categories Missing When Creating a Transformer Model Based on an SAP Query Containing a Manually
Created SAP Structure ..............................242
Error Occurs When Creating a PowerCube Containing an SAP Unbalanced Hierarchy .........242
Rebuilding a PowerCube Soon After Publishing Produces a TR0787 Error .............242
Using the cube group in the sample model Employee expenses.mdl ...............242
Known Issues When Using PowerCubes in the IBM Cognos Studios ................243
Not Yet Optimized IBM Cognos PowerCubes May Open Slowly in IBM Cognos BI ..........243
Analysis Studio Shows the Wrong Currency Symbol ....................244
Changes to Decimals in Currency Formats .......................244
Ragged or Unbalanced Hierarchies Result in Unexpected Behavior ...............245
Unable to Open the Great Outdoors Sales.mdl Sample Model and Generate Cubes ..........245
Unable to Publish a PowerCube ...........................246
Appendix C. IBM Cognos Transformer Error Messages ..............247
TR0104 ....................................247
TR0105 ....................................247
TR0106 ....................................248
TR0107 ....................................248
TR0108 ....................................248
TR0109 ....................................249
TR0110 ....................................249
TR0111 ....................................250
TR0112 ....................................250
Solution for Windows ..............................250
Solution for UNIX or Linux ............................250
TR0113 ....................................251
TR0114 ....................................251
TR0116 ....................................251
TR0118 ....................................251
TR0128 ....................................252
TR0131 ....................................252
TR0132 ....................................252
TR0133 ....................................252
TR0137 ....................................253
TR0149 ....................................253
TR0202 ....................................254
TR0203 ....................................254
TR0205 ....................................254
TR0206 ....................................254
TR0207 ....................................254
TR0208 ....................................255
TR0209 ....................................255
TR0210 ....................................255
TR0214 ....................................256
TR0215 ....................................256
TR0217 ....................................256
TR0301 ....................................257
TR0303 ....................................257
TR0404 ....................................257
TR0408 ....................................257
TR0412 ....................................258
TR0420 ....................................258
TR0423 ....................................258
TR0476 ....................................259
TR0500 ....................................259
TR0501 ....................................259
TR0502 ....................................259
viii IBM Cognos Transformer Version 10.1.1: User Guide
TR0503 ....................................260
TR0504 ....................................260
TR0505 ....................................260
TR0507 ....................................261
TR0508 ....................................261
TR0510 ....................................261
TR0514 ....................................262
TR0515 ....................................262
TR0518 ....................................262
TR0519 ....................................262
TR0523 ....................................263
TR0524 ....................................263
TR0525 ....................................263
TR0528 ....................................264
TR0534 ....................................264
TR0535 ....................................264
TR0536 ....................................265
TR0538 ....................................265
TR0540 ....................................266
TR0541 ....................................266
TR0552 ....................................266
TR0605 ....................................267
TR0606 ....................................267
TR0607 ....................................267
TR0613 ....................................268
TR0621 ....................................268
TR0623 ....................................268
TR0624 ....................................269
TR0657 ....................................269
Matching Model and Source Columns for Text Files or IQDs .................269
Matching Model and Source Columns for IBM Cognos Data Sources ...............270
TR0700 ....................................270
TR0701 ....................................271
TR0702 ....................................271
TR0705 ....................................271
TR0713 ....................................272
TR0745 ....................................272
TR0749 ....................................273
TR0750 ....................................273
TR0751 ....................................273
TR0752 ....................................273
TR0753 ....................................274
TR0800 ....................................274
TR0802 ....................................274
TR0803 ....................................275
TR0804 ....................................275
TR0805 ....................................275
TR0806 ....................................275
TR0807 ....................................276
TR0808 ....................................276
TR0809 ....................................276
TR0810 ....................................276
TR0811 ....................................277
TR0812 ....................................277
TR0813 ....................................277
TR0815 ....................................278
TR0816 ....................................278
TR0817 ....................................278
TR0900 ....................................278
TR0904 ....................................279
TR0906 ....................................279
Contents ix
TR0907 ....................................279
TR0914 ....................................280
TR0917 ....................................280
TR0919 ....................................280
TR1003 ....................................280
TR1005 ....................................281
TR1100....................................281
TR1101....................................281
TR1102....................................281
TR1106....................................282
TR1109....................................282
TR1304 ....................................282
TR1307 ....................................282
TR1308 ....................................283
TR1309 ....................................283
TR1310 ....................................283
TR1312 ....................................284
TR1319 ....................................284
TR1320 ....................................284
TR1502 ....................................285
TR1503 ....................................285
TR1601 ....................................285
TR1700 ....................................286
TR1703 ....................................286
TR1900 ....................................286
TR1903 ....................................287
TR1907 ....................................287
TR2000 ....................................287
TR2001 ....................................288
TR2002 ....................................288
TR2306 ....................................288
TR2307 ....................................288
TR2308 ....................................289
TR2312 ....................................289
TR2313 ....................................290
TR2314 ....................................290
TR2316 ....................................290
TR2317 ....................................291
TR2318 ....................................291
TR2319 ....................................291
TR2320 ....................................292
TR2321 ....................................292
TR2322 ....................................292
TR2323 ....................................293
TR2324 ....................................293
TR2325 ....................................293
TR2326 ....................................293
TR2502 ....................................294
TR2503 ....................................294
TR2504 ....................................294
TR2505 ....................................295
TR2506 ....................................295
TR2507 ....................................295
TR2508 ....................................296
TR2509 ....................................296
TR2510 ....................................296
TR2600 ....................................296
TR2601 ....................................297
TR2606 ....................................297
TR2608 ....................................297
TR2700 ....................................298
x IBM Cognos Transformer Version 10.1.1: User Guide
TR2701 ....................................298
TR2702 ....................................299
TR2703 ....................................299
TR2704 ....................................299
TR2705 ....................................300
TR2706 ....................................300
TR2707 ....................................300
TR2708 ....................................301
TR2710 ....................................301
TR2711 ....................................301
TR2712 ....................................302
TR2713 ....................................302
TR2714 ....................................302
TR2715 ....................................303
TR2716 ....................................303
TR2717 ....................................303
TR2718 ....................................304
TR2719 ....................................304
TR2720 ....................................304
TR2723 ....................................305
TR2724 ....................................305
TR2725 ....................................305
TR2726 ....................................306
TR2727 ....................................306
TR2728 ....................................306
TR2729 ....................................307
TR2731 ....................................307
TR2732 ....................................307
TR2733 ....................................308
TR2734 ....................................308
TR2735 ....................................308
TR2736 ....................................309
TR2737 ....................................309
TR2740 ....................................309
TR2800 ....................................309
TR3101 ....................................310
TR3124 ....................................310
TR3311 ....................................310
TR3519 ....................................310
Appendix D. Reference ............................313
The Interface of Cognos Transformer ..........................313
Lists ....................................313
Data Source Viewer and SQL Tab...........................315
Dimension Map ................................315
Scope Maps ..................................317
Category Counts ................................318
Diagrams...................................318
Allocation Types .................................320
Category Actions in Diagrams.............................320
Category Inclusion Settings..............................322
Date Formats and Functions .............................323
Default File Locations ...............................325
cogtr.xml File Settings ...............................326
Global Preference Settings .............................327
Relative Time Settings ...............................328
Example - Creating Custom Relative Time Periods .....................329
PowerCube Optimization Methods ...........................331
Rollup Functions .................................331
Special Characters .................................334
Cognos Transformer Samples .............................335
Contents xi
Sample Models .................................336
Sample PowerCubes ...............................336
Sample Data Sources ...............................337
Appendix E. Guidelines for Working with SAP BW Data for Use in Transformer ....339
Working with SAP BW Data Using a Package in Framework Manager ...............339
Creating a BW Query in SAP Business Explorer Query Designer ................340
Creating a Package in Framework Manager .......................343
Creating a Model in Cognos Transformer ........................347
Working with SAP BW Data Using Externalized CSV Files in Framework Manager ...........349
SAP BW Query Requirements ............................351
Framework Manager Considerations .........................352
Building PowerCubes from SAP BW Data ........................353
Appendix F. IBM Cognos Transformer Expression Editor..............355
Building Expressions ................................356
Building an If-Then-Else Calculated Expression ......................357
Functions....................................357
Date Functions .................................358
Numeric Functions ...............................362
Text Functions .................................367
Aggregate Functions ...............................371
Constants ...................................373
Operators ...................................374
Mathematical, Logical, and String Operators .......................374
Comparison Operators ..............................375
Notices ...................................377
Glossary ..................................381
A......................................381
C......................................381
D......................................381
F......................................381
G......................................382
H......................................382
I.......................................382
L......................................382
M......................................382
N......................................382
P......................................382
Q......................................383
U......................................383
Index ....................................385
xii
IBM Cognos Transformer Version 10.1.1: User Guide
Introduction
This document is intended for use with IBM®Cognos®Transformer, the OLAP
modeling component delivered with IBM Cognos Business Intelligence.
The IBM Cognos Transformer User Guide describes PowerCube modeling procedures
and concepts, product functionality, and related terminology. It includes reference
information that supplements the task- and process-oriented topics, as well as
troubleshooting tips and detailed help for the more commonly encountered error
messages.
You can use this document to help you model and build PowerCubes with the
Cognos Transformer user interface, or to perform production-related tasks from the
Windows, UNIX, or Linux command line.
For information about creating automation scripts using Model Definition
Language (MDL), see the Cognos Transformer IBM Cognos Transformer Developer
Guide.
For information about creating automation scripts using OLE automation, see the
IBM Cognos Transformer Automation Guide.
Audience
This information is for new IBM Cognos Transformer users and IBM Cognos Series
7 cube modelers who are seeking guidance as they migrate their PowerCubes and
related applications to the IBM Cognos environment. Advanced database
administration (DBA) or data modeling skills are not required. Business-relevant
examples, samples, and code examples are supplied in context.
Finding information
To find IBM Cognos product documentation on the web, including all translated
documentation, access one of the IBM Cognos Information Centers. Release Notes
are published directly to Information Centers, and include links to the latest
technotes and APARs.
You can also read PDF versions of the product release notes and installation guides
directly from IBM Cognos product disks.
Accessibility features
This product does not currently support accessibility features that help users with
a physical disability, such as restricted mobility or limited vision, to use this
product.
Forward-looking statements
This documentation describes the current functionality of the product. References
to items that are not currently available may be included. No implication of any
future availability should be inferred. Any such references are not a commitment,
promise, or legal obligation to deliver any material, code, or functionality. The
© Copyright IBM Corp. 2007, 2011 xiii
development, release, and timing of features or functionality remain at the sole
discretion of IBM.
Samples disclaimer
The Great Outdoors Company, GO Sales, any variation of the Great Outdoors
name, and Planning Sample depict fictitious business operations with sample data
used to develop sample applications for IBM and IBM customers. These fictitious
records include sample data for sales transactions, product distribution, finance,
and human resources. Any resemblance to actual names, addresses, contact
numbers, or transaction values is coincidental. Other sample files may contain
fictional data manually or machine generated, factual data compiled from
academic or public sources, or data used with permission of the copyright holder,
for use as sample data to develop sample applications. Product names referenced
may be the trademarks of their respective owners. Unauthorized duplication is
prohibited.
xiv IBM Cognos Transformer Version 10.1.1: User Guide
Chapter 1. What's New?
This chapter contains a list of new and removed features for this release. It also
contains a cumulative list of similar information for previous releases. Knowing
this information will help you plan your upgrade and application deployment
strategies and the training requirements for your users.
For information about upgrading, see the IBM Cognos Business Intelligence
Installation and Configuration Guide.
For an overview of new features for this release, see the IBM Cognos Business
Intelligence New Features Guide.
For changes to previous versions, see “New Features in Version 10.1.0”
To review an up-to-date list of environments supported by IBM Cognos products,
such as operating systems, patches, browsers, Web servers, directory servers,
database servers, and application servers, visit the IBM Cognos Customer Center
http://www.ibm.com/software/data/cognos/customercenter.
New Features in Version 10.1.1
There are no new features in this release of IBM Cognos Transformer.
New Features in Version 10.1.0
These are the new features in this release of IBM Cognos Transformer.
Publishing Cube Groups
You can now publish all or selected PowerCubes in a cube group. The data source
connections and packages are automatically created or updated in IBM Cognos
Connection for all cubes in the group.
This solution does not change the way regular PowerCubes and time-based
partitioned cubes are published. For more information, see “Publishing
PowerCubes” on page 162.
Deprecated Features in Version 10.1.0
There are no deprecated features in this version.
Removed Features in Version 10.1.0
The following features are removed in version 10.1.0.
PowerCube Connection (PCConn) Utility
This utility is no longer supported by IBM Cognos Transformer. It was replaced by
the copy and activate functionality.
For more information, see “Updating Published PowerCubes and PowerCube
Connections” on page 186.
© Copyright IBM Corp. 2007, 2011 1
Framework Manager Externalized Queries
Framework Manager IQDs, or externalized queries, are no longer supported as
data sources in Cognos Transformer. Instead, you can use IBM Cognos packages
and reports as data sources.
For more information, see “IBM Cognos Package or Report” on page 14.
2 IBM Cognos Transformer Version 10.1.1: User Guide
Chapter 2. Planning Your Model
IBM Cognos Transformer is a data modeling tool designed for use with IBM
Cognos 8 version 8.3 and subsequent releases.
You use this component to create a model, a business presentation of the
information in one or more data sources. After you choose a supported product
locale (language), add dimensional metadata, specify the measures (performance
indicators), and apply custom views, you can create PowerCubes based on this
model. You can deploy these cubes to support OLAP reporting and analysis.
This section provides a high-level overview of the modeling and planning process
to meet the OLAP needs of your users, as well as information about how to
upgrade an IBM Cognos Series 7 Transformer model.
The documented workflow follows a logical sequence, beginning with analyzing
your requirements and building a prototype model. If you have already completed
this planning stage, you can proceed to the sections of this document that deal
with data sources (Chapter 3), dimensions (Chapter 4), and measures (Chapter 5).
Dimensional Modeling Workflow
IBM Cognos Transformer is a proven and relatively simple tool for modeling
dimensional hierarchies and levels for PowerCubes.
After you relate the dimensions to your business performance indicators, you can
create powerful, secure cubes to be used for reporting and drill-through analysis in
the IBM Cognos studios.
v Carefully analyze your users' OLAP reporting requirements.
v If you have not already done so, build a prototype model.
v Choose your transactional and structural data sources and import the facts
(measures) and metadata (dimensions).
v Map your metadata into dimensions, and your facts into measures.
v Verify the model and resolve any ambiguities.
v Organize the data in your model into customized dimension views or cube
groups.
v Apply security and create custom views to control access to sensitive
information.
v Create and publish PowerCubes to IBM Cognos Connection.
v Manage and maintain your models, cubes, and reports for optimal effectiveness.
Troubleshooting tips are provided in this document and in the Administration and
Security Guide. This document also provides an overview of the functions
supported by Cognos Transformer, and how they may be used to create calculated
expressions. For more information, see “Cognos Transformer Functions” on page
99.
For information about scripting, see the Cognos Transformer Developer Guide and
the Cognos Transformer Automation Guide.
© Copyright IBM Corp. 2007, 2011 3
Analyzing Your Requirements and Source Data
To ensure that you develop an effective business intelligence model, we
recommend that you begin by carefully analyzing your users, the OLAP reports
they require, and your source data.
Use the following questions to analyze your users' OLAP reporting needs:
v What reports do users currently use? Which reports do they use most
frequently? Which reports do they use only rarely?
v Does each group require different reports? Are there some reports that are
required by all user groups?
v Do users need higher-level (summary) reports, detailed drill-through reports, or
both?
v How frequently are the measures in the report updated? How frequently do the
reports themselves change? Does the frequency vary from group to group?
v How often are reports required? Can you trade off frequency to ensure
accuracy? For example, if your users ask for monthly reports and the data
source is refreshed weekly, the data will always be current. However, if your
users want daily reports, the data will only be up to date on the first day of the
weekly cycle.
Analyze your source data, using questions such as the following:
v Does the data come from one source or many? What format is it in: flat files,
spreadsheets, or databases? Does it need to be converted to a supported data
source type before it is imported?
v Can you optimize existing queries by building new Cognos Transformer queries
using the metadata modeled in IBM Cognos packages or reports?
v How many records are there? By how much do you expect the volume of data
to increase?
v How much of the data is static and how much changes gradually over time?
Can you create different data sources for static and non-static data to support
incremental updates (an option that shortens cube creation time by appending
new data to a cube instead of recreating it)?
v How much data preparation is required?
Ensure that the source values that feed the categories are unique and, if feasible,
that you aggregate or otherwise preprocess your data before importing it. For
more information, see “Preprocessing Your Data.”
v Are linked measures from different data sources updated at the same time?
v Must you create additional data sources to accurately model your organization?
When you have answered these questions, you are ready to begin preparing your
source data for import and designing your prototype.
Preprocessing Your Data
Presort, clean, or consolidate your data to maximize reporting flexibility and
performance.
Preprocess data to achieve the following benefits:
v Presorted records are processed more quickly in Cognos Transformer.
v When you streamline your source data to contain only the information needed
for the model, read times are faster in Cognos Transformer.
4 IBM Cognos Transformer Version 10.1.1: User Guide
v You can use Cognos Transformer to presummarize the data when your users do
not require access to all the details in the source.
For example, if your organization processes 50,000 transactions daily, and you
create the cube weekly, you can summarize the transactions at the weekly level
before Cognos Transformer begins processing. This will greatly speed up cube
creation.
v Consolidation, combining records with identical non-measure values, reduces the
size of the cube and improves performance in your reporting application.
Consolidation is enabled by default in Cognos Transformer. Evaluate your data
to see if it can be further consolidated by using the Duplicates rollup or
Regular rollup features of Cognos Transformer.
For consolidation purposes, non-measure values are considered identical if they
meet any of the following criteria for the particular rollup:
– The source data contains transactions with identical non-measure values.
For example, two sales of the same product are made to the same customer
on the same day, but the colors differ. If colors are omitted from a dimension
view using the Suppress or Summarize command on the Diagram menu, the
sales records will have identical non-measure values.
– Records become identical when a dimension is omitted from the cube.
For example, two sales of the same product are made at different stores on
the same day. If the Stores dimension is removed from the model, these sales
records will have identical non-measure values.
– Records become identical because of the Degree of detail setting on the Time
tab of the Column property sheet.
For example, if the Degree of detail is set to Month for a column associated
with a time dimension that includes week and day values, Cognos
Transformer ignores the week and day values in the source transactions when
consolidating records.
v For queries based on relational packages, enabling the Auto summarize feature
on the General tab of the Data Source property sheet also helps reduce the
number of rows that Cognos Transformer retrieves from the source data, further
improving cube build performance.
Separate Your Structural and Transactional Data
Processing time improves when Cognos Transformer can query your structural and
transactional information separately. You must identify which data sources contain
purely structural information, which contain transactional information (measure
values or facts), and which contain a combination of the two.
When processing queries to create a PowerCube, Cognos Transformer orders the
queries, first reading the structural queries and then reading the transactional
queries.
Ideally, you should define each dimension or drill-down path with a separate
structural data source, and then add one or more transactional data sources to
provide the measures for those dimensions. This restructuring exercise helps to
partially normalize your data, speeding up both the category generation and cube
creation stages.
The best approach is to have unique levels near the bottom of the dimensions, and
to have the transactional queries link to the dimensions using those levels. This is
basically the star schema or snowflake method of creating dimensions in a
relational database. This type of design promotes faster processing because each
Chapter 2. Planning Your Model 5
transaction record has fewer business keys to process when identifying the
category with which the measure values are associated.
Define any transactional data sources that change frequently so that they contain a
small, concise record set, with the minimum amount of information needed to
update the data in your PowerCubes. Whenever possible, save your model with
generated category structures, to eliminate the redundant processing required to
continually rebuild them. Similarly, if your model contains long descriptions,
generate cubes from a model that is already populated with the categories
associated with those descriptions.
For more information, see “Control When the Source Data Is Read” on page 46.
Additional Data Modeling Tips
Enhance your model design by analyzing the data flow, resolving uniqueness
issues and data dictionary terms, building flexibility into your plan, and reducing
the Cognos Transformer processing load.
Consider building the following steps into your process:
v Analyze the data flow from the point at which your data is generated until the
data is input into Cognos Transformer. Determine if the data can be streamlined
or rationalized at any point, perhaps by creating a data warehouse, a series of
data marts, or a data-extract process to reorganize it.
v Resolve uniqueness issues and data dictionary terms before you merge two sets
of data into one model. Ensure data integrity by checking your column joins;
outer joins or table aliases may be required. Remember that Cognos Transformer
is not a relational database tool, and cannot perform joins between the columns
of different data sources. If you need to set up database joins, use a modeling
tool such as Framework Manager to create the joins, and then publish the
Framework Manager package for use in Cognos Transformer.
v Wherever possible, build flexibility into your plan. Use a different source file for
each aspect of your business, and organize the data sources in your model so
that each data source supplies the data for a different dimension. That way, you
can add more information into your cube as your business evolves, even if the
data comes from different software applications, platforms, departments, or
locations.
v Improve performance by continually striving to reduce the Cognos Transformer
processing load.
Building a Prototype
To field-test the accuracy of your analysis, build an initial model or prototype that
reflects the needs of the key decision makers in your company.
Base your prototype on an existing set of frequently used, stable OLAP reports,
and use the following checklist:
Procedure
1. Identify Measures
Measures are the numbers you use to gauge your organization's performance.
You should choose the critical success factors in your business as your
measures. Examples of typical measures include sales revenues, profit margins,
and response times.
6 IBM Cognos Transformer Version 10.1.1: User Guide
If you have multiple data sources, you must relate the dimensions and levels of
your model to the data source that contains the columns to be used for each
measure.
Your model is more effective if your measures are applicable to more than one
dimension. For example, if your dimensions are products, locations, and
customers, your measures should bridge these dimensions.
2. Specify a Time Dimension
To ensure that your users can make period-to-period comparisons and visualize
trends over time, choose a time dimension that reflects and synchronizes
accounting periods and reporting schedules.
In most cases, your requirements are met by models based on the calendar or
fiscal year. Month, Quarter, and Year categories can be supplemented by
relative time categories automatically generated by Cognos Transformer, such
as YTD Growth, the percent-growth year-over-year.
If your organization uses particular time periods, such as lunar weeks and
months, or three 8-hour shifts per day, Cognos Transformer supports the
definition of custom time dimensions. Even if your query objects originate in
Framework Manager, you should import the necessary time-related items into
Cognos Transformer, and then define your time dimensions there.
3. Select the Data to be Modeled
You begin by identifying the data sources that contain the data for the model
you want to create.
Suppose that information about your customers is stored in a Customers table
and information about your products is stored in a Products table. Related
tables called Customer_Details and Product_Details provide additional
information about customers and products. Order information is stored in two
tables called Orders and Order_Details.
In keeping with good design practice, you decide to set up the Customers,
Customer_Details, Product, and Product_Details tables as structural data
sources, to provide the information that Cognos Transformer uses to build the
Customers and Products dimensions in your model.
The information about transactions is stored in the Orders and Order_Details
tables. For efficiency, you decide to combine the information in these tables into
a single data source called Order_Info.
The Order_Info data source contains the following information, all of which
you use to associate sales with particular customers and products:
v The order dates generate categories for the time dimension.
v Data about customers and sales representatives generates the header
information.
v The product, order quantity, and sales amount for each line item in an order
provide the sales measures.
v The cost of the order and discounts applied to it provide supplementary fact
data.
Example - Your Prototype Sales Model, on Paper
You can create an initial dimension map on paper, to make sure you have
identified all of the dimensions, levels, and categories needed in your PowerCube.
The measures to be associated with this dimensional hierarchy are Sales, Order
Qty, Cost, and Discount.
You map the dimensions of your prototype as follows:
Chapter 2. Planning Your Model 7
Order date Products Locations
Year Product Group Region
Quarter Product Class Office
Month Product Name Sales Rep
Refining Your Model
Based on your paper prototype, you create the Dimension Map for your new
model in Cognos Transformer. You begin with one data source. You can enhance
the business value of your model later, by adding more sources or manipulating
the data derived from the existing data sources.
Suppose you are initially lacking information about the staffing levels in each
branch. You can either add another data source to provide this information or use
the Category Count feature of Cognos Transformer to provide this detail. The
resulting cube and OLAP reports can then deliver value-added information about
the average sales per employee.
Models can contain any combination of the following:
v regular measures, or the numeric fact data found in a transaction file
v calculated measures, or numeric data calculated from other measures,
mathematical operators, and numeric constants
v category counts, or the number of categories in a unique level for which the
measure values are not zero or missing
v calculated categories, whereby calculated expressions apply directly to any
measure
v calculated columns, whereby new data is based on values calculated from other
columns, functions, constants, and calculated columns
Product No
Use the following checklist to help refine your model:
v Add special categories to enable quicker data access.
Group your data based on attributes that may be contributing to the success of
your enterprise, such as product color or customer income.
v Add drill-down paths to provide more detail.
A dimension normally consists of a single drill-down path with one or more
drill-down levels, representing the hierarchical organization of the information.
However, you can further subdivide your dimensions, so your report users can
analyze their data at different levels of detail.
There are no restrictions on the number of levels and drill-down paths that you
use in a dimension. However, all alternate drill-down paths must converge at a
common unique level and, for performance reasons, it is best to keep a 1:10 ratio
or less between the categories in each level.
For information on drill through using categories from alternate drill paths, see
the Administration and Security Guide.
v Allocate measures to other levels or dimensions.
If your model uses multiple data sources, consider allocating measures to levels
or dimensions with which they are not normally associated. Allocation can
provide you with new insights into your data. For example, you can associate
resource-related data to financial data.
8 IBM Cognos Transformer Version 10.1.1: User Guide
You can allocate measures over entire dimensions, over levels within an
individual dimension, or over categories within levels. When allocating
measures, use measures that come directly from your source data rather than
calculated measures, and avoid overloading your model with superfluous detail.
v Consider combining information from another functional area, such as materials
and resource planning or performance quality, with the finance or customer
profitability data already in your business model.
Begin by listing the data columns and determining if there are any gaps,
particularly in the area of cost of materials, or indicators of quality.
Next, map the new dimensions, checking that the time periods are consistent
with each other and with your financial statements. Ensure that revenue and
expense values map to those in the financial statements.
Finally, verify the relationships that exist between the various measures. If these
are not one-to-one relationships, confirm how each relates to your common
dimensions.
Example - Adding Customer Service Data to Refine Your Model
Suppose your initial model includes the following dimensional hierarchy, as well
as values for Inventory Status and Turnover Ranges.
You have data for an extensive list of measures: Sales, Order Qty, Material Cost per
Unit, Discount, Percent Gross Margin, Carrying Cost per Unit, Percent Material
Cost per Sale, Percent Carrying Cost per Sale, Sales per Customer, Percent Profit
per Segment, and Inventory Turnover.
You decide you want to monitor customer service, so you expand your model to
include indicators of service quality. The new dimensions and categories might be
encoded Reasons for Dissatisfaction or Causes of Poor Quality Service.
You must ensure that your source data provides the required measures, such as the
number of complaints, returns, and claims, or the dollar value of returns and
claims.
You can complete your model by incorporating response times, labor costs, time
lost to service claims, rework hours, scrap costs, or any other factor that
significantly affects service quality.
Diagnose and Resolve Any Design Problems
You can use any or all of the following tools and techniques to diagnose and
resolve problems in your model design.
Show Scope
To see how your measures and levels are associated with their corresponding data
sources by allocation, direct association or indirect association, use the Show Scope
command on the Edit menu.
Show Count
To verify that you have maintained a 1:10 ratio or less between the categories in
each level, use the Show Counts command on the Edit menu. Lower ratios allow
for efficient partitioning and faster cube creation times in Cognos Transformer, as
well as easier data exploration in your reporting component.
Chapter 2. Planning Your Model 9
Show Reference
To confirm the origin of every data source column associated with your
Dimension Map and see how each is used, use the Show Reference command on
the Tools menu.
Generate Categories
To confirm how the categories in a specific data source relate to your model, use
the Generate Categories command on the Run menu, with the selected data
source. To prevent the generation of categories in specific levels or entire
dimensions, select the Prohibit automatic creation of new categories check box on
the General tab of the Level or Dimension property sheets.
Create Selected Cubes
During the prototyping stage, you may want to create only certain cubes. You can
enable or disable cube creation in one of following ways:
v Change the Cube creation option on the Processing tab of the PowerCube or
Cube Group property sheet.
v Use the Create Selected PowerCube command on the Run menu.
v Use the Model Definition Language (MDL) function CreateFromCubes. For more
information, see the Cognos Transformer Developer Guide.
Check Cube Build Status
When you build a cube in Cognos Transformer, you can check the status of the
cube build at any time without opening it by using the PowerCube Status
command on the Tools menu. You can check the status of all the cubes that are
defined for a model at the same time. If your model has more than one cube, you
can apply a filter to monitor the status of cubes enabled for creation, disabled
cubes, or both.
You can also filter the cube build status settings by selectively requesting one of
the following:
v Any status, to list all cubes associated with the model, regardless of their status.
v Errors, to list cubes that were not created because they are not valid, or failed.
v Warnings, to list all cubes for which warnings were detected during a previous
create.
v Successful, to list all cubes created without errors or warnings, having a status
of OK.
Consult the Error Message and Troubleshooting Help
In addition to the troubleshooting topics in the User Guide, help is available from
Help buttons in some error messages to help you resolve any model design
problems.
Review the Resulting Reports With Your Users
After you generate a few reports from your prototype, ask for feedback from
representative users by posing open-ended questions. If you are the IT specialist,
involve an experienced business analyst in the process.
10 IBM Cognos Transformer Version 10.1.1: User Guide
Together, try to develop and maintain a list of follow-up questions, such as the
following:
v Does each dimension level generate valid data, with measures that are properly
associated or coordinated, for every data source?
Try to spot measures that do not roll up as expected, or that are not additive in
every dimension.
v Are ranges or qualitative values coded realistically? Are the values for key
performance indicators consistent, or is the integrity of the underlying data
suspect?
In some cases, you may need to add other measures that substitute average
figures, or industry standards, for unavailable or non-continuous values.
v Is the data at some of the lower drill-down levels too sparse to be useful?
Should the model be redesigned, or should drill-through targets be added?
Consider expressing some values as ranges rather than absolutes, to create
useful groupings such as responsiveness or rates of return, for example, or to
hide sensitive details, such as salaries.
v Could the data flowing from different databases, models, and reports be better
coordinated, perhaps by using normalized measures, to ensure that computer
resources are not overburdened?
v Has anyone developed a calculated column or exception dimension that could
be added to the standard reports for the benefit of all?
If you maintain regular contact with your report users, you can incorporate their
feedback into your model enhancements. If you change your model and cubes, use
the label and description fields for each dimension, level, and measure, so that
reports created from your model are clear and intuitive.
Upgrade an IBM Cognos Series 7 Model
To upgrade models created in earlier versions of Cognos Transformer, you must
save them in the Model Definition Language (MDL) format before you can import
them into Cognos Transformer version 8.x and later. This ensures that equivalent
definitions are created for all model objects. You can upgrade models from IBM
Cognos Transformer, versions 7.x.
You can open an IBM Cognos Series 7 model with secured cubes in Cognos
Transformer, and convert the IBM Cognos Series 7 user class views to IBM Cognos
custom views. You can then choose the authentication provider you want to use
with the custom views. For more information, see Chapter 7, “Adding Security,”
on page 147.
During the transition from a Series 7 namespace to an alternate security provider,
you can use the PowerCube property All applicable namespaces to associate all
applicable namespaces during migration testing. When you associate all the
applicable namespaces to the cube, you can ensure that the group, role, or user
dimensional filtering is consistent with that which had been applied for the IBM
Cognos Series 7 user class. This option is supported only for migration testing, and
cannot be used to deploy cubes in production environments.
You can change the data source association for IBM Cognos Series 7 .iqd files to an
IBM Cognos package or report to take advantage of the enhancements available
when using IBM Cognos data sources. You change the association after the
updated model is saved in Cognos Transformer 8.x. For more information, see
“Change a Data Source Type” on page 33.
Chapter 2. Planning Your Model 11
When importing .mdl files from earlier versions, some features may not convert
correctly, such as legacy data that contains special characters, spaces, and quotation
marks. For more information, see the migration documentation delivered with
your version of the product.
Before you begin
Tip: If you plan to upgrade, ensure you save all your models as .mdl files before
you attempt to upgrade them.
Procedure
1. Open the model in the earlier version of Cognos Transformer and, from the
File menu, click Save As.
2. In the Save as Type box, click Exported Model Files (*.mdl).
Tip: By default, Cognos Transformer saves models in the My
Documents/Transformer/Models directory. You can set the default location to
which Cognos Transformer saves models by changing the Models directory
setting on the Directories tab of the Preferences property sheet.
3. Open your new .mdl file in the current version of Cognos Transformer, make
any required changes to the model design, and save it, again selecting the .mdl
format.
Tip: If your IBM Cognos Series 7 model includes security, you will receive a
message when you open the model in Cognos Transformer version 8.x and
later indicating that you must choose how to manage the security during the
upgrade process. For more information, see “Upgrade an IBM Cognos Series 7
Secured PowerCube” on page 158.
When you are ready to use the model in your production environment, you
may want to save it as a .py?-format file.
Models that are created using Cognos Transformer version 8.x and later (.mdl
and .pyj files) are not backward compatible with Cognos Transformer versions
7.x. As a result, we strongly recommend that you maintain the .mdl file for the
Cognos Transformer 7.x model for a period of time following an upgrade.
12 IBM Cognos Transformer Version 10.1.1: User Guide
Chapter 3. Data Sources for Your Model
Models contain definitions of dimensions, levels, and measures. They also contain
features such as calculated measures, dimension views, or custom views that you
add to the basic PowerCube definition to meet your particular business intelligence
needs.
By querying the data in the specified sources, you create the multidimensional
PowerCubes or cube groups required by users of the IBM Cognos Business
Intelligence components, such as Analysis Studio.
Data sources can be one of the following:
v Structural (dimensional)
Contain the columns that define the model structure, such as the categories in
each dimension. Structural sources usually contain many columns and few rows.
v Transactional (fact)
Contain the columns for the measures to be tracked. They usually contain many
rows and few columns, typically one for each dimension and one for each
measure.
v Mixed
Contain the columns that define the model structure and the columns that
contain the measures to be tracked, using the same data source.
Techniques for Designing Data Sources
When setting up the data sources for your model, you should take into
consideration three principles.
These three principles are as follows:
v Where possible, design your data so that the structural information for each
dimension is provided by one source.
v Ensure that each data source contains enough information to generate the
categories for a dimension without database joins. If you must use database
joins, join queries from separate database tables using tools such as Framework
Manager, before you import the data.
v In addition to database security, be aware that different releases of Cognos
Transformer offer different options for protecting your cubes and controlling
access to information. For example, Cognos Transformer version 7.x supports
user class views whereas later versions of Cognos Transformer replace this
feature with custom views that can be associated with IBM Cognos security
objects (users, groups, and roles).
Data Source Types
Cognos Transformer supports IBM Cognos Series 7 data sources as well as
packages and reports that contain IBM Cognos query items.
This section lists the supported data sources, summarizes the information you
must specify for each data source, and identifies associated limitations.
© Copyright IBM Corp. 2007, 2011 13
Tip: You can also click the Help button, where available, for context-sensitive
information about the parameters that you must specify.
Note: 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 Cognos 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.
IBM Cognos Package or Report
You can import query items, and the associated filters and prompts, from IBM
Cognos packages and reports.
You do this by choosing the Package or Report data source type and browsing and
selecting from the available metadata.
Note: Transformer does not support IBM Cognos Finance reports or packages as a
data source. However, you can create PowerCubes directly in Cognos Finance.
After import, you can combine the IBM Cognos data with the data from other
sources as required. Individual query items can be used as source columns in the
Cognos Transformer model, and can be updated using the Modify Columns
feature. In relational packages and reports, measures appear as defined in
Framework Manager.
For more information about using packages and reports as a data source in Cognos
Transformer, see “Guidelines for Using IBM Cognos Packages and Reports as Data
Sources” on page 15 and “Creating a Model in Cognos Transformer” on page 347.
For information about modeling IBM Cognos relational and dimensionally
modeled relational (DMR) data sources, see the Framework Manager User Guide.
Dimensionally Modeled Relational Packages
When you access metadata from a dimensionally modeled relational (DMR)
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 DMR packages can be directly accessed using:
v Insert Data Source option on the Edit menu
Using this option, you can select measures and query items. The dimensional
structure is not imported.
v Insert Dimension from Package option on the Dimension Map
Using this option, you can select dimensions, hierarchies or levels. The selected
dimensions are created in Cognos Transformer, together with queries containing
appropriate query items.
If you want to take advantage of Cognos 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.
14 IBM Cognos Transformer Version 10.1.1: User Guide
OLAP Packages
Cognos Transformer allows you to leverage metadata from published OLAP
packages. As a result, Cognos 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
amount of data that is not 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 Cognos Transformer models
v 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 “Working with SAP BW Data Using a Package in Framework Manager” on
page 339.
v Create the time dimension in the same way that you create fact queries.
Cognos Transformer does not support importing time dimensions from any
OLAP source, including PowerCubes. To create the Cognos Transformer time
dimension with relative time categories, import your time information from
either an IBM Cognos relational package or report, or from a flat file exported
from IBM Cognos BI or the original OLAP vendor.
In Cognos Transformer, you add dimensions from OLAP packages directly from
the Dimension Map. This is a practical way to begin creating conformed
dimensions and, to some extent, 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 Cognos Transformer Dimension Map.
SAP BW Packages
You can use Cognos 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 Cognos 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 BI:
v “Creating a BW Query in SAP Business Explorer Query Designer” on page 340
v “Creating a Package in Framework Manager” on page 343
v “Creating a Model in Cognos Transformer” on page 347
Guidelines for Using IBM Cognos Packages and Reports as Data
Sources
This section contains guidelines, best practices, and tips to help you create Cognos
Transformer models using the IBM Cognos packages and reports as data sources.
Chapter 3. Data Sources for Your Model 15
For more information on creating Cognos Transformer models, see “Creating a
Model” on page 24.
Use List Reports
Data source queries using reports perform most efficiently when the report is a list.
Graphs, dashboards, crosstabs, and complex reports cannot be used as data source
queries.
Importing Dimensional Packages
Use the Insert Dimension from Package command when importing dimensional
packages if you want the dimensional structure maintained. This option preserves
the dimension levels and uses the smallest set of query items.
Importing Time Dimensions from Packages
When you create a query in Cognos Transformer based on a Framework Manager
package that contains hierarchical time-related categories, Cognos Transformer
interprets the time-related categories as a regular dimension and not as a time
dimension. As a result, the time dimension in your PowerCube will not contain
any relative time categories.
To avoid this problem, ensure that you import all of the data needed to define
your time dimension, and use Cognos Transformer to create the date levels and
categories.
Prompts in Report Data Sources
You can use a report with prompts as a data source in Cognos Transformer. You
must provide values for any mandatory prompts “Edit Existing Prompts in IBM
Cognos Reports and Packages” on page 29 when adding a query based on the
report data source to the model. Cognos Transformer asks you for these values
only the first time you add a query from a report data source. Any values you
provide are cached.
If you want to add a second query using the same report as a data source to your
Cognos Transformer model, you will not be prompted to provide values for
mandatory prompts. The values in the cache will be used. Although you can
refresh the source when adding the second query to force Cognos Transformer to
reprompt you for values, data will still be retrieved based on the first query.
To create two queries in your Cognos Transformer model that are based on the
same report data source, where you want to provide different values for
mandatory prompts, you must duplicate the report data source. Use one report
data source to add the first query to the model and use the duplicate report data
source to add the second query to the model.
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.
For more information about prompt support, see “Edit Existing Prompts in IBM
Cognos Reports and Packages” on page 29, and “Using IBM Cognos Reports to
Create a Data Source” on page 193.
16 IBM Cognos Transformer Version 10.1.1: User Guide
Loading...