IBM Cognos User manual

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

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

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

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.

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.

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

Page is loading ...

IBM Cognos User manual

IBM Cognos User manual

Related papers

Other documents