IBM Cognos User Manual Download Page 49

Page: 49 / 411

Dimension Map

. If you add a query to an OLAP package data source using the

Add Query From Package or Report

option, you will need to manually construct

the dimension.

You can use the

Add Query From Package or Report

option to add an additional

query to the data source.

Procedure

Right-click the empty space in the

Data Sources

list for your model and click

Insert Data Source

Tip:

If the

Insert Data Source

option is unavailable, you may have right-clicked

a package or report data source.

In the

Data source name

box, enter the name of the data source and, in the

Data source type

box, select

Package

Report

Tip:

If you want the data source name to default to the name of the package

that you select in the next step, leave the

Data source name

box blank.

Click

Browse

to open the metadata browser and select a package or report

from the available list.

Tip:

You can also click the drop-down arrow to select a recently used package

or report from the list.

Click

Next

In the

Query Name

box, type a name for the new query.

In the

New Data Source

wizard, you create one query at a time.

In the

Source

list, select the query items to import and click

Add

to add the

selected query items to the Cognos Transformer query.

If you want Cognos Transformer to create preliminary dimensions in the

Dimension Map

, select the

AutoDesign

check box.

Click

Finish

You will be prompted to add another query. If you want to add another query,
click

Yes

and repeat steps 6 and 7. If you click No, a new data source

containing the specified metadata appears. The source columns appear in the

Data Sources

list and, if you selected

Run AutoDesign

, a preliminary

dimensional structure appears in the

Dimension Map

Tip:

Use the

Data Source Viewer

to view sample data and, for supported data

sources, the Cognos SQL or the native database SQL.

Add Additional Queries to Existing IBM Cognos Data Sources

IBM Cognos packages and reports are shown in the Data Sources list as a single
data source with multiple associated queries. When you use the New Model
wizard or New Data Source wizard, you can add only one query at a time.

To add a second query to your package or report, use the

Add Query From

Package or Report

option.

Add Query From Package or Report

opens the

Query

Definition

dialog box, where you can create the new query. When you create the

new query, it is imported as an additional query under the package or report data
source.

Tip:

If you imported your dimensions to the

Dimension Map

from a package

using the

Insert Dimension from Package

option, you can add another dimension

from that package to the

Dimension Map

using the same option. This adds the

additional query to your package data source and completes the

AutoDesign

Chapter 3. Data Sources for Your Model

Summary of Contents for Cognos

Page 1: ...IBM Cognos Transformer Version 10 1 1 User Guide ...

Page 2: ...on 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 ...

Page 3: ...lumn 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...

Page 4: ...om 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 O...

Page 5: ...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 Allocatio...

Page 6: ...sion 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 O...

Page 7: ...teway 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 2...

Page 8: ...timized 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 TR0...

Page 9: ...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 TR081...

Page 10: ...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 ...

Page 11: ... 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 Setting...

Page 12: ...mework 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 Lo...

Page 13: ...rs 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 ...

Page 14: ...ta 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 appli...

Page 15: ... 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 Co...

Page 16: ...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 ...

Page 17: ...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 dat...

Page 18: ...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 da...

Page 19: ... 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 pro...

Page 20: ...e 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 Framewo...

Page 21: ...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 tab...

Page 22: ...w data is based on values calculated from other columns functions constants and calculated columns 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 dimensio...

Page 23: ...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...

Page 24: ...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...

Page 25: ...on 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...

Page 26: ...s 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 ...

Page 27: ...ces 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 dat...

Page 28: ...ework 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 Dimensiona...

Page 29: ...l 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 Dimensi...

Page 30: ...ompts 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 ...

Page 31: ...e displayed but without the calculations Shortcuts to this type of folders are also displayed Handling Model Changes When the model associated with a package or report is subsequently altered for example query subjects are removed the Cognos Transformer model that uses the package or report as a data source is not automatically updated to reflect the changes You can use the Modify Columns feature ...

Page 32: ...iewer These characters are displayed as a dashed line Cognos Transformer does not support reports with multilingual data as a data source When the operating system locale is properly set Cognos Transformer displays the characters for that locale Business Viewpoint Studio IBM Cognos Business Viewpoint Studio provides you with one version of the truth for dimensions used in an enterprise s performan...

Page 33: ...e 39 For an up to date list of supported environments visit the IBM Cognos Customer Center http www ibm com software data cognos customercenter Delimited field Text with Column Titles Flat files are an excellent data source for achieving fast cube builds Flat files are also recommended when you want to import OLAP fact data With Delimited field text with column titles input values are obtained fro...

Page 34: ... file Cognos Transformer uses either the Microsoft ActiveX Data Objects ADO driver to access the data or runs the SQL queries stored in the mdb file to get the source columns from an ODBC enabled server database Note Password protected files are not supported In Cognos Transformer select Table or range Excel Crosstab With an Excel crosstab input values are obtained from an Excel crosstab file Cogn...

Page 35: ...ach column in the source file Accept the default Character set or specify an alternative Designing Successful IBM Cognos PowerCubes The most successful business intelligence applications are designed with well planned models This includes an analysis of how the data in the models will be used by report and analysis users Consider the following concepts when designing PowerCube models for use in IB...

Page 36: ...s data item references for categories or members in any report specification v as the value passed in PowerCube to PowerCube drill through in any report v as identifiers for categories or members used in filters expressions parameters or calculations in IBM Cognos BI v to return categories or members to IBM Cognos applications Any time a Cognos application requests the category or member the MUN e...

Page 37: ...s such as cities with the same name in two or more regions When you create models in Cognos Transformer multiple non unique categories imported into the same level are made unique by appending to the duplicate codes where represents an ascending numeric sequence The mappings between these assigned codes and their associated source values are stored in the Cognos Transformer model for use in subseq...

Page 38: ... Business Intelligence products Creating a Model To create the cubes that you need for OLAP reporting you begin by creating a model This involves v specifying the data sources for the model and any required security credentials v defining dimensions levels and measures based on the selected query objects in your IBM Cognos package or report or the tables rows or columns of your other data sources ...

Page 39: ... to which Cognos Transformer saves models by changing the Models directory setting on the Directories tab of the Preferences property sheet on the File menu On Windows Vista Cognos Transformer saves models in the Documents Transformer Models directory v When prompted to save in binary py or text mdl format use the latter when exporting models or to avoid possible fragmentation problems when you ha...

Page 40: ...Transformer to automatically create a preliminary dimensional structure for you on the last page of the New Model wizard select the Run AutoDesign check box 12 Click Finish 13 If you are prompted for data source connection and signon information v Select the connection and click OK v Choose whether to enter a valid user name and password for the current session or create a Cognos Transformer signo...

Page 41: ...ocedure 1 In Cognos Transformer click Create a new model 2 In the New Model Wizard click Cancel 3 With the Dimension Map pane selected from the Edit menu click Insert Dimension from Package 4 Click Browse to open the metadata browser 5 In the Browse Metadata dialog box select the package that contains your SAP BW query and click OK 6 In the Insert Dimension from Package dialog box click Finish 7 I...

Page 42: ...n Windows Vista Cognos Transformer saves models in the Documents Transformer Models directory v When prompted to save in binary py or text mdl format use the latter when exporting models or to avoid possible fragmentation problems when you have made a lot of changes since your last save action v In addition to saving your models in mdl format you should regularly use the Check Model command from t...

Page 43: ...os studio For example a report with a prompt page defined to contain a single value select drop down list may be presented in Cognos Transformer as a multi value select list This behavior occurs because Cognos Transformer does not process any information from report prompt pages Ensure that you understand the purpose of each prompt when using a report as a data source Many types of prompts exist s...

Page 44: ...xists in the Cognos Transformer query but cannot be found in the report or package used as a data source v An arrow that points to the right beside a prompt name identifies a new prompt This prompt exists in the report or package used as a data source but cannot be found in the Cognos Transformer model 3 Define the variable or value for the prompt Tips v For a prompt that requires a single value s...

Page 45: ... to save the command line file to your computer Remove Obsolete Prompts Cognos Transformer stores prompts and their values in the Cognos Transformer model and uses this information for data requests and to generate a prompt specification A prompt specification is supplied at the command line in batch mode to override the values stored in the model Prompts that are no longer in a package or report ...

Page 46: ... separately for each query the specified values should be the same in all queries If there is more than one fact query each may use a different prompt as the segmenting prompt The queries can use the same segmenting prompt if suitable Tip Segmentation is only supported for fact queries that have the Use stream extract option selected on the Data Source property sheet Source tab Procedure 1 In the ...

Page 47: ...pen the data source browser and select a data source from the available list If you click Cancel the entire change data source type action is canceled 4 Click Finish 5 In the Query Definition or Modify Columns dialog box match the columns in the original data source to the columns in the new data source v In the Source list select a column v In the Model list select the corresponding column v Clic...

Page 48: ...e model The import may include more metadata than expected This allows you to refine the columns that are used within the dimension Tip To ensure conformed dimensions across different packages the original OLAP source should use unique business keys This will help to ensure that Cognos Transformer generates category codes that are conformed to the OLAP source from which the dimension was created A...

Page 49: ...dimensions in the Dimension Map select the AutoDesign check box 8 Click Finish You will be prompted to add another query If you want to add another query click Yes and repeat steps 6 and 7 If you click No a new data source containing the specified metadata appears The source columns appear in the Data Sources list and if you selected Run AutoDesign a preliminary dimensional structure appears in th...

Page 50: ...In the Query name box type a name for the new query You create one query at a time 3 In the Source list select the query items to import and click Add to add the selected query items to the Query definition details box 4 Click Finish A new data source containing the specified metadata appears under the original data source The source columns appear in the Data Sources list Tip Use the Data Source ...

Page 51: ...columns in the model For example if you rename data source items Cognos Transformer may no longer be able to match those items with their corresponding columns in the model You can choose to resolve mismatches manually or let Cognos Transformer attempt to automatically resolve mismatches For IBM Cognos data sources Cognos Transformer presents options for matches for your review If Cognos Transform...

Page 52: ...he Candidates list of data source items click the one that you want to match to the column or click Leave unmatched 7 Repeat step 6 for each mismatched column that Cognos Transformer attempts to resolve 8 For any mismatched items that Cognos Transformer cannot suggest locations for Cognos Transformer presents possible name changes for your review From the Candidates list click the item that you wa...

Page 53: ... visit the IBM Cognos Customer Center http www ibm com software data cognos customercenter Isolation Levels for an IQD Data Source When you create a data source in Cognos Transformer using an IQD you must specify an isolation level typically the same one as was used when the iqd file was created This is necessary so that the data is processed properly in the model If you must change the Isolation ...

Page 54: ...l to the UNIX server Cognos Transformer can now refresh the data directly from the server holding the Oracle database whenever categories are generated or cubes are created Note The connection information for an IQD data source is stored in the cs7g ini file located in the installation_location CS7Gateways bin directory Modify the SQL Query in an IQD Data Source SQL is the standard query language ...

Page 55: ...format To use a crosstab as a source you must define one or more named ranges in the spreadsheet These ranges establish which data becomes columns in the model To use a database as a source you must define a named database range in the spreadsheet and also specify that range in Cognos Transformer Cognos Transformer reads the named ranges from the crosstab or database and the data class text numeri...

Page 56: ... Transformer reads all the expense types and expense amounts for each quarter and for each division Division Quarter Expense Expense Amount Glassware Q1 Expenses Salary 2 000 Glassware Q2 Expenses Salary 2 000 Glassware Q3 Expenses Salary 2 500 Glassware Q4 Expenses Salary 2 000 Glassware Q1 Production 1 500 Glassware Q3 Production 1 500 In your Cognos Transformer model under Glassware the Data So...

Page 57: ...file initially have these attributes set to zero To modify these attribute values use the Format tab on the Measure property sheet Cognos Transformer automatically interprets the following components of the portable subfile dictionary Integer and float data from portable subfiles are represented by their psd datatype as follows v Integers stored as Portable Zoned in the subfile appear as signed ze...

Page 58: ...gnos IBM Cognos Data Manager IBM Cognos Virtual View Manager or a data access application Data Source Scope Data sources may be associated with all or part of the Cognos Transformer Dimension Map To make the proper associations all column names related to the same level in the Dimension Map must match exactly Also the levels with which these source columns are associated must be either unique or f...

Page 59: ...columns for the ancestor levels and a missing level by definition is not unique The source does not contain enough data to map column values to a level without knowing the entire context The default color is red For example this source contains a column that provides category values for the level Product No but not for the ancestor levels Product Line and Product Type The context of the Product No...

Page 60: ...ad In models with multiple data sources you can control when a data source is read For example a data source can be read during category generation during cube creation or both You can use this capability to avoid unnecessary processing resulting in a more efficient model design Here are some typical scenarios 46 IBM Cognos Transformer Version 10 1 1 User Guide ...

Page 61: ...r transactional data sources and structural data sources that contain non static data v Generate categories only Cognos Transformer queries only for structural information when the Create Selected PowerCube and Update Selected PowerCube commands run If there are measures in any of the source files they are not retrieved v Create the PowerCubes Cognos Transformer queries the source and creates or u...

Page 62: ...sociates the values from both of these sources with a specific level in one of the dimensions in the model To create a join between two columns use a database query tool such as IBM Cognos or IBM Cognos Impromptu before you create the data source Troubleshooting Issues Related to Date Columns To obtain the data required to populate the time dimension for your model you need at least one data sourc...

Page 63: ... in the Data class box select the appropriate data type 5 In the Position box type the starting position of the column in a record The first byte in a record is byte number 1 6 In the Size box type the width of the column in bytes 7 Set other properties for the column as required and click OK 8 Repeat steps 2 to 7 for each column in the source file Example Defining Fixed field Columns You want to ...

Page 64: ...of zero Regardless of the precision supported by the source database the overflow limit for the 64 bit floating point storage type in Cognos Transformer is 18 significant digits excluding the decimal separator When a measure value has more significant digits after the decimal place than is specified in the precision attribute for the model the number is truncated and the last bit rounds up rather ...

Page 65: ...2 In the Degree of detail box select the date level appropriate to the measure in your data source For example if the source contains a measure that provides monthly forecast values click Month 3 Click OK Example Aligning the Date Dimension with Available Data Source Measures You want to map date dimension categories to the correct measures in a data source Suppose you have sales figures that are ...

Page 66: ...s the array correctly you must use the first month of your fiscal year as the date in your date column This specifies the year begin date for the year in which the array applies Procedure 1 Ensure that your model does not contain any objects in the Measures list 2 Double click the first column that you want in the time array such as Month_01 3 On the Column property sheet click the Array tab and i...

Page 67: ...ct and efficiently processed cubes and reports Example Consolidating Monthly Data Multiple source Model You want to consolidate data from more than one source using a monthly time array You have one data source for each year of data Each source file contains the total number of items sold by retailer type independent stores compared to department stores by month Procedure You define the Month01 co...

Page 68: ... CenturyBreak value to 80 so that the years 80 or higher are generated in the 20th century 1980 1999 and the years prior to 80 are generated in the 21st century 2000 2079 If your source files have overlapping date ranges that span both centuries such as 1900 to 1999 and 2000 to 2020 you must convert your source data to use a four digit YYYY date format rather than use a CenturyBreak setting Proced...

Page 69: ...or user ID and password When a signon has been configured without a password you can do one of two things to build a cube in batch mode v Ask the system administrator to create a Content Manager signon with a valid password v Create a data source signon in Cognos Transformer When you create a signon Cognos Transformer assigns the signon to the package or report on which all subsequent queries are ...

Page 70: ... Set as auto logon property For more information about IBM Cognos signons see the Administration and Security Guide Tip IBM Cognos signons must have an associated namespace to be valid Procedure 1 Open the Cognos Transformer model 2 Right click in the Signons list and click Insert Signon 3 In the Signon name box type the name for the signon 4 In the User ID box type the user ID 5 In the Password b...

Page 71: ... then automatically v creates a time dimension based on the date column If the dates are not in a predefined format Cognos Transformer may prompt you to define the format v adds columns with numerical values to the Measures list v places all remaining columns on the Dimension Map using a best fit approach You may need to change at least some of the dimensions and measures created by AutoDesign For...

Page 72: ...r Quarter and Month or a custom set Lunar Week Lunar Day and so on Procedure 1 In the Dimension Map click a dimension in the dimension line to ensure that no level is currently selected 2 From the Edit menu click Insert Dimension A new dimension is inserted to the right of the selected dimension Tip If the Dimension Map is active but no dimension is selected the new dimension will be inserted at t...

Page 73: ...hich the dimension was created Define a Calculated Column A calculated column is an expression that uses other columns functions and constants to derive new data for the model Use calculated columns in your dimension structure v to create exception dimensions or new ways of slicing and dicing your data based on a calculation from existing source columns v to create customized date values v to prod...

Page 74: ... insights into the data You define the margin ranges for Low Medium and High based on the Gross Margin formula Revenue Cost Revenue using the following if then else statement if Gross Margin 0 50 then Low else if Gross Margin 0 70 then High else Medium 2 Drag the MARGIN_RANGE column to the dimension line of the Dimension Map to form an exception dimension Results You have now segmented your data i...

Page 75: ... preceded by a special icon You can add source levels to a dimension v by using the AutoDesign tool v by using the Insert Level command in either the Dimension Map or the levels section of the Categories diagram and specifying the Associations for the new level v by dragging source columns onto the dimension line of the Dimension Map v by dragging source columns onto the levels section of a dimens...

Page 76: ... where there are too many child categories to be easily seen in the OLAP reporting components Because categories in manual levels typically connect to source categories in a lower level new source categories that are not linked to a parent manual category may appear during the generation process You can set up a temporary placeholder category in the manual level where these new orphan categories c...

Page 77: ...ram If you create a category in a dimension that does not yet contain a level for it Cognos Transformer automatically creates a new level and opens the property sheet of the new category Procedure 1 Open the Categories diagram for the dimension in which you want to create a category 2 Expand the category viewer right pane of the diagram as required to show the parent of the category to be added an...

Page 78: ... want to create special groupings such as Japan Hong Kong Calculated categories are identified in the Categories diagram by their own icon Creating a Calculated Category at the Dimension Level When created at the dimension level new calculated categories become siblings of the categories from which they are derived You can create calculated categories at the dimension level by using the Dimension ...

Page 79: ...ories from the Categories diagrammer or lower pane of the Categories diagram to each Category code drop location in the Expression definition box Tip You can also select and drag the appropriate level from the Categories diagrammer directly into your expression and type or copy and paste a valid calculation For more information see Appendix F IBM Cognos Transformer Expression Editor on page 355 6 ...

Page 80: ...gories diagram to another However the recommended method is to specify an Order by column for the level that contains the categories and then modify the Order value property for individual categories in that level The order values will then be automatically applied whenever new categories are added to the model For example suppose the categories in the Product Type level appear in the same order a...

Page 81: ... branch offices may report product sales down to the item level whereas others may report only to the product level You can create a subdimension for those branches that report to the item level Similarly your time dimension may contain levels for year quarter and month but your OLAP report users may not need to see the month values for the previous year You can use a subdimension to retain month ...

Page 82: ...ter levels Both drill down paths converge on the Month level That way your OLAP report users can drill down to the monthly data by either the calendar year or their fiscal year paths Tip You can view and manipulate drill down paths from the Dimensions pane of the Categories diagram Create an Alternate Drill down Path You set up an alternate drill down structure in a dimension to provide a differen...

Page 83: ... are often used when budgeting and forecasting or creating PowerCubes for planning related applications Although scenario dimensions are not a distinct dimension type you can flag any dimension other than the time dimension so that its data never rolls up to either the root category or a designated parent category which remains hidden You first designate a level that is a child of the non selectab...

Page 84: ... time periods such as year to date or previous quarter Some time properties are always true For example there are always twelve months in a calendar year and four weeks in a lunar month Because these standard properties have been programmed into Cognos Transformer you can work with time in ways not possible with other dimensions For example you can set up relative time categories to track period b...

Page 85: ...not appear in the cube Omit Categories Using Suppress on page 134 or clean the data to eliminate the null value Creating the Time Dimension Create a time dimension using any of several methods Whichever method you choose you must define or confirm the date input format You may discover that some of your data sources include information about their columns sometimes called metadata while others do ...

Page 86: ...prompts you for the information required in a logical sequence Later if you decide to change the information you can do so manually Procedure 1 From the Tools menu click Date Wizard 2 Type a name for the new time dimension and click Next 3 Choose the source column that contains the date values for the new dimension and click Next 4 Respond to the remaining prompts You can click Back to return and ...

Page 87: ...s property on the Source tab of the Level property sheets v whether to always include all date categories or only those for which source values exist when calculating relative time categories Set this property in the Inclusion box on the General tab of the Level and Category property sheets v the date function for each time level in the model such as Year Quarter and Month Set this property on the...

Page 88: ...calendar and fiscal years where the fiscal year spans different parts of two calendar years Typically calendar and fiscal years span different months converging at the month level with the same number of months in each drill down path When you create alternate drill down paths for your calendar and fiscal time periods the Order by column for each path must be identical at the convergence level Cog...

Page 89: ...that includes weeks you can choose from three options to handle weeks that span a higher level time period To specify how weeks split choose from the following options v Always split the spanning week into two separate weeks each of which includes the days that occur in the higher level time period Each part of the split week appears in the Categories diagram as a separate week For example the wee...

Page 90: ...ths and 5 4 4 week months Whereas lunar quarters or months must be placed in a lunar time hierarchy you can add weeks and day levels to your hierarchy as children of either lunar or standard calendar time periods To display the ending year rather than the starting year as the label for a lunar fiscal year that spans two calendar years select the Enable lunar fiscal labelling option on the General ...

Page 91: ...sion if the convergence level is Week Lunar month or Lunar quarter you must match both the Week begins on setting and the Add an extra week setting for all drill down paths If the last lunar month in the year has five weeks in a 4 4 5 week pattern the surplus days create an extra week The extra week is added to the previous lunar month to make a 4 5 5 week pattern rather than a 4 4 6 week pattern ...

Page 92: ...and stored in a database The first few rows are as follows Shift Hour Plant Equipment Temperature 01 0200 01 6EL Primary Boiler 235 01 0400 01 6EL Primary Boiler 237 01 0600 01 6EL Primary Boiler 233 01 0800 01 6EL Primary Boiler 235 02 1000 01 6EL Primary Boiler 228 02 1200 01 6EL Primary Boiler 232 02 1400 01 6EL Primary Boiler 231 02 1600 01 6EL Primary Boiler 233 You design your model to track...

Page 93: ...next year By including the most commonly required relative time categories in your cubes you avoid the need to recalculate them in every OLAP report The types of relative time categories are v single category periods such as Same Month Prior Quarter or Same Month Prior Year v to date periods such as Year To Date or Quarter To Date v N period running totals such as a 2 week total in the previous mo...

Page 94: ...d special categories only appear in reports when the time dimension contains one or more levels higher than the current period Set the Current Period Automatically Although the current period is set automatically you should explicitly select the column to use from the Data Sources list If two or more data sources contain columns that provide date values you will avoid ambiguity by explicitly clear...

Page 95: ...n and automatically updated on the time dimension property sheet Track Changes in a Measure over a Specific Time Period Businesses often need to track changes in a measure from one time period to another You can use relative time categories to enable your OLAP report users to analyze trends from month to month quarter to quarter and year to year For example suppose your users want to gauge current...

Page 96: ...use the most recent date values available after each data update Then you set up the following relative time categories Current Month Quarter To Date Year To Date Year To Date and Life To Date Procedure 1 Open the Categories diagram for the time dimension and position the pointer over the right side of the root category The pointer changes to a crosshair 2 Drag the pointer to the right of the root...

Page 97: ... current period as positive values in these fields signal measures that are essentially forecasts in future time periods Procedure 1 Open the Categories diagram for the time dimension and position the pointer over the right side of the root category The pointer changes to a crosshair 2 Drag the pointer to the right of the root category A new relative time category is created and its property sheet...

Page 98: ...nd Next Quarter projections Procedure 1 Open the Categories diagram for the time dimension and position the pointer over the right side of the root category The pointer changes to a crosshair 2 Drag the pointer to the right of the root category A new relative time category is created and its property sheet opens 3 In the Category code box type a meaningful name for the new category such as Next Mo...

Page 99: ...dd the required levels dimensions and measures to your model and create the cube For more information about creating cubes see Create a Single PowerCube on page 123 Setting up Special Categories A special category groups regular categories from any level in a dimension without regard for their normal hierarchical organization Special categories are unstructured and unlike the categories in an alte...

Page 100: ...for the dimension for which you want to create the special category and position the pointer over the right side of the root category The pointer changes to a crosshair 2 Drag the pointer to the right of the root category and release the mouse button A new special category is created and its property sheet opens 3 In the Category code box type the name of the special category and click OK 4 Connec...

Page 101: ...de of the root category The pointer changes to a crosshair 2 Drag the pointer to the right of the root category and release the mouse button A new special category is created and its property sheet opens 3 In the Category code box type the name of the special category and click OK 4 Add another special category to the right of the newly created special category 5 In the Category code box type the ...

Page 102: ...source category orphans we recommend that you either remove the orphan categories from the source file or use drag and drop in the Categories diagram to associate them with their proper parents For example suppose your model contains two data sources One is from an order tracking system and supplies data for the Country Region Branch and Customer Number categories The other contains Customer Numbe...

Page 103: ...nd click the Time tab 2 Type date values for Earliest date and Latest date as they currently exist in your data source and click OK 3 In the Data Sources list select the query that provides date values for the model 4 In the Data Sources list select the data source for which you want to regenerate categories and from the Run menu click Generate Categories From Selected Data Source Set up a Manual ...

Page 104: ...blems with your model design Run this tool after each major step in the model design process to check for potential problems Also if your model is based on more than one data source you may find it useful to generate categories from a specific source such as the one that supplies your key structural data or the values for your time dimension rather than from all the data sources Cognos Transformer...

Page 105: ...n generate categories from a selected data source with a simple Run command without having to adjust the Timing controls for the cube on the Data Source property sheet Procedure 1 In the Data Sources list select the data source you want to use to generate categories 2 From the Run menu click Generate Categories From Selected Data Source 3 Take note of any error messages If a Help button is availab...

Page 106: ...92 IBM Cognos Transformer Version 10 1 1 User Guide ...

Page 107: ...ich is calculated from the measures Revenue and Cost A measure folder can group existing measures into a logical collection and if needed can be assigned a calculated value itself In this model the Measures list contains both regular measures Revenue Product cost and Quantity and a calculated measure Profit Margin To perform calculations on a measure you create mathematical expressions based on an...

Page 108: ...not scale correctly when building PowerCubes In addition to checking the scale and assigning appropriate precision settings ensure you verify that results for query and cube rollup operations in Cognos Transformer are displayed as you expect Note Cognos Transformer automatically processes queries with measures last Add a Regular Measure Each model contains one or more regular measures that is nume...

Page 109: ...ist below it If you exclude a measure folder in the Measures list the lower level measures still appear Each measure must be excluded individually Note Measure folders in IBM Cognos Report Studio and Analysis Studio do not behave the same way as they do in IBM Cognos Series 7 PowerPlay You cannot drag and drop measures under a measure folder by dragging the measure folder itself Instead select all...

Page 110: ...ategory count dimension You can also create category count measures in time based partitioned cubes subject to certain restrictions To see information about time based partitioning consult the index Procedure 1 Click the Measures list to make it active and from the Edit menu click Insert Measure 2 In the Measure name box enter a name for the measure and click OK The measure name must be unique in ...

Page 111: ...erstand the results they are seeing in the intersecting cells of their crosstab reports Procedure 1 Click the Measures list to make it active and from the Edit menu click Insert Measure 2 In the Measure name box enter a name for the measure The measure name must be unique in the model 3 Select the Type tab click Calculated and then click Calculation 4 In the left pane of the expression editor expa...

Page 112: ...Rollup or After Rollup and click OK Specify How Missing Values Appear Cognos Transformer uses default measure settings for missing values to determine how null results are handled The default setting for missing values is NA when the default setting is used na is inserted into your OLAP reporting component for the missing values In releases of Cognos Transformer prior to version 8 3 the default se...

Page 113: ...n your OLAP reporting component However your report users should be advised that sign reversal can alter the results of run time calculations We recommend that you state which categories contain reversed signs in the Description box of the Measure property sheet thereby ensuring that this important information is shared with the appropriate users To reverse the sign of measure values for specific ...

Page 114: ...d arithmetic operators are addition subtraction multiplication division and exponentiation The Cognos Transformer expression editor also supports if then else constructs and brackets The boolean portion of the if then else supports AND OR and isnull a function that tests if a measure value is empty For more information about the expression editor see Appendix F IBM Cognos Transformer Expression Ed...

Page 115: ... the context you may change this default rollup to Minimum Maximum Average Count Count All Any or External For example suppose your sales analysis model contains a measure named QTY You want your OLAP reports to show the average monthly quantity sold for each store You use Regular rollup the Average function to obtain the correct result Your source data is as follows Date Store Product QTY 2007010...

Page 116: ...e uniquely identifies a specific category in the source file Otherwise Cognos Transformer makes the code unique by adding a tilde character with a numeric suffix creating a blended expression that cannot be interpreted by other IBM Cognos components For example suppose your cube contains more than one instance of the category code Item and so renames the second instance Item 1 the third instance I...

Page 117: ...et for each level in each dimension with externally rolled up measures v In the Associations box select the source column and click the ellipsis button to open the New Association dialog box v In the New Association dialog box select the Association role and then click the More button to select the structural source column to map to the level v If you are using allocations on the Column property s...

Page 118: ...cts the results If the Degree of detail is Day and your Days in week selection excludes weekends the records for each month are summed and then divided by the number of categories non weekend days in that month v If you insert a measure into a model where other measures have different settings for the Degree of detail property the inserted measure displays NA unless you initially set it to the low...

Page 119: ...orted measure type However Cognos Transformer only supports this feature if you specify that missing values be treated as NA on the Measure property sheet Also you must retain the default setting for First period Last period and Current period That is null and missing values cannot be excluded from the rollup calculations for these measure types Procedure 1 Open the Measure property sheet and clic...

Page 120: ...p tab 2 In the Regular rollup box select a rollup function If you select the Average function you can weight the average by selecting a measure from the Regular weight box However the weighting measure must have a rollup function of Default Sum or Sum 3 In the Time state rollup box click a rollup function If you select the Average function you can weight the average by selecting a measure from the...

Page 121: ...se dimension view options the sales records have identical non measure values In cases 1 2 and 3 consolidation uses Duplicates rollup to combine records with identical values in their non measure columns In case 4 unless Time state rollup is selected on the Measure property sheet consolidation uses Regular rollup to combine records with values made identical through the use of dimension views Note...

Page 122: ...60 3500 200703 East Store2 40 4000 After Regular rollup Average the monthly records for each Store in the East region are consolidated again Date Region Quantity Revenue 200701 East 110 2 55 11000 2 5500 200702 East 90 7000 200703 East 100 2 50 7500 2 3750 Your cube has a dimension view where store values are aggregated to the East level Set a Duplicates Rollup Function for Measures The Duplicates...

Page 123: ...fault in some cases you may want to specify a different rollup function such as Average For example suppose the following before rollup records are duplicates Date Store Product Quantity Price Revenue 20070101 Store1 TR139SQ 50 100 5000 20070101 Store1 TR139SQ 20 100 2000 20070101 Store1 TR139SQ 40 100 4000 20070101 Store1 TR139SQ 70 80 5600 The results after Duplicates rollup Average are as follo...

Page 124: ...ecified for each individual member of a subset To do this on Windows locate the ppds_cfg xml file in the installation_location configuration directory open it in any text editor and add the DisableConstantAllocationRollup parameter with a value of 1 to the PowerPlay DataServer section in the following way Section value PowerPlay DataServer DisableConstantAllocationRollup value 1 Section You can us...

Page 125: ...ated Measure Scope If a calculated measure is based on two regular measures the measure scope dimension map shows the lowest common level If the lowest common level is allocated the level appears with the same shading green for both the allocated measure and the calculated measure Procedure 1 In the Measures list select the measure whose scope you want to see 2 From the Edit menu click Show Scope ...

Page 126: ...gories Procedure 1 In the Dimension Map double click the parent level of the levels to which you want to allocate measure values to open its property sheet 2 Click the Allocation tab A list of measures that are candidates for allocation appears 3 In the Measure list box right click the Allocation type for the measure and select an option The allocation types include Do Not Allocate Constant and By...

Page 127: ...llocation type for a level the new allocation type is applied from the categories in that level to all descendant categories You can also set the allocation type for an individual category For example suppose that some operating costs associated with your product lines are based on sales channel factors such as direct sales and catalog orders Others are based on revenue You want to allocate costs ...

Page 128: ... Similarly there may be cases where you do not want categories in the next lower level to inherit the allocation type from the higher level When you disable allocation Cognos Transformer creates data points with missing values which appear as zeros in your OLAP reports You can change this default display to na instead For example suppose you track sales revenue each day for both products and regio...

Page 129: ...n time to the measures in the cube Converted values are subsequently rolled up using the method you specify in the Measure property sheet In previous Cognos Transformer releases the currency format specified in the Currency Table overrode all other currency format settings Starting with Cognos Transformer version 8 x if individual measures have a currency format specified this format overrides the...

Page 130: ...rom the name of the column used to create your time dimension and from each other Otherwise Cognos Transformer cannot differentiate between them The Check Model tool issues a warning if your model contains a Currency Table but none of the measures have currency conversion enabled You can ignore this message however currency conversion will not be available in any OLAP reporting components The Curr...

Page 131: ...ck OK 3 Confirm that the Base table columns box shows the correct names for the Date Country or region code Rate and optional Label source columns If not click the ellipsis button to the right of the column name box and make corrections in the New Association dialog box If the base table columns are not yet defined click Add to specify the associations for mandatory source columns in the New Assoc...

Page 132: ... euro and values had to be converted from the original currency to the euro and then from the euro to the target currency with rounding to the specified precision at each step Additional information to be specified in the Currency Record dialog box includes Entry date in the EMU YYYYMMDD and the fixed rates to use when converting data to or from the national currencies of the EMU countries or regi...

Page 133: ... are not EMU members add a row for each time period to which the conversion rates apply and insert the correct date This constitutes the Date column v Uniquely identify the countries or regions to which the rates apply using the internationally recognized code for each country or region If the country or region already appears in the base Currency Table the code must be identical to that used ther...

Page 134: ... preparing the conversion source files and creating a Euro table you can enable the conversion Procedure 1 Specify the time dimension level to which the conversion rates apply by clicking each currency in the Currencies box of the base Currency Table and clicking Properties to open the Currency Record dialog box 2 Confirm that the Date level property is correctly set 3 Click OK to update the curre...

Page 135: ... or 1 17 US dollars floating exchange rate Update a Currency Table Manually If your model design is simple and does not use external data sources to supply conversion rates you can manually create and update the required currency tables If you used an external data source to create a preliminary Currency Table but decide to maintain it manually remember to clear the Use an external currency data s...

Page 136: ...122 IBM Cognos Transformer Version 10 1 1 User Guide ...

Page 137: ...end users will require the cubes to be distributed For information about cube languages and locales see Managing Languages and Locales on page 161 Tip We recommend that you experiment until you have a satisfactory balance between cube build and data retrieval times This customization may but need not parallel the security based views that you associate with your configured IBM Cognos namespace You...

Page 138: ...mark in the file name By default Cognos Transformer saves cubes in the My Documents Transformer PowerCubes directory You can set the default location to which Cognos Transformer saves models by changing the PowerCubes directory setting on the Directories tab of the Preferences property sheet On Windows Vista Cognos Transformer saves cubes in the Documents Transformer PowerCubes directory We recomm...

Page 139: ...e categories will become the individual cubes in the cube group 6 In the Focus of detail area select the lowest level of category detail Note This is the level at which you want data for external categories to be summarized 7 When you have finished setting the required properties on each of the other tabs in the PowerCube property sheet click OK Defining a Time based Partitioned Cube Time based pa...

Page 140: ...c need to do a full build resulting in shorter down times for the production system and a more easily managed maintenance schedule v support rolling time periods You can manually edit the vcd file to remove references to cubes that are no longer required and drop invalid or out of date categories The control cube and definition file are automatically updated with the newest categories and cube ref...

Page 141: ...rollup cannot be one of the following a before or after rollup calculated measure an externally rolled up measure another category count measure an allocated measure if the allocation occurs in the same dimension as the category count above the counted categories level v Category inclusion is based on the time level specified when the time based partitioned cube was first created Not all children ...

Page 142: ... or possible categories that is an inclusive approach However this may not be feasible if for example all new categories are added only to the latest cube In such cases the sort order for the categories is stored in the individual child cubes called disjoint cubes and not in the large cube Sorting Considerations Even when the control cube contains high level metadata about the overall structure of...

Page 143: ...hild cubes the overall sort order is a merging of the categories from the individual cubes Example Sorting After the Addition of Categories to Disjoint Child Cubes Suppose your model design includes disjoint child cubes The overall category order in the parent cube is A B C D Categories A and B appear in one child cube C and D appear in the other Procedure You add a new category E to the A B child...

Page 144: ... child cube is built reflecting the changes Note Historical information continues to exist in older child cubes new information is placed in the new child cubes It is possible that rollups will not reflect the expected values because they may be using data from two sources Example Sorting in a Slowly Changing Dimension The following report shows sales by the sales representative Marthe for 2004 an...

Page 145: ... Q1 C Transformer Cube 2006 Q1 mdc You can also create a time based partitioned cube that is based on several different levels of the time dimension For example to improve run time performance your users might prefer to query against a cube that is based on a year cube three quarter cubes and a month cube To add these time based partitioned cubes you must first generate the child cubes for the spe...

Page 146: ...on is retained in the cube but restricted users cannot access it Procedure 1 On the PowerCube property sheet click the Dimensions tab 2 Right click a dimension to omit or hide and click Omit Dimension 3 Click OK Customizing Cube Content with Views Instead of omitting measures or entire dimensions from a cube you can select a customization option to deliver only information of interest to your user...

Page 147: ...than create one large cube that has an unacceptably long build time Create a Dimension View When you need to develop OLAP reporting solutions for diverse groups in your organization you can create dimension views and apply them to the cubes in your model thereby creating customized cubes and drill through reports to meet the needs of each group For example suppose you want to create a different cu...

Page 148: ...ies and from the Diagram menu customize the new dimension view by selecting the category actions that will help you achieve the summarization you want For more information see v Omit Categories Using Suppress v Omit Categories Using Cloak on page 135 v Omit Categories Using Exclude on page 136 v Omit Descendant Categories Using Summarize on page 137 v Omit Categories Using Apex on page 138 5 Open ...

Page 149: ...ore than one entire alternate drill down path Suppressing any category located above the convergence level in a drill down path has no effect on categories in other drill down paths Procedure 1 Open the Categories diagram for the dimension that contains the categories or level to be suppressed and if you have not already done so add a dimension view For more information see Create a Dimension View...

Page 150: ...el to be cloaked and if you have not already done so add a dimension view For more information see Create a Dimension View on page 133 2 In the Dimension pane select your new view 3 In the category viewer select the categories or level to be cloaked and from the Diagram menu click Cloak 4 Open the PowerCube property sheet and click the Dimensions tab 5 In the Dimensions list right click the dimens...

Page 151: ...nd their associated measure values Procedure 1 Open the Categories diagram for the dimension that contains the categories or level to be excluded and if you have not already done so add a dimension view For more information see Create a Dimension View on page 133 2 In the Dimensions pane select your new view 3 In the category viewer select the categories or level to be excluded and from the Diagra...

Page 152: ... dimension in which you created the dimension view and click View 6 In the Select a View dialog box select the dimension view that you created and click OK twice Omit Categories Using Apex When you create a dimension view and use the Apex option on a selected category the resulting cube contains only the apexed category and its immediate descendants The ancestors of the category any siblings and a...

Page 153: ...through paths are required the PowerCube does not have to be rebuilt or updated As a result end users are less affected by cube rebuilds or updates v Drill through paths can be defined by report or analysis users Because report or analysis users can define drill through paths according to their needs they are less dependant on the Cognos Transformer modeler For more information about specifying dr...

Page 154: ...uded in the cube Drill Through with Impromptu Query Definition Files If your model uses an Impromptu Query Definition iqd file as a data source and IBM Cognos Impromptu is installed Cognos Transformer automatically adds the corresponding Impromptu report imr file to the drill down list for each measure The correct filter is passed to display the appropriate Impromptu data according to the dimensio...

Page 155: ...ill through list for each measure in the source You can remove some or all of these reports from the Measure property sheets or add additional reports that are not associated with the original source To change targets for all measures in a cube at once use the Drill Through tab on the PowerCube property sheet To meet the needs of various users you may need to set up multiple drill through targets ...

Page 156: ...e second drop down list box click the drill through target for which the restriction will apply 4 In the Dimension Map click the level or dimension for which you want to restrict drill through 5 Right click the dimension and click Exclude DrillThrough The interface highlighting shows which level or dimension is now unavailable or out of scope for drill through By default the unavailable item appea...

Page 157: ... Each file contains measure values each is structurally identical to the source file on which the model is based and each filters the data to retrieve values for only one sales division Procedure 1 Open the property sheet for the PowerCube that will use the alternate source file and click the General tab 2 In the Data source list box select the appropriate item in the alternate source file 3 In th...

Page 158: ...uickly update this related information for an existing cube without affecting the main cube data For example suppose your PowerCube production schedule involves recreating a cube monthly while your transactional data source is updated weekly You set up drill through so your users can see on the first week of each month the first weekly report on the second week the first and second weekly reports ...

Page 159: ... make it active and then from the Tools menu click PowerCube Status 2 In the PowerCube Status dialog box select the cube whose staus you want to view The status value is listed in the PowerCube Properties box Chapter 6 Creating PowerCubes 145 ...

Page 160: ...146 IBM Cognos Transformer Version 10 1 1 User Guide ...

Page 161: ...ategories members dimensions or components thereof This filters the cube data that is shown to specific report users Member based security uses security objects such as users groups or roles to define user access to information With cube based security you apply security to an entire PowerCube or cube group by setting a password to restrict access to authorized users Assessing Your Security Requir...

Page 162: ...gning access to a level may not guarantee that the user also has access to all the categories or members in that level Before you add security in Cognos Transformer ensure that security was set up correctly in IBM Cognos For more information see the Administration and Security Guide IBM Cognos Security Objects Users groups and roles are IBM Cognos security objects created for authentication and au...

Page 163: ...update the model security at any time When you make changes to the security objects in your configured namespaces you do not need to rebuild the cube to reflect the changes PowerCubes reflect the applied member based security at run time For example if you use a group called System Administrators in a custom view within Cognos Transformer and then change the users who belong to that group in the a...

Page 164: ...om view appears in the custom view list the model dimensions appear in the Dimensions list and the measures appear in the Measures list 8 To omit the dimension from the custom view select the dimension and then click the Omit button Tip To reset a dimension to the parent or default view select the dimension and then click the Reset button 9 To exclude a measure from the custom view in the Measures...

Page 165: ...stom view Steps to Remove Security Objects From a Model Procedure 1 From the Security menu click Show Security Objects The Security Object Management dialog box shows the security objects imported into the model and their associated custom views 2 In the Security objects imported into model list select the security object you want to remove from the model and click Unassign Custom View 3 When you ...

Page 166: ... value or cell value is not shown to a union when none of the custom views in the union can see all the categories in the cell domain v in a union of custom views the root category shifts to the lowest common ancestor of all member custom views These rules apply to all custom views in the union The following examples illustrate the union of custom views in different scenarios Example Two Custom Vi...

Page 167: ...ed As a result the values for those categories are visible only in the total for the Location dimension Example Apexed Custom View Excluded by Another Custom View in the Same Dimension This example describes the union of an apexed custom view excluded by another custom view in the same dimension Custom view A apex Australia Custom view B exclude Far East The Region dimension is temporarily modifie...

Page 168: ...n In a crosstab report the cells for categories outside of the apex such as Golf Equipment Central Europe are shown but the data values appear as NA Example Union of Two Multi dimensional Apexed Custom Views This example describes the union of two apexed parent dimensions with multiple categories Custom view A apex Camping Equipment apex Asia Pacific Custom view B apex Outdoor Protection apex Amer...

Page 169: ...the Same Dimension The following examples describes the union of excluded categories in the same dimension Example 1 Custom view A exclude Central Europe Custom view B exclude Americas Each custom view is shown the values excluded by the other custom view Example 2 Custom view A exclude France Custom view B exclude Central Europe Mexico France is the only category that is not shown in either custo...

Page 170: ...is dimension is omitted from the domain of any query formed by custom view A The PowerCube query engine replaces the gap with the root of the Region dimension Custom view A is shown all other dimensions including the complete Product dimension Custom view B is not shown any category in the Product dimension and this dimension is omitted from the domain of any query formed by custom view B The Powe...

Page 171: ...r IBM Cognos Series 7 versions of PowerPlay for Windows Procedure 1 In the property sheet for the PowerCube or cube group click the Output tab 2 In the Password box enter the password and click OK 3 In the Confirm Password dialog box re enter the password and click OK Combining Custom Views with Dimension Views You can add custom views to a cube that already has dimension views Use this combinatio...

Page 172: ...means that should your users apply filters for North America with the contribution for Canada excluded they may erroneously conclude that Canada had no sales Procedure 1 In the PowerCubes list right click the cube you want to modify and then click Properties 2 On the Processing tab of the PowerCube property sheet select the Block totals for parents with excluded children check box and then click O...

Page 173: ...click Open Tip If you are already in Cognos Transformer click Open from the File menu 2 In the Import model with IBM Cognos Series 7 user class view dialog box select the appropriate security import option and click Next 3 If you selected Import user class views and user classes from the model in the Logon dialog box select the appropriate namespace and then log on with your user ID and password 4...

Page 174: ...160 IBM Cognos Transformer Version 10 1 1 User Guide ...

Page 175: ...requesting data from a multilingual package or report the run locale may be used to determine the language in which data is returned Note When a report is authored the author chooses a content locale This content locale is saved with the report and is the locale in which the metadata is expressed when Cognos Transformer accesses the report As a result ensure that reports used as data sources in Co...

Page 176: ...eed to specify while publishing a PowerCube include v Null suppression options You can set suppression options for the package published with the PowerCube These options determine whether IBM Cognos studio users can choose to hide empty rows only empty columns only or both empty rows and columns Options also determine the types of empty values that can be suppressed such as zero or missing values ...

Page 177: ...ce with other data sources such as a relational namespace For more information see the IBM Cognos Framework Manager User Guide v from the command line For more information see Appendix A Command Line Options on page 209 Tip You can disable cube publishing on the Processing tab of the PowerCube property sheet If you have already published a PowerCube and you want to replace it with a new version yo...

Page 178: ...namespace the All applicable namespaces including unsecured PowerCubes option is intended only for testing migration from a Series 7 namespace to an alternate security provider If you are testing the migration of IBM Cognos Series 7 PowerCubes and you want to review the security view that reflects the union of the applicable namespaces you must log on to all applicable namespaces in IBM Cognos pri...

Page 179: ...only or columns only v Allow access to suppression options When null suppression is selected this option determines whether users can choose the types of empty values that will be suppressed such as zero or missing values The types available to users depend on the studio For packages that contain more than one cube the selected suppression options apply to all cubes in the package As well these op...

Page 180: ...on to make suppression options available to the studio users when working with this package v Allow multi edge suppression When null suppression is selected this option determines whether users can suppress rows only columns only or both rows and columns If this option is not selected users can suppress rows only or columns only v Allow access to suppression options When null suppression is select...

Page 181: ... publish specification see Appendix A Command Line Options on page 209 Creating a Publish Specification to Publish All PowerCubes in the Model Use this method to publish all cubes in the model You create the publish specification from the Run menu in Cognos Transformer and publish the cube from the command line Procedure 1 From the Run menu click Create a publish specification for all cubes 2 In t...

Page 182: ...published successfully a confirmation message appears and data source connections and packages are automatically created for all affected PowerCubes in IBM Cognos Connection PowerCube Load Balancing Load balancing of the IBM Cognos servers is typically the responsibility of the administrator As a modeler you would not normally need to consider how the servers balance the load for query access Howe...

Page 183: ...ing your cubes v Update data source connection information for PowerCubes accessed by your OLAP report users If category code values in the PowerCube change advise your cube consumers of the categories or members that changed so that they can better handle the impact on their reports For more information about changes to category codes see Member Unique Names on page 22 We strongly recommend that ...

Page 184: ... appends the new data to the existing cube When you disable incremental update Cognos Transformer overwrites the existing cube For more information see Setting up Placeholder Categories for Cube Groups on page 186 To rebuild an incrementally updated cube you must delete the old mdc file and recreate the entire cube For information about updating published cubes see Updating Published PowerCubes an...

Page 185: ...open the model at the last checkpoint before failure and continue to develop it from that point or continue from the point where you last saved the file The qy file is deleted Cognos Transformer also writes messages to a log file which is stored in the location specified on the Directories tab of the Preferences property sheet The log file has the same name as your model with a log extension If Co...

Page 186: ... columns in the model select the items in the Source list and click Add 3 For columns that you want to remove from the model select the columns in the Model list and click Remove 4 For unmatched columns identified by a plus sign in the Matched to Source column in the Model list do one of the following v To manually match query items to columns select a data source item in the Source list and a col...

Page 187: ...e changes for your review From the Candidates list click the item that you want to match to the column to or click Leave unmatched Results Data source items are now synchronized with the model Move Categories When Source Data Changes You can prevent model errors due to changes in your source by designating a unique level This tells Cognos Transformer that categories in that level are identified by...

Page 188: ...egories in your model have moved from one location to another Suppose your organization has two staff members Alessandra Torta in Milan Employee Number 5528 and Ellen Shapiro in Amsterdam Employee Number 4125 After these two employees switch offices you want the measure data associated with them to move accordingly If you simply make the switch in your source data without specifying that the Emplo...

Page 189: ... specify a unique level ensure that your data does not have uniqueness violations Allocate extra time for data source processing to verify that all categories are unique within a level or eliminate this step if it is not necessary by clearing the Verify category uniqueness option on the property sheet for the data source v Consolidate transactional data to a higher summary level using the Duplicat...

Page 190: ...ll categories are included in the cube v Direct create The direct create optimization method is also a variation of the Categories method When Cognos Transformer uses the direct create method all categories in the model are added to the cube while the data sources are being processed Records that do not generate new categories are directly updated to the cube Use this method only when your model i...

Page 191: ...roduct Customer CustomerSite Order and OrderDetail This source would contain many duplicate values and the joins between the tables would be relatively complex Instead you create three separate sources for Products Customer Site and Order Order Detail data The volume of data contained in each is less than that in the single source and there are only simple joins between Customer and CustomerSite t...

Page 192: ...A category is only considered active if it has been created updated moved or had its properties changed after the date you specify For example suppose that last year your department was responsible for inspecting 40 homes for the elderly After a consolidation of facilities you now inspect and report on the operations of only 30 homes You delete the categories for the 10 homes that have been closed...

Page 193: ...more information see Omit Dimensions from a Cube on page 132 Update Cubes Incrementally It is best to place frequently updated data in one or more separate data sources so that you can perform incremental updates to quickly add the latest changes to your existing cube Tip Make sure that your data sources contain only new data otherwise data duplicated in your source files will be duplicated in the...

Page 194: ...e is a tradeoff however The more partitions there are the longer it takes to build the cube The partitioning process involves multiple passes through the data Categories are consolidated and sorted records in the resulting temporary work files are counted and the information is divided into rows that summarize the measures at one or more optimal partition levels By default Cognos Transformer autom...

Page 195: ...ion for that region However you cannot add new partition levels to a model if any cube in that model uses incremental update Instead you must repartition the model and rebuild the cube incorporating all the data from all increments Partitioning Checklist When partitioning confirm the following v Ensure that you set up clearly recognized business related groupings in your source data such as commod...

Page 196: ... number of records in the summary partition drops below this specified partition size v Specify the Maximum number of passes beginning with either five 5 or one 1 pass for every manually defined partition level The partitioning process continues one pass per level until this number is reached or until subsequent consolidation passes fail to yield a further reduction in the summary partition size T...

Page 197: ...e partition number you want to assign and click OK Note Although you can assign the same partition number to categories from different levels we recommend that you select categories from the same level and manually set no more than three levels of partitioning 8 Assign the same number to each category in the partition level starting with the lowest level and working up to the top level in the dime...

Page 198: ... Auto partition tab to that number of partition levels plus one However on testing you discover that additional partitions are needed You try various strategies using the Products dimension Procedure 1 First you assign a single partition to the entire Product Type level which results in the following Level 0 All dimensions Summary category record count of 237 Level 1 Line dimension Type 1 category...

Page 199: ...nce Item 2 and so on These category codes are numbered as they are encountered in the model This means that the codes do not stay the same in a predictable sequence as your organization and model evolves The tilde characters are interpreted as non numeric and because these codes do not appear in the data source they are ignored during the consolidation pass As a result duplicate records are overri...

Page 200: ...be group to list the members 6 In the property sheet for the placeholder cube change the name to New Setting up a New Cube Group Member Use the following steps to define a new cube group member Procedure 1 Open the diagram for the dimension on which the cube group is based 2 On the property sheet for the placeholder category rename the Category Label and Source file to match the new data column 3 ...

Page 201: ...a cube_name__UTCdate_time ver file exists Cognos Transformer deletes this file Only one cube_name__UTCdate_time ver file can exist in each deployment location Cognos Transformer creates a file named cube_name__UTCdate_time ver The prefix of this file name cube_name__UTCdate_time is the same as the folder in this directory that contains the newest version of the cube v Cognos Transformer writes mes...

Page 202: ...cube is rebuilt v Select Prompt to copy and activate to have Cognos Transformer prompt for confirmation before deploying and activating the rebuilt cube v Select Automatically copy and activate to have Cognos Transformer copy and activate the cube automatically each time it is rebuilt When you select Do not deploy Cognos Transformer performs no other action when the cube is rebuilt 5 Click Add in ...

Page 203: ...lt PowerCube in the production environment after the cube is manually copied to the deployment location where the IBM Cognos Business Intelligence server can access it You can copy the newer version of the cube to one or more deployment locations and then activate the newer version in each location simultaneously You can use the pcactivate command with updated Cognos Transformer cubes version 7 x ...

Page 204: ... prompt type the pcactivate command using the following syntax pcactivate cube_name mdc deployment_location where deployment_location specifies the directory in which the base cube is located as specified by the cube connection information in IBM Cognos Administration Using the previous example the location would be C Cubes For example type pcactivate Product mdc C Cubes To specify multiple deploy...

Page 205: ...reporting computers can involve multiple networks and host computers Average response times can be greatly affected by network capacity and the number of simultaneous requests to be handled Daily fluctuations in your users workload are just as important as the architecture used for your reporting system Computer Power When doing capacity planning for the data servers and reporting computers used i...

Page 206: ...ng cubes to your UNIX or Linux environment For more information about setting up and administering secure IBM Cognos deployments see the Administration and Security Guide and the Architecture and Deployment Guide Guidelines for Model Design Cube Creation and System Maintenance Process Use the following guidelines when building PowerCubes in UNIX or Linuxenvironments v Design and create a prototype...

Page 207: ...o v filter by value on specific columns For example you can include data for only a few of your regions when generating categories for the Regions dimension in the model v return a specific number of rows For example if the server data source contains two million records you can use only the first 10 000 records for your model prototype v return only unique records if the server data source contai...

Page 208: ...ustomer Center http www ibm com software data cognos customercenter Although you can use a delete script to remove an entire namespace to selectively remove obsolete security objects you must modify your model in Cognos Transformer on Windows The same applies if the contents of your namespace change because such changes will affect your custom views Controlling Processing with Preference Settings ...

Page 209: ...ored and an error message is written to the log file This type of error handling applies for example if the preference file value for the ModelSaveDirectory is incorrect However if the value for CubeSaveDirectory is incorrect the variable is ignored but no log file entry is added If the ChildRatioThreshold value is outside its acceptable range the invalid setting is changed at run time to either i...

Page 210: ...ault is the temp subdirectory in the installation_location c10 directory v Preference Name ModelSaveDirectory Value path This setting specifies where Cognos Transformer saves models On Windows the default is the Transformer Models subdirectory under your home directory On UNIX and Linux the default is the temp subdirectory in the installation_location c10 directory File v MultiFileCubeThreshold Th...

Page 211: ...nsistent possibly incomplete state 3 includes severe errors and warnings indicating a potential problem Warning messages do not impede processing 4 includes severe errors errors warnings and informational messages default You can use the log file to check the status of cube creation The progress of a cube update is indicated by statements in the file each containing the following fields date and t...

Page 212: ...he character used as a decimal point in a data source The default is a period v Preference Name DefaultSeparator Value This setting specifies the field delimiter in a delimited text data file The default is a comma v Preference Name ThousandSeparator Value This setting specifies the character used as a thousands separator in a data source The default is a comma v Preference Name CenturyBreak Value...

Page 213: ...ld number of bytes at which Cognos Transformer splits its work files The minimum is 1000000000 and the maximum is 1500000000 The default value is 1500000000 v Preference Name WorkFileSortMethod Value 1 Use this preference to choose between new sorting methods introduced in this version of Cognos Transformer and the type of sorting used in Cognos Transformer 8 4 This version of Cognos Transformer i...

Page 214: ...removed before the string is used For example a pair of backslash characters is replaced by one backslash v Variable substitution is not performed on the values of environment variables Shared Library Variables To run Cognos Transformer on UNIX or Linux the loader requires that the library path variable specify the location of the shared libraries The library path variable for each supported opera...

Page 215: ...mation see Log Files on page 196 Scheduling Batch UNIX and Linux Production Jobs If you want to automate cube creation you can use the UNIX or Linux scheduling commands the UNIX Linux at command or a crontab file to schedule these jobs at convenient times in batch mode For example suppose you have a model that you use to create 20 individual cubes and a cube group consisting of 10 cubes You can su...

Page 216: ...start the process For more information see Appendix A Command Line Options on page 209 Example Restarting a Failed Process from the Beginning When Cognos Transformer builds cubes some conditions may result in a failed process For example if Cognos Transformer has insufficient disk space for the model files data temporary files and cubes an error occurs and processing stops In such cases you can re...

Page 217: ... 2 19036 categories Country 274 categories Country 274 categories HR Code 3 75916 categories HR Code 4 123856 categories HR Code 5 271095 categories Multiprocessing with Cognos Transformer Server By upgrading to a multi CPU server and running multiple instances of Cognos Transformer in parallel you can significantly decrease the build time for your largest cubes In a model that generates more than...

Page 218: ...ate phase when it is freed for use by the operating system disk cache In the first phase the more categories there are the more memory is required Although models differ working memory usage is typically 500 1 500 bytes per category Paging swap file use occurs if the space allocated to working memory is insufficient and cannot grow to the amount required to process the model Reconfiguration of the...

Page 219: ... 8 D 1024 1024 2 is greater than 1431 the space calculation is that number plus E If the calculated result is less than or equal to 1431 the required space is that number do not add E Taking the test model as an example the calculated space requirement is 7047 MB given the following input values v A the total number of dimensions v B the number of dimension views associated with the PowerCube v C ...

Page 220: ...ted and records are committed to the PowerCube To reduce cube build time try raising the setting from its default 500000 to 800000 Or if you get error message TR0112 during a cube build lower the setting so records are committed more frequently thereby freeing up space You change this setting using the Maximum number of transactions per commit preference on the General tab of the Preferences prope...

Page 221: ... rows to fetch in each fetch operation can improve performance on some systems Although the current limit for this number is 32767 numbers larger than the default 100 may degrade performance on some systems v Fetch Buffer Size Increasing the size of buffer used during fetch operations from the default 2048 bytes can improve performance on some systems Where both entries have been changed the row s...

Page 222: ...um command to add the timing values for your selected cells as displayed in the lower toolbar 5 Repeat the above steps to examine each subphase These appear in distinct blocks as follows with each completing before the next subphase begins v Data Read INITIALIZING CATEGORIES OPEN DATA SOURCE READ DATA SOURCE MARKING CATEGORIES USED v Metadata Update SORTING UPDATE CATEGORY AND PROCESS WORK FILE ME...

Page 223: ...ng new versions of published cubes v supporting IBM Cognos prompts v opening specified mdl files and executing MDL statements v specifying the number of records for a test cube v regenerating categories and the measure scales used with them without building the cube Windows only command line options enable you to v run AutoDesign build a model create a cube and display the results in a supported r...

Page 224: ... filename py filename mdl Notes When using command line options the following considerations apply v On Windows you can use n with some options to run Cognos Transformer in batch mode v The option in the command line always starts with a dash v Command line options are case insensitive Arguments are case sensitive v If there are spaces inside any argument you must enclose the argument in double qu...

Page 225: ...ing pfilename py mfilename mdl e Updates the model structure but not the data e pfilename py mfilename mdl Restriction Cannot be used with c On Windows use with n On Windows Unix and Linux use with o f Specifies the user defined preference file Can be used to publish PowerCubes in batch mode and include prompts in an XML command file using the XML schema for preference files fpreference_file pfile...

Page 226: ...word pfilename py mfilename mdl m Opens the specified mdl file or accepts Model Definition Language MDL statements mfilename mdl n Runs cogtr in interactive or batch mode ndisplay_state pfilename py mfilename mdl Restriction Use on Windows only nologo Omits splash screen display when opening Cognos Transformer nologo pfilename py mfilename mdl Restriction Use on Windows only o Turns off various mo...

Page 227: ...or the test cube vdata_subset_number Restriction Use with c m or p On Windows use with n x Updates the column and measure scales based on the data source x mfilename mdl y Specifies how IBM Cognos Series 7 user class security conversion is performed v Use y1 to preserve both the Series 7 user classes and custom views associated with the IBM Cognos Series 7 model y1namespaceName username password v...

Page 228: ...gether to open the binary model file go_sales pyj and process it as described cogtr c pgo_sales pyj The following UNIX Linux example uses the c and m options together to open the equivalent full model definition the mdl text file for go_sales and process it as described cogtr c mgo_sales mdl d option This option sets a new value for a Cognos Transformer user preference The value overrides settings...

Page 229: ... setting for the DataSourceDirectory changing it to C Newdata On Windows it opens the Transact mdl model and creates the cubes defined there in batch mode cogtr dDataSourceDirectory C Newdata nTransact mdl The following example overrides the default value at which a warning is issued for a parent category having too many descendants The new preference setting threshold is 25 children cogtr dChildR...

Page 230: ...directory in the installation_location c10 directory If the logs subdirectory does not exist then the default is the current working directory LogFileName Default LoggingFrequency Default 1 LunarFiscalLabeling Default TRUE MaxTransactionNum Default 50000 ModelSaveDirectory Default On Windows this is the Transformer Models subdirectory under your home directory On UNIX and Linux this is the temp su...

Page 231: ...he object names labels descriptions drill through reports and security information cogtr e n o pNew pyj f option This option specifies the user defined preference file or files to use If you do not include the full directory path with the file name Cognos Transformer searches the executable directory of your most recently installed rendition of the product for the required xml file This option is ...

Page 232: ...ML file format supports multi value commands and user defined preference files Commands in XML files are executed sequentially unless specific rules are defined Example The XML schema can have multiple sections and multiple commands Commands can contain multiple parameters and parameters can have multiple values The cogtr xml file conforms to the XML schema The cogtr xml file contains two major pr...

Page 233: ... apos EquifaxCube apos Section Section Name RecentFileList Preference Name File1 Value c NationalOriginal mdl Preference Name File2 Value c Modified Cubexx mdl Preference Name File3 Value c GreatOutdoorsCompany_Slow_v1 mdl Preference Name File4 Value c testcube mdl Section Sections Commands Command Name Publish Parameters Parameter Name CubeName Value test Parameter Name CognosConnectionDataSource...

Page 234: ...s Command Command Name prompts Value MyQuery2 Parameters Parameter Name Prompt4 Values Value 1 Value Value 4 Value Value 8 Value Values Parameter Parameters Command Commands Settings Publishing in Batch mode The f command can be used to publish PowerCubes in batch mode Example The syntax is cogtr fspec_file xml n2 ox filename mdl where spec_file xml represents the path and name of the publish spec...

Page 235: ...command used to create the data source signon if needed Default False DataSourceDescription The description of the data source Default Empty DataSourceToolTip The tool tip for the data source Default Empty DataSourceUpdate The command used to update a data source Default FALSE PackageName The name of the package in Content Manager This is a mandatory value The set will fail if it is not defined De...

Page 236: ...ginally published using the command line you can do this using either the Cognos Transformer user interface or the command line after changing the DataSourceUpdate and PackageUpdate parameters in the publish specification file to TRUE The following example of a publish specification file shows the xml code for publishing a cube Command Name Publish Parameter Name CubeName Value NATIONAL Parameter ...

Page 237: ...e command file name contains a sequence of statements that define prompt values The Command File can contain one or more commands for prompts The command name is prompt The value attribute of the command specifies the prompt name Each prompt command contains one or more Parameter elements that specify a query name prompt attributes and values The Query Parameter element specifies which query the p...

Page 238: ...e Value Range Any type that understood by RS Optional Not in use Parameter Name PromptValueType Value String Parameter Name Values Values Value Range form Value Value Range to Value Values Parameter Parameters Command Commands Settings Simple Prompt A simple prompt is a variable that has a single value For example Parameter Name SimplePrompt Value Single Value Multi valued Prompt A multi valued pr...

Page 239: ...eing deployed use g option with ox option Note The ox option can only be used on Windows The powercube_name parameter represents the name of the cube in the Cognos Transformer model If powercube_name specifies a cube group that is not a time based partitioned cube all child cubes of the cube group are copied to the deployment location and activated If powercube_name specifies a time based partitio...

Page 240: ...i option to bypass the prompt and force Cognos Transformer to open the original model file instead of the checkpoint file The syntax for using the i option is as follows cogtr i filename py Note Checkpoint files have a qy extension As with py files the question mark in the extension is replaced by the character that is used in your release of Cognos Transformer such as qyj The following Windows ex...

Page 241: ...tion in the Signons list or retrieving user IDs and passwords from the configured authentication source This is especially useful when used with mdl files which by default do not use verb MDL and so do not store signon passwords For databases referenced in an Impromptu Query Definition IQD file the signon is the logical database name that appears in the related gateway ini file Multiple IQD data s...

Page 242: ...sal_log corpadm my_pass ksal_cube corpis bld_cube You can then call this secured file from the command line The following is a UNIX example cogtr c s mXyzsales mdl cat Sal_id txt v Add MDL statements to the end of the mdl model file that embed the user ID and password information and update the signon information needed to log on to the database Then run cogtr on UNIX using the m option specifying...

Page 243: ...l when using the command line you do not need to specify the l option because Cognos Transformer will authenticate against all existing signons by default m option This option specifies the mdl format model or script file to be processed If you use multiple occurrences of m files are processed in the order of their occurrence The syntax for using the m option is as follows cogtr mfilename mdl The ...

Page 244: ...splay of the Cognos Transformer splash screen The syntax for using the nologo option is as follows cogtr nologo pfilename py m filename mdl The following example opens the model Roofing mdl without first showing the splash screen cogtr nologo mRoofing mdl o option On Windows this option regenerates the categories after a model load but disables cube creation On UNIX and Linux it loads the model fi...

Page 245: ...n of Cognos Transformer The syntax for using the p option is as follows cogtr pfilename py The following Windows example opens the checkpoint file associated with model file Sales pyj then continues to process the model in batch mode creating all defined cubes cogtr n pSales pyj The following Windows example builds the cube and saves changes in the Model pyj file cogtr n s Model pyj The following ...

Page 246: ...ers for s are optional The following Windows example opens the model file Roofing mdl creates its defined cubes saves the model file and closes Cognos Transformer cogtr n s mRoofing mdl The following UNIX example starts Cognos Transformer parses a text model file mdl and saves the changes in a binary model file py cogtr mgo_sales mdl sgo_sales pyj t option This option sets the current period for t...

Page 247: ...s cogtr u powercube_name powercube_group_name child_cube_name p filename py mfilename mdl The powercube_name parameter represents the name of the cube in the Cognos Transformer model If powercube_name specifies a cube group that is not a time based partitioned cube all child cubes of the cube group are copied to the deployment location and activated If powercube_name specifies a time based partiti...

Page 248: ...ns in batch mode updates scales of columns defined in the model and saves it back to the model cogtr n x o s kfield CarlosR pw462 mField3 mdl y options This option specifies how IBM Cognos Series 7 user class security conversion is performed You can use the y options alone to save changes to the model file or you can use it in combination with other options The y options correspond to the three se...

Page 249: ...th the model but not the user classes This option allows you to maintain the view operations applied in the IBM Cognos Series 7 user class views but not use a Series 7 namespace with the custom views or if you do not intend to expose Series 7 as an a available namespace configured in IBM Cognos BI The following example v runs cogtr exe in batch mode v does not generate cubes or update categories v...

Page 250: ...CustColor10 0 CustColor11 0 CustColor12 0 CustColor13 0 CustColor14 0 CustColor15 0 CubeSaveDirectory logging_frequency 1 DataWorkDirectory ModelWorkDirectory WindowsSortRule 0 WindowsDateFormat 0 ChildRatioThreshold 35 rowCheckMax 600 sampleMax 300 DetachDataSource 0 LunarFiscalLabeling 0 AutoAutoDesign 1 DlgTitle 3 MaxTransactionNum 50000 EnableDrillThrough 0 LogDetailLevel 4 LogFileDirectory Lo...

Page 251: ...s an example of a transformer rc file LogFileDirectory logs ModelSaveDirectory tmp DataSourceDirectory tmp CubeSaveDirectory tmp DataWorkDirectory tmp ModelWorkDirectory tmp MaxTransactionNum 500000 LogDetailLevel 0 UseTransDAPipe 0 LogFileName LogFileAppend FALSE LoggingFrequency 1 WindowsDateFormat 0 MdcDebugOn DatDebugOn 0 WorkCountOn 0 DumpCSVPath ChildRatioThreshold 35 DetachDataSource TRUE F...

Page 252: ...EnablePCOptimizer TRUE TransdaPath TransdabPath CenturyBreak 20 KeepDataFiles 1 LoaderInterval LoaderTimeOut LoaderFileSize MultiFileCubeThreshold 0 HaltOnSecurityError FALSE 238 IBM Cognos Transformer Version 10 1 1 User Guide ...

Page 253: ...m the index Known Issues When Modeling in IBM Cognos Transformer Documented in this section are known issues and limitations when modeling in IBM Cognos Transformer BAPI Error Occurs After the Prompt Specification File Edited Manually In Transformer you create a prompt specification for a SAP based package You edit the prompt specification file prompt xml manually and save your changes After editi...

Page 254: ... open an IQD created in Framework Manager that contains an Oracle stored procedure you may receive a message similar to the following TR0118 Transformer can t read the database datasource defined in Lan location datasource iqd_name iqd DMS E_General A general exception has occurred during operation execute The native SQL generated in an IQD created in Framework Manager is wrong The IQD cannot be u...

Page 255: ...oid this problem add the locale string that is displayed in the Transformer File Session information to the Framework Manager parameter list so that Transformer can retrieve the expected data when accessing the data source However the model metadata will still be shown in English or in the Framework Manager design language Regular Columns Cannot Be Converted to Calculated Columns and Vice Versa Wh...

Page 256: ...e a TR2317 error when you create a PowerCube To avoid this error before generating categories for the dimension do the following steps Procedure 1 In the Dimension Map right click the lowest level in the ragged unbalanced hierarchy that is marked unique 2 Click Properties and on the Source tab click Move 3 From the Run menu click Generate Categories 4 From the Run menu click Create PowerCubes Rebu...

Page 257: ...del no longer exists or the data used to build the PowerCube is no longer available It is not necessary to run this command line utility for cubes created in Transformer version 8 x and later versions Procedure 1 Back up your target PowerCube then navigate to the c10_location bin directory 2 On Windows open a command line window and run PCOptimizer exe 3 On UNIX Linux enter the following line to r...

Page 258: ...ocales other than your running locale Also you cannot convert to a different currency while working in Analysis Studio 3 Define a default currency by setting the fallbackCurrency parameter in the installation_location configuration qfs_config xml file to GBP Great Britain Pounds or to an alternative currency code as listed in the installation_location bin ccli18nrescr_xx xml file Here is an exampl...

Page 259: ...ssing members may appear or disappear when grouped list reports are pivoted to a crosstab This happens with set expressions using the filter function and detail filters on members v Ragged and unbalanced sections of the hierarchy are suppressed when set expressions in that hierarchy are used on an edge v When a crosstab is sectioned or is split into a master detail report sections corresponding to...

Page 260: ... the case with the English version of the sample Sales and Marketing PowerCube you cannot publish the PowerCube Procedure 1 Right click the PowerCube and click Properties 2 On the Data Source tab in the Windows location property ensure that the data source location specified for the PowerCube is entered correctly For example the location for the English version of the sample sales_and_marketing md...

Page 261: ... is unable to locate or you have not referenced the user This error occurs when a script that uses Model Definition Language MDL requires an object that Cognos Transformer cannot find In this case the object is the ID for an authenticated user CAMID The problem may be that the object was not referenced or that it was referenced with an incorrect object name or object identifier Procedure You can v...

Page 262: ...viewing all of a dimension for which a custom view is applied in an ancestor custom view Procedure 1 Verify that the MDL script is correct and that you have specified the correct object identifier for each of the objects referenced 2 If descendants of the protected ancestor in the custom view do require access to the entire dimension you can v change the properties of the ancestor custom view so t...

Page 263: ...ed adding users to it as required to meet your reporting needs For more information see Chapter 7 Adding Security on page 147 TR0109 Cognos Transformer could not read the data source You have specified an invalid data source or a file format that is not appropriate for the type of data source defined in your model For example this error may occur if you are using an iqd file as a data source with ...

Page 264: ...nsufficient memory for Cognos Transformer to perform some operation Solution for Windows Procedure Close as many open applications as possible If this fails to correct the problem save your model exit and then restart Cognos Transformer You may need to restart Windows If this is a recurring problem you may need to augment computer memory in order to continue working on the model or PowerCube Solut...

Page 265: ...ormer cannot open the file file_name Procedure To resolve the problem ensure that the path and file name are correctly specified in the Directories tab of the Preferences property sheet If this error occurs when you are running an MDL script from the command line the problem may be that the script contains the command SavePY and you are also using the s command line option to save the file in py f...

Page 266: ...nt for command line option k is invalid When trying to pass database login information to Cognos Transformer using the k command line option an invalid entry has been detected Procedure To resolve the problem ensure that you enter a valid value for the k option For more information see Appendix A Command Line Options on page 209 TR0132 The database name in the command line is blank When trying to ...

Page 267: ...aned category to one of the others in the target level assuming you know which one they belong to v Determine what data is missing in your original data source and either supply that data or add a new source with the information needed to complete the path from a category in the target level to the orphaned categories If a key orphanage is created and later deleted during a single cube generation ...

Page 268: ...some other text field This action was taken to avoid syntax errors in MDL When Cognos Transformer generates MDL it uses double quotes to define the model This message informs you that double quotes in the source data have been changed to single quotes so that Cognos Transformer can distinguish between the source data and the structure of the model For more information about using Model Definition ...

Page 269: ... If you want cube records to be consolidated ensure that the cube does not contain calculated measures with a timing of Before Rollup TR0209 Sorting and consolidation for the PowerCube cube_name and n others must be performed to determine correct state measure values This message informs you that in spite of any contradictory settings for consolidation in the PowerCube property sheet one of the fo...

Page 270: ...lds which Cognos Transformer does not support Procedure If your source file is an iqd file and you have IBM Cognos Impromptu installed rerun the report in Impromptu and ensure that data is being returned TR0215 The PowerCube cube_name and n others may require consolidation Consolidation can t be performed because of before rollup calculations or because a cube is designated as using Direct Create ...

Page 271: ...tion applies to a level or Constant if it applies to an entire dimension TR0404 You didn t specify a server data source Do you want to continue This error occurs if you try to generate categories on a UNIX or Linux server but have not specified a data source that the server can access Procedure To resolve the problem specify a data source that resides on or is accessible by your UNIX Linux compute...

Page 272: ...bject identifier check boxes on the Titles tab of the Preferences property sheet The object name and identifier will appear in the title bar at the top of each property sheet and also in a tool tip when your pointer hovers over an object You can also find object identifiers in the mdl file For more information see the Cognos Transformer Developer Guide TR0423 A calculation definition was expected ...

Page 273: ...d identifier will appear in the title bar at the top of each property sheet and also in a tool tip when your pointer hovers over an object You can also find object identifiers in the mdl file For more information see the Cognos Transformer Developer Guide TR0500 You cannot move a category outside its drill down path On the diagram for a dimension that has an alternate drill down structure you have...

Page 274: ... category in a source level You can do this from the Source tab in the Level property sheet You may have inadvertently deleted the source value associated with a category Source values are required for categories in all source levels Categories in manual levels derive their source values from their category codes Procedure To resolve the problem edit the source value on the Category property sheet...

Page 275: ...e names Burlington MA and Burlington VT TR0507 The share reference category is not a category in the same dimension You have tried to apply a share using a level or category that is in another dimension Procedure To avoid this problem when you set up a share make sure you set it to a level or category in the same dimension as the categories to which you apply the share TR0508 You cannot move a cat...

Page 276: ...mension TR0515 In a regular diagram you can only connect to categories in a level above or below the original level You cannot connect to categories in the same level You have tried to connect two categories in the same level Procedure Ensure that you only connect a category to an ancestor or descendant category If the category that you want to connect to is at the wrong level restructure your mod...

Page 277: ...ories for which an OrderBy column is specified v Choose another OrderBy column for the level and have Cognos Transformer re order the categories based on this new OrderBy column v Disable the current OrderBy settings by choosing the blank entry at the bottom of the SortBy Column list on the OrderBy tab of the Level property sheet Tip You can order the categories manually by moving them on the diag...

Page 278: ... format MMDD unless specified otherwise in the Date tab of the Column property sheet This error can occur if the source value for a date category was inadvertently changed or deleted Procedure To resolve the problem ensure that the Source value on the General tab of the Category property sheet uses the same date format as that specified on the Time tab of the Column property sheet TR0534 The drill...

Page 279: ...rs specified then change their partition numbers to zero 0 Tip If you are unsure which categories in the alternate drill down path are partitioned an easy way to isolate categories with partition numbers other than 0is to save the model as an mdl file You can then open the mdl file using any text editor and search for the string NewPartition to locate all levels or categories with partition level ...

Page 280: ...inition Language MDL script that tries to exclude a drill category The Cognos Transformer Windows interface does not allow you to exclude a drill category Procedure To avoid this problem when you reference an object in MDL make sure you use the correct object identifier or object name You can verify Cognos Transformer object names and identifiers on the Windows interface To make them visible selec...

Page 281: ...ou can also find object identifiers in the mdl file For more information see the Cognos Transformer Developer Guide TR0606 The specifications for the date format and the level of detail are inconsistent Confirm these specifications in the Column property sheet You have specified conflicting properties for the Date input format and the Degree of detail on the Time tab of the Column property sheet P...

Page 282: ... name or object identifier that Cognos Transformer is unable to locate or you have not referenced the association This error occurs when an MDL script requires an object and is unable to find it In this case the object is an association The problem may be that the association was not referenced or that it was referenced with an incorrect object name or object identifier Procedure You can verify Co...

Page 283: ...match those in the data source and flags any mismatches in the Modify Columns dialog box For more information about the Modify Columns feature see Synchronizing Columns with Your Data Source on page 37 Matching Model and Source Columns for Text Files or IQDs As the modeler you must ensure that the columns in your model reflect the current state of your data sources Procedure 1 In the Data Sources ...

Page 284: ...ems to columns select the columns in the model and click Auto Match 6 If Cognos Transformer provides one or more locations in the data source that may be appropriate for unmatched columns do one of the following v If one of the locations is an appropriate match select the location and click Next or Finish v If none of the locations are an appropriate match click Match by reference instead and clic...

Page 285: ...curs when MDL syntax requires an object and is unable to find it In this case the object is a PowerCube The problem may be that the PowerCube was not referenced or that it was referenced with an incorrect object name or object identifier Procedure You can verify Cognos Transformer object names and identifiers on the Windows interface To make them visible select the Object name and Object identifie...

Page 286: ... the existing target categories Moreover no new data exists in data source B to provide a new target category As a result Cognos Transformer places the orphaned category under a key orphanage starting at the target level and a cube for that key orphanage is generated v Data source C is processed and new data is provided that places the orphaned category under one of the existing target categories ...

Page 287: ...Date Rate and one or both of Label and Country Code TR0751 Currency table Rate column not found The Rate column you specified does not exist or you did not specify a Rate column Procedure To resolve the problem make sure Cognos Transformer can access the source files needed to populate the currency columns for Date Rate and one or both of Label and Country Code TR0752 The currency table Label or C...

Page 288: ...rom the Data Sources list and inadvertently create a new level in the time dimension To resolve the problem specify a date function for the level on the Time tab of the Level property sheet TR0802 Levels in this time dimension are not in their natural order year quarter month day Please re order the levels An operation such as the following has been performed on date levels in the time dimension v...

Page 289: ...tom time period with a leaf level that includes minutes or seconds However if you suspect that you have a runaway process discontinue category generation and verify that your time dimension is properly defined by checking the Time tab settings on the Dimension Drill Category and Level property sheets TR0804 The earliest date is invalid You have specified an invalid Earliest date on the Time tab of...

Page 290: ...ion property sheet Procedure Ensure that the date format you have specified is a valid one TR0809 The current period cannot be a special category You have tried to set the current time period to the value represented by a special category Procedure Use a regular category rather than a special category to set the current period TR0810 The date levels specified for this dimension are not allowed Ens...

Page 291: ... properties coincide For example for a drill down path that uses lunar time periods if you set the Year begins property to 20070101 ensure that the Week begins on property is set to Monday because January 01 2007 occurred on a Monday TR0812 level_name is not a valid convergence level in this time dimension The drill down paths leading to the convergence level do not share the same set of date cate...

Page 292: ...rrect object name or object identifier Procedure You can verify Cognos Transformer object names and identifiers on the Windows interface To make them visible select the Object name and Object identifier check boxes on the Titles tab of the Preferences property sheet The object name and identifier will appear in the title bar at the top of each property sheet and also in a tool tip when your pointe...

Page 293: ...06 The associated column that is specified for dimension dimension_name cannot be found in the Data Sources list Cognos Transformer is unable to find a column to match the one specified as the column associated with a regular time dimension This can happen when v the source file for a data source has changed v you delete or change the name of a source column v you change the column name specified ...

Page 294: ... model so that the calendar and lunar year drill down paths converge as required at the Day level TR0917 You have tried to create a Regular time dimension without providing the name of a column that provides date values for the categories in the dimension Please specify a column for a regular time dimension Procedure On the General tab of the Dimension property sheet specify the name of a data sou...

Page 295: ...eyword in the MDL script Procedure Check that all the keywords are spelled correctly on the specified line For more information about supported keywords see the Cognos Transformer Developer Guide TR1101 Cognos Transformer detected a syntax error at line n in file file_name There is an error in the MDL This message is generally followed by one or more additional error messages which may be further ...

Page 296: ...erent object identifier Object identifiers can be any number greater than 100 and less than 4 294 967 296 TR1109 The import file file_name contains a string on line n which contains a new line character This error indicates that there is a newline character on the specified line in your source file Procedure First check that there is no newline character on the specified line Then try saving the m...

Page 297: ... settings so that both measures use the same type of rollup For example you cannot specify a regular timing of After Rollup for a calculated measure M1 if M1 is referenced in the expression for calculated measure M2 and the timing for M2 is Before Rollup TR1310 A measure was expected but not found Either you have referred to a measure by an object name or object identifier that Cognos Transformer ...

Page 298: ... data sources that contain source values for the Revenue measure The data sources are not related in any other way Both data sources have columns that are used as source values for levels in different dimensions Cognos Transformer cannot perform rollups correctly because it does not know how to associate measure values with specific categories Procedure To resolve the problem we recommend that you...

Page 299: ...e Windows interface To make them visible select the Object name and Object identifier check boxes on the Titles tab of the Preferences property sheet The object name and identifier will appear in the title bar at the top of each property sheet and also in a tool tip when your pointer hovers over an object You can also find object identifiers in the mdl file For more information see the Cognos Tran...

Page 300: ...ermediate levels for Quarters and Months Tip You can change the warning level by changing the default Warn when the number of child categories exceeds setting located on the General tab of the Preferences property sheet The default value is 35 TR1703 A data input conversion or overflow error occurred at source record number n for measure measure_name in source file file_name This error may be caus...

Page 301: ...backup copy of the model and its PowerCubes saved prior to processing the most recent increment and retry When you see a second warning indicating that the time stamps on the PowerCube and model do not match proceed with the update accepting the risk that you might thereby duplicate some of the cube data v Adopt the existing cube and rerun the incremental update having first confirmed that if the ...

Page 302: ...02 This dimension cannot be omitted for this custom view because it is the main dimension for time based partitioned PowerCube cube_name and the custom view has been added to that cube For a custom security view in a time based partitioned cube you have tried to omit the dimension containing the level that defines the time based partitioned cube Procedure You cannot omit this dimension If you want...

Page 303: ...nterface set the dimension type on the General tab of the Dimension property sheet to Time In MDL include the option DimType Date in the Dimension definition statement 2 Alternatively remove the date functions entirely Note On the Windows interface you may need to temporarily select the Time dimension type on the General tab of the Dimension property sheet so you can open the Time tab and delete t...

Page 304: ...ns so that Cognos Transformer no longer verifies that unique levels contain unique categories v you use multiple data sources in a model and do not define levels associated with columns from multiple data sources as unique v you create an alternate drill down structure that requires a unique convergence level and the uniqueness property is not set v you specify that a level is unique and Cognos Tr...

Page 305: ... help for a detailed explanation of level uniqueness This error indicates that you have a problem related to level uniqueness This can occur when v you disable some error checking options so that Cognos Transformer no longer verifies that unique levels contain unique categories v you use multiple data sources in a model and do not define levels associated with columns from multiple data sources as...

Page 306: ...re ordering categories in the time dimension decide which aspect of the model is most important the order of categories within the time dimension or the relative time categories that are included within the time dimension TR2321 A share object level must be an ancestor of a share target level You have tried to apply a share to one or more categories in a level when the share that is currently set ...

Page 307: ... to an MDL script that attempts to specify a partition number for a level in an alternate drill down path The Windows interface does not normally allow you to specify partition level numbers in levels other than those in the primary drill down structure Procedure To resolve the problem verify that your partitioning is specified only for levels in the primary drill down path If partitioning is requ...

Page 308: ...title bar at the top of each property sheet and also in a tool tip when your pointer hovers over an object You can also find object identifiers in the mdl file For more information see the Cognos Transformer Developer Guide TR2503 Special categories cannot be summarized in dimension views This error typically appears in the log file in response to an action requested by an MDL script Under normal ...

Page 309: ...former Developer Guide TR2506 An apex action cannot be done with special categories This error typically appears in the log file in response to an action requested by an MDL script Under normal circumstances the Windows interface does not allow you to perform exclude summarize or cloak actions on special categories in a dimension view Procedure If you encounter this error ensure that the category ...

Page 310: ...es TR2509 The position of the pre defined views All Categories and Omit Dimension cannot be changed You have attempted to move one of the following predefined dimension views v All Categories v Omit Dimension These default dimension views are created for every dimension and are used to include or exclude entire dimensions from a PowerCube Procedure To avoid this problem do not delete reorder or re...

Page 311: ...e same name already exists verify also that the existing py file you are attempting to overwrite is not set to read only TR2606 Cognos Transformer has detected one or more suspended models Show the list of suspended models Cognos Transformer has detected qy files that contain checkpoints indicating there are one or more suspended models You can view a list of these suspended models so you can try ...

Page 312: ...file no longer match those in the data source and flags any model columns that have changed on the Windows interface To resolve the problem do the following Procedure 1 Select the affected data source in the Data Sources list and from the Tools menu click Modify Columns 2 For each changed column click Match Add or Remove to make the columns in the model match the columns in the data source TR2701 ...

Page 313: ...he data source This error appears on category generation if a data source column cannot be associated with some level in the model Procedure You can solve the problem in the following ways v If the level you want to associate with the column contains only unique category values select the Unique check box on the Source tab of the Level property sheet This allows Cognos Transformer to directly asso...

Page 314: ...ing click the Measures tab and ensure that you have included at least one measure TR2706 Level level_name can t be reached in drill down path drill_category_name for data source data_source_name A level must either be the first source level be directly below a source level or be designated as a unique level All measures in data source data_source_name will have zero values in the PowerCube The lev...

Page 315: ...e source data for your model is structured so that all measures used in the Before Rollup calculated measure are contained in the same data file TR2710 Level level_name has an allocation specified for measure measure_name1 using measure measure_name2 The referenced measure must include a lower level in its measure scope than the measure being allocated A measure that you have used to allocate anot...

Page 316: ... measure_name1 This error occurs because you have inadvertently specified an invalid circular allocation Measures cannot be allocated by their own values or by other measures that are allocated by those values For example if you allocate Measure A using values for Measure B you cannot then allocate another measure Measure C using values for Measure B if Measure C is already allocated using Measure...

Page 317: ...data can be reported against it in the PowerCube A category is accessible if it or a descendant exists in a key level or it has an ancestor with allocated measure values This message warns you that the dimension you have created contains one or more categories that cannot be accessed This situation can occur for one of the following reasons v There is no Current Period category v You have created ...

Page 318: ...urce that provides category values for the dimension v one or more transactional data sources that contain one or more columns with measure values v at least one column associated with a unique level in the dimension TR2719 Data source data_source_name contains no measures It is referred to as a structural data source and should come before all measure data sources in the Data Sources list Cognos ...

Page 319: ...hen you specify partition level numbers across different dimensions You must avoid setting level 1 for categories in one dimension and level 3 for categories in another dimension without setting level 2 somewhere else TR2724 Partition n is defined in dimensions dimension_name1 and dimension_name2 A partition number cannot be assigned in more than one dimension You have tried to specify partition l...

Page 320: ...Month level You cannot assign partition level 3 to categories in the Quarters level Procedure To avoid this problem when you set up partition levels ensure that you start numbering at 1 for the highest levels 2 for child categories of that level 3 for their descendant child categories and so on TR2727 Measure measure_name1 is an allocated measure which is referenced by the Before Rollup calculated...

Page 321: ...mension and reset the Degree of detail property on the Time tab TR2731 Cube group cube_group_name is not defined in the primary drill down path in dimension dimension_name A cube group must be specified in the primary drill down of a dimension that has alternate drill downs You have tried to define cube groups based on a target level that exists in an alternate drill down path Cube groups can exis...

Page 322: ...he Dimensions tab of the PowerCube property sheet include at least one dimension with which the named data source is associated TR2734 All cubes have been disabled PowerCube mdc file generation is not possible All of the PowerCubes in the model are disabled No cube mdc file can be created Procedure To resolve the problem open the PowerCube Status dialog box from the Tools menu and enable at least ...

Page 323: ...cause Cognos Transformer analyzes one data source at a time both measures must exist in the same data source Procedure To avoid this problem ensure that measures used to weight other measures exist in the same data source TR2740 Measure measure_name has a time state rollup specified There is no time dimension in this model so the time state rollup setting will have no effect This message may indic...

Page 324: ...espace is not the same Access Manager namespace that the IBM Cognos Series 7 model was designed with Procedure In IBM Cognos use the Series 7 namespace that you used to create the IBM Cognos Series 7 user class views TR3311 An error occurred during data retrieval from the database If available refer to the Details for more information You may get this error message when you try to create a PowerCu...

Page 325: ...ires the first parameter to be a child category of the second parameter Reversing the order of the child and parent categories within this function causes an error in the predefined syntax Procedure For more information about the syntax of supported Cognos Transformer functions see Functions on page 357 Appendix C IBM Cognos Transformer Error Messages 311 ...

Page 326: ...312 IBM Cognos Transformer Version 10 1 1 User Guide ...

Page 327: ... Page From the Welcome page you can create a new model or open and edit an existing model The Welcome page also keeps a list of the last four models that you opened or created with the most recent model listed first If you try to open a model that has been deleted or renamed you receive an error message The list of recently used models is stored in the cogtr xml file which is located in the instal...

Page 328: ... when you first create your model For all data sources except IBM Cognos package and report data sources you can drag columns from the Data Sources list to the Measures list to define the measures for your model If you attempt to add a measure that has the same name as an existing measure you will receive an error message indicating that another measure already has the same name Each measure in a ...

Page 329: ...reports For more information see Chapter 7 Adding Security on page 147 Data Source Viewer and SQL Tab The Data Source Viewer shows sample data from your data source When the data source is an IQD data source or a query based on an IBM Cognos package or report the SQL statement for your data is also shown The SQL tab provides a preview of the data in Structured Query Language SQL form for the selec...

Page 330: ...r a selected dimension or level to set its available options v add levels manually so that you can more accurately allocate key performance measures v lock levels or entire dimensions so that new data does not disrupt your primary structure v show category counts for each level in every dimension v show the scope for selected data sources or measures v establish associations between levels and mea...

Page 331: ...t will be dropped The boundaries of the box clearly indicate whether the dropped level appears above under or between two existing levels as shown Scope Maps If your model uses multiple data sources or contains measures that have meaning for some but not all the categories in each dimension you can check the relationships between various levels in the dimension map using the Show Scope command on ...

Page 332: ... category can have the same name in the level By default this level appears with red shading You can choose to show the scope for either a selected data source or measure Levels are a specific color depending on their scope and derivation However you can change the default color coding for these states on the Dimension Map tab of the Preferences property sheet Tip You can perform the same actions ...

Page 333: ...suppress a level v define a set of levels or categories and apply one or more operations or calculations to them The category viewer icons indicate which properties apply to the listed categories The only exception is apexed categories There is a toolbar icon for apexing but no viewer icon for it The icon for a calculation applied at the dimension level is slightly different from that used for a c...

Page 334: ...ing categories 4 Set the properties as required for your newly connected descendant category Allocation Types The following table lists the allocation settings that you can specify and provides a description of each type For more information about allocations see Allocating Measures on page 109 Allocation setting Description From Level Category property sheet only Uses the setting for the level in...

Page 335: ...wn path Suppress The Suppress action eliminates categories maintaining links from the parent to the child categories The Cognos Transformer diagram shows categories Report users bypass the suppressed category when they drill down You can apply the Suppress action at the dimension level of the category viewer and in a dimension view but not in a custom view You cannot suppress root categories In di...

Page 336: ...evel property sheet The setting then applies to all categories subsequently created in that level The following table lists the available Inclusion settings and provides a description of each Tip You may not see all the settings listed in Cognos Transformer The settings that are available depend on the item you selected level category drill category or special category Inclusion setting Descriptio...

Page 337: ... descendants and all related data from the cube The result is identical to using the Exclude option on the Diagram menu For a category to be excluded the following conditions must be true The category is not the share category of another The category is not referenced by a special category Date Formats and Functions You set date formats and functions on the General tab of the Preferences dialog bo...

Page 338: ...mple 130407 or 13 Apr 2007 MDY Month Day Year for example 041307 or Apr 13 2007 YM Year Month for example 200704 or 2007 Apr This setting is processed as if the day component were 01 MY Month Year for example 042007 This setting is processed as if the day component were 01 Y Year for example 2007 This setting is processed as if the month and day components were 01 To construct dates consisting of ...

Page 339: ... 3 month patterns containing four weeks five weeks and four weeks 5 4 4 week pattern Based on repeating 3 month patterns containing five weeks four weeks and four weeks Week Based on weeks in the format YYYY MMM DD or YY MMM DD When using this function you must specify how the weeks split over month boundaries and the day that marks the start of each week using the Time tab of the Drill Category p...

Page 340: ...ry files location as specified in IBM Cognos Configuration Model Temporary File Temporary files location as specified in IBM Cognos Configuration Logs IBM Cognos logs directory The logs directory is relative to the bin directory cogtr xml File Settings The Cognos Transformer global preference and processing settings can be specified in the cogtr xml file or one of the other files that are referenc...

Page 341: ...ans that multifile cube generation is disabled To test or use the multifile feature on smaller cubes we recommend that you specify a smaller number for this setting such as 1000000 Cognos Transformer uses this setting to determine the number of output mdp multidimensional partition files that are needed to build the multifile cube For more information see the section Automatic Creation of Multifil...

Page 342: ...thout deleting the special category and its existing substructures Current Period The current time period For example if the current date is 2007 05 30 then the Current Month is 200705 Period To Date The period to date For example if the current date is 2007 05 30 the Quarter To Date category is a specially constructed version of 2006 Q2 that contains only the months of April and May assuming a st...

Page 343: ...arter category is a specially constructed version of 2007 Q1 that contains only the first and second months January and February of that quarter Custom A relative time period that you specify by changing the predefined category details For more information see Example Creating Custom Relative Time Periods Cognos Transformer supports the following mechanisms for setting the current period v automat...

Page 344: ...0 4 You need a time period that spans the quarters 2006 Q1 2006 Q2 2006 Q3 and the months 2006 10 and 2006 11 This is a Year to Date category two quarters ago up to the same month in that quarter as the current month is in its quarter the second month You specify the following details v Current Period 2007 05 30 v Basic Approach Period To Date Total v To date Period Year v Target Period Month v Ta...

Page 345: ...el to the cube before the data sources are processed Records that do not generate new categories are then directly updated to the cube This optimization method is best used with models that are expected to generate few new categories and where all categories are expected to be added to the cube Note This setting is not available for individual cubes in a cube group Rollup Functions The rollup func...

Page 346: ...tegories of the current category Count All Shows the number of records including those containing null values for this measure for all descendant categories of the current category Any Shows the value 1 if any records for a descendant category contain values Shows the value 0 if no records exist for this measure or all records that do exist have null values for this measure External Shows source v...

Page 347: ... level the Current Period option reports the measure value for April 2007 At the quarter level the option reports the measure value for April in Quarter 2 because April is the current period but it shows the value of the last active month in every other quarter that is March for Quarter 1 September for Quarter 3 and December for Quarter 4 Average Shows the average of the measure values from all ca...

Page 348: ...tains both single and double quotation marks Cognos Transformer changes the quotation marks to avoid ambiguity All quotation marks in the original text are converted to single quotation marks and the entire string is enclosed in double quotation marks For example in MDL syntax New York becomes New York and Other s No Name Brands becomes Other s No name Brands Similarly London and London prime beco...

Page 349: ...mple above When it is the first character in the brackets it also searches on itself and everything else in the brackets In the example Apr 07 finds both Apr 07 and Apr 07 In this case the hyphen is not used to define a range but as a character that will be searched on p iu ck finds pick and puck a d ay finds bay and day but not may m u st finds mist but not must m a o st finds must but not most A...

Page 350: ...reat Outdoors Sales mdl Cognos Transformer model based on the Sales query package GO Sales query in IBM Cognos Connection installation_location webcontent samples models Transformer Sample PowerCubes Cognos Transformer includes several sample PowerCubes Model name Model Location sales and marketing mdc Sales and Marketing mdl installation_location webcontent samples models Transformer employee exp...

Page 351: ...t IBM Cognos Connection in the GO Data Warehouse query Report Studio Report Samples folder Overtime List report IBM Cognos Connection in the GO Data Warehouse query Report Studio Report Samples folder Pension Plan List report IBM Cognos Connection in the GO Data Warehouse query Report Studio Report Samples folder Regular Salary List report IBM Cognos Connection in the GO Data Warehouse query Repor...

Page 352: ...338 IBM Cognos Transformer Version 10 1 1 User Guide ...

Page 353: ...ansformer can use the CSV files as a data source to create a model and generate PowerCubes This method should only be used in a IBM Cognos environment when you want to leverage data in IBM Cognos BI to build PowerCubes for IBM Cognos BI In Transformer version 8 3 use published packages for dimensions and use CSV files for facts There are special considerations when using externalized CSV files wit...

Page 354: ... on the right hand side of the page For example Columns or Rows The characteristics you select will define the metadata in the Cognos Transformer cube The characteristics must adhere to the following restrictions v You must have at least a single optional variable to segment the data v Select a characteristic that is representative of the data source The characteristics can be either key figures w...

Page 355: ...recommend that you use the SAP BW naming convention in the Technical Name field That is begin the entry with the letter Z followed by an intuitive name or your standard naming convention It is important to write down this technical name as you will need it to find the query in Framework Manager Results You are now ready to create a variable Create a Variable For more information on using the SAP Q...

Page 356: ...ent dimension which may contain 5 distinct members and see how it compares No formula can be applied as no two environments are alike However a cautious approach is recommended to avoid disrupting your SAP BW environment Procedure 1 In Query Designer right click a characteristic that you have selected in the previous procedure and select Restrict To ensure that data is distributed evenly select a ...

Page 357: ...nfiguration as a time dimension is a global entry every imported dimension will then be treated as time strings v Create a package Create a Package on page 345 When creating the package for publishing to Content Manager hide the primary hierarchy in those dimensions where you imported two hierarchies The primary hierarchy is necessary and must be in the package for querying to work correctly You c...

Page 358: ...lect all the attributes here you can exclude unwanted query items when publishing the package 6 Complete the remaining screens in the Metadata Wizard accepting the default values and click Next This will generate dimensions and import the metadata 7 At the final wizard screen verify the results and click Finish Results You are now ready to create a model in Cognos Transformer For more information ...

Page 359: ... each of them individually Excluding parent objects also exclude all of its children Note that excluding or unselecting many objects from larger cubes will require a significant amount of time Note Framework Manager supports ctrl shift and alt shift functionality Use these keystrokes to select multiple objects that you wish to include or hide in the cube For example if you wish to only include two...

Page 360: ...rmissions for the package v To accept the default access permissions click Finish v To set the access permissions click Next 6 When you are prompted to open the Publish Package Wizard click Yes 346 IBM Cognos Transformer Version 10 1 1 User Guide ...

Page 361: ... the desired IBM Cognos component accessing the dimensional and fact data from the original SAP BW source In addition you can combine the SAP metadata in a Cognos Transformer model with other corporate metadata or personal sources provided you have the necessary matching key information to join the data during cube building When you create the Cognos Transformer model you must use the Insert Dimen...

Page 362: ...ata 8 If there are errors or warnings you are notified In the Data Sources pane expand the package to view the data source queries and query items Key figures or measures appear in the Measures pane Ensure that the aggregation rule for each measure is correctly defined within Cognos Transformer to align as closely as possible with the aggregation rule defined in SAP BW It is recommended that the s...

Page 363: ...sing Framework Manager packages published to Content Manager is the preferred method to leverage SAP BW data For general information about creating packages see Create or Modify a Package in the Framework Manager User Guide For SAP specific information about creating packages see Working with SAP BW Data Using a Package in Framework Manager Extract Size The Extract Size data source property within...

Page 364: ...ion about externalizing model query subjects see Framework Manager Considerations on page 352 Key Figures Dimensions from an SAP BW InfoCube When externalizing the key figures dimension from a model based on an InfoCube Framework Manager uses exactly the same approach as used for externalizing model query subjects For an InfoCube containing more than a few thousand transactions externalizing an In...

Page 365: ...If a characteristic is included in an SAP BW query as a free characteristic no values will appear for that characteristic in the key figures extract A filter on a free characteristic acts as a filter on the data returned by SAP BW It is an efficient mechanism for defining a subset of an InfoCube Such a filter may also be applied to a characteristic along an axis of an SAP BW query in which case th...

Page 366: ... defined either within the SAP BW query in BEx or in a model query subject in Framework Manager Each characteristic extracted must contain at least one query item from the lowest level of its hierarchy if there is one to provide linkage with the key figures extract You should include the business key query item from each of the levels of each dimension depending on the level of granularity that yo...

Page 367: ...f v the model is published to Content Manager v externalizing the data takes longer to perform than the timeout period assigned to passports within IBM Cognos Configuration Users are not prompted to re enter their authentication credentials If an error occurs the externalized data is still complete and valid However if the modeler chooses to actually publish the model the modeler must re authentic...

Page 368: ...hat the dates for the PowerCube can be derived entirely from the transaction data v Insert the various CSV files corresponding to the characteristics that were externalized using Framework Manager Each CSV file contains a column that corresponds to a column in the key figures CSV file By right clicking the various columns and editing the column properties ensure the columns that provide the linkag...

Page 369: ...nto the expression once the type of value is determined The expression editor is context sensitive so it presents only options that are relevant at each stage of expression building v operators such as Operators specify what happens to the values on either side of the operator There are four types mathematical operators perform mathematical operations on two parts of an expression for example stri...

Page 370: ...see Functions on page 357 For information about applying or working with calculations see the appropriate product documentation Procedure 1 From the left pane locate and select the component you want 2 Click the arrow button to add the component to the expression 3 Continue adding components until your expression is complete 4 Click OK Results Tips v To add components quickly to the expression dou...

Page 371: ...arenthesis and click OK If your expression is valid the definition is saved for that measure 3 If an error appears try again using a mathematically correct form Tip Unlike calculated columns calculated measures support isnull expressions that resolve to True which is the condition that arises with a missing value You can use this capability to avoid divide by zero overflow errors 4 Build the cube ...

Page 372: ...0 Returns the number of days since Jan 1 1900 inclusive The value returned is negative if the date is before 1900 Day Returns a numeric value for the day of the month from 1 to 31 from a date datetime value or interval Days to End of Month Returns the number of days to the last day of the month from a date or datetime value First of Month Returns the first day of the month from a date or datetime ...

Page 373: ...onths today 10 Returns the result 01 20 2007 00 00 add months today 10 Returns the result 05 20 2007 00 00 Add Years Returns a datetime value resulting from adding a number of years to a date Syntax add years date_exp datetime_exp integer_exp Examples add years today 10 Returns the result 03 20 2017 00 00 add years today 10 Returns the result 03 20 1997 00 00 Age Returns age as a month day year in...

Page 374: ...20 18 22 00 000 Returns the result 20 day 20 00 00 00 000 Returns the result 20 Days to End of Month Returns the number of days to the last day of the month from a date or datetime value Syntax days to end of month date_exp datetime_exp Example days to end of month 2007 03 20 Returns the result 11 First of Month Returns the first day of the month from a date or datetime value The datetime value is...

Page 375: ...tween two dates If the first date is later than the second date then the result is a negative number This function does not round months the days and time portions of the difference are ignored This function is processed only on an IQD data source Syntax months between date_exp_1 datetime_exp_1 date_exp2 datetime_exp2 Examples months between 2007 03 21 add months 2007 03 21 4 Returns the result 4 ...

Page 376: ... a number rounded to the next highest integer Floor Returns a number rounded to the next lowest integer Integer Divide Returns the integer obtained from truncating the result of an integer divided by a second integer Mod Returns the remainder modulus of an integer divided by a second integer Number to String Returns a string from a number Round Down Returns a number rounded down Round Near Returns...

Page 377: ...t 2 Floor Returns a number rounded to the next lowest integer Syntax floor numeric_exp Examples floor 1 23 Returns the result 2 floor 3 45 Returns the result 3 Integer Divide Returns the integer obtained from truncating the result of an integer divided by a second integer Syntax integer divide integer_exp1 integer_exp2 Examples integer divide 10 20 Returns the result 0 integer divide 20 6 Returns ...

Page 378: ...urns the result 12345 number to string 12345 678 Returns the result 12345 Round Down Returns a number rounded down The integer_exp value determines the position that is rounded A positive integer_exp acts on the digits to the right of the decimal point A negative integer_exp acts on the digits to the left of the decimal point An integer_exp value of zero rounds the number and removes the decimal p...

Page 379: ...removes the decimal places Syntax round near numeric_exp integer_exp Examples round near 113 6667 0 Returns the result 114 round near 113 6667 1 Returns the result 113 7 round near 113 6667 1 Returns the result 110 round near 113 6667 2 Returns the result 100 round near 366 2162 0 Returns the result 366 round near 366 2162 1 Returns the result 366 2 round near 366 2162 1 Returns the result 370 rou...

Page 380: ...1 Returns the result 370 round up 366 2162 2 Returns the result 400 Round Zero Returns a number rounded toward zero The integer_exp value determines the position that is rounded A positive integer_exp value acts on the digits to the right of the decimal point A negative integer_exp value acts on the digits to the left of the decimal point An integer_exp value of zero rounds the number and removes ...

Page 381: ...th character and numeric values Function Description Char_Length Returns the number of characters in a string First Word Returns the first word in a string Left Returns a specific number of characters starting at the left of the string Lower Converts uppercase characters to lowercase Position Returns the starting position of a string in a second string Reverse Reverses the characters in a string R...

Page 382: ... string Syntax char_length string_exp Examples char_length ABCDEFG Returns the result 7 char_length Returns the result 0 char_length Returns the result 1 First Word Returns the first word in a string Syntax first word string_exp Example first word Cat sat on the mat Returns the result Cat Left Returns a specific number of characters starting at the left of the string Syntax left string_exp integer...

Page 383: ... position Z ABCDEFGH Returns the result 0 Reverse Reverses the characters in a string Syntax reverse string_exp Example reverse ABCDEF Returns the result FEDCBA Right Returns a specific number of characters starting at the right of the string Syntax right string_exp integer_exp Example right ABCDEFG 3 Returns the result EFG String to Integer Returns the integer representation of string_exp Syntax ...

Page 384: ...ns a string with leading spaces removed For example if you merge two data items with leading spaces use trim leading to eliminate the spaces between them Syntax trim leading string_exp Example trim leading ABC Returns the result ABC Trim Trailing Returns a string with trailing spaces removed For example if you merge two data items in an expression and the data items have trailing spaces the spaces...

Page 385: ... data items Percent Returns the percent of the total value for selected data items Percent growth Returns the growth over a linear series of categories by calculating the percentage change between each pair of categories Share Returns the values of categories as a percent share of other target categories Average Returns the average value of selected data items Cognos Transformer supports the Avera...

Page 386: ...ge categories for Q1 Q2 Q2 Q3 and Q3 Q4 use the function change Quarter Max Returns the maximum value of selected data items Syntax max numeric_exp Example max Sales Returns the maximum value of all Sales values Min Returns the minimum value of selected data items Syntax min numeric_exp Example min Sales Returns the minimum value of all Sales values Percent Returns the percent of the total value f...

Page 387: ...xample A dimension contains a level with Quarters Q1 to Q4 To create growth categories for Q1 Q2 Q2 Q3 and Q3 Q4 use the function percent growth Quarter Share Returns the values of categories as a percent share of other target categories Syntax share cat_code cat_code set cat_code set set level Example A dimension contains the category Color with several descendant categories such as Black and Chr...

Page 388: ...ical Operator Returns True if either of the two conditions on both sides is true Not Logical Operator Returns True if the condition is false String Operator Concatenate two strings together Mathematical Operators Performs mathematical operations The precedence for processing mathematical operators is as follows v exponents v multiplication and division v addition and subtraction Mathematical opera...

Page 389: ... compare two values and produce the logical value True or False Operators Description Comparison Operator Symbols Compares two values Isnull Determines if a value is undefined in the data Comparison Operator Symbols Compare two values Syntax Value1 Value2 Example Qty 100 If the quantity is less than 100 evaluates to True and retrieves only those rows Price 1000 or Qty 3 If the price is greater tha...

Page 390: ...yntax abc isnull Example Telephone Number isnull If there are no telephone numbers evaluates to True Retrieves only the rows missing telephone numbers 376 IBM Cognos Transformer Version 10 1 1 User Guide ...

Page 391: ...iries in writing to Intellectual Property Licensing Legal and Intellectual Property Law IBM Japan Ltd 1623 14 Shimotsuruma Yamato shi Kanagawa 242 8502 Japan The following paragraph does not apply to the United Kingdom or any other country where such provisions are inconsistent with local law INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PUBLICATION AS IS WITHOUT WARRANTY OF ANY KIND E...

Page 392: ...l results may vary Users of this document should verify the applicable data for their specific environment Information concerning non IBM products was obtained from the suppliers of those products their published announcements or other publicly available sources IBM has not tested those products and cannot confirm the accuracy of performance compatibility or any other claims related to non IBM pro...

Page 393: ...e either registered trademarks or trademarks of Adobe Systems Incorporated in the United States and or other countries v Microsoft Windows Windows NT and the Windows logo are trademarks of Microsoft Corporation in the United States other countries or both v Linux is a registered trademark of Linus Torvalds in the United States other countries or both v UNIX is a registered trademark of The Open Gr...

Page 394: ...380 IBM Cognos Transformer Version 10 1 1 User Guide ...

Page 395: ...neral dimensional attributes do not have measure values or rollups associated with them but are used to locate or filter members C calculated member A member of a dimension whose measure values are not stored but are calculated at run time using an expression cardinality 1 For OLAP data sources the number of members in a hierarchy The cardinality property for a hierarchy is used to assign solve or...

Page 396: ...gnos BI a model is created and maintained in Framework Manager The model or a subset of the model must be published to the Cognos server as a package for users to create and run reports model segment A part of a Framework Manager project such as a parameter map a data source a namespace or a folder that is a shortcut to a second project Segments are used to simplify model maintenance or to facilit...

Page 397: ...data and form the set of available data for authoring reports in Query Studio and Report Studio A query subject is similar to a relational view in that it can be treated as a table but does not necessarily reflect the data storage U user Any individual organization process device program protocol or system that uses the services of a computing system Glossary 383 ...

Page 398: ...384 IBM Cognos Transformer Version 10 1 1 User Guide ...

Page 399: ...levels 64 calculated column converting to regular column 241 calculated columns how to define 59 using to create exception dimension example 60 using to resolve uniqueness problems 34 using to support allocated measures example 60 calculated measures defining 97 specifying rollup type and timing 100 supported mathematical functions 100 timing of rollup operations 98 tracking between different time...

Page 400: ...375 is null 376 symbols 375 components and 374 not 374 or 374 conformed dimensions combining or crossing data sources 21 connections specifying for IQD data sources 39 updating for PowerCubes 187 consolidating by using average duplicates rollup example 109 by using sum duplicates rollup example 108 data in cubes 178 measure data 107 time data in monthly or quarterly arrays 52 with duplicates rollu...

Page 401: ...ent dimensions example 155 union of multi dimensional apexed views example 154 union with an apex cloaked in the same dimension example 153 union with an apex excluded the same dimension example 153 union with an apex in different dimensions example 154 union with an apex in the same dimension example 152 custom views continued union with omitted dimensions example 156 unions 151 customizing cubes...

Page 402: ...line help 239 error messages in Cognos Transformer 247 errors calculations with double quotation marks 240 exclude category action 321 excluding categories from cubes 322 categories using a custom view 137 expression editor 355 expressions 355 building 356 calculated 357 If Then Else 357 supported mathematical functions 100 externalized queries removed features 2 externalizing characteristic dimen...

Page 403: ...80 UNIX procedure 201 InfoCube key figures externalizing 350 input scale defining column parameters 50 integer divide function 363 interface overview of Cognos Transformer version 8 x features 313 introduction Cognos 8 documentation set xiii invalid dates resolving 88 invalid operations 319 iqd data source troubleshooting Sybase connection error 240 IQD files specifying connection strings 39 speci...

Page 404: ...p type and timing 100 specifying time state rollup type 104 supported mathematical functions 100 suppressing allocation 115 time state rollup example 105 timing of rollup operations 98 member unique names relational metadata 22 resolving uniqueness problems 23 members 63 memory allocation adjusting to speed build times 204 metadata updating for cubes 144 metadata processing e command line option 2...

Page 405: ...onn 1 percent growth function supported for calculated expressions in Cognos Transformer 100 percent growth summary 373 percentage summary 372 performance improving in UNIX production environment 201 PowerCubes 199 period to date categories setting up relative time periods 328 placeholder categories using to define cube groups 186 plain text password avoiding 228 planning models optimized for OLAP...

Page 406: ...ile 167 publishing PowerCubes troubleshooting failure to publish 246 using pcactivate exe 189 Q queries adding to existing data sources 36 queries continued importing 36 removing obsolete prompts 31 quotation marks MDL syntax conventions 334 R ragged hierarchies Report Studio 245 range prompt 224 ranges specifying for spreadsheet sources 41 read operations controlling when sources are read 47 reco...

Page 407: ...ed packages 17 Show Counts command using to troubleshoot design problems 9 Show References command using to troubleshoot design problems 9 Show Scope command 317 checking measure allocation scope 111 using to troubleshoot model design problems 9 signon creating a Cognos 8 signon 55 creating a data source signon 55 signon processing k command line option 227 simple prompt 224 sorting 18 source leve...

Page 408: ...d to period changes 81 time based partitioning adding new data considerations 129 cube customization process 131 optimal cube designs 125 slowly changing dimensions 129 troubleshooting processing issues 128 time state rollups example 105 ignoring null or missing values 105 specifying measure rollup functions 104 supported functions 331 timing controlling when sources are read 47 tips modeling best...

Page 409: ...07 289 TR2308 289 TR2312 289 TR2313 290 TR2314 290 TR2316 290 TR2317 291 TR2318 291 TR2319 292 TR2320 292 TR2321 292 TR2322 292 TR2323 293 TR2324 293 TR2325 293 TR2326 294 TR2371 307 TR2372 308 TR2374 308 TR2375 308 TR2376 309 TR2502 294 TR2503 294 TR2504 295 TR2505 295 TR2506 295 TR2507 295 TR2508 296 TR2509 296 TR2510 296 TR2600 297 TR2601 297 TR2606 297 TR2608 298 TR2700 298 TR2701 298 TR2702 2...

Page 410: ...aked categories in the same dimension example 156 excluded categories in different dimensions example 155 excluded categories in the same dimension example 155 multi dimensional apexed custom views example 154 unique moves when and how to specify example 174 uniqueness tilde character versus MUNs 185 UNIX updating IQD files 40 UNIX Linux default file locations 325 UNIX Linux processing c command l...

Page 411: ...Y year function 362 years between function 362 Index 397 ...

Search results

Summary of Contents for Cognos

Reviews:

IBM Cognos, User Manual, Page: 49 / 411

Search results

Summary of Contents for Cognos

Reviews: