Greetings, fellow data analysts!
Plans are an expression of a company’s intentions. How did we get where we are today, and where are we headed from here? You can’t shape the future any way you wish. But if you don’t have a plan, you are leaving it to chance – and to your competitors. Plans provide orientation for actions, set benchmarks for comparisons, and are often necessary to win an internal budget or external investors. In DeltaMaster, planning is closely tied to analysis and reporting. And as you know it from those task areas, DeltaMaster is designed to delegate as many routine tasks to the computer as possible for planning, too. These features may not be as striking as some analytic functions or report formats, but that is what makes them so valuable. They facilitate the work of planners in a subtle way so they can fully focus their valuable attention on the material – the plan – and not the system handling. In this edition of clicks!, we will present one particularly clever function called Input Forwarding, which determines how DeltaMaster saves entered values to the database. Once configured, it works automatically. So read carefully what happens behind the scenes – otherwise, you will hardly even notice it.
Best regards,
Your Bissantz & Company team
Many companies use DeltaMaster for planning in addition to reporting and analysis. DeltaMaster supports planning with a set of functions to simplify data entry and modeling of the underlying database. One of them is called Input Forwarding, which allows you to create rules on how the data entered in measures is captured and saved. Configuring these types of rules is a task in developing planning applications. The end users in Viewer mode (in other words, the planners who have to enter their data) won’t even notice it. What is important to them is that the data they have entered appears where it belongs – and not which rules are working in the background. In this edition of clicks!, we will take a peek behind the scenes with a focus on multidimensional planning with Microsoft SQL Server Analysis Services.
“Enter” and “save” are two different things
An entered value always refers to a specific measure and a particular combination of dimension members. This context results from the current View and the Axis Definitions of the pivot table. Let’s say, for example, you have entered the revenue (a measure) for the year “2015” and in the scenario “Plan” (both of which are dimension members). Before DeltaMaster writes this entry to the database, it could first run additional processing steps. In other words, DeltaMaster doesn’t have to save the entered number exactly as it has captured it. Input Forwarding is what we call the processing steps between entering and saving data. You can choose from the following options:
a) Forwarding the entry to one or more other measures, which will be written to the database – instead of or in addition to the measure where the value has been entered
b) Forwarding the entry to different dimension members than the one referenced in the View and Axis Definition
c) Converting the entered number or the change to the previous value (e.g. rounding up or down)
d) Making the processing dependent on predefined conditions
Forwarding rules, in short, can influence all three parameters (e.g. the measure, dimension members, and the entered number) of the entered data. We will first explain where and how you can edit rules and then describe some scenarios where you can use them.
Rules in the Measure Properties
You can define Input Forwarding rules for each measure – in fact for the one which the user enters a value for, thus for the “source” of the forwarding. In the Measure Properties on the Data Input tab, you can administer the rules in a table (see screenshot below; starting in DeltaMaster 5.6.0). Each row contains one rule.
The columns have the following meanings:
- With the check box in the Active column, you can activate or temporarily deactivate a forwarding rule without losing the definition.
- In the second column, you can enter the Measure that you want to define as the target. This can be another measure or the same one whose properties you are currently editing if you want to define certain dimension members for it or apply conditions. This is the same as scenario a) on the previous page.
- With the optional Tuple Expression, you can forward an entry to specific dimension members. This is equivalent to scenario b) on the previous page. If the field is empty, the entries will be forwarded to the dimension members that are defined in the View and the Axis Definitions of the pivot table.
- The optional Condition determines if the rule will be run, i.e. if an entry will be forwarded or not. This is scenario d) on the previous page.
- With the optional Expression for Target Value, you can individually calculate the value to be forwarded as described in scenario c). Here you can make use of two variables: “#old” refers to the value before the data input while “#new” refers to the entered one. If the field is empty, a value change will be forwarded proportionately.
For more information, please read DeltaMaster deltas! 5.6.0, feature #4. Input forwarding can link multiple measures to cause a chain reaction. If the target of a rule is a measure that also contains rules, DeltaMaster will automatically calculate them as well. In this case, it will treat the calculated values the same way as a user entry.
The easiest case: one to one
In the simplest scenario, DeltaMaster will write the entry of the user directly to the database – one to one, without any further processing. No forwarding rules are necessary. In this case, the measure and the referenced dimension members must be defined directly in the database (i.e. they were not created as a user-defined measure or a calculated member in DeltaMaster). In simple applications where you can transfer all entries directly to the database, you do not need to make any special configurations.
Forwarding input to other measures
Enabling data entry on calculated measures is a different story. You cannot write these types of entries directly to the database because these measures are unknown there. You can, however, distribute the entry to database measures and, therefore, produce the entered value in the process.
Let’s say, for example, that you want to test different rebate rates in sales. The rebate rate is defined in DeltaMaster as the quotient of rebate and revenues. These two measures are contained in the database while the rebate rate is not. Nevertheless, DeltaMaster can accept and process entries for the quotients. In the screenshot on your right, the cells marked in light gray designate where data input is allowed (see DeltaMaster deltas! 5.5.4, feature #11): only the ones for rebate rates.
How revenues and rebate comprise the rebate rate is defined as a rule. Revenues should remain constant; a change in the rate should only affect the absolute rebate. The screenshot shows how you can implement this. The rule has been defined for the Measure “Rebate %”, yet this measure should not be changed (which is why the first row is not marked as Active). The target of the rule is the Measure “Rebate“, which should be calculated as the product of the “new” rebate rate entered by the user and the revenues which remain constant. DeltaMaster saves the calculated rebate in the database and uses it to generate the new rebate rate which is displayed in the data entry cell. The users on the front-end are completely unaware that this calculation loop has taken place. They enter the rate and view it in the data-entry template. That’s all that matters to them – and not that DeltaMaster has first distributed the entered value to other measures and then calculated the measures.
The example above uses forwarding rules to enable data entry for measures that do not exist in the database. You can also use this function for dependant measures. For example, (early-payment) discounts and rebates are often closely tied to revenues and, therefore, rise or fall as revenues increase or decrease. Using forwarding rules, DeltaMaster can automatically make these changes to simplify data input for the user. In this case, you do can leave the Expression for Target Value field empty. DeltaMaster will proportionately adjust the dependant values automatically.
You can see the effect of the rules in the screenshots on your right. The “Simulation” column is prefilled with actual values. If you want to increase revenues by 10 percent, enter it in the field. DeltaMaster will automatically forward this change to rebate and discount and calculate the net revenues based on these new values.
 |
 |
Input forwarding with conditions and variable target
Each save operation has a certain view, which can also be modified between the stages of entering and saving the data. You will find a typical example for this in multi-year planning. Many companies want to plan the near future in more detail than the distant future. This means that the system needs to break down the volume for the coming year by months while a sum for the entire year suffices for the following ones. These different levels of granularity are common in large planning solutions – but quickly get complicated when you have to model them in OLAP databases. With Input Forwarding, you can cleverly manage how DeltaMaster should “book” a budget value – for example, broken down by individual months for the next year or as a sum on the respective December of the following years.
This scenario uses two options of Input Forwarding: the Condition when this rule shall apply as well as the Tuple Expression that defines the dimension members where DeltaMaster should save the entry. This example uses both options in combination; you can, of course, use them separately as well.
We require two rules in this scenario: one for the next year (i.e. 2015), and one for all other years. Both rules affect data input for the volume and need to be entered in its Measure Properties. Since they also both deal with the volume, you need to select it as the Measure. The first row, which does not contain a Tuple Expression and Condition, is deactivated since the volume should not be changed independently from the dimension members involved.
Let’s start with the Condition, which is an MDX statement that returns “true” or “false”. The statements in the screenshot above, for example, ask if the entry refers to the period 2015 (row 2) or not (row 3).
The Tuple Expression defines the members where DeltaMaster should save the entered value. In the first case, that is the year 2015. Accordingly, this value will be allocated to the subordinate months using OLAP rules for data allocation (also called “splashing”). For all other years, it will save the entered value in the field for December (i.e. the last month in the final quarter).
The MDX statements shown above are designed to illustrate the principle. You can, of course, define more powerful, flexible statements in real applications. The Tuple Expression in the second row “[Period].[Period].CurrentMember” is for clarification as well because an empty field means that DeltaMaster should use the current member from every dimension.
The example below shows how an entry for different periods could appear. The 2015 sum was distributed equally across all months while the sum for 2016 was completely saved in December.
You can implement this automatic adjustment of the planning granularity for other dimensions as well to plan by product groups instead of products, by regions instead of individual customers, and so on.
Questions? Comments?
Just contact your Bissantz team for more information.
