WIXLIB

IBM Cognos 8FRAMEWORK MANAGERUSER GUIDE

Product InformationThis document applies to IBM Cognos 8 Version 8.4 and may also apply to subsequent releases. To check for newer versions of this document, visit the IBM Cognos Resource Center (http://www.ibm.com/software/data/support/cognos crc.html).CopyrightCopyright 2008 Cognos ULC (formerly Cognos Incorporated). Cognos ULC is an IBM Company.Portions of Cognos ULC software products are protected by one or more of the following U.S. Patents: 6,609,123 B1; 6,611,838 B1; 6,662,188B1; 6,728,697 B2; 6,741,982 B2; 6,763,520 B1; 6,768,995 B2; 6,782,378 B2; 6,847,973 B2; 6,907,428 B2; 6,853,375 B2; 6,986,135 B2;6,995,768 B2; 7,062,479 B2; 7,072,822 B2; 7,111,007 B2; 7,130,822 B1; 7,155,398 B2; 7,171,425 B2; 7,185,016 B1; 7,213,199 B2; 7,243,106B2; 7,257,612 B2; 7,275,211 B2; 7,281,047 B2; 7,293,008 B2; 7,296,040 B2; 7,318,058 B2; 7,325,003 B2; 7,333,995 B2.Cognos and the Cognos logo are trademarks of Cognos ULC (formerly Cognos Incorporated) in the United States and/or other countries. IBMand the IBM logo are trademarks of International Business Machines Corporation in the United States, or other countries or both. Java andall Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both. Other company, product,or service names may be trademarks or service marks of others.While every attempt has been made to ensure that the information in this document is accurate and complete, some typographical errors ortechnical inaccuracies may exist. Cognos does not accept responsibility for any kind of loss resulting from the use of information containedin this document.This document shows the publication date. The information contained in this document is subject to change without notice. Any improvementsor changes to the information contained in this document will be documented in subsequent editions.U.S. Government Restricted Rights. The software and accompanying materials are provided with Restricted Rights. Use, duplication, or disclosure by the Government is subject to the restrictions in subparagraph (C)(1)(ii) of the Rights in Technical Data and Computer clause atDFARS 252.227-7013, or subparagraphs (C)(1) and (2) of the Commercial Computer Software - Restricted Rights at 48CFR52.227 as applicable.The Contractor is Cognos Corporation, 15 Wayside Road, Burlington, MA 01803.This document contains proprietary information of Cognos. All rights are reserved. No part of this document may be copied, photocopied,reproduced, stored in a retrieval system, transmitted in any form or by any means, or translated into another language without the priorwritten consent of Cognos.

Table of ContentsIntroduction11Chapter 1: What’s New?13New Features in 8.4 13Changed Features in 8.4 14Deprecated Features in Version 8.4 14Conformed Dimensions on SAP BW Data Sources 15New Features in Version 8.3 15Changed Features in Version 8.3 15Deprecated Features in Version 8.3 16Framework Manager Security Filters for SAP BW: Notice of Intent to Change the DefaultSetting 16IQD Externalize Method 17Removed Features in Version 8.3 1719Chapter 2: Getting Started with Framework ManagerAnalyze the Problem 19Building IBM Cognos 8 Applications 20Objects You Will Use 21Create a Project 24Open a Project 25The Project Page 25The Project Viewer 25Change Options for Projects 28Reorder Objects 29The Explorer Tab 30The Diagram Tab 30The Dimension Map Tab 32The Properties Pane 32The Tools Pane 33Naming Conventions for Objects in a Project 35Sample Models 37The Great Outdoors Warehouse Model 37The Great Outdoors Sales Model 38Chapter 3: Importing Metadata from Data Sources41Data Sources 41Data Source Security 41Types of Data Source Connections 42Native Metadata 42Working With Data Source Connections 43Create a Data Source Connection 46Importing Metadata 48Import Metadata from a Relational Database 49Import Metadata from an IBM Cognos 8 Model 51User Guide 3

Table of ContentsImport Metadata from an Architect Model or an Impromptu Catalog 51Import Metadata from IBM Cognos DecisionStream or IBM Cognos 8 Data Manager 52Import from IBM Metadata Sources 56Import Metadata From Third Party Metadata Sources 59Troubleshooting Metadata from Other Sources 64Import Metadata Using XML as a Data Source 65Import Objects with the Same Name 66Chapter 4: Modeling Relational Metadata67Relationships 67Cardinality 68Modify a Relationship 72Create a Complex Expression for a Relationship 72Create a Relationship 72Create a Relationship Shortcut 73Detect and Generate Relationships 74Query Subjects 75Data Source Query Subjects 75Model Query Subjects 77Stored Procedure Query Subjects 78Determinants 82Create a Model Query Subject Based on Existing Objects 86View Related Objects 87Create a Query Set 87Test a Query Subject or Query Set 91Validate a Query Subject 93Update Query Subjects 94Convert a Query Subject into a Dimension 95Convert a Model Query Subject into a Data Source Query Subject 96Edit the SQL 96Change the Type of SQL 97Change How the SQL Is Generated 102Dimensions 103Normalized Data Sources 104Create a Regular Dimension 105Sort Members of a Level 111Roles 112Create a Measure Dimension 113Convert a Measure into a Query Item 115Scope Relationships 115Create a Regular Dimension Based on Existing Objects 116View Related Objects 116Test a Dimension 117Convert a Regular Dimension into a Query Subject 120Multilingual Metadata 120Setting Up a Multilingual Reporting Environment 121Using a Macro to Model Multilingual Data 123Add a Language to a Project 124Export a Translation Table 1254 Framework Manager

Table of ContentsImport a Translation Table 125Example - Create a Multilingual Project for Relational Metadata 126Query Items 128Modifying How Query Items Are Aggregated 131Format Query Items 138Define a Prompt Control 139Convert a Query Item into a Measure 143Adding Business Rules 143Create a Calculation 144Create a Filter 146Apply a Filter 148Example - Show the Currency Name for Each Country 150Create a Parameter Map 151Example - Specifying a Language Value for Relational Metadata 153Create a Session Parameter 153Using Parameters with Relational Data Source Query Subjects 155Creating Prompts with Query Macros 155Organizing the Model 167Create a Star Schema Group 167Use Shortcuts 172Create a Folder or Namespace 175Create a Query Item Folder 176Create a Measure Folder 176Analyze a Model 177Chapter 5: Working with SAP BW Metadata183Import from an SAP BW Data Source 183Mapping SAP BW Objects to Framework Manager 189Dimensions 190Modify a Regular Dimension 191Roles 194Modify a Key Figures Dimension 196Conform SAP BW Dimensions 197View Related Objects 199Test a Dimension or Other Object 200Working with Model Query Subjects 202Query Items 205Modifying How Query Items Are Aggregated 208Format Query Items 211Define a Prompt Control 211Adding Business Rules 223Create a Calculation 223Create a Filter 225Apply a Filter 227Create a Parameter Map 229Create a Session Parameter 230Organizing the Model 232Use Shortcuts 232Create a Folder or Namespace 234User Guide 5

Table of ContentsOptimizing SAP BW Performance 235Dimension Settings 235Data Source Settings 237Chapter 6: Publishing Packages239Verify a Model or Package 239Create or Modify a Package 242Security 244Users, Groups, and Roles 244Add Data Security 245Add or Remove Object Security 247Modify Package Security 249Specify Languages 250Set Suppression Options 251Externalizing Query Subjects and Dimensions 251Publish a Package 254Publish a Package Based on an OLAP Data Source 256Publishing a Package by Running a Script 257Update a Report to Use the Latest Version of a Package 257Chapter 7: Managing the Project259Understanding the Metadata in Your Model 259Explore a Package 259View the Distribution of an Object in Packages 260Create Model Documentation 260Multiuser Modeling 261Branching and Merging Projects 261Segmenting and Linking Projects 268Repository Control 272Using External Repository Control 282Administering the Metadata 283Copy, Move, Rename, or Delete a Project 283Analyze the Impact of Changes to a Package 285Remap an Object to a New Source 288Export Metadata 289Project Reuse 290Model Portability 292Synchronize Projects 299Query Behavior 302Set Governors 303Specify Where Aggregate Rollups are Processed 309Improve Performance by Setting Query Processing Type 309Improving Performance by Reusing Cached Data When Running a Report 310Select Function Sets 311Quality of Service 312Control and Optimize How Queries Are Run 314Chapter 8: Guidelines for Modeling Metadata317Understanding IBM Cognos 8 Modeling Concepts 317Relational Modeling Concepts 3186 Framework Manager

Table of ContentsModel Design Considerations 327Dimensional Modeling Concepts 334Building the Relational Model 335Defining the Relational Modeling Foundation 336Defining the Dimensional Representation of the Model 342Organizing the Model 345Chapter 9: The SQL Generated by IBM Cognos 8349Understanding Dimensional Queries 349Single Fact Query 349Multiple-fact, Multiple-grain Query on Conformed Dimensions 350Modeling 1-n Relationships as 1-1 Relationships 353Multiple-fact, Multiple-grain Query on Non-Conformed Dimensions 355Resolving Ambiguously Identified Dimensions and Facts 358Query Subjects That Represent a Level of Hierarchy 358Resolving Queries That Should Not Have Been Split 359Chapter 10: Upgrading Models363IBM Cognos ReportNet Model Upgrade 363Verifying the Model in IBM Cognos ReportNet 363Opening and Upgrading the Model 364Verifying and Repairing the Upgraded IBM Cognos ReportNet Model 366IBM Cognos 8.1 or 8.2 Model Upgrade 370Verifying the Model in IBM Cognos 8.1 or 8.2 370Opening and Upgrading the IBM Cognos 8.1 or 8.2 Model 371Verifying and Repairing the Upgraded IBM Cognos 8.1 or 8.2 Model 371Upgrading Segmented and Linked Projects 371Appendix A: Troubleshooting373Measures Not Added After Initial Import in an Upgraded Model 373Unable to View the Result Set of a Stored Procedure 373Unable to Compare Two CLOBs in Oracle 373An Out of Memory Error with ERWin Imported Metadata 373Calculation Fails Testing 374Framework Manager Cannot Access the Gateway URI 374Object Names Appear in the Wrong Language 375Full Outer Joins in Oracle Return Incorrect Results 375Error When Testing Query Subjects in a Model Imported from Teradata 375Error for Type-In SQL Query Subject 375Function Name Not Recognized 376QE-DEF-0259 Error 376QE-DEF-0260 Parsing Error 377Externalized Key Figures Dimension Retains Old Prompt Value 377Older IBM Cognos 8 Models Display Level Object Security 377Exporting a Framework Manager Model to a CWM File Fails With Error MILOG.TXT wasnot found 377Difference in SQL for Inner Joins After Upgrading to IBM Cognos 8.3 378Publishing a Large Package Results in a CCLOutOfMemory Error 378Full Outer Joins Not Sent to Oracle 9i and 10GR1 378Review Governors When Upgrading Models 378User Guide 7

Table of ContentsFile Creation from Framework Manager fails with Error QE-DEF-0178 379381Appendix B: Using the Expression EditorSearching for Values May Return Unexpected Results 382Expression Components 382Operators 383Summaries 390Member Summaries 401Constants 405Constructs 406Business Date/Time Functions 407Block Functions 412Macro Functions 412Common Functions 422DB2 439Informix 453MS Access 458Oracle 465Red Brick 473SQL Server 478Teradata 485SAP BW 491Sybase 492Report Functions 500Appendix C: Data Formatting Reference519Data Formatting Properties 519"Not Applicable" Characters 519Any Error Characters 519Calendar Type 519Clock 519Currency 519Currency Display 520Currency Symbol 520Currency Symbol Position 520Date Ordering 520Date Separator 520Date Style 520Decimal Separator 520Display AM / PM Symbols 520Display As Exponent 520Display Days 521Display Eras 521Display Hours 521Display Milliseconds 521Display Minutes 521Display Months 521Display Months 521Display Seconds 521Display Time Zone 5218 Framework Manager