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.

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

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).

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.

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.

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:

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:

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

Greetings, fellow data analysts!

In The BI Survey 15, DeltaMaster ranked first within its peer group in five categories. One of them was project success, where users measured their ability to complete projects on time and on budget. The high implementation speed of DeltaMaster projects is possible, in part, because you can modify and extend DeltaMaster applica­tions quickly – oftentimes without even having to change the underlying database. To create new structures and KPIs, you simply reuse what is already available in the database. This also works with member properties, which we explore in this edition of DeltaMaster clicks! as the second part of our series.

Best regards,
Your Bissantz & Company team

In the last edition of DeltaMaster clicks!, we introduced the concept of member properties and explained how you can use them to design reports, in particular, Pivot Tables. Yet that is just the beginning. With DeltaMaster, you can use member properties to extend the analysis model to create new structures which you can use in analyses and reports again. Member properties are also very useful when working with the ReportServer.

Creating a virtual hierarchy from member properties

In our reference application “Chair”, the attribute “ABC Revenue” is saved for each customer. This could be, for example, a classification taken from a CRM system. With DeltaMaster, you can use this attribute to build a virtual hierarchy that is additional to those in the database. This function is available in the Dimension Browser (I want to menu) of Miner mode.

DeltaMaster requires four pieces of information to create a hierarchy:

• First of all, select the Dimension level in which you want to use a member property for creating the classification. The resulting hierarchy will contain the members of this level including their subordinate members. DeltaMaster will ignore members of higher levels and, therefore, not include them in the hierarchy (with the exception of the “all” member).

• The values of the selected Member property determine which members will be grouped. DeltaMaster creates a separate class in the hierarchy for each distinct characteristic and assigns all members with this characteristic to this class. In the scenario above, for example, DeltaMaster will generate the class “A” and assign all customers that have the value “A” for “ABC Revenue” to it.
• DeltaMaster will create the new hierarchy below the member specified as the Parent member. Most times, you will choose the “all” member of the dimension (e.g. “All customers”).
• Finally, you must specify if the Classification should be static or dynamic. Static means that DeltaMaster will only assign members to classes a single time, namely when it creates the hierarchy. The found classes then are saved to a list and remain even if in the future some members will be saved to the database with different property values. Dynamic means that DeltaMaster updates the hierarchy with each database query using the member property values that are valid at the time of execution.

You can now access the virtual hierarchy in the Dimension Browser, hierarchy menus, and other places where you work with hierarchies. Functions for renaming or deleting the hierarchy are located in the I want to menu in the Dimension Browser.

Hierarchies based on member properties offer various ways to process information from the database and utilize it in analysis and reporting. When it comes to designing and modeling the application, you should weigh the pros and cons of using hierarchies from member properties in comparison to two alternative constructs:

• DeltaMaster can also access parallel hierarchies that have been created in the database. If you intend to work with structures based on time-independent member properties on a regular basis, it might be wise to create them directly in the database.
• You can also calculate the values that determine the classes within DeltaMaster (instead of transferring them from a source system or master data). For example, you can easily calculate a Concentration Analysis based on revenues in DeltaMaster and reuse the ABC classification as a virtual hierarchy. Since this classification is dynamic, you can easily create the hierarchy at any time using the currently valid proportions. Before you build any structures based on analytic criteria, always check first if you can do this using the analytic methods in DeltaMaster.

Creating a measure from member properties

There is a second possibility to use member properties as structural components in analysis models: You treat them as a separate measure, which you can use to conduct further analysis. If your products are saved with their net weight, for example, you can “extract” these numbers as a measure and use them for calculations. This also works with the floor space for stores, population per region, and many other scenarios.

To create a Member Property Measure, add a new measure in the Measure Browser (I want to… menu) and choose the respective option.

On the Definition tab, select the Member Property you want to access (from which Dimension, Hierarchy, and Dimension Level). If desired, DeltaMaster can aggregate member property values for superior levels, for example, to create an average weight for the product group. The individual values are not weighted when they are added to the aggregate. If you do not activate this option, the measure will only return a value for members of the respective level.

As the screenshot on your right shows, net weight is now a measure and not just a descriptive attribute in the report. This means that you can visualize it, for example, using bars.

Member properties are master data and do not change over time. Their values, therefore, are constant by principle. This is something to keep in mind when modeling the database. For example, population and purchasing power are certainly attributes of cities, regions, and countries. You could present them as member properties and use them as a measure for calculations in DeltaMaster. These values will then remain constant within this application. For many analytic purposes, this is perfectly fine. If, however, you need to include changes in population and purchasing power, however, you should create them as separate measures (i.e. not member properties) directly in the database.

Special properties

Member property values also serve as parameters in some functions of DeltaMaster. Then you find a dialog box which the report editor or the application administrator uses to select in which member property the necessary information are saved.

• The “business factor” is an important member property in data models with a dimension for accounts or rows of a P&L statement. This factor describes the business effect of a measure and determines if DeltaMaster displays it using blue or red color in reports and analyses.

In the Hierarchy Properties (Dimension Browser, I want to menu), you can select which member property describes the factor (see screenshot on the next page). Please read DeltaMaster clicks! 01/2010 for further instructions.

• Member properties are essential for a Location Analysis with Google Maps. In order to place objects in the map, DeltaMaster needs the geographic coordinates (i.e. latitude and longitude). This information must be saved as member properties (see DeltaMaster clicks! 06/2011).

• In a Geo Analysis, you can either use the dimension members alone or one of the member properties as a Reference element for levels that are district identical (see DeltaMaster clicks! 05/2014). If you enter postal codes as a member property of sales districts, for example, DeltaMaster can use them to display organizational units on the map.

• In a Geo Analysis, you can opt to color regions in a predefined color regardless of measures (see DeltaMaster deltas! 5.3.2, feature #10). DeltaMaster then takes the color code from a member property that was defined in the Level Properties (context menu of a level in the Dimension Browser) for Member Colors. This option is only used on rare occasions.

Member properties in ReportServer

In ReportServer, you can reference member properties using the variable “@Pxxyy” (“Property“) and include them for example in the subject line or text of an email. In this case “xx” stands for the ID of the dimension and “yy” for the ID of the member property. You can find these IDs in the Model Browser (Model menu in Miner mode).

Example: If you select people (e.g. field sales reps or the employees responsible for a cost center) as dimension members in a dimension of an analysis model, you can then save the proper salutation for correspondence (e.g. “Dear Ms.”, “Dear Mr.”) as a member property. Using the variable “@Pxxyy”, you can access these fields and insert them in the email body. For more information on automatically sending personalized emails, please read DeltaMaster clicks! 11/2011.

Questions? Comments?

Just contact your Bissantz team for more information.

Greetings, fellow data analysts!

A member property is a typical example of an abstract database term that has a very practical use in applications. In DeltaMaster, member properties come to life as images in reports, weight or size informa­tion for products, population numbers of regions, cost center names (in place of numbers), the way you address report recipients, and in many other situations in which you describe report objects (members) in more detail. DeltaMaster offers vast support for member properties – so much so that we will break down this topic into three separate issues of clicks!. This first part will show how member properties can enhance reports and give practical tips how your applications can profit from them.

Best regards,
Your Bissantz & Company team

The analysis models of DeltaMaster use measures and members to describe your business world. To describe these members, you can also use attributes called member properties, which are created and maintained in the underlying database. As you can see in the following examples, you can use member properties in many different ways:

Common usage scenarios for member properties include:

• Additional information for individual objects: You can enhance customer data, for example, by including the address, name of the respective purchasing manager, the homepage, and the path to an image file of the logo and use them in reports.
• Classifying attributes: You can, for example, classify customers by creating industry keys, different statuses, or other properties that define a group. DeltaMaster can then group them in virtual hierarchies that extend the analysis model with new structures without having to change the database.
• Alias: You can, for example, create short and long names for accounts or use the name, number, or a combination of both. Aliases are also common in the time dimension to label reports in different date formats (e.g. “Nov 2015”, “2015-11”, “Nov”, “N”, or “11”). For more tips on using concise labels, either with or without member properties, please read DeltaMaster clicks! 08/2014.
• Multilanguage applications: This role is very similar to aliases, yet there are differences in handling. With aliases, you want to use either this name or that one for an individual dimension or level – in a single report. You configure language, however, as a one-time setting for the entire application. The alias sets of DeltaMaster make this possible – and much more. See DeltaMaster clicks! 04/2007 for more information.
• Technical information: For example, you could create for customers a master directory for exported documents on the file server or add the email address of the respective account manager. DeltaMaster is able to include this information in its processing rules, for example, when running jobs in Report Server. Technical information also includes attributes that have a certain function in the analytic methods of DeltaMaster. Examples include: latitude and longitude coordinates in a Location Analysis, reference elements in a Geo Analysis, or the so-called BI factor in account dimensions.
• Modeling of multidimensional databases: You can use member properties to map complex relationships in a KPI scheme (by means of unary operators and user-defined aggregation rules). These types of topics, however, are not covered in clicks!.

An important trait of member properties is that they count as master data. Their values rarely change, if at all, over time.

Some member properties only make sense on a certain dimension level while others can be used on all levels. For example, you could have one homepage or one sales director for an individual customer but not for the customer’s region or country, which many others share as well. In the case of accounts, however, you could easily use translations as well as long or short names on any level.

The database you use determines if the properties apply to a specific level or all levels:

• Member properties are defined separately for each level in all regular, level-based hierarchies of Microsoft Analysis Services and all relational models.
• Member properties are defined for all levels in IBM Cognos TM1, Infor BI OLAP, Oracle Essbase, SAP BW as well as parent-child hierarchies in Microsoft Analysis Services.

DeltaMaster can work with both variations and offers special options to improve the way you work with level-specific properties.

So much for theory – let’s take a look at a real example. Where do you find member properties in DeltaMaster? This edition of clicks! will start with general settings and Pivot Tables while the next one will focus on analytic methods, modeling, and ReportServer.

Showing member properties as an alias

One central setting is located in the Dimension Browser: You can set an Alias for each level in Pivotizer, Analyzer, and Miner modes as well as in the Model mode of DeltaMaster 6. The selection list contains all available member properties. If you select a member property, DeltaMaster will display its values as alias in place of the member name throughout the entire application – in other words, in all analyses, reports, and dialog boxes. DeltaMaster also uses aliases in place of member names in the search function, for example, in the View window when you hold the Shift key while clicking on a member name.

Multilanguage applications through member properties in alias sets

DeltaMaster also uses member properties to administer several languages simultaneously in one application. This initially works the same way as aliases (see description above): For each language, there is a field allotted in the database to store the translation of the member name. If you want to use an application in a different language, you can combine the desired aliases in an alias set instead having to select them individually for all levels. This way, you only have to define which member properties should be used as aliases e.g. in the alias set “German” one time. You can then select the desired set to display the respective aliases in all dimensions and levels at once.

Using alias sets, you can also administer other namable components of a DeltaMaster application in several languages. These include the names of measures, measure groups, dimensions, dimension groups, hierarchies, levels, report folders, and reports. DeltaMaster uses a type of translation table for these objects so that the translated names can be directly maintained in DeltaMaster. Dimension members have a special role. Since dimension members are usually very large in number, they are not part of alias sets; instead, you create a reference to the translations in the database. For more information on multilanguage applications, please read DeltaMaster clicks! 04/2007.

Display in Pivot Tables

You can use member properties in many different ways in Pivot Tables. The Axis Definition contains a separate tab devoted to them. This tab displays a list of all member properties that are available in the respective dimension. In the case of level-based hierarchies as shown in the screenshot on your right, both the level and the member property are listed. Otherwise, you will only see the property. From this list, you select which properties should be displayed in the table. You can change the display order using drag and drop. Alternatively, you can display the member properties directly in the report through the context menu of the axis.

You can define the type of View individually for each member property.

• As column or As row: DeltaMaster displays the selected member properties in fixed columns or fixed rows in the table, behind or below the member name. This adds one or more additional fields to the column or row heading. These fields behave the same way as member names. For example, they use the same settings for the column width (Table Properties, General tab) and you can search for them using the context menu. This most commonly used option is also the default setting.

The member properties in the example above are numerical. To show that these numbers are captions and not part of the analysis data, DeltaMaster displays them in black type.

• As tooltip: DeltaMaster only displays member properties when you mouse over the member name. This option is very useful when you don’t need the properties as orientation in the table, but you would like the option to access additional information here and there. By displaying member properties as a tooltip, you can integrate more information in the table without needing more space. When you export the Pivot Table to HTML format (e.g. as an email or with ReportServer as a file), the tooltips remain intact. They are not supported in other export formats.

• The table above shows the same properties from the past screenshot as a space-saving tooltip.
• As caption: DeltaMaster replaces the name of the member as if you had selected an alias in the Dimension Browser but only for the respective Pivot Table. If both are activated, the display as caption supersedes the alias from the Dimension Browser.

The caption is the first entry in fixed columns or rows. You can also use this option to change the display order so that other properties are placed at the front or up top instead of the name. DeltaMaster can only display one property as a caption on each hierarchy level.

The screenshot above uses the German names as a caption for the main products while the rest of the table is in English.

• As column/row and as image:DeltaMaster interprets the member properties as the addresses of image files (URL or path/file name), loads these images (bitmaps), and displays them as additional columns or rows in the table. That creates highly vivid presentations, especially when you can easily differentiate the report components and they can be well displayed as a photograph, which is the case with physical products. For more information, please read DeltaMaster deltas! 5.5.7, feature #10.

• As caption and as image: DeltaMaster once again interprets the values as addresses of image files but places them at the first position in the column or row in place of the member names. With this option, you can design a table with just images and no text.

Options for level-specific member properties

As mentioned above, member properties are defined for each level in certain databases. This can make designing data-dense reports a challenge – especially when you need to show several levels in a report. Each member property occupies a separate column but only returns a value for the members of a specific level. This creates gaps in the caption columns.

With DeltaMaster, you can counter this effect using two options in the Axis Definition. These options are located on the right side of the member property list.

• You may want to merge related member properties when working with aliases and languages as in the example above. This option ensures that Delta­Master outputs values of different members in one and the same column and “fills the holes” so to speak. The table needs less space because related properties all use one column instead of being distributed across their own columns. Which properties are related depends on their names. To combine them, select the desired property for one level. DeltaMaster will automatically determine which member properties are related to it. For more information, please read DeltaMaster deltas! 5.5.5, feature #15.

• You can also close the gaps in captions by repeating the member properties of higher levels on lower levels. This does not make the table smaller, but fills it. This option is helpful if you are working with objects on lower levels and want to show their higher levels in context (without adding them as an aggregated member in the Pivot Table) or you want to “pass on” properties to lower levels and display them there. For more information, please read DeltaMaster deltas! 5.5.5, feature #12.

You can use these options separately or in combination.

Filter and sort options in Pivot Tables

You can use member properties to refine the selection of the members displayed in the Pivot Table. In the Axis Definition on the Filter tab, you can define rules which member should be included in the axis. Since member properties are often the data type “text”, DeltaMaster offers the comparative operators “begins with”, “ends with”, “contains” as well as the respective negations. For more information, please read DeltaMaster deltas! 5.3.3, feature #16.

To sort the table by a member property, select it from the Axis Definition on the Ranking tab. Here it does not matter if you can see the property in the Pivot Table or not.

Member properties in analytic methods

DeltaMaster also offers member properties in three analytic methods: Ranking (see screenshot below), Cross Table Analysis, and PowerSearch (provided that you are only analyzing one level).

Once the analysis is calculated, you can show member properties by activating the respective entries in the context menu.

Greetings, fellow data analysts!

As you well know, you can use Business Intelligence applications to view and analyze numbers in great detail. These numbers tell you who, what, when, where, and how much – but never why. This answer requires language – spoken or written – right alongside your data. That is why DeltaMaster offers rich functionality for adding comments. Users can enter comments in each report and, in the case of pivot tables, even on individual values. The following pages will provide an in-depth look at cell comments. One lesser known feature, for example, is that you can aggregate comments on subordinate levels as a summary. Instead of searching for comments, cell by cell, in a granular cost center budget or complex material master, DeltaMaster automatically pulls them together in a variety of ways. In this edition of clicks!, we will show how you can use cell comments to enhance your planning and reporting applications.

Best regards,
Your Bissantz & Company team

In planning, simply entering and submitting numbers often does not suffice. The stories behind these numbers are much more interesting – the underlying conditions and reasons, developments and expectations, consequences and demands. That’s why comments are so important: You can enter and evaluate them along with the respective values. In DeltaMaster, you can add comments for every single value in a pivot table and save them centrally to share with other users. DeltaMaster also offers the option to aggregate cell comments across multiple levels. What’s more, you can even use cell comments beyond planning, for example, to enhance your reporting applications as well. On the following pages, we will show how you can work with cell comments, present various options for aggregating comments, and explain what preparations need to be made ahead of time.

Showing cell comments

If a cell in a pivot table contains a comment, DeltaMaster will designate it with a small red triangle in the upper-right corner of the table cell – a similar look and feel as Microsoft Excel. If you mouse over this symbol, DeltaMaster will display the cell comment as a tooltip. When you export pivot tables to Excel or HTML emails, the comments are included. They are not displayed in other export formats (e.g. Word, PowerPoint, PDF) because they lack suitable possibilities to add comments at the present time. If desired, you can copy cell comments to report comments (context menu of the pivot table or I want to menu) to include them in the export.

Alternatively, DeltaMaster can also display the text directly in the cells of the pivot table. This option is intended for special applications that rely heavily on commentary. This option then displays the text comments in the entire table instead of the commented values. If necessary, you can hide cell comments column by column, using the Column Properties in the context menu (see DeltaMaster deltas! 5.5.7, feature #14).

If you want to display both comments and values, create a copy of the measure and add it to the table next to the actual one.

You can define if DeltaMaster should show flags only or show text in cells in the Table Properties on the Cell Comments tab.

Writing a cell comment

To add a new Cell Comment or edit an existing one, open the respective function in the context menu of the cells. This function is supported in Viewer, Pivotizer, Analyzer, and Miner modes as well as using the WebClient. A license for planning functionality is required. If you do not see this entry in the context menu, you will have to first prep the application for data entry. We will explain what this entails later on.

The upper part of the editing dialog box shows the cell that the comment is referencing.

If you have activated an aggregated display for comments and the subordinate levels contain comments, DeltaMaster will display them in the lower section of the dialog box. Although you cannot edit the other comments here, you can copy them to a clipboard.

If you wish, DeltaMaster can automatically enter the Cell address and/or the respective Value in the input field as Default values for new Cell Comments (see screenshot above). You can regulate this behavior in the Options (Extras menu) on the Cell Comments tab. The Cell address at cockpit transfers the captions of the members that lie on the axes of the pivot table. The Cell address at cube includes all dimensions including the selected members in the View.

To delete a comment, remove the text in the input field. This will delete the comment text in the database. The database entry will remain with an empty text field.

DeltaMaster saves all comments to a central database so that authorized users can immediately access the same comments. In doing so, it includes their exact “coordinates”, meaning the measure and dimension members that the comment is referencing (i.e. based on the Axis Definition, View, and Cell Comment Context as explained below).

Aggregating cell comments

One clever function in DeltaMaster is the aggregation of texts. If someone has entered comments for a child member deeper in the hierarchy, users can automatically see it on higher levels as well. That means you can review all comments on the highest level without having to check all of the hierarchy’s branches. This also ensures that you do not oversee comments when the respective members are hidden, for example, due to filter criteria or because the lowest hierarchy levels are collapsed.

You can set the Aggregation mode under Options (Extras menu) on the Cell Comments tab.

We will use an example from our reference application “Chair” to clarify these modes. We have created cell comments for revenues on various levels of the customer dimension – specifically for the members “All Customers”, “South”, “Tunbridge Wells”, and “Eurotunnel Corp.”. The area “South 1” contains no comments. The following screenshot shows a pivot table configured to display the texts directly in the cells. This way, you can view all comments that are available for a cell at a glance.

• Not aggregated displays comments (or the red triangles that reference them) where they were written.

The screenshot on your right shows this starting scenario. Four cells contain comments while one does not.

• Aggregated displays all comments of all child members in the parent member.

In other words, it displays the comments for that particular member as well as all subordinate members in the hierarchy. This also includes members that are not currently displayed in the table. If comments are available for other customers in the area “South 1”, DeltaMaster would display them here – and in all higher levels as well.

• If you decide to aggregate up to a certain level, DeltaMaster will display another field to the right of the drop-down list. Here you enter on how many levels to the top DeltaMaster should display the comment. This means that the comments are only passed on to a certain number of levels and not to the highest level. The value is a moving gap, meaning “x” levels to the top and not the “x-th” level from the top.

This option is very useful when you are working for example with complex cost center budgets or granular sales structures. It accounts for the fact that the relevance of individual postings decreases as the overall context grows, while the number of individual postings and comments increases. A comment on an individual cost area may be relevant if you are observing cost areas, cost centers or cost center groups, but not business divisions, companies, or the entire group. The cost center, in turn, is relevant for the cost center group and business division, possibly even the entire company, but not the group.

The example on your right is aggregated to 2 dimension levels, which is also the default setting. DeltaMaster will now display the comment for “Eurotunnel Corp.” on the two next higher levels but not for the region “South” and the top member “All customers”. The same applies to the comment for “Tunbridge Wells”, which you can see for “South 1” and “South” but not any higher.

• If you want to aggregate conditionally, DeltaMaster will only display the comments from lower levels when no comment is available for that particular cell itself.

This is the case with “South 1” in the example on your right. Since no comments are available for this member, DeltaMaster lists the comments for its child members. All other members have their own comments, which are displayed accordingly.

The aggregation mode applies to all pivot tables and FlexReports in the application.

Prep work

Before you can work with cell comments, you must first complete the following steps:

1. Ensure that the application is connected to the relational model (Model menu).
2. Create tables for cell comments (or ask an IT expert for assistance). See the following section for more information.
3. Activate planning functions (Extras menu, Options, Data Entry tab).
4. Activate the cell comments in the application (Extras menu, Options, Cell Comments tab).

These steps also apply to pure reporting applications if you want to allow users to enter comments on specific values (and not values themselves). The settings on the Data Input tab of both the Report Properties and Table Properties differentiate it from a planning application. These settings define if and where you can enter values. Cell comments are independent from these settings.

Configuring a database for cell comments

Although creating tables for cell comments is not very difficult, it is usually a job for a database administrator because it requires special permissions. In DeltaMaster, Miner mode is required.

Connecting to the relational model (Model menu) is necessary so that you can administer the cell comments. It is also a prerequisite for displaying the two control elements to configure cell comments (i.e. the link in the Model Browser on the System tab and the Cell Comments Tab in the Options)

In the Model Browser, the System tab contains a link to create/update tables for cell comments. This link opens a dialog box where you can select the fact tables to create or update cell comment tables. If desired, you can also create additional fields so that DeltaMaster can save when a comment was saved by which user (see DeltaMaster deltas! 5.6.0, feature #17). For technical reasons, you will also have to enter the field length. The default value is 1,000 characters, which is slightly more than the length of the past two paragraphs.

If you close the configuration with OK, DeltaMaster will immediately run the SQL commands on the respective database. This is usually the relational model of the application (unless you have specified another one). This means that DeltaMaster saves comment tables and fact tables in the same database. If you want to save the comments in another database, you will have to specify this in the Options on the Cell Comments tab (see below). Instead of running the SQL commands immediately, you can also generate them as script so that you can modify them individually or run them in another database. DeltaMaster either copies the Script to the clipboard or saves it as a file.

You can use the wizard to create new or update existing comment tables. The updating function is additive, which means that the generated scripts do not delete anything (e.g. tables, rows, or columns). If you no longer need certain fields after you make changes to the model, an administrator should delete them manually or rebuild the table from scratch using only the necessary fields.

A special note for database administrators:  Each selected fact table uses one comment table with the name “<fact table>_TEXT”. If DeltaMaster cannot find the respective table in the relational model, it will create the table by generating the CREATE command as a Script; otherwise it will provide instruct­tions to add columns conditionally. The field length is only relevant when creating a table or new columns and has no effect on existing columns. In order to enter cell comments, users will require write permissions for the cell comment table (UPDATE and INSERT).

DeltaMaster takes the Cell comment context (or Dimension context for cell comment tables) into account when you create or update comment tables. You can edit this context in the Options of the Cell Comments tab. It regulates which dimensions and hierarchies DeltaMaster should include for administrating cell comments. Please read DeltaMaster deltas! 5.6.4, feature #5 for more information.

If you want to enter cell comments for user-defined measures, please note that DeltaMaster does not include any fields for them when generating data structures – and for good reason: Applications tend to accumulate a large number of user-defined measures – for example, filter values that are created by dragging and dropping values in an analytic method. It wouldn’t make sense, to automatically generate comment fields for them. If you want to save comments to a specific user-defined measure, you will have to create a column manually in the comment table. Afterwards, you enter this column in the Measure Properties on the System tab as a Comment Column.

Calculated members, such as “?Budget” also require additional prep work. In the Editor for calculated members, you will need to enter a Comment Key, which is the name or the ID that DeltaMaster uses to access the cell comment table. If the members of the dimension are numerical, enter a unique number (e.g. -1) for the Comment Key. Otherwise, please enter text.

If the tables are saved on the same server in a different Database, please enter its name in the Options on the Cell Comments tab.

Once you have set up the database in this manner, select the option to get cell comments from relational database to activate the full comment functionality.

Questions? Comments?

Just contact your Bissantz team for more information.

Greetings, fellow data analysts!

One major advantage of OLAP databases is that you can create a data model that closely resembles your business structures. You typically group customers, materials, teams, cost centers, etc. into hierarchies that you can use in reporting and analysis. These structures are essential so that report consumers can gain the insights they need to drive business performance. Even though the OLAP data model represents how you usually want to view your data, there are times when you will need to diverge from it: You may want to create different groups and subsets to examine the effects of individual measures. You can define these special groups easily in DeltaMaster without having to make any changes to the underlying database. That makes it a role model in user-friendly data modelling!

Best regards,
Your Bissantz & Company team

There are times when you want display or examine customers, products, materials, etc. differently than how they are structured in your (OLAP) database. For example, you may want to analyze your sales numbers for just a few articles that you heavily promoted or examine certain customer regions where you are facing new competitors. So how do you make an individual member selection that you can use over and over again without needing to define it in each report? In DeltaMaster, you can either use named sets or user-defined hierarchies.

Named sets

Let’s say that you want to monitor the success of the marketing activities in the Nuremberg Metropolitan Region for our reference company “Chair”. Some cities within the metropo­litan region are already available in the customer base. To display them together in a pivot table, you select them in the Axis Definition of the pivot table – a routine task for DeltaMaster report editors.

If you already know that you will need this same selection time and time again, you can save it as a Named Set in the upper-right menu of the Axis Definition.

Once you have saved this selection, you can open it at any time from this same menu. DeltaMaster will also offer all other named sets defined for the respective dimension – for example, all named sets with customers in the customer dimension, all named sets with products in the product dimension, etc.

In the Named Set Browser (Model menu in Miner mode), you can later modify these groups (Properties) or add new ones using an editor very similar to the Axis Definition. The only difference is that you can select the dimension in the Named Set Editor while you cannot in the Axis Definition (i.e. given in the Report Definition).

In our example, you can access the cities in the metropolitan region whenever you create a pivot table. That saves editing time, helps minimize mistakes, and simplifies future maintenance: If you ever need to change the list of cities, you only change the named set. DeltaMaster will automatically use the modified named set in all pivot tables, calculations, analyses, etc. You can also iterate named sets in Small Multiples to dynamically compose reports as described in DeltaMaster clicks! 12/2012.

If the members in the named set have child members, you can drill down on them as usual. To create a sum of these members, you need to display the Column Aggregation in the context menu of the pivot table. This aggregation is merely a calculation in the given table – not a separate object in the analysis model of DeltaMaster.

The table above also displays the named set in the pivot table. You can activate the option to Show level caption or named set caption instead of hierarchy caption in the Table Properties on the General tab.

As a general rule, you may use named sets wherever you can enter MDX – for example, in the Axis Definition, the Calculated Member Editor, and in the editor for user-defined measures. To view the MDX name of the set as a tooltip, just hold the Alt key and mouse over the entry in the model tree. With a double click, you can transfer the name into the text field for the MDX expression.

If you know its MDX name, you can also use the named set in Report Server to automatically distribute reports. To successively process all members of a set in a job, enter the MDX name (e.g. “[namedset]”) in the Report Generator. To the left of it, you must select a hierarchy that corresponds to the set.

User-defined hierarchies

The second way to combine members is to create user-defined hierarchies, a function that is only supported in Miner mode. To create a user-defined hierarchy, first select the desired members in the Dimension Browser. Afterwards, choose the respective option from the I want to menu.

DeltaMaster then creates a new hierarchy called “Customer (Selection)” and displays it in the Dimension Browser. It also combines the selected cities into a new virtual member, the class member “Selection”. All remaining members in the respective hierarchy level, such as the members from the region “South 1” and all other regions are now grouped in the virtual member “Residual Class”. This is one difference to named sets, which combine a group of members without taking the remaining ones into account. A user-defined hierarchy, in contrast, keeps all members in the dimension and splits them into two or more groups called classes. This also applies to members that are added later as the database is updated (e.g. new customers). DeltaMaster will automatically add these new members to the residual class, a special class that cannot be removed.

You can change the names of the hierarchy, its levels, and virtual members. To edit the hierarchy name, select the respective option in the I want to menu. You can also modify the levels and virtual members by selecting the option in their respective context menus. DeltaMaster recognizes and uses the residual class as such even after you have changed the name.

Using the respective entries in the context menu of the Dimension Browser, you can add class members or move members to other classes. Each member is always assigned to exactly one class. You can switch the class order by holding the Alt key while dragging and dropping a class there, for example, to move the residual class to the end of the list.

In reports and analyses, virtual members behave the same way as aggregated members from the database. For example, you can show and hide (i.e. expand and collapse) child members. Most importantly, they have a sum or an aggregated value that you can reuse as a separate report and analysis object.

That means that DeltaMaster can calculate sparklines for the class members and generate filter measures that, in turn, can be transferred to analytic methods.

The names of the hierarchy and class members have a major influence on how well people can use the application and understand the results. For example, users in Viewer mode – in other words, who did not define the group themselves – can also access user-defined hierarchies. It, therefore, makes sense to change the abstract name to one that reveals why this selection has been made in the first place.

DeltaMaster displays the names of the levels in hierarchy menus of the analytic methods and the pivot navigation. Here, too, a straightforward name will say more the automatically generated “class” names.

Database or DeltaMaster?

A DeltaMaster analysis model is not the only place where you can manage members in multiple hierarchies at the same time. OLAP databases offer these capabilities as well. You can organize customers there, for example, based on regions, industries, legal structures, or size. DeltaMaster can process parallel hierarchies from the database. Creating them in the database, however, is more complex than in DeltaMaster. Nevertheless, it may pay to invest your time in modelling the database, especially when any of the following cases apply:

• You need to group a large number of members – meaning hundreds or thousands and not just a few specific ones.

• You want to use the parallel hierarchies frequently over a long period of time – and not for just a few ad hoc evaluations.
• You can divide all members into relevant groups that you can compare with each other – and not place just a few members in a selection while the others fall in an irrelevant residual class.
• You want to compare the parallel hierarchies in a cross table. This requires hierarchies from the database and not just user-defined ones.

For the first two factors, query speed plays an important role. The server can pre-calculate hierarchies created in the database and return them very quickly. If you use user-defined hierarchies, DeltaMaster will only calculate the aggregations once the query has been made. That may take a bit longer especially when many members are involved.

You can also define named sets in the database. DeltaMaster will transfer these sets and display them in the Named Set Browser as well (i.e. with the Scope “Global”). Although you may not edit their definitions in DeltaMaster, you can otherwise use these named sets from the database just as the ones created in DeltaMaster.

Set or hierarchy?

Technically speaking, these two alternatives are related. In fact, the user-defined hierarchies of DeltaMaster are implemented using named sets and calculated members. There are, however, differences in usage. The following table provides a side-by-side comparison of the most important characteristics.

Both approaches are reusable and, therefore, efficient. They generate new objects in the DeltaMaster analysis model, which can be administered centrally. This ensures that all changes are updated automatically in analyses and reports without any extra work on your part. In both cases, you can dynamically define the members using MDX (instead of a list as in the example above). You can also create hierarchies within an analysis method (e.g. Concentration, Portfolio, or Distribution Analysis) in DeltaMaster. In this case, we refer to them as virtual hierarchies.

Questions? Comments?

Just contact your Bissantz team for more information.

Greetings, fellow data analysts!

Entrepreneurs and managers have hoped to gain competitive advantages from sophisticated analytic technologies long before Big Data started making headlines in daily newspapers. Our experience from countless analysis projects, however, shows that all-knowing algorithms don’t necessarily reveal the best tips on what work is yet to be done. Oftentimes, the most valuable insights come from well-thought-out KPIs that are analyzed with robust methods. The secret here is asking the right questions and figuring out how to translate them into numbers. Software, in turn, should be intuitive enough so that users can easily define, analyze, and present these metrics. This edition of clicks! will show how you can easily translate questions into analytic applications without getting bogged down with statistics or technology. We will use an early warning system as an example.

Best regards,
Your Bissantz & Company team

Different departments and industries often have one and the same requirement: an early-warning system that notifies users before things get out of hand. Some address this need with forecasts, which were the focus of DeltaMaster clicks! 12/2014. Recognizing relevant changes at an early stage is almost as important – and we will explain how in the sections below.

The screenshot on your right shows a report that we created for this purpose based on the data from our “Chair” reference application. It shows total rebates by products and the rate of current rebates to the average over the past few months (i.e. delta to average; ? to Ø).

This warning signal is easy to recognize: Discounts for “Jackson 10” have risen sharply in the report month. You can also see that discounts have dropped significantly for “Precisio LF” and “DCW Plywood”. You can also identify compensating effects, which are also important information.

So how can you create this type of early warning system in DeltaMaster? First, you have to answer some business aspects:

• What things can get of hand? In other words, which measures are suitable indicators that should be monitored closely?

These include “undesirable” KPIs – such as discounts (in sales), dead mileage (in logistics), cancellations (in publishing houses and service providers), or returns (mail-order companies). These measures must be available – or at least definable – in the underlying database. In most cases, they are already available in DeltaMaster applications, for example, as part of a KPI overview.

• How will you recognize when things are getting out of hand? What are you going to use as a comparison? How will you evaluate the results?

One robust method monitors the measure’s development over time and compares the current value of a measure with previous ones. Here, it makes sense to compare not just a single previous value, but rather the average of several periods (e.g. the past three months) to smooth the development and put the latest results into perspective. This is called a moving average, rolling average, or running average.

As the requirement for the system in our example, we want to observe how discounts develop in comparison to the rolling average of the past three months.

Time analysis member for the three-month average

The following section will briefly explain how to create time analysis members in DeltaMaster. For detailed instructions, please read DeltaMaster clicks! 07/2011.

You add a new Time Analysis Member in the dimension “View of period”, also known as “Time Utility” (Pivotizer, Analyzer, or Miner mode).

As Calculation Type this member is a Query, which is defined as a moving aggre­gation. Three parameters have to be set: first, the desired Level of the time hierarchy (e.g. “Month”); second, the number of members that you want to include (Lag); third the mathe­matical Function (e.g. sum, minimum, maximum, or average) that you wish to calculate.

The previous period is selected as the Reference Member. This is another frequently used time analysis member that queries and returns the respective previous period. When you select the previous period as a reference member, DeltaMaster will not include the “current” period (i.e. the one selected in the View window) in calculating the average. If you select July as the current month, June will be the previous period. DeltaMaster will then use this reference member to calculate the rolling three-month average based on the months April, May, and June.

If you let DeltaMaster propose a name for the time analysis member, please note that these suggested names will contain variables such as “{cp}” or “{pp1}”. DeltaMaster will replace these variables with the concrete period names (e.g. the name of the current period for “{cp}”; the name of the previous period with a lag of 1 for “{pp1}”). DeltaMaster will not include the shift caused by the reference member in the suggested name. If you choose the previous period as the reference member, therefore, you should change the name accordingly.

Time analysis member for variances to the average

The average member can be used immediately for further analysis. Our example, however, goes one step further to calculate the variance to the average. We will review this step briefly below. For detailed instructions, please read DeltaMaster clicks! 07/2011.

Using the Dimension Browser, we add an additional calculated member. This time, however, it is a “regular” one and not a time analysis member.

This member, “Delta to Average” calculates the variance between the actual value and the average that was previously created. You can use the average to show the absolute or relative (percentage) variance. In most cases, however, you will want to examine the absolute variances because they reveal the most important drivers (e.g. clients, products, materials). The relative variances are used more for grading and assessment.

You can insert Special Characters for delta and average using an option in the context menu. This option is available in all entry fields for names, descriptions, annotations, etc. – even in other dialog boxes as well.

Calculated members add flexibility

Ultimately, the calculated members provide two new attributes which you can flexibly use again and again in analyses and reports. In particular, you can easily combine them with other dimensions, levels, and measures.

The rows in the report on your right, for example, display the regions from the customer dimension instead of the product groups from the product dimension. The columns contain the same members as in the previous example and fulfill the same purpose: They quickly show when objects diverge from their previous behavior. You can even stretch this evaluation across multiple levels of the hierarchy. This example includes the aggregated member “All customers” and sorts it so you can view the individual objects as part of the big picture. (This list is filtered so that small amounts do not appear; this is why there is a small variance to the sum.)

You can also use calculated members flexibly with all mea­sures. If you used volume in place of rebates in our exam­ple, you would get a report similar to the screenshot on your right. This report, which shows another period as in the previous examples and was filtered to hide small values, reveals a precarious drop in sales volume.

You can also use calculated members to define additional measures, for example, in filter values. You can create them in the Measure Browser (Model menu) and examine them using various analytic methods in DeltaMaster. For more information on working with filter values (e.g. the effects of filter types), please read DeltaMaster clicks! 03/2008.

In the screenshot on your right, we have applied the Ranking method to the sales volume analysis presented above as a pivot table. The advantage of this method is that you can group the individual positions next to each other based on their algebraic sign – with the good ones on the left and the bad ones on the right. For more tips how you can compare positive and negative values in the same report, please read DeltaMaster clicks! 02/2015.

Using the analytic method Small Multiples, you can even combine multiple analyses for various dimensions or measures into a report (for more information and further hints, please read DeltaMaster clicks! 12/2012). The time level of the time analysis members, however, remains constant. If you want to observe a three-day average in addition to the three-month average, you will need to create a further time analysis member for the “days” level.

Push or pull?

When it comes to early warning systems, many users want to receive automated notifications. As soon as a certain threshold has been met, DeltaMaster should then send an e-mail to warn certain people.

With DeltaMaster ReportServer, you can easily configure these types of automated exception reports. DeltaMaster clicks! 11/2008 explains how that works. It also provides a few good reasons why you may want to avoid using thresholds and send regular, concise reports instead.

Our blog posting “The fundamentals of management information” (www.bissantz.de/bissantz-ponders/management-information-2) summarizes these reasons:

We, too, cannot warm up to the concept of fixed thresholds that trigger alerts. How would you even implement that? How much work does that entail in complex corporate environments? How general or specific should these thresholds be – on country, regional divisional, product, industry, and/or customer levels? Should they be relative or absolute values? Sometimes, people turn to traffic light charts. Yet, because these signals are cautious in both directions, most things are displayed in yellow. You can set thresholds in DeltaMaster for the rare cases where contractual, qualitative, or technical limits provide clarity. We, however, are big fans of sorting everything in descending order based on the value that is important to the company. That allows you to view everything at a glance – without needing thresholds.

Adept readers like to read good reports with well-though-out content and an attractive design – and not just when exceptions occur! There is so much they can identify at a glance – provided that it contains the right information. Ideas and tips for implementation: see above.

Greetings, fellow data analysts!

The shortest route between Boston and Philadelphia is 490 kilometers. That is about the distance of the longest stage ever at the Tour de France – and only one of fifteen at the 1919 tour. Like always, there was a proud winner at the end of the day’s race. Someone else, however, wore the famous yellow jersey (Maillot Jaune). Outstanding performance during one stage of a race is one thing; however, it’s the overall classification that really counts.

In business, it is no different. That’s why you review the numbers for the past month as well as the months prior to it. That is called the “year to date” or a cumulation. This edition of DeltaMaster clicks! will show how you can handle these types of calculations with DeltaMaster. Use them to help your business contend for the yellow jersey – without you even having to break a sweat.

Best regards
Your Bissantz & Company team

In the standard reports generated by the Start Wizard of DeltaMaster, cumulated values play an impor­tant role and are always positioned directly next to non-cumulated values – a central characteristic of these reports. The Start Wizard can also create the necessary calculations in the analysis model. In this edition of clicks!, we will take a closer look at cumulations, explain why they are so important for standard reports and management information as a whole, and show different ways to work with cumulated values in DeltaMaster.

When we talk about a “cumulation”, we mean adding up the values of several periods with a fixed start and a flexible end period. This summation is also called “year to date” (YTD). The start period is typi­cally the first month of the (fiscal) year but can also be the first day of the quarter (“quarter to date“, QTD), the first day of the month (“month to date“, MTD), the start of a shift, etc. The end period is the current month, day, hour, etc. – with “current” here meaning the report period defined in the View of Delta­Master. The fixed starting period differentiates the cumulation from a rolling aggregation. A rolling aggregation, in comparison, has a constant length such as the past 12 months (“moving annual total”, MAT) which determines the starting point.

Non-cumulated and cumulated values: always together

The standard reports in DeltaMaster display the current and cumulated values for all comparison types (i.e. current month, variance to the previous year, variance to budget) directly next to each other instead of combining the non-cumulated values in one group and the cumulated values in another. The cumu­lation, therefore, is the innermost nested dimension on the column axis.

That is how the Start Wizard creates it in the Axis Definition, and we recommend the same basic layout for reports that you create without Start Wizard as well. This structure is based on the premise that you should always view the current month in the context of the business year leading up to that point. Before you discuss the difference between the variance to the previous year and a variance to budget, you should first know if the respective monthly variance differs from the cumulated variance. These cumulated values help you view the current month’s performance in the context of the year so far, see if it is representative, and calculate its contribution to the overall results. This is why they are placed next to each other in the report. You need the cumulation so you can classify the current month. And if there is not enough room in your report to list all value types and comparisons, the cumulation is often more important than the individual values. In other words, it makes more sense to omit the monthly variance than the cumulated variances.

The visualizations in DeltaMaster support this concept. The bars use a global scale, which makes them comparable across all columns. Just by viewing the bar pattern, you get an idea of the current month’s performance. The pattern in the left column is chaotic. The differences in length are larger than in the right column, and some bars are much longer than the longest bar on the right. This visual disorder signifies that business in this month was probably turbulent as well, and that this month does not fit in the picture of the year. That is important to help interpret the content.

Cumulation member

To display the cumulation in DeltaMaster applications, you typically use so-called time analysis mem­bers. These calculated members are created in DeltaMaster or the underlying (OLAP) database as an additional “switch” in a dimension. Their job is to run various queries and calculations on the time axis, always starting from the current time period, either as defined in the View or resulting from the structure of pivot tables. With time analysis members, you can for example query the value of the previous month or the respective month in the previous year, calculate the variance between these values and the current one, or add up all of the values from a dynamically defined time series (i.e. a cumulation).

In most cases, that looks like the screenshot on your right. There are two members: “non-cumulated” as a reference member from the database (written in black) and “cumulated” as a calculated cumu­lation member (written in green if it was defined in DeltaMaster).

This method of modeling is extremely flexible:

• Since you can combine a cumulation member with any measures and members in other linked dimensions, you only need to create the cumulation once. You can see this clearly in the report shown above. DeltaMaster combines the same two members, “non-cumulated” and “cumula­ted”, with different time references, value types (i.e. current, absolute variance to the previous year, relative variance to the previous year, absolute budget variance, relative budget variance) and different measures (e.g. revenues, discounts). The members also work if you switch to different months, even after you enter a new year.
• A cumulation member automatically “finds” the right starting period – even if the end period (i.e. current month) of the report changes after you refresh it or switch to a different year.
• You can use a cumulation member to switch entire reports or as an iteration in Small Multiples reports (see DeltaMaster clicks! 11/2010, section “Iteration type ‘member’”). This allows you to compare other report formats (e.g. Geo Analysis) as pivot tables in non-cumulated and cumulated views.

You add time analysis members in Pivotizer, Analyzer, and Miner modes by opening the Dimension Browser and selecting the respective entry from the I want to or the context menu. For a systematic introduction, please read DeltaMaster clicks! 08/2007.

In the Time Analysis Member Editor under the section Periods-to-date, you can select the Level that determines which member should start the cumulation. In other words, the cumulation member aggregates values that belong to the same parent member on the defined level. In most cases where you want to add up the months of the current year, that is the level “Year”: The monthly values of the same year are added up starting with the first month. This definition applies to all levels below defined one. The member in the example shown can create a sum of both quarters and months depen­ding if the current member is a quarter or month. If you wish to create a cumulation of the current quarter (quarter to date), select “Quarter” as the level.

In the section Periods-to-Date-to-Year-Ago, you can combine this calculation with movement on the time axis. This allows you to query the respective values of the previous year as well as calculate variances (all Calculation Types are available; for Period-to-Period only Query). This option is necessary when only one dimension is available for cumulation and time analysis. If you have two dimensions, one for cumulation and one for time analysis (e.g. “Period View” or “Time Utility”), you would include the values from the previous year by combining both dimensions. For example, you would combine the member “Cumulated” with members such as “Previous Year” or “Previous Year Variance” in the Axis Definition of a pivot table.

Cumulation dimension

So which dimension should you use when creating a cumulation member? We recommend that you create a separate dimension solely for this purpose (and another one for time analysis). This dimension only needs one member: “non-cumulated”, which serves as a reference member for the cumulation member that you want to create in DeltaMaster.

If you create a data model using DeltaMaster Modeler, you do not need to worry about the cumulation dimension because it is a fixed component of the metadata model and DeltaMaster Modeler will generate it automatically including the reference member “non-cumulated”. You do not need to reserve a column in the relational data source for it.

The case is also nearly that simple when you generate an OLAP Database using DeltaMaster CubeWizard (Model menu): The wizard offers you to create a cumulation dimension so that you do not need to create a separate column in the relational source. Then you add the cumulation member in DeltaMaster as described above.

Even if you have no influence on the data model, you can still use the cumulation logic of DeltaMaster. On principle, you can add a cumulation member to any other dimension. If the data model contains no cumulation dimension, a time analysis dimension (e.g. “Period View” or “Time Utility”) is a good place to store a cumulation member. As of DeltaMaster 5.6.3, you can even add one to the time dimension – with only minor limitations as described in DeltaMaster deltas! 5.6.3, feature #3.

Automation through Start Wizard

Creating a cumulation member is one of the first steps of every project. The Start Wizard of DeltaMaster can complete these tasks for you automatically (Model menu in Miner mode). DeltaMaster only needs to know where (i.e. in which dimension) you want to store the cumulation. You can enter this information on the Model tab. If DeltaMaster finds a dimension with a name such as “Cumulation”, it will automatically select it. Otherwise, you can select the desired dimension from a list containing all dimensions. Next, you define which member of this dimension should be used as the “non-cumulated” Reference Member. The first time you use this wizard, there is often only one member in this dimension anyway. You will also need to enter the Cumulation Member you wish to use. If none is available, DeltaMaster can create one. Otherwise, you can select a defined member from the database. If you choose to create one, DeltaMaster will try to reuse a suitable, existing cumulation member so that the Start Wizard can be used it again and again without generating duplicate calculated members.

If you assign no dimension to the Cumulation, DeltaMaster will create the cumulation member in the time dimension as described above.

The Start Wizard will then generate all reports and the necessary model components.

Cumulation in analyses

Once you have defined a calculated member, you can use the cumulation as an additional attribute in reports or change the perspective of analyses and reports in the View. In some cases, cumulations are even inherent to the method.

For example, DeltaMaster automatically works with cumulated values in a Trumpet Curve without having to create a cumulation member (or using one that already exists). This method is named after the shape of the resulting tolerance corridor, which is relatively wide at the beginning of the year (i.e. because there is still time to take counteractions) and gets narrower towards the end. By forecasting the cumulated variance to the year’s end, you can see how likely you will reach the year’s target based on the current development. For a detailed description of this method, please read DeltaMaster clicks! 05/2007.

The standard report “Way to go” also centers on cumulated variances. If these are negative, it even shows what you need to achieve in addition to obtain the planned yearly target. This report, which is generated by the Start Wizard, accesses a cumu­lation member. For more information, please read DeltaMaster clicks! 12/2014.

Since both the Trumpet Curve and “Way to go” reports have cumulations in them, you should always use them with a “not cumulated” setting – and not adjust them to include an additional cumulation. This primarily refers to the setting in the View window as well as the Axis Definition of pivot tables or the Measure Properties. You also need to keep this in mind for all other calculations based on a sum, for example, a rolling average (see DeltaMaster clicks! 07/2011) or user-defined measures that calculate an aggregation using MDX.

A Concentration Analysis also deals with cumulated values as illustrated with the class summary shown here on your right. These cumulations, however, refer to a consecutive addition from the largest member down to the current member. This, again, is based on the principle that the first member in the sum is constant and further ones are added successively. The cumulation, however, is based on a sorted list – and not time. As a result, you can combine a Concentration Analysis with a cumulated view.

Sparklines: Better without cumulations

Visualizing cumulated values with sparklines only makes sense for variances – budget-actual variances in particular. Otherwise, they produce almost identical saw-tooth waves as shown in the left column of the screenshot on your right. This visualization is useless because it cannot differentiate the changes from period to period.

You may sometimes receive requests to label the sparkline with a cumulated value – for example, to display the monthly values as the columns of the sparkline as well as the sum as a number directly after the sparkline. We vehemently disagree! The standard rule for creating and reading sparklines says that the number is the label of the last column. This provides a solid base for the other columns so that you can interpret them. If you deviate from this rule, many readers will not realize this and there is a high risk for misunderstandings.

Therefore, non-cumulated values with sparklines belong in one column and the sums without sparklines belong in a separate, adjacent one as shown in the screenshot on your right.

These tips are explained in part three of our checklist for DeltaMaster applications (see DeltaMaster clicks! 11/2013).

Literally

The simple concept of a cumulation is rather cumbersome to put in writing – and the simpler case, the “non-cumulation” even more so. In fact, it will make the table start with a negation which is not really beautiful. Nevertheless, the combination of “non-cumulated/cumulated” is clear, compact, and consis­tent. That’s why the abbreviated version “not cum./cum.” is usually our first choice. If you want to avoid using negations, “current/cumu­lated” or “cur./cum.” is another option. The term “Year to date” or “YTD” is also widely used. Instead of negating this term, we recommend a term such as “current” (“cur.“) or “actual“ (“act.“). No matter which term you use, please use it consistently.

Abbreviating captions

Readers and editors will generally assume that the values are non-cumulated unless the report states something to the contrary. You can (and should) use this assumption to your advantage and hide the member “non-cumulated” in the View Description. This makes sense because you really do not need to say it every time. To hide it, open the Dimension Browser and deactivate the respective option in the context menu of that member (Pivotizer, Analyzer, or Miner mode). This shortens the labeling of the reports and improves their readability. This tip is also listed in part one of our checklist for DeltaMaster applications (DeltaMaster clicks! 09/2013).

No special requirements in the data model

In order for DeltaMaster to calculate the cumulation, there are a few simple “requirements” for the data model. In fact, you can view them more as a best practice.

The structure of the time axis is important for the cumulation. The periods must be in one (and only one) hierarchy, for example, with months, quarters, and years so that DeltaMaster can find the period where the cumulation begins. The periods, therefore, should not be modelled consecutively for multiple years in a flat list, and the different time levels (e.g. months, quarters, years) should not exist as separate dimensions. Occasionally, you may come across data models that classify cumulated values as additional members below the non-cumulated members. These types of constructions are outdated, redundant, and often awkward to analyze. A cleaner way to model is to create a time dimension with separate dimensions for time analysis and cumulation as described above. If the time axis of your model looks like the one on your right, it is fine.

Questions? Comments?

Just contact your Bissantz team for more information.

Greetings, fellow data analysts!

You can often make processes much more efficient by focusing on changes. Loading a data warehouse, for example, is much faster when you concentrate on new and modified records (i.e. delta load). Video films are compressed by first saving a few reference images – instead of them all in their entirety – and then calculating the smaller difference images. To discuss legislative changes more quickly, lawyers and poli­ticians compare the old and new versions and highlight the modified passages. As a general rule, editors of articles, reports, and many other written compositions also mark their changes or track them using a word processing program.

DeltaMaster supports a type of change logic as well: You can use ReportServer to automatically update and distribute reports as well as highlight any changes. That makes it easier for the reader without making it harder for the editor. Sometimes boosting efficiency can be that simple.

Best regards,
Your Bissantz & Company team

There are many different business scenarios where you need to update and extend lists or evaluations on a regular basis. If your sales team is inviting clients and prospects to an in-house exhibition, for example, the sales director will want to have daily updates on the registrations. The registration list will grow larger from day to day. To ensure that the sales director does not have to review it each time from start to finish, you should highlight the new registrations – not to mention, the accomplishments of the hardworking inside sales team.

The screenshot on your right shows an example of this type of report. This email, opened in Microsoft Outlook, is based on a DeltaMaster pivot table that ReportServer sent as an HTML email. The report shows two things: the current status in its entirety as well as what has chan­ged. That is important because changes usually require more attention than things that have stayed the same (and have already received due attention at an earlier time). Best of all, you can set up these applications quickly with DeltaMaster ReportServer as we will explain below. As example, we stick to the event registrations. The steps below, however, are a general description and apply to many different usage scenarios, such as customer service inquiries (i.e. support cases), manufacturing orders, or entries from a CRM system. You can even highlight changes in reports where only the values change and the number of rows and columns remain constant.

Exception Reporting: Changes as exceptions

The key to recognizing changes is so-called Exception Reporting. With this option, you can influence the behavior of Report Server. For each report, you can define a condition that ReportServer evaluates when processing the job. Only if the condition is fulfilled, ReportServer will include the report in the job output. If all reports of a report source (i.e. analysis session or application) are filtered out through conditions, ReportServer will end its work without outputting anything. In other words, it generates no files and sends no emails. It only generates files and sends emails if the defined condition has been fulfilled for at least one report (or at least one report is not linked to a condition). You can use ReportServer in this manner to send event-driven messages. For more information about this function, please read DeltaMaster clicks! 11/2008.

Conditions

You can configure Exception Reporting in Pivotizer mode and higher in the Report Properties (context menu of the reports in the Briefing Book) on the respective tab. Here you opt to include the report in the result of a ReportServer job only, if a condition has been fulfilled.

The condition is defined as one of three things:

• The number of rows in the report: The most important scenario where you would use this condition is to eliminate empty reports. You should only include a report if it has “more than 0 rows”. This option, for example, is used to create detailed lists about variances: If there are no variances, ReportServer should not generate the entire report. This option is also useful if you are preparing personalized briefing books: ReportServer will hold back reports that are filtered by recipients if they are empty following the filter. Instead of receiving an empty report, the recipient receives nothing at all. This option is designed for pivot tables and primarily tabular report types (e.g. SQL Drill Through, Ranking, or PowerSearch).
• An MDX statement: If the MDX expression returns “true”, ReportServer will include the report in the job result. With MDX, you can include criteria that are not reflected in the report’s length or cannot even be taken directly from the report. You can apply the MDX expression to all report types. This is the only option for combination cockpits or other cases where you cannot define a row count as a general rule.
• A comparison whether the current results contain changes compared with the last run: This option is available for all types of reports. Depending on the output format, you can highlight the changed table cells in pivot tables and reports from an SQL Drill-Through.

For the task at hand in our example, the third option is the right one: ReportServer should check if something has changed in the registration list and highlight the changes if necessary. You only need to activate the option in the Report Properties. No changes are necessary in ReportServer.

Updating the report source

How does ReportServer know if something has changed? It calculates the report with “fresh” data and compares the result of this calculation with the one saved in the analysis session (DAS file). If the results are identical, nothing has obviously changed and it will not include the report in the job output. If there are no reports to be included in the job output, ReportServer will not output anything as described above. There is no need to send a message when there is nothing new to report.

Provided that the change recognition is activated for at least one report, ReportServer will recalculate the analysis session selected as the Report Source for each job cycle and save it to the same DAS file. This differs from the normal behavior of ReportServer, which generally does not change the report source. When it comes to changes, however, it will update the report source with each cycle. The DAS file, therefore, must not have write protection. Applications in Repository and DM2GO files are currently not supported, the change management feature is currently reserved for DAS files only.

Formatting

If ReportServer has recognized changes and these changes are to be highlighted, DeltaMaster will color the background of the respective cells yellow depending on the output format. This formatting is supported in Microsoft Office formats (Word, Excel, PowerPoint), PDFs as well as HTML files and mails. In SQL Drill-Through reports, you can output the report content as text (instead of embedding the report as an image in the HTML document). In this case you can use CSS to modify the formatting (see DeltaMaster deltas! 5.6.1, feature #1b).

See and go

If you send the list as an email, do a service to your readers and enter the most important figures in the subject line. In our example that would be the current number of entries (i.e. registrations), how many are new compared to the past report, and how many were removed.

This method of reporting is extremely efficient. No information channel is monitored as intensively as an email inbox – and the subject line is the first thing that readers notice in their email. Oftentimes, however, this channel is not utilized to its full potential. All too often, the subject line only provides a general announcement (e.g. “Registrations for in-house exhibition”) instead of actual information (“In-house Exhibition: 41 Registrations, 5 new, 0 cancelled”). In DeltaMaster clicks! 06/2013, we explained how you can write better subject lines using variables that automatically embed values from reports into the subject line.

To communicate changes in reports, you can use three special variables:

“X” here stands for the ID of the report where you want to insert the length or delta length. You can find the ID in the Report Properties or tooltip that appears when you hold the Alt key while placing your mouse on a report. In order for this to work, the report must be based on a pivot table or SQL Drill-Through.

You can place variables in the Email Subject, Email Text, and Email Attachment. When it sends the message, ReportServer will replace these variables with the respective values.

Questions? Comments?

Just contact your Bissantz team for more information.

Greetings, fellow data analysts!

Many regular sources of information follow specific conventions or guidelines regarding the content and order for reporting things. Standards for form and content make it easier to create and use reports as well. With DeltaMaster, you can even standardize things that are not part of the initial report – namely, the interactive steps to trace values and variances back to their origins. Using the Pivot Navigation, you can define and save analysis paths directly in a report. With a simple double click or tap, users can access them intuitively and start a detailed analysis using the exact steps that you intended. Best of all, setting it up is simple. We will explain how in this edition of DeltaMaster clicks!.

Best regards,
Your Bissantz & Company team

The Pivot Navigation in DeltaMaster is a tried-and-tested method to interactively start a detailed analysis in a pivot table. Using a drill-down function, it expands the pivot table – column for column or row by row. Each step adds a dimension and explains how the respective starting value is broken down among the members of the new dimension that is displayed. You can determine which dimension DeltaMaster shows next in various ways. One popular choice, which is simple for end users as well, is the predefined navigation: If you double click on a value in the table or tap on the “Plus” button (DeltaMaster Navigator), DeltaMaster will run the next step in a certain order that was defined in advance and saved with the report. In this edition of DeltaMaster clicks!, we will show you how.

Variance analysis, step by step

Let’s use the following screenshot from DeltaMaster 6.0.1 Navigator as an example. The mouse pointer shows that the cumulated budget-actual variance for Gross Margin is ?1,644.4. Using the Pivot Naviga­tion, you can examine the reasons behind this variance.

If you double click on the value you want to analyze, DeltaMaster will insert an additional dimension – in this case, the sales teams – into the table and show how the variance breaks down among the individual members of this dimension.

In our example, as shown in the screenshot above, you will see this exact dimension (and no other one) because it was defined as the first step of a predefined sequence. It doesn’t matter if you start the analysis from current revenues, cumulated discounts, absolute variance of the material costs, the relative variance of gross margin, or some other value. The breakdown by sales teams will always be the first step in this table. Here you can see that the team Brown was responsible for the largest part of the variance (-2,353.0). With another double click, DeltaMaster will break down this number further.

Products, the dimension defined in our sample table as the second step, were added next. Softpad (?1,654.2) was the main driver behind the variance of sales team Brown. DeltaMaster will provide even more information if you double click again.

Now DeltaMaster shows the customers as well. Here you can clearly see that it doesn’t simply return all of the members of this dimension. Instead, it only returns the most important ones – namely, those with the strongest positive and negative variances. DeltaMaster grouped customers with smaller negative variances into the member “remaining bottom”, customers with smaller positive variances into the member “remaining top”. This reduction, which is characteristic for the Pivot Navigation, is intended to direct your attention to the most important objects. In fact, you can sort the dimension members and limit the count in each step (or change the default setting in DeltaMaster).

For the sake of this example, we decided to leave it to three navigation steps. Generally speaking, you can make your analysis paths as long as the number of dimensions that you have. In each step, the users can opt to diverge from the defined path to continue the analysis on their own (i.e. using hierarchy menus which are shown for the respective values) or use the integrated data mining method. This edition of clicks! will only delve into the predefined navigation.

Predefining means analyzing in advance

In order to specify navigation steps in advance, you need to edit the respective pivot table in Analyzer or Miner mode. This involves four mandatory steps:

1. Run the Pivot Navigation using the same steps you want to define for other users.
2. Save non-automatic steps (on row axis) as default (Table Properties, Pivot Navigation tab).
3. Collapse the navigation steps until you return to the original table.
4. Save the report.

In other words, DeltaMaster will derive the analysis path based on a Pivot Navigation that you have already run (e.g. and not in a separate configuration dialog box). This method may remind you of macros that you record and run using your keyboard or mouse.

Think straight

Report consumers, therefore, access the Pivot Navigation using the exact same steps how the report editor had run and saved it previously. As a result, you should invest the time to think out the analysis path carefully from a business standpoint. Two things are very important: the order in which the additional dimensions appear in the table and the selected dimension members in the respective navigation step.

Order

To define the order of the dimension, use the user driven navigation, which is primarily the hierarchy menus of the pivot table. These hierarchy menus will appear if you mouse over one of the little, black arrows displayed under the data cells as soon as you position your mouse in the lower section of the data cells. If the hierarchy menu is very long in your application or allegedly contains double entries (i.e. typically dimensions with the same names as the level), you should check the setting for displaying hierarchy menus in the Options (Extras menu) as described in DeltaMaster clicks! 02/2011.

The Automatic Navigation, which is offered at the top of the menu, is not relevant for predefining analysis paths. It applies a data mining method to select the next dimension depending on the current data constellation. Although this function is very useful when you are uncertain where to begin, it is an entirely different task. If you define an analysis path in advance, you want the analysis to always follow the same steps – and not different ones.

When defining the order of the dimensions, you should also keep in mind how users or departments in your company typically discuss and question their numbers. Some companies follow specific guidelines; some departments have even defined standard agendas. (In some cases, the Pivot Navigation could even trigger these types of standardized procedures because it creates the ideal requirements to intro­duce and enforce standards.) In case of doubt, observe how strongly the dimensions distinguish and use that as a guide. It usually makes sense to start with dimensions that only have a few members, thus, distinguish strongly. The dimensions with many members follow later on. Sales controllers, for example, first ask for regions or countries and then products. Responsibilities within the organization also provide valuable information: Attributes, where you know who is responsible for the respective results or who can influence them, often appear at the beginning of the analysis path.

Member selection

For each navigation step, you need to select which members from the respective dimension you wish to use. You can do this either explicitly (i.e. you select them individually) or implicitly (i.e. DeltaMaster automatically selects them based on the default settings).

• If you select a dimension or level with the hierarchy menu shown above, the current default settings for the Pivot Navigation will automatically apply to this step.

You can edit these default settings in Miner mode by configuring the Pivot Navigation (Change menu in the Cockpit window).

Here you can opt to limit results to “X” number of members. This helps the report consumers stay focused since only the most important members are included. Alternatively, you can also choose the option to show top and bottom members (by algebraic sign) including remaining members as shown with the customers in the example above. This is especially interesting for variances: This option displays positive and negative variances together in one table. For more tips, please read DeltaMaster clicks! 02/2015.

If you work in Analyzer mode or do not customize the configuration of Pivot Navigation in Miner mode, DeltaMaster will apply the default setting which sorts the members (top or bottom depending on the factor in the Measure Properties and the algebraic sign of the actual value) and limits the count to 10.

• Alternatively, you can define each step explicitly (i.e. individually) by using the hierarchy menu when you create the step or anytime afterwards.

If you choose the user defined Navigation in the hierarchy menu, DeltaMaster will open the Navigation Definition dialog box where you can define the next navigation step in detail.

This dialog box is, more or less, the same as the Axis Definition when creating pivot tables. You can use it, for example, to define how users can select mem­bers: individually as a Member selection (e.g. pick list), by levels as a Level selection, or using an MDX expression. The regular options to sort (Ranking) and Filter members are also available. What is different from the Axis Definition is that you select the desired dimension at the very top of the Navigation Defini­tion. (In the Axis Definition this is fixed because it is predefined through the Report or Cockpit Definition).

The same dialog box will also open when you edit a Navigation for a predefined step at a later time. You can use this menu on steps that were configured implicitly or explicitly.

Due to the close similarities between the Navigation and Axis Definitions, you can also view the Pivot Navigation as a series of nested pivot tables. In each nested pivot table, a member of the next, outside table works like a filter on the row axis.

Save & go

Once you have run the Pivot Navigation as the task at hand requires, open the Table Properties (context menu or I want to menu, or F4 key). The Pivot Navigation tab contains a summary of the analysis path. Here you can save non-automatic navigation steps (on row axis) as default by clicking on the respective link. The word “non-automatic” is used here to remind you that DeltaMaster will only save these steps as explained above. If you use the automatic navigation somewhere in the table, DeltaMaster will only include the path to that point. “On row axis” was added to remind you that you can only save a standard analysis path on this axis. This is no major limitation because you would only use the Pivot Navigation on this axis anyway. “As default” was also added to emphasize that the saved analysis path will serve as a default setting so that report consumers can use the Pivot Navigation in a very comfortable, intuitive way. Generally speaking, they can still use the other options as well (e.g. hierarchy menu and data mining automation).

Back to the beginning

Once you save the navigation steps as default, close the Table Properties. The analysis path is saved in the pivot table but is still open as well. If you saved the report like this, you would have to trace back the steps of the navigation each time you open the report before you can start the interactive navigation.
As a report editor, therefore, you should close each step run in the navigation to restore the table to its original form. The easiest way to do this is to collapse the first step by clicking the minus sign. This will close all of the following steps as well.

At the very end save the report and the application or analysis session. You can now use the predefined navigation in Viewer and Pivotizer modes (see DeltaMaster clicks! 04/2014). If you double click on any values in the table, you can analyze them using the same steps defined previously. You can also use the predefined navigation in Web Option or DeltaMaster Navigator; no additional licensing is required.

Questions? Comments?

Just contact your Bissantz team for more information.

Greetings, fellow data analysts!

The vast, built-in library of analysis methods is one of the strengths of DeltaMaster. Although the special algorithms, heuristics, and report formats in Miner mode usually stand out the most, the software offers many other functions that deliver new insights within tabular reports as well. One example is the cluster analysis, which searches for similar structures in the report data and highlights what deserves attention. This function is so subtle and simple to use that we nearly asked ourselves if it could even fill an entire issue of DeltaMaster clicks! – but exactly this subtlety should get the full attention it deserves.

Best regards,
Your Bissantz & Company team

DeltaMaster offers a variety of fast analyses to help you examine pivot tables and cross tables more closely. Like a good personal assistant, these methods scrutinize the table and automatically highlight interesting findings. One example is the cluster analysis, which identifies similar objects and arranges them into groups. This analysis is useful for profiling customers, identifying market segmentations, or simply searching the objects within a table for similarities.
A cluster should group objects that are as similar as possible – and as different as possible from the objects in the other groups. Clusters, therefore, should be very homogenous so that the objects within it are easy to characterize. Different clusters, however, should be very heterogeneous so that it is easier to take clear-cut actions.

Performing a cluster analysis in pivot tables and cross tables is very simple in DeltaMaster. It requires no parameters or prior knowledge – and the results speak for themselves.

Starting an analysis

The cluster analysis is available in both pivot tables and Cross Table Analysis. Pivotizer, Analyzer, or Miner mode is required for pivot tables; Analyzer or Miner mode for Cross Table Analysis. Although the analysis works the same way in both modules, the Threaded Analysis Technology is only offered in combination with the Cross Table Analysis.
You can access Clustering along with the other fast analyses in the context menu of the table under the section Analysis.

From the submenu, you can now select which objects (i.e. rows or columns) DeltaMaster should group and how (i.e. by the share of the row/column sums or absolute values).

That’s all! DeltaMaster will instantly start the calculation and attempt to group rows or columns where the values or shares are similarly distributed among the members of the other axis.

Agglomerations

The example on your right from our “Chair” reference application shows a comparison of product and customer dimensions – a typical starting point for a cluster analysis. To emphasize the relationships among these values, we have activated circles (context menu, Graphic section, with a global scale which you can set in the Table Properties on the Graphic (1) tab). This visualization may be a bit surprising at first: In most reports, we use bars or columns because it is easier to estimate lengths or heights than areas. Circles, however, have the advantage that you can compare them in two directions (i.e. vertically and horizontally), and a comparison by rows and columns is just what we need here. Since products and regions are attributes of equal footing, you can use circles to signalize relevant combinations (see our posting on “Criss-crossing circles” at https://www.bissantz.de/bissantz-ponders/criss-crossing-circles). Yet even though these types of charts are suitable, you still cannot identify similar demand patterns just by looking at them.

Cluster analysis will change that in an instant. If you now group the rows by shares, DeltaMaster will automatically switch the tabular view to display the shares of the row sum instead of the absolute values. The cluster analysis rearranges the rows and groups similar members together, ignoring any sort orders of the axis. DeltaMaster designates the cluster with a black frame. In this case, five clusters has been found and the optical impression confirms the statistical findings.

• The first cluster contains products with high sales volumes in the North, average volumes in the South, and low volumes in the East and West – each based on the total volumes with this product (i.e. row sum).
• The second cluster shows products with high volumes in the North and similarly low volumes in all other regions.
• The third cluster includes products that only sell well in the North.
• The fourth cluster comprises products that primarily sell in the North but with a good portion in the South as well. The other regions are irrelevant.
• The fifth cluster covers products that generate a considerable portion of their sales volumes in the East and West.

Entries that do not match any of these patterns or each other are presented at the bottom of the table, separately from the clusters.

If you opt to calculate the clusters using absolute values instead of shares, you will get a different picture. Both types of analysis are justified. Oftentimes, you will focus on absolute values and, in turn, clusters with high values. Observing relative values, however, can make sense and provide interesting insights as well. You could, for example, develop the same guidelines for shipping logistics for products with similar numbers of sold items even though the sales volumes generated through them are of different magnitude.

If you save a pivot table or Cross Table Analysis with a calculated cluster analysis, you can access the clusters from Viewer mode as well. If you then refresh the report or change the View, for example, to another month, DeltaMaster will automatically recalculate the cluster analysis.

Behind the scenes

Cluster analysis in DeltaMaster is designed so that is simple to use and robust against different constellations of data. If you care to know the details: It uses a Euclidean distance to determine the similarity of objects and groups them using the average linkage method. An additional set of rules ensures that the analysis generates usable results even under adverse conditions. Thanks to the adapting threshold, you will rarely experience that DeltaMaster finds no clusters or only one cluster containing all objects.

Threaded Analysis Technology: Reusing found clusters

In many cases, cluster analysis may provide all of the information you need – namely, a reference to data relationships that you probably would not have detected without computerized help. Sometimes, however, you may want to go a step further, for example, to analyze the identified groups and use these clusters in other reports and analyses.

You can perform these types of advanced analyses starting with a Cross Table Analysis in Miner mode. You can first save the found clusters as a virtual hierarchy, which you can then access in the Dimension Browser, in pivot tables, analysis methods, etc. along with the dimensions that DeltaMaster has received from the database. Since the hierarchy is administered solely in DeltaMaster, no changes to the database are necessary. DeltaMaster will initially give the members default names (e.g. “Cluster 1”, “Cluster 2”), which you can later change in the Dimension Browser (context menu or F2 key). Members that do not match in any cluster are assigned to the virtual member “Non-clustered”. If required, DeltaMaster can also create a “Residual class”, which serves as a collection area for all members omitted from the current table view (e.g. rows/columns that are empty or have been filtered out). For more information, please read DeltaMaster deltas! 5.5.5, feature #10.

You can create a virtual hierarchy from the Drill-in menu (Analysis window). This entry will only appear if DeltaMaster has calculated the cluster analysis and actually found clusters.

For reusing clusters as a virtual hierarchy, it may be reasonable to define a Cluster Count. You can enter this number in the Settings of the Cross Table Analysis (see DeltaMaster deltas! 5.5.6, feature #7).

Universal usage

You can apply cluster analysis to any pivot table or Cross Table Analysis you wish. This analysis is well suited for customers, regions, products, materials, and other attributes that you can plausibly define and examine as a group. You can also perform a cluster analysis on nested axes, for example, if you want to observe articles and different configuration options at the same time. In some scenarios, however, you would not want to use a cluster analysis due to the given content. It would not make sense, for example, to group accounts, P&L lines, or the measures of a KPI view. You would also generally not search for clusters in the time dimension.

Questions? Comments?

Just contact your Bissantz team for more information.

Greetings, fellow data analysts!

The Brockhaus encyclopedia, once known throughout Germany as the epitome of a reliable information source, is now a thing of the past: The printed version was discontinued in 2014. New media and Internet portals such as Wikipedia sealed its fate. If you browse through their articles, however, you will sometimes miss the professional expertise that made the printed works so renowned. On the other hand, you probably do not want to miss the flexibility to jump from article to article – or the interactive cross references, which link articles and further information to each other so that you can explore your thoughts more deeply. That is enlightening and fun as well – as long as you don’t get bogged down in the masses of knowledge. This same handling can give management information a boost, too. In the pivot tables of DeltaMaster, you can create links that guide users to detailed analyses. These links can even transfer members and values from the initial report for further investi­gation. To ensure that no one loses their way on the analysis paths, the report editor defines where to place the links as well as where they should lead in advance. Such reports are enlightening and fun to use!

Best regards,
Your Bissantz & Company team

A good report does more than simply answer a single question – it triggers new ones as well. Great if you can find the answers to those questions directly from the same report!
To do that, many DeltaMaster users rely on links. Links enable users in Viewer, Pivotizer, and Analyzer modes to jump from a table cell to predefined reports and analyses. The View of the initial report remains the same: If the user has set any filters, they apply to the target report as well. Filters and measures can also be transferred from the initial report so that the target report returns the exact perspectives and measures that the users want to investigate. This approach opens analysis paths that are as flexible as in Miner but as simple as in Viewer. We will explain that in detail later.

Adding links

You can define links in pivot tables in Miner mode (Change menu in the Cockpit window).

For each pivot table, you can define multiple links that are displayed in the same order that they are in the dialog box. You can sort the links by dragging the small gray fields in the very first column and dropping them to the desired position.

Each link has two mandatory fields: Target Type and Target. All of the following fields in the link definition are optional or pre-configured automatically so that they work without any additional changes on your part.

The Target Type shows the type of object that the link references.

• The most important Target Type is Cockpit/Analysis Template. These are the pivot tables, FlexReports, Combination Cockpits, and parameter sets of analysis methods that were created and saved in Miner mode. When you follow this type of link, DeltaMaster will dynamically create a new report based on the selected cockpit or analysis template.
• If the target is a Report or First report in folder, DeltaMaster will open an existing report and select it in the Briefing Book.
• If the target is a Folder, DeltaMaster will display its Title Page as it is configured in the Folder Properties and select the folder in the Briefing Book.
• An External link references files or folders in the file system or documents on a Web server. DeltaMaster will follow these links in either an internal Web browser in DeltaMaster or an external one based on the settings in the Options (Extras menu) on the General tab.

The Target Type determines how to select the link’s actual Target.

• If you are a report editor, you may already know the dialog box for selecting Cockpits/Analysis Templates from the analysis method Small Multiples and from Combination Cockpits. The selection list is sorted alphabetically. The analysis templates are grouped by the respective analysis methods.
• The selection lists for a Report or Folder are also sorted alphabetically.
• For an External link, you enter the respective URL (F2 key), for example: “http://www.bissantz.com/”. You can also select a file using the selection dialog box. UNC paths are supported.

Once you have selected the Target Type and Target, the link will work.

Displaying links in the pivot table

Starting in version 5.6.3, DeltaMaster will designate when links are defined in a pivot table by placing small symbols directly in the report of Viewer, Pivotizer, and Analyzer modes. In Miner mode, you can choose which visualization you want to use (View Type) and where the symbols should be placed (View Location) – specifically, either in data cells or in row headers.

• A gray, diagonal arrow indicates a data cell or row header with exactly one link. Click on the arrow to follow that link.

• A menu symbol with three horizontal lines (as you know from smartphones) indicates a data cell or row header with multiple links. The menu will open when you mouse over it. Click on an entry to follow that link.

• In place of the arrow, DeltaMaster can also display a function symbol (“f”). This resembles the mathematical symbol that describes dependencies of variables (“f(x) = …”). This symbol can give the link a special meaning, for example, when it refers to a functional report where the respective value serves as a starting variable for further calculations. The function symbol, like the arrow, stands for a single link. Click on the symbol to follow that link.

DeltaMaster will automatically choose between arrow and menu symbol: If a data cell or row header has exactly one link, DeltaMaster will display an arrow. If there are several links, DeltaMaster will combine them in a menu and display the menu symbol.

If you enter a Text to a link, DeltaMaster will display it as a tooltip for arrows and function symbols or as a caption in menus. If the Text field is empty, it will not display a tooltip and show the original names from the Target field in the menus. The Text is designed for end users and has a great influence on whether they consider the application to be user-friendly or not. Some report editors even enter questions in this field – the clearer the better! If links have the Target Type Cockpit/Analysis Template, DeltaMaster will also display the text as a heading in the Report window.

To learn more about displaying links in a report, please read DeltaMaster deltas! 5.6.3, feature #5.

Conditions for links

Sometimes links are only relevant when certain Conditions have been met. You may, for example, only offer a customer list if revenues have been booked for a certain country or product group.

You can define these criteria as an MDX expression – for example, “[Measures].[Revenues] > 0”. DeltaMaster evaluates these conditions for each cell based on the respective row and column members. If that is not desired in certain cases, you can set the value type, time reference, etc. using tuple expressions.

In the bottom part of the definition dialog box, you can opt to only display links for certain objects (i.e. measures). The list refers to the link selected in the upper part of the dialog box. If the option is activated, DeltaMaster will only show the link in Viewer, Pivotizer, and Analyzer mode if a data cell or row header contains one of the listed measures.

DeltaMaster will evaluate all of these criteria dynamically and take them into account when it differentiates how many links (i.e. one or more) are valid for a data cell or row header. The same report, therefore, may contain a larger or smaller number of links depending on the current view.

Links and filters

One feature that is especially interesting in interactive data analysis is that links can transfer properties of the current report view to the target report. This affects the selected members in the View window as well as members on the rows and columns of the pivot table.

• If you switch to another report in the Briefing Book, DeltaMaster will always display the “new” report with the View that was saved with it. If you follow a link, however, the View will not change. If a user, for example, has set individual filters for countries, product groups, and sales channels in an overview report, these filters would also apply to the detailed reports that are available through links. Miner users will recognize this handling from cockpits. Now users in Viewer mode can enjoy this same handling through links.
• DeltaMaster can also filter the target report based on the row objects and/or column objects. If the user follows a link, DeltaMaster will recognize which dimension members (objects) are valid for this cell – figuratively speaking, how the rows and columns of the respective table cells are labelled. DeltaMaster will then open the linked report and select these exact members in the View. In doing so, the value of the cell will appear in the target report the exact same way as it was displayed in the initial report. You can activate the filters of the target report separately by row and column objects. If the links are displayed in the row heading, you can only filter row objects; the option for column objects will be deactivated. If neither option is activated, the link will work as if you changed to another report with a constant View.
• You can also transfer a measure similar to how you would transfer dimension members. If you have defined a link to an analysis template from a Ranking where the customers are sorted by revenues, you can also use the same link for all other measures of the pivot table. This will create a ranking of customers by revenues, discounts, margin, etc. depending on which measure applies to the cell containing the link. If the link target works with more than one measure (e.g. Portfolio Analysis or pivot table with a KPI overview), this option will have no effect. See DeltaMaster deltas! 5.5.1, feature #10 for more information.

The View Context, which applies to filter settings in Viewer mode, determines the dimensions that Viewer users can see and where they can make a selection. This also applies to filters that are set by links. The view context of the initial report has precedence over the context of the target report. The Default Selection (see DeltaMaster deltas! 5.5.7, feature #22) will not apply and be removed in all hierarchies.

The example below links all cells to the same target (i.e. the report “Customers Overview”). The link is configured to filter row objects and column objects as well as transfer the measure.

If you follow the link for the selected value “74.092”, you will see the following:

The report “Customers Overview” is now selected in the Briefing Book and displayed for September 2014 – as it is in the initial report. From this report, DeltaMaster has also taken the row object “Sales: Brown” and the column objects “cum.” and “?Budget”, which are all selected in the View window and act as a filter on the target report. DeltaMaster has also transferred the measure “Rebate” to the target report.

Returning back

You may want to offer the option to return back to the link’s origin from a linked report. DeltaMaster will then display this return link above the report as shown in the screenshot above. You can also link multiple pivot tables like a chain. Before you use this option, please take into consideration that multi-level analysis paths can quickly get confusing and require a great deal of concentration from the individual users.

Analysis paths just like in Miner, yet simple like in Viewer

You do not need to use links in Miner mode, because it offers the same functionality in a different way. In Miner, you can, for example, use the drag-and-drop function to transfer measures from a pivot table to an analysis method (Threaded Analysis Technology), and the View remains the same when you switch from one cockpit to another. You can implement these functions in Viewer mode with the help of links. For this purpose, you will need to define which detailed reports and analyses are possible in advance (Miner mode) to provide users with logical in-depth analyses for that given situation.

Alternatives: Keeping the context to prevent change blindness

Links are not the only way you can offer detailed analyses of pivot tables in Viewer mode. In many cases, the Pivot Navigation offers a good, or even better, alternative. And if you want to offer a time series for more detailed analysis, you can also use sparklines in combination with the Semantic Zoom function. These alternatives both provide additional information in the original context by expanding the existing report step by step and not switching to a different report. For more information, please read Delta­Master clicks! 04/2014.
With the Pivot Navigation, users can display, for example, the main customers who have caused a variance as additional entries directly within the original report (see DeltaMaster clicks! 02/2015).

To examine absolute and percentage changes from period to period, you can also zoom into sparklines that are displayed in the report. The smooth tran­sition from a cell graphic to a chart makes the content easy to understand.

The resulting visualizations are very comprehensible. The types of comparison stay the same and you never lose sight of the analysis path. This helps minimize the risk of change blindness, a natural phenomenon in which people completely oversee even major changes when their attention is focused elsewhere. This effect can occur with links. If the entire picture changes as the readers switch from one report to the other, they must concentrate more to stay focused and not forget which values and context they were examining in the first place – similar to getting lost in hyperspace. This problem does not occur with the Pivot Navigation and Semantic Zoom functions, which keep the original context and give the reader a firm holding.

Design options

As a thoughtful report editor or application designer, you should always check whether links, the Pivot Navigation, or sparklines with the Semantic Zoom (in the case of references to time) are the best way to offer details to your readers. If you want to present the details in the same structures as the initial values, the Pivot Navigation is the best choice – provided, of course, that you have valid licensing for the Navigation Option or use DeltaMaster Navigator. In cases where you need or want to change the visualization (e.g. to jump from a pivot table to a Geo Analysis map), links are the only option.

When planning analysis paths with links, you should also take the structure of reports into consideration. In cases where the users keep filters or measures while switching to another report, they should expect that the basic assumptions and main structural characteristics will stay the same. For example, the reading direction should remain constant so that the previous year, budget, variance, and forecast values are all displayed in the same order. These types of design decisions play a pivotal role in determining if the application is easy to use and understand.

Questions? Comments?

Just contact your Bissantz team for more information.

Greetings, fellow data analysts!

A general rule of writing says that the most important message belongs at the top, for example, in the heading or at the beginning of an explana­tion. That’s how we learned it, and that’s how we do it – in writing as well as reporting. When you analyze customers, products, materials, etc., you probably also sort them by a measure so that the most relevant objects appear at the top of the table. Yet in the case of variances, which are probably the most important information of all, the top items only reveal half of the story. The largest positive variances are at least just as significant as the largest negative ones that lie at the very bottom – unless, of course, you take special precautions. So how can you design reports so that both types of variances get the attention they deserve? In DeltaMaster, you can configure reports to automatically unveil positive and negative factors. We personally recommend starting with the good news first*. The choice, however, is up to you.

Best regards,
Your Bissantz & Company team

*www.bissantz.de/bissantz-ponders/neurocontrolling-1

Making management decisions rarely requires a complete understanding of all clients, all products, all cost centers, or other influencing factors. Usually, just the most important ones suffice. For absolute measures such as revenues, you can easily find the most relevant factors in a sorted list. (That, too, can get tricky if you need to sort objects across different levels or hierarchies, but we won’t delve into that here.) When you are observing variances (for example, between actual and budget values or the current and previous year), a simple, sorted list is not very helpful. Since variances can be either positive or negative, the list shows the most important things at the very top and the very bottom – with many less interesting items in between. So how can you display the most relevant variances, whether positive or negative, together in a concise report? DeltaMaster offers many options, which we will explain below.

Top and Bottom in pivot tables

Pivot tables are the most important format for standardized reports. Using a special sort order, you can display the most important positive and negative values next to or on over each other in the pivot table. DeltaMaster will automatically pool the residual, non-listed members and display them as “(remaining top)” and “(remaining bottom)” between the two groups.

In the Axis Definition on the Ranking tab, you can specify if DeltaMaster should sort the members by TopCount/ BottomCount or BottomCount/TopCount. For the parameter, you enter how many top and bottom members should be included. If you choose 5, for example, DeltaMaster will display the five most important positive and the five most important negative results as well as the remaining top and bottom members. For more information, please read DeltaMaster deltas! 5.5.8, feature #15.

Top and Bottom in the Pivot Navigation

DeltaMaster can automatically focus on the most important positive and negative members when you analyze variances step by step in a pivot table.

If you want to make this function available to users in Navigator or Viewer mode (with the Navigator option), you must configure the Pivot Navigation in the respective pivot table from the Change menu of the Cockpit window (Miner mode).

As one of the options, you can define that DeltaMaster should only show top and bottom members, including remaining members. This setting is valid for every kind of use of the Pivot Navigation:

• For the automatic navigation where DeltaMaster automatically selects the attribute to be examined in the next step based on a data mining method.
• For the predefined navigation where you predefine a certain sequence of attributes. In this case, the sort order of the top and bottom attributes is the default setting for each step.
• For the user-defined navigation, where users click on a small arrow below a value to open a hierarchy menu and then select the next step themselves (according to the defined Navigation Context).

For more information, please read DeltaMaster deltas! 5.5.8, feature #13. Both functions described above only work in combination with MDX OLAP models (e.g. Microsoft Analysis Services or SAP BW).

Ranking with separation by algebraic sign

As an alternative to pivot tables, you can use a Ranking report. When you configure this analytic method, you can specify that DeltaMaster should sort the elements of the selected dimension and display them in two groups next to each other. These types of reports show the top per­formers as well as the biggest flops. Since DeltaMaster applies a common scale to the in-cell bars on both the left and right sides, the readers can easily compare values just by looking at them. In the screenshot above, for example, the positive bar of the second-best product “DCW Plywood” is just as long as the negative bar for “Softpad 117”.

To create these types of reports, you only need to configure two settings in Miner mode:
First, you will need to select the option to display Top and Bottom members (column header or I want to menu). This will divide the report layout into two sections.

Secondly, you will need to activate the rules for the algebraic sign in the Settings. On the Cut tab, you select if DeltaMaster should separate the top and bottom objects based on their sign (positive/negative). On the Filter tab, you have the option to select further measures that should be visible in the report. Nested dimensions, as you know from pivot tables, are not supported. On the General tab, you can limit the result to display a certain number of objects. This number applies to the top and bottom members, similar to the Count in the Ranking options of a pivot table. Since a report created as a Ranking can be scrolled and usually only displays a limited number of other measures, it can generally handle a larger count of objects than a pivot table. Setting a reasonable upper limit in a Ranking report ensures faster processing and is easier on the readers, who rarely need hundreds or even thousands of members in a list to support their decisions.

An analysis can be even more when it presents several rankings next to each other instead of just one. To do that, you save the settings as a so-called analysis template. You can then access this template in the method Small Multiples, which iterates them over and over with different report content. The results become part of an automatically formatted “multi-report”.

The screenshot on your right shows an example of this type of Small Multiples report (with a different view than in the past examples). Instead of one, this report contains four rankings, one for each region. The bars are globally scaled so that you can easily compare the different regions to each other. If you wish to discuss the results within a particular region, you can switch the scales to individual, for a better differentiation.

For more information on Small Multiples, please read DeltaMaster clicks! 12/2012.

Waterfall Analysis

You can also use visualizations to compare and contrast positive and negative variances. A Waterfall Analysis, which specializes in these types of presentations, displays a variance as a transition from a start value to an end value (e.g. from the value of the previous year to the current year or from the budget to the actual value). DeltaMaster will arrange the objects (e.g. customers, products) from start to the finish to show their contribution to the growth or decline. In contrast to the methods described previously, the variance in a Waterfall Analysis does not need to be defined as a measure. You select the start and end value, and DeltaMaster will calculate the variance on its own.

As often is the case with charts, choosing the right scale can be tricky, especially when the values of the individual objects are relatively small in comparison to the start and end values. In this case, you may want to hide the columns for these values using the respective function in the context menu or the I want to menu as we did in the screenshot above. Although the first and the last column have been “cut off”, the visualization is still an honest representation because the changes remain proportional to each other and to the total change. Similar to pivot tables, DeltaMaster will combine the smaller values into groups with causing or compensating objects. You can adjust these groups in the Settings.

Knowing when

These and other refined options make the Waterfall Analysis a good tool for specialized visualization and analytic needs. For standardized reports and (guided) analysis, graphical tables such the sorting options in a pivot table or Ranking reports are the better choice. Using pivot tables has its advantages, especially when you need to view attributes in different (nested) dimensions or levels, wish to use graphical elements as signals, or offer integrated analytic functions to your readers. Although a Ranking report has a relatively simple structure in comparison, you can intuitively display both lists next to each other, support larger numbers of members, and create highly informative comparisons in combination with Small Multiples.

Greetings, fellow data analysts!

You certainly have seen files names with appendages such as “final v2” in your email inbox or on a server – names that reveal the authors’ premature hope that they would soon be moving on to other matters? Although packing the results of your work into files is convenient, files have the tendency to change and multiply like wild. With good organization, you can keep this problem under control. As the number of people who work with these files increases, however, many DeltaMaster clients prefer a technical alternative: Instead of distributing reports as files, they offer them through a central database known as Repository. This way, each application only exists once and they can control who can work exactly how with the application. Repository, therefore, is an important foundation for supplying information with DeltaMaster especially for larger or even enterprise solutions. This version of clicks! provides an brief overview – with no “v2” version to follow.

Best regards,
Your Bissantz & Company team

When you work with DeltaMaster, you can save your results in one of two ways: in files or databases.

Saving results in files is the older of the two options. Most DeltaMaster users have probably already worked with DAS files which contain Briefing Books along with their reports and analyses as well as all definitions, notations, settings, and so on.

As an alternative to files, you can also save all of these elements to a database or open them from a database also known as Repository. This has its advantages, especially when you want to deploy applications and reports that were created centrally to a large number of users.

On the following pages, we will provide an overview on working with Repository especially from the perspective of a report consumer. We will also compare this concept to saving results in files. For your IT experts, we will also briefly outline the system requirements and the installation process; detailed instructions are provided in a separate documentation.

Terminology

Repository is the central component for the database-driven deployment and administration of DeltaMaster applications. It also serves as the foundation for DeltaMaster WebOption, Add-in for Office, and DeltaMaster Gate. Repository is an administration database that is separate and independent from the database where the analysis data are stored. Instead of business attributes and measures, Repository stores DeltaMaster applications – the same information contained in a DAS file but in a database. Organizational information (e.g. permissions) is also stored in Repository.

Technically speaking, Repository consists of two components: a relational database where the applications, permissions, roles, etc. are stored, and a Windows service (“DeltaMaster Service”), which DeltaMaster uses to access the Repository database. This detail, however, is irrelevant for the purpose of this overview. The term Repository here refers to the service and database together. To interactively administer the objects in Repository, a further component called Repository GUI is available.

In DeltaMaster 5, the analytic results that you save are called an “analysis session”. When the analysis session is saved to a file, this is an “analysis file” (i.e. DAS or DM2GO file). When saved to a database, the analysis session is called an “application”. DeltaMaster 6 has simplified this terminology and always refers to “applications” regardless if they are stored in a file or in a Repository.

Opening and using applications

Opening applications from the Repository is simple. The Portal of DeltaMaster lists all known Repositories in a separate section (see screenshot on the previous page), each with a list of the related applications. If DeltaMaster does not display the section showing the Applications in Repository in the Portal, you must configure the Repository access for the DeltaMaster installation or the current user (see below for more information about the configuration).

DeltaMaster accesses the list of applications from the respective Repository where they are centrally maintained by the report editors. In doing so, DeltaMaster takes into account which user is logged on to the system and only displays the applications which the user can access in the Portal. In the context menu of a Repository, you can refresh the list of applications. This is useful when someone publishes additional applications while you are working with DeltaMaster. DeltaMaster will then display these applications in the Portal without requiring a restart.

Compared to working with files, this way of accessing applications is much easier for everyone involved. Report viewers are spared the task of searching for files (and the agony of wondering which version is correct). Instead, they select them from the predefined applications in a centrally maintained directory. Report editors don’t have to worry about distributing applications. All authorized users can access them from Repository – instantly and in the same version. As a result, any changes to reports instantly take effect for all users, and you can even pull reports completely from circulation.

Once you have opened the application, it looks and reacts identically to analysis sessions from files. The functions, the options, the way you work with reports and analyses are not dependent on where you have opened the application. The only difference occurs during saving: There are no drives, folders, or file names in Repository. Contrary, the Repository contains mechanisms like rules and rights to manage, who can save which objects, that don’t exist for files.

Changing and saving applications

As a report consumer, you generally only open an application to read it – regardless if you are using Reader, Viewer, Pivotizer, Analyzer, or Miner modes. These user modes determine which interaction and editing possibilities exist within the application. If you can save them in Repository depends on if the application was opened for read or write access.

In order to save changes, you must first open the application by the options dialog (context menu of the application in the Portal).

In the Options dialog box, you select to open the application for exclusive write access. This option protects the applications in Repository: It coordinates the processing by preventing multiple authorized users from working on the same objects at the same time and overwriting each other’s results.

Accordingly, DeltaMaster only grants write access to one user. During this time, other users can only access the application in read-only mode. If you want exclusive write access, you must request it explicitly when opening the application. You cannot claim it retrospectively.

The commands to save in Repository are organized like those for files in the File menu. You can save applications that are already opened from Repository by using the shortcut Ctrl+S on your keyboard. Here, there is practically no difference to saving to a file.

When you save an application to Repository for the first time (e.g. because it is a brand-new application or because you want to publish an existing analysis file through Repository), DeltaMaster will prompt you to complete a few mandatory specifications. At the top of the dialog box, you select the Repo­si­tory where the application should be written (see DeltaMaster deltas! 5.6.0, feature #6). The Connection properties derive from this selection. FileID is the unique identification number of the application. In the case of a new application, it is better to let DeltaMaster select the FileID automatically. For more information about the ID, please read the DeltaMaster WebOption handbook. The Name of the application will be shown wherever you can access Repositoriess. With Repository GUI, you can change it anytime and add a description as well.

Administering rights

One interesting function of Repository is that you can assign permissions for applications and even individual reports and folders of the Briefing Book. This makes it possible to provide or prohibit access to selected reports or folders for certain users (or the roles in which they are grouped). Depending on which rights the person opening them has, the same application may appear to be larger or smaller in scope.

The administration of permissions is broken down into two components and accounts for the prevailing distribution of tasks between IT and business departments.

In Repository GUI, you can define which user can access which applications at all. Here you can also define the membership of users in roles.

Directly in DeltaMaster, you can maintain which folders and reports the users in a role can access within an application (Folder Rights or Report Rights in the respective context menu in the Briefing Book). You can also specify the Session Rights (File menu) or Briefing Book Rights (context menu or I want to menu in the Briefing Book).

For detailed information about administering applications and roles with Repository GUI, please read the DeltaMaster WebOption handbook. Further information about report and folder permissions is contained in DeltaMaster deltas! 5.4.9, feature #20.

Configuring access to Repository

In order to access one or more Repositories, DeltaMaster requires their “coordinates” – specifically, the names or IP addresses of the servers that offer a Repository service. DeltaMaster retrieves this information from Windows Registry in one of two ways. First, it can be imported automatically from a central authority (e.g. system administrators or IT). This way, the individual workstations can be set up so that all Repositories are known and available without requiring any work from the users.
Secondly, you can allow users to enter Repositories on their own. In this case, they would need to enter the necessary information with a DeltaMaster dialog box that is available under Options (Extras menu) on the Portal tab. The upper part of the dialog box lists the Repositories that were entered by the system administrators. Users can neither change nor remove these entries. In the bottom section, they can add further Repositories. Each Repository monitors that the applications offered to a given user correspond with his or her permissions. Entering a Repository in the options, therefore, does not mean that the user can automatically access all listed applications. These applications still must be activated for that particular user. For more information, see DeltaMaster deltas! 5.6.0, feature #8.

Repository and ReportServer

You may use applications in Repository as a source of jobs in ReportServer. Aside from that, you can also use ReportServer to automatically publish applications in Repository. This way, you can use the update and iteration options in ReportServer to automatically prepare a number of individual Briefing Books and publish them through Repository.

Comparing the concepts

You can build DeltaMaster solutions using file-based, database-driven, or hybrid approaches. In addition, you can also transfer analysis files to Repository or save applications from Repository as analysis files at any time. This gives DeltaMaster users the freedom to decide for themselves whether they want to build a new solution in Repository from the beginning or migrate them at a later time (e.g. as the number of users begins to grow, report usage needs stricter regulations, or application deployment requires rationalization).

The following table compares and contrasts various characteristics of file-based and database-driven approaches to deploying applications:

Both options have their advantages. You cannot really say that one is generally better than the other – only which one is better for your specific scenario.

What IT has to know

Installation and the initial configuration of Repository are generally tasks for IT professionals – primarily because these tasks require special permissions and not because they are particularly complicated. The complete system requirements are documented in a separate installation and configuration guide. The section below provides a brief summary.

Repository requires:

• Microsoft SQL Server (database module) for the Repository database
• Microsoft .NET 4 Framework for the Repository service

The installation also requires administrator rights

• On the operating system level to install the service
• On the database level to create the Repository database, configure its permissions, and modify the rights to the databases with the application data

Experienced administrators or application managers with the respective permissions should only need about half an hour for the installation and initial configuration – provided, of course, that the system requirements have been fulfilled. A setup program (MSI) is available (see DeltaMaster deltas! 5.6.0, feature #8). A detailed installation guide that explains the installation without a setup program is also available in English and German.

The Repository database and the respective service can run on either different servers or the same one. To test it, you only need a normal PC workstation or a laptop where you can run DeltaMaster (i.e. desktop client). All DeltaMaster modules with Repository support (i.e. desktop client, Report Server, Web Client, Add-in for Office, DeltaMaster Gate) can use the applications in Repository simultaneously and in the same form. In conjunction with the desktop client, the main processing load comes from calculating and displaying reports in the desktop client; this is the same as in file-based usage.

In most cases, you will probably want to make a central setting on the PC workstations where the users can access one or more Repositories. The “coordinates” of the Repositories must be disclosed to the client installations of DeltaMaster. DeltaMaster will search for them in the Windows Registry (see DeltaMaster deltas! 5.6.0, feature #8) so that they can be easily installed on the users’ computers using the typical mechanisms. In addition, users can also enter Repositories individually in a dialog box in DeltaMaster.