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.