Framework:BI Center:Create CoPlanner Cube
In this example, a revenue planning is created as a CoPlanner cube. Measures with plan, forecast and actual values should be available. To create a CoPlanner cube change in the developer mode.
| Entry Click on the “My objects” directory with the right mouse button. From the “New” context menu, select the command “CoPlanner cube” |
The dialog “CoPlanner cube” is displayed.
| Entry Enter the name of the new CoPlanner cube, “My revenues”, and confirm by clicking OK. |
The new CoPlanner cube object “My revenues” is created and the CoPlanner cube designer is opened. The following screen is presented.
Elements and Functions
“Data sources“ tab
In this tab, the data basis is defined. The available data objects are shown in the left tree of the designer. These are arranged by CoPlanner tables, CoPlanner cubes and OLAP cubes. You can change the width of the tree by clicking on the right edge with the left mouse button. With the button held down, make the area bigger or smaller.
| Entry As the data basis, choose the CoPlanner tables „Umsatzerloese_IST“ and „Umsatzerloese_PLAN“ twice. . Using drag & drop, drag these tables to the working area. Rename the secound „Umsatzerloese_PLAN“ to „Umsatzerloese_Forecast“. |
Properties in the “Data sources” tab
“Dimensions” tab
The “Dimensions” tab shows all dimensions assigned to the data objects, whereas identical dimensions contained in the data objects are connected and assigned. Assigned dimensions are indicated with a green check 
, non-assigned dimensions or dimensions that cannot be assigned are shown on a grey background.
You can break an assignment of the dimension by clicking on the corresponding element. In the same manner, you can also re-establish an assignment.
To delete a dimension, all assignments must be broken first. Then the “Delete” button 
will show up next to the dimension, and the dimension can be removed.
| Entry Choose the “Produktgruppen” und “Plan” from the “Dimensions” tree. |
The following screen is presented.
For both data objects, the assignment is shown on a grey background.
| Entry Click the black arrow in the grey element. |
A dialog to select the dimensions opens. Only the possible assignments are shown in black.
| Entry Rename the secound plan to Forecast and connect it to the data source "Umsatzerloese_Forecast" |
In the corresponding element, the “Manually assigned” 
icon is displayed.
Properties in the “Dimensions” tab
| Entry Activate the “Measure” tab. |
“Measure” tab
In the “Measure” tab, you define which measures are shown.
| Hint Please note that a measure cannot have the same denomination as a dimension |
Here you also set the number format. This will be used for all later analyses.
Another possibility in this step is the display of cumulated values.
| Entry
Move following measures from the data object tree in the working area by the order specified and rename them: Umsatzerloese_IST:
Umsatzerloese_Forecast:
Umsatzerloese_Plan:
|
The following screen shows up:
| Entry In the measures "Quantity" enter “n2” under the “Number format” property for an integer with 2 decimal places. |
Actual and Foreast measures should be taken from the previous year.
Entry Enter following settings to these measures:
|
Properties in the “Measure” tab
In the right part of the designer, the properties of the measures are shown.
AggregationsType
Specifies how subordinated elements are aggregated (projected) in the sum element above.
SUM: Subelements are added Member: Subelements are averaged
Caption
This text is shown.
CalcBackExpression
As the name suggests, this special setting permits “back calculation”, for instance
calcback[MeasureTarget]=[Measure1]*[Measure2];
In the example quantity x price=turnover, let the quantity and the price be database fields and the turnover a calculated field. In order to determine the necessary quantity by entering a value in the ‘Turnover’ field, the following formula is used:
calcback [Quantity]=[Turnover]/[Price];
Quantity is the field whose value is changed – the formula after the “=” character provides the calculation of the new quantity. As it is always advisable, in the case of divisions, to prevent a division by 0, it is recommended to extend the expression as follows:
calcback [Quantity]=IIF([Price]=0,[ Quantity],[Turnover]/[Price]);
This now prevents a change of the ‘Quantity’ field if the ‘Price’ field is still 0 when a turnover is entered. Rewriting into several fields simultaneously is also supported – various CalcBack instructions are simply separated with semi-colons.
| Hint If a table and a writable column are additionally selected, this field will be written simultaneously as well. |
Formulae in CalcBack expressions are to be defined like in the ‘Expression’ field (see below).
Expression
If the measure is calculated via a formula, it is shown here.
Name The system name of the object.
NumberFormat
Definition of the number format. The following are allowed:
- for integers: n0…n2, #.##0.00, etc.
- percentages: p0…p2, 00.00%, etc.
ReadOnly
Defines whether this column is to be write-protected.
CumSettingType
Setting of the cumulation. The following possibilities are available for selection:
- CalendarYear
- FiscalYear
- Ongoing
- Month
- Quarter
- Week
CumSettingColumn
CumSettingColumn offers the dimensions of the data source for selection over which a cumulation is to be performed.
ShiftCol
This offers the dimensions of the data source to which a time shift can be applied.
Shift_Interval
Number of periods that are to be shifted. A forward and backward time shift is possible and defined by using the (+/-)sign.
TimeDimType
Days/Calendar week/Month/Quarter/Year – defines the type of the period that is to be shifted.
| Entry Activate the “Calculated measure” tab |
“Preview” tab
In this tab, the result of the CoPlanner cube is displayed. In the tree on the left, the available dimensions and measures are displayed. They can be added to the analysis as a filter or measure by dragging and dropping or with a mouse click. The right part shows general properties of the analysis.
| Hint If you activate the “preview” tab, all adjustments performed up to this point will be saved. |
| Entry Assign the dimension “Products” to the vertical axis, “Period” to the horizontal axis. As a filter, choose “Plan”, “Forecast”, the “Company” and the “Cost Center” dimension.
With the dialog “Select measure” |
