1. Automatic compact display
2. Formatting and scaling of measures
3. Formatting using calculated members

The first method is the most recent in DeltaMaster and the easiest to apply: Just activate the respective option, and the numbers appear in the report or throughout the application in a concise, decision-oriented notation.

You can find a comparison of the display with the numbers written out in full and the new compact display on the next page.

Numbers written out in full
Compact with K, M, B
Compact with apostrophes

The great thing about this automatic feature is that units can be entirely mixed: In the very same table, row, or column, some figures can be formatted in millions, others in thousands or billions, and the very small ones can be written out in full. This may seem surprising at first glance – but you will soon discover how easy on the eye these figures are, and how clear and distinct they look to us when the clutter of the less significant decimal places is removed.

In addition, if reports are scaled insensitively and indiscriminately with just one factor, strange num­bers often arise. We don’t talk of 0.1 million if we mean 100 thousand, or of 0.01 million or even 0 million if we mean 10 thousand. In this respect, formatting by DeltaMaster is in line with natural parlance: In the report, the numbers are set out as they would be pronounced in a meeting or on the phone.

The look of figures of different magnitudes can also be interpreted, for example amounts in the low millions and high thousands: The thousands have more digits, more digits means more detail, more detail means smaller values, and small values means less important. The large, important figures are short and simple. This makes the compact notation a wonderful, pragmatic tool for decision-oriented reports.

And the compact notation also makes life easier for report editors, as the formatting automatically adjusts to fit the respective values set out in the report. So if the user drills down from group to business unit, for example, the change of scale is automatically taken into account.

Compact for everyone, more compact for professionals

DeltaMaster provides two display variants, as shown above:

• The variant in thousands (K), millions (M), and billions (B) is the preferred choice for reports that are aimed at a wider audience. The abbreviations are widely used, including outside of controlling, and need no further explanation.

 

• The variant with apostrophes (one apostrophe for the thousand factor, two for millions, three for billions) is particularly compact. It is quite widespread among professionals: In meetings, it is common for a couple of figures on a flip chart to be appended with a little stroke, an apostrophe, to denote thousands, and with a second one to denote millions. What simplifies writing on a flip chart also makes the report easier to read and can be learned quickly, even though it is not yet standard everywhere.

No further settings or parameters are required. Percentages are excluded from the display regardless of how large they are. The display has no impact on the calculations: DeltaMaster always calculates with the exact values, including decimal places. The figures are exported to Microsoft Excel as num­bers written out in full to their original degree of precision, regardless of how they were displayed in DeltaMaster. Earlier versions of DeltaMaster do not support this formatting, but are not affected by the setting – the numbers are written out in full, as before. In planning applications, enter the numbers in full, not in compact notation.

To activate the display, switch to Modification mode.

Compact display as default in the application

As default for all reports in the current application, activate the display in the options (via the gear wheel  in the menu bar), on the Presentation tab. In appli­cations that you are creating with DeltaMaster 6.1.6 or higher, this is already the default setting.

Compact display in Graphical Tables

In addition, when you edit, you can set the display of numbers for Graphical Tables individually. You can find the options in the Edit menu (on the right) in the Presentation section. In this way for example, you can set the decision-oriented compact notation as the default and produce some “lookup tables” with accounting precision for other purposes. If none of the three options are selected, the general setting in the application options is applied.

An abbreviated display of numbers can also be attained with the following two methods. They are both based on the analysis model, one on measures and the other on (calculated) dimension members.

Formatting and scaling of measures

You can find all relevant settings in a central position: in the Measure Properties on the Format tab (see also DeltaMaster clicks! 02/2014).

Output of values in thousands or millions can be attained in two ways: via a Custom .NET formatting string or via Scaling.

• Formatting strings come from IT. They describe how a numerical value is to be output as text. For example, they can be used to control which characters are to be used as thousands separators and decimal separators, how many decimal places are to be taken into account, and how negative numbers are to be displayed. This implicitly also means that it is possible to convert numbers to thousands or millions.

The formatting string can either be stated as a constant expression (in the Custom field) or determined dynamically by means of an MDX expression, for example in accordance with hierarchy levels. The link next to the input field for custom formatting opens a Microsoft website that explains the structure of formatting strings (see also DeltaMaster clicks! 02/2008, with an example of filter-dependent formatting with MDX).

• Scaling is known in the business world as well, and relates to the numerical values themselves: They are converted (divided) and thus transformed. Here too, DeltaMaster calculates internally with the original values. Besides powers of ten, other factors can also be stated here as factors, for example in order to convert units of measurement. In addition, the factor can be calculated by means of an MDX expression. This must return a numerical value, for instance from a reference to the selected member in a specific dimension (for example currency, unit, value view) or in accordance with hierarchy levels.

Formatting and scaling work in parallel: Scaling transforms values, and formatting controls their notation. Scaling is easy to use, but additionally requires formatting (in case of doubt: Number with 0 decimal places). Formatting strings are not exactly intuitive, but if you understand how they work, you can often avoid the need for additional scaling.

The concise notation in K, M, and B set out above can also be achieved with formatting strings:

#,0, „K“	Scaling in thousands, no decimal places
#,0,, „M“	Scaling in millions, no decimal places
#,0,,.0 „M“	Scaling in millions, one decimal place

The comma between the hash and the zero means that group separators (thousands separators) will be output if required; in English, this is usually a comma. The commas after the zero result in the scaling – for each comma, the value is divided by 1,000.

The settings on the Format tab, including MDX expressions, can be saved as a format template for number formats and reused with other measures (I want to menu top right on the tab).

You can access the measure properties in DeltaMaster 6 when Modeling. If the key figure is displayed in the Filter bar, click on it once to edit the properties. Otherwise, click on the three dots after the measures to open the Measure Browser; then edit the properties via the context menu.

Formatting with calculated members

This case too is described in detail in DeltaMaster clicks! 02/2014. Formatting and scaling can be linked to (calculated) members as well as to measures. If applicable, an auxiliary dimension is often provided in the analysis model for this, for example unit or value view. In this way, a specific formatting can be applied to multiple measures, eliminating the need to format them separately. In addition, one measure can be combined with various members for formatting instead of deriving multiple measures with differing formatting from one basic measure.

One special feature is that the formatting members can also be set explicitly in Presentation mode – this is not possible with formatted measures, and neither possible nor necessary with the automatic compact notation.

Calculated members can be edited in DeltaMaster 6 when Modeling. In the Filter bar, click on the name of a dimension to open the Dimension Browser. It is possible to add and edit calculated members via the context menu and the I want to menu. The formatting options correspond to those of the measures, but an MDX expression is not possible here. Scaling is achieved via a calculation using an MDX expression in the Definition field.

The two structure-driven approaches (via measures and calculated members) are tried and tested and an efficient tool for special tasks, usually in larger applications. The automatic compact display is universal and easier, as well as being more effective by virtue of the clever, value-dependent scaling.

Compact in tiles and Sparklines

In the case of two special displays, DeltaMaster uses compact notations as a matter of course:

In zoomed Sparklines, where space is particularly tight, the columns or dots are labeled in a particularly readable manner thanks to the apostrophe short-form.

And on application and report tiles as well, Delta­Master automatically formats large, absolute numbers in a compact manner. Here, K represents thousands, M millions, and Bn billions.

Before: A long, un-wrapped table – or rather an almost empty screen and a list whose length can only be gaged with the scroll bar. Well-versed readers will have a hunch that even though the table starts with good news, such as the pleasing changes at the customers SuperOffice, Bavaria Furniture, etc., at least one piece of bad news will be lurking at the end of the list – the short bar next to SuperOffice suggests this. If you scroll to the end of the table, you will lose sight of the beginning. There would actually still be space here, …

After (see below): The same list in the screen section of identical size, wrapped and readable in full, including the already feared sharp negative variance.

Further suggestions on how to compare positive and negative variances in the same report can be found in DeltaMaster clicks! 02/2015. – The above illustration is an example of a vertical wrap: A long table with one fixed column and one data column is wrapped across three display columns. DeltaMaster repeats the labeling of the columns automatically. The status bar points out the wrap so that you can see that the layout is a result of the formatting rather than of the structure (i.e. in the exam­ple, the revenue is always the same, and there are not three different measures with the same name, for instance).

The following illustration shows an example of a horizontal wrap: A table that was previously too wide, …

… fits entirely on the screen and is easy to read. Again, the screen section is the same as in the continuous display.

Give the wrap a try

You can activate the Table Wrap in Modification mode if you edit the properties of the Graphical Table (Edit menu, on the right) on the corres­ponding tab.

You have already encountered the two alternatives in the above examples.

• The horizontal wrap is suitable for wide tables: After a number of columns automatically determined by DeltaMaster or predefined by the user, the table is wrapped to the right, and the further columns are shown in a new display row. In figurative terms, what no longer fits at the back of the screen is continued underneath from the front (in a line pattern like a Z).
• The vertical wrap is suitable for long tables: After a number of rows automa­tically determined by DeltaMaster or predefined by the user, the table is wrapped at the bottom so that further rows are shown in a new display column. In figurative terms, what no longer fits at the bottom of the screen is continued at the side from the top (in a line pattern like a W).

Automatically wrapped is best

Where a choice can be made between automatic and custom in DeltaMaster, we generally recommend automatic. This also applies to the table wrap: In automatic wrapping, DeltaMaster derives the number of columns and rows dynamically from the available screen space in order to make optimum use of it.

In special data situations, you may wish to specify the number of columns or rows, for instance if the report needs to follow a specific symmetry. In the illu­stration on the right, the report already wrapped above is struc­tured with the sales areas in such a way that the sales areas of a region appear in pairs: South 1 next to South 2, North 1 next to North 2.

An essential condition for special cases such as this is normally that the structure of the data to be displayed is constant and the length and width of the report are independent of the Filters (View). In the case of nested axes, the columns and rows on the very inside are counted.

The options for equal column width or equal row height ensure a consistent grid in the wrapped table and therefore, in some cases, a more harmonious appearance of the report. This particularly applies if the labels in the column/row headings have differing lengths or have one row in some cases and multiple rows in others, as shown in the following example – without standardized row height on the left, and with on the right.

The starting point was a table with ten entries and a fixed column width (Properties of the Graphical Table, General tab); the wrap was forced after predefined five rows.

Editing a wrapped table

The table wrap is highly dynamic. An essential condition for that is a dynamic environment in which DeltaMaster has absolute authority over the output. This results in certain limits regarding use, namely by other programs (Microsoft Office, web browsers) and by other DeltaMaster functions that compete with the table wrap for control of the precious screen space. For instance, in the event of export, the table wrap is only retained if you export the table as an image; the WebClient does not support the table wrap currently. Automatic row and column structuring (separation) cannot be used concurrently with the table wrap. The analysis functions Zoom and Navigate, searching on the row axis, and displaying as Small Multiples are also not possible in wrapped tables. (Incidentally, the display of Graphical Tables as Small Multiples also adapts very well to the available screen space; despite different concepts, a certain similarity with the table wrap may be seen here.)

Apart from this, the table can still be adapted and structured in Modification mode. For example, you can:

• change the structure of the table by adding, removing, or changing the sequence of dimensions and measures;
• calculate and display variances (Magic Button Deltas);
• display graphics and Sparklines (Magic Button Graphic or History or the Edit menu);
• calculate and display shares (Magic Button Share);
• drill down and roll up branches; and
• display and hide member properties (context menu).

In particular in operational reports, e.g. evaluations of customers, products, or materials – you can make the reports much easier to read with the table wrap. Give it a try now!

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.

Greetings, fellow data analysts!

When we demonstrate how DeltaMaster can be used to analyze variances during seminars and webinars on reporting, we sometimes find ourselves being asked the question: Where do you actually get the planned figures from that you use to calculate the variances? The answer is simple: From DeltaMaster! Some people may use it solely as a reporting and analysis tool, but for others, it is the most important instrument for collecting and storing planned figures. In this DeltaMaster clicks! issue, we reveal what users need to know about inputting data. The next issue will be aimed at report editors and application administrators, giving them an overview of how and where to adapt the DeltaMaster application to make data entry as easy as possible.

Best regards,
Your Bissantz & Company team

Data input reports

Planning in DeltaMaster uses Graphical Tables (pivot tables), i.e. the same format you know so well from reporting and analyses. In DeltaMaster, a data input screen is simply a report in which you can enter values using your Windows client, web browser or iPhone/iPad app. The option of making planning applications available on the web is especially popular: Planning cycles often involve employees who do not yet work interactively with DeltaMaster and instead receive their reports by e-mail or as PDF files in a user directory, for example. The web means they can be involved without having to install software – a link to the planning application is all they need to start inputting data.

If transaction control is enabled in the application, you can start to Input data by clicking on the corres¬ponding button at the top-left of the report; otherwise you can start to input data immediately. During data input, the Input button is replaced by symbols for accepting and saving the values   or canceling and discarding the data inputted . Filters, which can be set above the report, work in the same way as in reporting applications.

The small gray arrows and menu symbols in the cells and headers may also be familiar to you from reporting. They indicate links to other reports, typically to detailed views, e.g. from annual figures to the monthly distribution.

Select and input

Values are inputted intuitive¬ly: Select the desired cell and enter the new value or change calculation using the keyboard. Values can be inputted in cells with a gray background; the other cells are not editable.

• Click on a cell to select it. A dotted border shows that the cell has been selected. You can also move between cells using the keyboard, either with the arrow keys or using Tab and Shift+Tab.

• Type the new value or change calculation into the selected gray cell. If you are used to doing so, you can switch the selected cell into input mode beforehand using the Enter key or the F2 key, but you don’t have to do it this way. Enter the new value then hit the Enter key.

Once you have completed your input by hitting the Enter key, DeltaMaster automatically moves the selection to the next cell down or to the right (depending on the configuration). The double-click, by the way, has the same function as in reporting applications: It is used to perform a predefined navigation step or – in DeltaMaster 6.1.5 or later – an automatic navigation step if no or no more navigation steps are predefined. This is worth noting because you can actually input values in cells displayed as a result of navigation. In many cases, this can be especially useful as automatic sorting picks out the most important items in each navigation step, e.g. the products or customers with the highest revenues – and once these have been processed, you may well have already planned a good amount of the total value.
To select a cell in the app, touch it until the on-screen keyboard appears.

In-cell calculations

The value entered can be an absolute value that replaces the previous value or it can be a calculation – a little formula that calculates the new value based on the existing value and the change entered. This eliminates the need for pocket calculators or other tools.

Absolute values are inputted as simple figures with or without an algebraic sign before the value. Values inputted with a percentage sign are interpreted as decimals. For example, entering “8%” results in a value of 0.08.

DeltaMaster recognizes calculations when one of the operators “+” (plus), “-“ (minus) or “*” (multiply) is placed after the figure. The following calculations are available:

• Addition, subtraction: The operators “+” and “-“ are used to add the value entered to the existing value or to subtract the value entered from the existing value. These operators must be placed after the value entered, as otherwise they will be treated as algebraic signs. Absolute figures (“10+”) and percentages (“10%+”) are acceptable arguments. In the case of percentages, DeltaMaster calculates the proportion of the existing value and adds or subtracts it accordingly.

• Multiplication: The operator “*” is used to multiply the existing value by the value entered. It may be placed in front of or after the value entered. Percentages may also be used in this case.

The following table illustrates how the input formats work:

Old value	Input	New value	Explanation
300	10	10
300	+10	10
300	10+	310	Increased by 10
300	-10	-10
300	10-	290	Decreased by 10
300	10%	0.1
300	+10%	0.1
300	10%+	330	Increased by 10 percent
300	-10%	-0.1
300	10%-	270	Decreased by 10 percent
300	1.1*	330	Multiplied by a factor of 1.1 (increased by 10 percent)
300	*0.9	270	Multiplied by a factor of 0.9 (decreased by 10 percent)
300	*10%	30	10 percent of the old value

DeltaMaster also supports rules on how the values entered are calculated and saved in the database. This is known as input forwarding. Relationships between the measures can also be taken into account. More information can be found in DeltaMaster clicks! 07/2014.

Automatic aggregation and distribution of changes in values (splashing)

When you input values in hierarchies, DeltaMaster ensures that the totals are always correct. The parent and child elements in the hierarchy are adjusted automatically when values are entered: The aggregated elements are updated (bottom-up) and the change in value is automatically distributed proportionately (top-down) so that the total corresponds to the new value and the proportions remain the same. This is also known as splashing. The process is fully automated, so you do not need to do anything yourself.

??

Fix values

If your planning encompasses multiple dimension levels, you may wish to selectively disable the automatic value adjustments performed by DeltaMaster when aggregating/splashing. Certain values can be “frozen” so that they remain unchanged even when the parent or child values change. To ensure that the totals are still correct, DeltaMaster will automatically distribute the change in value around the frozen values. Cell fixation is the tool in question. You can turn it on or off via the context menu for a cell or by hitting the F6 key (provided the respective measure and the report are configured accordingly). The illustration shows how the fixation works.

??

In the example, an increase is planned for the North region but the total for Germany is fixed. This means the values for the other regions must be reduced. However, one of the regions, South, is also fixed. DeltaMaster therefore distributes the change across the East and West regions, as the following breakdown illustrates.

Customers	Old value	Fixed	Input	New Value
Germany	300	Yes		300	Fixed, so unchanged
North	120		+30	150	Increased directly by input
South	90	Yes		90	Fixed, so unchanged
East	60			40	Reduced by 2/3 of the change in value
West	30			20	Reduced by 1/3 of the change in value

In the report, the lock symbol is used to designate fixed cells. A gray lock indicates fixing that can be set and removed by the user when inputting data (in Presentation Mode), e.g. in order to distribute planned values to various customers without changing the total. A black lock indicates fixing that can only be set and removed in Edit Mode; the effect is the same.

Cell comments

The reason why a planned figure comes about or the considerations behind it are often as impor¬tant as the figure itself. With this in mind, com¬ments can be stored for all planned values. To enter, edit, and delete cell comments, use the corresponding function in the context menu (or the key combination Ctrl+F2 in DeltaMaster 6.1.6 or later). A small red triangle in the cell corner indicates that a cell contains a comment. Depending on how the application is configured, comments are also displayed at higher levels so that the user does not miss them even when the respective branch is closed in the report. More detailed information can be found in DeltaMaster clicks! 10/2015. Cell comments cannot be made using the app.

Report comments

In addition to the individual values, planners can also comment on the report as a whole. If this function is enabled, an icon of a speech bubble and a pen can be found in the status bar of the report on the right-hand side (). Clicking opens an input area in which you can create and edit a comment on the report.

The two small icons in the input area are used to accept or discard the input. Report comments are stored centrally in a database with a reference to the current filter settings (view). This means that different comments can be created for the same report for different months, scenarios, plan versions, customer groups, etc.

The icon of a speech bubble and a user silhouette shown above () is for comments that can only be edited in Edit Mode, i.e. by report editors and application administrators. These comments can be shown and hidden when inputting data in Presentation Mode. In planning applications, they are often used to assist users, e.g. by providing information on the business relevance of the figures that are the subject of the query, the planning process, or the report structure.

Copy and paste

The copy and paste functions can be used to edit an entire cell area in your Windows client or web browser. The cell area is selected using the mouse or the keyboard.

• Mouse: Click on a cell at the corner of the desired cell area, e.g. the top left-hand corner. Hold down the Shift key and click on the cell in the diagonally opposite corner of the desired cell area, e.g. the bottom right-hand corner.

• Keyboard: Select a cell in the corner of the area. Hold down the Shift key and use the arrow keys to extend the area from this cell upwards, downwards or to either side.

You can use the usual key combination of Ctrl+C to copy the selected values to the clipboard. Use Ctrl+V to paste the values from the clipboard into the report. This lets you transfer values from different cell areas of the same report, from other DeltaMaster reports, or from Microsoft Excel, for instance. If you try to cut data using Ctrl+X, a confirmation prompt will appear in order to prevent you from accidentally deleting data.

Selecting a cell area is only relevant for copying and cutting, not pasting. If the clipboard contains multiple values, DeltaMaster automatically pastes these into the input-ready cells below and next to the selected cell irrespective of whether these cells are also selected. If the target cells already contain values, these will be overwritten without prompting.

Copy and paste values retaining their distribution

In the copying process described in the previous section, values are entered in the cells of the table in the same way as if they had been inputted directly. By means of splashing, the copied value is distributed across the cells below the target cell in accordance with the existing distribution of the target. DeltaMaster also offers a second copying variant, in which the copied value is transferred along with all of the values below the respective cell in line with the multi-dimensional data structure. This preserves the existing value distribution of the source. You can copy values while preserving their distribution by dragging and dropping in your Windows client or web browser.

In the example, an entire value structure is dragged from one budget version to a second, empty version. This lets you quickly pre-fill the second version with values. If you were to simply enter or copy and paste the total as a value, DeltaMaster would have to distribute this value equally to all of the child cells, which typically results in unrealistic value distribution (and requires a lot of computing time).

Copy and paste data areas

The two copying processes described above (copy cell values using Ctrl+C/Ctrl+V, copy value distributions by dragging and dropping) are intended for visible cells or cell areas in the report. DeltaMaster also lets you copy, move, or fill larger data areas or even entire sections of the OLAP cube. These (copying) processes work like macros that are predefined and which include all of the settings for the copying process, so you can easily access them when inputting. The processes are often used to pre-fill planned values, e.g. by transferring the actual figures for the previous year as the planned figures for the current year or by copying seasonal distributions.

If processes are defined for the current report, these are available in the Execute Process section of the Report menu (on the right-hand side). If this section is not shown, this means no processes are defined for the current report. Launching a process initially opens a dialog informing you about what the process will do and letting you set parameters depending on the configuration of the process definition. Information on working with processes can be found in DeltaMaster clicks! 09/2014.

Run special functions

A project-specific additional DeltaMaster module lets you integrate an action menu with application-specific functions into the interface. This is displayed next to the application menu in the top left-hand corner of the DeltaMaster window. Like the menu, it is only visible when the mouse pointer is moved to the menu bar area.

If a calculation or simulation function is implemented in the planning project, DeltaMaster displays an additional button with an „f“, the symbol for mathematical functions.

Inputting data records with SQL Drill Through

All of the processes described here relate to inputting data in Graphical Tables. You can also input data in SQL Drill Through and save it in a relational database. The underlying concept, the application scenarios, and the preparations in terms of the database are different to those for the typical multidimensional planning applications assumed in the above discussion. In particular, relational input applications let you create new data records, e.g. new inquiries or orders (transaction data) or new customers, materials, or scenarios (master data). Users typically input not only numerical values in the tables, but also other data types, especially text. We mention it here for the sake of completeness – relational inputs will be the subject of

Greetings, fellow data analysts!

Fulfilling some of the analytic requirements from business departments is often easier said than done. Let’s assume that your new marketing director wants to know what the current demand profile is and how it has changed over time. But what is a demand profile? How would you describe it or show its development? A little business sense is helpful to begin with. A demand profile could be the revenue breakdown by article groups. You could then also formulate the task as follows: Which article groups generate which share of revenues and how have those shares changed over time? That is where DeltaMaster 6 comes into play. With just a single mouse click, you can calculate shares as well as visualize them and their development. We’ll explain how below. You’ll see that some analyses are even easier done than said!

Best regards,
Your Bissantz & Company team

Automatically calculating shares in lists

Many reports on business operations are created as simple lists that contain just a single data column displaying, for example, volumes or revenues for customers, articles, or materials.

In DeltaMaster 6, you can easily display the shares of the total as an additional column – with a single click on the Share/% Magic Button.

This function recognizes and supports multilevel lists. In other words, you do not need to worry about which level the calculation is based on. If you wish to rename the column, you can enter an alias in the Axis Definition.

The filters (view) are decisive for the summation. Whether subordinate members are visible or hidden in the Axis Definition (e.g. due to a Ranking or filter rules) does not make a difference. If these types of limitations apply, DeltaMaster indicates this in the status bar of the report.

The additional column has no effect on the analysis model and, in this sense, works like Row, Column, and Table Aggregations. That makes it very easy to use; however, you cannot use these shares to make other calculations somewhere else. If the Share Magic Button is not available, this is due to the underlying data: DeltaMaster does not offer this function if this presentation is not possible with the current table structure.

A further mouse click, this time on the Graphic/Auto Magic Button, visualizes the shares as segments. In the screenshot on your right, the values determine the intensity of the color as well. This feature in the Options on the Presentation tab is available as of DeltaMaster 6.1.5.

These segments are not to be confused with pie charts. Normal charts would slice the pie and leave the slices on the “plate” so that you cannot compare the slices. DeltaMaster, in contrast, shows a segment for each object so that each slice gets a separate plate. To be precise, we really should be speaking here of sectors, as this is the geometric term when pie pieces have their points in the center. Segments, on the other hand, are cut off from the circle by chords. You can completely break a circle down into sectors but not into segments (with the exception of a semicircle). For business scenarios, however, that is basically just quibbling. When you talk about markets, you talk about segments. Period.

Now let’s take a look at the options for visualizing shares.

Visualizing shares: Fill Bars, Fill Columns, Fill Circles

If you want to visualize shares as charts, the Graphic Magic Button is the place to get started – and a click on Auto is always worth the effort. When you click on this button, DeltaMaster creates the right visualization for the data automatically.

In the example above, DeltaMaster has automatically recognized that we are interested in shares. With a simple click on Auto, the software will present the segments to emphasize this.

For special types of share views that cannot be generated automatically or deliberately need to vary from the automated design, you can work with the menu of the Graphic button. This menu contains six different chart types, which you can recognize by the icons with partially filled graphical elements. You can combine these charts into three pairs: bars, columns, and circles, each with shapes of the same (constant) or different (variable) sizes.

The shares are already calculated

The chart types with shapes of the same size are intended for visualizing shares that are contained in the table’s structures as percentage values – in other words, as a Measure or through (Calculated) Members (see DeltaMaster clicks! 07/2008). You can also use the Share Magic Button to visualize these calculated values.

The shapes with the same sizes everywhere represent the constant, maximum share of 100 percent whereas the filling indicates the respective share. In other words, the presentation is normalized. The list above with the calculated share column is an example.

If the table does not contain percentage values, all three chart types will be deactivated in the Graphic menu because the visualization cannot be used. DeltaMaster does not display any elements as charts for values exceeding 100 percent; after all, it cannot do more than fill the shape’s entire border. This is why you should only use fill elements when you are working with genuine shares – and not with other percentage values such as relative variances, load capacity, or goal achievement rates, which can exceed 100 percent.

Calculating, displaying, and visualizing shares

The chart types with variable shapes represent absolute and relative sizes at the same time: the absolute ones on the outside (as the border of the shape), the relative ones on the inside (as a filled segment). In contrast to the variant explained above, the shares do not already have to be in the table. Instead, DeltaMaster calculates them automatically, displays them in the respective cells as a second value, and visualizes them as charts.

Since the scale is very important in this type of presentation, DeltaMaster regulates it automatically and documents it in the report’s status bar.

• Shapes, globally scaled
  In order to illustrate relationships in absolute terms, DeltaMaster views all of the values from the table together and draws the bars, columns, and circles to the same scale. Since the length, height, and area (of the circle) are proportional to the absolute values, they are comparable throughout the table.
• Segments are calculated based on the typical reading direction and are drawn cell by cell
  The shares on which the size of the filled segments depends are calculated row by row for columns, and column by column for bars and circles. This means that DeltaMaster adds up the total for each column or row and calculates the share of the individual values in relation to the column or row totals respectively. DeltaMaster displays these shares in the cells and highlights them as filled inner segments of the bars, columns, or circles.

What is so ingenious about this visualization is that it displays the absolute size and the share at the same time.

This logic implies that these segments cannot be viewed additively. That is obvious with circles because you cannot combine circular segments with different radii into a whole. That also applies to bars and columns: Small values result in small bars or columns – and small shares as well. This is why only a small part of the small bars or columns is filled. If you placed the parts end to end, you would not get a length of 100 percent because each section is measured in relation to its own absolute value. When interpreting the chart, take a close look at the outer dimensions to compare the absolute sizes. The filled inner segment gives you a feel for the share of an object.

If multiple hierarchy levels are displayed on the axis, you need to ensure that the calculations refer to the correct level. You can define this level in the Axis Definition on the Options tab; this setting is also valid for Row, Column, and Table Aggregations.

The chart types with a variable size are ideal for absolute values. Since DeltaMaster calculates the shares automatically, you no longer need to model measures or calculated members in order to create a share analysis. This presentation also works with percentage values. Reading them, however, takes some practice because each cell contains two percentage values – the original value, which is treated as a percentage point, and the (unweighted) share calculated from the total.

Adding charts

As with all chart types, you can add graphical elements for shares to a report in one of three ways:

1. By clicking on the icon in the Graphic menu
   To show the chart in all (suitable) columns or rows at once, click on the corresponding icon in the menu of the Graphic Magic Button.
2. By dragging the icon from the Graphic menu and dropping it in the table
   To show the chart in only specific columns or rows, drag the icon from the menu and drop it in a row or column – either on the headers or directly on the values in the table. DeltaMaster automatically recognizes where the charts should be displayed (i.e. bars and circles in columns, columns in rows). You can follow these steps several times to add charts to multiple columns or rows in succession.

A subtle change in the menu indicates that you can use the drag-and-drop function: When you hover over an icon with your mouse, the menu’s frame border opens and invites you to drag the icon out.

1. By activating the Graphic in the Edit menu (on the right)
   As an alternative to option a), you can also use the menu to the right of the report to display a specific chart type in the entire table. Clicking on the Graphic entry opens and closes the list of chart types. By clicking on a chart type, you activate and deactivate this chart type for the entire table. From the Edit Menu, you can activate the chart types, as described here with a constant size, as Fill Bars, Fill Columns, and Fill Circles. The variants with a variable size are available only through the Graphic Magic Button. (These share values that are calculated and displayed in addition are part of the magic, as it were.)

Each table supports only one chart type so the report stays easy to read.

To remove the charts, follow these same steps in reverse. If you want to remove all charts, click on the icon highlighted in orange on the Graphic Magic Button or the bold entry below Graphic in the Edit Menu (on the right). If you merely want to remove charts from specific columns or rows, drag one of the elements that you want to remove from the report and drop it either back in the bar with the Magic Buttons or in the empty space next to the table.

Settings

The Properties of the table include options for defining the size settings: the width (or length) for Fill Bars; the width, height, and alignment in the cell (i.e. the position of the columns in relation to the values) for Fill Columns; and the radius for Fill Circles. These parameters are valid for charts with constant and variable sizes alike (for variable sizes, the parameter applies to the extreme value; all others are measured proportionally). In most cases, you don’t need to worry about these parameters: The default values are designed to create an appealing presentation and enable good differentiation.

You can find an abbreviated form of these settings in the Edit Menu (on the right). By clicking on the gear icon next to the active chart type, you directly open the respective tab in the Table Properties. DeltaMaster also offers this icon for Color and Sparklines, provided that color and/or sparklines are activated in the report.

Analyzing changes in shares

You can run a very productive analysis simply by letting DeltaMaster take charge automatically. This also allows you to intuitively visualize how the shares have changed over time.

What makes this presentation so special is the coloring of the segments. The colors show if a share is greater than or less than it was in the previous period. This is very helpful because shares rarely change rapidly, the values are typically close to each other, and the segments otherwise are almost impossible for the naked eye to differentiate. Take a look at the Standards in the screenshot above. The 44.4 percent from August seems to be just as large as the 43.0 percent from September. The color clearly shows, however, that the share has dropped! The color of the shape depends on the Business Intelli­gence factor of the Measure (in this case, blue for units sold). The color of the segment depends solely on the difference with respect to the previous month. A smaller share is colored red for revenues as well as for costs, whereas a larger share is colored blue.

Otherwise, the structure of the Graphical Table closely resembles those of the other automated share analyses. The circles have variable sizes and are globally scaled so that you can compare the absolute values row by row over time, and column by column in the structure. The shares are calculated by column; each column (period), therefore, adds up to 100 percent. That is how you show how the demand profile has changed! The marketing director that we mentioned in our introduction would be very pleased.

You can generate this presentation with a single mouse click on the Graphic/Auto Magic Button. Your only prep work is to put together the data you need in the report. That is not hard at all because the task at hand defines it: one KPI (Measure), the time, and the profile-forming attributes. You merely need to pay attention to a few details regarding the way the report is structured:

• The Measure is located in the filter (the “slicer”) and thus listed in the top-left part of the table.
• The time members are located on the column axis. Calculated Members, including Time Analysis Members, or other dimensions cannot be used there.
• The dimensions that create the profile and for which the shares are to be calculated are located on the row axis.

The presentation displayed above can only be created using automation and not through individual settings in DeltaMaster. You can also interpret that this way – with DeltaMaster, you get the best results automatically!

Compatibility

The calculated share column described at the beginning works in DeltaMaster 5 as well, but without the show/hide option. You can also use DeltaMaster 5 to define and display Fill Bars, Fill Columns, and Fill Circles with a constant size for shares that are already in the table. Fill elements with variable sizes and the one-click capabilities to show share values are only available in Delta­Master 6. In DeltaMaster 5, they are displayed as simple bars, columns, or circles and without the share values.

 

Greetings, fellow data analysts!

When you are searching for new insights in data, you sometimes need to draw on new data – data that goes beyond what is offered in a data warehouse and, quite possibly, will only be collected for a specific purpose. For example, you may want to supplement the information that you provide on a regular basis with special analyses that stem from other systems or you might want to include external data, such as raw material prices, weather data, market analyses, or statistics from the internet. In applications that only contain actual data, you may want to add at least the aggregated plan or budget values, for example, for the business units, divisions, or regions. These types of supplementary data are often stored in Excel files. You can easily transfer data in this format to existing DeltaMaster applications. That makes the path from new data to new insights fast and easy! We will explain what you need to know on the following pages.

Best regards,

Your Bissantz & Company team

There are several ways to integrate data from Microsoft Excel in DeltaMaster applications:

a) In the context of automated data provisioning, data from one or more Excel files can be automatically transferred to a data warehouse, where it is linked with data from other systems. In this case, the import from Excel is part of the extraction, transformation, loading process (ETL) and works like transfers from other source systems. Setting up the processing, which takes place outside of DeltaMaster, requires technical skills and permis¬sions. This is typically a job for IT or tech-savvy business users.

b) In self-service applications, an Excel file is the primary data source. DeltaMaster, therefore, does not access the data from a large data warehouse but from the Excel file, which was automatically transformed for this purpose into what is known as a local cube file. Business users can easily create, update, and distribute these types of applications with DeltaMaster. These applications, however, are for self-service scenarios. That means they only support multiple users to a limited degree and are not intended for large data volumes.

c) Aside from the data in a data warehouse, values from Excel can also be loaded into the DeltaMaster application. The primary data source is the data warehouse. In addition, DeltaMaster reads values from one or more Excel files and makes them available as a Measure in the application. Setting this up is simple – even for business users. No changes in the data warehouse are necessary. The application retains its multi-user capabilities.

d) In planning applications, values from Excel can be copied to input masks or even imported row by row from an Excel file. Plan values that were generated in other systems can then be transferred to the central planning processes of DeltaMaster. Planners can run the import on their own, even over the Web. The process of collecting and checking worksheets is no longer a central task and can be delegated to the planners. Only the import procedures need to be set up centrally.

This edition of clicks! will focus on scenario c): transferring values from Excel files to an existing application. Such a combination of data sources is also called a mash-up.

Use case: Sales controlling and CRM

We will explain the procedure based on an example from our “Chair” reference model. We want to extend an existing analysis to show how many calls the respective internal sales team made to specific clients.

Information about phone calls does not exist in sales reports and analyses used for accounting. The IT department, however, kindly exported the data for a short period from the CRM system and delivered it as an Excel list so that we can compare it to the sales controlling. In the screenshot above, you can see how this list is displayed in DeltaMaster – and how it looked in Excel. The revenues shown here come from the known OLAP application. We supplemented the number of calls from the Excel file.

This example reflects the first use case from the introduction – a special analysis from another system. In this scenario, as well as the other two that we described (i.e. budget values, external sources), we could have also integrated the data on the database level. After all, connecting data from various sources is one of the main benefits of a data warehouse, and users can enter plan, budget, or simulation values directly in DeltaMaster and save them to a data warehouse. Maintaining the necessary structures in the database, however, requires special skills. It also involves a certain amount of effort, which you may not always want to take upon yourself – especially when there is only a small amount of data that you want to combine or it changes irregularly or rarely at all.

Creating and defining a Measure for external values

With DeltaMaster 6, you can define separate Measures (KPIs) that pull their values from an Excel file instead of from a database query. In order for this to work, the application must be based on MDX (e.g. Microsoft Analysis Services or SAP BW). You can also display and use these types of Measures in DeltaMaster 5. However, DeltaMaster 6 is required to create, edit, and update them.

To create a new Measure, switch to Edit Mode. When Modelling, click on the three dots in the Measure Bar to open the Measure Browser. There, open the I want to menu to add a new measure.

For the Measure Type, select External Measures.

To define the Measure, you only need two entries: which file you want to use (Microsoft Excel workbook) and which Worksheet in it. DeltaMaster supports all popular file formats, including the old XLS format (prior to Excel 2007) as well as the newer XLSX, XLSM, and XLSB formats. We will explain below how to set up the table as far as content is concerned. As soon as you select a file, DeltaMaster will import it and offer your worksheets, including the hidden ones, in the selection list. The measure is based on exactly one worksheet that you choose from the list. That’s all! Just enter a name for the Measure and, if you wish, a description, and the new Measure is available in the current application.

In the Measure Browser, the Measure Type is marked as “ExternalData”.

Transferring by copying, without locking

When you create a Measure (or update a Measure as described below), DeltaMaster imports the selected Excel worksheet and copies the values to the application or analysis session. As a result, you can still work with the values even if the Excel file is no longer available in the future. This is possible due to the way that DeltaMaster captures the data: It reads out all data from the file in one go and copies it to the application (instead of opening a connection to the file and running queries).

This prevents issues with locked files as well! Even if you or another user have the file open in Excel or another program, you can still define, update, or use the Measure in DeltaMaster.

Presenting and using external values

This new Measure works just like any other Measure. It can be displayed in Graphical Tables, alone or in combination with other Measures, including those that originate from the same or a different Excel file. It can be used in calculations such as User-defined Measures or Filter Measures. You can Browse, Zoom, and Navi¬gate the Measure. You can use Measures from external data in all other types of reports as well: Geo Analysis, Portfolio Analysis, Time Series Analysis, and the methods of the analytic library. As with other User-defined Measures, data entry is not supported.

Let’s look at the example on the right. The number of calls from the Excel file is listed next to the revenues from Analysis Services. The calculation for Revenues per Call was defined as a Quotient Measure in DeltaMaster. Sparklines shed light on how the number of calls has developed over time. Using the Naviga¬tion, we have identified which sales teams made the calls and generated the revenues for the first customer.

Displaying and calculating with external values requires more computing time than database queries. This is the reason why the method is not suitable for large data volumes. A few thousand rows, however, are usually no problem.

Updating values: Shift+F9 or Measure Properties

What is really neat is that the Measure can access a fresh supply of data – in other words, you can update the values that were transferred to the application! In this case, DeltaMaster will reopen the designated Excel file and import the values again. You can update the Measure in two ways:

• In Presentation Mode or when Editing, you can start an update by pressing Shift+F9. The Shift key, to an extent, strengthens the function of the F9 key: F9 alone will recalculate the report with the current values from the database. In combination with the Shift key, it updates the values of the Measures from Excel contained in the report. This update in one report also affects all other reports that use this Measure.
• In Edit Mode when Modeling, you can refresh the values in the Measure Properties, independently of the reports. The Definition tab contains a link to update the data.

The ease of use described above also applies to updating. You do not need to close the Excel file in advance. You can edit your values in Excel and update them in DeltaMaster at virtually the same time.

When working with applications in the Repository, you can only use Shift+F9 to update in the Windows client of DeltaMaster 6. This shortcut is not supported in the Web Client, in the App, or in the Office Add-in. In these cases, the application must first be updated in the Repository to make the new Excel data available in the application. If this task needs to be performed on a more frequent basis, you may want to create a job in Publisher (ReportServer) to update the Repository.

In DeltaMaster 5, you can use Measures with external values but you cannot update them in DeltaMaster 5. Applications in the Repository or analysis sessions (DAS files), therefore, must be updated with DeltaMaster 6 before they reflect the current values in DeltaMaster 5.

Setting up the Excel table

The Excel table must have a certain setup so that DeltaMaster can process it. The setup is a very common one, as you will see in the following example. The screenshot on your right shows an excerpt of the data. Each row lists the respective month, customer, sales team (Sales), and the number of phone calls that the respective team made with that customer in that particular month.

The setup of this table can be described using the following properties.

(1) List format
The table must be set up as a list. That means that each row contains a single data record. Cross tables are not supported.

(2) Column headings
The first row contains the column headings. These names must correspond to those of the dimension levels in the analysis model. The names displayed in the Dimension Browser – or, wherever appropriate, the current Alias Set – are what is important (not internal IDs or MDX names with brackets, etc.).

(3) Left side: One or more columns with Members
The first columns contain Dimension Members. These Members must belong to the level that is listed in the column heading and correspond to the current Alias Set (provided that one is active).

(4) Unique Member combinations
The combination of all Members in a row describes which cell in the OLAP cube should be assigned the numerical value. The Member combination, therefore, is a compound key that must be unique and complete. It is not allowed to have multiple rows with identical Member combinations, and all fields in each row must be populated.

(5) Right side: A column with the numbers
The numerical values are located in the last populated column on the right. The column heading is irrelevant because the name of the Measure will be defined in DeltaMaster.

Fulfilling these criteria does not involve much work because most exchange files are generated in this format anyway. One thing you’ll need to watch is covered in item (4) above: If multiple operations refer to the same Members, they will need to be aggregated in the Excel file. This is why our example shows exactly one row for each customer and month, with the total number of calls listed in the right-most column – and not, for example, one row per call with a count value of 1.
DeltaMaster will also transfer data records that cannot be allocated in the data model. If these records are not needed, they should be deleted beforehand.

How the application and analysis model interact

When DeltaMaster reads the records, it assigns the values to exactly one Member in each dimension referenced; the Members belong to the same level. The Measure returns no values below this level. Above it, the values will be automatically aggregated to a sum.

The levels in the analysis model must have unique names so that DeltaMaster can assign the column headings to the desired levels. This is not always the case. In some parent-child dimensions, for example, the levels are only numbered, and names such as “Level 02” can appear in customer as well as article dimensions. In these cases, you can rename the levels in DeltaMaster (when Modeling, click on the respective dimension in the Filter Bar, switch to the Levels tab, and rename the level in the context menu of the level). Aside from stabilizing the integration of external values, this improves the application as a whole because the level names are displayed in different parts of reports and on the user interface. Taking the time to create logical, unique names is definitely worth the effort.

When you assign values to level and member names, DeltaMaster will apply the respective current Alias Set as mentioned above. This makes it easy for users to prepare external data (i.e. how it looks in DeltaMaster is how it must look in Excel).

External values vs. DeltaMaster Modeler and self-service BI

As tempting as it may sound, the approach described above is no substitute for automated ETL processes or custom applications with an Excel file as a data source. The main reason is that you cannot build structures with the external values. You can easily “attach” them to existing structures, on a certain level in every referenced dimension. Upward aggregation starting on this level is also possible – but the hierarchy must already be in place. Aside from that, the Excel data is always integrated as a Measure; you cannot add any additional Members to a dimension. Also in cases where you need to integrate large data volumes, you should turn to other solutions. Fortunately, you can count on DeltaMaster for this as well. With the Modeler and the self-service functions, you have the professional tools to build both small and large applications from scratch.

Greetings, fellow data analysts!

Let’s stick with the navigation (Pivot Navigation): Sometimes you may want to establish some sort of standard agenda within a department – a common approach how data should be analyzed and discussed. With DeltaMaster, report editors can predefine a series of analytic steps so the recipients can easily follow this common thread. When a user double clicks on a value or selects Navigate/Plus in the Analysis menu, DeltaMaster will add exactly one attribute to the table and break down (i.e. drill down on) the respective value based on this attribute. This way, the presentation is refined step by step on a predefined standard analysis path, for example, to examine revenues or cost drivers or explain variances. How do you define such a standard analysis path? We have already explained one possibility, which is to define it for and save it along with a specific report. DeltaMaster 6 offers another option: You can predefine a specific Navigation also for measures, which allows you to reflect the respective business context. We will explain how in this third part of our series on Navigating in Graphical Tables.

Best regards,
Your Bissantz & Company team

You can create one Predefined Navigation per report in both Delta¬Master 5 and DeltaMaster 6. In DeltaMaster 6, you can even define the Navigation for specific measures. This has two advantages:

• You can define the standard analysis path individually for each measure. This allows you to predefine different analysis paths e.g. for financial KPIs than for performance KPIs, for revenue items others than for expense items, or for absolute values others than for shares.
• The standard analytic path for a measure applies to all reports of the application. This simplifies the usability for report recipients: When they use the Predefined Navigation, DeltaMaster will break down the measure the same way – no matter in what report. And report editors only need to define this path once to make it available in all reports of the application.

Let’s use an example from our “Chair” reference application. If you are analyzing revenues, you might want to start with sales teams, followed by customers, followed by products. In the case of material costs, however, you may want to start your analysis by colors, followed by products, followed by customers.

You can easily recognize the different analysis paths in the screenshots above. Of course, you could have created these views with a User-defined Navigation (using the Hierarchy Menu that is available at every value). In this case, however, the paths were predefined and run in Presentation Mode, either with a double click or by selecting Navigate/Plus. This functionality is supported in the Windows client, the Web browser, and the App for iPhone and iPad.

Defining the path with drag and drop

You can set up an analyis path in the Measure Properties. In DeltaMaster 6, you can edit these properties when Modelling, by clicking on a measure and switching to the Analysis Context/ Navigation tab. Here you can define which attributes can be used to analyze the measure (Analysis Context, left) and which attributes to offer in which order for the Predefined Navigation (right).

To edit the Analysis Context, use the tree structure on the left-hand side. This displays the dimension groups, dimen¬sions, hierarchies, and levels of the analysis model very similar to the report-specific Navigation Context (see DeltaMaster clicks! 10/2016) or the Filter Context of reports. If attributes are deactivated here, they will not be available in the analytic methods or in the Navigation. This includes the Predefined, User-defined, and Automatic Navigation – even when the attribute is activated in the Navigation or Filter Context of the report.

The entries in the tree structure on the left also serve as building blocks for the standard analysis path that you define on the right. The handling is very easy: Drag and drop the desired steps, arrange them as you wish, and configure them.

• Adding a navigation step: Drag the dimension, hierarchy, or level from the tree structure (left) and drop it in the field on the right. DeltaMaster will always enter a level in the path. If you move a dimension or hierarchy, DeltaMaster will automatically select a level (the first one below the “All” level).
• Editing a navigation step: Select the entry (in blue font). This opens a dialog box that is derived from the Axis Definition. Use this dialog box to make detailed settings, e.g. which members to include in the step and how to display them (using Ranking and Filter).
• Sorting navigation steps: Move the entries (in black) on the right using drag and drop.
• Removing a navigation step: Move a navigation step (in black) from right to left using drag and drop.

This operation method is even faster than defining a report-specific path: First, you have to execute the navigation in the report, step by step. Afterwards, you have to save the result as a default, undo the steps, and then save the report (see DeltaMaster clicks! 05/2015). In the case of a measure, however, you can edit and save the order of the steps and the settings for the steps in a dialog box.

When you are editing a navigation step, the Ranking tab is very important. Here you define how to sort the members and if you want to limit them to a certain number. When you are analyzing variances, the most interesting options are “TopCount/Bot¬tomCount” (descending) and “BottomCount/TopCount” (ascen¬ding) because they display positive and negative variances together in the table. The column index that is used is based on the cell for which the step is run. DeltaMaster automatically selects the sort direction based on the value of the cell.

Each dimension can only be used once in the analysis path. Hierarchies with only one level and with no top member (e.g. Value Type or View of period) are not suitable because no further differentiation is possible. The levels that are included in the path should be activated in the Analysis Context (left side).

A navigation path that is predefined for a measure overrides a report-specific path. User-defined and Automatic Navigation are still possible – the predefined path is just an additional offering so report recipients can easily break down KPIs based on a standardized line of arguments.

Greetings, fellow data analysts!

In the last issue of DeltaMaster clicks!, we showed how to use the Navigation to start an individual analysis from an existing report. This enables report recipients to work with the data on their own and transition very smoothly from an overview to the details – wherever this option is necessary or seems gainful. In Delta­Master 6, users can navigate in every Graphical Table and also in the Presentation Mode. The Navigation becomes especially easy and efficient when the reports are prepared accordingly. In this edition of DeltaMaster clicks!, we have put together some tips – at a glance and in detail.

Best regards,
Your Bissantz & Company team

There are four starting points to prepare and set up the User-defined Navigation (Pivot Navigation) in the Presentation Mode:

1. Define the Navigation Context. This allows you to control which attributes should be available in the User-defined Navigation – in other words, which entries should be available in the Hier­archy Menu offered for all data cells. The Navigation Context also limits the search area for the Automatic Navigation (data mining).
2. Check the general settings. This includes setting a limit for the number of members that should be added in each step and combining the rest to residual members (“remaining top” and “remaining bottom”).
3. Predefine the standard analytic path. This allows users to navigate using the Analysis Menu (Navigate: +, Auto, –) or by double clicking on a value. It doesn’t get easier than that. To predefine an analytic path, run the Navigation in the Edit Mode following the same steps that it should later use in the Presentation Mode and save this navigation step as a default. In DeltaMaster 6.1.4 and prior versions, this function is located in the Properties of the table on the Navigation tab. In future versions, you can access it as part of the Navigation of the current report in the Edit menu (on the right side of the DeltaMaster window). For more information, please read DeltaMaster clicks! 05/2015.
4. Define the Analysis Context of measures. For KPIs, you can define which attributes should be available for detail analyses using the Navigation, similar as in reports. We will explain this functionality further in a later issue of DeltaMaster clicks!.

We will take a closer look at the first two points below.

Modifying the Hierarchy Menu with the Navigation Context

In a User-defined Navigation, users can decide in the Presen­tation Mode which attribute they want to break down (i.e. drill down into). DeltaMaster offers a Hierarchy Menu for this purpose. This is displayed when you point your mouse on the small, black triangle at the bottom of a cell.

A good menu setup is essential to work effectively with and build acceptance for the User-defined Navigation. The more attributes are offered the more flexibility users have in their analysis; this, on the other hand, makes it harder for them to choose which direction to take or to find the desired attribute. If users feel that the menu is too long or confusing, this is usually because the Navigation was set up too vaguely, if at all.

The Navigation Context allows you to fine-tune the Hierarchy Menu for a specific report. You can modify it as part of the Navigation of the current report in the Edit menu.

All structure attributes of the analysis model are listed on the Navigation Context tab (see screenshot on the next page):

	dimension groups, that make it easier to work with dimensions in DeltaMaster (e.g. Period, in the following picture);
	dimensions (e.g. Period, Cumulation, View of period, and Customer);
	hierarchies (e.g. Customer and Branch in the Customer dimension); and
	levels (e.g. Main product group, Product group, and Product in the Product hierarchy and dimension).

Report editors know this tree structure from other DeltaMaster dialog boxes, such as the Filter Context (View Context) in the Report Proper­ties or the Measure Context. By ticking the check boxes, you can activate and deactivate the respective attribute.

	A deactivated attribute is not available in the User-defined and Automatic Navigation and, therefore, will not be of­fered in the Hierarchy Menu.
	An activated attribute in the Navigation Context is available, generally speaking, in the User-defined and Automatic Navigation. It can, however, be excluded because of other limitations (see section below).

The Navigation Context is not initially limited in a new report. In other words, all attributes are activated. In large analysis models, this can lead to very long Hierarchy Menus that are hard to handle. We recommend, therefore, to always keep the Navigation Context in mind and to customize it.

When Editing, you can work with all attributes to create the Predefined Navigation because the Navigation Context is not yet in force. The limitations only come into effect in the Presentation Mode.

Navigation Context and other limitations

The Navigation Context defines the framework for analyzing data in the Presentation Mode. In actual operations, however, there are four additional factors that determine the attributes in which users (still) can navigate:

• The respective measure the user is analyzing: When Modelling, you can define an Analysis or Navigation Context for each measure, similar as you would for the report. Users cannot Navigate into attributes that are deactivated in this context – regardless if they are using a User-defined, Predefined, or Automatic Navigation.
• The filter (view) that is currently set in the Presentation Mode: Users can generally only drill down on levels below the members selected as a filter. If in a dimension, for example, a member of the lowermost level is selected as a filter for the report, no further differentiation is possible for this dimension. Accordingly, this dimension will not be offered for Navigation.
• The table setup: This also applies to members that have become part of the report through a Level or Member Selection in the Axis Definition.
• The Navigation steps that were previously taken: Finally, the Hierarchy Menu gets smaller with each Navigation step taken because each level can only be used once.

In contrast, the Filter Context (View Context) from the Report Properties only has an indirect influence on the Navigation. It determines which possibilities for changing the filter (view) of the report are available in the Presentation Mode. This, in turn, can mean that a certain attribute is available or not for Navigation. The Perspectives (see DeltaMaster deltas! 6.1.3, feature #9) only have an indirect effect on the Navigation as well.

Better few but thought-through

Our recommendation: First deactivate all dimensions using the respective link and then activate the three or four dimensions that users will most frequently need in the report. Just select the most important ones – no more, not too many, and not everything that someone might need at some point in time. This applies to dimensions as well as (parallel) hierarchies and their levels. It is often not necessary to drill down, for example, to the lowest level of material or customer master data in all reports.

The Automatic Navigation also profits from a narrowly defined Navigation Context: Fewer dimen­sions, hierarchies, and levels mean that the data mining algorithm needs less time for calculation – and delivers results faster.

Checking the general settings

It also is worth the time to check the other settings for the Navigation. The General tab offers three options as explained below.

• Limit result to “x” members: This limit ensures that the Automatic Navigation only displays the “x” largest or smallest or the largest and smallest members. This “x” also serves as a default limit for the Ranking in a User-defined Navigation. If necessary, you can deactivate this limit to enter all members in the table. This, however, often leads to excessive results and should only be done in special cases. We strongly recommend limiting the results. The default setting is 10 members, but 5 – maybe only 3 – often suffice.
• Show top and bottom members including remaining members: This is especially interesting when users analyze variances in the report. If the option is activated, DeltaMaster will calculate the largest positive values (“top”) and the strongest negative ones (“bottom”) and insert them together in the table. At the same time, it will combine the remaining members with positive and negative values and display them as “remaining top” or “remaining bottom”. For more information, please read DeltaMaster deltas! 5.5.8, feature #13. In order for this to work, the aforementioned option that limits the result must be activated and the application must run on an MDX OLAP database (e.g. Microsoft Analysis Services or SAP BW). For further tips on how to compare positive and negative variances in the same report, please read DeltaMaster clicks! 02/2015.

• Allow Navigation on the column axis: If you activate this option, DeltaMaster will show the small black arrow that you click to display the Hierarchy Menu not only at the bottom but also to the right of the data cells. This allows users to expand the table on the column axis. In certain data scenarios, this can make sense – for example, if you want to break down a year’s value by quarters or months. Most reports, however, are designed so that the differentiation works better “downwards”. That is why the Navigation “to the right” is deactivated in the default setting.

Using these general options and the Navigation Context, you can prepare each Graphical Table so it is easy to use in the Presentation Mode. You and your users can then explore this data in a clearly defined environment – a common requirement in Self-Service BI.

Greetings, fellow data analysts!

Starting with DeltaMaster 6, a number of functions that previously required specific licenses are now available to all users. This includes Browse, Zoom, and Navigate, the three most important functions to start an analysis spontaneously from a given report. Using these functions in DeltaMaster 6 is simple – even for those with little practice or training. This opens many new possibilities to design reports and applications!

The Navigation, in particular, has what it takes to drastically simplify Business Intelligence so it can play a more active role. We warmly recommend this method – so much that we have made it the focus of a small series in DeltaMaster clicks!. It is well worth the effort migrating existing reporting systems to utilize the Navigation and developing new ones for.

We will explain below what report recipients need to know about the Navigation. The next issue will show how report editors can set it up to make it easy for their report consumers – and easy on themselves.

Best regards,
Your Bissantz & Company team

Navigating (the Pivot Navigation) in DeltaMaster is a patented method for expanding Graphical Tables (Pivot Tables) step by step. With each step, it drills down on the initial KPI using a further attribute. This breakdown helps quickly reveal the causes of a variance or the most important drivers of revenues or costs.Report recipients and editors both benefit: The recipients of these clear, easy-to-understand reports have the opportunity to examine the content in detail if neces­sary. Report editors profit from dynamic, easy-to-maintain reports – and often a reduction in the total amount of reports that they need.

Select cell, start to navigate

The Navigation is supported in all Graphical Tables – in other words, a large portion (possibly even all) of the reports in an application. In DeltaMaster 6, you do not even have to set up the reports first.  We do recommend that you configure the Navigation first as we will explain briefly below and in more detail in the next issue. But even without any prep work, you can still navigate any Graphical Table. The handling in the Windows client, the WebClient, and the App for iPhone and iPad is identical:

1. Select the value that you want to analyze by tapping or clicking (once) on the respective cell. The selected cell will have a dotted border.
2. Run a Navigation step. You can do this in one of three ways:

• through the Analysis Menu below the report (Navigate: +, Auto, –),
• through the menu that DeltaMaster directly offers at the bottom of the selected cell (with a small black triangle), or
• with a double click on the value.

You can repeat this sequence (i.e. selecting a value, running the Navigation) as long as there are still available attributes that can appear but have not yet been displayed in the table. In this way, the analysis becomes more and more granular, yet remains easy to use and read: one cell, one step, one more attribute. In most cases, you do not even need to select the cell for the next step because DeltaMaster moves the selection marker with each step and automatically selects the next cell.

You can analyze the selected value in one of three ways:

a)    with the Predefined Navigation along an analysis path that a report editor has defined in advance;

b)   with the Automatic Navigation along an analysis path that DeltaMaster automatically takes based on a data mining method; and

c)    with the User-defined Navigation along an analysis path that you choose step by step.

You can run the Predefined and Automatic Navigation through the Analysis Menu. For a User-defined Navigation, use the Hierarchy Menu that you can display at the data cells.

Analysis Menu: Predefined and Automatic Navigation

In the Analysis Menu (below the report), click on or tap the Navigate button to display the Plus, Auto, and Minus buttons.

• Plus will run a(nother) Navigation step starting with the selected cell. This expands the table with an additional dimension as shown below – provided that a cell containing a value is selected as the starting point. If a suitable cell is not selected, DeltaMaster will call this to your attention.
• Minus undoes the last Navigation step. This removes an attribute from the table, thereby making it smaller – provided that the selected cell was entered in the table by navigating.

The fastest and easiest way to navigate a table is to double click on a cell. This runs a step with the predefined Navigation, even if you have not activated the Navigate function in the Analy­sis Menu.

Which dimensions DeltaMaster enters in which order and sort order depends on the settings predefined in the Edit mode (typically by the report editor). This simplifies the handling for all report recipients. It also helps promote standardization by creating uniform guidelines for discussing the numbers and establishing a standard agenda. If no steps have been predefined or you have already run all predefined steps, DeltaMaster will point this out.

If you want to run the Automatic Navigation you activate the function with the respective button. Before each step, DeltaMaster will automatically calculate which attribute will be inserted next. A data mining algorithm examines which criteria are still available and automatically selects the most interesting one. This Navigation also works when no steps have been predefined. Also after the Predefined or User-defined Navigation and even in alternation with them, you can let the Automatic Navigation guide the way.

The fastest and easiest way to navigate automatically is to double click while holding the Ctrl key. This runs a step with the Automatic Navigation regardless if a Predefined Navigation is available or not.

After each navigation step, DeltaMaster will automatically move the selection marker to select the next logical cell: the first of the newly displayed cells. This is the most important value for the next step because it is the largest or smallest depending on the defined sort order. Pay attention to the dotted border – it shows which cell is selected and, therefore, what is the starting point for the next step. This allows you to analyze deeper with just a single control: clicking Plus again and again.

Hierarchy Menu at the data cells: User-defined Navigation

The User-defined Navigation helps answer specific questions that go beyond a predefined or automatically calculated path. If you point your mouse on the bottom part of a cell, a small black arrow will appear. If you mouse over this arrow, DeltaMaster will open a menu that offers all attributes (dimensions, hierarchies and levels) where you can drill-down for further details.

Click on the desired attribute to run a navigation step in this direction, i.e. to drill down on it.

The possible selections in the menu are dynamic and change with each step. They depend, among other things, on which attribute are already contained in the table or fixed in the Filter Bar (above the report): Each attribute (or better said, each level) can only be used once. Accordingly, the menu shrinks with each navigation step until no further dimensions are available. The menu primarily depends on the so-called Navigation Context, in other words, the set of dimensions, hierarchies, and levels that you can possibly select in this report – mostly a small selection from the data model. This context is defined in the Edit mode (so typically by the report editor).

That means: If you feel that the menu is too long and contains too many attributes, you (or a report editor) can shorten it in the Edit mode. Defining a Navigation Context is simple as we will explain in the next edition of clicks!. In the Edit mode, you can also change the names of hierarchies and levels. That means if some of the options in the menu have strange names or look more like technical terms than business names, you can solve this issue quickly in DeltaMaster.

If you need very specific information, you can edit the Navigation for a step that you have already run and, for example, change the sort order, display more/ fewer rows, or set numerical filters. This entry is available in the Hierarchy Menu of the cells with a gray arrow. (These are the cells where the Navigation that you want to change begins). This will open a dialog box that is similar to the Axis Definition, which is very familiar to report editors. Report recipients usually do not modify the Navigation themselves.

Reading the Navigation

We will use the following example to show how the Navigation evolves and how to read it in the report. Although this is actually self-explanatory, we wanted to do this for the sake of completeness. Our example starts with a budget-actual comparison. What interests us is the variance in planned margin with the amount of -2.7 percent or the respective absolute value (1,644.4). We have already selected this cell as you can see based on the dotted border.

Step one breaks down the initial value by sales teams. Since this was set up as a Predefined Navigation, we can run this step and all others by clicking the Plus button in the Analysis Menu.

DeltaMaster enters both sales teams as new rows in the table and displays an additional column with the labels (All teams, Brown, Miller). A small gray arrow at the bottom of the initial cell marks the starting point of the Navigation, in other words, which value was broken down. The selection marker automatically moves to the first new member that was added. This is the single most important cause of the variance. The table sections that were not drilled down further are now gray to emphasize the area being analyzed in more detail.

The next step drills down further into the variance of the most important causer (Brown, ?2,353.0).

DeltaMaster once again adds a few more rows: the products that caused the variance within Brown’s team. Two gray arrows mark the Navigation path in the data cells. DeltaMaster automatically moved the selection marker (dotted border) again, now to the most important product group (Softpad). You can clearly see that the new rows are sorted in ascending order based on the budget variance, from the worst negative variance with Softpad (?1,654.2) to the largest positive variance with Jackson (351.9). Two members have negative variances and several have positive variances. DeltaMaster only displays the three largest variances respectively and combines the rest into “remaining top”. This is typical for the Navigation, which only adds (and sorts) the most important members in each step. You can define exactly how many members it selects and how they are sorted in the Edit mode. Here you can also activate the option to combine the remaining members in a group. For the record, the members were sorted in the previous step as well, but it is not so obvious when there are only two members.

The next step breaks down the variance for the most important product group: Softpad.

This again adds new rows to the table: the customers of the most important product that has primarily caused the variance in Brown’s team. Three gray arrows mark the Navigation path in the data cells. DeltaMaster once again automatically moves the selection marker (dotted border) – now to the value of the most important customer (Eurotunnel Corp, ?1,341.5). This means that you only need to click the Plus button in the Analysis Menu for this next step.

The row headers create a wedge-shaped pattern where you can view the details step by step. DeltaMaster also documents the individual steps in the status bar below the report.

This shows which attribute was added in each step, for example, “Product group”. It also displays how the members are sorted, for example, “Bottom/Top”. This means the largest and smallest members together, which is very interesting in the case of variances (see DeltaMaster clicks! 02/2015 for more information).

Navigating for further members

In the most common cases for using the Navigation, you will probably want to break down one starting value in more detail as in the example above. Nevertheless, you can also repeat the analysis path you have taken for other initial values. That can produce very informative visualizations. For example, you might want to compare the variances for revenues to the variances for gross margin. The Navigation makes it possible.

A further navigation step can be added not just to the last step, but even to any cell – inside the Navigation area or also outside (in gray). When you start in a column where you have already used the Navigation and run the same steps as you did before, DeltaMaster will display the new sequence alongside the existing one(s).

Navigating in the Web browser and App

All three Navigation methods (Predefined, Automatic, User-defined) also work with the WebClient, i.e. in the Web browser and in the DeltaMaster App for iPhone and iPad.

When using the App, please note that when reports that are larger than the available screen space, they will automatically be displayed a bit smaller. Tap once on the report to display them in the original size. Navigation is only supported when reports are in their original size (not the smaller display). To start the User-defined Navigation, tap and hold the desired cell until the Hierarchy Menu opens. The Web browser supports the same double click operation as the Windows client: double click to start a Predefined Navigation, Ctrl + double click to start an Automatic Navigation. There is no equivalent, however, in the App.

The functionality to subsequently edit a User-defined Navigation is not available for the Web browser and the App. The sort order, in other words, is always based on the default setting in the report and cannot be changed for individual steps.

DeltaMaster 5: Same handling, different licensing

The Navigation tips and instructions described above have been written for DeltaMaster 6, but are also valid for DeltaMaster 5. The differences lie in the licensing: The Pivot Navigation in Delta­Master 5 belongs to Miner mode. You can also use it in Viewer, Pivotizer, and Analyzer modes with a separate license (Na­vigation Option, see DeltaMaster clicks! 04/2014). The WebOption of DeltaMaster 5 also supports Pivot Navigation. The App, however, requires DeltaMaster 6.

Greetings, fellow data analysts!

If you want to send information or write a letter to more than one person, you inevitably come in contact with placeholders, fillers, or variables as they are called in the world of data processing. Letter drafts and templates, for example, often start with “Dear Mr. …,”. This ellipsis is also a variable because you want to replace it with the respective last name in the output. The Publisher or the Report­Server of DeltaMaster provide a highly efficient way to automatically send information to or communicate with people. Each job in Publisher, so to speak, is a correspondence draft or template, where you can clearly define how the reports should be updated, modified, and distributed. You can choose from a series of variables to make these individual modifications. In this edition of clicks!, we will explain what they are and what you need to know about them – in full and without fillers.

Best regards,
Your Bissantz & Company team

With the DeltaMaster Publisher or ReportServer, you can customize reports for different recipients. This individualization occurs in at least two different ways: On the one hand, the reports should be indivi­dualized regarding their content. They should show the exact data that is intended for the user and for which he is responsible. On the other hand, the output should be technically individualized so that a separate email is sent or a file is saved for each recipient. Each recipient, of course, has a different email address, and each file needs its own unique file name or storage location so that it is not written over when the Publisher processes the next member.

In the last edition of clicks!, we discussed the first aspect, which is how to customize the content of reports through the View (i.e. the Filter settings of the reports). The Publisher can automatically modify reports, even repeatedly in a loop for several members (i.e. iteration) with the Report Generator. In this edition, we will show how you can individualize the output. The answer is simple: with variables!

Individualization through variables

You can define the automatic modifications of the Publisher in the job definition – specifically, in the Report Update, Report Folders, Report Generator, and Report Recipients windows.

Everything you can define in these four sections can be accessed through variables and thus be used to customize the output:

• Names and properties of members from the ReportUpdate
• Names of the Report Folders
• Names and properties of members that the Report Generator processes successively (iteration)
• Addresses of Report Recipients that can be assigned to the Report Generator members. We will talk about the address in more detail below.

These four sections serve as the “sources” for the variables: Here is specified where they obtain their values. You can use variables in the fields of the job definition, in particular: the Address field (to describe variable file names, directories in the file system, and email addresses). Export Template, E-Mail Subject, E-Mail Body, E-Mail Attachment, and Notification (further right in the job definition, not pictured here) are other areas where you frequently use variables.

When running a job, the Publisher replaces the variables with actual attributes, such as the month selected in the Report Update, the name of the branch or cost center manager from the Report Generator, or the email addresses which are assigned to them. For example, this would transform the address “C:Chair AG@IMN.doc” to “C:Chair AGSouth.doc”.

Publisher variables, part 1

You can use the following variables to reference changing components from the four windows in the fields of the job definition.

@Dxx	Name of the Report Update member in the dimension with the ID xx. The variable name stands for “dimension”.
@Pxxyy	Member property of the Report Update or Report Generator member in the dimension with the ID xx and the member property with the ID yy. The variable name stands for “property”.
@Fxx	Name of the Report Folder with the ID xx. The variable name stands for “folder”.
@IMN	Name of the current Report Generator member. The variable name stands for “iterator member name”.
@IDA	Address assigned to the current Report Generator member, either in accordance with the address of the selected member property in the Report Generator window or the address list in the Report Recipients window. The variable name stands for “iterator distribution address”.

The IDs are numeric values, which are easy to identify in a DeltaMaster session. In Modification Mode of DeltaMaster 6, hold the Alt key and mouse over the respective object: a dimension in the Filter Bar or a folder in the Report List.

When Modelling, you can determine the ID of a member property (i.e. “yy” in “@Pxxyy”) with the help of the Dimen­sion Browser. Open on the Levels tab the menu in the Alias column and count the position of the desired property. “(none)” has the ID ?1 and cannot be used in the Publisher. The next properties are 0, 1, 2, etc. This is also the case in DeltaMaster 5, where the View window is equivalent to the Filter bar. You can find the ID of a member property in the Model Browser (Model menu in Miner mode) on the Model tab.

All IDs must be entered as a two-digit number, if necessary, starting with a zero. For example: “@D01” for the dimension with the ID 1.

If the Publisher cannot resolve a variable, it will state this in the Monitor (DeltaMaster 5: Event Log) and reference the variable name instead. This makes it easy to recognize errors. A generated file named “@IMN.doc”, for example, is a sign that no Report Generator was defined.

The export type “file” displays the Address of the file name and the path of the file. You can enter both, including the path, using variables (e.g. “C:Chair AG@IMNSales.doc”). If the directory does not yet exist, the Publisher will generate it automatically.

Further variables are listed at the end of this clicks!.

Addresses of report recipients: the variable “@IDA“

Just to recap: You can use variables to reference and reuse whatever the Publisher is respectively processing. When sending a report as an email (distribution type “mail”), you will even need two variables instead of one: the name of the current sales region and the email address of the person who should receive the generated document. This is what the variable “@IDA” does. It returns the address that is assigned to the currently processed Report Generator member “@IMN”.

You can assign members and addresses in two different ways:

• Including them in the analysis model as member properties
• Maintaining them directly in the Publisher in the Report Recipients window. Through this mechanism, you can automatically generate and distribute reports with each application, regardless of the data model and without having to modify it.

On a side note: Even though we have been and will continue to talk about email addresses, you can apply this technique for other types of text components (e.g. E-Mail Subjects) that you want to insert into file or directory names for specific users.

Using addresses from the analysis model

Member properties provide additional information about dimension members. You can use them in many different ways, for example, to provide master data characteristics from an ERP system, aliases, or terms in several languages in the analysis model. You can also save email addresses of field sales repre­sentatives, branch directors, cost center managers, product managers, department heads, etc. as mem­ber properties, provided that they are available in the model as a separate dimension member.

In the Report Generator, you can select which member property should be used as an address for each entry (for each dimension).

Maintaining addresses in the Publisher

Alternatively, you can assign the desired addresses to the members of the selected Report Generator in the Report Recipient window. The name of a member is displayed in the left side of the list. On the right side, enter the Address that you want to use for this member.

You can modify the list using the commands in the upper-right part of the window

New Recipient	Creates a new, empty entry in the list. Enter the name of the member and the address. This command is very useful if you want to save an address for a member that isn’t currently or is not always contained in the Report Generator because it depends on a condition defined as an MDX query. To set up an empty list for the first time or extend an existing one, however, it is simpler to create the list.
Delete	Deletes the selected row (no confirmation prompt).
Generate List	Generates or updates the recipient list based on the current specification in the Report Generator. The Publisher will add a new entry for each member that is not already referenced in the Report Recipient window. If a member is already referenced, the entry (Address, User ID, and Pass­word) will remain as is. Members that are in the current recipient list but no longer appear in the member selection will be removed.

Setting up the list for the first time is fast and easy. With just one click the Publisher will generate thelist and you can enter the respective Addresses for the automatically generated Names.

Optionally, you can store specific User IDs and Passwords for the individual members. Using the permissions of the respective user, the Publisher can then log in to the database to gain the same database view as the user. User IDs and passwords are stored without encryption in the Publisher database.

The Report Recipients window is only activated when a Report Generator is selected. As soon as at least one Name is entered, the Publisher works according to the list and expects that an entry is available for each Report Generator member. If an entry is missing, Publisher will skip over this member, generate no output, and note this in the Monitor or Event Log. If an entry is available but the Address is empty, it will take the value from the member property assigned to it in the Report Generator. If this is missing as well, the Publisher will enter the variable name “@IDA”.

Examples

The following example illustrates the functionality of the variables. Here we have created two jobs that generate and distribute reports from the same analysis session (report source). The first job should export Word documents and save them locally while the second should send the reports as an HTML mail.

The following processing rules are valid for both jobs:

• Select “Aug 2015” in the time dimension (with the ID 2) under the Report Update
• Include only the Report Folder “Sales” (with the ID 10)
• Iterate over the members “North”, “South”, “East”, and “West” in the Report Generator.

For the first job, the Address is: “C:Chair AG@F10 @IMN, @D02.doc”. The Report Recipients window stays empty.

When you run the job, it will generate four separate documents with meaningful names. It will also set the filters which are included in the reports.

For the second job, the Address is simply “@IDA”. In the Report Recipients windows, a list is created similar to the one on page 5. The address “sales-south@example.com” is assigned to the member “South”, “sales-north@example.com” is assigned to the member “North”, etc. The designation that shows which analyses, regions, and periods are covered is included in the E-Mail Subject: “@F10 @IMN, @D02”.

If you run the job, it will send four emails with customized subject lines and report content to four different email addresses.

These types of mails are especially valuable when the subject line doesn’t just announce the information but proclaims it by quoting the most important KPI(s) from the report. You can do this using the variable “@Report” as mentioned in the following section and listed in the source named there.

Further examples, including how to modify the output with member properties (“@Pxxyy”) and personally address your mails, are described in DeltaMaster clicks! 11/2011 (“Sending personalized emails with Report Server”).

Publisher variables, part 2

The variables described above refer to components of the job definition. There are others, however, that you can also use to modify the output of the Publisher. For the sake of completeness, we will list them here as well.

Variables for report content (cell values, changes over the previous calculation)

The following four variables refer to the report content. They are primarily used to modify the subject line and the text of automatically generated emails (E-Mail Subject and E-Mail Body fields in the job definition).

@Reportx!RyCz	Value of the cell in row y, column z in report x.
@Reportx#R	Number of rows in report x.
@Reportx#+R	Number of rows added to report x since the last completed job.
@Reportx#-R	Number of rows removed from the report x since the last completed job.

You can find the report ID x the same way that you would find the ID for dimensions and report folders: In Modification Mode, hold the Alt key and mouse over the report in the report list.

For more detailed information, please read DeltaMaster clicks! 06/2013 (“Enhancing e-mail subject lines with values from reports”) and 06/2015 (“Highlighting changes in automatically updated reports”).

Variables for export templates

The Publisher can also replace variables when running an export based on custom templates and standard ones for Microsoft Office and PDF formats (i.e. “DeltaMaster.dot/.pdf/.pot/.potx/.xlt” files). When the following variables are added to export templates, they will be replaced in the output files.

@shortdate	Date of the export in short format based on the Windows settings (e.g. “7/14/2016”)
@shorttime	Time of the export in short format based on the Windows settings (e.g. “01:59 PM”).
@longdate	Date of the export in long format based on the Windows settings (e.g. “Thursday, July 14, 2016”)
@longtime	Time of the export in long format based on the Windows settings (e.g. “01:59:12 PM”).
@database	Name of the database upon which the report source (application/analysis session) is based
@cube	Name of the OLAP cube upon which the report source is based

This allows you, for example, to embed the creation date on the title page of a Word file. The Publisher also recognizes and replaces these variables in the Address, E-Mail Subject, etc. Please note, however, that time and short dates are not suitable Addresses for the Distribution Type “file”. The reason is that the typical output for time uses a colon (e.g. “01:59”) and the output for short dates uses slashes (e.g. “7/14/2016”), which are not allowed in file names.

Variables for language

In the job definition, you can select one or more (interface) languages and alias sets in which the report source should be implemented (DeltaMaster deltas! 5.5.4, feature #9). This, too, is an iteration that you can reference with variables.

@aliasset	Current alias set from the iteration over the members in the Alias Set of the job definition.
@language	Current language from the iteration over the members in the Language field of the job definition.

The variables are important for the Address (e.g. when a job should output documents in several languages) as well as the Export Template (e.g. if the export should be based on language-independent templates).

Variables for the CSV export

When exporting to CSV format (comma-separated values), each exported table will be written to a separate file. This makes it easier to transfer the data to other systems. CSV is different from the other export formats where all reports are only written to a single file. This means the Address for a CSV export must be able to differentiate individual reports. The following variables do just that:

@rid	ID of the currently processed report. The variable name stands for “report ID”.
@rn	Name of the currently processed report. The variable name stands for “report name”.

The CSV export is explained in detail under DeltaMaster deltas! 5.4.9, feature #9.

Greetings, fellow data analysts!

In a three-part series from April through June, we presented the output formats and channels that you can use to automatically generate, update, assemble, and distribute reports. Many of you responded with inquiries about Publisher or ReportServer, as the module is called in DeltaMaster 5. Some of the questions are easy to answer. For example: Isn’t this type of automation, a “server”, something for large applications with many consumers and frequent updates? No! Automation has its benefits even when you only have to export or send a single briefing book, but more than one time and to more than one person. Here you can already find ways to boost efficiency, and DeltaMaster can help. Another question was: “Does it work with every report or do you have to take certain things into consideration when you initially build it?” The short answer, generally speaking, is yes. The detailed response on how to design easy-to-publish reports is the subject of this edition of clicks!. If you embrace these ideas, you will reap the benefits – both in interactive, dynamic reporting and automated, static reporting – and achieve further gains in efficiency.

Best regards,
Your Bissantz & Company team

How well reports can be automatically updated and adapted to different consumers and content all depends on the way they are designed. Yet there are different ways to achieve an objective and to build a report. It also makes a difference if you only need the report once (and never again) or intend to reuse it, vary it, or build a template from it. In these cases, the report logic plays an important role.

Input, process, output

Before we make recommendations for designing easy-to-publish reports, let’s first take a look at the way Publisher (DeltaMaster 6) and ReportServer (DeltaMaster 5) work. Roughly speaking, we can break this down into the IPO model:

1. Input: DeltaMaster opens the report source (an application in the repository or an analysis session in a DAS or DM2GO file) and connects to the analytic database (the data warehouse).
2. Process: The reports are recalculated from the report source. Delta­Master runs all necessary database queries so that the reports reflect the current status of the data. Before it runs these queries, you can modify the reports with three tools:

• Using the Report Update, DeltaMaster changes the View (i.e. filters) in all reports and sets the selected members in each listed dimension. Let’s say, for example, that you need to change the period to July 2016. A typical use case: The monthly balance is completed and the cube data has been recently processed. Now all you need to do is to switch all reports to the new month. The report update has no direct effect on the output as in step 3.

• In the Report Generator, you can define several members that are processed in a loop (iteration) to change the View (filters) repeatedly. The report generator has a direct effect on the output in step 3. It gene­rates a separate output for each member of the loop, for exam­ple, an individual briefing book sent by email to each sales representative, cost center manager, and so on. Let’s say, for example, that the members “North”, “South”, “East”, and “West” are selected in the customer dimension. DeltaMaster will then switch all reports from the report source to the member “North”, calculate them with this filter, generate the output (e.g. as an HTML file), and distribute them (e.g. as an email). The next member in line (e.g. “South”) will follow. DeltaMaster again switches the filter in all reports, recalculates them, generates the output file(s), and sends the result(s). This process continues with “East” followed by “West”.

• The Briefing Book Generator also processes several members in a loop. Instead of more output files, however, it generates new folders and reports within the briefing book from the defined application or analysis file. You can access this tool, which we introduced in DeltaMaster clicks! 06/2006, in the Settings of a job. It has no direct effect on the output in step 3. The briefing book generator is rarely used and reserved for special tasks – after all, the goal is usually to create just a few, interactive reports instead of many static reports in the briefing book. As a result, we will not cover this tool in more detail on the following pages.

In the Report Folders window, you can define on which reports you want to use the tools – only the reports in the selected Report Folders, possibly including sub-folders or, if nothing is selected, all reports in the report source. For all three tools, the respective modifica­tions only affect the further processing through the Publisher (i.e. not the saved report source).

3. Output: DeltaMaster outputs the reports in the set report format through the defined distribution type (e.g. PDF file saved to the file server, HTML file sent as an email, or a new DeltaMaster application in the repository). For more information on report formats and distribution types, please read Delta­Master clicks! 04/2016, 05/2016, and 06/2016. In the case of exception reporting, not all processed reports are included in the output: You can define the conditions when a report should be included in the output or not. For more information on exception reporting, please read DeltaMaster clicks! 11/2008.

In short, the Report Update changes the View within the briefing book without generating additional output. The Report Generator changes the View in the briefing book in a loop and generates a new output for each new View. Like most users, you will probably use a combination of the two. For example, you set the new month in all reports (with the Report Update) before you generate and save or send the briefing book for each individual cost center or office (with the Report Generator).

The View is key

What this all tells us is: The Publisher primarily changes the View. That is its own major service. For most other tasks, the Publisher relies on the functionality of the interactive DeltaMaster program, for example, querying the database, calculating reports, or generating export files. DeltaMaster does those things anyway at the user’s request. The Publisher can call up these functions and use them as well. As value added, it automatically changes the View.

And that is the secret behind reports that are easy to customize and automatically update: You have to do it through the View – and only the View.

Reports should be dynamic

If you want an easy-to-publish report, you need to design it so that the automatic variations only depend on the View (in other words, from filters as you would set them in the Filter Bar or the View window) and require no structural changes. To put it in hands-on terms, if a report recipient in Presentation Mode of DeltaMaster 6 or in Viewer mode of DeltaMaster 5 can switch the briefing book to create a custom View, Publisher can do it as well. You could also say whatever is good for dynamic, interactive reporting is also good for static, automated reporting. It’s that simple: If you want to mass produce a series of good, static reports, all you need is a good dynamic report.

We will use two examples to show how you can generate dynamic reports. Both involve Graphical Tables (Pivot Tables). The relation of the Axis Definition and the View plays an important role.

Example 1: Comparison to the previous month

This very simple report compares a given month to the three months prior (e.g. July to April, May, and June). You can do this in different ways.

As a first attempt, you might generate the report by placing the months in the View and refer­encing them as a level selection in the Axis Defi­nition. You can certainly do it that way, but there are better alternatives if you want to publish it. If you would use this report in the following month and place four new months in the report update, you would receive the desired result in this particular report. However, you would have to set this View for all other reports in the report source. In many other reports, this could cause undesirable results, for example, a calculation of the sum of the four months.

The better approach is to describe the reporting task abstractly. After all, the report should actually display the current month and the three months prior regardless what the current month is – and not simply April, May, June, and July. To do this in Delta­Master, you could use time analysis members, which are usually designed as an auxiliary time utility dimension. The mem­bers “Current” and “Previous Period” are frequently already available. That means that you would only need to add members for the two months prior to it: “Previous Period (-2)” and “Previous Period (-3)”.

A report based on this kind of definition is only dependent on a single filter. The “current month” refers to the month set in the View: July 2015. The other three do that as well, with the respective offset. This creates a rolling calculation that depends on a single filter member and works well, both as an interactive chart or when Publishing. That is both elegant and efficient!

For more information on time analysis members, please read DeltaMaster clicks! 08/2007. If no separate utility dimension is available, you can add them directly to the time dimension (see DeltaMaster deltas! 5.6.3, feature #3). Experienced report editors make it even easier with MDX. The expression “<view>. lag (3):<view>” in the time dimension creates the same result with the same column structure as the member selection in the Period View.

Please note that this is just an example. In a live application, we would recommend placing more than just a few months next to each other to enable comparisons. The example above would not suffice to send a signal or trigger actions. A more interesting option would be to calculate the three-month average and show the variance of the current month to this average. That sends a clear message! Please read DeltaMaster clicks! 07/2011 for step-by-step instructions.

Example 2: Reports per sales region

In this example, let’s create a simple budget-actual comparison for different sales regions.

The report editor, in this case, decided to set the region as a member selection in the Axis Definition. You could already see this in the previous screenshot: The region “South” is listed in parentheses, which means that the filter is set in the current report but not used. The selection in the axis definition overrides the filter in the report. The Publisher cannot access a member that is set as a member selection in the Axis Definition. That is the nature of the member selection in the Axis Definition: Whatever is set here is valid – regardless of the View and, therefore, also regardless of Report Update and Report Generator.

The solution is clear: You need to design the report so that the Axis Definition only contains the attributes that cannot be changed, for example, the column structure with time comparisons or variances. When working with dimensions such as customers, products, colors, offices, or cost centers, you should select them in the View and not the axis as a general rule.

With this definition, you can automatically iterate this report over the regions with no further problems.

Minimal requirements, maximum effect

The Publisher is a notably thin component that is included in the installation files and scope of delivery of DeltaMaster 6. You can install the ReportServer of DeltaMaster 5 as an added option or copy a few files (less than 1 MB) into the DeltaMaster program directory from a zip archive. Storage requirements are minimal and getting started is simple since key functions for report recalculations and exports are already contained in DeltaMaster. These functions are simply automated in DeltaMaster when pulishing.

If you want to initially test Publisher/ReportServer, you can give your IT the all-clear that ReportServer is called “server” just because it is a highly servile tool and works automatically (instead of interactively). It doesn’t need a server operating system, separate computer/user, or special system services. You can use it as an additional component for automation on any PC or laptop of someone who uses DeltaMaster interactively. The only thing you have to keep in mind are the user permissions. If you want to publish applications from the repository with DeltaMaster 6, you will need to assign the roles “Define Report Distribution” and/or “Execute Report Distribution” to these applications. Please note that these are two separate roles (one does not include the other). Frequently, both roles are assigned to a given user. No special license for DeltaMaster 6 is necessary – the publishing functionality is already included in the REPORTING licenses. DeltaMaster 5 requires separate licenses for ReportServer.

If you work with very large applications, you can run jobs on a separate computer. We refer to this as “ReportService” in contrast to “ReportServer”. Please read DeltaMaster deltas! 5.4.2, feature #27 for more information.

Greetings, fellow data analysts!

Distributing reports is the one task in Business Intelligence where you can see the efficiency gains so clearly like nowhere else. Once you have created a DeltaMaster application or analysis session, you can use it as a template to automatically generate, update, assemble, and distribute tailored briefing books for individual consumers – all at marginal cost close to zero. In order to do that, you need a report format called Graphical Tables, which ensure efficient, reliable pro­duction processes with no follow-up work. Another advantage of Graphical Tables is that they are easy to read and clear to under­stand – which you surely know because we have frequently dis­cussed that already. This time, we will focus on the output of automated reporting. For publishing reports (using the ReportServer as the module is called in DeltaMaster 5) DeltaMaster offers a wide range of output formats and channels. We will provide an overview of these options as well as tips regarding selection in a three-part series of DeltaMaster clicks!. Part 3 offers a few closing thoughts for selecting the report format and distribution type.

Best regards,
Your Bissantz & Company team

When you publish reports (or work in ReportServer), you can define the Report Format and Distribution Type for each individual job. This opens a world of opportunities for automated reporting. We will explore some clever ways to use them in this edition of clicks!.

For that, we assume a widespread structure: The Data Warehouse and the reports, which build on it, are maintained centrally. The reports are to be distributed to many different recipients, for example, branch managers, outside sales representatives, cost center managers, product managers, executives, or external partners.

A brief list of requirements

When considering how to design your automated reporting, start by reviewing these five aspects – even if they are not 100% complete – as food for thought.

• Currency of the data: Should report data be pulled dynamically from the database or would static reports suffice?
• Analytic capabilities: Should recipients be able to analyze the data on their own? If so, should these capabilities be sophisticated or basic (e.g. filtering)?
• Usage environment: Where do the recipients use their reports? How often do they use them? Which software and hardware do they use?
• Further processing: Are the report recipients “end consumers” or do they need to add comments or incorporate the content into other documents?
• Security: Do you need capabilities to manage access to the reports and the data they contain at any time?

Currency of the data

The DeltaMaster-specific report formats “das” and “repository” are dynamic. When you publish, these reports will be recalculated when they are displayed – either automatically (e.g. upon changing a view) or when the user updates them (with the F9 key or the respective menu commands). In other words, the data in the report is as current as it can be (i.e. in the database). In the Repository, this applies to both the report data and DeltaMaster application. When an application has been updated, it is immediately available in the updated version – and only in this version. The previous version no longer exists, unlike files where copies may still exist. Another advantage of storing applications in the Repository is that you can use them with DeltaMaster in so many ways, including the Windows client, WebClient, the App for iPhone/iPad, and the Office add-in.

All other report formats (e.g. Office formats, PDF, HTML) are static. They display the values that were available when the file was generated. That suffices (and is sometimes explicitly requested) for many use cases, for example, when the data does not change frequently as in the case of monthly or quarterly reports. Other reasons for using a static format may have to do with the usage environment of the recipient (see below), processing load of the database server, or licensing model of the database vendor.

As a side note, you can also use static versions of DeltaMaster-specific output files. Using the “das-offline” and “mobile” formats, you generate reports for DeltaMaster or DeltaMaster ReaderApp that require no connection to the database. In addition, you can also open a normal DAS file (i.e. a saved analysis session) without a database connection because the values displayed in the reports are saved to the file. DeltaMaster only needs to run a new query on the database when you recalculate the reports (e.g. by hitting the “F9” key or changing the view).

Analytic capabilities

When the recipients should or want to analyze their data own their own, the DeltaMaster formats are the best choice hands down.

Here it makes no difference, how sophisticated the analytic requirements are. Many users are completely satisfied when they can change a few filters to switch the view to a different country, product color, or from the current to the previous month.

Browse, Zoom, and Navigate, the core analytic functions of DeltaMaster 6, are especially easy to use and very powerful as well. With these three functions, users can answer most of the questions that arise in a report – immediately and without leaving that report. The Zoom and Navigate features are even supported in a Web browser or on iPhone/iPad devices. Links enable a direct jump into the analytic methods of DeltaMaster, up to Data Mining. To offer users such interactive analyses, select “das”, “dm2go”, or “reposi­tory” as the report format. The “repository” format is required to use the report in WebClient and in the app for iPhone/iPad.

DeltaMaster generally needs to query the database to conduct interactive analyses. The “das” and “repository” formats are dynamic as defined in the previous section. They can connect to the database and pull current data from it. The “dm2go” format is an exception because it accesses data from a database extract that was included in the file. Users can conduct interactive analyses based on this static data (see DeltaMaster clicks! 07/2013).

Usage environment

Let’s quickly recap what we have already discussed: There are many good reasons to solely rely on DeltaMaster to automatically distribute reports. Nevertheless, not every recipient can use DeltaMaster anywhere and all the time. Many users probably only have Microsoft Office, Adobe Reader, and similar programs – if at all, in the case of smartphones. DeltaMaster supports these scenarios as well with universal formats: PDF, Word, Excel, PowerPoint, and HTML. PDF files, in particular, are relatively robust and are reasonably supported even on lesser common devices and operational systems used in companies. The results are all static. When there is new data, new files will need to be generated.

The distribution type also depends on the usage scenario and conditions. Employees who primarily work in the office or in the field with a good (VPN) connection to the enterprise network can work well with files on the file server. In these cases, the repository would offer a good alternative as well. Emails are sure to grab your readers’ attention when you make the effort to use strong subject lines (see DeltaMaster clicks! 06/2013). On the other hand, emails have something transient about them. Keeping things in order also requires a certain amount of discipline from the recipients.

Processing

Many times, a report has fulfilled its purpose when the recipient knows what to do, who to contact, where adjustments are necessary, and what is missing – true to our slogan “Look, see, do”. Other times, the report is just a part of something greater and the recipients need to add comments, justify forecasts, give recommendations, substantiate arguments, and possibly add a report from another source. If no further processing or changes to the documents are desired, PDF files are a good option. An HTML mail is more flexible. When forwarding emails, for example, recipients can add comments between the reports, change the headings, or delete reports that are not required. Exports as Word documents or PowerPoint presentations are especially suited for further processing. These formats are also robust enough for creating extensive documents.

In the case of complex word processing requirements, for example, that require pages of argumentation aside from the DeltaMaster reports, you should consider using DeltaMaster Add-in for Microsoft Office. Instead of exporting the DeltaMaster reports (i.e. “push” as by publishing), this add-in embeds them as dynamic objects in Word documents or PowerPoint presentations so that they can be immediately updated in Word or PowerPoint (i.e. “pull”). You can also use this option to present various data sources in one document. For more information about the Add-in, please read DeltaMaster clicks! 12/2013.

The DeltaMaster-specific formats are also well suited as the starting point for further processing. You can add comments, even on individual values (cell comments, see Delta­Master clicks! 10/2015) and copy reports to the clipboard to transfer them to other documents.

Security

The DeltaMaster formats “das” and “repository” are very strong when it comes to security. You can change the access rights for applications, folders, and reports at any time in the Repository, for example, to recall or deny access to reports. DeltaMaster offers its own, comfortable interface for administrating permissions, oftentimes, within the departments themselves. Beyond these permissions, those in the database are and remain valid even when the users access the database through analysis sessions (DAS files) or other programs and not through the Repository. Changes to the database permissions, or course, are possible as well. These, however, require the tools and methods of the respective database products, which is usually a task reserved for IT professionals.

Office and PDF files are difficult to withdraw or eliminate by nature. Nevertheless, you can at least protect the document from unsolicited access by encrypting them and adding a password (see DeltaMaster deltas! 5.5.5, feature #3.) The once common complaint that hacking encrypted Office documents is child’s play no longer holds true provided that you use the file formats introduced in the 2007 version (i.e. DOCX, XLSX, PPTX) as described in the aforementioned deltas!.

Summary

Let’s just review what we have already covered. Generally speaking, there are many strong arguments – for editors, administrators, and recipients to exclusively use DeltaMaster! The Repository, in particular, paves the way for intelligent solutions, both big and small. There are also logical reasons to offer other formats, such as PDF files, or send reports directly as an HTML mail. The good news is that DeltaMaster supports all of these options so you can use them alone or combine them as you need for your tasks. This also holds true for scenarios that we could not describe here – for example, applications that are only accessible through Repo­sitory (without Publish/ReportServer) or are integrated in portals such as SharePoint. If you have any questions or need support in planning your automated reporting processes, please contact your Bissantz & Company team.

Greetings, fellow data analysts!

Distributing reports is the one task in Business Intelligence where you can see the efficiency gains so clearly like nowhere else. Once you have created a DeltaMaster application or analysis session, you can use it as a template to automatically generate, update, assemble, and distribute tailored briefing books for individual consumers – all at marginal cost close to zero. In order to do that, you need a report format called Graphical Tables, which ensure efficient, reliable pro­duction processes with no follow-up work. Another advantage of Graphical Tables is that they are easy to read and clear to under­stand – which you surely know because we have frequently dis­cussed that already. This time, we will focus on the output of automated reporting. For publishing reports (using the ReportServer as the module is called in DeltaMaster 5) DeltaMaster offers a wide range of output formats and channels. We will provide an overview of these options as well as tips regarding selection in a three-part series of DeltaMaster clicks!. Part 2 will review the various distri­bution types.

Best regards,
Your Bissantz & Company team

When you publish reports (or work in ReportServer), you can define the Report Format and Distribution Type for each individual job.

We already introduced the Report Formats in the last edition of DeltaMaster clicks!. This time, we will focus on output channels. You can choose from five different Distribution Types:

• file
• ftp
• mail
• print
• repository

In the job definition, you will need to enter additional parameters, which vary by the distribution type. The Address field, in particular, is used in many different ways depending on which distribution type you have selected.

file: Save output to a file

Frequently, you will want to save the output to the file system on the local computer or network – for example, in a shared directory or a personal folder for the recipient. To do this, enter the desired path – with or without a file name – as the Address in the job definition. If you only enter a directory (i.e. without a file name), DeltaMaster will automatically assign a file name based on the Report Source.

The path and file names may contain variables that will be replaced when the job is executed. “@IMN”, for example, stands for the member that Report Generator is currently processing. This could be the name of the sales region or cost center for which the report source is calculated. The address “C:Chair AGSales Region @IMN.doc”, for example, will produce the file names “Sales Region North.doc” and “Sales Region South.doc” when the report generator iterates over the members “North” and “South”. If you enter a variable instead of the file name for the directory, DeltaMaster will distribute the documents to individual directories (provided that the user running the job has the necessary write permissions).

The most important variables are listed as a tooltip in the Address field. For more information, please read DeltaMaster clicks! 11/2011.

ftp: Upload output using a File Transfer Protocol

You would typically transfer files using FTP to distribute them through a Web server. In other words, this distribution type also saves files, but to a remote system and not locally. One typical use case is to publish to a portal where users can access their documents. Combined with the export format “HTML”, you can create “pseudo-dynamic” solutions: Although the exported files are static by nature, the information is automatically updated on a regular basis and appears as if it has been pulled straight from the database. However, a solution is only truly dynamic when it uses the Repository.

When you enter the login information to the FTP server in the Address field of the job definition, please use the format “ftp://user:password@server/path”. ReportServer variables are supported as well.

mail: Send output as an email

Sending an email is probably the most attractive way to distribute reports. These emails are sent from the SMTP server defined in the Settings (E-Mail Server tab). This setting is valid for all jobs.

In the Address field, you enter the email addresses of the report recipients. This field supports Report­Server variables in order to transfer the addresses from the application or analysis session. If they are not available there, you can enter them directly in the job definition (Window Report Recipients, variable “@IDA“). If you are sending the report to several people, please separate the addresses – either with or without variables – with a comma. To send someone a report in copy, please enter “CC:” or “BCC:” directly before the respective address.

The fields E-Mail Subject, E-Mail Body, E-Mail Attachment are relevant for the “mail” distri­bution type. ReportServer variables are also supported in these fields. You can use a ReportServer variable to query a specific cell in an individual report and enter its value in the subject line. This provides a most attractive opportunity to automatically generate personalized messages and even report KPIs directly in the subject line. In other words, you can use the subject line to not just announce your information but to broadcast it loud and clear! To review how this works, please read DeltaMaster clicks! 06/2013 (in short: with the variables “@Report<Report ID>!R<Row>C<Column>“). The suggestions of DeltaMaster clicks! 11/2011 are also recommendable: This issue explains how to send personalized emails, e. g. with a personal salutation etc.

The E-Mail Attachment gets more technical. What this field means depends on which export format you have selected. “HTML” generates emails in HTML format and embeds the reports as images. Since the images are typically sent together with the email, the E-Mail Attachment field will remain empty. If you want to save them to a Web server and load the images when the e-mail is displayed, enter the login information to this server as an E-Mail Attachment (DeltaMaster deltas! 5.3.6, feature #12). All other export formats will send the reports as an attachment. In this case, enter the file name for the E-Mail Attachment, as the equivalent to the Address in the distribution type “file”. The generated file attach­ments will be automatically deleted once the email has been sent – unless the file name includes a path and ends with an asterisk (e.g. “C:StorageMonthly Reports @IMN.doc*”). This stores the files, for archiving, documentation, or revision. If you place two asterisks (“**”) behind the file name, the job will generate and save the file but not send it. This option is designed to announce that new reports are available without sending them along with the email.

For more information on sending emails automatically, please read DeltaMaster clicks! 03/2009. DeltaMaster deltas! 5.3.6, feature #12 also provides a detailed explanation of HTML mails.

print: Print output

Paper-based reporting may not have the same importance that it once had. There are, however, still scenarios that rely on paper and benefit from an automatically triggered printing process. Printing also doesn’t always involve paper, ink, and toner. Sometimes a printer driver serves as an interface, for example, to a fax transmission or document management system. A special printer driver receives the documents that are ready for printing and forwards them in its own way. PDF files, for example, are often generated through a print driver. DeltaMaster, however, has its own specialized export formats for PDF. When you publish, you can set the printer (driver) in the Settings. This printer is then valid for all jobs in the current job definition database. The Address field in the job definition is irrelevant for print output.

repository: Creating or updating an application in DeltaMaster Repository

Finally, you can publish applications to DeltaMaster Repository as a database-driven alternative to sharing applications with DAS files. This distribution type is frequently used to “slice” smaller, specialized applications from an extensive report source or set a report view for individual recipients.

In this case, the Address will direct you to a job-specific setting. You will need to select the repository where you want to generate the applications and the range where DeltaMaster can assign IDs (i.e. Start FileID, End FileID). These entries should correspond with the applications that have already been created in the repository and the ranges in other jobs. For more information on working with the repository, please read DeltaMaster clicks! 01/2015.

Combinatorics

You can flexibly combine these five distribution types with the report formats in each job. Only two restrictions apply:

• The distribution type “repository” must be used in combination with the report format “repository” and vice versa: The report format “repository” must be used in combination with the distribution type “repository”.
• The distribution type “print” is designed for Office and PDF formats. You cannot combine “print” with DeltaMaster-specific formats such as “das” or “dm2go” or “csv”, “mobile”, or “ticker”. You could theoretically combine “print” and “html” but it is usually not the best choice.

Greetings, fellow data analysts!

Distributing reports is the one task in Business Intelligence where you can see the efficiency gains so clearly like nowhere else. Once you have created a DeltaMaster application or analysis session, you can use it as a template to automatically generate, update, assemble, and distribute tailored briefing books for individual consumers – all at marginal cost close to zero. In order to do that, you need a report format called Graphical Tables, which ensure efficient, reliable pro­duction processes with no follow-up work. Another advantage of Graphical Tables is that they are easy to read and clear to under­stand – which you surely know because we have frequently dis­cussed that already. This time, we will focus on the output of automated reporting. For publishing reports (using the ReportServer as the module is called in DeltaMaster 5) DeltaMaster offers a wide range of output formats and channels. We will provide an overview of these options as well as tips regarding selection in a three-part series of DeltaMaster clicks!. Part 1 will start with an overview of the various report formats.

Best regards,
Your Bissantz & Company team

When you publish reports (or work in ReportServer), you can define the Report Format and Distribution Type for each individual job.

This allows you to tailor the output to very specific reporting tasks. For example, you could send an email announcing the day’s revenues each evening, archive monthly reports as a PDF in a specific directory on the file server, and send personal sales figures including the respective database extract to each field sales representative for analysis. The automation for publishing is also useful in planning applications: A single job can automatically generate multiple customized applications in the repository which are pre-configured for individual recipients (i.e. planners) so that their respective planning objects are already set as the default values.

You can choose from the following 21 export formats:

csv	doc	pdf	pdf-ppt	ppt	xls
das	excel	pdf-doc	pdf-word	repository
das-offline	html	pdf-excel	pdf-xls	ticker
dm2go	mobile	pdf-powerpoint	powerpoint	word

You will find this same list along with a short description at the end of this clicks!. We have combined the formats into groups as we look at them below in more detail.

Greetings, fellow data analysts!

If you are frequently on the road, you have probably noticed that the latest smartphone or sleekest tablet is only half as fun when you have no network service – and that seems to happen more often than your provider advertises. A network by its very nature, how­ever, primarily consists of holes. That’s why the offline capabilities of mobile solutions are so important: If you want to be mobile, you need a solution with strong offline capabilities! With DeltaMaster App for iPhone and iPad, you are well equipped. You can download reports or entire applications to these devices, take them with you, and access them on the go – even if you aren’t online. Once you have your reports in DeltaMaster App, you can enjoy your beloved Apple devices with or without a network connection.

Best regards,
Your Bissantz & Company team

In the previous edition of DeltaMaster clicks!, we introduced our app for iPhone or iPad devices. This app, which was launched with DeltaMaster 6, primarily works online. In other words, it pulls data fresh from the database. This gives reports and applications on an iPhone/iPad the same dynamic, interactive capabilities that you know from DeltaMaster. Users, for example, can update reports (i.e. refresh from the database), add filters, display details in Graphical Tables (pivot tables) using drill-down functions, open links and even zoom (Semantic Zoom) and navigate (Pivot Navigation). The app, however, is also designed to support offline usage.

Online and offline mode

The app has both an online and offline mode. You can see which mode is active by glancing at the antenna tower icon in the upper-right corner of the app. The app is online when waves appear at the top and offline when they do not. Tap the symbol to switch from one mode to the other. In online mode, you can access all reports and applications based on your permissions in the repository. In offline mode, you can use any applications and reports that you previously downloaded to your iPhone or iPad. In order to use the app in offline mode, therefore, you must first save the desired reports or applications to the device while you are still online.

 

Downloading reports

As a general rule, you can download an application or report right where you would normally select them. In other words, you can download reports from the report list and applications in the dashboard. To open the report list, tap the  icon at the top of a report.

In the list, tap and hold the report until a context menu appears at the bottom of the screen. Here you can download the report for offline use.

This works the same way for folders such as “Sales”. If you tap and hold a folder, the menu will appear and you can download the folder, including all reports that are contained in the folder and its subfolders. This way you can quickly save several reports to your device at once.

The app designates downloaded reports with a small save icon to the right of the report list. In the case of folders, it will only mark the folders themselves and not the contained reports.

Download applications

You can save applications to your iPhone or iPad in the same manner: Tap and hold the tile in the dashboard until the menu appears to download the application.

Update and remove

Once you have downloaded a report or application, the context menu will contain two additional commands. Update reloads the report or application from the server and saves it to the device. This includes the definitions, structures, and values. Remove deletes the local copies while making no changes on the server side. In order to update, the app must be online. The remove function also works offline.

Going offline

As long as you are online, the app will not use the reports and applications you have downloaded: In online mode, the app ignores the downloaded versions and always pulls the data directly from the server.

In offline mode, the app will access the downloaded versions. To switch to offline mode, tap the antenna tower symbol in the upper-right corner. The app displays a menu which you can use to go offline. You must activate offline mode explicitly. Turning of the Internet connection or doing something similar will not automatically switch the app’s mode.

Now you can only access the downloaded reports (numbers 1 through 5) from the report list. All other reports are grayed out.

In the dashboard, you can now only access applications that have been completely downloaded as well as applications with down­loaded reports. In the example on your right, you can open the “HR” (Human Resources) application and several reports from “Gross Margin”; all others are disabled. The icon next to “HR” designates that the entire application has been downloaded. This icon does not appear for “Gross Margin”. The application is displayed as active and can be opened, however, because you have saved individual reports from this application to your device.

Switching back to online mode will not change the downloaded reports and applications. When displaying reports, the app will pull current data from the server and ignore the downloaded versions, as it always does in online mode. If you want to update downloaded reports and applications, select the respective function in the menu.

Additional functions in online mode

Five functions of the app are not available in offline mode: Changing a filter, opening links, drill downs (expanding/collapsing) as well as the Zoom and Navigate functions in Graphical Tables all require an online database connection.

Online mode is also required to export reports as PDF files or to the formats of Microsoft Office (i.e. Word, Excel, PowerPoint) because the respective files are generated on the server before the app saves them to the device.

Installing the app

If you have not yet installed DeltaMaster App, you can download it free of charge at the app store of Apple. The easiest way is to simply scan the QR code on your right with your iPhone or iPad. This code or the address appstore.com/deltamaster will direct you to the app’s homepage in the store. Alternatively, you can search the app store using the keyword “DeltaMaster”.

Test it in our demo system

If you aren’t yet able to access your DeltaMaster applications with an app due to the way they are currently deployed, you can use ours in the meantime! We have configured a demo system so that you can get familiar with the app based on sample data.

The QR code on your right will take you directly to https://www.bissantz.de/#demo-registration , where you can request your personal login and password for the demo system. We will then send your login information as an email. Ideally, you should open this email on your iPhone or iPad. Otherwise, please forward it to these devices. If the app is installed on the device, you can immediately open the configuration file (DMSETTINGS), which is sent as an attachment with the email, to enter all settings automatically. Currently, the registration page and the demo application is available in German only.

For more information on the app, system environment, and connection configuration, please read DeltaMaster clicks! 02/2016.

Greetings, fellow data analysts!

Mobile BI is the term for querying or displaying performance management data on the go. “On the go”, thereby, is relative as the usage of mobile devices continues to grow within companies: People take tablets along to meetings or check smartphones throughout the day to see if things are running smoothly. For these and other scenarios, there’s the DeltaMaster App, with which you use applications from DeltaMaster 6 on an iPhone or iPad. The app works both online and offline – and the reports look just as good on the smaller screens as on those of a laptop or desktop computer. The way data is presented often determines if the people who receive it actually read it. That’s why some companies feel that one advantage of the DeltaMaster App is that it improves the acceptance for and the usage of BI applications. Swiping through DeltaMaster reports on an iPhone or iPad, zooming through time, or navigating to the details is truly a pleasure! That means tangible benefits for data-driven management.

Best regards,
Your Bissantz & Company team

The DeltaMaster 6 release includes a brand-new app for iPhone and iPad devices. We will explain what the app can do and how it is integrated in the DeltaMaster environment. And we invite you: Try it yourself! With our demo system, it is easy as you will see below.

Before we start, let’s have a look at some major features of the DeltaMaster App.

Users can…

• update reports (i.e. refresh reports with data from the database),
• add filters (i.e. change the view to show a different month, cost center, or material group),
• zoom and navigate in pivot tables (i.e. semantic zoom, pivot navigation),
• display reports and entire applications in offline mode without network access, and
• share reports (i.e. export to PDF or Word files).

The app applies the same permissions used in the Windows client or web client (i.e. browser). This makes it an ideal addition to existing DeltaMaster solutions. For many clients, this is an important reason to migrate to DeltaMaster 6. Best of all, no separate licenses are required.

Starting with the dashboard

Once you start the app, you will see a dashboard with tiles in the familiar red-blue color scheme of DeltaMaster. If you hold the device upright, which is usually the case with smartphones, you will view the dashboard in a special row presentation. Each entry stands for one DeltaMaster application that is represented with a KPI in the dashboard. Here you can immediately see what is happening in the indivi­dual areas – for example, where positive or negative results are to be observed – and what issues you should address first.

If you rotate your device horizontally, the display will change. The dashboard will now appear as square tiles – just like those in the Windows client (see image on page one).

For some users, a mere glance at such a data-dense dashboard will suffice to know how the company or the area under their management is performing as a whole. Those who have the time or want to know more details, open the respective application by tapping a tile. This procedure is identical in the Windows client, web browser, and app.

Appealing as usual

The app will display the predefined start report in the familiar style of DeltaMaster. In this case, it is a Graphical Table with a variance analysis. A first glance reveals that the reports in the app are a spitting image of those in the Windows client.

The app adapts the font size to the respective screen size so that you can view reports without scrolling. To display the original size, tap the report twice.

To flip ahead to the next report or back to the previous one, swipe the screen as you would in a photo app. The background colors reflect the “report weather” that is displayed in both the app and Windows client. Thanks to this color-coding, you immediately recognize a noteworthy situation even if you are just quickly swiping through the reports.

In the following screenshots, we have omitted the iPhone bezel to emphasize the details.

Selecting reports from the report list

To select and open a report from the report list, tap the  symbol on the upper-left side of the report.

Graphical Tables in the report list are color coded in red and blue as shown on the screenshot on the following page. This is another example of the strong signaling concept in DeltaMaster 6. If a report is gray, no report weather is available for this report. This can have a number of reasons. The two most common are that no report weather (e.g. weather cell) has been defined in this Graphical Table or the report stems from an analytic method (e.g. Portfolio Analysis, Geo Analysis, or SQL Drill Through). As a matter of principle, the report weather can only be defined in a Graphical Table.

To open the report, just tap it. The entries that are not numbered (e.g. “Sales” and “Analyses” in the example on your right) are fol­ders, which you can open or close with a tap. To close the report list without opening another report, tap the “X” in the upper-left corner

If you hold your finger on an entry, a type of context menu will open at the bottom of the screen. Here you can download the report for offline use, export it as an Office or PDF file, or print it.

Setting a filter

As long as you are in online mode, you can interactively filter all reports – in other words, choose other attributes than the ones that have been saved, e.g. another month, country, or material group. To filter, tap on the header where the current filter is listed – for example, “Sep 2015” in our example. The app will then display all available dimensions (i.e. filter criteria, attributes) in a list.

To open the dimension, tap a row, similar to how you would in the Filter Browser of the Windows client (known in DeltaMaster 5 as the Dimension Browser). The app designates parent members with a small gray arrow. Tap the name to open or close this branch.

To select a member, tap the checkbox. Multiple selections are supported. If you drag a member in its row to the left, an additional button will appear on your right. By tapping this button, you will add it to the current selection. To close the filter settings and apply them to the current report, tap “X” in the upper-left corner.

The filter options in the app are identical to those in the Windows and web client because they are the same repository applications. Accordingly, the Filter Context (known in DeltaMaster 5 as the View Context) is valid in the app as well. Report editors can use it to control which dimensions and members are offered to report consumers.

Zoom and navigate

The analytic functions Zoom and Navigate in Graphical Tables are probably the app’s greatest features.

Use the patented Semantic Zoom to take a closer look how a measure has developed over time. You can enlarge sparklines in granular levels to display additional information as more and more space becomes available on your screen. This type of zoom, therefore, increases the information quantity of the table.

Pivot Navigation is another patented feature, which you use to break down a KPI, step by step, and explain how the respective starting value is divided to the values of a further attribute. This helps you pinpoint revenue or cost drivers and the causes behind variances very quickly.

Both functions start from a specific table cell – in other words, you want to zoom on a certain sparkline or break down a particular value. Tap this cell to select it. If the cell is displayed in a smaller size, first switch back to the original size by tapping the report twice. Now select an individual cell and tap “+” or “–“ to zoom and navigate.

These two functions are prime examples for the smooth transition from an overview to the details in DeltaMaster. The fact that these transitions take place in the same report foster understanding because the context remains constant.

Offline mode

You can save reports and entire applications on an iPhone or iPad so you can work with them even if you have no access to the database or even the Internet. This ensures that you always have your DeltaMaster reports on hand even in adverse conditions, for example, in an airplane, train, warehouses, or even during customer appointments where you don’t want to rely on mobile networks or a guest access point.

Changing a filter, opening links as well as the Zoom and Navigate functions in Graphical Tables all require a database connection. Accordingly, these four functions of the app are not available in offline mode.

We will explore the offline mode of the app in more detail in a future edition of DeltaMaster clicks!.

Installing the app

You can download the DeltaMaster App free of charge in the Apple app store. The easiest way is to simply scan the QR code with your iPhone or iPad. The code on your right or the address appstore.com/deltamaster will take you directly to the app’s homepage in the app store. Alternatively, you can also search the store for the keyword ”DeltaMaster”. If you do not have an iPhone, iPad, or iTunes, the page will not be displayed. A preview is available at itunes.apple.com/de/app/deltamaster/id924014410.

When you start the app for the first time, it will inform you that you must configure the connection to the server. This tells the app where it should retrieve the applications and data as well as which user wants to work with it.

Test the app in our demo system

The app is very easy to configure when you test it with our demo system. We have specially designed this system so that you can get to know the app with sample data even if you do not already work with DeltaMaster WebClient.

The QR code on your right will take your directly to www.bissantz.de/testen, where you can request your personal login and password for the demo system. We will then send your login information as an email. Ideally, you should open this email on your iPhone or iPad or forward it to these devices. If the app is installed on the device, you can immediately open the configuration file (DMSETTINGS), which is sent as an attachment with the app, to enter all settings automatically. Currently, the registration page and the demo application is in German only.

If desired, you can configure the connection to the server manually.

Configuring the connection to the server

Click the gear symbol  in the upper-right corner of the dashboard to open the Settings to which the Connection belongs.

• Server: Enter the server and, if necessary, the path to DeltaMaster WebClient (e.g. “192.168.0.101/deltamaster6” or “demo. deltamaster.info”). This is the same server entry you would enter as an address in the browser, simply without “http” or “https”.
• Secure Connection: Activate this option to encrypt the connection to the server (i.e. HTTPS). This option should be activated for productive systems, provided, of course, that the server you are calling supports HTTPS.
• Check the server certificate: This option is only relevant for Secure Connections. If it is active, the app will check if a trusted certificate is used for the encryption. This option should be activated for productive systems. If you are working with development or test systems, you can deactivate it because e.g. the development or test server has a self-signed certificate. The encryption also works without a trusted certificate. In this case, however, it cannot check the identity of the server.

• User Name: Enter your Active Directory or Windows user name to use the app. The authentication is valid for DeltaMaster Repository as well as the data sources (OLAP and/or relational databases) of DeltaMaster applications (single sign-on).
• Password: Enter the password for the User name you have entered. If you change your password, you will need to change it in the app as well. Please keep this in mind if you have to change your password in regular intervals, as required in the security guidelines of many companies.

Use the button at the bottom of the page to test the Connection. If you have no connection, make sure if the connection settings work in a web browser, for example, in Microsoft Internet Explorer, Mozilla Firefox, or Google Chrome. The app uses the same login information that you would use to access DeltaMaster through a web browser. As a result, you can enter the Server (including the path), User Name, and Password in the app just as you would in a web browser. In most development or test systems, the encryption is not activated or only a self-signed certificate is available. If the app cannot make a connection in this case, deactivate the option to check the server certificate and secure connection as a whole.

DeltaMaster WebClient is mandatory

On the server side, DeltaMaster WebOption in version 6.1 or higher is required. WebOption includes the Repository as a central administrative instance for user groups, applications, roles, etc. as well as the WebClient for communicating with the web browser and app. The DeltaMaster App communicates with DeltaMaster WebClient which, in turn, communicates with DeltaMaster Service, which queries the analytic databases.

The app generally works online and obtains its data “fresh” from the database. The app also has an offline mode. The dashboard and all applications that can be accessed through the app are user-specific. The app applies the same permissions and roles as in DeltaMaster Repository and the analytic databases.

This architecture makes application deployments very simple. You can work with one and the same application from the Windows client, WebClient (browser), or app. This also means that as soon as the WebClient goes live, you instantly have a second way to access it – namely, through the app. This is practical when users work with mobile devices other than those from Apple. The login and password are identical to your usual access information and do not need to be administered separately.

WebOption is typically installed and operated on a company’s own servers – in other words, in its own infrastructure and not in a public cloud.

The app for the new DeltaMaster generation

We will close with a note for those readers who have worked with DeltaMaster for many years. The app that we have presented here belongs to DeltaMaster 6. It is completely different from DeltaMaster ReaderApp, which was launched in 2011 as an add-on for DeltaMaster 5 (see DeltaMaster deltas! 5.4.9, feature #3). The most important difference is that DeltaMaster App in version 6 primarily works online (i.e. with database access) and additionally has an offline mode to download applications or reports and save them locally. DeltaMaster ReaderApp in version 5, in contrast, works offline, meaning with no database connection. Reports are calculated in advance and transferred to the device as DMMOBILE files. To update reports in the ReaderApp, you need updated files, for example, as an email or through a web server. The technology is robust and works from a DeltaMaster session as well as with DeltaMaster ReportServer. However, ReaderApp in version 5 does not support dynamic functions such as the filtering of reports or the zooming and navigating in Graphical Tables.

 

To build new and extend existing mobile applications, we recommend using the DeltaMaster App of version 6. It provides considerably more capabilities and seamlessly integrates with the landscape, permissions, and roles of DeltaMaster. Moreover, it belongs to the DeltaMaster generation that will lay the foundation for all future developments.

Greetings, fellow data analysts!

The data required for management information is not always already available in the data warehouse. Sometimes, you have to piece it together from Excel spreadsheets, Access files, or other sources at a spur of the moment. Today, this is called Self-Service BI – and DeltaMaster is the optimal tool for these types of tasks. The software supports you not only in analyzing and visualizing data but modeling new data sets as well. Best of all, you don’t need any help from the IT department or any special technical skills. You can also utilize proven concepts in data modeling, for example, member properties to add information or even images to reports or to publish in several languages. In the first two parts of our series, we already explored various ways to use member properties in DeltaMaster. In this edition, we will show how to make them accessible in a new analysis model.

Best regards,
Your Bissantz & Company team

In the past two issues of DeltaMaster clicks!, we introduced the concept of member properties (also known as attributes) and explained the various ways you can use them in DeltaMaster. Up until now, we assumed that the member properties already existed in the database. Adding them to the database is an aspect of data modeling, which DeltaMaster supports in two different ways:

• DeltaMaster Modeler is designed for building larger applications with Microsoft SQL Server Analysis Services. This module is targeted to professionals who have experience in working with databases. It reduces the work required for routine tasks and provides many advantages by standardizing data preparation and modeling.
• To create simpler applications in terms of Self-Service BI, you use DeltaMaster TableWizard/ CubeWizard or the Model mode of DeltaMaster 6. No programming or database skills are required. The data that you need to process is typically stored in Excel or Access files (i.e. XLS or MDB files). You can also connect to ODBC or OLE DB data sources.

On the following pages, we will show how you can create member properties using TableWizard or the Model mode of DeltaMaster 6.

Columns of properties

We will start the following example with an Excel file that contains raw data from our “Chair” reference application and has already been loaded into a new Analysis Model in DeltaMaster. For more information about these first steps, please read DeltaMaster clicks! 04/2010.

The table’s setup is typical for data sets in self-service applications: The rows of the table contain business transactions (e.g. sales contracts or invoice data) while the columns show various attributes and KPIs for these transactions. Many times, several attributes refer to the same object. For each customer, for example, there is a customer number from the database (“CustomerID”), name (“CustomerName”), and branch classification (“BranchName”). Each product has an ID (“ProductID“), a name in English and German (“Product_EN”, “Product_DE”) as well as size and weight information. The table also contains hierarchy data for the product dimension. This includes information on the product group and product main group, which also have their own IDs as well as English and German names. The table also contains measures (revenues, discount, etc.), which, as an exception, are not relevant in the context of our topic.

With DeltaMaster TableWizard, you can create an analysis model for DeltaMaster with this data. The model can be multidimensional – in other words, an OLAP cube, which you can build using DeltaMaster CubeWizard (see DeltaMaster clicks! 01/2013). It can also be relational, which means there is no intermediate storage. OLAP cubes have advantages if you want to add additional means of comparison (as calculated members) or KPIs (as user-defined measures). For working with member properties as described below, there is no difference between multidimensional and relational models.

Here’s how

During the modeling process, you define how you want to use the data columns in the analysis model: in a dimension or as a measure. In a dimension, the values of the column can take on various tasks: They can form the base dimension members, combine dimension members into groups (levels), or describe dimension members – which brings us back to the topic of member properties.

To define a field as a property of a dimension level using TableWizard, you will first need the dimension. You can then assign one or more properties to its levels. You can add a Dimension in the context menu of the column header. In our example, we have already modeled the time axis. So we continue with the column “CustomerID”.

The new dimension is immediately available in the View window. The column header in the table is colored in purple to show that this column is used as a dimension (or hierarchy or level).

If you use the Dimension Browser to take a closer look at this dimension, you will see that DeltaMaster created dimension members from the values in the column “CustomerID“ and placed them on the lowest level of a new hierarchy with the same name. DeltaMaster automatically added the sum member “All” and the respective level. It also used “CustomerID”, the name of the field that was used to build up the structure, as the name for the level, hierarchy, and dimension.

You can easily change the names of these structural components. For example, you can rename the sum member and levels in their respective context menus (or by hitting the F2 key), the hierarchy through the I want to menu, and the dimension by directly editing the name (alternatively: I want to menu, or, when the Dimension Browser is closed, you can also change the name with a double click or by selecting the respective entry in the context menu of the View window.)

It is recommended to use clean naming conventions from the start. The names used in this example are very clear, almost to the point of exaggeration for instructional purposes.

In the new customer dimension, you can now assign properties to the customer – for example, a name, “CustomerName” – using the respective entry in the context menu.

DeltaMaster will then ask where it should assign the property in two steps – specifically, in which dimension (or dimension and hierarchy if there is more than one hierarchy) and on which level. If you click on the name of the level, DeltaMaster will take over the field as a member property.

You can opt to add the new member property as an alias for the dimension member as well. Click on the option that DeltaMaster displays as a third step to the right of the member name.

DeltaMaster colors the fields used as a property in yellow. You can remove this member property using the respective entry in the context menu.

If you want to use a field in multiple hierarchies, hold the Alt key while opening the context menu. Now you can add further Member Properties. If you mouse over the column header, DeltaMaster will display all usages of the field as a tooltip.

You can instantly see the modeled properties in the Dimension Browser and use them in reports and analyses as described in DeltaMaster clicks! 11/2015 and 12/2015. On each level, you can select one of the member properties as an Alias, which is valid for the entire application. Alternatively, you could also define in the data source table that DeltaMaster should use a property as an alias (see the fourth screenshot on the previous page).

The screenshot on your right shows how the product dimension was modeled from the sample data. To define several member properties at the same time, press and hold the Ctrl key, click on the desired column headers, and then select the respective menu from the context menu. All Properties of a level are displayed in the Dimension Browser on a separate tab.

The member properties that you create with TableWizard are always for a specific level. This has to do with the underlying database technology – namely, relational models or models based on Microsoft Analysis Services with regular, level-based hierarchies (see DeltaMaster clicks! 11/2015, page 3). You cannot create cross-level properties with TableWizard.

Even simpler in DeltaMaster 6

Building self-service applications is even easier in DeltaMaster 6.1.0. Once you have selected the data source (Application menu , New), e.g. an Excel file, DeltaMaster will switch to Model mode.

You can create a dimension by dragging a field from the data table and dropping it on the upper part of the filter bar or using the respective function in the context menu. The context menu also contains an option to rename the field. This function is much more comfortable in Delta­Master 6 than in DeltaMaster 5: When renaming the field in the table view, DeltaMaster will change the name of the dimension, hierarchy, and level as well. The fields that have already been used will be in black type while the unused ones will be gray. Renamed fields are displayed with cursive type in the column header.

DeltaMaster 6 uses clear words to describe how the member properties should be used. The context menu determines if a field is (solely) the Property of a dimension level or (also) an Alias for its members. You can assign several fields as a property but only one can also serve as an alias. Once you have defined a field as an alias, DeltaMaster will remove any previous reference to an alias. The property itself will remain intact.

The Filter Browser (i.e. the name of the Dimension Browser in Delta­Master 6) will display the assigned alias in place of each member. The customer IDs, which were hard to read, are now gone.

In DeltaMaster 6, you can also view the defined properties on the Levels tab. This tab is only displayed in Model mode, not in Edit or Report mode. If an alias has been defined in Model mode, it is valid for the entire application.

If you need to use a different alias for individual reports in DeltaMaster 6 or DeltaMaster 5, you can define this in the Axis Definition (see DeltaMaster clicks! 11/2015).07