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.