1. DL manuals
  2. IBM
  3. Transformer
  4. Cognos
  5. User Manual

IBM Cognos User Manual

Manual is about: Version 10.1.1

Summary of Cognos

  • Page 1

    Ibm cognos transformer version 10.1.1 user guide.

  • Page 2

    Note before using this information and the product it supports, read the information in “notices” on page 377. Product information this document applies to ibm cognos business intelligence version 10.1.1 and may also apply to subsequent releases. To check for newer versions of this document, visit t...

  • Page 3: Contents

    Contents introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Xiii chapter 1. What's new? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 new features in version 10.1.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 new features in version 10.1.0 . ...

  • Page 4

    Synchronizing columns with your data source . . . . . . . . . . . . . . . . . . . . . . . 37 synchronizing columns with text data files or iqds . . . . . . . . . . . . . . . . . . . . 38 connecting to an iqd data source . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 isolation levels for an...

  • Page 5

    Track changes in a measure in future time periods . . . . . . . . . . . . . . . . . . . . . 84 track monthly performance measures in different time periods. . . . . . . . . . . . . . . . . 85 setting up special categories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 create a new spec...

  • Page 6

    Omit categories using apex . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138 setting up drill-through targets . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 drill through in ibm cognos bi . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 drill through in ibm cognos ser...

  • Page 7

    Adding security to a unix or linux cube . . . . . . . . . . . . . . . . . . . . . . . 194 controlling processing with preference settings or environment variables . . . . . . . . . . . . . . 194 preferences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 environment variables...

  • Page 8

    Preventing errors when model calculations use double quotation marks . . . . . . . . . . . . . 240 framework manager and transformer may display different locale session parameters for some languages 241 regular columns cannot be converted to calculated columns and vice versa . . . . . . . . . . . 2...

  • Page 9

    Tr0503 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260 tr0504 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260 tr0505 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260 tr0507 . . . . . . . . . . . . . . . . . . . . . . ...

  • Page 10

    Tr0907 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279 tr0914 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280 tr0917 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280 tr0919 . . . . . . . . . . . . . . . . . . . . . . ...

  • Page 11

    Tr2701 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298 tr2702 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299 tr2703 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299 tr2704 . . . . . . . . . . . . . . . . . . . . . . ...

  • Page 12

    Sample models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336 sample powercubes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336 sample data sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337 appendix e. Guidelines for working with sap bw...

  • Page 13: Introduction

    Introduction this document is intended for use with ibm ® cognos ® transformer, the olap modeling component delivered with ibm cognos business intelligence. The ibm cognos transformer user guide describes powercube modeling procedures and concepts, product functionality, and related terminology. It ...

  • Page 14

    Development, release, and timing of features or functionality remain at the sole discretion of ibm. Samples disclaimer the great outdoors company, go sales, any variation of the great outdoors name, and planning sample depict fictitious business operations with sample data used to develop sample app...

  • Page 15: Chapter 1. What'S New?

    Chapter 1. What's new? This chapter contains a list of new and removed features for this release. It also contains a cumulative list of similar information for previous releases. Knowing this information will help you plan your upgrade and application deployment strategies and the training requireme...

  • Page 16

    Framework manager externalized queries framework manager iqds, or externalized queries, are no longer supported as data sources in cognos transformer. Instead, you can use ibm cognos packages and reports as data sources. For more information, see “ibm cognos package or report” on page 14. 2 ibm cogn...

  • Page 17

    Chapter 2. Planning your model ibm cognos transformer is a data modeling tool designed for use with ibm cognos 8 version 8.3 and subsequent releases. You use this component to create a model, a business presentation of the information in one or more data sources. After you choose a supported product...

  • Page 18

    Analyzing your requirements and source data to ensure that you develop an effective business intelligence model, we recommend that you begin by carefully analyzing your users, the olap reports they require, and your source data. Use the following questions to analyze your users' olap reporting needs...

  • Page 19

    V you can use cognos transformer to presummarize the data when your users do not require access to all the details in the source. For example, if your organization processes 50,000 transactions daily, and you create the cube weekly, you can summarize the transactions at the weekly level before cogno...

  • Page 20

    Transaction record has fewer business keys to process when identifying the category with which the measure values are associated. Define any transactional data sources that change frequently so that they contain a small, concise record set, with the minimum amount of information needed to update the...

  • Page 21

    If you have multiple data sources, you must relate the dimensions and levels of your model to the data source that contains the columns to be used for each measure. Your model is more effective if your measures are applicable to more than one dimension. For example, if your dimensions are products, ...

  • Page 22

    Order date products locations year product group region quarter product class office month product name sales rep product no refining your model based on your paper prototype, you create the dimension map for your new model in cognos transformer. You begin with one data source. You can enhance the b...

  • Page 23

    You can allocate measures over entire dimensions, over levels within an individual dimension, or over categories within levels. When allocating measures, use measures that come directly from your source data rather than calculated measures, and avoid overloading your model with superfluous detail. V...

  • Page 24

    Show reference to confirm the origin of every data source column associated with your dimension map and see how each is used, use the show reference command on the tools menu. Generate categories to confirm how the categories in a specific data source relate to your model, use the generate categorie...

  • Page 25

    Together, try to develop and maintain a list of follow-up questions, such as the following: v does each dimension level generate valid data, with measures that are properly associated or coordinated, for every data source? Try to spot measures that do not roll up as expected, or that are not additiv...

  • Page 26

    When importing .Mdl files from earlier versions, some features may not convert correctly, such as legacy data that contains special characters, spaces, and quotation marks. For more information, see the migration documentation delivered with your version of the product. Before you begin tip: if you ...

  • Page 27

    Chapter 3. Data sources for your model models contain definitions of dimensions, levels, and measures. They also contain features such as calculated measures, dimension views, or custom views that you add to the basic powercube definition to meet your particular business intelligence needs. By query...

  • Page 28

    Tip: you can also click the help button, where available, for context-sensitive information about the parameters that you must specify. Note: although you can add an unlimited number of data sources or columns to each model, you must perform any necessary joins between the various data files before ...

  • Page 29

    Olap packages cognos transformer allows you to leverage metadata from published olap packages. As a result, cognos transformer powercubes can be used as high speed data access cache methods for distributing smaller or focused areas of your business information. Consider the size of the resulting cub...

  • Page 30

    For more information on creating cognos transformer models, see “creating a model” on page 24. Use list reports data source queries using reports perform most efficiently when the report is a list. Graphs, dashboards, crosstabs, and complex reports cannot be used as data source queries. Importing di...

  • Page 31

    Framework manager governor settings cognos transformer cannot view or override framework manager governor settings with an exception of the governor setting that controls null suppression for sap bw data sources. For more information on framework manager governor settings, see the framework manager ...

  • Page 32

    Aggregate values in imported query items when you import a report studio report, a query studio report, or a package as a data source where aggregate values do not match the source package or report, you receive an error message if the report or package contains an aggregation rule that is not suppo...

  • Page 33

    Tm1 cubes ibm cognos transformer supports tm1 ® cubes as a data source. You must install the tm1 client on the same computer as the ibm cognos business intelligence installation. Impromptu query definition file impromptu ® query definition (.Iqd) files are generated from ibm cognos impromptu. The .I...

  • Page 34

    With delimited-field text, input values are obtained from a text file with one record per line. When importing a flat file data source in cognos transformer, specify how the fields (column values) are delimited in the field delimiter box, and either accept the default character set or specify an alt...

  • Page 35

    Powerhouse portable subfile with a powerhouse ® portable subfile, input values are obtained from a cognos powerhouse 4gl portable subfile. In cognos transformer, specify the portable subfile dictionary (.Psd) file or the data (.Ps) file. Accept the default character set or specify an alternative. Fo...

  • Page 36

    Conformed dimensions are also key in successful data analysis using multiple powercubes. When two cubes are to be used together, as with drill through, ensure that the dimensional structure and the category source values are the same in each cube model. Changes in the structure of a dimension in one...

  • Page 37

    V the category or member may no longer exist in the data source. To avoid these problems, use the following best practices: v use unique codes and keys within a dimension for the category or member keys. Ensure that your cognos transformer model source values have unique values throughout the levels...

  • Page 38

    Maximizes query speed, cognos transformer does not check your model for uniqueness conflicts. For this reason, save your model after every cube build. To ensure successful business intelligence applications using ibm cognos bi, the data sources that feed your cognos transformer models must have uniq...

  • Page 39

    The order in which the data sources, queries, and columns appear in the list does not impact the cube build performance. Also, regardless of this order, cognos transformer automatically processes queries with measures last. Creating a model using an ibm cognos package or report when the data source ...

  • Page 40

    Tip: you can also click the drop-down arrow to select a recently used package or report from the list. 5. In the browse metadata dialog box, select the package or report to use for the data source, and click ok. 6. Click next. 7. If you select a report as a data source and it contains mandatory prom...

  • Page 41

    Results use the data source viewer to view sample data and, for supported data sources, the cognos sql or the native database sql. Creating a model using an sap bw package sap bw packages can be used to import fact data and dimensional data. When creating your model use the insert dimension from pac...

  • Page 42

    Transformer hierarchy. In cognos transformer, it is possible to define the text that should be used for blanks (the default text is ""). A best practice is to define a more appropriate text for blank entries for all such levels. 9. If you want to add another query, repeat steps 3 to 7. 10. Save the ...

  • Page 43

    7. Specify whether or not to run autodesign, and click finish. 8. Confirm that your selected items appear as expected in the data sources list. Tip: to view sample data or the sql from your data source, from the view menu, click data source viewer. 9. Save the model. Results if you imported measures...

  • Page 44

    Data source to add the first query to the model, and use the duplicate report data source to add the second query to the model. Cognos transformer may reflect a cascade prompt as two multi- or single-select prompts: v the first prompt represents the first level of the original cascade prompt. V the ...

  • Page 45

    – when you enter a prompt value, the data type is not verified. Entering 15oo, rather than 1500, for a numeric value does not generate an error or warning even though the value is invalid. Powercube creation may fail if you use an invalid value. 4. For each prompt that you want to delete from the qu...

  • Page 46

    Results note: alternatively, you can remove obsolete prompts by directly editing the prompt specification file that is generated from the model. Specifying a segmenting prompt for an sap bw query a segmenting prompt is used when querying a sap bw data source for fact data. Also known as a bex variab...

  • Page 47

    Change a data source type if you decide after you import a data source in cognos transformer that the data can be more effectively sourced from a different data source type, you can change the data source. You cannot change a data source for a query in the model to match another query already in the...

  • Page 48

    Tip: if you want to use the measures from an olap source, create a relational query against the source data used to build the original olap source. This approach will provide the best possible performance. Cognos transformer version 8.X and later does not support parent-child hierarchies in olap dat...

  • Page 49

    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 1. Right-click the ...

  • Page 50

    Process on the 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 should not import query items from different query subjects unless the query was intended for or works well with t...

  • Page 51

    Tip: if the insert data source option is unavailable, you may have right-clicked a package or report data source. 2. In the data source name box, type the name of the data source and, in the data source type box, select one of the available options. 3. Click next to specify information about the dat...

  • Page 52

    2. If there are columns in the model that cannot be matched to data source items, you will get a warning message. Click no to keep these unmatched columns in the model. If you click yes, cognos transformer deletes the unmatched columns. 3. For data source items that do not appear as columns in the m...

  • Page 53

    Results data source items are now synchronized with the model. Connecting to an iqd data source you can use impromptu query definition (iqd) files to access data from local or server-based databases, even if you do not have ibm cognos impromptu installed on your modeling computer. The .Iqd file type...

  • Page 54

    Isolation level description phantom protection prohibits access by a transaction to rows inserted or deleted since the start of the transaction. Serializable ensures that a set of transactions performed concurrently produce the same result as if they were performed sequentially. For an up-to-date li...

  • Page 55

    Character sets used with delimited text data sources cognos transformer is designed to handle the more common character sets used in delimited text data sources. However, consider the following when working with delimiters. V standard delimiters such as the comma, semicolon, or space character remai...

  • Page 56

    Row a b c d e 2 3 income-net sales 10,000 10,000 15,000 20,000 4 expenses- salary 2,000 2,000 2,500 2,500 5 expenses- rent 800 800 800 800 6 expenses- advertising 600 700 600 600 7 expenses- production 1,500 1,500 2,000 4,000 8 net income 5,100 5,000 9,100 12,000 for this crosstab, the following ran...

  • Page 57

    Row a b c d 5 1290 power design 80,000 the named range for cells a1 .. D5 is datatable. Cognos transformer reads rows 1 to 5 in the range datatable. The data sources list shows the human resources columns emp_no, name, dept, and salary. Powerhouse data source parameters you can specify a powerhouse ...

  • Page 58

    V measure allocation capabilities measures that are not normally related to some parts of the dimensional hierarchy can have values allocated to those parts of the dimension. For example, suppose your sales-tracking system lacks information about staffing levels at each branch. You include a data so...

  • Page 59

    Level derived indirectly when a level is derived indirectly, the source is not related to the level, but is related to descendants of the level. The default color is light yellow. For example, this source contains a column that provides category values for the unique level product no., but no column...

  • Page 60

    Because go data warehouse contains columns for all six dimensions, its data source scope covers all the dimensions in the model. Control when the source data is read in models with multiple data sources, you can control when a data source is read. For example, a data source can be read during catego...

  • Page 61

    V after cognos transformer reads your purely structural data sources and populates your model with data for the required category structure, you can change the timing so that these static categories are not regenerated every time you create the powercube. V for structural data sources that frequentl...

  • Page 62

    As the modeler, you must ensure that your model columns remain synchronized with their associated data sources. You can use the data sources list to create or delete columns, and to examine or modify column properties. For some data sources, cognos transformer can automatically identify columns and ...

  • Page 63

    3. If required, specify the turn-of-the-century break-point (centurybreak setting). 4. Specify whether the measure values associated with these dates apply to some or all levels in the time dimension. Define columns in a fixed-field text data source when your data source is a fixed-field text file, ...

  • Page 64

    Column 1 column 2 column 3 column 4 20070103 product1 color1 02140330 20070103 product1 color2 02870335 20070103 product2 color1 04560508 20070103 product2 color3 03110388 scale input values you may want to change the number of decimal places or significant digits in your measures to better reflect ...

  • Page 65

    Set the level of detail for dates values for some measures in a model often apply to time periods that are not at the lowest level in the time dimension. In such cases, you specify the actual level of detail to which the date values apply. For example, actual revenue values may be derived from invoi...

  • Page 66

    You can define more than one array per model. For example, you can set up one source file with all quarterly or all monthly arrays. Or you can set up multiple source files, with one array type in each file, using a different array for each year of data. However, you must use the same format in all d...

  • Page 67

    11. If there are no problems with your design, you can now create the cube and confirm your results in your olap reporting component. If there are problems with your model design, review the check model messages to identify the issues. Example - consolidating quarterly data (single-source model) you...

  • Page 68

    Results in your olap reports, you can use nesting to show how many items were sold by each retailer type for the 12-month periods beginning 2006/01 and 2007/01. You can also show the totals sold by all retailers for each year, and for all years. Product line 2006 2007 2006+2007 sports 60 120 180 gen...

  • Page 69

    There are two types of cognos transformer signons: data source signons and ibm cognos signons. By defining a data source signon in the cognos transformer model, you can avoid having to specify the signon information that the data source requires to build the cube. Creating a data source signon data ...

  • Page 70

    7. Click ok. The new data source signon appears in the signons list and is preceded by a special icon creating an ibm cognos signon you can configure ibm cognos to use authentication to an external namespace where users are prompted for credentials as part of the logon process. You can create a sign...

  • Page 71

    Chapter 4. Structuring your data into dimensions by structuring your data into dimensions, or hierarchies that represent major segments of your business information, you ensure that powercubes created from your model support your users' olap reporting and analysis needs. For example, in a sales anal...

  • Page 72

    Tip: if you do not want to use autodesign, you can either clear the run autodesign check box on the last page of the new model wizard or clear the run autodesign when creating a new model check box on the autodesign tab of the preferences property sheet. Creating a new dimension you need to create a...

  • Page 73

    Procedure 1. In a cognos transformer model, right-click the dimension map and click insert dimension from package . 2. Browse to select a dimensional package and click finish. 3. In the dimension tree for the package, select the dimensions, hierarchies, or levels that you want to add to your model a...

  • Page 74

    If you have not specified a data class, you are prompted to specify whether the column consists of text, date, or numeric data. 4. In the left pane of the expression editor, expand the columns and functions folders as needed, select each parameter you want to use, and click the right-arrow button to...

  • Page 75

    Results your olap analysis users can now see each player's salary forecast. Adding levels and categories to a dimension each dimension in a model contains one or more levels that represent the information hierarchies that your users can explore as drill-down levels in their olap reporting component....

  • Page 76

    Tip: you can create a default time dimension with year, quarter and month levels by setting the data class on the general tab of the column property sheet to date and dragging the column to the dimension map. Procedure 1. In the data sources list, select the column for which you want to create a sou...

  • Page 77

    5. For each intermediate category required in the manual level, create a category manually. For more information about manual categories, see “create categories manually.” 6. Connect the categories created in step 5 to the appropriate child source categories in the next lowest level. Example - addin...

  • Page 78

    Calculations are computed in cognos transformer and then added to the powercube, the results immediately appear in the olap reporting component for every measure specified in the cube. For example, you can base a calculation on the special time categories current month and last month. You use the pe...

  • Page 79

    Expression, and type or copy-and-paste a valid calculation. For more information, see appendix f, “ibm cognos transformer expression editor,” on page 355. 8. Click ok in the expression editor when you are ready to save your final expression. Creating a single calculated category perform the followin...

  • Page 80

    7. Double-click category set and observe that set 1 appears in both the left and right panes of the expression editor. 8. Expand the levels in the categories diagrammer, so you can select categories for the set. 9. Select and drag the categories in your set, one by one, to the drop location for set ...

  • Page 81

    6. In the sort as box, click either alphabetic or numeric to specify whether values are to be interpreted as text or numbers during the sort. 7. Click ok. Order categories using a global preference setting you can specify that all categories in the model use a particular sort order, rather than manu...

  • Page 82

    Drill-down paths organizing the data in your model into meaningful hierarchies enables your olap report users to analyze the business information at various levels of detail. Each dimension consists of one or more drill-down paths that typically contain several drill-down levels. For example, a typi...

  • Page 83

    Because the category values at the convergence level and below are shared by all drill-down paths, removing or changing a category in one path at or below the convergence level immediately affects the same category in all other drill-down paths. Procedure 1. In the dimension map, select the level th...

  • Page 84

    Procedure 1. Open the categories diagram view of your model, right-click the category whose values you want to show when the cube opens, and click set as default category . 2. Right-click either the root or the parent category for your scenario categories, click properties, and on the general tab, s...

  • Page 85

    You are required to specify both the date source column and the dimension name for your time dimension. You can insert manual levels into a time dimension, but you must specify a date function for each level so cognos transformer knows how to relate the categories to their parent levels during categ...

  • Page 86

    Note: if you manually type the name for the source column in the date level creation dialog box, you are warned that the columns do not exist in the data sources list. Cognos transformer uses standard date functions to generate categories in the levels of the time dimension without actually referrin...

  • Page 87

    3. In the dimension type box, select time. 4. Click the time tab. 5. In the date level creation dialog box, select create standard levels (year/quarter/month) to allow cognos transformer to automatically specify the required date structure. 6. In the source column list, select the column that contai...

  • Page 88

    Set up fiscal years, quarters, and months by default, dates in the time dimension are organized in accordance with the standard calendar year, with a year begins property controlling the date when the year starts. To set up a time dimension for a non-calendar fiscal year, you change the year begins ...

  • Page 89

    6. In the level name box, type a name for the parent of the convergence level in the new drill-down path. For a fiscal year path connected at the month level, the parent level name is fiscal quarter. 7. In the associations box, click add. 8. In the new association dialog box, select source from the ...

  • Page 90

    Example - changing how partial weeks are handled at the end of the year in this example, you change how partial weeks are handled at the end of the calendar year. Because your business aligns its fiscal year with the calendar year, you do not want cognos transformer to apply the default setting alwa...

  • Page 91

    We recommend choosing this method if you want to keep the lunar year aligned with the calendar year or your fiscal year, a requirement in most business operations. Procedure 1. On the dimension map, create a new time dimension with automatically added levels. 2. Open the property sheet for the year ...

  • Page 92

    Procedure 1. Open the property sheet for the time dimension whose absolute range you want to change and click the time tab. 2. In the earliest date and latest date boxes, type the dates that represent the lower and upper boundaries of the range respectively and click ok. 3. On the dimension line of ...

  • Page 93

    5. In the date level creation dialog box, select do not create levels and click ok twice. 6. Position the cursor in the dimension map, below your new time dimension and, from the edit menu, click insert level to open the property sheet for your first manual level. 7. In the associations box, click a...

  • Page 94

    – to-date – prior to-date – to-date change (automatically calculated categories) – to-date growth (automatically calculated categories) the periods depend on the date levels defined in your model. In the cognos transformer categories diagram, relative time periods appear in the category viewer as sp...

  • Page 95

    2. On the general tab, clear the sets the current period check box for all but the one you want used to set the current period. 3. Open the property sheet for the time dimension and, on the time tab, select the automatically set the current time period check box. Click ok. Set the current period man...

  • Page 96

    V last month v last quarter v last year v same month, prior quarter v same month, prior year note: select custom if you want to override any of the default selections on the relative time tab. For example, you can specify the basic approach, a different target time period for your new category, a ne...

  • Page 97

    Period will be reported, and the corresponding offsets, to track values that apply to a period other than the current one. For more information about specifying custom time periods, see “track changes in a measure over several time periods” and “track changes in a measure in future time periods” on ...

  • Page 98

    For example, if you are creating a running total of 6 months, create the total within the context of year. 11. In the context offset box, or in the context range box if you selected n-period running total (grouped) in the basic approach box, type the number by which the context period is offset when...

  • Page 99

    Track monthly performance measures in different time periods business users often want to see monthly performance in one time period compared to another. You can add the extra time dimension to your model by using the calculated columns feature. This solution doesn't require a change to the date col...

  • Page 100

    For example, suppose that one dimension in your model organizes all your products by product line and product type. You want to group your most promising products into a special category. These include star gazer-2 tents, the compact relief kit first aid kit, sunshelter-15 sunblock, and all your wat...

  • Page 101

    In the diagram, the children of the special category appear both in the main hierarchy and in the special category branch. Any changes to a regular category within this group are reflected in both instances. For example, suppose you have a dimension called region that includes branches from three co...

  • Page 102

    It is possible for parents to add rollup values from some children and not from others. If no child categories have category rollup selected, then the parent category becomes a placeholder and no values are associated with it when viewed in the olap reporting component. Note: you cannot perform roll...

  • Page 103

    2. Click the time tab. 3. From the date input format box, select the appropriate date format and click ok . 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 . Adjust the date ra...

  • Page 104

    3. In the diagram, click on the right side of the drill category, so that the pointer changes to a crosshair, and drag the pointer to a position under the new level. When you release the mouse button, the category property sheet appears. 4. Select category is an orphanage, specify any other required...

  • Page 105

    Generating categories from a specified data source 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 so...

  • Page 106

    92 ibm cognos transformer version 10.1.1: user guide.

  • Page 107

    Chapter 5. Modeling your key performance measures in your cognos transformer model, you must include the key measures required to gauge the performance of your organization. Quantity of items sold and profit margin are examples of such performance indicators. A model can contain any number of regula...

  • Page 108

    Rollup to handle these summarizing operations, such as computing the mathematical average or taking the last result. During the modeling process, you should also confirm how duplicate values are consolidated, and determine if any values need to be allocated to categories, levels, or dimensions that ...

  • Page 109

    Define a measure folder when you use measure folders to create groupings of measures, your users can more easily navigate through various measure rollups and drill down to view the lower-level measures in their olap reports. Measure folders can represent logical groupings of lower-level measures, or...

  • Page 110

    For example, you can create a count to show how many customers of each type bought a specific product each month, quarter, or year. This count is a consolidation of all the categories in the unique level customer number. For example, if the same customer buys a product in two months, the quarterly r...

  • Page 111

    Define a calculated measure you can create a calculated measure to derive new numeric data from regular measures, other calculated measures, functions, and constants. You can specify the format for the result, and also explain the calculation in the description box on the measure property sheet, so ...

  • Page 112

    Timing operation sequence after rollup 1 regular rollup takes place on all measures. 2 time state rollup takes place. 3 allocated measures are calculated. 4 calculations are performed on calculated measures. Before rollup 1 calculations are performed as the cube is created. 2 regular rollup takes pl...

  • Page 113

    Changed to the current cognos transformer default (na). Upgraded models will handle missing values in the same way as they did in previous versions of cognos transformer. To ensure that missing values are shown appropriately, review the missing value settings for all measures before building new cub...

  • Page 114

    Note: the cognos transformer expression editor is not the same expression editor used in framework manager or the ibm cognos studios. When you build an expression, you select elements, one by one, from the available components list in the left pane of the expression editor and build your expression ...

  • Page 115

    For example, level of inventory is recorded for a specific product, at the same warehouse, in the same year, on different dates. If you select average and the degree of detail set for the column is day, the identical records are summed and then divided by the number of day categories in the month. H...

  • Page 116

    Procedure 1. Open the measure property sheet and click the rollup 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 type of ...

  • Page 117

    Missing and, by default, be reported as zero. If the special category has only one child, you can either supply a value for the special category or accept the value taken from the child. Procedure 1. After you have imported the structural and transactional data sources into your model, open the data...

  • Page 118

    V time state rollup cannot be applied to a measure if the model has more than one time dimension. V if you use time state rollup but more than one record is associated with each data point, values will be summarized using regular rollup. For example, if there are two inventory stock counts daily, th...

  • Page 119

    Date product qty 20061115 tr139sq 400 20061130 tr139sq 600 20061215 tr139sq 250 20061231 tr139sq 350 procedure you select last period from the time state rollup drop-down list of functions for the quarter date level. Results your olap report shows the following quarterly data, and the year-level rol...

  • Page 120

    Set regular and time state rollup together you can use regular rollup and time state rollup together to summarize measure values in your olap reports. When both are selected, regular rollup is performed first, followed by time-state rollup. There are several constraints that apply when defining thes...

  • Page 121

    Results the first rollup, sum, yields qty values of 900 for 20061115 and 800 for 20061130. The second rollup, last period, yields a qty of 800 for 200612. Consolidation consolidation uses rollups to combine records with identical non-measure values into a single record, thereby reducing cube size an...

  • Page 122

    Suppose your source data contains daily transaction records such as the following, which are used to create the date and region dimensions. Date region - store quantity revenue 20070105 east-store1 50 5000 20070110 east-store2 20 2000 20070131 east-store1 40 4000 20070201 east-store1 60 3000 2007022...

  • Page 123

    If values for calculated measures are generated before rollup, cognos transformer cannot perform consolidation. However, you can overcome this problem by using calculated columns. Because such values are always calculated before rollup, consolidation is again possible. Procedure 1. Open the measure ...

  • Page 124

    This type allocates a single, constant measure value to all descendant categories. V by measure this type allocates values to descendant categories in proportion to a specific measure. You can allocate measures to dimensions or levels that lack them in the following ways: v over entire dimensions, w...

  • Page 125

    The scope of a measure depends on its relationship to the levels in the dimension map . Level derived directly in this case, the dark yellow color indicates that the level takes its category values directly from the measure. In this example, the levels year, quarter, and month are directly associate...

  • Page 126

    However, you can change this default allocation to do not allocate or by measure (proportionally allocate, based on values in another measure). Procedure 1. Open the property sheet for a dimension that is not referenced by the measure you want to allocate and click the allocation tab. 2. Right-click...

  • Page 127

    Example - allocating a measure to levels in the time dimension you decide to allocate your annual sales forecasts on a monthly basis, in proportion to actual revenues. Procedure using the level property sheet for year, the parent level of quarter and month, set the allocation type for the revenue me...

  • Page 128

    Procedure 1. Open the categories diagram, and then open the property sheet for the category for which you want to set the allocation type. 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 m...

  • Page 129

    Procedure 1. Open the dimension, level, or category property sheet for which you want to suppress default allocation. 2. On the allocation tab, right-click the allocation type and select do not allocate . Setting up currency conversion if your report users need to see their monetary measures in more...

  • Page 130

    (such as euro triangulation) to follow. For more information, see “enable conversion using a euro table” on page 118. We recommend that you always build cubes in cognos transformer using the system locale that matches the locale of the measures in your cube, and that you explicitly define a default ...

  • Page 131

    3. If categories have not been generated for the time dimension in your model, click generate categories from the run menu. By generating categories immediately, you do not have to wait until cube creation to see the period information in the conversion rates box of the currency table dialog box. 4....

  • Page 132

    This excerpt shows rates for the final quarter of 2006, including three mandatory columns for the currency record dialog box (date, country code, and rate), and the optional column label. To start, conversion rates have a default value of 1.0000, but you can change the value for any period that is m...

  • Page 133

    Before you begin you should clear the timing options in the property sheets for all currency data sources so that updates to the currency table do not interfere with category generation in the rest of your model. If you decide to leave all timing options at their default settings, ensure that the na...

  • Page 134

    2. Confirm that the base table columns box shows the correct names for the date , country code, rate, and optional label source columns, to populate each currency in the euro currency table. If not, click the ellipsis (...) button to the right of the column name and make corrections in the new assoc...

  • Page 135

    Euro date,euro label,euro country code,euro conversion rate 0,austrian schilling,aut,13.7603 0,belgian franc,bel,40.3399 0,finnish markka,fin,5.94573 0,french franc,fra,6.55957 0,german deutsche mark,deu,1.95583 0,italian lira,ita,1936.27 0,dutch guilder,nld,2.20371 0,spanish peseta,esp,166.386 1999...

  • Page 136

    122 ibm cognos transformer version 10.1.1: user guide.

  • Page 137

    Chapter 6. Creating powercubes when you select create powercubes from the run menu, one or more powercubes are generated with the default property settings. These binary files contain all the source data for the dimensions and levels in your model. Powercubes generated by cognos transformer version ...

  • Page 138

    Each cube uses structured query language (sql) to retrieve the specified query items and their relationships from the data sources in your model. When you create or modify a cube, you can also apply security to it, to control your users' access to information. For example, if your data source contai...

  • Page 139

    Create a cube group you can create a cube group for any level in a dimension and add child cubes that include selected categories or measures. For each cube in a group, you must specify the degree of detail to be included so that your users see only the most relevant data. For example, suppose you w...

  • Page 140

    V maintains a list of all measures, dimensions and root categories, currency records, and the entire structure of the time dimension v contains the control information to gather the data from the child cubes v serves as the entry point for olap report users to access the time-based partitioned cube ...

  • Page 141

    V your model can only have one time dimension. If the model has more than one time dimension, the enable time-based partitioning check box is permanently disabled. V dimension calculation definitions that create calculated categories are not supported. You cannot use a calculated category at the tim...

  • Page 142

    Create a time-based partitioned cube group to more easily manage time-based partitioned cubes, you can gather them into like-structured groups. Procedure 1. To set up the cube group, on the powercube property sheet, click the cube group tab, select the enable time-based partitioning check box, and c...

  • Page 143

    Adding new data to time-based partitioned cubes new categories are added to the newer cubes in the normal manner. However, older, pre-existing categories may not have new data associated with them in the more recent cubes. In such cases, not every child cube may contain the complete set of metadata;...

  • Page 144

    Considering the warnings about unexpected sort order, you can use unique moves in time-based partitioned cubes by selecting both unique and move on the level property sheet. Any changes in the model are reflected in the new child cubes. If you make a change to the model and then build your time-base...

  • Page 145

    In other words, after the model structure changed, the older child cubes were locked and were therefore not updated with the new data. The control cube combined data stored in the newest child cube and therefore could update the parent value in the time-based partitioned cube. Customizing a time-bas...

  • Page 146

    Tip: be careful not to exclude measures that are used in the calculation of other measures used in the model. Otherwise, errors may occur when your cube is created or updated. Procedure 1. On the powercube property sheet, click the measures tab. 2. Right-click a measure and click exclude. 3. Click o...

  • Page 147

    Note: the cloak, summarize, and suppress options do not remove data from the cube. Therefore, even if many categories are omitted, there is no corresponding decrease in cube size. You create dimension and custom views in much the same way, but you must configure security before you include custom vi...

  • Page 148

    Implement and maintain. Because only a subset of the categories is written to the cube, a cube that implements dimension views often has the advantage of being smaller and therefore quicker to build and to deploy. You should keep the following constraints in mind when implementing this feature: v yo...

  • Page 149

    For example, suppose that your company maintains branches throughout the world. Some branches are identified by their country or region. Others are identified by city only. In your model, you structure the regions dimension to show levels for retailer country or region and city. To streamline your c...

  • Page 150

    Like suppress, the cloak option causes a category to be omitted from reports based on the cube. Unlike suppress, all descendants of that category are also omitted. When you apply cloaking to a cube, consolidation occurs by default. If new subordinate categories are added to a cloaked level, they are...

  • Page 151

    From your cube, so that users only see data for the united kingdom and canada. You create the dimension view and apply it to the powercube object. All data associated with the excluded categories is omitted from the resulting cube and its related reports. Excluded categories are not included in cate...

  • Page 152

    Categories in a level, if new subordinate categories are added, they are automatically included in the summarized ancestors. Although applying a summarized view consolidates the records in a cube by default, you should not use this option as a means of decreasing the size of your cube. Also, you can...

  • Page 153

    3. In the category viewer, select the categories to be apexed and, from the diagram menu, click apex. Note: this action omits all ancestor and sibling categories. If you need to reverse this command, reapply it at a higher level. 4. Open the powercube property sheet, and click the dimensions tab. 5....

  • Page 154

    Transformer version 8.X and later this is not required; the source value will always be passed as the business key to relational target reports. Drill through in ibm cognos series 7 drill-through settings in cognos transformer models are maintained for building powercubes for use in ibm cognos busin...

  • Page 155

    If a drill-through filter applies to a level that was not declared unique in the model, the parameters passed to the target component must contain information for that level and for each ancestor level until a unique level or the highest level in the dimension is reached. In powerplay-to-impromptu d...

  • Page 156

    Procedure 1. Open the measure property sheet, and click the drill through tab. If you are setting up drill through for all measures in a cube, open the powercube property sheet, and click the drill through tab. 2. If you are setting up drill through for the first time, click the allow drill through ...

  • Page 157

    2. Click add, type the name of your temporary file in the file name box, and click open to add it to the custom reports list. 3. Select your temporary file from the custom reports list, and click it once to edit the filename. 4. Type the path to the unix/linux server, and press the enter key on your...

  • Page 158

    Procedure 1. From the run menu, click test build. 2. In the number of records to process box, type the number of records to be read from the source. 3. Click either generate categories only or create powercubes. 4. Click ok. Update the powercube metadata during powercube creation, all the source dat...

  • Page 159

    Procedure 1. Click the powercubes list to 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

    Chapter 7. Adding security cognos transformer supports simultaneous user authentication and logon using the full range of supported ibm cognos security providers. You can also add custom views to each powercube to grant or deny access to sensitive business intelligence information. These access cont...

  • Page 162

    V ensuring that the required authentication provider is configured in your ibm cognos bi environment and that the required users, groups, and roles are available from that cognos namespace, referencing the configured authentication provider of your choice for more information, see the administration...

  • Page 163

    A role is a grouping that typically includes users who have similar responsibilities and privileges in your organization. Roles can include users, groups, and other roles. Individual users can belong to several groups or roles. Group and role names must be unique. Create member-based security you ad...

  • Page 164

    Procedure 1. Open a model in cognos transformer and, from the security menu, click log on . 2. In the logon dialog box, select the appropriate namespace from the drop-down list, and log on as an authenticated user. 3. Open the categories diagram for your model and click the custom views tab. 4. Righ...

  • Page 165

    Update model security when the security data in your authentication provider is updated, you are required to update the security objects associated with cognos transformer custom views. When custom views are enabled for a model, cognos transformer reads the security objects from the namespace config...

  • Page 166

    A user can be a member of several user groups or roles, and can belong to multiple custom views. In ibm cognos, when a user belongs to more than one group or role and the group, roles, or user belongs to multiple custom views, the cube opens with a union of all the custom views. A union of custom vi...

  • Page 167

    Example - apexed custom view cloaked by another custom view in the same dimension this example describes the union of an apexed custom view cloaked by another custom view in the same dimension. Custom view a: apex australia custom view b: cloak asia pacific the region dimension is temporarily modifi...

  • Page 168

    Example - union of two apexed custom views in different dimensions this example describes the union of two apexed custom views in different dimensions. Custom view a: apex camping equipment custom view b: apex americas all children of the region dimension are shown because they are shown to custom v...

  • Page 169

    Example - union of excluded categories in two dimensions this example describes the union of excluded categories in two dimensions. Custom view a: exclude camping equipment custom view b: exclude asia pacific because custom view a is shown all categories except the camping equipment, and custom view...

  • Page 170

    Example - union of excluded and cloaked categories in the same dimension this example describes a dimension with custom views that include excluded and cloaked categories. Custom view a: exclude united states, cloak mexico custom view b: cloak boston in each custom view, boston is cloaked. Because c...

  • Page 171

    Create password-protected cubes you apply security to an entire ibm cognos powercube or cube group by setting a password to restrict access to authorized users. When users access cubes and reports in their reporting component, they must enter the password in the cube logon dialog box to see the data...

  • Page 172

    3. On the dimensions tab of the powercube property sheet, select the appropriate dimension views. 4. Create and deploy your secured powercubes. Block total values for parent categories with excluded children you can block the summed values for parent categories with excluded children, so that the re...

  • Page 173

    V import the ibm cognos series 7 user class views and user classes associated with the model. Choose this option when you want to maintain the view operations applied in the user class views and use the ibm cognos series 7 user classes, or if you want to transition to an alternate security provider ...

  • Page 174

    160 ibm cognos transformer version 10.1.1: user guide.

  • Page 175

    Chapter 8. Powercubes in production environments with cognos transformer, you can build and deploy ibm cognos powercubes in distributed production environments. This enables you to take advantage of dedicated cognos transformer servers for optimal cube build performance, while ensuring that your ibm...

  • Page 176

    Cube is not a multilingual cube; it is a unilingual cube in the utf-8 code page. Ibm cognos series 7 products are unable to read powercubes that use the utf-8 code page. To build powercubes in a code page that is different than the current code page, you can change the cube code page setting on the ...

  • Page 177

    You can publish a single cube, a cube group, or all cubes in a model in one publish operation. To organize cubes efficiently, publish them so that hierarchies are represented in ibm cognos connection. For time-based cubes, the parent and all child cubes are published, but a package is created for th...

  • Page 178

    This is the data source name that appears in ibm cognos connection. Tip: optionally, you can add a description and screen tip for the published cube. 6. Click next. 7. In the publish powercube dialog box, do the following: v if you want to set a value that is different from the default of 80 mb, ent...

  • Page 179

    10. In the finish page, the create a package check box is enabled and greyed out so that you can publish the package; click ok. 11. In the name box, specify a name for the published package. This is the package name that appears in ibm cognos connection. Tip: optionally, you can add a description an...

  • Page 180

    5. If the cube is secured, in the authentication box, select the appropriate namespace. For powercubes that are in development and in transition from a series 7 namespace to an alternate security provider, you can associate all the applicable namespaces. This option is intended only for the testing ...

  • Page 181

    Procedure 1. In the powercubes list, right-click the cube, and click publish powercube as data source and package . 2. In the publish cube dialog box, click create publish specification radio button, and click ok. 3. In the file name box, type the name for the publish specification file, then save t...

  • Page 182

    For information about publishing cubes from the command line using the publish specification, see appendix a, “command line options,” on page 209. Publishing a cube group you can publish all or only selected, enabled powercubes in the group. The publish information for each cube-member of the group ...

  • Page 183

    Each time these users access a cube, their requests are routed to the dedicated server. V when you have several cubes that use the same metadata but are each built with a different language, you can use package routing to direct users who use a specific language to the appropriate powercube package....

  • Page 184

    The placeholder categories are: early dates, late dates, or invalid dates. To lock a dimension, preventing cognos transformer from adding unwanted categories, we recommend that, in the dimension property sheet, you prohibit the automatic creation of new categories. When you are making changes to you...

  • Page 185

    Partitions are spread across the multidimensional partition, and one .Mdc file is added to hold the powercube metadata. For example if you have 100,000 input records and set the multifilecubethreshold value to 20,000, a maximum of 5 .Mdp files and one .Mdc file are created, depending on the partitio...

  • Page 186

    2. To ignore a checkpoint file in batch mode, use the -i command line option; for more information, see appendix a, “command line options,” on page 209. Matching model and source columns when you create a model, the columns in each data source are saved as part of the model definition. If you reorde...

  • Page 187

    Tip: click refresh source to refresh the source list for the data source. Click validate to check whether columns in the model violate any framework manager governor settings. 4. For columns that you want to remove from the model, select the columns in the model list and click remove. 5. For unmatch...

  • Page 188

    In ibm cognos, any categories that do not move under a different parent will have the effect of a changed member unique name for that category. When reports that directly reference this category or member are run, the report consumer will receive an error. The report author will need to reassociate ...

  • Page 189

    To speed up cube creation, reduce cube size, or improve run-time access for your olap report users, you may need to v adjust your data v streamline your data sources v redesign your model v select different cube processing options tip: when you make changes to your model, be aware that the changes y...

  • Page 190

    Two different optimization methods at the same time. The check model feature issues a warning indicating when categories optimization is used instead of auto-partition. For information about cube partitions, see “choosing a partitioning strategy” on page 180 and “partition manually (if required)” on...

  • Page 191

    For time-based cubes, child cubes are always published with the time-based cube. Disabling publishing for a child cube only restricts publishing of that cube individually. Enable crosstab caching use this option to store summaries for the initial crosstab in the cube. These summaries help improve ru...

  • Page 192

    Tip: because the consolidation process runs against every source file in your model, ensure that your temporary file space is at least as large as the sum of all your source files. Procedure 1. Open the powercube property sheet for the cube that you want to consolidate and click the general tab. 2. ...

  • Page 193

    This action removes all categories not created or changed since your specified date. 3. Regenerate the cube and confirm that the stale-dated categories are no longer present. Modifying a powercube if the business information requirements or data access rights of your report users change, you will li...

  • Page 194

    Tip: if your model uses cube groups, and only a subset of them contain data that has changed since the last update, you can save processing time by disabling the cube creation option for the unchanged member cubes. For performance reasons, we recommend that you periodically recreate the entire cube,...

  • Page 195

    Constraints in developing your partitioning strategy, consider the following constraints: v you cannot use auto-partitioning if consolidate is disabled on the general tab of the powercube property sheet, or if optimization is set to any method other than auto-partition or default, such as categories...

  • Page 196

    Adjust the auto-partitioning parameters you can use the auto-partition tab on the powercube property sheet for each cube in your model to test different settings and see which ones yield the best results for your situation. Before you begin before you begin, ensure that any previously defined manual...

  • Page 197

    Procedure 1. Let cognos transformer auto-partition your cube, leaving the optimization settings at their defaults. 2. In the resulting log file, note the partition points selected by the auto-partitioning algorithm. Generally, the dimensions with the most levels offer the greatest possibility for co...

  • Page 198

    Window shows the dimension name, the category code, and its record count. Check to see if any of the partitions are larger than your specified partition size. 12. Open the cube in your olap reporting component and try drilling down to the partition with the largest number of records. If performance ...

  • Page 199

    Results having reached the extent of your own expertise, you gather the following data and prepare to contact cognos software services, to have them investigate your problem further: v the cognos transformer log file from the powercube build, with information about which dimensions were involved in ...

  • Page 200

    Setting up placeholder categories for cube groups when you define a cube group, you can manually add a placeholder category to the level from which future cubes will be derived to allow for additions. Provided the new members have the same rolled-up data as the existing members, the cube containing ...

  • Page 201

    Location manually. The pcactivate command is the only method to activate an updated series 7 cognos transformer powercube. Copying and activating a newer version of a published powercube after rebuilding or updating a published powercube, you can make the newer version of the powercube available, wi...

  • Page 202

    Tip: if you created new versions of more than one powercube, you can copy and activate them at the same time using copy and activate powercubes from the run menu. Setting up a copy and activate strategy perform these steps on your powercube before using the copy and activate functionality. Procedure...

  • Page 203

    Example - using an updated powercube to process a query this is an example of using an updated powercube to process a query. For a cube named mycube.Mdc, where one version was copied and activated on february 1, 2008 and a newer version was copied and activated on march 1, 2008, the deployment locat...

  • Page 204

    C:\cubes\product note: for multifile cubes and time-based partitioned cubes, all cube parts must be copied to locations relative to the location of the base cube. 2. Copy the updated powercube to the directory described in step 1. Based on the example from step 1, the directory structure after copyi...

  • Page 205: Environments

    Chapter 9. Guidelines for optimizing production environments this section provides optimization guidelines that have proven useful in enterprise-wide deployments. Organized as follows, these guidelines assume that your production system is implemented either on a unix or linux platform, or in a mixe...

  • Page 206

    When running cognos transformer on unix or linux rather than windows, consider the following: v supported data sources include delimited and fixed-field text files, cognos powerhouse 4gl subfiles, ibm cognos packages and reports, ibm cognos impromptu query definition (.Iqd) files (on ibm cognos seri...

  • Page 207

    Found in the production source which were not found in the prototype source will provide new categories for the unix or linux server model. Ibm cognos impromptu query definition (iqd) files are available only on supported ibm cognos series 7 platforms. Iqd files are not a supported data source on li...

  • Page 208

    After you have the cube designed the way you want it, you can remove the filter using the prompt, or set the filter to the level of data required. 4. Build the cube. 5. If your production server is a different machine than the one on which you built the cube, use your standard file transfer protocol...

  • Page 209

    Environment variables override settings in any preference file, and the command line options override both the preference file settings and the environment variable settings. For example, to override the directory where source files are read, you can include the following unix environment variable d...

  • Page 210

    The temporary file can be used to recover a suspended model at strategic checkpoints if severe errors occur during cube creation. This file has the extension .Qy?, where the ? Is replaced by the character used in your version of cognos transformer. V path1;path2;..."/> this setting specifies where c...

  • Page 211

    This setting specifies the log file name. You can include the full path with the file name. If you do not specify the file name, the output is displayed on the screen, which is the product default. On windows, if the log file name is not specified, a model name or a generated name, such as tfmr0001....

  • Page 212

    Warning these settings control whether cognos transformer issues warnings about potential incremental update problems and ratios between categories and their descendants. V this setting issues warnings when an event is going to take place that will make an incrementally updated cube invalid; for exa...

  • Page 213

    Powercube build performance the following preferences have an impact on powercube build performance. V use this preference to choose between the type of multiprocessing used in this version of ibm cognos bi and the type used in version 8.4. In this version of the product, flat file, package and repo...

  • Page 214

    Available to cognos transformer during the powercube build. Cognos transformer can avoid memory exhaustion if workfilemaxsize is too high; however, if there is not enough physical memory, cube build performance may not be optimal. Environment variables this section describes the environment variable...

  • Page 215

    V you can view the contents of the log file, which contains messages issued by cognos transformer. V if you used a crontab file or scheduled the job with the unix or linux at command, you can check your email for a completion message, and review the log file for any warnings or errors. V you can che...

  • Page 216

    Procedure the following mdl file contains the verb statements that are required to generate categories for the model go_sales.Py?, without creating the cubes for that model. Openpy "go_sales.Py?" populatemodel savepy "go_sales.Py?" if these statements are saved in a file called gen_nat.Mdl, you can ...

  • Page 217

    Test model characteristic description or value total number of categories 492,152 number of non-measure dimensions 5 number of measures 5: 1 regular, 2 calculated, and 2 after-rollup calculated source data format ascii (tilde-character delimited) number of source files 9: 6 structural and 3 transact...

  • Page 218

    Cogtr -mmodel.Mdl & when control returns to the command prompt, initiate a second cogtr command in parallel. V to continue processing on unix or linux after session log off, type the command nohup cogtr -mmodel.Mdl memory allocation it is important to provide sufficient physical memory, on a server ...

  • Page 219

    You may also want to configure the build server to use striping (raid level 0) rather than mirroring, assuming that you have a backup for use if the system crashes during a production build. The build time is reduced because data is distributed among nonredundant disk drives. Allocating sufficient s...

  • Page 220

    On unix or linux servers, the default value for this setting is 32768 (32 mb). You can double the value to 65536 (64 mb) or triple it to 98304 (96 mb) to optimize larger unix or linux systems. To modify the writecachesize setting, open the ppds_cfg.Xml.Sample file located in the installation_locatio...

  • Page 221

    Defined in the cogtr.Sh file, or the environment variables tmpdir, temp, and tmp as defined by the operating system. If multiple variables are defined, cognos transformer uses the first one in the list. The unix or linux command line syntax for specifying global preferences is cogtr -fpreferences.Rc...

  • Page 222

    Using the log file to analyze processing bottlenecks the cognos transformer log file provides useful information to help you diagnose the cause of processing bottlenecks during cube builds. Complete the following procedure: 1. Import these delimited-field data log files into a spreadsheet, such as m...

  • Page 223

    Appendix a. Command line options cognos transformer can perform certain modeling and cube-building tasks from the windows, unix or linux command line. Note: you must invoke the command line utilities from the directory where the cognos transformer executable resides. In cognos transformer version 8....

  • Page 224

    The following example, run from the bin folder where cognos transformer is installed, tests to ensure that your windows operation ended successfully. If a non-zero error code is returned, the process failed, or cognos transformer terminated unexpectedly without completing the specified sequence. Cog...

  • Page 225

    Command line options cognos transformer supports the following windows and unix and linux command line options. Detailed explanations are provided in the subsections that follow. Command line options are case-insensitive. Option meaning -a runs autodesign then creates a cube, opens powerplay (window...

  • Page 226

    Option meaning -j publishes a single powercube or all enabled powercubes in a cube group. Use the -j form of this option to publish new powercubes and keep the previously published cubes unchanged. -j cube_name use the -ju form of this option to update previously published powercubes and cube groups...

  • Page 228

    Use this option with the -d, -f, -r, and -nologo options. Leave a space between the -a and the data source file name. The syntax for using the -a option is as follows: cogtr -a data_source the following windows example opens the sales data source and uses autodesign to place dates in the time dimens...

  • Page 229

    Precedence over the setting in the environment variable, and the setting in the environment variable takes precedence over the setting in cogtr.Xml. If you set a preference more than once in a command line, the last setting overwrites all previous settings. You can use most settings in the cogtr.Xml...

  • Page 230

    Preference or environment variable settings datasourcedirectory default: the data subdirectory in the installation_location/c10 directory dataworkdirectory default: the temp subdirectory in the installation_location/c10 directory decimalpoint default: a period (.) defaultseparator default: a comma (...

  • Page 231

    Preference or environment variable settings thousandseparator default: a comma (,) workfilemaxsize default: 1500000000 minimum: 100000000 maximum: 1500000000 -e option this option updates and saves all the cube metadata that is defined in the model, but does not update the data. The cube metadata co...

  • Page 232

    Tip: you can base your preference file entries on the settings the cogtr.Xml file, and then run batch jobs by referencing the appropriate file using the -foption. Set your environment variables globally or use the -doption for specific command sequences. The syntax for using the -f option is as foll...

  • Page 233

    The following diagram shows the xml schema. The following example shows multiple preferences and commands being passed to cognos transformer. Xmlns:xsi="http://www.W3.Org/2001/xmlschema-instance"> /package[@name='equifaxcube']"/> value="c:\test1.Mdc"/> appendix a. Command line options 219 xmlns:xsi=...

  • Page 234

    Value="true"/> value="true"/> c:\test\cube.Mdc c:\test\cube1.Mdc c:\test\cube2.Mdc 1 4 8 value="true"/> value="true"/> c:\test\cube.Mdc c:\test\cube1.Mdc c:\test\cube2.Mdc value="true"/> c:\test\cube.Mdc c:\test\cube1.Mdc c:\test\cube2.Mdc 1 4 8 publishing in batch-mode the -f command can be used to...

  • Page 235

    Parameter name description cognosconnectiondatasourcename the name of the data source. This is a mandatory value. The set will fail if it is not defined. Default: the name in the model datasourcewindowslocation the windows location of the data source. Default: the current cube location on windows da...

  • Page 236

    Parameter name description packageallownullsuppression the command used to specify whether suppression is available to ibm cognos studio users when working with this package. Default: true packageallowmultiedgesuppression the command used to specify whether ibm cognos studio users can suppress both ...

  • Page 237

    Including cognos transformer prompts in an xml command file you can include prompts in an xml command file. You must use the following command line in order for cognos transformer to read the file: cogtr -fcommand file name where command file name contains a sequence of statements that define prompt...

  • Page 238

    Abc bcd cdf dfe range - form range - to simple prompt: a simple prompt is a variable that has a single value. For example multi-valued prompt: a multi-valued prompt is a variable that has a number of different values. For example value1 value2 value3 range prompt: a range prompt is a type of multi-v...

  • Page 239

    Range-from range-to a range prompt does not need to contain both range-from and range-to values, but it must have one or the other, as shown below. Range-to mun prompt: a mun prompt is a type of single-value prompt. It must refer to a member unique name. For example -g option this option copies newe...

  • Page 240

    On windows, the following example deploys all cubes in sales.Pyj without rebuilding them. Cogtr -g -ox -psales.Pyj the following example copies and activates the child cube named 'france' in a cube group named 'europe'. Cogtr -geurope/france -psales.Pyj -h option this option is displays help for the...

  • Page 241

    The -j form of this option publishes only new powercubes and keeps the previously published cubes unchanged. A data source connection for the powercube is published if a connection with the same name does not already exist. If the data source connection is successfully published, the associated pack...

  • Page 242

    Processes the model in batch mode. The signon sal_log includes the oracle user id corpadm and the password my_pass. The command to process the .Mdl file for model xyzsales.Mdl is as follows: cogtr -c -s -mxyzsales.Mdl -ksal_log=corpadm/my_pass when you use this command, the user id and password appe...

  • Page 244

    In interactive mode, the cognos transformer application window opens and the user is prompted for information, such as logon credentials, in the same way that they are prompted when cognos transformer is opened directly. No information is added to the log file when the supplied logon credentials are...

  • Page 246

    V 1 - includes only severe errors v 2 - includes error messages and level 1 messages v 3 - includes warning messages, and level 1 and 2 messages v 4 - includes all message levels, from informational to severe; the default setting the syntax for using the -r option is as follows: cogtr -rlog_level -p...

  • Page 247

    The following example opens the model year3.Mdl, sets the current period to the category which has a category code of 20061201-20061231, then runs the process in batch mode to create all defined cubes. Cogtr -n -t"20061201-20061231" -syear3.Mdl -u option the -u option writes the partition informatio...

  • Page 248

    Because the option syntax does not reference a model file, -v is used in combination with -c, -m, or -p, each of which does reference a model file. The syntax for using the -v option is as follows: cogtr -vdata_subset_number the following example processes a subset of the records in the binary model...

  • Page 249

    The -y1 option requires that you configure the ibm cognos series 7 security on which the upgraded model was designed as an available namespace in ibm cognos. The unique identifier that locates the user class in access manager is converted to an ibm cognos identifier, and this process will fail if yo...

  • Page 250

    Backward compatibility cognos transformer supports previous versions of windows and unix or linux preference files. Sample windows preference files for ibm cognos series 7 the command line preference files for windows in this version of cognos transformer are the same as those for previous versions ...

  • Page 251

    Diagscalefont=0 diagfont= diagfontweight=0 diagfontcharset=0 modelsavedirectory= datasourcedirectory= diagfontitalic=0 fullanimation=1 multifilecubethreshold=0 introduction_on=1 dspreviewshowbydefault=0 dspreviewsamplesize=100 dspreviewcolumnwidthrule=34 ordercategoriesbydefault=0 workfilesortbuffer...

  • Page 252

    Workfilesortsize=8000000 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

    Appendix b. Troubleshooting this section describes issues and limitations that may be encountered by cognos transformer users working in a powerplay, and ibm cognos bi environment, with suggested workarounds. Accessing error message help error message help is provided in cases where the basic error ...

  • Page 254

    [tr1907] transformer cannot gain access to database database_name with signon information . The database connection fails because quotes are added to the sql query when the data source is created in framework manager. To successfully connect to the iqd data source and import the model, you must firs...

  • Page 255

    To avoid this problem, you have two choices: v you can open the cogtr.Xml.Sample file in a text editor, search for the string objectidoutput, and change the setting to 0. Save the cogtr.Xml.Sample file as cogtr.Xml . Restart transformer, and resave the model. V you can use your rdbms or a tool such ...

  • Page 256

    Ensure that the machine where transformer is installed has sufficient memory to perform the import. If physical memory is limited, transformer may perform the operation very slowly. In this situation, you can end the task using task manager. Categories missing when creating a transformer model based...

  • Page 257

    You want to use them as data sources to create content in the ibm cognos bi environment, you must generate the cubes and then publish the cube group. Procedure 1. Open the employee expenses model in ibm cognos transformer. 2. Generate the powercubes. All powercubes in the model are generated. 3. Pub...

  • Page 258

    – 20 if an error occurred while accessing the powercube v -v or verbose mode; text is output to standard output (stdout), indicating what was done, including any error messages. If running in interactive mode, -v is assumed. All text is output in english only. V -h for command-line help; if cubename...

  • Page 259

    For example, if the actual revenue measure format specifies two decimal places and the usd currency in the currency table specifies no decimal places, two decimal places will appear in the usd currency value. V calculations that include a division operator and at least one currency operand will now ...

  • Page 260

    To avoid this problem, set up great outdoors sales.Mdl using the following steps: procedure 1. Modify the cs7g.Ini to contain [databases] connections. The cs7g.Ini file is located in the installation_location/c10/cs7gateways/bin directory. 2. Open odbc data source administrator and create a new odbc...

  • Page 261

    Appendix c. Ibm cognos transformer error messages this section lists error messages in ibm cognos transformer for which additional help is provided. Accessing error message help error message topics are provided in cases where the basic message is not sufficient. Their purpose is to identify the pos...

  • Page 262

    Procedure if you require access to a category that is inaccessible due to access controls placed on its ancestor, you can v remove the restrictions imposed by the ancestor custom view v create a new custom view at the ancestor level, with the appropriate security v selectively add users from the low...

  • Page 263

    This error message may appear when you make updates to a model using an mdl script. Under normal circumstances, the cognos transformer windows interface prevents you from including a measure that is excluded in an ancestor custom view. Procedure 1. Verify that the mdl script is correct, and that you...

  • Page 264

    One of your source files may have been inadvertently overwritten by an empty file, or the process that creates your source files may have failed and created a file with no records. Procedure to resolve the problem, ensure that each source file associated with the data in your model contains the corr...

  • Page 265

    Tr0113 cognos transformer cannot create the file file_name. Cognos transformer is unable to create a file. Procedure to resolve the problem, ensure that you are not attempting to overwrite a read-only file, and that the file is not currently locked by another process. Tr0114 cognos transformer canno...

  • Page 266

    2. If you are using cognos transformer to generate a powercube on an hp-ux 10 server computer, and the data source is an .Iqd file that accesses a sybase database, ensure that the following requirements are met: v open client must be installed on the hp-ux server computer. V the sybase and dsquery e...

  • Page 267

    Tr0137 cube group cube_group_name (and possibly others) contains cubes based upon key orphanage categories. Due to the temporary nature of these categories, the data in these cubes may belong to other cubes within their respective cube groups. Cognos transformer has created a key orphanage in the ta...

  • Page 268

    Tr0202 you did not include any dimensions. Cognos transformer cannot create a powercube. You have tried to create a powercube in which all the dimensions are omitted. Procedure to resolve the problem, open the powercube property sheet and click the dimensions tab. Ensure that at least one dimension ...

  • Page 269

    You have tried to generate categories using a data source that is not associated with any level in any dimension in the model. This can happen when you add a data source to the model without specifying that at least one dimension is derived from that data source. Procedure to resolve the problem, as...

  • Page 270

    Procedure use the logged information to identify which input data records contain invalid source values, and then make the required corrections. Tr0214 the temporary file for source file source_file_name and powercube cube_name is empty. Check that your source file contains data. During the powercub...

  • Page 271

    Procedure ensure that the powercube creation box on the general tab of the data source property sheet is selected, if you want this data source processed during cube generation. Tr0301 there are no columns for autodesign to use. Cognos transformer is unable to locate columns for use in the autodesig...

  • Page 272

    Tr0412 error encountered while trying to save preferences to path_name\cogtr.Xml this error may be caused by insufficient space on your hard disk or by an inaccessible cogtr.Xml file. Procedure if the problem is caused by insufficient disk space, either create more disk space or switch processing to...

  • Page 273

    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. Tr0476 a category set was expected but not found. Either you have referred to a category set by an object name or object ide...

  • Page 274

    You have tried to create a category manually, in a manual level, and you have omitted the category code. By default, cognos transformer will try to assign a unique category code to each of the categories you create. Procedure this category code is displayed in the category code box on the general ta...

  • Page 275

    The category values in a convergence level that connects multiple drill-down paths must be unique. Similarly, in order for cognos transformer to be able to directly relate source column values to categories in a level, the category values in that level must be unique. Procedure if a level is at the ...

  • Page 276

    Procedure 1. To view the object identifier for each object, select the object identifier check box 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 ...

  • Page 277

    You have tried to create a subdimension in one of the paths of an alternate drill-down structure. Subdimensions are permitted in dimensions with only one drill-down path or, in an alternate drill-down structure, at or below the convergence level. Procedure to avoid this problem, ensure that subdimen...

  • Page 278

    This error occurs when a model definition language (mdl) script requires an object and is unable to find it. In this case, the object is a category. The problem may be that the category was not referenced, or that it was referenced with an incorrect object name or object identifier. Procedure 1. You...

  • Page 279

    You have tried to create a new category below the convergence level of an alternate drill-down structure, and the new category descends from a category above the convergence level of an alternate drill-down structure. This is not a valid action. Procedure 1. Instead, on the diagram, create a new cat...

  • Page 280

    Tr0540 changing the primary drill-down path from drill_down_pathname will cause any allocations and partitioning information to be removed from the current primary drill-down path. Do you want to continue? Cognos transformer supports partitioning and measure allocation only in the primary drill-down...

  • Page 281

    Tr0605 a column was expected but not found. Either you have referred to a column by an object name or object identifier that cognos transformer is unable to locate, or you have not referenced the column. This error occurs when mdl syntax requires an object and is unable to find it. In this case, the...

  • Page 282

    Procedure 1. If your data source is an .Iqd file and you have ibm cognos impromptu installed, you can resolve the problem in the source file. Change the data definition for the column using the string-to-number function, save the .Iqd file, and then use cognos transformer to replace the faulty colum...

  • Page 283

    Procedure to resolve the problem, either remove the unwanted association or make the association reference a different data source. Tr0624 one association_type association named association_name is already defined. This error occurs v on the windows interface, if you try to create an association whe...

  • Page 284

    Matching model and source columns for ibm cognos data sources 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 list, select the query whose columns you want to modify, and from the tools menu, click modify...

  • Page 285

    V if you set the inclusion setting on the category property sheet to either suppress or exclude for a category in the level that defines the cube group v if you use the diagram to either suppress or exclude one of the categories that defines a cube within a cube group for example, suppose that you c...

  • Page 286

    You have tried to create a cube group, but no cubes were created. The problem may be that the category or level used to create the cube group is suppressed, excluded, or cloaked. Procedure modify your model design to avoid creating an invalid cube group structure. Tr0713 in cube group cube_group_nam...

  • Page 287

    Tr0749 the currency table populate failed. This error occurs when cognos transformer cannot find the source files needed to populate the currency table. Procedure to resolve the problem, ensure that the data file name and path are correct or, if the currency source is an .Iqd file, check the databas...

  • Page 288

    Tr0753 the conversion rate entered is invalid. It either contains invalid characters or is less than or equal to zero. The source file that supplies values for the rate column contains invalid data. Procedure to resolve the problem, ensure that all conversion rates are positive numbers. The data can...

  • Page 289

    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. Tr0803 more than 1000 da...

  • Page 290

    You have specified a date that is not valid, or specified a date prior to 1990 when using the clean house command. Procedure ensure that the date is later than 1989, either by typing it in the format configured for your system, or by selecting a valid date from the embedded calendar. Tr0807 the earl...

  • Page 291

    Procedure 1. To resolve the problem, structure your time dimension so that date level functions are specified correctly. Ensure that within a single drill-down path, all date functions are exclusively lunar periods or calendar periods. 2. In addition, if you have a time period in which alternate dri...

  • Page 292

    Tr0815 the interval between the year start-days for alternate drill-down paths drill_down_name1 and drill_down_name2 in dimension dimension_name must be equal to one or more whole units of time as represented by the convergence level. You have created an alternate drill-down structure in the time di...

  • Page 293

    You have made changes to the range of dates cognos transformer uses to generate date categories. Procedure ensure that you do not inadvertently change these default values. If the changes are intentional and you proceed, all the categories in your time dimension will be deleted. Tr0904 the date leve...

  • Page 294

    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 to...

  • Page 295

    You have tried to use an.Iqd file as a data source, but the contents of the .Iqd file have become corrupted. Procedure verify that you are using the correct .Iqd file. If you have ibm cognos impromptu installed, try to recreate the file, or investigate using framework manager to resolve the problem....

  • Page 296

    There are several conditions that could cause this error. However, this error message is generally followed by one or more additional messages. On the windows interface, you can search the index for information about these error messages, organized by tr number. Procedure for information about the m...

  • Page 297

    You have tried to delete a measure that is used as a weighting for a regular, time state , or duplicates rollup. Procedure before you try to delete a measure, make sure it is not being used as a weighting factor for another measure. Tr1308 this measure is referenced by calculated measure measure_nam...

  • Page 298

    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. Tr1312 the type of time state rollup used must be the default because there are multiple time dimensions. Your model contain...

  • Page 299

    In constructing the data sources for your model, you have created a measure-containing (transactional) data source that provides only category values in alternate drill-down paths. However, for measure values to be written to the powercube, they must be associated with the primary drill-down path. P...

  • Page 300

    Procedure to resolve the problem, you can restore the model from a .Mdl backup file, or upgrade to the current version of cognos transformer (.Pyj) and migrate your models as .Mdl files. Tr1700 the category category_name in the dimension dimension_name has n immediate descendants. This number should...

  • Page 301

    Procedure to resolve the problem, click ok at the prompt to proceed with the incremental update and click cancel to set the powercube status to invalid. You must then adopt the cube, to reset the status to ok. Tr1903 incremental update of cube_name cannot proceed because of the status_name status of...

  • Page 302

    In a powercube group, you have tried to omit the dimension containing the level that defines the group. Procedure you cannot omit this dimension. If you want to omit a portion of the dimension, use a custom dimension view. Be aware, however, that omitting one of the categories that defines a cube in...

  • Page 303

    This error occurs when mdl syntax requires an object and is unable to find it. In this case, the object is a level. The problem may be that the level was not referenced, or that it was referenced with an incorrect object name or object identifier. Procedure you can verify cognos transformer object n...

  • Page 304

    Tr2313 you are creating a level as a convergence level that connects two or more alternate drill-down paths. Categories in convergence levels must have unique source values. Is this level truly unique? You have created a convergence level but have not designated it as unique. Uniqueness is set by se...

  • Page 305

    Tr2317 the level level_name is designated as unique. Source value source_value was used in an attempt to create a category in the path drill_path_1. Source_value already exists in level level_name in the path drill_path_2. This error indicates that you have a problem related to level uniqueness. Thi...

  • Page 306

    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 transformer detects that it is not v there is some other problem with the source data procedure to resolve the problem, exam...

  • Page 307

    Tr2323 the share object id is invalid. A share object must be an object identifier of a category or level in the same dimension. You have tried to specify an invalid share. A share object must be v an existing category or level v at a higher level in the same dimension v specified by its object iden...

  • Page 308

    You have specified a column to supply a description for the categories in a level, and cognos transformer is unable to find the column that you specified. Procedure to avoid this problem, ensure that the column name you specify matches one that is defined in a data source for your model. Tr2502 a vi...

  • Page 309

    Procedure if you encounter this error, ensure that the category object identifier that you specified in the catupdate or catmake statement does not refer to a special category. You can find object identifiers in the .Mdl file. For more information, see the cognos transformer developer guide. Tr2505 ...

  • Page 310

    Tr2508 this view is in use. If it is removed, references to it will use all categories. Do you want to remove it? You have tried to delete a dimension view that is currently being applied to one or more of the cubes in the powercubes list. By deleting the view, you remove any restrictions (exclude, ...

  • Page 311

    There may be a problem with the temporary files created during the cube-building process. Procedure 1. Check the location of the temporary files, as specified on the directories tab of the preferences property sheet; for example, data temporary files (dir1;dir2). If a directory is not specified, cog...

  • Page 312

    You have asked cognos transformer to view any suspended models that exist, and cognos transformer cannot locate any suspended models. By default, cognos transformer stores suspended models with the extension .Qy? In your temporary directory. Procedure if you know that suspended models should exist b...

  • Page 313

    Tr2702 there isn't a data source that is associated with dimension dimension_name. The specified dimension has no data sources associated with it. Procedure to resolve the problem, ensure that v you have included all the data sources required for each dimension in your model v the column names for e...

  • Page 314

    For example, suppose two data sources are used to generate categories for a products dimension that contains the levels product line, product brand, and item. If one data source contains values that generate categories down to the item level, and the other data source contains values that generate c...

  • Page 315

    For example, suppose you create a calculated measure m1 with a regular timing of before rollup. You then create calculated measure m2 with a regular timing of after rollup, and you reference m1 when defining m2. The rollup timings conflict. Procedure to avoid this problem, ensure that the rollup tim...

  • Page 316

    Procedure to resolve the problem on the windows interface, open the property sheet for each of the data sources in your model. On the general tab, ensure that the generate categories check box is selected for each data source that provides category values for the model. Tr2712 there are no data sour...

  • Page 317

    Procedure to resolve the problem, specify a valid measure allocation regime that does not include a circular reference. Tr2715 measure measure_name1 is allocated by the calculated measure measure_name2. Measure measure_name1 is a component of the calculated measure. This error occurs because you hav...

  • Page 318

    Dimension. These categories become leaf level categories. Cognos transformer does not allow partitioning on leaf level categories. Procedure to resolve the problem, you can do one of the following: v completely repartition the model, to avoid partitioning on the categories that become leaf level cat...

  • Page 319

    Cognos transformer does not allow dimension views that use summarize or cloak for time dimensions that specify a time-state rollup for one or more measures. Procedure to resolve the problem, do one of the following: v eliminate the summarized or cloaked view from the time dimension. V eliminate the ...

  • Page 320

    For example, in a time dimension, you assign partition level 1 to the years 2000 through 2005, and then use the same level number for the quarters in 2006. This is valid. However, having assigned level 1 to a year, you cannot then assign the same level number to any category in any lower level of th...

  • Page 321

    Procedure check the entry for the database in ibm cognos configuration or the [powerplay server list] section of the .Ini file. It must match the database definition specified in ibm cognos impromptu when the .Iqd file was created. Tr2729 measure measure_name has a time state rollup specified. This ...

  • Page 322

    For example, suppose you create a cube group based on the level state in the regions dimension. The level contains three categories: california, new york, and massachusetts. You cannot then apply a dimension view that suppresses or cloaks any of those three states. Procedure to resolve the problem, ...

  • Page 323

    Tr2736 measure measure_name1 has an average time-state rollup with a weighted measure measure_name2 specified. Both measures must exist in the same data source in order for the weighted measure to be applied. The measure used to provide the weighting for another measure is not found in the same data...

  • Page 324

    Tr3101 cognos transformer could not create the directory directory_name. You may have insufficient space in your temp directory or on the drive where the temp directory resides. Procedure to resolve the problem, try the following: v free up some disk space. V check the size of, and available space f...

  • Page 325

    If you have specified incorrect parameters for your function, this error message will appear. For example, the cognos transformer-specific share function requires the first parameter to be a child category of the second parameter. Reversing the order of the child and parent categories within this fu...

  • Page 326

    312 ibm cognos transformer version 10.1.1: user guide.

  • Page 327: Appendix D. Reference

    Appendix d. Reference this section provides a description of the cognos transformer user interface, as well as additional information about the interface, modeling concepts, and database terminology to help you create powercubes for use in ibm cognos. This information supplements the more task-orien...

  • Page 328

    Data sources the data sources list shows all the data sources used to generate model categories, associate measure values, and create powercubes, along with their source columns. The icons in the data sources list indicate whether the object is a regular data column or a calculated column . If the d...

  • Page 329

    Other lists a powercubes list shows the cubes or cube groups associated with the model. A custom views list shows versions of the cube filtered for specific reporting purposes. For more information about custom views, see chapter 7, “adding security,” on page 147. A signons list shows v all the data...

  • Page 330

    Use the dimension map to define, check, or update the structure of your model. For example, you can v add, move, or remove entire dimensions or the layered levels within them v create or remove alternate drill-down paths to support multidimensional analysis v create a time dimension that contains th...

  • Page 331

    Icon description a time dimension a locked dimension a manual level ellipsis points (...) following a level name indicates a subdimension. When modeling on the dimension map, you can v create a dimension map that reflects the structure of your dimensional data source v drag a column from the data so...

  • Page 332

    For data sources, the level is not related to the source column by name but a lower level is, meaning that the source records can be matched unambiguously to categories in the level. By default, this level appears with light yellow shading. V level has allocated measures. The values for the measure ...

  • Page 333

    V views based on a configured cognos namespace on the custom views tab (left pane) v all the levels and categories in the selected dimension, in the category viewer (right pane) you can access the main menu, toolbar buttons, and pop-up menus from any pane and use these controls to fine-tune your mod...

  • Page 334

    Tip: if you use the diagram to add a special or manual category with descendants, check the scope map to ensure that your additions do not conflict with other categories in the model, or cause uniqueness problems. Connect manual levels using the diagram after adding special or manual categories to a...

  • Page 335

    Exclude the exclude action eliminates categories and their descendants. The cognos transformer diagram shows categories and hides their descendants. Report users can drill down only to the parent of the excluded category. You can apply the exclude action at the dimension level of the category viewer...

  • Page 336

    Summarize the summarize action eliminates descendants, but rolls up their values for representation in the selected category. The cognos transformer diagram shows categories. Report users can drill down only to the summarized category. They cannot see any of the category descendants. You can apply t...

  • Page 337

    Inclusion setting description suppress (categories only) excludes the category from the cube but retains its descendants and all data values associated with them. The immediate descendants appear in reports at the same level as the excluded category. The result is identical to using the suppress opt...

  • Page 338

    Date setting description from windows control panel as defined in regional settings. The value is taken from the short date style box on the date tab, or its equivalent entry on your version of the windows operating system. You can change this setting on the general tab of the preferences property s...

  • Page 339

    Tip: you can combine codes. For example, use yyyy mm dd to show dates in the format 2007 jan 01 and use yy "q"q to show dates in the format 07 q1. For lunar years, quarters are labeled q1-4, months are labeled 1-12 or 1-13, and days are labeled 1-28. Date functions the following table describes the ...

  • Page 340

    Object default file location (windows 2000/ windows xp) powercube my documents\transformer\powercubes data temporary file temporary files location as specified in ibm cognos configuration model temporary file temporary files location as specified in ibm cognos configuration logs my documents\transfo...

  • Page 341

    File, the updated file is saved by default to a virtual store directory and not to the installation_location/configuration directory. Global preference settings you can globally change the cognos transformer preference settings stored in the cogtr.Xml file from the command line or using the preferen...

  • Page 342

    Mdl save option setting use the cogtr.Xml file to specify the manually maintained mdl save options. V to set mdl save actions to use verb instead of structured format, type name="verboutput" value="1"/> v to set mdl so that it does not save the object identifiers, type transdapath this setting appli...

  • Page 343

    Relative time setting description last period the last period at this level. For example, if the current date is 2007/05/30 - the last month category is 2007/04 - the last quarter category is 2007 q1, assuming a standard year of four quarters beginning in january - the last year category is 2007 sam...

  • Page 344

    V basic approach = single category v target period = month v target offset = -2 v context period = year v context offset = 0 2. You also need a time period for 2006/10, which is two quarters ago, one month before the month in which the current period occurs in the current quarter. You specify the fo...

  • Page 345

    Powercube optimization methods several types of optimization methods are available for use in your models. Method description auto-partition enables the auto-partition tab, where you can set the parameters for cognos transformer to devise a partitioning scheme. This is the default optimization setti...

  • Page 346

    Note: when you enable consolidation on the general tab of the powercube property sheet, cognos transformer performs the duplicates rollup first, followed by the regular rollup. Regular rollup the following table lists the regular rollup options and explains how the results appear in the reporting co...

  • Page 347

    Time state rollup option results in reporting component first period shows the measure value from the first subordinate period in the time period being examined. For example, a time dimension contains years, quarters, and months, and you are examining data at the quarter level. For each quarter, the...

  • Page 348

    A duplicates rollup setting other than none forces cognos transformer to consolidate the source file when generating cubes, as described in the following table. Consolidation setting cognos transformer action default or yes (with sort) sorts the source file, then consolidates duplicate records by us...

  • Page 349

    Search character search results example ~ zero or one occurrence of the preceding character or sub-expression files~ finds file and files, but not filed. * zero or more occurrences of any characters except newline can* finds can and canada. # zero or more occurrences of the preceding character or su...

  • Page 350

    Before you can use the sample databases, ibm cognos must be installed, configured, and running, and the samples must be set up. For more information, see "setting up the samples" in the installation and configuration guide. Sample models cognos transformer includes several sample models. Model name ...

  • Page 351

    Sample data sources cognos transformer includes several sample data sources. Data source name type location employee expenses list report ibm cognos connection, in the go data warehouse (query) , report studio report samples folder health insurance list report ibm cognos connection, in the go data w...

  • Page 352

    338 ibm cognos transformer version 10.1.1: user guide.

  • Page 353: Use In Transformer

    Appendix e. Guidelines for working with sap bw data for use in transformer starting with transformer version 8.4, you can use framework manager packages published to content manager to leverage your sap bw data. The sap-based packages can be used as data sources to create transformer models. As resu...

  • Page 354

    Before you begin you can use cognos transformer to import both dimensional and fact data from an sap bw query source. The following instructions describe how to rebuild an sap bw cube as an ibm cognos transformer cube. To do so, the sap bw query package must be in a specific format. There are three ...

  • Page 355

    V if you include 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 returned sap bw data. You can use this as a filter to define a subset of an infocube. V use a picklist prompt, rather than a typ...

  • Page 356

    You must choose carefully which dimension to define the variable on. It may require some experimentation to achieve optimal performance. For example, you may have a country or region dimension that contains three countries or regions as members, united states (us), canada (ca), and mexico (mx). If m...

  • Page 357

    7. Select the variable and click the right arrow to move the selected variable to the selection window. Save the query. You are now ready to import the query in framework manager. Creating a package in framework manager before you create a package in framework manager, you must first import the sap ...

  • Page 358

    5. Select the main query items that directly relate to the level. That is, those labeled (key), (name), and so on. Tip: secondary or additional attributes are removed on import to cognos transformer. Only items that are needed are imported. However, to improve performance, we recommend that you do n...

  • Page 359

    Create a package use the following steps to create a package in framework manager using sap bw metadata. Procedure 1. Click the packages folder, and from the actions menu, click create, package. 2. In the provide name page, type the name for the package and, if you want, a description and screen tip...

  • Page 360

    5. Choose whether to use the default access permissions 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 guid...

  • Page 361

    7. Select the default values, and click publish. This will publish the package to the content store, and will allow you to access the package in cognos transformer. 8. At the final screen verify the results, and click finish. Results you are now ready to create a model in cognos transformer. For mor...

  • Page 362

    V ensures that the scope is set properly between the dimensions and facts. V populates the dimension with the appropriate business key and caption information. V only imports the necessary items from the bw package required for cube building, when the metadata is imported. This reduces the number of...

  • Page 363

    Results you can now use this model to create powercubes for use with the desired ibm cognos component, accessing the dimensional and fact data from the original sap bw data source. For more information, see the section "create a model" in the cognos transformer user guide. Working with sap bw data u...

  • Page 364

    Model query subjects are extracted using the same mechanism by which queries are executed within ibm cognos. Therefore, the extract size property has no effect on the query execution. Measure dimensions when extracting a measure dimension, you should create a model query subject containing the measu...

  • Page 365

    Sap bw query requirements for the remainder of this section, we assume that an sap bw query is being used as the basis for externalizing the data, not as the basis for reporting, and not with the intent of exceeding the memory and time limitations associated with extracting data directly from an inf...

  • Page 366

    V null values v # symbols v rest_h v not assigned because such duplicate tokens can cause problems in cognos transformer, in unique levels for example, we recommend that you assign filters to the dimension so that they do not appear in the imported data. Finally, remember to select only those query ...

  • Page 367

    With a variable. Thus, by choosing an appropriate characteristic, the key figures dimension can be extracted without exceeding the memory restrictions of either the client or server. Memory caches on the client and server are flushed after each query. If a presentation hierarchy is used to drive the...

  • Page 368

    V during the import of sap bw metadata into a model that will extract data, limit the model to only those query items that are absolutely required to build a powercube. This will improve data extract performance. V null values are included in csv files when externalizing sap bw-based query subjects ...

  • Page 369

    Appendix f. Ibm cognos transformer expression editor the cognos transformer expression editor is not the same expression editor as the one used in framework manager or the web studios. The expression editor can be invoked from a number of places within cognos transformer. The functionality varies de...

  • Page 370

    V the type of data class (numeric, date, or text) v the type of data column v whether the calculation includes an if-then-else conditional construct as you add or type each component in the expression, the syntax is evaluated against mathematical rules. The tips window of the expression editor provi...

  • Page 371

    Building an if-then-else calculated expression your models can include calculated measures that use if-then-else expressions. For example, you can define the following conditional expression: if ("net income" null you can also define an if-then-else calculation to avoid division by zero: if ("revenu...

  • Page 372

    Function description numeric functions accepts numeric input and returns numeric values. Text functions accepts character input and can return both character and number values. Aggregate functions executes a predefined function and returns an aggregate value. Date functions date functions return a v...

  • Page 377

    Syntax absolute (numeric_exp) examples absolute (-5.3) returns the result: 5.3 absolute (2) returns the result: 2 ceiling returns a number rounded to the next highest integer. Syntax ceiling (numeric_exp) examples ceiling (-1.23) returns the result: -1 ceiling (1.23) returns the result: 2 floor retu...

  • Page 378

    Mod returns the remainder (modulus) of an integer divided by a second integer. If the second integer is zero, cognos transformer issues a divide by zero error. Syntax mod (integer_exp1, integer_exp2) examples mod (245,3) returns the result: 2 number-to-string returns a string from a number. If the n...

  • Page 379

    Returns the result: 366 round-down (366.2162, 1) returns the result: 366.2 round-down (366.2162, -1) returns the result: 360 round-down (366.2162, -2) returns the result: 300 round-near returns a number rounded to the nearest value. The integer_exp value determines the position that is rounded. A po...

  • Page 380

    An 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 the decimal places. Syntax round-up (numeric_exp, integer_exp) examples round-up (-113.6667, 0) returns the result: -113 round-up (-113.6667, 1) returns the result: -1...

  • Page 381

    Returns the result: -100 round-zero (366.2162, 0) returns the result: 366 round-zero (366.2162, 1) returns the result: 366.2 round-zero (366.2162, -1) returns the result: 360 round-zero (366.2162, -2) returns the result: 300 sqrt returns the square root of a positive number. Syntax sqrt (numeric_exp...

  • Page 382

    Function description trim-leading returns a string with leading spaces removed. Trim-trailing returns a string with trailing spaces removed. Upper converts lowercase characters to uppercase. Note: the pack, spread and substitute text functions are not supported in cognos transformer version 8.X and ...

  • Page 383

    Syntax lower (string_exp) example lower (’abcdefg’) returns the result: abcdefg position returns the starting position of string_exp1 in string_exp2. The first character in a string is at position one. Syntax position (string_exp1, string_exp2) examples position (’def’, ’abcdef’) returns the result:...

  • Page 384

    Example string-to-integer (’ 101 tents’) returns the result: 101 substring returns a substring from a string. The first character in the string is at position one. Syntax substring (string_exp, integer_exp1, integer_exp2) where: v string_exp is the string from which you want to extract a substring v...

  • Page 385

    Examples upper (’auriga’) returns the result: auriga upper (’deer’) returns the result: deer aggregate functions executes a predefined function and returns an aggregate for a set of values based on the function values. Note: you can access aggregate functions through calculated categories, calculate...

  • Page 386

    Note: this function is used for automatically generated relative time categories, such as year-to-date (ytd change). It appears in the list of available components when you use the expression editor to create a calculated category. Tip: to use tooltip on the pop-up diagram to show the labels and obj...

  • Page 387

    Sales rep sales 07 percentage bill gibbons 60646 7.11% bjorn flertjan 62523 7.35% chris cornel 22396 2.63% conrad bergsteige 13500 1.59% percent-growth returns the growth over a linear series of categories by calculating the percentage change between each pair of categories. The order of pairs is ba...

  • Page 388

    Constant description single category inserts an expression into a category. Number inserts the number zero, which you can replace with a new numeric value. String inserts two quotation marks and positions the cursor between them. Operators operators specify what happens to the values on either side ...

  • Page 389

    Example name = ’smith’ or name = ’wong’ retrieves the data for the names smith and wong. Not logical operator returns true if the condition is false. Example not ( "state" ’ca’ ) returns true for all states that are not california. String operator (+) concatenates two strings together. Syntax char1 ...

  • Page 390

    Syntax 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: Notices

    Notices this information was developed for products and services offered worldwide. Ibm may not offer the products, services, or features discussed in this document in other countries. Consult your local ibm representative for information on the products and services currently available in your area...

  • Page 392

    Ibm may use or distribute any of the information you supply in any way it believes appropriate without incurring any obligation to you. Licensees of this program who wish to have information about it for the purpose of enabling: (i) the exchange of information between independently created programs ...

  • Page 393

    Ibm trademarks is available on the web at “ copyright and trademark information ” at www.Ibm.Com/legal/copytrade.Shtml. The following terms are trademarks or registered trademarks of other companies: v adobe, the adobe logo, postscript, and the postscript logo are either registered trademarks or tra...

  • Page 394

    380 ibm cognos transformer version 10.1.1: user guide.

  • Page 395: Glossary

    Glossary this glossary includes terms and definitions for ibm cognos transformer. The following cross-references are used in this glossary: v see refers you from a term to a preferred synonym, or from an acronym or abbreviation to the defined full form. V see also refers you to a related or contrast...

  • Page 396

    G governor a set of rules to limit user activities, such as the execution of reports, that either take too long or consume too many resources. H hierarchy the organization of a set of entities into a tree structure, with each entity (except the root) having one or more parent entities and an arbitra...

  • Page 397

    Q query subject a named collection of query items that are closely functionally related. Query subjects are defined using framework manager to represent relational data and form the set of available data for authoring reports in query studio and report studio. A query subject is similar to a relatio...

  • Page 398

    384 ibm cognos transformer version 10.1.1: user guide.

  • Page 399: Index

    Index special characters .Mdl errors calculations with double quotation marks 240 .Xml files global preference settings 326 a absolute function 362 activating new versions of powercubes 187 add-days function 359 add-months function 359 add-years function 359 adding object security 150 age function 3...

  • Page 400

    Changes to decimal formats 244 changing data source types 33 char_length function 368 characteristic dimensions externalizing 350 characters sets supported by delimited text files 41 check model command using to find problems in drill-down paths 69 using to verify design 90 checking cube status 145 ...

  • Page 401

    Cubes (continued) creating views to summarize values for specific categories 138 creating views to suppress specific categories 135 customizing time-based partitioned cubes 131 data consolidation 178 design/build considerations 123 disabling cube build using -ox option 231 excluding specific measure...

  • Page 402

    Decimal formats changes 244 decimal places defining column parameters 50 degree of detail setting for date columns 51 delimited text files supported character sets 41 delimiters quotation marks in mdl 334 deploying new versions of published powercubes 187 detailed fact query subject 340 detailed key...

  • Page 403

    Functions (continued) right 369 round-down 364 round-near 365 round-up 365 round-zero 366 sqrt 367 string-to-integer 369 substring 370 supported expressions for calculated measures 100 text 367 today 361 trim-leading 370 trim-trailing 370 type 357 uppercase 370 year 362 years-between 362 g gateways ...

  • Page 404

    Lunar weeks adding to resynchronize with calendar year 77 m maintenance cubes and models 169 incremental cube updates 180 optimizing production environments 191 routine cube modifications 179 routine model cleanup tasks 175 routine task checklist 161 setting environment variables 200 setting global ...

  • Page 405

    N named ranges multipage crosstab (example) 41 specifying 41 spreadsheet database (example) 42 new features overview of cognos transformer version 8.X interface 313 version 10.1.0 1 version 10.1.1 1 not logical operator 375 null values time dimensions 71 number-to-string function 364 numeric functio...

  • Page 406

    Powercubes (continued) sorting in a slowly changing dimension (example) 130 sorting time-based partitions (example) 129 transformer guidelines 354 troubleshooting slow build times 202 troubleshooting time-based partitioning issues 128 updating metadata 144 using alternate data sources when creating ...

  • Page 407

    Round-up function 365 round-zero function 366 run autodesign command -a command line option 214 advantages and limitations 57 s samples cognos transformer 336 employee expenses.Mdl 243 transfomer data sources 337 transfomer models 336 transfomer powercubes 336 sap bw guidelines for using fact data 3...

  • Page 408

    Sybase data source connection troubleshooting 240 symbols comparison operator 375 synchronizing data source with model columns 34 syntax command line 210 syntax conventions quotation marks in mdl 334 t tempfiledir setting 199 temporary files allocating sufficient space to optimize processing 205 tes...

  • Page 409

    Tr0705 272 tr0713 272 tr0745 272 tr0749 273 tr0750 273 tr0751 273 tr0752 273 tr0753 274 tr0787 error transformer 242 tr0800 274 tr0802 274 tr0803 275 tr0804 275 tr0805 275 tr0806 276 tr0807 276 tr0808 276 tr0809 276 tr0810 277 tr0811 277 tr0812 277 tr0813 277 tr0815 278 tr0816 278 tr0817 278 tr0900 ...

  • Page 410

    Transformer (continued) unable to open iqd file 240 transformer version 7.X externalized csv files 349 transformer version 8.X externalized csv files 349 trim-leading function 370 trim-trailing function 370 troubleshooting accessing error message help 239 checking cube status 145 common unix process...

  • Page 411

    Y year function 362 years-between function 362 index 397.