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

Exclusive offer: get 50% off this eBook here

### 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

£22.99    £11.50
by Jason Edwards Ned Riaz Rich Babaran | July 2009 |

In this two-part article by Ned Riaz, Jason Edwards, and Rich Babaran, we will discuss how data is stored in IBM Cognos Planning Analyst. We will begin by defining the D-Cube and explaining the things that you need to think about before creating the D-Cube. We will discuss the importance of the order of dimensions in enforcing calculation and format priorities. We will show you how you can view the multiple slices of the cube and how you can save a selection of the cube as a separate object. We will explain how you can restructure the dimensions of the cube by adding, deleting, substituting, and reordering dimensions. We will cover some of the important functions available with the D-Cube, including global formatting, exporting, and other options that can make it easier for you to work with the program. We will illustrate how you can use data entry commands that will enable you to enter data, execute mathematical operations, or set restrictions for a cell, a range of cells, or the entire cube. Finally, we will cover Breakback—a powerful feature that allows you to cascade changes throughout the cube simply by making the change to a calculated item.

# Overview of the D-Cube

In Analyst, data is stored in a D-Cube. The D-Cube is a multi-dimensional data structure similar to an OLAP or Excel pivot table. Each of the dimensions contains a list of related data. For example, in a four dimensional cube, the first dimension may contain the items in  P&L statement, the second may list the departments, the third show the timescale, and the fourth may show the version (Budget or Actual). In this example, you have the  P&L statement by month, by department, and by version. The D-Cube must have at least two dimensions. If more than two dimensions exist, the other dimensions appear as pages.

The following screenshot depicts a four dimensional D-Cube. The first two dimensions form the rows and columns. The third and fourth dimensions are displayed as pages, in the upper-left corner of the D-Cube. You can switch to different pages in the cube by clicking the page drop-down list and selecting another page.

# Creating a D-Cube

Creating a D-Cube takes a few simple steps but it requires a great deal of forethought. Before creating the D-Cube, you must think through the dimensions that will form the cube. You must know what data will populate the D-Cube. You must know what category of D-Lists your selection falls under so that you can prioritize the calculations appropriately. You must know what function the D-Cube performs. Is it an input cube, a calculation cube, a summary or reporting cube, or a staging cube? You must also know in which library the D-Cube will be stored, and whether the D-Cube will be shared or will be exclusive to a model. Finally, you must consider size and performance, especially if you plan to deploy the D-Cube in Contributor.

To create a D-Cube:

1. Click on File | New | D-Cube.
2. In the Create new D-Cube box, select the D-List that makes up the D-Cube.
3. Drop the D-List onto the lower pane.
4. Order the D-List. Click on OK.
5. Enter the D-Cube name, and then click on OK.

By default, the D-Cube opens with the longest D-List as the rows and the timescale as the columns. You can transpose the cube or swap the dimensions by dragging and dropping them. When you close and reopen the D-Cube, it will revert to the default view.

## Order of dimensions

It is important to have the proper order of dimensions in the D-Cube. The order of dimensions is important because it determines which calculation takes precedence when calculations from two separate D-Lists intersect. The order also determines the priority of formats when the cell has opposing formats. As a rule, the dimensions must follow this order:

1. Calculation: Calculation D-Lists contain mathematical operations over and above simple addition. Any use of operators, such as multiplication, subtraction, division, or the use of a BiF, make the D-List a calculation D-List.
2. Aggregation: Aggregation D-Lists usually contain a hierarchy of items, such as an organizational hierarchy, a list of products, customers, or cost centers, or a list of items with a simple subtotal.
3. Non-Calculating: Non-Calculating D-Lists contain no calculations.
4. Timescale: Timescale D-Lists contains items that span a period of time, such as months, weeks, or days.
5. Versions: Version D-Lists contain iterations of the data, such as Actual, Budget, Forecast, and Revised Budget.

It is critical to set the proper order of dimensions before building the Contributor application. If you reorder the dimensions after the application is created, all of the data in the D-Cube will be deleted. In addition, reordering dimensions changes the structure of the import tables and, therefore, the way that you import data into Contributor.

## Size considerations

The size of the D-Cube is measured by the number of cells in it. The number of cells you can have in a D-Cube is limited only by your computer's memory. When you build a D-Cube, you should keep in mind the amount of memory available on the computer on which the model will be used. You will not be able to open a large cube if your PC does not have enough memory to handle the data. If the model is deployed in Contributor, size is an even more important consideration. Even with a powerful computer, you will experience performance lag because a large model must pass through the organization's network. In a wide area network, the problem associated with a large model becomes more apparent.

To determine the number of cells, find the product of the number of items in all of the D-lists in the D-Cube. For instance, if a D-Cube has five D-Lists, which have the following number of items: 8, 1, 10, 5, and 12, the number of cells in the D-Cube will be 4,800. This is 8*1*10*5*12.

You can find the number of cells in the dimension selection box, as illustrated in the following screenshot:

The number of items in a dimension has an inverse impact on the magnitude of size increases for additions to the cube. The fewer the items, the bigger the impact that an addition will have to the size of the cube. In the example of the cube above, if you add one item to a dimension that has only one item, you will double the size of the cube. However, if you add one item to a dimension that has 10 items, you will increase the size of the cube by only ten percent.

One clue that your D-Cube may have a problem with size is the number of dimensions. While there is no limitation on the number of dimensions that a cube can have, having too many of them can become a problem. With a D-Cube that has too many dimensions, any additional item in one of the D-Lists increases the size in an order of magnitude depending on the number of items in the D-List. If possible, keep the number of dimensions to not more than five. If you need to have a D-Cube that provides views of data beyond five dimensions, consider splitting the D-Cube. You are more likely to require less memory if you have several smaller cubes than with one super cube.

With Analyst, you can open a D-Cube that has several million cells on a PC that has 512MB of RAM. However, if you plan to deploy the model in Contributor, the threshold for the number of cells is much lower. An acceptable benchmark is roughly 500,000 cells per e.List. This benchmark is subject to many other factors, such as network latency, client PC memory, and CPU, all of which can contribute to model performance.

# Working with the D-Cube

When working with the D-Cube, you have several functionalities that help you to hone in on the information that you want. By default, the D-Cube opens in full view, but you can open only a slice of the cube and save the slice for later viewing.

## Opening a full view of the D-Cube

The most straightforward way to view the D-Cube is to open all dimensions. To open a full D-Cube:

1. Click on File | Open | D-Cube.
2. Select the D-Cube. Click on OK.
3. Select the Full option. Click on OK.

## Opening a selection of the D-Cube

A selection is a subset of a D-Cube. You can only open the specific items of the dimensions that you want to view. Because the data in a selection is fewer than the full view, less memory is required and the D-Cube opens and recalculates faster. To open a selection of the D-Cube:

1. Click on File | Open | D-Cube.
2. Select the D-Cube. Click on OK.
3. Select the Edit Selection option. This option opens the dimension selection box, where you can select the items that you want to view in the D-Cube slice.

As shown in the following illustration, the item selection box lets you select the items from a dimension that you want to include in your view. In the item selection box, each tab represents a D-List. To select the items, move the items from the Items available pane (on the left) to the Items included pane (on the right).

4. Click on OK.

## Viewing different slices of the D-Cube

You can view the D-Cube in a variety of ways. You can open a selection of the D-Cube, or several selections at the same time. If you have a selection open, you can change it to another selection. The ability to switch views makes it easier to work with the D-Cube. For instance, suppose you have a D-Cube that has two versions: Budget and Actual. If you want to enter only budget data, you may want to open only the Budget dimension and keep the Actual dimension closed. You can have multiple selections of the D-Cube open at the same time in the same way that you open the D-Cube. This allows you to arrange the selections next to each other on the same screen.

To modify the views of the D-Cube:

1. With the D-Cube open, click on D-CubeSelections:
1. New Slice: This option allows you to create a new view of the same D-Cube, with the same selection.
2. Reselect: Modify the current selection. Selecting this option allows you to add or remove dimensions in the current view.
3. Save: Save the current selection as a saved selection. This option allows you to save the current view so that you can open it again later.
2. If you select Reselect, move the items that you want to add to or remove from the current view from the Items included to the Items available pane, or vice versa, respectively. Click on OK.

## Saving a selection

You can select a view of a D-Cube and then save this selection so that you do not have to reselect the same view the next time that you open the cube. When you save a selection, you create a "saved selection"—an object that is derived from the cube.

To save a selection:

1. Click File | Open | D-Cube.
2. Select the D-Cube.
3. Under Mode, select Edit Selection.
4. Select the D-List items that you want to view in the D-Cube, and move them from the Items available pane to the Items included pane. If you do not select anything in the Items available pane, then all of the items will be included in the selection, including any future additions to the D-List.
5. Under the Save Selections option on the lower-right corner of the dialog box, click on the Save button.
6. Enter the name of the saved selection. Click on OK.

If you have a saved selection, you can load it into the item selection box. The dimension item selection box is used in many functions, including export, D-List imports, and D-Cube allocations (to be discussed later).

## Opening the saved selection

Once saved, the selection becomes a separate object that can be copied and shared. However, because it is a subset of the cube, you have to go through the cube to access the saved selection.

To open the saved selection:

1. Click on File | Open | D-Cube.
2. Select the D-Cube.
3. Under Mode, select Saved Selection.
4. Select the Saved Selection. Click on OK

 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: £22.99
Book Price: £36.99
See more

## Modifying the saved selection

You usually modify a saved selection when the cube has changed; for instance, when new items have been added or when dimensions have changed. Remember that if a saved selection is shared, any modification may affect all of the objects that use it. So be cautious when you do this.

To modify the saved selection:

1. Click on File | Open | D-Cube.
2. Select the D-Cube.
3. Under Mode, select Edit Selection.

4. Click on the Load button in the lower-right corner of the dialog box, to open the list of saved selections.
5. Select the saved selection. Click on OK.
6. Modify the saved selection by adding to or removing D-List items from the Items included pane
7. Click on the Save button.
8. Select Saved Selection, and then click on OK to save your changes.
9. Click on OK to open the saved selection.

## Restructuring dimensions of a D-Cube

A cube consists of data viewed along two or more sets of related data attributes. When you add a dimension, you create a completely new set of data attributes. You must designate the item in the new dimension to which the current data will belong. For example, suppose you have a cube that lists expense items by department and by month. Here, you can view data along these three dimensions. If you add a fourth dimension, say a versions dimension that consists of actual and budget, you must designate the existing data as either actual or budget. Also, when you add a dimension, think about where in the order of dimensions you will position it. Sometimes, a new dimension can affect the existing calculation priorities so it's a good idea to check for this.

To add a dimension to a D-Cube:

1. With the D-Cube open, click on D-Cube | Dimensions | Add.
2. The following message will appear: This operation cannot be undone. The D-Cube will be saved before the operation can be performed. Do you want to continue? Click on Yes.
3. Select the D-List that you want to add to the D-Cube.
4. Select the position of the new dimensions in the order of dimension by clicking on the D-List that will be under the new dimension. Click on OK.
5. Select the D-List item in the new dimension to which the current data will belong, and move the item to the right-hand pane. Click on OK.
6. The following message will appear: Do you want to open the entire cube?
7. Click on Yes if you want the entire D-Cube to open. Click on No if you only want the item that you selected to open in the D-Cube.

### Deleting a dimension

When you delete a dimension, you need to decide which items in this dimension you want to retain. If you select more than one item, Analyst will aggregate the data in the new cube. If you do not select any item, Analyst will aggregate all of the data. The example in the preceding section uses a four dimensional cube that lists expenses, by department by month by version. If you delete the version dimension, you must decide whether the remaining data will belong to actual or budget. If you select both or neither of them, Analyst will add together the actual and budget and put the result into the new cube.

To delete a dimension in a D-Cube:

1. With the D-Cube open, click on D-Cube | Dimensions | Delete.
2. The following message will appear: This operation cannot be undone. The D-Cube will be saved before the operation can be performed. Do you want to continue? Click on Yes.
3. Select the D-List that you want to delete from the D-Cube. Click on OK.
4. Select the D-List items containing the data that you want to be summed after the dimension is deleted. If no selection is made, all of the detail items are summed. Click on OK.
5. The following message will appear: Do you want to open the entire cube?
6. Click on Yes if you want the entire D-Cube to open. Click on No if you only want the item that you selected above to open in the D-Cube.

### Substituting a dimension

You can substitute an existing dimension with another dimension. When you substitute dimensions, you have to decide, via an allocation table, how the items correspond in the new and old dimensions. Once you complete the allocation table, the data will simply transfer to the new dimension items accordingly.

To substitute a dimension in a D-Cube:

1. With the D-Cube open, click on D-Cube | Dimensions | Substitute .
2. The following message will appear: This operation cannot be undone. The D-Cube will be saved before the operation can be performed. Do you want to continue? Click on Yes.
3. Select the new D-List. Click on OK.
4. In the Select dimension to substitute box, select the D-List that you are replacing. Click on OK.
5. The program searches for objects that reference the old D-List and prompts you to substitute the new D-List in these objects. Click on Yes to substitute the new D-List and update the references. Click on No if you want to update the references manually, later.
6. In the Old-New Match Item box, map the items in the old D-List to the items in the New D-List. Click on OK.

### Reordering D-Cube dimensions

You would typically reorder dimensions to enforce calculation priorities. In Analyst, reordering dimensions will not cause you to lose data, but your calculations may behave differently depending on the new order.

To reorder the dimension:

1. With the D-Cube open, click on D-Cube | Dimensions | Reorder.
2. The following message will appear: This operation cannot be undone. The D-Cube will be saved before the operation can be performed. Do you want to continue? Click on Yes.
3. In the Reorder dimension box, move the dimensions to their new positions using the arrows in the box. Click on OK.

## Formatting data using the D-Cube Format

You can apply a global format to all of the data in the D-Cube. This D-Cube format will be overwritten by any format that is applied locally in a D-List.

To format the D-Cube:

1. Click on File | Open | D-Cube.
2. Select the D-Cube.
3. On the toolbar, click on D-Cube | Format.
4. Select the required format from the options shown in the following screenshot.

As shown in the following illustration, the steps to format a D-Cube are similar to those for formatting a D-List:

### Numeric

You can apply the Numeric format by using the following steps:

1. In the Format Type box, select Numeric. Set the format as follows:
1. Scaling Factor: Select the number that the data will be divided by. For instance 1,000 with a scaling factor of 1,000 will display 1. The number 1 with a scaling factor of .001 will display 1,000. This affects only how the number is displayed, not the value of the number.
2. Use thousand delimiter: Select this checkbox if you want thousands to be separated by commas.
3. Blank if zero: Select this checkbox if you want zeros to display as blanks.
4. Decimal Places: Select the number of decimal places to which the value should be displayed.
5. Negative: Enter the prefix and suffix for negative values. For instance, the prefix '(' and suffix ')' will display negative values enclosed in parentheses. For example, (100). The prefix '-' will display -100.
2. Click on OK.

### Date format

You can apply the Date format by using the following steps:

1. In the Format Type box, select Date/Time.
2. Select one of the following formats:
1. Date: Select this checkbox, and then select the date format.
2. Time: Select this checkbox, and then select the time format.
3. Date/Time: Select this checkbox, and to display date and time format.
4. Start/End: By default, Start is enabled. Select End if you want the stored number to start a day earlier. Analyst generates zero for 1/1/1900. By clicking on End, 1/1/1900 is stored as 1, 1/2/1900 is stored as 2, and so on.
3. Click on OK.

### Text format

To apply the Text format, select Text in the Format Type box, and then click on OK. Text formatted items will accept letters, numbers, and special characters.

### D-List format

Although a D-List is an option in a D-Cube format, it has no practical function. You cannot have a cube whose data consists of only D-List formatted data. In all cases, a D-List format is useful only when it is used locally.

### Format priority

The format priority is determined by the order of the dimensions. When two or more D-Lists contain items that are formatted differently, the format in the first D-List takes precedence. It is more efficient to apply a D-Cube format and then format the D-List item when it requires a different format. Having a global format reduces the chance of conflicts in formatting. If possible, keep your local formats confined to the calculation D-List only.

# Summary

In this article, 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.

In the next part of the article, we will cover some of the important functions available with the D-Cube, including global formatting, exporting, and other options that can make it easier for you to work with the program. We will explain how you can restructure the dimensions of the cube by adding, deleting, substituting, and reordering dimensions. Finally, we will cover Breakback—a powerful feature that allows you to cascade changes throughout the cube simply by making the change to a calculated item.

 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: £22.99
Book Price: £36.99
See more

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