WIXLIB

IBM Cognos Framework ManagerVersion 10.1.0User Guide

Product InformationThis document applies to IBM Cognos Version 10.1.0 and may also apply to subsequent releases. To check for newer versions of this document,visit the IBM Cognos Information Centers r0m0/index.jsp).CopyrightLicensed Materials - Property of IBM Copyright IBM Corp. 2005, 2010.US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.IBM, the IBM logo, ibm.com, Impromptu, ReportNet, TM1, and Cognos are trademarks or registered trademarks of International BusinessMachines Corp., in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A currentlist of IBM trademarks is available on the Web at www.ibm.com/legal/copytrade.shtml.Microsoft, Windows, Windows NT, and the Windows logo are trademarks of Microsoft Corporation in the United States, other countries, or both.UNIX is a registered trademark of The Open Group in the United States and other countries.Java and all Java-based trademarks and logos are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.

Table of ContentsIntroduction11Chapter 1: What’s New?13New Features in 10.1.0 13Changed Features in 10.1.0 15Deprecated Features in 10.1.0 15Removed Features in 10.1.0 15New Features in 8.4 15Changed Features in 8.4 16Deprecated Features in Version 8.4 17Conformed Dimensions on SAP BW Data Sources 17New Features in Version 8.3 17Changed Features in Version 8.3 18Deprecated Features in Version 8.3 18Framework Manager Security Filters for SAP BW: Notice of Intent to Change the DefaultSetting 19IQD Externalize Method 19Removed Features in Version 8.3 19Chapter 2: Getting Started with Framework Manager21Analyze the Problem 21Building IBM Cognos Business Intelligence Applications 22Objects You Will Use 23Create a Project 26Open a Project 27The Project Page 28The Project Viewer 28Change Options for Projects 30Reorder Objects 31The Explorer Tab 32The Diagram Tab 32The Dimension Map Tab 34The Properties Pane 34The Tools Pane 36Naming Conventions for Objects in a Project 38Sample Models 39The Great Outdoors Warehouse Model 39The Great Outdoors Sales Model 40Chapter 3: Model Design Accelerator43Explorer Tree 43Explorer Diagram 44Model Accelerator Workspace 45Query Subject Diagram 45Relationship Editing Mode 45Licensed Materials – Property of IBM Copyright IBM Corp. 2005, 2010.3

Table of ContentsModel Warning View 46Change the Settings for Diagrams 47Create a Project 47Create a Star Schema 48Managing Your Star Schema 50Chapter 4: Importing Metadata from Data Sources51Data Sources 51Data Source Security 51Types of Data Source Connections 52Native Metadata 52Working With Data Source Connections 53Create a Data Source Connection 56Importing Metadata 58Import Metadata from a Relational Database 59Import Metadata from an IBM Cognos Model 61Import Metadata from an Architect Model or an Impromptu Catalog 61Import Metadata from IBM Cognos DecisionStream or IBM Cognos Data Manager 62Import from IBM Metadata Sources 66Import Metadata From Third Party Metadata Sources 69Troubleshooting Metadata from Other Sources 74Import Metadata Using XML as a Data Source 75Import Objects with the Same Name 76Chapter 5: Modeling Relational Metadata77Relationships 78Cardinality 78Modify a Relationship 82Create a Complex Expression for a Relationship 82Create a Relationship 83Create a Relationship Shortcut 83Detect and Generate Relationships 84Query Subjects 85Data Source Query Subjects 85Model Query Subjects 87Stored Procedure Query Subjects 88Determinants 92Create a Model Query Subject Based on Existing Objects 96View Related Objects 97Create a Query Set 97Test a Query Subject or Query Set 101Validate a Query Subject 104Update Query Subjects 104Convert a Query Subject into a Dimension 105Convert a Model Query Subject into a Data Source Query Subject 106Edit the SQL 106Change the Type of SQL 107Change How the SQL Is Generated 112Dimensions 114Normalized Data Sources 1144 IBM Cognos Framework Manager

Table of ContentsCreate a Regular Dimension 115Sort Members of a Level 121Roles 122Create a Measure Dimension 124Convert a Measure into a Query Item 125Scope Relationships 126Create a Regular Dimension Based on Existing Objects 127View Related Objects 127Test a Dimension 128Convert a Regular Dimension into a Query Subject 130Multilingual Metadata 131Setting Up a Multilingual Reporting Environment 131Using a Macro to Model Multilingual Data 134Add a Language to a Project 134Export a Translation Table 135Import a Translation Table 136Example - Create a Multilingual Project for Relational Metadata 136Query Items 138Modifying How Query Items Are Aggregated 141Format Query Items 148Define a Prompt Control 149Convert a Query Item into a Measure 154Adding Business Rules 155Create a Calculation 155Create a Filter 158Apply a Filter 160Example - Show the Currency Name for Each Country 162Create a Parameter Map 163Example - Specifying a Language Value for Relational Metadata 165Create a Session Parameter 165Using Parameters with Relational Data Source Query Subjects 167Creating Prompts with Query Macros 168Organizing the Model 179Create a Star Schema Group 180Use Shortcuts 184Create a Folder or Namespace 187Create a Query Item Folder 188Create a Measure Folder 188Create a Durable Model 189Analyze a Model 191Chapter 6: Working with SAP BW Metadata197Import from an SAP BW Data Source 197Mapping SAP BW Objects to Framework Manager 203Dimensions 204Modify a Regular Dimension 205Roles 210Modify a Key Figures Dimension 212View Related Objects 213User Guide 5

Table of ContentsTest a Dimension or Other Object 213Working with Model Query Subjects 216Query Items 219Modifying How Query Items Are Aggregated 222Format Query Items 225Define a Prompt Control 226SAP BW Variables 230Numeric Variable Property Values 234Characteristic Variable Property Values 235Picklist Prompts 237Adding Business Rules 237Create a Calculation 237Create a Filter 239Apply a Filter 242Create a Parameter Map 243Create a Session Parameter 245Organizing the Model 246Use Shortcuts 247Create a Folder or Namespace 248Chapter 7: Publishing Packages251Verify a Model or Package 251Create or Modify a Package 254Security 256Users, Groups, and Roles 256Add Data Security 257Add or Remove Object Security 259Modify Package Security 261Specify Languages 262Set Suppression Options 263Externalizing Query Subjects and Dimensions 263Publish a Package 266Publish a Package Based on an OLAP Data Source 268Publishing a Package by Running a Script 269Update a Report to Use the Latest Version of a Package 270Chapter 8: Managing the Project271Understanding the Metadata in Your Model 271Explore a Package 271View the Distribution of an Object in Packages 272Create Model Documentation 272Multiuser Modeling 273Branching and Merging Projects 273Segmenting and Linking Projects 280Using External Repository Control 284Administering the Metadata 284Copy, Move, Rename, or Delete a Project 285Analyze the Impact of Changes to a Package 287Remap an Object to a New Source 290Export Metadata 2916 IBM Cognos Framework Manager

Table of ContentsProject Reuse 292Model Portability 294Synchronize Projects 301Query Behavior 304Set Governors 304Specify Where Aggregate Rollups are Processed 311Improve Performance by Setting Query Processing Type 312Improving Performance by Reusing Cached Data When Running a Report 313Select Function Sets 314Quality of Service 315Control and Optimize How Queries Are Run 317Chapter 9: Guidelines for Modeling Metadata319Understanding IBM Cognos Modeling Concepts 319Relational Modeling Concepts 320Model Design Considerations 329Dimensional Modeling Concepts 336Building the Relational Model 338Defining the Relational Modeling Foundation 338Defining the Dimensional Representation of the Model 345Organizing the Model 348Chapter 10: The SQL Generated by IBM Cognos Software351Understanding Dimensional Queries 351Single Fact Query 351Multiple-fact, Multiple-grain Query on Conformed Dimensions 352Modeling 1-n Relationships as 1-1 Relationships 355Multiple-fact, Multiple-grain Query on Non-Conformed Dimensions 357Resolving Ambiguously Identified Dimensions and Facts 360Query Subjects That Represent a Level of Hierarchy 360Resolving Queries That Should Not Have Been Split 361Chapter 11: Upgrading Models365Verifying the model before upgrading 365Opening and upgrading the model 365Upgrade and governors 366Upgrade and data types 366Upgrade and query subjects that are based on SAP BW metadata 367Verifying and repairing the upgraded IBM Cognos ReportNet model 367Converting dimension information to either determinants or dimensions 368Selecting and repairing objects in the upgraded IBM Cognos ReportNet model 371Upgrading segmented and linked projects 372Appendix A: Troubleshooting375Unable to Compare Two CLOBs in Oracle 375An Out of Memory Error with ERWin Imported Metadata 375Framework Manager Cannot Access the Gateway URI 375Object Names Appear in the Wrong Language 376Full Outer Joins in Oracle Return Incorrect Results 376Error When Testing Query Subjects in a Model Imported from Teradata 376User Guide 7

Table of ContentsError for Type-In SQL Query Subject 377QE-DEF-0259 Error 377Externalized Key Figures Dimension Retains Old Prompt Value 378Older Models Display Level Object Security 378Exporting a Framework Manager Model to a CWM File Fails With Error MILOG.TXT wasnot found 378Difference in SQL for Inner Joins After Upgrading to IBM Cognos BI, Version 8.3 and Later 378Full Outer Joins Not Sent to Oracle 9i and 10GR1 379Unexplained Discrepancies in Number Calculations 379381Appendix B: Using the Expression EditorSearching for Values May Return Unexpected Results 382Calculation Components 382Operators 383Summaries 391Member Summaries 402Constants 405Constructs 407Business Date/Time Functions 408Block Functions 413Macro Functions 414Common Functions 424Dimensional Functions 431DB2 453Informix 469MS Access 475Netezza 483Oracle 490Red Brick 499SQL Server 504Teradata 511SAP BW 517Sybase 519Postgres 527Vertica 533Paraccel 538MySQL 541Greenplum 545Report Functions 551Appendix C: Accessibility features571Accessibility features in Framework Manager 571Keyboard Shortcuts for Framework Manager 571Keyboard shortcuts for Model Design Accelerator 572IBM and accessibility 573Appendix D: Data Formatting Reference575Data Formatting Properties 575"Not Applicable" Characters 575Any Error Characters 5758 IBM Cognos Framework Manager

Table of ContentsCalendar Type 575Clock 575Currency 575Currency Display 576Currency Symbol 576Currency Symbol Position 576Date Ordering 576Date Separator 576Date Style 576Decimal Separator 576Display AM / PM Symbols 576Display As Exponent 576Display Days 577Display Eras 577Display Hours 577Display Milliseconds 577Display Minutes 577Display Months 577Display Months 577Display Seconds 577Display Time Zone 577Display Weekdays 577Display Years 578Display Years 578Divide By Zero Characters 578Exponent Symbol 578Group Size (digits) 578International Currency Symbol 578Mantissa (digits) 578Maximum No. of Digits 578Minimum No. of Digits 579Missing Value Characters 579Negative Pattern 579Negative Sign Position 579Negative Sign Symbol 579No. of Decimal Places 579Numeric Overflow Characters 579Padding Character 579Pattern 579Percentage Symbol 580Percent Scale (integer) 580Scale 580Secondary Group Size (digits) 580Security Error Characters 580Thousands Separator 580Time Separator 580Time Style 580Time Unit 581Use Thousands Separator 581User Guide 9