Greetings, fellow data analysts!
When it comes to planning, no one wants to start from zero. It is easier to begin with numbers that you already have – whether they come from past periods, similar market segments, or divisions that have already finished their budgets. Copying, changing, and adding information is usually much faster than entering all of the numbers from scratch. With DeltaMaster, you can even set up flexible copy processes from the start. This simplifies the work for your planners without burdening them with the complexity of the underlying data model. We will provide an overview in this edition of clicks!.
Best regards,
Your Bissantz & Company team
In planning applications, transferring existing values and then modifying or adding data as needed is much more convenient than entering the entire budget from scratch. You could, for example, transfer actual revenues of the previous year as plan revenues for the coming year or apply the previously planned, seasonal revenue distribution of a certain article to similar articles. Since the areas that need to be planned already contain proposed values, users can focus their attention on special areas.
There are two ways that you propose plan values:
- You can calculate them in the database. This is a good choice especially when the values need to be derived based on many different criteria and rules that have to be programmed. This method of pre-filling values is a job for modelling and application development.
- Individual users can also copy data within DeltaMaster. In this case, you can predefine so-called processes that users can then access from a menu.
User can start these processes on their own when they need them. Since the processes are configured in advance, you can adapt the possibilities for copying to the application and entry scenario without confronting the user with the full complexity of the data model.
The copy processes in DeltaMaster are unique because they use the data model, dimensions, and measures for orientation – and not simply the cells of the table that is currently displayed. This is the case with a basic copy/paste function, which you can also use in DeltaMaster to copy values from a table to a clipboard and paste them somewhere else as you would in a spreadsheet (see DeltaMaster deltas! 5.4.5, feature #18). Due to the principle of copy and paste, however, you are bound to the current report view, which really limits your possibilities. In most cases, you only copy and paste a small amount of values. Comprehensive planning applications, however, entail vast amounts of data stock, multidimensional data that can be described using many different attributes (dimension members). In these types of applications, you have to transfer large sections of the database and not just a few table cells or values.
The copy processes of DeltaMaster are specialized in that exact respect. They allow you to copy values in the database regardless if you can see them in a report or not. You can also run calculations as well, for example, to increase all of the values by a certain percentage. Detailed instructions are available in DeltaMaster Help (F1 key or Help menu) under the Keyword “Processes”. This edition of clicks! will provide an overview of how you can define in advance which copy options should be offered and how users work with them in a data input report.
One last thing before we get started: These processes cannot only copy data, but they can also move or “set” it. Moving, in this case, means that the values in the source range are deleted once they have been copied. Setting means that the values in the target range aren’t derived from the values of a source range but are entered and distributed as (calculated or constant defined) numbers. This edition of clicks! will focus on copying only, but the same general process is valid for moving and setting as well.
Processes are saved settings
A process contains all defined settings for a copy procedure, similar to a “macro”. These settings affect:
- the source range from where the data should be copied and the target range where these data should be entered. A “range” in this case refers to a database section that you can describe with a combination of dimension members, for example, the value type (actual, plan, etc.) and a certain period. We refer to the source and target range together as the Execution context.
- the action that the process has to finish – in particular, defining the respective measures and calculation rules (e.g. percentage change).
The various parameters enable a great deal of flexibility and dynamics. You can even configure an interactive selection that allows individual users to adapt source and target range as well as action parameters when running a process – within well-defined boundaries, of course.
With the following example from our reference application “Chair”, we will illustrate how end users work with the processes and how you configure them. The database contains a value type called Simulation that can be used for own considerations. Before you can start simulating, this value type must be “filled” with either current budget values or actuals from the previous year depending on the scenario at hand. Starting from these numbers, users can make their own thoughts, change (i.e. overwrite) the transferred values, and explore what variances would result. A process is defined to transfer the values.
Select and run processes in a menu
Users (i.e. planners) can run processes easily in Viewer mode. When a process has been defined in a data input report, DeltaMaster will display an additional Execute process menu in the Report window. If this menu is missing, there are no processes defined for the current report.
If you execute a process, DeltaMaster will first open a dialog box that informs the users what the process will do. The Execution context (source and target range) and the Tasks are listed here as well.
If configured in the process definition, users can define or edit the source and the target themselves by selecting the desired member from the Dimension Browser or a predefined list. In our example, users could select the periods and source value type by themselves.
To select the periods, the Dimension Browser will open. In this case, only members on a monthly level are selectable. DeltaMaster makes a suggestion based on the settings in the View window and pre-selects the current month as the Target and the respective month of the previous year as the Source. These three characteristics – that the selection is made in the Dimension Browser, that the time dimension is limited to the months, and which “defaults” apply to the source and target – were all previously defined in the Process Definition.
For the value type, only the source can be selected. (The value type Simulation is fixed for the target). The selection here is displayed as a list for simplicity reasons because the users may only select from a few members that have no hierarchical relationship. This behavior is defined in the settings in the Process Definition as well.
The cumulation may not be changed in the dialog box because the user should only work with non-cumulated values. To emphasize this point, DeltaMaster explicitly lists this criterion even though the users cannot change it.
DeltaMaster also accounts for user entries in the tasks, provided that they are intended that way – for example, the percentage for raising the values.
The bottom section of the dialog box shows how many cells are affected in the database so that the user gets a feeling for how time-consuming the triggered action is.
Defining processes
You can create and edit the Process Definitions in Miner mode (Model menu). The prerequisite is that you activated Planning functions on the Data Input tab in the Options (Extras menu). Otherwise, DeltaMaster will not offer the entry in the Model menu.
You can complete all tasks from a central dialog box. This dialog is broken down into three sections:
- The top section lists the previously defined processes, similar to jobs in ReportServer. In Viewer mode, DeltaMaster uses the Name of a process as a menu entry and the Description as a help text.
The center and bottom parts of the dialog box refer to the process that you select here.
- In the center section, you can edit the Execution context (see screenshot on the next page). DeltaMaster uses dimensions and members to specify this source and target range of the copy procedure.
- The bottom section of the dialog box contains more detailed specifications for the Tasks that will be run (see screenshot in the section “Editing tasks” below). Here you can specify which measures you want to copy and if additional calculations are necessary.
Editing the Execution context
DeltaMaster offers various settings to define the source and target range of the tasks. You can also configure the selection options for end users in Viewer mode. Thereby, you do not need to define the members that you need to include when copying in advance. Users can select them interactively during the process execution.
Each row of the Execution context section describes a Dimension, which you can select in the first column. Each dimension can only appear in the Execution context once. The more dimensions you list, the more precisely you can limit the source and target range. For all dimensions that you do not use, DeltaMaster will automatically include the member selection in the View window when the process is run. If a dimension is not marked Visible, it will not be shown in the dialog box before the process is executed. This way, you can include technical attributes (e.g. flags or status) that are required for the application logic but are not part of the business-side of planning tasks and, therefore, do not need to be presented to the user. This option was used to hide the period view in the example above.
The same five settings are used to describe the Source and the Target.
The first setting is the Selection Method. This determines if the user can choose the dimension members that should be included during the process execution – and if yes, if they should do so from the Dimension Browser or a simple selection List. You would choose the Dimension Browser when you can select members on different levels – typically in the customer or product dimension but sometimes time as well. The list, in turn, is helpful when you only want to offer a few members on the same level (e.g. as in value types or scenarios).
The Selection Range and Selection Query belong together. These fields come into play when you want to use an interactive selection (i.e. the selection methods Dimension Browser and List) in the dimension. In these cases, you can regulate which members should be offered for the selection – for example, only members on the level “Month”.
The Default Selection and the Default Selection Query also belong together. These fields are relevant for all Selection Methods. If you want to offer no selection, this is where you would define the final source and target range. No changes are possible then during the process execution. If you intend to offer a selection from a Dimension Browser or List, this is where you define the default setting for the execution dialog box. This is how we have referenced the month of the previous year in our example.
Editing tasks
The bottom (i.e. third) section of the dialog box is where you describe the Tasks. Each task that you copy includes two measures: one as a Source and one as a Target. In many cases, you will use the same measure for the source and target. In some data models, however, the value types Actual and Plan are modeled as separate measures. If this is the case, you can select them here. In the Calculate target column, DeltaMaster can also calculate the values for the target if necessary – either According to Operator (e.g. “5%+”; see DeltaMaster clicks! 01/2006) or using an MDX statement. When the process is run, DeltaMaster will display this as an internal calculation that cannot be changed.
Since each process can include several tasks, you can combine copy tasks for units sold, revenues, and discounts in a single process.
Assigning processes and reports
Once you have defined a process (e.g. Execution context and Tasks), you will need to assign the process to one or more reports. This step is necessary because the view of a report as well as the stipulations in the process definition affect the copy procedure. The report and process, in other words, should be closely aligned.
To assign one or several processes to a report, edit the Report Properties in Miner mode (context menu of the report in the Briefing Book). The Data Input tab displays all defined processes; here you can activate those that should be available for the respective report. With this step, DeltaMaster will make the processes available for use in Viewer mode and display this as a menu in the Report window.
For more information on the individual settings, please read the respective document in DeltaMaster Help. The many different settings and combination possibilities may surprise you at first. They are also the reason why you can prepare the copy procedure so well and design them with many different levels of leeway so that end users can move around complicated data models with confidence. Once the processes have been configured, they can be opened and used in later sessions – even by many local users that don’t necessarily know the data model. That all translates to major gains in efficiency.
Questions? Comments?
Just contact your Bissantz team for more information.
