Storing Planning Data in IBM Cognos: D-Cube (Part 2)

Exclusive offer: get 50% off this eBook here
IBM Cognos 8 Planning

IBM Cognos 8 Planning — Save 50%

Engineer a clear-cut strategy for achieving best-in-class results using IBM Cognos 8 Planning with this book and eBook

$35.99    $18.00
by Jason Edwards Ned Riaz Rich Babaran | August 2009 | Architecture & Analysis

Read Part One of Storing Planning Data in IBM Cognos: D-Cube here.

Exporting data from the D-Cube

You can export data from the D-Cube into an ASCII file or to the clipboard. The export function gives you the ability to format how you want to export the data. You can set the delimiter, insert headers, and arrange the order of the dimensions.

You can also suppress zero values in calculated data so that the export function will not include records that have zero or null data.

To export from a D-Cube:

  1. Open the D-Cube.
  2. Click on D-Cube|Export.
  3. The Export function displays four tabs:
    1. Export
    2. Header/Footer
    3. Zeros
    4. Show Det/Tot

Export

Observe the following:

  1. Under the Export to option, select whether you want to export to a file or to the clipboard. If you are exporting to a file, enter the path and name of the file that you want to export the data to. Alternatively, you can click on the Browse button to save the file.
  2. Click the Select button to open the item selection box, and then select the dimension items containing the data you want to export.

    If you have a saved selection containing the data that you want to export, then you can load that selection into the dimension selection box.

Groups

Select how you want the dimensions to be displayed as columns:

  • Single Column: Export each dimension as a single column.
  • Multiple Column: Select one dimension whose items you want displayed as separate columns, and set the rest of the dimensions as single columns. The last dimension marked as [data] under the Dimension Order box contains the items that will used as multiple columns (see the following example):

Storing Planning Data in IBM Cognos: D-Cube (Part 2)

Single vs. multiple columns

The following table illustrates a single column file with each dimension laid out as a separate column:

 

Storing Planning Data in IBM Cognos: D-Cube (Part 2)

 

 

The following table illustrates a multiple column file. In this example, the items in the Month dimension are displayed as separate columns.

 

Storing Planning Data in IBM Cognos: D-Cube (Part 2)

 

 

Format

In the Format section, select the following:

  • Separator: Set the delimiter to be used in the export file. Select Comma, Tab, Semicolon, or Aligned Columns.
  • Column Headings
    • Normal: This is the default setting. If you export in multiple columns, this option will include columns headers on each page but not for the rows and page dimensions. If you export in single columns, this no column headers will be included.
    • At Top: This option includes the D-List names and column headers at the top of first page only.
    • Above Each Page: This option includes the D-List names and column headers at the top of each page of the export.
    • None: This option does not include column headers at all.
  • Mode
    • Append: Add the data to any previously-exported data in the same file.
    • Overwrite: Overwrite any previously-exported data in the same file.
  • Data formats:
    • Apply Regional Settings: Select this checkbox to use the regional settings in your operating system as the format for the export.
    • Pipes as Spaces: Select this checkbox to replace any pipe symbols (|) with spaces.
    • Plain Number Format: Select this option to remove any numeric formats that you applied in the D-List. The values are exported in as many decimal places as is necessary in is basic format. All commas, currency signs, and percentages will be removed. Negative values enclosed in parenthesis will be prefixed with a minus sign. Non-numeric formats (Text, Date, and D-List) will be retained.
    • Text Qualifier: Choose whether you want the data exported with a single quote or double quote text qualifier.
  • Dimension Order: Determine the order of the dimensions that will be exported as columns. Move the dimensions up or down using the arrows, or click the Dimension Order button to arrange the dimensions according to their order in the D-Cube.

Header/Footer

This option lets you enter a title and/or footer to the export file. Enter the title or footnote by typing directly into the text box.

Zeros

This option suppresses any record with zero values. This option is independent of the zero suppression in force when you are viewing the D-Cube. You can suppress zeros in rows or columns by highlighting the dimension labeled R or C respectively. You can deselect a selected dimension by pressing Ctrl and clicking on the highlighted item. To suppress zeros in pages, select Suppress Zero Pages. If you want to suppress zeros in all of the dimensions, highlight all of the dimensions and then select Suppress Zero Pages.

Show Det/Tot

This option lets you choose whether you want to export only detail items or calculated items by highlighting the dimensions containing the detail or subtotal items, as appropriate. There are separate selection boxes for detail and total items.

Breakback

Breakback is a powerful feature in IBM Cognos Planning. With Breakback, you can enter data into calculated cells and change the variables that make up the formula according to rules that you specify. Breakback is commonly used to propagate changes to a total across its detail items, in proportion to the value of the detail items.

Suppose you have five products, showing a total of 1500 units:

 

Product

Current

Product A

100

Product B

200

Product C

300

Product D

400

Product E

500

TOTAL

1500

With Breakback, if you enter 3000 in the total, the detail products will change as follows:

 

Product

With Breakback

Product A

200

Product B

400

Product C

600

Product D

800

Product E

1000

TOTAL

3000

Breakback distributes the changes in the TOTAL to Products A to E in proportion to their original share of the total. Breakback works on addition, subtraction, multiplication, and division. It can handle multiple calculations across multiple dimensions and hierarchies.

Breakback on hierarchies

You can apply Breakback on a grand total consisting of multiple subtotals across various hierarchical levels. In a simple hierarchy where you have only one subtotal, Breakback distributes the value across its children proportionately. In a multi-level hierarchy, Breakback cascades the changes one level at a time down through the hierarchy. If you enter a value into a total, Breakback will distribute the value proportionately to the subtotals immediately below the total, then to the subtotal the next level down, and so on.

IBM Cognos 8 Planning Engineer a clear-cut strategy for achieving best-in-class results using IBM Cognos 8 Planning with this book and eBook
Published: July 2009
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

Configuring D-Cube options

There are several options that you can use to improve the look of the D-Cube. You can set the width, add lines, enable data color conventions, and show the sum, count or average of highlighted numeric items. These options work only in Analyst. They will not work in Contributor. There is one option that gives you greater control over Breakback. This option will work in Contributor.

Widths

This option allows you to set the width of the rows and columns. You can set Exact or Minimum widths. The program does not wrap the labels, so you must know the width of the longest label in order to set the width properly.

Storing Planning Data in IBM Cognos: D-Cube (Part 2)

Lines

This option puts a line before or after a calculation. The lines can go horizontally or vertically. You can set the thickness and the style of the lines.

Storing Planning Data in IBM Cognos: D-Cube (Part 2)

Zeros

This option allows you to hide rows, columns, or pages that have zero data. You can set this option on rows, columns, and pages. Or you can suppress specific dimensions by clicking on the dimension on the lower pane. You can select multiple items by pressing Ctrl key and clicking simultaneously on those items. Ctrl+click the items that you want to remove.

Storing Planning Data in IBM Cognos: D-Cube (Part 2)

Breakback

This option lets you set Breakback to a decimal or integer. Select Decimal if you want the changes to Breakback to be made up to the decimal level. Select Integer if you want the Breakback to round to an integer.

Storing Planning Data in IBM Cognos: D-Cube (Part 2)

Stored Copy

When you enable this option, Analyst stores the saved copy of the cube in memory so that it can track changes to the data, and display the change in a variety of colors. Disabling this option can improve performance as your computer will not need to store the saved version, freeing up more memory to handle the working version.

This option is best disabled in staging cubes. Staging cubes are typically large and require a great deal of memory. Because they are not used for data input, there is no benefit in having this option turned on.

Storing Planning Data in IBM Cognos: D-Cube (Part 2)

Show Det/Tot

This option lets you automatically hide detail or calculated dimensions. Highlight the dimensions that you want to be automatically hidden when you open the D-Cube.

Storing Planning Data in IBM Cognos: D-Cube (Part 2)

AutoSum

This option lets you apply an operation to highlighted cells in the D-Cube. The result of the calculation is displayed in the lower-right corner of the D-Cube.

Storing Planning Data in IBM Cognos: D-Cube (Part 2)

Entering data into D-Cubes

There are several ways of entering data into a D-Cube. The simplest way is to type the data directly into the cell. But you can also enter data in several other ways: you can copy and paste data from a spreadsheet, you can enter data into a range of cells using the data-entry command function, or you can link data from a source outside of the D-Cube.

Data color conventions

Analyst displays data in a variety of colors. Non-calculating data entry cells display data in blue. Calculated cells display data in black. When you type into a data entry cell, the data first appears in green. When you press Enter, the data turns purple and any calculated data will turn red. When you save the D-Cube, the data that you entered turns blue. These colors represent stages in the way the D-Cube processes data. Green means that the data has been typed. Red means the data has been stored in memory and the D-Cube recalculated. Blue means the data has been stored in the file.

Data entry commands

Analyst provides several commands that let you enter data or apply a mathematical operation on a cell, range of cells, or the entire D-Cube. You can apply restrictions such as hold, lock, and protect. These tools give you powerful scenario planning capabilities. For instance, you can set a baseline plan, increase or decrease sections of the plan, or hold several key numbers so that they do not change when your assumptions change.

Entering data using the keyboard characters

You can populated a range of cells by the typing certain keyboard characters. For instance, if you type 100 followed by the greater than sign, "100>", the program will populate all cells to the right of the current cell with 100. Typing 100 followed by the pipe symbol, "100|" will populate the cells below with 100. The basic keyboard functions are as follows:

  • Greater than sign (>): Copies data to the right
  • Less than sign (<): Copies data to the left
  • Pipe (|): Copies data downward
  • Carat (^ ): Copies data upward
  • Colon (:): Stops the cells from populating when used in conjunction with the other commands above

Applying mathematical operations

You can apply mathematical operations to a cell or range of cells. For instance if you type "add10" on a cell, the program will add 10 to the data currently in the cell. So if the data is 100, typing "add10" will change it to 110. The same method can be used for subtraction, multiplication, division, and a host of other operations. The following table lists the various operations that you can use in Analyst:

 

Command

Example

Description

Zero

zero

Sets range to zero

Set

set99

Sets range to any value you type after the operator

Add

add10

Adds the amount you enter to current values in the cell. You can use the plus sign (+) instead of Add

Subtract

subtract10

Subtracts the value you enter from current values in the cell

Multiply

multiply1.2

Multiplies amount you enter to current values in the cell. You can use the asterisk (*) instead of Multiply

Divide

divide2

Divides amount you enter to current values in the cell. You can use the forward slash (/) instead of Divide

Percent

percent10

Takes the amount you enter as a percentage of the current values. You can use the percent sign (%) instead of Percent

Increase

increase10

Increases current values by the amount you enter

Decrease

decrease10

Decreases current values by the amount you enter

Reset

reset

Resets cell values to last saved version

Hold

hold

Holds the range of cells against breakback

Cells can still be changed by entering or copying data or by D-Links.

Release

release

Removes the holds on a range

Lock

lock

Write protects the range. Cells can still be changed by breakback but not by entering data or by D-Links

Unlock

unlock

Removes the locks on a range

Protect

protect

Protects cells from data entry. The cells can still be changed by breakback or by D-Links.

Unprotect

unprotect

Removes cell protection

Power

Power2

Raises the underlying values by the power indicated by the number next to it

Random

random100

Creates random numbers up to the number specified.

Round

Round10

Rounds the data by the number specified

If you round a total, a breakback will be triggered, altering the details so that they add up exactly to the rounded number.

Thousands (K)

100K

Multiplies the number by 1000

Millions (K)

1M

Multiplies the number by 1000000

To apply an operation to a range of cells:

  1. Highlight the cells.
  2. Right-click on the selection, and then select Apply Commands from the context menu.
  3. Select the operator.
  4. Type the required value next to the operator. Click on OK.

To apply a mathematical operation to a selection of a D-Cube:

  1. With the D-Cube open, click on D-Cube|Commands.
  2. Select the operator.
  3. Type the value next to the operator (for example, Add10).
  4. Click on either:
    1. Select All: Applies the command to all dimensions.
    2. Select: Applies the command to a selection of the D-Cube.Select the items in the dimension selection box and click on OK.
  5. Click on OK to apply the command.

Locking, protecting, and holding cells

By default, all of the cells are editable, including calculated cells. However, you can lock, protect, or hold a cell or a range of cells. Locked cells prevent you from typing data or linking data into the cell. Protected cells prevent you from entering data but allow you to link data from another D-Cube. Held cells prevent the data from being changed as a result of Breakback. To undo the Lock, Protect, and Hold functions, select Unlock, Unprotect, and Release, respectively.

To lock, protect, or hold a cell:

  1. Right-click on the cell.
  2. Select Lock, Protect, or Hold.
  3. Click on OK.

To lock, protect, or hold a range of cells:

  1. Highlight the cells.
  2. Right-click on the selection, and then select Apply Commands from the context menu.
  3. Select Hold, Protect, or Lock.
  4. Click on OK.

To lock, protect, or hold a selection of a D-Cube:

  1. With the D-Cube open, click on D-Cube|Commands.
  2. Select Hold, Protect, or Lock.
  3. Click on either:
    1. Select All: Applies the command to all dimensions.
    2. Select: Applies the command to a selection of the D-Cube. Select the items in the dimension selection box and click on OK.
  4. Click on OK to apply the command.

Summary

In this two-part article series, we discussed the D-Cube in greater detail. We showed you how you can create a D-Cube, and stressed the importance of the order of dimensions and size. We walked through the different ways of viewing a cube, and demonstrated how you can slice the cube and save the selection for later viewing. We showed you how you can apply a D-Cube format and export data from the D-Cube. We discussed Breakback, a powerful feature that lets you enter data into calculated cells and propagate these changes across the variables that make up the formula. We discussed the various D-Cube options that you can use to improve the look of the cube, and additional functions that facilitate data entry. Finally, we discussed the various data entry commands that you can apply to a cell, a range of cells, or the entire cube.

 

If you have read this article you may be interested to view :

IBM Cognos 8 Planning Engineer a clear-cut strategy for achieving best-in-class results using IBM Cognos 8 Planning with this book and eBook
Published: July 2009
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

About the Author :


Jason Edwards

Jason Edwards is an IBM Cognos Planning solutions expert and partner of Agile Strategic Business Consulting. He has extensive experience in application design and development and has effectively implemented highly successful IBM Cognos Planning solutions for a variety of companies. Jason is certified in IBM Cognos Planning and holds a Bachelor's degree in accounting and finance. Jason can be contacted at jason.edwards@agilestrategic.com.

Ned Riaz

Ned Riaz is a Certified IBM Cognos Planning expert and principal partner at Agile Strategic Business Consulting, a consulting company that specializes in IBM Cognos Planning and Business Intelligence implementations.

Ned has obtained a B.S. degree in Accounting and Management Information Systems, and he passed the CPA (Certified Public Accountant) exam after finishing his degree. After finishing his education, Ned worked as an auditor, accountant, and finance director in many industries, such as banks, software reselling, and entertainment. He became involved in system development work in the late nineties, and deployed various medium-sized accounting and general ledger systems.

Ned joined Adaytum Software, the original manufacturer of Planning products, in late 1999 when Adaytum had less than 50 employees. He has been working with Planning products since Contributor 1.1 and Analyst 2.2 were released in early 2000. While working with Adaytum, Ned designed and built many Planning models for a wide range of customers.

During his days in Adaytum and Cognos, Ned designed and deployed models and systems for many large fortune 500 companies in various industries, such as pharmaceutical, hospital, aircraft operations, and retailers.

As a partner/employee of Agile Strategic Business Consulting, Ned has been involved in designing and deploying various models and systems at a large information delivery corporation since 2006.

Ned and his wife live in Central Minnesota. He enjoys cycling, badminton, and volunteering with rescued rabbits. He can be contacted at ned.riaz@agilestrategic.com and on the Web at www.agilestrategic.com.

Rich Babaran

Rich Babaran has over 20 years of experience in financial modeling and analysis, planning and forecasting, performance measurement development, workflow modelling, and process improvement. He has spent the last 9 years helping organizations improve their planning processes using IBM Cognos Planning. Rich has a Bachelor's degree in Economics and an MBA in Finance. Rich is a partner at Agile Strategic Business Consulting.

The authors and Packt Publishing would like to extend their thanks to Janosys Inc. for their gracious support in the development of this book.

Books From Packt

SOA Cookbook
SOA Cookbook

SOA Governance
SOA Governance

RESTful PHP Web Services
RESTful PHP Web Services

SAP Business ONE Implementation
SAP Business ONE Implementation

Oracle Modernization Solutions
Oracle Modernization Solutions

Oracle SOA Suite Developer's Guide
Oracle SOA Suite Developer's Guide

ASP.NET 3.5 Application Architecture and Design
ASP.NET 3.5 Application Architecture and Design

Building SOA-Based Composite Applications Using NetBeans IDE 6
Building SOA-Based Composite Applications Using NetBeans IDE 6

 

 

 

 

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software