Even if you build your dimensions and measures from in-scope data source columns, you may have to change your column definitions to ensure that your model delivers solid business value.
Source columns contain not only the text, date, and code values that become the categories in your model, but also the numeric values that you select as measures, or performance indicators. Data columns can also contain values that you may want to use as alternate labels, short names, or textual descriptions for categories.
If you need more structural or numeric information in your model than is available from your source data, you may be able to add it by using calculated columns. For more information about the functions you can use when creating calculated columns, see IBM Cognos 8 Transformer Expression Editor.
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, Transformer can automatically identify columns and assign default column names and properties. However, if your data sources include IBM Cognos 8 packages or reports, you select specific query objects from an available list.
Any time that you create columns manually, as you must for fixed-field text files, you must identify only those data columns that are used in the model. You can change these assignments later.
When an identically named column appears in two or more data sources, Transformer associates each column with a level that has its source column set to the common name. However, it cannot use matching columns to perform joins on the source files. Instead, it uses the common name to associate both columns with categories in the dimension map, or with a measure in the model.
For example, suppose your model has an Order Header data source and an Order Detail data source. Each contains a column named ORDER_NUMBER. Transformer cannot join an Order Header record with an Order Detail record to create a composite record describing a sales line in an invoice. However, Transformer associates the values from both of these sources with a specific level in one of the dimensions in the model.
To create a join between two columns, use a database query tool such as IBM Cognos 8 or IBM Cognos Impromptu before you create the data source.
To obtain the data required to populate the time dimension for your model, you need at least one data source that includes the required date values. However, in a model with multiple data sources, date columns may appear in several source files, and date columns may not relate to your chosen time dimension.
For example, suppose your sales-analysis model contains one source file with order information, including the order date, and another that contains sales forecasts by time period. For Transformer to relate values from both sources to your chosen time dimension, you must rename the columns that contain the date values in each source file so that they use the same name.
To ensure that you associate your date values with the appropriate dimension levels and measures, without increasing the size of the model unnecessarily, we recommend that you
Set the absolute range of dates allowed in the model.
Match the date format in the model to the format in the source file.
If required, specify the turn-of-the-century break-point (CenturyBreak setting).
Specify whether the measure values associated with these dates apply to some or all levels in the time dimension.
If you want to take advantage of Transformer's relative time functionality, do not import the date dimensions from dimensional packages. Instead, use the Insert Data Source option to import the appropriate date field to create your time dimension.
When your data source is a fixed-field text file, you must define the columns using the Column property sheet. Otherwise, Transformer does not have the necessary information about how columns in the source file are defined and cannot accurately populate your model.
Because this is a manual process, you can define overlapping columns or define a column that includes other columns.
With fixed-field text sources, you cannot use the Modify Columns command on the Tools menu to remap model columns when the structure of the source file changes. Instead, you must manually modify the starting byte and width of each column on its property sheet.
Use the New Data Source wizard to add a fixed-field text data source.
From the Edit menu, click Insert Column.
In the Column name box, type a name for the new column.
On the General tab, in the Data class box, select the appropriate data type.
In the Position box, type the starting position of the column in a record.
The first byte in a record is byte number 1.
In the Size box, type the width of the column in bytes.
Set other properties for the column as required and click OK.
Repeat steps 2 to 7 for each column in the source file.
You want to define columns for a fixed-field source file in which each record is 38 bytes long and encompasses five data fields.
| 12345678 | 90123456789012 | 34567890 | 12345678 |
| 20070103 | Product1 | Color1 | 02140330 |
| 20070103 | Product1 | Color2 | 02870335 |
| 20070103 | Product2 | Color1 | 04560508 |
| 20070103 | Product2 | Color3 | 03110388 |
You specify in the New Data Source wizard that the first field is an 8-byte numeric field giving the date of a transaction. The next 14-byte field indicates the product type. Note that the type Product1 uses fewer than the 14 bytes available in the field. An 8-byte field indicates the color of each item sold. The last 8 bytes store monetary values for the measure fields Cost (4 bytes) and Revenue (4 bytes).
Transformer can now correctly interpret the columns. For example, the first record represents a transaction that generated 330 in revenue, occurring on January 3, 2007, involving Product1, Color1, that cost 214 to produce.
You may want to change the number of decimal places or significant digits in your measures to better reflect what your users are expecting in their OLAP reports or to create calculations. Decimal values are read into the model based on a scale that you specify. This scale is the source value, multiplied by 10, raised to the power of the input scale. This scaling formula allows Transformer to handle values without integer portions, such as .0003.
To properly scale your input values, you must
Set the Input scale property for the source column.
Set the Output scale property for the measure that uses the source column.
Set the Precision property for the measure that uses the source column.
When you create a data source using an IBM Cognos 8 package, Transformer automatically uses the scale specified in the Framework Manager query, which in turn matches the scale defined in the source database. Consequently, on the General tab of the Column property sheet, the Input scale option always shows a default value of zero.
Regardless of the precision supported by the source database, the overflow limit for the 64-bit floating point storage type in Transformer is 18 significant digits, excluding the decimal separator. When a measure value has more significant digits after the decimal place than is specified in the precision attribute for the model, the number is truncated and the last bit rounds up, rather than down.
Unless truncated, Transformer does not round numbers scaled using Input scale, Output scale, and Precision. For example, in scaling 1,792,485.86 to x.x million using an Input scale setting of 0, an Output scale setting of 6, and a Precision setting of 1, Transformer produces a result of 1.7 million, not the rounded-up value 1.8 million.
Open the Column property sheet for the measure that you want to scale.
On the General tab, in the Input scale box, type the appropriate value and click OK.
Tip: For monetary amounts, this is normally the number of places after the decimal in the source data.
Open the Measure property sheet for the measure that you want to scale.
On the General tab, in the Output scale box, type an appropriate value.
When this value matches the input scale specified in step 2, consistency is maintained between the source data and the measures in your reports. However, this consistency is not mandatory. Enter a value that makes sense for the reporting needs of your users.
In the Precision box, type a value for the number of decimal places to appear in the report, and click OK.
Repeat steps 1 to 5 for each source file column that needs scaling.
You want to scale decimal values so that they appear as integers in your reports.
You have a source file in comma-separated (.csv) format that contains the following values:
DATE,CUSTOMER,PRODUCT_LINE,PRODUCT_TYPE,PRODUCT,QUANTITY,SALE_AMOUNT
20060603,GO Outlet Montreal,Outdoor Products,Tents,40100,5,600.55
20060604,GO Outlet Montreal,Outdoor Products,Tents,40101,2,189.90
20060604,GO Outlet Montreal,Outdoor Products,Tents,40102,1,129.95
To convert these decimal values to the appropriate integer values in your Transformer model without changing the results in your data source or reports, you specify the following settings:
an Input scale of 2 for the column SALE_AMOUNT
Each value in the source is multiplied by 10 to the power of 2 (input scale), yielding an integer value. For example, 600.55 becomes 60055.
an Output scale for the measure that matches the Input scale for the SALE_AMOUNT column
The measure value is divided by 10 to the power of 2 (output scale) when reports are run. For example, the scaled value 60055 reverts to 600.55.
a Precision property of 2 for the measure, if two decimal places are required, or 0 if the reports need not carry this level of detail
An example is 600.55 or 600, but not 600.5500.
The result of all these transformations is that your report users see the appropriately scaled values for SALE_AMOUNT.
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 invoice information that accumulates on the dates that orders are filled. In contrast, sales forecasts or budgets are usually projected for months or quarters, not days. You can specify the level of detail to which forecasts and budgets apply in the time dimension for your model.
The level of detail setting that you specify for a column must be supported by the date values stored in the associated column in your data source. For example, you cannot specify a degree of detail of day if the date values are stored only as year and month data, such as 200602.
If a particular measure has meaning to only one level in the time dimension, you can allocate values for that measure to lower levels.
Open the property sheet for the relevant date column and click the Time tab.
In the Degree of detail box, select the date level appropriate to the measure in your data source.
For example, if the source contains a measure that provides monthly forecast values, click Month.
Click OK.
You want to map date dimension categories to the correct measures in a data source.
Suppose you have sales figures that are stored in the following format in your data source:
WEEK,CUSTOMER,SALES_REP,TOTAL_SALES 20060208,Fresh Air Lte 4,Francoise LeBlanc,4977.99 20060215,Fresh Air Lte 4,Francoise LeBlanc,2955.85
The date values are specified in YMD format, but the associated measure values are actually weekly sales summaries by sales representative.
You specify a Degree of detail setting of Week so that you report the correct values.
Your transactional data is stored as quarterly or monthly values, but in general, you roll up this information into yearly results. It may be more efficient to define the columns in your model as members of a time array, rather than as individual measures. A time array consists of four or twelve adjacent columns that contain quarterly or monthly values for one year.
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 date columns and a different name for the first month or quarter in each array.
If you use the New Model wizard to create an initial model, remember to clear the Run AutoDesign check box. Otherwise, all the measures appear in the Measures list before you define your array, and you must delete them before beginning the array definition.
An array is treated as one object. Do not delete a column that is a member of an array. If you do, all other member columns in the same array are automatically deleted.
Before you import any data, ensure that your source files contain groups of contiguous columns, such as four columns for a quarterly array, or 12 columns for a monthly array.
Also, the data source or sources for the time array must contain at least one date column in addition to the columns that represent the individual elements of the array. The value in the date column must be the same for all data in the array.
To ensure that Transformer processes the array correctly, you must use the first month of your fiscal year as the date in your date column. This specifies the year-begin date for the year in which the array applies.
Ensure that your model does not contain any objects in the Measures list.
Double-click the first column that you want in the time array, such as Month_01.
On the Column property sheet, click the Array tab and, in the Array type box, click 4 quarters or 12 months.
The Data Class of the subsequent columns automatically changes to Array Member.
In the Date column box, click the column that contains the starting month of your fiscal year.
This is the date column, which is usually the same value as the first month in the array, in YYYYMM format.
In the Start month box, type the number of the month in which the fiscal year begins.
Click OK, and drag the column to the Measures list.
If you want to add another data source, click the Data Sources list, click the Insert Data Source button, and repeat step 2.
In the Column name box, type a different name to distinguish this column from the first month or quarter in the previous array. You can also enter explanatory notes on the Description tab.
Repeat steps 3 to 6 to add this new array to your model.
After you specify all the arrays that you want in your model and ensure that the Measures list contains the initial column of each array, from the Tools menu, click Check Model.
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.
You want to consolidate data using a quarterly time array.
Your source data contains the total number of sales for each product by quarter. You define the Q1, Q2, Q3, and Q4 columns as members of a time array, as follows:
DATE PRODUCT Q1 Q2 Q3 Q4
200601 Product1 100 200 150 400
200601 Product2 100 175 150 350
200601 Product3 75 100 100 100
200701 Product1 110 210 160 420
200701 Product2 125 200 175 375
200701 Product3 125 150 150 150
Your OLAP reports show the following.
| 2006 | 2007 | 2006+2007 | |
| Product1 | 850 | 900 | 1750 |
| Product2 | 775 | 875 | 1650 |
| Product3 | 375 | 575 | 950 |
| TOTALS | 2000 | 2350 | 4350 |
Observe how using time arrays yields compact and efficiently processed cubes and reports.
You want to consolidate data from more than one source, using a monthly time array.
You have one data source for each year of data. Each source file contains the total number of items sold by retailer type (independent stores compared to department stores) by month.
You define the Month01 column in each data source as the first column in a twelve-member array. You then rename the initial column names, so that Transformer can differentiate between the two, changing the initial column in the second array (2007) to A2Month_01, to distinguish it from Month_01 in 2006.
The consolidated data for Array 1 (2006 Months) is as follows:
DATE TYPE 01 02 03 04 05 06 07 08 09 10 11 12 200601 Sports 05 06 07 05 04 03 06 04 08 02 01 09 200601 General 10 13 07 05 14 15 06 15 08 12 10 05
The consolidated data for Array 2 (2007 Months) is as follows:
DATE TYPE 01 02 03 04 05 06 07 08 09 10 11 12 200701 Sports 10 13 07 05 14 15 06 15 08 12 10 05 200701 General 20 26 14 10 28 30 12 30 16 24 20 10
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.
| 2006 | 2007 | 2006+2007 | |
| Sports | 60 | 120 | 180 |
| General | 120 | 240 | 360 |
| TOTALS | 180 | 360 | 540 |
Observe how using time arrays yields compact and efficiently processed cubes and reports.
If your legacy data source represents date values using two-digit years and the data spans two centuries, you can supply a CenturyBreak value, or change the default break-point in the cogtr.xml file, so that your data is correctly incorporated into your model.
By default, Transformer interprets the years 00 to 19 as 2000 to 2019, and the years 20 to 99 as 1920 to 1999. For a different default setting, open the cogtr.xml file and specify a CenturyBreak value equal to the last two digits of the first year that you want to appear in the earlier century.
For example, set the CenturyBreak value to 80 so that the years 80 or higher are generated in the 20th century (1980-1999) and the years prior to 80 are generated in the 21st century (2000-2079).
If your source files have overlapping date ranges that span both centuries, such as 1900 to 1999 and 2000 to 2020, you must convert your source data to use a four-digit (YYYY) date format rather than use a CenturyBreak setting.
Open the cogtr.xml file and search for an existing CenturyBreak entry.
The cogtr.xml file is located in the installation_location\configuration directory.
If you cannot locate a CenturyBreak entry, Transformer uses the default value (20), meaning that the years 00 to 19 are interpreted as 2000 to 2019 and the years 20 to 99 are interpreted as 1920 to 1999.
Modify or create the CenturyBreak entry to match the date values in your data source by typing the following, where xx is the last two digits of the first year that you want to be in the earlier century:
CenturyBreak=xx
Save the cogtr.xml file.
Close and restart Transformer for the change to take effect.