Calculating revenue by modifying the model

This tutorial covers the following skills in IBM® Cognos® Insight: creating a cube calculation, inserting a tab, creating a crosstab, creating a chart, replacing dimensions and measures, suppressing empty cells, and changing how totals appear.

As a data analyst at the Sample Outdoors Company, you want to change the Order Revenue column so that it reflects changes to the Quantity and Unit price values. This will enable you to decide how the quantities and unit prices of products affect the company's revenue.

Procedure

Click the Actions icon , and then click Open.
Navigate to where you downloaded the samples and double-click Orders_language_code.cdd.
For example, if you want to work in the Italian sample, double-click Orders_IT.cdd.
In the Cognos Insight window, next to the tabs, click the Insert a new tab icon .

A new tab appears in the Cognos Insight window. The new tab is called Tab 5.
Right-click the new tab, click Rename, and then enter Test.
You now have a blank canvas to use for creating a new view of the data.
Open the content pane to see all the dimensions and measures that have been imported into this workspace.
Drag the Order details cube onto the workspace.
By default, dragging the cube to the workspace creates a crosstab and a chart. The crosstab and chart display the first dimension in the cube, Order number, and all the measures that are available in the cube.
The overview area identifies the data that appears in your crosstab:
- The rows section shows that the Order number dimension appears in the rows of the crosstab.
- The columns section shows that the Order details Measures dimension appears in the columns of the crosstab. This dimension includes all of the measures in the Order details cube.
- The context section shows the remaining dimensions in the context of the crosstab. The dimension names in the context section represent the data that is displayed in the crosstab. For example, the context section displays All Products, so the data in the crosstab represents all of the products. If you filter the Products dimension to display the Legend item in the context section of the overview area, then the data in the crosstab represents only the Legend product.
You decide that you want to see all the data, but nested in a way that is easy to understand at a glance. To do this, you need to change the dimensions in the rows, columns, and context sections of the overview area.
From the content pane , from the Order details cube, drag the Products dimension and position it after the Order number dimension in the rows section of the overview area.
The cursor changes as you hover over the overview area to show you where you can and cannot drop the dimension.
Tip: You can also drag dimensions from one section of the overview area to another.
From the Order details cube, drag the Customers dimension and position it after the Products dimension in the rows section of the overview area.
From the Order details cube, drag the Order size dimension and position it after the Customers dimension in the rows section of the overview area.
The rows section now includes the following dimensions, nested in this order: Order number, Products, Customers, and Order size, as shown in the following image. The columns section includes the Order details Measures dimension.
Right-click each of the four totals in the rows of the crosstab (such as All Order numbers), and then click Show Totals > Don't Show Totals.
The crosstab is still mostly empty, because it is showing a cell for each data intersection. You decide to remove the empty cells.
In the widget toolbar, click the Suppress empty cells icon , and then click Rows.
The crosstab now displays all the data in the cube as shown in the following image.
You decide that you want to focus on the Kanga Kampers customer.
In the overview area, click the Customers dimension, and then click Kanga Kampers.
The crosstab and chart now show only the Kanga Kampers data.
Multi-select all of the cells in the Order Revenue column of the crosstab, and then right-click the selection and click Create Cube Calculation.
In the Cube Calculation Name window, enter Order Revenue Calculation, and press Enter.
The calculation that you are creating applies only to the dimensions and measures as they are filtered in the crosstab. For example, the Customers dimension is filtered to include only the Kanga Kampers product in the crosstab, so the calculation applies only to the Kanga Kampers product.
Tip: You can change these filters in the expression editor. Each dimension in the filter area, under Define a new expression for, displays the members that appear in the crosstab and the dimension name. For example, the Customers dimension is filtered to include only the Kanga Kampers product, so the Customers dimension includes Kanga Kampers, the member that appears in the crosstab, and Customers, the name of the dimension.
In the data hierarchy, in the Terms tab, scroll down and expand Order details Measures.
The Terms tab includes the dimensions and measures that are available in the current cube. These are called the terms in your expression.
Tip: You can use expression terms from other cubes in the workspace by importing a term. Importing a term from another cube enables you to add a dimension or measure from another cube to your calculation. You do this by mapping the dimensions in the current cube to the dimensions in the cube that you want to import from.
Drag the Quantity measure to the Expression pane.
In the Expression pane, type an asterisk (*), which is the symbol for multiplication.
Tip: You can add calculation templates to the Expression pane by choosing them from the Simple tab. For example, on the Simple tab, if you click Arithmetic in the Operation type list, and * (Multiplication) in the Operation list, the following template for a multiplication appears in the Expression pane: ([<operand 1>] * [<operand 2>]). You can then replace [<operand 1>] and [<operand 2>] with the dimensions and measures you want to use in the calculation.
Drag the Unit price measure to the Expression pane, placing it after asterisk (*).
To save and apply the calculation, click OK.
Now the Order Revenue column in the crosstab is calculated by multiplying the Quantity by the Unit price.
Click a cell in the Unit price column, type a new price, and press Enter.
The Order Revenue value in that row changes to reflect the new unit price. You can also change the values in the Quantity column to see how a change in the quantity that you sell impacts the Order Revenue.
Click the Actions icon, and then click Close.
When you are prompted to save the changes you made to the sample, click No.