DeltaMaster clicks 03/2017

Setting up data input for planning – An overview

PDF Download

Greetings, fellow data analysts!

In the last issue, we discussed how DeltaMaster can make it easier for you to input data in planning applications. Most of the functions described are available in all applications without having to be set up specifically for this purpose. However, some functions do need to be set up, while you will get more benefit from others if you prepare them carefully. These tasks are the responsibility of report editors and application administrators. This issue of DeltaMaster clicks! provides an overview of where you can make adjustments in DeltaMaster. In other words, it serves as a kind of checklist for making sure that your users can work efficiently. After all, the better an application is set up, the more comfortable it is for everyone to use.

Best regards,
Your Bissantz & Company team

Graphical Tables as input screens

In DeltaMaster, planning and reporting are closely interconnected and use the same interface and formats. A data input report – or if you prefer, a data input mask – is basically structured in the same way as designing a report for reporting and analysis, namely as a Graphical Table (pivot table). There are also some finer points when it comes to data input. You can find out about them below, where we will also tell you where to find further information. All settings must be made in Edit Mode.
There are six aspects to consider:

1) Application
All the settings that apply to the entire application, and especially how the application interacts with the database, are grouped under Options.

2) Measure
Measure Properties let you define a specific treatment for measures in the application. For example, income and expense measures can be handled differently, or absolute values can be treated differently to rates and proportions. In particular, the rules on Input forwarding and Value fixation can be edited here.

3) Report
Under Report Properties, you can use the Filter context to determine which filters (dimensions) should be provided and whether (copying) Processes should be available to users.

4) Table
You can use the Axis Definition to determine the elements and measures for which planners should be required to input data. The Table properties contain options on the behavior of the data input report. You can switch between aggregated and detailed planning via Navigation and Links.

5) Cells
In some cases, you may want to always fix cells so that this fixing cannot be removed when data is entered.

6) Extended functions
For special tasks, the Action menu can be used to integrate extended functions into the interface, such as external programs or stored procedures.

In the following sections, we will discuss these six areas in greater detail and present the main adjustments available to you.

1) Applications

Two Options tabs are relevant for planning purposes: Data Input and Cell Comments.

a) Data input

The Data Input tab lets you activate planning functions – this checkbox is what turns the application into a planning application in the first place. The external conditions need to be met: The data source must offer write-back support, the user must be authorized, and a planning license for DeltaMaster must be available.

The second option is used to improve the responsiveness of the system during data input as required. Users generally expect reports to update and show the latest figures after every piece of data is entered – as long as the recalculation process does not slow things down. If the database response time means this cannot be guaranteed, you can turn off automatic recalculation; in this case, the user simply hits the F9 key to update the report when a longer input sequence is complete.

If Transaction control in presentation mode is enabled, DeltaMaster treats all data input as a transaction in the database theory sense of the word. The transaction begins as soon as the user begins inputting data (Input button). It ends as soon as the user accepts  or rejects  the data input – this is the equivalent of committing to or rolling back the transaction. If the input is rejected, all of the data entered since the start of the transaction is reverted and the database is returned to how it was before the transaction. Transaction control offers additional options for influencing data input and storage. For example, conditions that must be met before DeltaMaster accepts the transaction can be formulated in the Table Properties (see section 4a), and a calculation or simulation function can be implemented as a project-specific extension (see section 6). If transaction control is not enabled, the Input, Accept, and Reject buttons are not displayed and the report is ready for data input as soon as it is opened. In this case, any data entered can only be reverted to the extent described in the following section.

The fourth option has a similar effect to the second, but for writing rather than reading. DeltaMaster collects the data inputted until the report is recalculated, and only writes it to the database at this point. This lets users enter a sequence of values without being slowed down by any delay in writing to the database. The most recent input sequence can be reverted using the key combination Shift+F9.

b) Cell comments

In the same way as planned values, DeltaMaster can administer comments on individual values, known as cell comments. These comments are often more important than the figures themselves when it comes to the business interpretation of planning data, since they can explain all of the considerations leading to or resulting from the value. Under Options, you can define whether the application should use cell comments, how cell comments should be aggregated across several dimension levels, and whether DeltaMaster should generate proposed values for new comments. Extensive information on cell comments and how to set them up can be found in DeltaMaster clicks! 10/2015.

c) Report comments

The second comment option in DeltaMaster involves database-assisted report comments. These refer not to individual cells (values), but to the report as a whole – and to the filter settings (view) that apply when saving the respective comment. This means that the same report can have different comments for different views. When inputting data, you can go to report comments by clicking on the icon of a speech bubble and a pen () in the status bar of the report.

The function is enabled and the database and table from/to which the comment should be read/written are defined in the Options. DeltaMaster can generate or update the table automatically if desired. You can use the Report Properties (see section 3c) to control which reports have report comments available.

2) Measure

A Data Input tab can also be found in the Measure Properties (when Modeling). The most important settings relate to Input forwarding and Fixation.
Input forwarding is used to describe dependencies between measures. One frequently cited example is the rebate rate, a measure defined in DeltaMaster that cannot be saved directly in the database, but that must be apportioned to the “revenues” and “rebate” operands. This apportionment is controlled using rules in the Measure Properties. Extensive information can be found in DeltaMaster clicks! 07/2014.

If you Allow fixation for the current measure, the user can “freeze” values using the F6 key. This means they remain unchanged even when the parent, sister, or child values change, and the change is instead distributed around them. The Table Properties also let you define the dimension in which the fixation should apply (see section 4a). An example of how fixation works can be found in Delta-Master clicks! 02/2017.
Optional displaying as a checkbox is intended for status fields and similar. Instead of having to enter 0 or 1 as a numerical value to set or remove a status, this provides users with a familiar control element.

3) Report

DeltaMaster saves a defined view along with every report – a number of filters that apply to the report, e.g. a certain month, a customer region, and a product group. These parameters also apply for planning and determine what the values entered refer to. When you Edit a report, you can define options for Data input in the Report Properties and use the Filter context to control which properties should be available for filtering.

a) Data input

Even in planning applications, there may be reports in which no input is desired, e.g. status reports, value overviews, or lookup tables. This tab lets you disable the input function for the respective report.

If (copying) processes are defined in the application, this tab also lets you define which of these processes should be available in the current report. The processes are only available in Presentation Mode, not in Edit Mode. To define the processes, you need to use the correct “Maintenance Pack” for the respective DeltaMaster 6 release.
Users can use the processes to flexibly copy existing values before selectively changing and adding to them. For example, the actual revenues for the previous year can be copied over as the planned revenues for the new year, or the existing planned seasonal revenue distribution for an item can be applied to similar items. Calculations are also possible, e.g. a 10 percent increase. Extensive information on the processes can be found in DeltaMaster clicks! 09/2014.

b) Filter context

Filter context is not a planning-specific function, but performs the same role in planning as in pure repor¬ting applications – and has the same high degree of importance. The filter context determines which filter-setting options are available to users, e.g. which month, customers, and products should be displayed or planned. Visually speaking, the filter context determines which filter properties (dimensions) are provided in the filter bar above the report. The fewer the properties, the clearer and easier to use the application will be in Presentation Mode. In other words, you should always pay particular attention to the filter context in both planning and reporting.

c) Comment

If database-assisted report comments are enabled in the application (see section 1c), this function can be activated for each report via the Comment tab.

4) Table

It goes without saying that the structure and properties of the Graphical Table have an effect on the input options. Axis Definition is of fundamental importance. It determines the planning objects (dimension elements) and planned figures (measures) for which values are recorded by DeltaMaster. The following options are also worth a look when it comes to the design of the planning application.

a) Properties: Data input

In the first big input field, you can use an MDX expression to determine the cells in which Data input is permitted. Cells for which the expression returns the value “true” can accept data input and are automatically colored light gray. The expression is pre-set as “false” in new reports, meaning that data input is initially disabled and must be explicitly enabled. If the MDX condition cannot be evaluated, e.g. due to syntax errors, data input is disabled for the entire report.

To allow the fixation that is fundamentally permitted for a measure (see section 2) in the current report, select the dimension in which it should apply.

The second big input field is only available if transaction control is enabled (see section 1a). You can implement input validation or plausibility checks by setting a Commit condition in the form of an MDX expression for this field. As soon as the user completes the transaction, DeltaMaster evaluates the condition and only transfers the data input to the database if “true” is returned.

DeltaMaster makes data entry easier for the user by automatically moving the selection to the next input-ready cell when the Enter key is pressed. This is convenient as reports are typically edited from top to bottom and left to right.

b) Properties: Cell comments

If cell comments are enabled in the application, you can determine whether and how these should be showed in the current report: with a flag (red corner) that brings up a tool tip when the user points the mouse at this corner or as text directly in the cell.

c) Navigation

Like the filter context, the Navigation settings apply to both reporting and planning. Yes, you can navigate within a data input report, too – and enter values in the cells displayed! So it is also worth examining and setting up the navigation context as described in DeltaMaster clicks! 10/2016 and 11/2016.

d) Links

Links are another way of moving from an overview to a detailed view. In planning, they are typically used to link from an annual figure to the seasonal distribution while retaining the context, e.g. a specific customer group. Links compete with navigation to a certain extent. DeltaMaster clicks! 03/2015 discusses links in detail – and explains why navigation is often the better option.

e) Comment

Report comments are ideal for user documentation. You can use them to connect information on the business relevance of the figures that are the subject of the query, the planning process, or the report structure directly with the report, thereby making users’ work easier. Comments recorded using the icon of a speech bubble and a user silhouette () are stored in the application for all views. This means they are available to all users and cannot be edited in Presentation Mode (unlike database-assisted report comments; see section 3c).

5) Cells

In some data input reports, you may want to prevent users from making accidental or deliberate changes when entering data. To achieve this, you can always fix certain cells in the report (context menu or key combination Ctrl+F6). This function is typically used for linked reports, e.g. if you want to plan annual figures in one report and the monthly distribution in a second linked report – but you need to ensure that the total figure for the year remains constant and the user can only change the weightings of the individual months. Permanently fixed cells are indicated by a black lock symbol.

6) Extended functions

If required, functions from external programs may be provided using the Action Menu. Users can make use of these functions, e.g. stored procedures or tools, without leaving DeltaMaster.

Examples of these actions include:

• Data can be imported from Excel row by row using special import routines. Checking rules in the external program code are used to get the editor to validate the transferred values or to check them for plausibility – i.e. so that this is done decentrally, rather than centrally after the Excel list has been submitted.

• To enable integrated planning, planned figures from Procurement and Sales that are initially drawn up separately can be transferred to the income statement.

Project-specific extensions also include the option to call up a specific stored procedure from the data input report. This is mainly used for intermediate calculations and simulations. DeltaMaster displays an additional button (“f”) for this purpose.

These special functions must be programmed individually and therefore typically form part of larger planning projects.

For the sake of completeness, you should bear in mind that this issue only relates to Graphical Tables and that data can also be inputted using SQL Drill Through.